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=utf8CREATE TABLE `category` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`)) CHARSET=utf8
category 插入 100 条数据，item 插入 1000 条。
当表的存储引擎为 InnoDB 执行
EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;
结果：
+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+| 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)还是执行
EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;
结果：
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+| 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 | |+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+
MyISAM 的 item 使用的是全表扫描。同样的数据结果、同样的数据、同样的索引(MyISAM 和 InnoDB 的索引默认都是 B-TREE)，为什么差别就这么大呢？
来自 SchoolMessenger 的高级数据库架构师 Bill Karwin 对此做出如下解释：
在 InnoDB 中，所有二级索引内部包含表的主键列。因此这两张表的 name 列的索引(name)隐式地持有两个列：一个本字段 name 和一个主键 id。
这意味着 EXPLAIN 对于 category 表的访问的解释为一个 "index-scan"(type 为 "index" 印证了这个)。通过对索引的扫描，它也可以访问到 id 列，藉此查找第二张表 item 的相关记录。
同理，对于 item 表的 category_id 字段上的索引实际是 category_id、id，所以只需要简单读取该索引即可拿到 item.id，完全不需要去读取该表(Extra 值为 "Using index" 印证了这个说法)。
MyISAM 并不像 InnoDB 那样在二级索引中保存主键，因此它也就不能得到同样的优化。对于 category 表的访问 type 是 "ALL" 也就意味着将要进行一次全表扫描。
我期望对于 MyISAM 的 item 表的访问是 "ref"，因为它使用 category_id 列的索引来查找行。但当表中数据量比较少或者你在创建该索引后还没完成 ANALYZE TABLE item 时优化器可能会给出扭曲的结果。
Bill Karwin 追加回复：
看上去相比较表扫描优化器更喜欢一个索引扫描，因此它在 InnoDB 里做了一次索引扫描，并把 category 表放在前面。优化器放弃了我们在查询中给它的表的顺序，它对这些表进行了重新排序。
在 MyISAM 引擎下的两个表里，不管优化器选择先访问谁都要做一次表扫描，但是通过把 category 表放在第二步里，它放弃了 item 表的二级索引，连接的是 category 表的主键索引。优化器更倾向于查找一个 unique 或者 primary 的索引(type "eq_ref")。
