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

红太狼 2023-10-07 15:51 2阅读 0赞

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

*  [优美的音乐节奏带你浏览这个效果的编码过程][Link 1]
 *  [坚持每一天，是每个有理想青年的追求][Link 2]
 *  [追寻年轻人的脚步，也许你的答案就在这里][Link 3]
 *  [如果你迷茫 不妨来瞅瞅这里][Link 4]

--------------------

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center]

##### 1 索引普通查询 #####

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 1]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 2]

其中 user\_id 添加的普通索引，如下图所示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 3]

然后当我执行查询语句

select * from question_extracting where user_id BETWEEN 670 AND 990

如下图所示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]  
毫无疑问在执行上述这个查询时，user\_id 上有索引，肯定是走的这个索引查询。

##### 2 索引选错 查询耗时 #####

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 4]  
这两个事务的操作结果是，事务A中会开启user\_id索引查询，而事务B中的查询就不会再使用user\_id索引查询。

这是因为 MySql 选错了索引 。

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

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

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

*  使用索引 user\_id，每次从索引 user\_id上拿到一个值，然后回到主键索引上查出整行数
 *  选择扫描 如100 万行（基数），是直接在主键索引上扫描的，没有额外的代价

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

##### 3 无法正确估算行数原因 #####

一个索引上不同的值的个数，可称之为“基数“，可以使用 show index 方法，看到一个索引的基数，如下图所示我们看到这里 user\_id的基数  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]  
在这里有两个值 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 这个字段表示的是预计扫描行数）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]

--------------------

完毕

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

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

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

![在这里插入图片描述][20201031094959816.gif_pic_center]

[Link 1]: https://www.bilibili.com/video/BV1QX4y1G7Sh/
[Link 2]: https://www.zhihu.com/zvideo/1348960604003180544
[Link 3]: https://www.ixigua.com/6933784858019758595
[Link 4]: https://biglead.blog.csdn.net/article/details/93532582
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20210311122806189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20210311124928415.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20210311175945582.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20210311180055790.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210311180304574.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20210311180648930.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210311181529753.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210311183300229.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[Link 5]: https://www.toutiao.com/c/user/token/MS4wLjABAAAAYMrKikomuQJ4d-cPaeBqtAK2cQY697Pv9xIyyDhtwIM/
[20201031094959816.gif_pic_center]: https://img-blog.csdnimg.cn/20201031094959816.gif#pic_center