ORDER BY 可以匹配索引是限制的
本地做了下测试,发现跟单纯的读文档的理解差别还是挺大的。
结论一:ORDER BY的字段是否走索引跟表的数据量有关系,表的数据量比较小的时候会走全表扫描,数据量比较大的时候才会走索引,这可能也是MySQL基于代价的结果。
ORDER BY可以走索引的情况:
-- Order by
explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`;
-- Order by的排序顺序必须一致
explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float` asc,`real` asc;
-- Order by不一定完全匹配索引,但是where中必须是常量
explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100 ORDER BY `real` asc;
-- Order by是组合索引,where中必须是常量
explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100 ORDER BY `float` asc,`real` asc;
-- Order by是组合索引,where中必须是常量【不能走索引】
explain SELECT * FROM `mytest`.`table_rm002` where `real` = 100 ORDER BY `float` asc,`real` asc;
-- 下面三种待验证
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2
测试案例:
构建10条数据的表
看看10条数据的ORDER BY索引会怎么走,竟然没有走索引,而是使用了using filesort
测试50W条数据情况
看看50W条数据的ORDER BY索引会怎么走,发现走了索引idx_cmplx,奇怪吧,反正我是有点颠覆了价值观。
附上测试中使用的SQL语句
-- 查看Query的执行计划
explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`;
-- 重建表索引
analyze table `mytest`.`table_rm002`;
-- 删除数据
delete from `mytest`.`table_rm002`;
-- 构造数据
insert into `mytest`.`table_rm002` select * from `mytest`.`table_rm001` limit 500000;
-- 表的创建语句
CREATE TABLE `table_rm002` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`varchar` varchar(32) DEFAULT NULL,
`tinyint` tinyint(4) DEFAULT NULL,
`smallint` smallint(6) DEFAULT NULL,
`mediumint` mediumint(9) DEFAULT NULL,
`bigint` bigint(20) DEFAULT NULL,
`integer` int(11) DEFAULT NULL,
`float` float DEFAULT NULL,
`real` double DEFAULT NULL,
`decimal` decimal(10,0) DEFAULT NULL,
`boolean` tinyint(1) DEFAULT NULL,
`date` date DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`time` time DEFAULT NULL,
`year` year(4) DEFAULT NULL,
`text` text,
`blob` blob,
PRIMARY KEY (`id`),
KEY `idx_float` (`float`),
KEY `idx_varchar` (`varchar`),
KEY `idx_cmplx` (`float`,`real`)
) ENGINE=MyISAM AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT