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

有读者可能会一脸懵逼？

啥是索引潜水？

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

这个名字还真不是我起的，今天要讲的知识点就叫索引潜水（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);

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

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

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

explain select * from user

where age in (1,2,3,4,5,6,7,8,9,10);

一下子减少到了30行，可是实际行数是多少呢？

实际是310行，预估扫描行数是30行，真是错到姥姥家了。

MySQL咋回事啊，到底还能不能预估？

不能预估的话，换其他人！

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

跟这个词语相关的，还有一个配置参数 eq_range_index_dive_limit。

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

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

show variables like '%eq_range_index_dive_limit%';

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

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预估扫描行数更准确，可以选择更合适的索引。

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

文章持续更新，可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。

MySQL查询性能优化七种武器之索引潜水的更多相关文章

1. MySQL查询性能优化(精)

   MySQL查询性能优化 MySQL查询性能的优化涉及多个方面,其中包括库表结构.建立合理的索引.设计合理的查询.库表结构包括如何设计表之间的关联.表字段的数据类型等.这需要依据具体的场景进行设计.如下 ...

2. 170727、MySQL查询性能优化

   MySQL查询性能优化 MySQL查询性能的优化涉及多个方面,其中包括库表结构.建立合理的索引.设计合理的查询.库表结构包括如何设计表之间的关联.表字段的数据类型等.这需要依据具体的场景进行设计.如下 ...

3. 到底该不该使用存储过程 MySQL查询性能优化一则

   到底该不该使用存储过程   看到<阿里巴巴java编码规范>有这样一条 关于这条规范,我说说我个人的看法 用不用存储过程要视所使用的数据库和业务场景而定的,不能因为阿里巴巴的技术牛逼,就视 ...

4. MySQL 数据库性能优化之索引优化

   接着上一篇 MySQL 数据库性能优化之表结构,这是 MySQL数据库性能优化专题 系列的第三篇文章:MySQL 数据库性能优化之索引优化 大家都知道索引对于数据访问的性能有非常关键的作用,都知道索引 ...

5. SQL Server查询性能优化——覆盖索引（二）

   在SQL Server 查询性能优化——覆盖索引(一)中讲了覆盖索引的一些理论. 本文将具体讲一下使用不同索引对查询性能的影响. 下面通过实例,来查看不同的索引结构,如聚集索引.非聚集索引.组合索引等 ...

6. SQL Server 查询性能优化——覆盖索引

   覆盖索引又可以称为索引覆盖. 解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖. 解释二: 索引是高效找到行的一个方法,当能通过检索索引 ...

7. MySQL优化技巧之五（mysql查询性能优化)

   对于高性能数据库操作,只靠设计最优的库表结构.建立最好的索引是不够的,还需要合理的设计查询.如果查询写得很糟糕,即使库表结构再合理.索引再合适,也无法实现高性能.查询优化.索引优化.库表结构优化需要齐 ...

8. MySQL查询性能优化---高性能(二)

   转载地址:https://segmentfault.com/a/1190000011330649 避免向数据库请求不需要的数据 在访问数据库时,应该只请求需要的行和列.请求多余的行和列会消耗MySql ...

9. 《高性能MySQL》之MySQL查询性能优化

   为什么查询会慢? 响应时间过长.如果把查询看做是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定的时间.如果要优化查询,实际上优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数, ...

10. mysql查询性能优化

    mysql查询过程: 客户端发送查询请求. 服务器检查查询缓存,如果命中缓存,则返回结果,否则,继续执行. 服务器进行sql解析,预处理,再由优化器生成执行计划. Mysql调用存储引擎API执行优化 ...

随机推荐

1. 一个login

   login 1.获取提交表单,保存到变量中.2.判断用户密码是否正确,利用Model类.3.验证用户是否激活.3.判断用户是否记住登录状态,是的话,将其用cookie和session分别保存.没有的话 ...

2. linux第8天 connect强化

   今天没有系统学习什么新知识,就是把以前学到的知识复习了一下,其中有几点值得注意 connect(fd, (struct sockaddr *)addr, sizeof(struct sockaddr_ ...

3. Adobe Edge Animate--关于全局变量和全局方法的定义

   Adobe Edge Animate--关于全局变量和全局方法的定义 版权声明: 本文版权属于 北京联友天下科技发展有限公司. 转载的时候请注明版权和原文地址. BY:sonicxsxs 前文中有关音 ...

4. Qt5：不规则按钮的实现---通过贴图实现

   在应用开发中,有时候为了美观会在UI界面中增加不规则的按钮 现在我们就来看看Qt中是怎么实现不规则按钮的 /////////////////////////////////////////////// ...

5. 5. React 组件的协同使用 组件嵌套和Mixin

           组件是React的核心,构建大型项目时多个组件之间需要进行协同使用.可以从横向和纵向两个角度来实现组件的协同使用,纵向的协同使用就是组件嵌套,横向的协同使用就是Mixin(抽取公共方法 ...

6. C++ 标准库之 iomanip 、操作符 ios::fixed 以及 setprecision 使用的惨痛教训经验总结

   本菜鸡自从退役之后就再也没怎么敲过 C++ 代码,在 C++ 语言下,求解关于浮点数类型的问题时,之前有碰到类似的情况,但是似乎都没有卡这块的数据,基本上用一个 setprecision 函数保留几位 ...

7. C# ffmpeg工具将视频转为SWF格式

   1.下载ffmpeg工具 using System; using System.Collections; using System.Configuration; using System.Data; ...

8. ubuntu下搭建node server的几个坑

   [ubuntu下搭建node server的几个坑] 1.环境变量 process.env.PORT需要使用 export PORT=80设置 windows下是set PORT=80 2.命令连结 ...

9. APP流氓大法之apk 静默安装

   老大要我弄个自动更新,要用到静默安装,网上找到了些大拿的代码,我拿去改吧改吧,先贴出来: /** * 软件静默安装 * @param apkAbsolutePath apk文件所在路径 * @retu ...

10. BZOJ1934：[SHOI2007]善意的投票 & BZOJ2768：[JLOI2010]冠军调查——题解

    https://www.lydsy.com/JudgeOnline/problem.php?id=1934 https://www.lydsy.com/JudgeOnline/problem.php? ...