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 bookID  limit 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 bookID  limit 50


果然又退回到应用联结索引，并且Using filesort了。

通过测试，临界点在35，小于这个数，才应用主键索引——阐明优化器外部有个估算：先通过where子句取得后果集再排序，或者从排序后果一一检索后果集，那种更值得？当然这种估算未必精确，比方这里limit 50的状况，如果咱们强制应用PRIMARY索引，其实成果更好：

select * from book where language_code='en-US' order by bookID  limit 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的扭转，不影响优化器；惟一的毛病是：对于那些真的用主键索引更快的数据，就享受不到优化的益处了。

尝试优化索引基数

后面说过，索引基数不是实时更新的，可能导致优化器误判，那么能够尝试用analyze命令从新计算索引统计信息，看看是否会影响优化器的抉择：

analyze table book