13518219792
小编给大家分享一下MySQL索引失效的解决方法，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
创新互联公司是一家专业从事网站建设、成都网站设计、网页设计的品牌网络公司。如今是成都地区具影响力的网站设计公司,作为专业的成都网站建设公司,创新互联公司依托强大的技术实力、以及多年的网站运营经验,为您提供专业的成都网站建设、营销型网站建设及网站设计开发服务！
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';
order_recipient_extend_tab 表有6千万数据，慢查询的查询字段包括 start_date、station_id、status，按照索引设计初衷会走但实际上失效的索引是：
| 联合索引 | 字段1 | 字段2 | 字段3 |
|---|---|---|---|
| idx_date_station_driver | start_date | station_id | driver_id |
了解Mysql怎么执行where条件查询，能更快速清晰地洞见索引失效的原因。此次慢查询中匹配度高的索引是idx_date_station_driver，分析此次慢查询中where条件查询的执行过程。
Mysql对where条件提取规则主要可以归纳为三大类：Index Key(First Key & Last Key)，Index Filter，Table Filter。
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 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为：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可以使用该索引。所以范围查询使联合索引停止匹配的根本原因是，索引树上非首字段的有序状态依赖前一个字段相等情况，而范围查询破坏了下一个索引字段局部有序状态，导致索引停止匹配。
范围查询使联合索引停止匹配，并不能在索引匹配的时候就过滤掉 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优化器估算出Table Filter的回表操作开销大于全表查询，所以选择了全表查询。范围查询使联合索引停止匹配是索引失效的罪魁祸首，而回表操作的开销是索引失效的直接原因。
该慢查询索引失效的罪魁祸首是范围查询使联合索引停止匹配，只需要把范围查询的字段调整到精确查询的字段后面，即将
联合索引 idx_date_station_driver(start_date, station_id, driver_id)修改为 idx_station_date_driver(station_id, start_date, driver_id)。优化后的结果如下图所示。
违反最左前缀匹配原则。例如有索引index(a,b)，但查询条件只有b字段。
在索引列上做任何操作，包括计算、函数、类型转换等。
范围查询使联合索引停止匹配。
减少select*的使用。避免不必要的回表操作开销，尽量使用覆盖索引。
使用不等于（!=、<>），使用or操作。
字符串不加单引号索引失效。
like以通配符开头'%abc'。注意like ‘abc%’ 是可以走索引的。
order by 违反最左匹配原则，含非索引字段排序，会产生文件排序。
group by 违反最左匹配原则，含非索引字段分组，会导致产生临时表。
慢查询的分析离不开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索引失效的解决方法”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！
