【原创】MyISAM和InnoDB的order by的区别

这里有2个表，字段是相同的，只是存储引擎不同而已：

 

CREATE TABLE `InnoDB_caipiao` (

  `id` int(10) NOT NULL,

  `result` varchar(30) CHARACTER SET latin1 NOT NULL,

  `date` varchar(20) CHARACTER SET latin1 NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

CREATE TABLE `MyISAM_caipiao` (

  `id` int(10) NOT NULL,

  `result` varchar(30) CHARACTER SET latin1 NOT NULL,

  `date` varchar(20) CHARACTER SET latin1 NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

 

我们分别对这两个表执行相同的SQL语句：

        EXPLAIN SELECT * FROM table_name ORDER BY id LIMIT 0 , 100;

 

得到的结果分别是：

InnoDB_caipiao表

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

|  1 | SIMPLE      | caipiao | index | NULL          | PRIMARY | 4       | NULL |  100 |       |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

 

MyISAM_caipiao表

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

|  1 | SIMPLE      | caipiao | ALL| NULL          | NULL | NULL        | NULL |  1507 |   Using filesort    |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

 

下面我们来分析一下order by在MyISAM和InnoDB中的区别。

1）MyISAM：

        MyISAM是普通索引，只是在索引里存储了主键值和rowid。id虽然是主键，并且主键的索引也是有顺序的，但是在获取每行所有数据的时候，会根据索引里的rowid随机访问读取数据的。而mysql的优化器对此进行了优化，选择直接读取原表再排序的方法，没有使用主键索引。

修改SQL语句：

        EXPLAIN SELECT id FROM table_name ORDER BY id LIMIT 0 , 100;

我们就会看到不同的结果：

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

|  1 | SIMPLE      | caipiao | index| NULL          | PRIMARY| 4        | NULL |  1507 |   Using index   |

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+

 

2）InnoDB：

        InnoDB是聚簇索引，基于主键有序，而且索引里存储了每行所有的数据（包括MVCC会使用的事务id）。所以直接扫描索引即可。

来自为知笔记(Wiz)