首页 技术文章 数据库 Mysql索引失效怎么办?失效原因浅析
正文 评论

Mysql索引失效怎么办?失效原因浅析

本篇文章给大家记录一次Mysql索引失效,分析一下Mysql索引失效原因,希望对大家有所帮助!

Mysql索引失效怎么办?失效原因浅析

此文章包含Mysql的Where条件查询执行过程、范围查询使联合索引停止匹配、回表操作分析、常见索引失效场景、Extra分析等知识。

背景

6千万数据量的数据表出现了一个满查询,复现sql语句发现查询并没有走索引而是走全表查询,找出索引失效原因。

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>'1628442000' and start_date<'1631120399' and station_id='1809' and status='2';

Mysql索引失效怎么办?失效原因浅析

order_recipient_extend_tab 表有6千万数据,慢查询的查询字段包括 start_date、station_id、status,按照索引设计初衷会走但实际上失效的索引是:

联合索引 字段1 字段2 字段3
idx_date_station_driver start_date station_id driver_id

Where条件查询执行过程

了解Mysql怎么执行where条件查询,能更快速清晰地洞见索引失效的原因。此次慢查询中匹配度高的索引是idx_date_station_driver,分析此次慢查询中where条件查询的执行过程。

Mysql对where条件提取规则主要可以归纳为三大类:Index Key (First Key & Last Key),Index FilterTable Filter

Index Key

Index Key用于确定此次sql查询在索引树上的范围。一个范围包括起始和终止,Index First Key用于定位索引查询的起始范围,Index Last Key用于定位索引查询的终止范围

  • Index First Key

    提取规则:从索引的第一个字段开始,检查该字段在where条件中是否存在,若存在且条件是=、>=,则将对应的条件加入Index First Key之中,继续读取索引的下一个字段;若存在且条件是>,则将对应的条件加入Index First Key中,然后终止Index First Key的提取;若不存在,也终止Index First Key的提取。

  • Index Last Key

    与Index First Key正好相反,提取规则:从索引的第一个字段开始,检查其在where条件中是否存在,若存在并且条件是=、<=,则将对应条件加入到Index Last Key中,继续提取索引的下一个字段;若存在并且条件是 < ,则将条件加入到Index Last Key中,然后终止提取;若不存在,也终止Index Last Key的提取。

按照Index Key的提取规则,在此次慢查询中提取出来的Index Last Key为:start_date>'1628442000',Index Last Key为: start_date<'1631120399'。

Index First Key只是用来定位索引的起始范围,使用Index First Key条件,从索引B+树的根节点开始,使用二分搜索方法快速索引到正确的叶节点位置。Where查询过程中Index First Key只做了一次判断。

Index Last Key,用来定位索引的终止范围,因此对于起始范围之后读到的每一条索引记录,均需要判断是否已经超过了Index Last Key的范围,若超过,则当前查询结束。

Index Filter

在Index Key确定的索引范围中,并不是所有的索引记录都满足查询条件。比如Index Last Key和Index Last Key范围中,不是所有索引记录都满足 station_id = '1809'。这个时候就需要用到Index Filter了。

Index Filter,又名索引下推用于过滤索引查询范围中不满足查询条件的记录。对于索引范围中的每一条记录,均需要与Index Filter进行对比,若不满足Index Filter则直接丢弃,继续读取索引下一条记录。

Index Filter的提取规则:从索引的第一个字段开始,检查其在where条件中是否存在,若存在且条件仅为 =,则跳过第一字段继续检查索引下一字段,下一索引列采取相同的提取规则(解释:条件为=的字段已经在Index Key中过滤掉了);若存在且条件为 >=、>、<、<= 其中的几种,则跳过当前索引字段,将其余where条件中索引相关字段全部加入到Index Filter之中。

按照Index Filter的提取规则,在此次慢查询中提取出来的Index Filter为:station_id='1809'。在Index Key确定的索引查询范围中,遍历索引记录时都需要比较 station_id='1809',不满足该条件则直接丢失,继续读取索引下一条记录。

Table Filter

Table Filter用于过滤掉索引无法过滤的数据。在二级索引中通过主键回表查询到整行记录后,判断该记录是否符合Table Filter条件,不符合则丢失,继续判断下一条记录。

提取规则很简单:所有不属于索引字段的查询条件,均归为Table Filter之中。按照Table Filter的提取规则,在此次查询中Table Filter为:status=‘2’。

总结和补充

Index Key用于确定索引扫描的范围;Index Filter用于在索引中进行过滤;Table Filter需要回表后在Mysql服务器进行过滤。

Index Key和Index Filter发生在InnoDB存储层,Table Filter发生在Mysql Server层。

在 MySQL5.6 之前,并不区分Index Filter与Table Filter,统统将Index First Key与Index Last Key范围内的索引记录,回表读取完整记录,然后返回给MySQL Server层进行过滤。

在MySQL 5.6及之后,Index Filter与Table Filter分离,Index Filter下降到InnoDB的存储引擎层进行过滤,减少了回表与返回MySQL Server层的记录交互开销,提高了SQL的执行效率。

分析索引失效原因

首先是count(),此时通配符 * 经优化并不会拓展所有列,实际上会忽略所有的列直接统计行数。所以只想收集行数最好使用count()。

接下来分析where语句。假设此慢查询会使用了二级索引idx_date_station_driver,按照上面where条件查询的执行过程,该慢查询的Index First Key为start_date>'1628442000',Index Last Key为: start_date<'1631120399',Index Filter为:station_id='1809',Table Filter为:status=‘2’。

提取Index First Key后在索引B+树上定位索引起始范围就是索引匹配的过程,在索引B+树上使用二分搜索方法快速定位符合查询条件的起始叶子节点。通过上文Where条件查询执行过程,我们知道该慢查询的where条件(start_date>'1628442000' and start_date<'1631120399' and status='2' and station_id='1809'),只匹配了索引idx_date_station_driver(start_date, station_id, driver_id)的第一个字段,即只匹配了idx_date_station_driver(start_date),station_id='1809‘精确查询并没有作用到匹配索引上,而是在Index Filter即索引下推过程中发挥了作用。实际上这里是因为范围查询使联合索引停止匹配

范围查询导致联合索引停止匹配

为什么范围查询会使联合索引停止匹配?这里涉及到最左前缀匹配原理。假设建立一个联合索引 index(a, b),会先对a进行排序,在a相等的情况下对b进行排序,如下图所示。在该索引树上,a是全局有序的,而b则处于全局无序、局部有序状态。从全局来看,b的值为1、2、1、4、1、2,只有 b=2 查询条件无法直接使用该索引;从局部来看,当a的值确定时,b则是有序状态,a=2 && b=4可以使用该索引。所以范围查询使联合索引停止匹配的根本原因是,索引树上非首字段的有序状态依赖前一个字段相等情况,而范围查询破坏了下一个索引字段局部有序状态,导致索引停止匹配。

Mysql索引失效怎么办?失效原因浅析

范围查询使联合索引停止匹配,并不能在索引匹配的时候就过滤掉 station_id不等于'1809' 的数据,导致Mysql在索引上的扫描范围Index First Key和Index Last Key完全由start_timestamp_of_date时间决定。start_timestamp_of_date范围查询可以过滤73%数据量,而station_id='1809'精确查询能过滤掉99%的数据量。

查询条件 数据量 占比
所有数据 6367万 100%
start_timestamp_of_date>'1628442000' and start_timestamp_of_date<'1631120399' 1742万 27.35%
station_id='1809' 8万 0.16%

回表操作的开销

由于status字段不在索引idx_date_station_driver字段上,所以需要回表查询索引过滤的数据,在Mysql服务层判数据是否符合查询条件。

Mysql的优化器在执行sql语句时会先估算走匹配度高的索引的开销,如果走索引的开销比查全表还大,那么Mysql会选择全表扫描。这个结论可能反常识,在我们印象中索引就是用来提高查询效率的。这里主要涉及两个因素:

  • 当查询条件或查找的字段不在二级索引的字段上时,会执行回表操作,会走:二级索引+主键索引。

  • 磁盘随机I/O的性能低于顺序I/O。回表查询在主键索引上是随机I/O,全表扫描在主键索引上是顺序I/O。

做实验分析回表操作的开销是否是索引失效的直接原因?

去除status='0'查询条件,explain查看该查询是否使用到了索引idx_date_station_driver。结果如下图所示,少了回表操作的开销,索引并未失效。

Mysql索引失效怎么办?失效原因浅析

总结

结合以上分析总结索引失效原因是:范围查询使联合索引停止匹配,索引匹配过滤的数据不够多,导致Mysql优化器估算出Table Filter的回表操作开销大于全表查询,所以选择了全表查询。范围查询使联合索引停止匹配是索引失效的罪魁祸首,而回表操作的开销是索引失效的直接原因。

优化索引

该慢查询索引失效的罪魁祸首是范围查询使联合索引停止匹配,只需要把范围查询的字段调整到精确查询的字段后面,即将

联合索引 idx_date_station_driver(start_date, station_id, driver_id) 修改为 idx_station_date_driver(station_id, start_date, driver_id) 。优化后的结果如下图所示。

Mysql索引失效怎么办?失效原因浅析

拓展

索引失效常见场景

  • 违反最左前缀匹配原则。例如有索引index(a,b),但查询条件只有b字段。

  • 在索引列上做任何操作,包括计算、函数、类型转换等。

  • 范围查询使联合索引停止匹配。

  • 减少select*的使用。避免不必要的回表操作开销,尽量使用覆盖索引。

  • 使用不等于(!=、<>),使用or操作。

  • 字符串不加单引号索引失效。

  • like以通配符开头'%abc'。注意like ‘abc%’ 是可以走索引的。

  • order by 违反最左匹配原则,含非索引字段排序,会产生文件排序。

  • group by 违反最左匹配原则,含非索引字段分组,会导致产生临时表。

Explain分析

慢查询的分析离不开mysql的explain语句,explain主要关注两个字段Type和Extra。

Type表示访问数据的方式,Extra表示过滤和整理数据的方式。这里列举出来方便查找。

Type Extra
ALL 全表扫描 Using index 使用覆盖索引,不需要回表,不需要Mysql服务层过滤
index 索引树全扫描 Using where 从存储引擎层获取数据,在Mysql服务层用where查询条件过滤数据。
range 索引树范围扫描 Using where; Using index 索引范围扫描。索引扫描和全表扫描类似,只是发生的层面不一样。
ref 非唯一性索引扫描,比如非唯一索引和唯一索引的非唯一前缀 Using index condition 使用索引下推,在存储引擎层充分利用查询索引字段过滤数据
eq_ref 唯一性索引扫描,比如唯一索引、主键索引 Using temporary 临时表存储结果,用于排序和分组查询
const 将查询转化成常量 Using filesort 文件排序,用于排序
NULL 不用访问表或索引 NULL 回表

更多编程相关知识,请访问:编程入门!!

以上就是Mysql索引失效怎么办?失效原因浅析的详细内容,更多请关注主题君其它相关文章!

-=||=-收藏赞 (0)
更多主题
单栏极简文艺WordPress博客主题:Diaspora主题
¥ 0 关注:20,369
详情
简约黑白WordPress个人博客主题:Personal主题
¥ 0 关注:20,224
详情
v1.0.0
小程序收录免费WordPress主题:XCX主题
¥ 0 关注:20,010
详情
v1.0.0
一款简约单栏的免费WordPress博客主题:itheme主题
¥ 0 关注:18,243
详情
功能丰富切轻量的WordPress虚拟资源主题:Rizhuti主题
¥ 599 关注:18,192
详情
大气优雅免费WordPress博客主题:HONEY主题
¥ 0 关注:15,013
详情
回复
暂无评论

不要再留垃圾评论了,主题君整理资源不容易,留几句鼓励的话吧。