MySQL利用索引排序实践

要使用多列索引(联合索引)，需要满足最左原则，where条件必须和索引的顺序一致，如果只用到单列则必须是最左列。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。也需要满足索引的最左前缀要求（例外：前导列为常量的时候）。--摘自《高性能MySQL》

建如下表测试索引排序(版本5.7.17)，联合索引：test_realname_sex_age_index，单列索引：sex_index、age_index，（一次查询只能利用一个索引）

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `realname` varchar(10) NOT NULL DEFAULT '',
  `sex` tinyint(4) NOT NULL DEFAULT '0',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  `job` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `test_realname_sex_age_index` (`realname`,`sex`,`age`),
  KEY `sex_index` (`sex`),
  KEY `age_index` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=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;

1. 查询在索引第一列上是范围条件

• 不能

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;