mysql - Innodb和Myisam在Mysql中的性能差异

Question

我有一个包含超过 3000 万条记录的 mysql 表，该表最初是用 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 应该更快。

我正在运行的查询是针对本地数据库的，在测试时没有其他进程访问该数据库。

Answer 1

这是一个令人惊讶的巨大性能差异，但我能想到一些可能有所贡献的因素。

MyISAM 历来被认为比 InnoDB 更快，但对于最近版本的 InnoDB 来说，这适用于小得多的用例集。对于只读表的表扫描，MyISAM 通常更快。在大多数其他用例中，我通常发现 InnoDB 更快。通常快很多倍。在我对 MySQL 的大部分使用中，表锁是 MyISAM 的丧钟。

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

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

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

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

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

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

设置分析= 1;

这将为您的 session 打开查询分析。运行查询后，运行

显示个人资料；

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

显示查询 1 的配置文件；

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