mysql - 为什么这个对 MEMORY 表的查询比它在 InnoDB 上的查询慢？

Question

我有一张表的 InnoDB 和 MEMORY 版本。两者具有相同的索引和相同的 30,000 行数据。有一个特定的查询在针对 MEMORY 表执行时运行速度非常慢。

这是针对 InnoDB 的:

SELECT emails.id
FROM emails
LEFT JOIN custom_data_person pd1 ON (pd1.person_id = emails.person_id)
WHERE pd1.field_id = 13

2928 rows in set (0.24 sec)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pd1
         type: ref
possible_keys: person_id,field_id
          key: field_id
      key_len: 5
          ref: const
         rows: 20240
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: emails
         type: ref
possible_keys: person_id
          key: person_id
      key_len: 4
          ref: test.pd1.person_id
         rows: 1
        Extra: Using index

这是内存:

SELECT emails.id
FROM emails_memory AS emails
LEFT JOIN custom_data_person pd1 ON (pd1.person_id = emails.person_id)
WHERE pd1.field_id = 13

2928 rows in set (1.40 sec)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emails
         type: ALL
possible_keys: person_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 30000
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pd1
         type: ref
possible_keys: person_id,field_id
          key: person_id
      key_len: 10
          ref: test.emails.person_id,const
         rows: 1
        Extra: Using where; Using index

(注意person_id索引是一个BTREE索引，我用默认的HASH索引试了一下，结果是一样的。)

所以看起来 MySQL 以不同的方式优化了第二个查询，这使得它的性能更差。这是为什么？我可以“修复”它吗？

Answer 1

你真的不在乎。对于一个只有 30,000 行的小表，任何事情都会非常快，即使是表扫描。

但是，看起来它选择了不同的解释计划。在 innodb 案例中，它首先使用 custom_data_person 表，并使用覆盖索引。然后它查询电子邮件表以查找在 custom_data_person 表中找到的每一行。这似乎是理智的解释计划。

这可能是内存表优化器中的一些糟糕的悲观情绪。

我会避开内存表。如果你想要一个表现得几乎像内存表的表，使用 MyISAM 表并在服务器启动时截断它。内存表非常糟糕，因为它们存储填充到最大长度的 varchars，所以它们通常比其他类型的表使用更多的内存。 MyISAM 非常有效地使用存储。或者，对所有内容使用 InnoDB 表。

不幸的是，innodb 不提供在每个表的基础上设置持久性的方法，因此如果每个事务的 fsync 困扰你，你必须做更大(因此更少)的事务。

使用多个引擎是一种妥协，因为服务器几乎无法自动在引擎之间分配其(有限)内存。所以你通常只想使用一个引擎；这包括内存引擎，如果您以这种方式配置它，它将愉快地从您的 innodb 中带走大量内存(因此使其变慢，因为它可以容纳更少的内存中的数据库)。

说真的，您真的、真的、真的不关心 30k 行。 30k 行可以容纳在最小的内存中，即使它们很大。当您使用 30k 行时，任何引擎都是内存引擎。