本文探讨了MyISAM和InnoDB存储引擎在通过主键自增ID进行排序时的不同之处。实验结果显示，MyISAM在排序时不使用索引，而是采用文件排序的方式；而InnoDB则利用主键索引进行排序，从Explain结果中可以看到其使用了PRIMARY键索引。
1、MyISAM在通过主键排序时没有用到索引，而是直接用的文件排序
mysql> alter table edu_text engine=myisam;
Query OK, 5 rows affected (0.23 sec)Records: 5 Duplicates: 0 Warnings: 0
mysql> explain select id,title,time from edu_text order by id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | edu_text | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
2、InnoDB通过主键排序
mysql> alter table edu_text engine=innodb;
Query OK, 5 rows affected (0.39 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> explain select id,title,time from edu_text order by id;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | edu_text | index | NULL | PRIMARY | 4 | NULL | 5 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
扫一扫
4545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
