ORDER BY 可以匹配索引是限制的
具体可以参考下http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html
本地做了下测试,发现跟单纯的读文档的理解差别还是挺大的。
知识点一:ORDER BY的字段是否走索引跟表的数据量有关系,表的数据量比较小的时候会走全表扫描,数据量比较大的时候才会走索引,这可能也是MySQL基于代价的结果。
知识点二:WHERE KEY_PART1 > CONST ORDER BY KEY_PART1,KEY_PART2也是可以走索引的,但是官方没有列举这么仔细。
知识点三:MySQL两种排序方式一种是需要回表的,另外一种不需要回表。
一般情况包含大字段的时候会需要回表,否则直接都拿出来了对关键字排序即可。
知识点四:这里filesort并不是说通过磁盘文件进行排序,仅仅告诉我们进行了一个排序操作。
知识点五:如果排序字段同时存在两个表中,或者join完之后排序,则需要在临时表中进行,一般如果order by字段在驱动表上则先对驱动表进行排序再做join也是顺序的。
知识点六:如果无法避免排序,该如何优化。
1.增大max_length_for_sort_data 如果所有字段的最大长度小于这个参数值的时候,MySQL会选择第二种排序算法,否则选择第一种。
2.去掉不必要的字段,如果内存不够但是增大max_length_for_sort_data,则需要排序的数据会分成很多段进行,效率比较低,去掉不必要的字段来适应max_length_for_sort_data。
3.增大sort_buffer_size参数设置:
增大这个参数并不是为了让MySQL可以选择第二种排序算法,而是为了让数据减少排序的分段。
ORDER BY可以走索引的情况:
1. -- Order by
2. explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`;
3.
4. -- Order by的排序顺序必须一致
5. explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float` asc,`real` asc;
6.
7. -- Order by不一定完全匹配索引,但是where中必须是常量
8. explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100 ORDER BY `real` asc;
9.
10. -- Order by是组合索引,where中必须是常量
11. explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100 ORDER BY `float` asc,`real` asc;
12.
13. -- Order by是组合索引,where中必须是常量【不能走索引】
14. explain SELECT * FROM `mytest`.`table_rm002` where `real` = 100 ORDER BY `float` asc,`real` asc;
15.
16. -- 下面三种待验证
17. SELECT * FROM
18. WHERE
19. ORDER BY key_part1 ASC;
20.
21. SELECT * FROM
22. WHERE
23. ORDER BY key_part1 DESC;
24.
25. SELECT * FROM
26. WHERE key_part1 = constant1 AND
27. ORDER BY
28.
29. 测试了下官网给出的三个模板SQL有点令人误解:
30. 其实这样的格式也是可以使用索引的
31. SELECT * FROM
32. WHERE
33. ORDER BY
测试案例:
构建10条数据的表
看看10条数据的ORDER BY索引会怎么走,竟然没有走索引,而是使用了using filesort
测试50W条数据情况
看看50W条数据的ORDER BY索引会怎么走,发现走了索引idx_cmplx,奇怪吧,反正我是有点颠覆了价值观。
看吧,虽然这里的key_part1是< ,order by是key_part1和key_part2但是仍然走了索引
1. mysql> explain SELECT * FROM `mytest`.`table_rm002` where `float` < 100 order by `float`,`real` \G;
2. *************************** 1. row ***************************
3. id: 1
4. select_type: SIMPLE
5. table: table_rm002
6. type: range
7. possible_keys: idx_cmplx,idx_float
8. key: idx_cmplx
9. key_len: 5
10. NULL
11. rows: 59
12. where
13. 1 row in set
附上测试中使用的SQL语句
1. -- 查看Query的执行计划
2. explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`;
3.
4. -- 重建表索引
5. analyze table
6.
7. -- 删除数据
8. delete from
9.
10. -- 构造数据
11. insert into `mytest`.`table_rm002` select * from
12.
13. -- 表的创建语句
14. CREATE TABLE
15. int(11) NOT NULL
16. varchar` varchar(32) DEFAULT NULL,
17. DEFAULT NULL,
18. smallint` smallint(6) DEFAULT NULL,
19. DEFAULT NULL,
20. bigint` bigint(20) DEFAULT NULL,
21. integer` int(11) DEFAULT NULL,
22. float` float DEFAULT NULL,
23. real` double DEFAULT NULL,
24. decimal` decimal(10,0) DEFAULT NULL,
25. DEFAULT NULL,
26. date` date DEFAULT NULL,
27. DEFAULT NULL,
28. time` time DEFAULT NULL,
29. year` year(4) DEFAULT NULL,
30. `text` text,
31. `blob` blob,
32. PRIMARY KEY
33. KEY `idx_float` (`float`),
34. KEY `idx_varchar` (`varchar`),
35. KEY `idx_cmplx` (`float`,`real`)
36. ) ENGINE=MyISAM AUTO_INCREMENT=3000001 DEFAULT