关于数据库:mysql-查询语句中的-order-by-对索引的影响

6次阅读

共计 3123 个字符,预计需要花费 8 分钟才能阅读完成。

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
正文完
 0