MySQL索引失效及解决、覆盖索引、全值匹配

雨点打透心脏的1/2处 2023-09-27 21:41 115阅读 0赞

## 什么是索引失效，如何避免它？ ##

![在这里插入图片描述][1abd05744be24110b33d53a933268e76.png]

索引失效是指在MySQL数据库中，索引没有起到预期的作用，导致查询性能下降的情况。¹²³⁴⁵

索引失效的原因有很多，例如：

*  不符合**全值匹配**或**最佳左前缀法则**，即查询条件没有包含索引的第一列或跳过了索引中的某些列。¹²³
 *  在索引列上使用了**运算、函数、类型转换**等操作，导致MySQL无法利用索引。¹²³
 *  使用了不等于、or、like %等条件，导致MySQL无法有效地使用索引。¹²³⁴
 *  查询结果集过大，导致MySQL认为全表扫描比使用索引更快。³⁴

如何避免索引失效的方法有很多，例如：

*  根据查询条件设计合理的索引，遵循全值匹配和最佳左前缀法则。¹²³
 *  避免在索引列上使用运算、函数、类型转换等操作，尽量让索引列保持原始值。¹²³
 *  优化查询条件，尽量使用等于、in、between等条件，避免使用不等于、or、like %等条件。¹²³⁴
 *  使用覆盖索引，即只访问索引的查询，避免select \*。¹²³
 *  分析SQL执行计划，使用explain命令或第三方工具，查看是否有优化空间。²³

## 什么是覆盖索引 ##

覆盖索引是指查询的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。²³⁴⁵⁶

覆盖索引的好处是可以避免回表操作，即不需要再根据主键去查找数据行，从而减少系统调用和数据拷贝等时间，提高查询性能。¹³⁵

覆盖索引的使用方法是根据查询需求设计合适的索引，尽量让索引包含所有需要的字段，避免select \*。¹²³⁴⁵

例如，假设有一个表user，包含id、name、age、gender等字段，其中id是主键，name和age是非主键索引。如果查询语句是：

select name from user where age = 20;

那么这个语句就可以利用覆盖索引，因为name和age都在非主键索引中，不需要再回表查找数据行。⁴

## MySQL全值匹配 ##

MySQL全值匹配是指在使用复合索引时，查询条件要包含索引的所有列，才能最大程度地利用索引。¹

例如，假设有一个表emp，包含id、name、age、deptid等字段，其中id是主键，age、deptid和name是复合索引。如果查询语句是：

select * from emp where age = 20 and deptid = 10 and name = 'Tom';

那么这个语句就可以利用全值匹配，因为查询条件包含了复合索引的所有列。¹

如果查询语句是：

select * from emp where age = 20 and deptid = 10;

那么这个语句就不能利用全值匹配，因为查询条件缺少了复合索引的最后一列name。¹

[1abd05744be24110b33d53a933268e76.png]: https://img-blog.csdnimg.cn/1abd05744be24110b33d53a933268e76.png

发表评论取消回复

表情：

评论列表（有 0 条评论，115人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引失效问题及解决方案

在MySQL中，如果一个索引失效，这意味着当你试图使用这个索引来加速查询时，系统无法正确利用。以下是一些可能导致索引失效的情况以及解决方案： 1. **更新少的列**：如

迈不过友情╰/ 2024年12月23日 21:00/ 0 赞/ 12 阅读

相关索引失效：理解MySQL索引失效的原理及解决办法？

在MySQL中，索引是一种特殊的数据结构，用于快速查找和排序数据。当查询语句与索引设计不匹配时，可能会导致索引失效。索引失效的常见原因包括： 1. 查询条件不在索引列上：

拼搏现实的明天。/ 2024年12月15日 10:15/ 0 赞/ 18 阅读

相关【索引失效】MySQL索引失效场景

如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按as...

今天药忘吃喽~/ 2024年04月23日 04:45/ 0 赞/ 114 阅读

相关 MySQL索引失效及使用索引的优缺点

本文所有实验基于MySQL5.7.21，实验将会用到Explain工具，不了解的同学可参考此文章：[MySQL性能优化神器Explain详解][MySQL_Explai...

r囧r小猫/ 2024年04月18日 20:30/ 0 赞/ 74 阅读

相关【MySQL进阶-03】深入理解mysql的索引分类，覆盖索引，覆盖索引失效，回表，MRR

MySql系列整体栏目 -------------------- <table> <thead> <tr> <th align="left">内容</th

Myth丶恋晨/ 2024年03月31日 17:32/ 0 赞/ 72 阅读

相关索引覆盖、索引失效以及查看索引执行计划

在讲述这三个主题之前，我们首先对索引的分类进行概述： 1.按照创建方式索引包括自动创建的索引和主动创建的索引：自动的创建主键约束（PRIMARY KEY）、唯一约束（UN

Myth丶恋晨/ 2024年03月30日 12:10/ 0 赞/ 67 阅读

相关 MySQL索引失效原因及解决方案

MySQL索引失效原因及解决方案 ![在这里插入图片描述][e08768b40a674d278e8286d3a4438138.png] 在使用MySQL数据库时，索引是

系统管理员/ 2023年10月14日 08:03/ 0 赞/ 31 阅读

相关 MySQL索引失效及解决、覆盖索引、全值匹配

什么是索引失效，如何避免它？ ![在这里插入图片描述][1abd05744be24110b33d53a933268e76.png] 索引失效是指在MySQL数据库中，索

雨点打透心脏的1/2处/ 2023年09月27日 21:41/ 0 赞/ 116 阅读

相关 mysql覆盖索引详解——like模糊全匹配中使用索引

什么是覆盖索引？解释一：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。解释二：索引是高效找到行的一个方法

深藏阁楼爱情的钟/ 2022年02月04日 01:27/ 0 赞/ 543 阅读

相关 MySQL索引失效及使用索引的优缺点

> 本文所有实验基于MySQL5.7.21，实验将会用到Explain工具，不了解的同学可参考此文章：MySQL性能优化神器Explain详解联合索引失效先创建一个

悠悠/ 2022年01月12日 16:17/ 0 赞/ 313 阅读