mysql在MyISAM和InnoDB之间的索引使用差异 [英] mysql difference in index usage between MyISAM and InnoDB

问题描述

我有这些小桌子， item 和 category ：

CREATE TABLE `item` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `category_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `category_id` (`category_id`)
) CHARSET=utf8

CREATE TABLE `category` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) CHARSET=utf8

我已插入100个类别和1000个项目。

I have inserted 100 categories and 1000 items.

如果我执行此操作：

EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;

然后，如果表的引擎是InnoDB，我得到：

Then, if the tables' engine is InnoDB I get:

+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+
| id | select_type | table    | type  | possible_keys | key         | key_len | ref                | rows | Extra       |
+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | category | index | PRIMARY       | name        | 452     | NULL               |  103 | Using index |
|  1 | SIMPLE      | item     | ref   | category_id   | category_id | 3       | dbname.category.id |    5 | Using index |
+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+

然而，如果我切换到MyISAM（使用 alter table engine = myisam ），我会得到：

Whereas, if I switch to MyISAM (with alter table engine=myisam) I get:

+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+
|  1 | SIMPLE      | item     | ALL    | category_id   | NULL    | NULL    | NULL                    | 1003 |       |
|  1 | SIMPLE      | category | eq_ref | PRIMARY       | PRIMARY | 3       | dbname.item.category_id |    1 |       |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+

我的问题是，为什么处理索引的方式存在差异？

My question is, why this difference in the way indexes are handled?

推荐答案

在InnoDB中，任何二级索引内部都包含主键表的列。因此列（名称）上的索引 name 隐含在列（name，id）上。

In InnoDB, any secondary index internally contains the primary key column of the table. So the index name on column (name) is implicitly on columns (name, id).

这意味着EXPLAIN显示您对类别表作为索引扫描（这在类型列中显示为索引）。通过扫描索引，它还可以访问id列，用于在第二个表item中查找行。

This means that EXPLAIN shows your access to the category table as an "index-scan" (this is shown in the type column as "index"). By scanning the index, it also has access to the id column, which it uses to look up rows in the second table, item.

然后它还利用了（category_id）上的项索引，它实际上是（category_id，id），只需读取索引就可以为select-list获取item.id.根本不需要阅读该表（这在 Extra 列中显示为Using index）。

Then it also takes advantage of the item index on (category_id) which is really (category_id, id), and it is able to fetch item.id for your select-list simply by reading the index. No need to read the table at all (this is shown in the Extra column as "Using index").

MyISAM不存储以这种方式使用辅助键的主键，因此无法获得相同的优化。对类别表的访问是类型ALL，这意味着表扫描。

MyISAM doesn't store primary keys with the secondary key in this way, so it can't get the same optimizations. The access to the category table is type "ALL" which means a table-scan.

我希望对MyISAM表项的访问是ref，因为它使用（category_id）上的索引查找行。但是如果表中的行数很少，或者自创建索引以来没有完成 ANALYZE TABLE项，优化器可能会得到偏差的结果。

I would expect the access to the MyISAM table item would be "ref" as it looks up rows using the index on (category_id). But the optimizer may get skewed results if you have very few rows in the table, or if you haven't done ANALYZE TABLE item since creating the index.

重新更新：

看起来优化器更喜欢索引扫描通过表扫描，所以它有机会在InnoDB中进行索引扫描，并将类别表放在第一位。优化器决定重新排序表，而不是按照查询中给出的顺序使用表。

It looks like the optimizer prefers an index-scan over a table-scan, so it takes the opportunity to do an index-scan in InnoDB, and puts the category table first. The optimizer decides to re-order the tables instead of using the tables in the order you gave them in your query.

在MyISAM表中，将有一个表 - 扫描它首先选择访问的表，但是通过将类别表放在第二位，它会连接到类别的PRIMARY键索引而不是item的辅助索引。优化器更喜欢查找唯一键或主键（键入eq_ref）。

In the MyISAM tables, there will be one table-scan whichever table it chooses to access first, but by putting the category table second, it joins to category's PRIMARY key index instead of item's secondary index. The optimizer prefers lookups to a unique or primary key (type "eq_ref").

这篇关于mysql在MyISAM和InnoDB之间的索引使用差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！