|
首先你要理解,innodb的主键索引都是聚簇索引,它的辅助索引里面都包含有表的主键,而且主键都在辅助索引的最后一列.而myisam的索引,都是普通索引,存储的是列的值,还有列在原表中的地址值---不存储主键值. 好了,下面我们开始: 先创建两个表:一个是innodb引擎的,一个是myisam引擎的 CREATE TABLE `innodb` ( `id` int(10) UNSIGNED NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `myisam` (`id` int(10) UNSIGNED NOT NULL, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=myisam DEFAULT CHARSET=latin1; mysql> EXPLAIN SELECT * FROM innodb ORDER BY id /G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: pktest type: INDEX------ innodb是聚簇索引,所以mysql直接扫描primary key并取出所有的字段.需要用到primary key,此处直接扫描索引,而不需要查找表,比ALL更快 possible_keys: NULL KEY: PRIMARY ----由此可见,key的内容并不一定在possible keys中 key_len: 4 ref: NULL rows: 6 Extra:---这里为何不显示为using index呢? 1 row IN SET (0.00 sec) mysql> EXPLAIN SELECT * FROM myisam ORDER BY id /G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: myisam type: ALL----- myisam是普通的索引,只是在索引中存储了行的rowid,此索引的id列应该是有序的.但是通过id列去定位到原表的rowid,然后由rowid查找表的所有字段,会带来大量的随机读.此时优化器选择了直接读取原表排序的方法,没有利用任何索引. possible_keys: NULL KEY: NULL key_len: NULL ref: NULL rows: 6 Extra: USING filesort---- 有filesort出现,全表扫描以后,再排序 1 row IN SET (0.00 sec) mysql> EXPLAIN SELECT id FROM innodb WHERE a=3 /G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: innodb type: ref possible_keys: a KEY: a key_len: 5 ref: const rows: 1 Extra: USING WHERE; USING INDEX---- 因为在这个表上有索引a,它包含了a列和id列,而且a列在前,id列在后,所以优化器只需要直接查询该索引即可.所以会出现using index,因为直接用到了索引a,与原表无关 1 row IN SET (0.00 sec) mysql> EXPLAIN SELECT id FROM myisam WHERE a=3 /G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: myisam type: ref possible_keys: a KEY: a key_len: 5 ref: const rows: 1 Extra: USING WHERE---- 对myisam引擎,它依然用到了索引a,但是只是通过索引a去查找到行在原表中的rowid,然后根据rowid得出id列的值.所以这里没有useing index.归根结底是因为myisam的索引不会存储主键,所以不得不进行二次扫描 1 row IN SET (0.00 sec) mysql> EXPLAIN SELECT id FROM innodb WHERE a=3 ORDER BY id /G *************************** 1. row *************************** id: 1 select_type: SIMPLE TABLE: innodb type: ref possible_keys: a KEY: a key_len: 5 ref: const rows: 1 Extra: USING WHERE; USING INDEX; USING filesort---- 这 里,同样,在索引a中,含有(a,id)两列,只需要查询索引就ok了.根据a=3得到需要的行,然后按照id排序.所以此处不应该出现 filesort,因为对于a,id列的排序,直接再查找一次primary key即可得出.所以这里的key列处,应该是key: a, primary. 这样效率会更高.据说这是mysql 优化器的一个bug.以后会修复. (责任编辑：IT) |