为什么在MEMORY表上的查询比在InnoDB上的查询慢? [英] Why is this query on a MEMORY table slower then its twin on InnoDB?

问题描述

我有一个表的InnoDB和MEMORY版本.两者具有相同的索引和相同的30,000行数据.当对MEMORY表执行时，有一个特定的查询运行非常慢.

I have an InnoDB and MEMORY version of a table. Both have the same indexes and the same 30,000 rows of data. There's a particular query that runs very slow when run executed against the MEMORY table.

这里反对InnoDB:

Here's against 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索引进行相同的操作，结果也相同.)

(Note that the person_id index is a BTREE index. I tried the same with the default HASH index and the results were the same.)

因此，MySQL似乎以不同的方式优化了第二个查询，这使其性能更差.这是为什么?我可以修复"它吗?

So it appears MySQL optimized the second query in a different way which made it perform worse. Why is that? Can I "fix" it?

推荐答案

您并不在乎.对于拥有30,000行的小型表，即使是表扫描，一切都将非常快.

You don't really care. With a tiny table with 30,000 rows, anything is going to be very fast, even a table scan.

但是，似乎它选择了不同的解释计划.在innodb情况下，首先使用custom_data_person表，并使用覆盖索引.然后，它查询email表中custom_data_person表中找到的每一行.这似乎是理智的解释计划.

However, it looks like it's chosen a different explain plan. In the innodb case, it's used the custom_data_person table first, and used a covering index. It then queries the emails table for each row found in the custom_data_person table. This seems like the sane explain plan.

这可能是内存表优化器中的一些不好的悲观.

It's possibly some bad pessimisation in the optimiser for memory tables.

我会避开内存表.如果要使用行为几乎类似于内存表的表，请使用MyISAM表并在服务器启动时将其截断.内存表难以置信的糟透了，因为它们存储的varchars已填充到最大长度，因此它们通常比另一种类型的表使用更多的内存. MyISAM非常有效地使用存储.或者，对所有内容都使用InnoDB表.

I'd steer clear of memory tables. If you want a table which behaves almost like a memory table, use a MyISAM table and truncate it on server startup. Memory tables suck incredibly badly because they store varchars padded to maximum length, so they usually use a lot more memory than another type of table. MyISAM uses storage very efficiently. Alternatively, use InnoDB tables for everything.

不幸的是，innodb无法提供按表设置持久性的方法，因此，如果每个事务的fsync困扰您，您就必须进行更大(因此更少)的事务.

Unfortunately innodb doesn't provide a way to set durability on a per-table basis, so if the fsync on each transaction bothers you, you have to do bigger (and hence fewer) transactions.

使用多个引擎是一种折衷，因为服务器几乎没有任何方法可以在引擎之间自动划分其(有限)内存.因此，您通常只想使用一个引擎.如果您这样配置的话，其中包括内存引擎，它将很高兴地从您的innodb中夺走内存堆(因此使其速度变慢，因为它可以容纳较少的数据库内存).

Using several engines is a compromise, as there is rarely any way the server can automatically divide up its (finite) ram between the engines. So you usually want to use just one engine; this includes the memory engine which will happily take away heaps of memory from your innodb (hence make it slower as it can fit less of your db in memory) if you configure it that way.

尽管如此，您确实真的不在乎3万行.即使内存很大，也可以将3万行存储在最小的内存中.当您使用3万行时，任何引擎都是内存引擎.

Seriously though, you really, really, really don't care for 30k rows. 30k rows can fit in the smallest memory even if they're vast. Any engine is a memory engine when you use 30k rows.

这篇关于为什么在MEMORY表上的查询比在InnoDB上的查询慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！