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

雨点打透心脏的1/2处 2023-09-27 21:41 122阅读 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 条评论，122人围观）

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

相关阅读

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

相关 MySQL 覆盖索引

本文主要概述mysql的覆盖索引，以及几种常见的优化场景内容概要聚集索引和辅助索引什么是覆盖索引几种优化场景总

╰+攻爆jí腚メ/ 2022年06月05日 02:53/ 0 赞/ 254 阅读

相关 MySQL覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。只扫描索引而无需回表的优点： 1.索引条目通常远小于数据行大小，

深碍√TFBOYSˉ_/ 2022年04月25日 05:10/ 0 赞/ 258 阅读

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

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

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