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
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。