本文转载自：https://segmentfault.com/a/1190000014205981
要使用多列索引(联合索引)，需要满足最左原则，where条件必须和索引的顺序一致，如果只用到单列则必须是最左列。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。也需要满足索引的最左前缀要求（例外：前导列为常量的时候）。--摘自《高性能MySQL》
建如下表测试索引排序(版本5.7.17)，联合索引：test_realname_sex_age_index，单列索引：sex_index、age_index，（一次查询只能利用一个索引）
CREATETABLE`test`(`id`int(11)NOTNULLAUTO_INCREMENT,`realname`varchar(10)NOTNULLDEFAULT'',`sex`tinyint(4)NOTNULLDEFAULT'0',`age`tinyint(4)NOTNULLDEFAULT'0',`job`varchar(10)NOTNULLDEFAULT'', PRIMARYKEY(`id`),KEY`test_realname_sex_age_index`(`realname`,`sex`,`age`),KEY`sex_index`(`sex`),KEY`age_index`(`age`))ENGINE=InnoDBDEFAULTCHARSET=utf8
联合索引测试
使用两种不同的排序方向排序
能利用索引排序
EXPLAIN SELECT * FROM test WHERE realname = 'wen' ORDER BY sex DESC ,age DESC;
不能利用联合索引排序，因为sex和age的排序方向不一致
EXPLAIN SELECT * FROM test WHERE realname = 'wen' ORDER BY sex DESC ,age ASC ;
用了一个不在索引中的列
不能，因为job列不在联合索引内
EXPLAIN SELECT * FROM test WHERE realname = 'wen' ORDER BY sex,job;
where order by中的列无法组合成索引的最左前缀
不能，realname和age不满足最左原则
EXPLAIN SELECT * FROM test WHERE realname = 'wen' ORDER BY age;
能
EXPLAIN SELECT * FROM test WHERE realname = 'wen' ORDER BY sex;
查询在索引第一列上是范围条件
不能
EXPLAIN SELECT * FROM test WHERE realname > 'wen' ORDER BY sex,age;
EXPLAIN SELECT * FROM test WHERE realname BETWEEN 'wen1' AND 'wen2' ORDER BY sex,age;
索引上有多个等于条件(in)
不能
EXPLAIN SELECT * FROM test WHERE realname = 'wen' AND sex IN (1,2) ORDER BY age;
前导列不为常量，并且使用范围条件
不能
EXPLAIN SELECT * FROM test WHERE realname > 'qqq' ORDER BY sex;
能，满足最左原则
EXPLAIN SELECT * FROM test WHERE realname = 'qqq' ORDER BY sex;
选择合适的索引顺序
一般来说：将选择性最高的列放到索引的最前列。计算方法
SELECT count(DISTINCT realname)/COUNT(*) realname_selectivity,count(DISTINCT sex)/COUNT(*) sex_selectivity,count(DISTINCT age)/COUNT(*) age_selectivity,count(*) total FROM test;
使用单索引排序的例子
where条件和排序的列不一致
不能使用索引排序
EXPLAIN SELECT * FROM test WHERE sex = 1 ORDER BY age;
使用了范围
EXPLAIN SELECT * FROM test WHERE age > 17 ORDER BY age;
没有where条件直接排序
主键可以排序
EXPLAIN SELECT * FROM test ORDER BY id;
不能
EXPLAIN SELECT * FROM test ORDER BY sex;
网友评论