在MySQL中当有多个索引时 你知道MySQL是如何选择索引的吗 ???

在码农的世界里,优美的应用体验,来源于程序员对细节的处理以及自我要求的境界,年轻人也是忙忙碌碌的码农中一员,每天、每周,都会留下一些脚印,就是这些创作的内容,有一种执着,就是不知为什么,如果你迷茫,不妨来瞅瞅码农的轨迹。


有一种业务场景就是 不断地删除历史数据和新增数据的,你会发现这个过程非常慢,你知道这是怎么回事吗 ?

在这里插入图片描述

1 索引普通查询

MySQL 中一张表可以支持多个索引,在 SQL 语句也并不主动指定使用的索引,具体使用哪个索引是由 MySQL 自己来选择的。

在这里插入图片描述

如下图所示,在这里我们有一张490多万数据的表

在这里插入图片描述

其中 user_id 添加的普通索引,如下图所示
在这里插入图片描述

然后当我执行查询语句

select * from question_extracting where user_id BETWEEN 670 AND 990

如下图所示
在这里插入图片描述
毫无疑问在执行上述这个查询时,user_id 上有索引,肯定是走的这个索引查询。

2 索引选错 查询耗时

如下图所示 ,在事务A中开启这个查询,然后同时事务B中删除数据,再插入数据,事务B中再开启同样的查询。

在这里插入图片描述
这两个事务的操作结果是,事务A中会开启user_id索引查询,而事务B中的查询就不会再使用user_id索引查询。

这是因为 MySql 选错了索引 。

在MySql中,优化器是负责来选择索引的,来决定使用哪个索引,从而找到一个最优的执行方案。

在执行过程中,扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少,反之就会多,体现的就是操作缓慢。

在上述两个事务的查询中,优化器需要考虑

  • 使用索引 user_id,每次从索引 user_id上拿到一个值,然后回到主键索引上查出整行数
  • 选择扫描 如100 万行(基数),是直接在主键索引上扫描的,没有额外的代价

优化器会估算这两个选择的代价,在这个查询中,优化器认为直接扫描主键索引更快,所以就出现了这个情况 ,原因就是没有准确的估算查询时间,或者说是没有准确估算扫描主键行数。

3 无法正确估算行数原因

一个索引上不同的值的个数,可称之为“基数“,可以使用 show index 方法,看到一个索引的基数,如下图所示我们看到这里 user_id的基数
在这里插入图片描述
在这里有两个值 M与N ,在 MySQL 中,有两种存储索引统计的方式:

  • 参数 innodb_stats_persistent 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10
  • 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16

然后MySQL 在获取如上图所示的索引的基数,是InnoDB 引擎默认会选择 N 个数据页,统计这些页面上的不同值,得到一个 平均值,然后乘以这个索引的页面数,就得到了这个索引的基数(有一定的误差)。

也就是MySQL没有正确估算这个基数而导致的索引选错。

4 解决方法

在业务开发中,可以通过force index 显示的指定查询索引

# 未指定 
EXPLAIN select * from question_extracting where user_id BETWEEN 670 AND 990

# 显示指定
EXPLAIN select * from question_extracting force index(user_id) where user_id BETWEEN 670 AND 990

可通过 Explain 关键字来对比扫描索引基数,如下图所示,当两者的 rows 有较大差别时,可以考虑选择显示指定索引查询。(rows 这个字段表示的是预计扫描行数)

在这里插入图片描述


完毕

不局限于思维,不局限语言限制,才是编程的最高境界。

以小编的性格,肯定是要录制一套视频的,随后会上传

有兴趣 你可以关注一下 西瓜视频 — 早起的年轻人

在这里插入图片描述

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页