mysql 查询语句中的 order by 对索引的影响数据库sql查询优化index

Mysql从5.0以后，索引的使用就相当智能了，甚至还支持索引聚合（一次查询使用多条索引），但昨天一次业务查询中，一条where子句(例如 where user_id=1000)明明可以使用联合索引，把扫描行限制在数千范围内的select语句，竟然使用了全主键索引扫描，导致花费将近40s秒才返回结果集。更神奇的是，该where子句只有特定的数据才会导致误用主键，大部分数据（比如 user_id = 18 或其他值）都可以在几百毫秒内完成。
经过explain的初步判断，应该是order by id limit x子句影响了索引的选择。
这里把这个问题复现一下。
建立数据表
首先准备一份不多不少的数据：足以让mysql对索引做出优化，又不至于查询很慢。从Kaggle找到一份现成的图书数据Good read books，创建一张book表，并导入文件"books.csv"。

CREATE TABLE `NewTable` ( `bookID`int(11) NOT NULL AUTO_INCREMENT , `title`varchar(256) NULL DEFAULT NULL , `authors`varchar(128) NULL DEFAULT NULL , `average_rating`decimal(10,2) NULL DEFAULT NULL , `isbn13`varchar(20) NULL DEFAULT NULL , `language_code`varchar(10) NULL DEFAULT NULL , `num_pages`int(11) NULL DEFAULT NULL , `ratings_count`int(11) NULL DEFAULT NULL , `text_reviews_count`int(11) NULL DEFAULT NULL , `publication_date`date NULL DEFAULT NULL , `publisher`varchar(256) NULL DEFAULT NULL , `create_time`datetime NULL DEFAULT NULL , `update_time`datetime NULL DEFAULT NULL , `count`int(11) NULL DEFAULT NULL , PRIMARY KEY (`bookID`), INDEX `idx_book_title` (`title`) USING BTREE , INDEX `idx_book_author` (`authors`) USING BTREE , INDEX `idx_book_avg_rating` (`average_rating`) USING BTREE , INDEX `idx_book_isbn` (`isbn13`) USING BTREE , INDEX `idx_book_num_pages` (`num_pages`) USING BTREE , INDEX `idx_book_lan_code_pub_create` (`language_code`, `publisher`, `create_time`) USING BTREE );

检查数据
总数 select count(*) from book可以看到全表数据约7500条：

文章图片

数据记录数据表前几条大致是如下的样子：

文章图片

索引其中有一条 idx_book_lan_code_pub_create的联合索引，是为测试语句准备的，它首列language_code区分度不是很高，而且很不平衡，正好适合测试。

文章图片

用show index查看一下索引统计信息的话:

文章图片

可以看到它的基数只有22
测试索引使用
简单首列查询

select * from book where language_code='en-US'

文章图片

很简单地使用了联合索引第一列，扫描919行即得到全部结果（也是919行）
加order by

select * from book where language_code='en-US' order by bookID

文章图片

显然，仅仅order by并不会影响索引的使用，但在Extra信息中，出现了Using filesort，结果集在内存中排序，这通常是影响性能的提示，不过对于900多个结果来说，也不算事儿。
加limit

select * from book where language_code='en-US' order by bookIDlimit 5

文章图片

戏剧性的结果出现了：mysql选择了PRIMARY，也就是主键索引，而抛弃了where子句适用的联合索引，Extra字段中出现Using where，意味着在内存中做筛选，同时Using filesort消失了。
此时mysql优化器应该是看到了：总共需要才返回5条数据，联合索引的扫描数可能不低，那还不如干脆用主键，按顺序依次取数据，取出来再看是否满足where子句要求，满足的就留下，不满足的放弃（就是所谓Using where），果然才取了40条（rows=40）就找到了全部记录。
调整limit数量那么是不是只要加limit，就会导致使用主键索引呢？也不会。这次我们把限制调整到50：

select * from book where language_code='en-US' order by bookIDlimit 50

文章图片

果然又退回到使用联合索引，并且Using filesort了。
经过测试，临界点在35，小于这个数，才使用主键索引——说明优化器内部有个估算：先通过where子句获得结果集再排序，或者从排序结果逐个检索结果集，那种更值得？当然这种估算未必准确，比如这里limit 50的情况，如果我们强制使用PRIMARY索引，其实效果更好：

select * from book where language_code='en-US' order by bookIDlimit 50

文章图片

可以看到只需要50行扫描，而且真正执行的话，使用主键索引只需3ms，而缺省的联合索引需要5ms。
调整language_code值如果把language_code从总数第二的'en-US'，换成最多的'eng'会怎样呢？
索引切换的临界点会变为145，说明mysql优化器会先从联合索引先大致确认给定数据的检索量，再决定用哪种索引，这也解释了为什么在实际业务环境中，user_id的不同取值，会影响查询计划。
通常优化器的选择是正确的，但也会出现文章一开始提到的误判，这有可能是索引基数（cardinality）更新不及时造成的。
解决方案
强制指定索引使用force index强制指定，无论如何limit都不会用主键索引了：

select * from book force index(idx_book_lan_code_pub_create) where language_code='en-US' order by bookID limit 5

文章图片

优点是可靠：指定的索引一定能用上；缺点是死板：将来SQL语句变了，可能影响优化，指定的索引也不能改名，而且用了数据库方言
改用非主键字段排序这里用bookID排序，基本相当于按插入顺序，那么跟create_time是一致的，改成order by create_time，肯定也不会用主键索引了：

select * from book where language_code='en-US' order by create_time limit 5

文章图片

这个方法的优点很明显：还是普通SQL，没有数据库依赖性，将来SQL的改变，不影响优化器；唯一的缺点是：对于那些真的用主键索引更快的数据，就享受不到优化的好处了。
尝试优化索引基数 【mysql 查询语句中的 order by 对索引的影响】前面说过，索引基数不是实时更新的，可能导致优化器误判，那么可以尝试用analyze命令重新计算索引统计信息，看看是否会影响优化器的选择：