Mysql中Innodb和Myisam的性能差异

Question

我有一个MySQL表格，其中包含超过3000万条记录，最初是使用MyISAM存储的。以下是该表格的描述：

我会对这个表格运行以下查询，通常需要大约30秒才能完成。每次更改@eid以避免数据库或磁盘缓存。

select count(fact_data.id) 
    from fact_data 
    where fact_data.entity_id=@eid 
        and fact_data.metric_id=1

我将这个表格转换成InnoDB格式，没有做任何其他更改，现在每次运行查询时，相同的查询结果都可以在不到一秒的时间内返回。即使我随机设置@eid以避免缓存，该查询仍然在不到一秒的时间内返回。

我一直在研究两种存储类型之间的差异，试图解释性能显著提高的原因，但一直没有得出结论。事实上，我阅读的大部分内容表明Myisam应该更快。

我正在针对一个本地数据库运行查询，在测试期间没有其他进程访问该数据库。

- opike

Answer 1

这是一个出乎意料的大性能差异，但我可以想到一些可能造成的原因。

历史上，MyISAM被认为比InnoDB更快，但对于最近版本的InnoDB来说，这只适用于非常少量的使用情况。MyISAM通常对于只读表的表扫描更快。在大多数其他用例中，我通常发现InnoDB要快得多。往往快得多。在大多数MySQL用法中，表锁定对MyISAM来说是致命的。

MyISAM将索引缓存在其键缓冲区中。也许您将键缓冲区设置得太小了，以至于它无法有效地缓存相当大的表的索引。

MyISAM依赖于操作系统从OS磁盘高速缓存中缓存表数据。如果操作系统内存不足，它将开始转储其磁盘高速缓存。这可能会迫使它继续从磁盘中读取。

InnoDB在其自己的内存缓冲区中同时缓存索引和数据。如果将innodb_flush_method设置为O_DIRECT，则可以告诉操作系统不要使用其磁盘高速缓存，尽管此功能在OS X上不受支持。

InnoDB通常以16kb页面缓冲数据和索引。根据您在查询之间更改@eid值的方式，由于来自前一个查询的磁盘读取，它可能已经缓存了一个查询的数据。

确保创建的索引完全相同。使用explain检查MySQL是否使用索引。由于您包含了描述的输出而不是show create table或show indexes from的输出，因此我无法确定entity_id是否是复合索引的一部分。如果它不是复合索引的第一部分，则不会使用它。

如果您正在使用相对较新的MySQL版本，请在运行查询之前运行以下命令：

set profiling = 1;

这将为您的会话打开查询分析功能。运行查询后，运行

show profiles;

这将显示可用配置文件的查询列表。默认情况下，我认为它保留了最近的20个。假设您的查询是第一个，请运行：

show profile for query 1;

接下来，您将看到运行查询的每个阶段的持续时间。这对于确定是什么（例如，表锁定、排序、创建临时表等）导致查询变慢非常有用。

- Robert Stewart

Answer 2

我最初的怀疑是原始的MyISAM表格和/或索引随着时间的推移变得碎片化，导致性能逐渐下降。由于您创建InnoDB表时已经包含所有数据（因此所有数据都按顺序存储在磁盘上），因此不会出现相同的问题。

您可以通过重建MyISAM表来测试这个理论。最简单的方法是使用“空”ALTER TABLE语句：

ALTER TABLE mytable ENGINE = MyISAM;

然后检查性能看是否有改善。

另一个可能性是数据库本身只是针对InnoDB性能进行了调优，而不是MyISAM。例如，InnoDB使用innodb_buffer_pool_size参数来知道应该为存储在内存中的缓存数据和索引分配多少内存。但是MyISAM使用key_buffer参数。如果您的数据库具有较大的innodb缓冲池和较小的键缓冲区，则InnoDB性能将比MyISAM性能更好，特别是对于大型表格。

- Eric Petroelje

---

一个相对简单的测试是创建一个新的MyISAM表并计时查询该表，以确认这个猜测。 - Matt Ball

Answer 3

你的索引定义是什么？有一些方法可以为MyISAM创建索引，使得当你认为会使用索引字段时，它们却不会被使用。

- Christopher Brand