MyISAM和InnoDB中索引使用的区别

页面导航:首页 > 数据库 > DB2 > MyISAM和InnoDB中索引使用的区别

MyISAM和InnoDB中索引使用的区别

来源: 作者: 时间:2016-01-23 11:15 【

两个小型表 item、category:
两个小型表 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

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")。
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<