MySQL索引失效十种场景与优化方案

Mysql 联合索引失效 概念详解 小白教程 阅读详情

1 数据准备

1.1 新建数据表

CREATE TABLE `player` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `player_id` varchar(256) NOT NULL COMMENT '运动员编号',
  `player_name` varchar(256) NOT NULL COMMENT '运动员名称',
  `height` int(11) NOT NULL COMMENT '身高',
  `weight` int(11) NOT NULL COMMENT '体重',
  `type` varchar(256) DEFAULT '0' COMMENT '球员类型',
  `game_performance` text COMMENT '最近一场比赛表现',
  PRIMARY KEY (`id`),
  KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
  KEY `idx_type` (`type`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
复制代码

以上数据表声明三个索引:

  • 联合索引:idx_name_height_weight
  • 普通索引:idx_type
  • 普通索引:idx_height

1.2 新增100万条数据

@SpringBootTest(classes = TestApplication.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class PlayerServiceTest {

    @Resource
    private PlayerRepository playerRepository;

    @Test
    public void initBigData() {
        for (int i = 0; i < 1000000; i++) {
            PlayerEntity entity = new PlayerEntity();
            entity.setPlayerId(UUID.randomUUID().toString());
            entity.setPlayerName("球员_" + System.currentTimeMillis());
            entity.setType("0");
            entity.setWeight(150);
            entity.setHeight(188);
            entity.setGamePerformance("{\"runDistance\":8900.0,\"passSuccess\":80.12,\"scoreNum\":3}");
            playerRepository.insert(entity);
        }
    }
}
复制代码

2 基础知识

2.1 explain type

执行计划中访问类型是重要分析指标:

2.2 explain Extra

Extra表示执行计划扩展信息:

3 索引失效场景

本章节介绍索引失效十种场景:

  • 查询类型错误
  • 索引列参与运算
  • 错误使用通配符
  • 未用到覆盖索引
  • OR连接无索引字段
  • MySQL放弃使用索引
  • 联合索引失效 索引不完整 索引中断 非等值匹配 最左索引缺失

3.1 查询类型错误

3.1.1 失效场景

explain select * from player where type = 0
复制代码

3.1.2 解决方案

数据表定义type字段为varchar类型,查询必须使用相同类型:

explain select * from player where type = '0'
复制代码

3.2 索引列参与运算

3.2.1 失效场景

explain select * from player where height + 1 > 189
复制代码

3.2.2 解决方案

explain select * from player where height > 188
复制代码

3.3 MySQL放弃使用索引

3.3.1 失效场景

MySQL发现如果使用索引性能低于全表扫描则放弃使用索引。例如在表中100万条数据height字段值全部是188,所以执行如下语句时放弃使用索引:

explain select * from player where height > 187
复制代码

3.3.2 解决方案一

调整查询条件值:

explain select * from player where height > 188
复制代码

3.3.3 解决方案二

强制指定索引,这种方法不一定可以提升性能:

explain select * from player force index(idx_height) where height > 187
复制代码

3.4 错误使用通配符

3.4.1 数据准备

避免出现3.3章节失效问题此处修改一条数据:

update player set player_name = '测试球员' where id = 1
复制代码

3.4.2 失效场景一

explain select * from player where player_name like '%测试'
复制代码

3.4.3 失效场景二

explain select * from player where player_name like '%测试%'
复制代码

3.4.4 解决方案

explain select * from player where player_name like '测试%'
复制代码

3.5 OR连接无索引字段

3.5.1 失效场景

type有索引,weight无索引:

explain select * from player where type = '0' or weight = 150
复制代码

3.5.2 解决方案

weight新增索引,union拼装查询数据

explain
select * from player where type = '0' 
union
select * from player where weight = 150
复制代码

3.6 未用到覆盖索引

3.6.1 失效场景

Using index condition表示使用索引,但是需要回表查询

explain select * from player where player_name like '测试%'
复制代码

3.6.2 解决方案

覆盖索引含义是查询时索引列完全包含查询列,查询过程无须回表(需要在同一棵索引树)性能得到提升。Using Index; Using where表示使用覆盖索引并且用where过滤查询结果:

explain select id,player_name,height,weight from player where player_name like '测试%'
复制代码

3.7 联合索引失效

3.7.1 完整使用

联合索引idx_name_height_weight完整使用key_len=778:

explain select * from player where player_name = '球员_1682577684751' and height = 188 and weight = 150
复制代码

3.7.2 失效场景一:索引不完整

weight不在查询条件,所以只用到idx_name_height,所以key_len= 774:

explain select * from player where player_name = '球员_1682577684751' and height = 188
复制代码

3.7.3 失效场景二:索引中断

height不在查询条件,所以只用到idx_name,所以key_len= 770:

explain select * from player where player_name = '球员_1682577684751' and weight = 150
复制代码

3.7.4 失效场景三:非等值匹配

height非等值匹配,所以只用到idx_name_height,所以key_length=774:

explain select * from player where player_name='球员_1682577684751' and height > 188 and weight = 150
复制代码

3.7.5 失效场景四:最左索引缺失

player_name最左索引不在查询条件,全表扫描

explain select * from player where weight = 150
复制代码

4 文章总结

本文第一进行测试数据准备,第二介绍执行计划相关知识,第三介绍索引失效10种场景:查询类型错误,索引列参与运算,错误使用通配符,未用到覆盖索引,OR连接无索引字段,MySQL放弃使用索引,联合索引中索引不完整,索引中断,非等值匹配,最左索引缺失。

Mysql索引失效的常见原因&如何用好索引 本篇文章主要介绍了索引失效的常见原因和如何用好索引,并以案例的形式进行了说明 阅读详情

相关推荐

MySQL索引失效的十种场景与优化方案【超详细讲解】

本文第一进行测试数据准备,第二介绍执行计划相关知识,第三介绍索引失效10种场景:查询类型错误,索引列参与运算,错误使用通配符,未用到覆盖索引,OR连接无索引字段,MySQL放弃使用索引,联合索引中索引不完整,索引中断,非等值匹配,最左索引缺失。

xxxzzzqqq_的博客 362

MySQL索引失效场景以及解决方案

在对SQL语句进行索引查询时会遇到索引失效的时候,对于该语句的可行性以及性能效率方面有至关重要的影响,本篇剖析索引为何失效,有哪些情况会导致索引失效以及对于索引失效时的优化解决方案,其中着重介绍最左前缀匹配原则、MySQL逻辑架构和优化器、索引失效场景以及为何会失效......

程序小白进阶之路 5606

麒麟银河操作系统V10部署ffmpeg(也能用于Linux系统)

部署ffmpeg用来处理视频的各种操作,如编码解码,格式转换,剪辑,处理实时视频流,音频流,加滤镜等

zhouwenxing666的博客 9042

MySQL 使用 OR 条件导致索引失效

MySQL 使用 OR 条件导致索引失效,及其解决方案

yyp_abcd的博客 7365

STM32实战:用定时器触发ADC+DMA双缓冲实现电机电流采样(附避坑指南)

本文详细介绍了如何利用STM32的定时器触发ADC注入组采样,结合DMA双缓冲机制实现电机电流的高效采样。通过PWM触发和双缓冲架构,解决了开关噪声干扰、实时性要求和数据一致性等核心挑战,显著降低CPU负载并提升采样精度。附有完整的代码示例和避坑指南,适用于无人机电调、工业伺服驱动器等高精度电机控制场景。

weixin_33737134的博客 520

正弦波产生电路_文氏桥振荡

本文以文氏桥振荡为基础产生正弦波,介绍了原理、计算、应用时需要注意的一些点,并给出了实测可用的电路模板

白鹤忘机的博客 3万+

MySQL索引失效的十大场景及优化方案

诸如 `WHERE col2 = 'value'` 或 `WHERE col2 = 'value' AND col3 = 'value'` 的查询将无法使用该复合索引。当使用 `LIKE '%keyword'` 或 `LIKE '%keyword%'` 这种左模糊或全模糊匹配时,B+树索引的字符串前缀匹配优势丧失,导致索引失效。大多数情况下,在索引列上使用 `!虽然 `IN` 操作符通常可以使用索引,但当IN列表中的值非常多时,优化器可能会评估使用索引的成本高于全表扫描,从而导致索引失效。

TSH1264037548的博客 559

从触觉到视觉:GelSight传感器的工作原理与实战应用指南

本文深入解析了GelSight光学触觉传感器的核心工作原理,它通过将物理接触转化为视觉图像,为机器人提供了高精度的触觉感知。文章详细介绍了其在工业微观缺陷检测、机器人灵巧操作等领域的实战应用,并提供了从系统搭建到算法开发的完整指南,帮助工程师将这一前沿技术落地于实际项目。

solidity8miner的博客 942

MySQL查询性能优化深入理解索引失效的十大场景与解决方案

例如,如果字段`user_id`定义为`VARCHAR`类型,但查询时使用了`WHERE user_id = 123`(数字类型),MySQL需要将`user_id`字段的字符串值转换为数字进行比较,这将使索引无法使用。如果必须允许`NULL`值,则需要通过`EXPLAIN`分析执行计划,确保查询能够有效利用索引。当使用`LIKE`进行模糊查询时,如果通配符`%`或`_`出现在模式字符串的开头,如`WHERE name LIKE '%abc'`,那么索引将失效,因为数据库无法利用索引的有序性进行快速定位。

lookenghua的专栏 351

深入理解 SAP HANA 上的查询处理机制:从 CDS 分层建模到执行计划落地

本文探讨了S/4HANA系统中CDS视图性能问题的本质及其优化策略。文章指出,性能问题往往源于数据模型、查询编译和执行计划的综合影响,尤其在多层CDS视图叠加时表现更为明显。通过分析SAP HANA查询处理链路的四个关键阶段(SQL Plan Cache、SQL Front End、SQL Optimizer和Execution Plan),揭示了CDS视图性能波动的根本原因。文章强调,优化CDS性能需要关注数据规模、谓词选择性和执行计划稳定性,建议使用参数化查询、合理分层视图结构,并利用PlanViz等工

2007 年 ~ 2025 年,深耕 SAP 技术 18 年 4819

Android Framework SurfaceFlinger面试题及参考答案

请简述 SurfaceFlinger 在 Android 系统中的作用。 SurfaceFlinger 是 Android 系统中一个非常关键的服务。它主要负责将多个应用程序的 Surface 合成到一个屏幕显示内容中。 从应用程序的角度来看,每个应用都有自己的 UI 界面,这些 UI 界面实际上是由一系列的图层(Surface)构成。例如,一个应用可能有背景图层、按钮图层、文本图层等。应用通过向 SurfaceFlinger 提供这些 Surface 来请求显示。 SurfaceFl

大模型大数据攻城狮的专栏 1188

MySQL中索引失效的十大场景与优化实战

然而,即使创建了索引,在某些场景下索引可能无法被查询优化器有效使用,导致查询性能急剧下降,出现全表扫描。`来更新表的统计信息。在极少数情况下,可以使用`FORCE INDEX`提示强制使用特定索引,但这应作为最后手段,因为它破坏了优化器的自适应性。存在复合索引`idx_name_age (name, age)`,但查询为`SELECT FROM users WHERE age = 25;在一个只有100行记录的`config`表上,或在`gender`列(只有'M','F'两种值)上建立索引并进行查询。

qq_24865031的博客 352

【通信协议】GATT 与 BLE HID 工作原理详解

详细介绍BLE HID 跟GATT工作原理与两者的交互关系

以实战经验介绍行业知识 846

解析MySQL索引失效的十大场景与优化实战

,并确保连接列上有索引。=`或`<>`操作符会导致索引失效。在复合索引中,如果某一列使用了范围查询(如`>`、`<`、`BETWEEN`),那么该列之后的索引列将无法被用于进一步过滤。例如,对于索引`idx_a_b_c`,查询`WHERE a = 1 AND b > 10 AND c = 20`,索引只能用到`a`和`b`,`c`则无法使用。例如,有一个复合索引`idx_a_b_c (a, b, c)`,查询条件`WHERE b = 1 AND c = 2`将无法使用该索引,因为它跳过了最左列`a`。

qq_24865031的博客 389

ConvNeXt 改进 :ConvNeXt添加GnConv递归门控卷积,二次创新CNBlock结构 ,独家首发

视觉 Transformer 在多种任务中取得了显著的成功,这得益于基于点积自注意力的新空间建模机制。视觉 Transformer 中的关键因素——即输入自适应、长距离和高阶空间交互——也可以通过卷积框架高效实现。作者提出了递归门控卷积(Recursive Gated Convolution,简称 gnConv),通过门控卷积和递归设计执行高阶空间交互。该新操作具有高度的灵活性和可定制性,兼容各种卷积变体,并将自注意力中的二阶交互扩展到任意阶数,而不会引入显著的额外计算。

在职AI算法工程师,擅长计算机视觉,YOLO目标检测、分割等,擅长web、pyqt界面可视化,好内容持续更新中,来这里跟大家一起学习,共同进步 243

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

MySQL索引失效的十大经典场景与优化实战

然而,即便创建了索引,在某些场景下MySQL可能不会使用索引,导致查询性能急剧下降。,即使`status`字段有索引,优化器可能判断使用索引的成本高于全表扫描,特别是当`status`的值分布不均匀或'completed'状态占比较小时。,即使`name`字段上有索引,由于无法确定`'手机'`在字符串中的起始位置,MySQL无法使用索引进行快速定位,只能进行全表扫描。,如果`id`有主键索引而`email`没有索引,MySQL可能会选择全表扫描而非使用`id`索引,因为需要同时满足OR两边的条件。

trust090的博客 523

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

MySQL索引失效的十大场景分析与优化方案

然而,即使创建了索引,在某些场景下MySQL可能无法有效利用,导致查询性能急剧下降,即所谓的“索引失效”。通过深入理解B+树索引的工作原理、最左前缀原则以及MySQL优化器的行为,并结合具体的业务查询模式来设计和优化索引,可以最大限度地发挥索引的效能,从而提升整个数据库系统的性能。在WHERE子句中,如果OR条件中包含非索引列,即使其他条件涉及索引列,MySQL也可能放弃使用索引而进行全表扫描。如果索引列是字符串类型,而查询条件中使用了数字(反之亦然),MySQL会进行隐式类型转换,导致索引失效。

luowei01的专栏 262

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

MySQL不使用子查询的原因

这些案例展示了如何通过不同优化策略提升MySQL查询性能,特别是在处理子查询时。创建合适的索引:经常用于WHERE和JOIN的字段应建立索引,避免在低选择性的字段上建立索引(如性别字段)。避免索引失效的情况:使用函数计算的字段不会使用索引,如应优化为。组合索引的最左前缀法则:确保查询条件从组合索引的最左列开始。使用EXPLAIN分析查询执行计划:通过EXPLAIN关键字可以帮助我们了解查询的执行计划,从而发现性能瓶颈。优化查询语句:避免使用SELECT *,使用LIMIT。

2509_94186526的博客 988

MySQL索引失效的12种场景及解决方案

索引优化是一个持续的过程,需要结合具体的业务场景和数据特点。通过了解这些索引失效的场景和原理,你可以更有针对性地设计索引策略,显著提升数据库性能。没有一劳永逸的索引方案,随着数据量的增长和业务的变化,索引策略也需要不断调整和优化。持续监控、分析和优化是保持高性能数据库的关键。

风象南的专栏 4124

【MySQL】MySQL索引失效场景全面解析与优化指南

MySQL索引失效分析与优化摘要 索引是数据库性能优化的关键,但索引失效会导致全表扫描等性能问题。本文解析了MySQL索引失效的常见场景及优化方案: 索引失效原理:B+树索引结构下,优化器基于成本计算决定是否使用索引,影响因素包括选择性、扫描比例和I/O成本等。 主要失效场景: 违反最左前缀匹配原则 对索引列使用函数/计算 隐式类型转换 使用不等于操作符 LIKE前导通配符 OR条件包含无索引列 范围查询导致后续索引失效 子查询写法不当 优化方案: 合理设计复合索引顺序 避免对索引列运算 保持类型一致 使用

NeoLshu的博客 1785
上一篇: 5年开发经验,看完这份37W字Java高性能架构,终于拿到架构师薪资
下一篇: MYSQL相关之不常见变量、排序函数、JDBC数据库与Java连接
程序员职业指南
博客等级 码龄4年 2140粉丝 960原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值