性能文章>MySQL查询性能优化七种武器之索引潜水>

MySQL查询性能优化七种武器之索引潜水原创

3月前
271564

有读者可能会一脸懵逼?

啥是索引潜水?

你给起的名字的吗?有没有索引蛙泳?

image1.jpeg

这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive)。

先要从一件怪事说起:

我先造点数据复现一下问题,创建一张用户表:

CREATE TABLE `user` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
 PRIMARY KEY (`id`),
 KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过一批用户年龄,查询该年龄的用户信息,并查看一下SQL执行计划:

explain select * from user 
where age in (1,2,3,4,5,6,7,8,9);

image2.png

where条件中有9个参数,重点关注一下执行计划中的预估扫描行数为279行。

到这里没什么问题,预估的非常准,实际就是279行。
image3.png

但是,问题来了,当我们在where条件中,再加一个参数,变成了10个参数,预估扫描行数本应该增加,结果却大大减少了。

explain select * from user 
where age in (1,2,3,4,5,6,7,8,9,10);

一下子减少到了30行,可是实际行数是多少呢?
image4.png
实际是310行,预估扫描行数是30行,真是错到姥姥家了。

image5.png

MySQL咋回事啊,到底还能不能预估?

不能预估的话,换其他人!

image6.GIF

大家肯定也是满脸疑惑,直到我去官网上看到了一个词语,索引潜水(Index dive)。

跟这个词语相关的,还有一个配置参数

eq_range_index_dive_limit。

MySQL5.7.3之前的版本,这个值默认是10,之后的版本,这个值默认是200。

可以使用命令查看一下这个值的大小:

show variables like '%eq_range_index_dive_limit%';

image7.png

当然,我们也可以手动修改这个值的大小:

set eq_range_index_dive_limit=200;

这个 eq_range_index_dive_limit 配置的作用就是:

当where语句in条件中参数个数小于这个值的时候,MySQL就采用索引潜水(Index dive)的方式预估扫描行数,非常准确。

当where语句in条件中参数个数大于等于这个值的时候,MySQL就采用另一种方式索引统计(Index statistics)预估扫描行数,误差较大。

MySQL为什么要这么做呢?

都用索引潜水(Index dive)的方式预估扫描行数,不好吗?

其实这是基于成本的考虑,索引潜水估算成本较高,适合小数据量。索引统计估算成本较低,适合大数据量。

一般情况下,我们的where语句的in条件的参数不会太多,适合使用索引潜水预估扫描行数。

建议还在使用MySQL5.7.3之前版本的同学们,手动修改一下索引潜水的配置参数,改成合适的数值。

如果你们项目中in条件最多有500个参数,就把配置参数改成501。

这样MySQL预估扫描行数更准确,可以选择更合适的索引。

image8.jpeg

快去检查一下你们的线上配置吧!

💥看到这里的你,如果对于我写的内容很感兴趣,有任何疑问,欢迎在下面留言📥,会第一次时间给大家解答,谢谢!

点赞收藏
分类:标签:
一灯架构

只分享有趣的技术干货

请先登录,查看6条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

技术分享 | 幽灵攻击与编译器中的消减方法介绍

技术分享 | 幽灵攻击与编译器中的消减方法介绍

Java服务异常排查定位大图

Java服务异常排查定位大图

【全网首发】不经意的两行代码把CPU使用率干到了90%+

【全网首发】不经意的两行代码把CPU使用率干到了90%+

【全网首发】Tablestore-OTSClient连接池连接无法复用分析

【全网首发】Tablestore-OTSClient连接池连接无法复用分析

如何修改 Nginx 源码实现 worker 进程隔离

如何修改 Nginx 源码实现 worker 进程隔离

【全网首发】记一次MySQL CPU被打满的SQL优化案例分析

【全网首发】记一次MySQL CPU被打满的SQL优化案例分析

4
6