MyISAM 和 InnoDB 中索引使用的区别
来源:互联网 发布:数据库脚本怎么写 编辑:程序博客网 时间:2024/05/23 19:02
两个小型表 item、category:
category 插入 100 条数据,item 插入 1000 条。
当表的存储引擎为 InnoDB 执行
结果:
然后将表的存储引擎切换到 MyISAM 时(使用 alter table engine=myisam)还是执行
结果:
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")。
原文链接:http://stackoverflow.com/questions/16024226/mysql-difference-in-index-usage-between-myisam-and-innodb。
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")。
原文链接:http://stackoverflow.com/questions/16024226/mysql-difference-in-index-usage-between-myisam-and-innodb。
0 0
- MyISAM 和 InnoDB 中索引使用的区别
- MyISAM和Innodb索引的区别(转)
- mysql中MyISAM和InnoDB的区别
- mysql中MyISAM和InnoDB的区别
- myisam和innodb中count(*)的区别
- MySQL中MyISAM和InnoDB的区别
- mysql中myisam和innodb的区别
- myisam和innodb中count(*)的区别
- sql中InnoDB和MyISAM的区别
- Mysql 中MyISAM 和Innodb 的区别
- mysql中innodb和myisam的区别
- myisam和innodb中count(*)的区别
- mysql中innodb和myisam的区别
- mysql中innodb和myisam的区别
- MySQL中MyISAM和InnoDB的区别
- Mysql innodb引擎和myisam引擎 使用索引区别
- Mysql innodb引擎和myisam引擎使用索引区别
- MySQL中myisam和innodb的主键索引有什么区别?
- 补贴行动背后,火炬租房如何实现“去中介化”?
- sed基本用法简介
- 3.1 坐标系简介
- javaSE学习笔记之几个关键字this、super、final、static
- 多线程(1)
- MyISAM 和 InnoDB 中索引使用的区别
- Myeclipse console日志最开始的日志无法查看的解决办法
- 【三门问题】自写过程中遇到的种种记录,求个大大解释下期中问题的原理哇,拜谢
- google推出的SwipeRefreshLayout下拉刷新用法
- 为什么匿名内部类中引用的局部变量和参数需要final而成员字段不用?
- 联网查看图片的Demo
- 写在准备找工作的日子里
- 《C++ 沉思录》阅读笔记——代理类
- Opengl编程遇到的问题以及解决