当前位置: 首页 > news >正文

MySQL 索引失效处理:原因分析与优化实战

MySQL 索引失效处理:原因分析与优化实战

  • MySQL 索引失效处理:原因分析与优化实战
    • 引言
    • 一、什么是索引失效?
    • 二、索引失效的常见原因
      • 2.1 查询条件中使用函数或表达式
        • 示例:
        • 原因:
      • 2.2 数据类型不匹配
        • 示例:
        • 原因:
      • 2.3 使用 `OR` 条件
        • 示例:
        • 原因:
      • 2.4 索引列参与计算
        • 示例:
        • 原因:
      • 2.5 使用 `LIKE` 以通配符开头
        • 示例:
        • 原因:
      • 2.6 复合索引未遵循最左前缀原则
        • 示例:
        • 原因:
      • 2.7 数据分布不均匀
        • 示例:
        • 原因:
    • 三、索引失效的优化方法
      • 3.1 避免对索引列使用函数或表达式
        • 优化示例:
      • 3.2 确保数据类型匹配
        • 优化示例:
      • 3.3 使用 `UNION` 替代 `OR`
        • 优化示例:
      • 3.4 避免索引列参与计算
        • 优化示例:
      • 3.5 优化 `LIKE` 查询
        • 优化示例:
      • 3.6 遵循复合索引的最左前缀原则
        • 优化示例:
      • 3.7 强制使用索引
        • 优化示例:
      • 3.8 更新统计信息
        • 优化示例:
    • 四、实战案例
      • 4.1 案例背景
      • 4.2 优化步骤
    • 五、总结


MySQL 索引失效处理:原因分析与优化实战

引言

索引是 MySQL 中提升查询性能的关键工具。然而,即使创建了索引,查询性能也可能不理想,甚至出现索引失效的情况。索引失效会导致 MySQL 执行全表扫描,严重影响查询性能。本文将深入探讨索引失效的常见原因,并提供相应的优化方法,帮助你更好地处理索引失效问题。


一、什么是索引失效?

索引失效是指 MySQL 在执行查询时,未能使用已创建的索引,而是选择了全表扫描或其他低效的执行方式。索引失效通常会导致查询性能显著下降。


二、索引失效的常见原因

2.1 查询条件中使用函数或表达式

如果查询条件中对索引列使用了函数或表达式,索引可能无法生效。

示例:
-- 索引失效
SELECT * FROM test_table WHERE YEAR(created_at) = 2023;-- 优化后
SELECT * FROM test_table WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
原因:

MySQL 无法对函数或表达式的结果使用索引。


2.2 数据类型不匹配

如果查询条件中的数据类型与索引列的数据类型不匹配,索引可能无法生效。

示例:
-- 索引失效(age 是 INT,但条件是字符串)
SELECT * FROM test_table WHERE age = '30';-- 优化后
SELECT * FROM test_table WHERE age = 30;
原因:

MySQL 需要进行隐式类型转换,导致索引失效。


2.3 使用 OR 条件

如果查询条件中使用了 OR,且 OR 两边的条件涉及不同列,索引可能无法生效。

示例:
-- 索引失效
SELECT * FROM test_table WHERE age = 30 OR name = 'Alice';-- 优化后
SELECT * FROM test_table WHERE age = 30
UNION
SELECT * FROM test_table WHERE name = 'Alice';
原因:

MySQL 无法同时使用多个单列索引。


2.4 索引列参与计算

如果查询条件中索引列参与了计算,索引可能无法生效。

示例:
-- 索引失效
SELECT * FROM test_table WHERE age + 10 > 40;-- 优化后
SELECT * FROM test_table WHERE age > 30;
原因:

MySQL 无法对计算后的结果使用索引。


2.5 使用 LIKE 以通配符开头

如果查询条件中使用 LIKE,且通配符(%_)出现在开头,索引可能无法生效。

示例:
-- 索引失效
SELECT * FROM test_table WHERE name LIKE '%Alice%';-- 优化后(如果可能)
SELECT * FROM test_table WHERE name LIKE 'Alice%';
原因:

MySQL 无法对以通配符开头的模式使用索引。


2.6 复合索引未遵循最左前缀原则

如果查询条件未遵循复合索引的最左前缀原则,索引可能无法生效。

示例:
-- 创建复合索引
CREATE INDEX idx_age_created_at ON test_table(age, created_at);-- 索引失效(未使用 age)
SELECT * FROM test_table WHERE created_at > '2023-01-01';-- 优化后
SELECT * FROM test_table WHERE age = 30 AND created_at > '2023-01-01';
原因:

复合索引需要从左到右匹配,否则无法使用索引。


2.7 数据分布不均匀

如果索引列的数据分布不均匀(如某个值出现频率极高),MySQL 可能认为全表扫描比使用索引更快。

示例:
-- 假设 age=30 的数据占 90%
SELECT * FROM test_table WHERE age = 30;
原因:

MySQL 优化器认为全表扫描的成本更低。


三、索引失效的优化方法

3.1 避免对索引列使用函数或表达式

确保查询条件中不对索引列使用函数或表达式。

优化示例:
-- 不推荐
SELECT * FROM test_table WHERE YEAR(created_at) = 2023;-- 推荐
SELECT * FROM test_table WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

3.2 确保数据类型匹配

确保查询条件中的数据类型与索引列的数据类型一致。

优化示例:
-- 不推荐
SELECT * FROM test_table WHERE age = '30';-- 推荐
SELECT * FROM test_table WHERE age = 30;

3.3 使用 UNION 替代 OR

如果查询条件中使用了 OR,可以尝试使用 UNION 替代。

优化示例:
-- 不推荐
SELECT * FROM test_table WHERE age = 30 OR name = 'Alice';-- 推荐
SELECT * FROM test_table WHERE age = 30
UNION
SELECT * FROM test_table WHERE name = 'Alice';

3.4 避免索引列参与计算

确保查询条件中索引列不参与计算。

优化示例:
-- 不推荐
SELECT * FROM test_table WHERE age + 10 > 40;-- 推荐
SELECT * FROM test_table WHERE age > 30;

3.5 优化 LIKE 查询

尽量避免在 LIKE 查询中以通配符开头。

优化示例:
-- 不推荐
SELECT * FROM test_table WHERE name LIKE '%Alice%';-- 推荐
SELECT * FROM test_table WHERE name LIKE 'Alice%';

3.6 遵循复合索引的最左前缀原则

确保查询条件遵循复合索引的最左前缀原则。

优化示例:
-- 不推荐
SELECT * FROM test_table WHERE created_at > '2023-01-01';-- 推荐
SELECT * FROM test_table WHERE age = 30 AND created_at > '2023-01-01';

3.7 强制使用索引

如果 MySQL 优化器未选择使用索引,可以尝试强制使用索引。

优化示例:
SELECT * FROM test_table FORCE INDEX (idx_age) WHERE age > 30;

3.8 更新统计信息

如果数据分布不均匀,可以更新表的统计信息,帮助优化器选择更合适的执行计划。

优化示例:
ANALYZE TABLE test_table;

四、实战案例

4.1 案例背景

假设我们有一个查询:

SELECT * FROM test_table WHERE YEAR(created_at) = 2023;

通过 EXPLAIN 分析发现 typeALL,说明 MySQL 正在执行全表扫描。

4.2 优化步骤

  1. 避免使用函数
    将查询条件改为范围查询:

    SELECT * FROM test_table WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
    
  2. 创建索引
    created_at 创建索引:

    CREATE INDEX idx_created_at ON test_table(created_at);
    
  3. 使用 EXPLAIN 验证
    确认索引是否生效:

    EXPLAIN SELECT * FROM test_table WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
    

五、总结

索引失效是 MySQL 查询性能优化的常见问题。通过本文的学习,你可以掌握以下技能:

  1. 识别索引失效的常见原因。
  2. 使用 EXPLAIN 分析查询执行计划。
  3. 优化查询语句,避免索引失效。
  4. 强制使用索引或更新统计信息。

希望本文能帮助你更好地处理 MySQL 索引失效问题,提升数据库查询性能!

相关文章:

MySQL 索引失效处理:原因分析与优化实战

MySQL 索引失效处理&#xff1a;原因分析与优化实战 MySQL 索引失效处理&#xff1a;原因分析与优化实战引言一、什么是索引失效&#xff1f;二、索引失效的常见原因2.1 查询条件中使用函数或表达式示例&#xff1a;原因&#xff1a; 2.2 数据类型不匹配示例&#xff1a;原因&a…...

基于Python的AI代码审计工具实现方案,结合DeepSeek API和商业化设计

以下是一个基于Python的AI代码审计工具实现方案&#xff0c;结合DeepSeek API和商业化设计&#xff0c;分为基础功能版和进阶扩展方向&#xff1a; 基础版实现代码 (命令行工具) import os import requests from dotenv import load_dotenv import hashlib import json from t…...

用Python实现线性回归:从数学原理到代码实战

一、前言&#xff1a;为什么线性回归是AI必修课&#xff1f; 作为机器学习领域的"Hello World"&#xff0c;线性回归算法具有三大核心价值&#xff1a; 1️⃣ 理解监督学习的底层逻辑&#xff08;特征工程→模型训练→预测输出&#xff09; 2️⃣ 掌握梯度下降等优化…...

系统可观测性(1)基础概念

系统可观测性(1)基础概念(Log/Tracing/Metrics) Author: Once Day Date: 2025年2月8日 一位热衷于Linux学习和开发的菜鸟&#xff0c;试图谱写一场冒险之旅&#xff0c;也许终点只是一场白日梦… 漫漫长路&#xff0c;有人对你微笑过嘛… 全系列文章可参考专栏: 十年代码训练…...

Redis未授权访问漏洞导致getshell

一、漏洞信息 redis默认情况下会绑定在本地6379端口&#xff0c;如果没有进行采用相关的策略&#xff0c;就会将redis服务暴露到公网上&#xff0c;如果再没有设置密码认证(一般为空)的情况下&#xff0c;会导致任意用户可以访问到目标服务器的情况下未授权访问redis以及读取r…...

Electron 全面解析:跨平台桌面应用开发指南

引言 在当今多平台并存的数字时代&#xff0c;如何高效开发跨平台桌面应用成为开发者面临的重要挑战。Electron作为GitHub开源的跨平台框架&#xff0c;凭借其独特的Web技术融合能力&#xff0c;已成为构建桌面应用的热门选择。本文将深入探讨Electron的核心原理、开发实践及未…...

React进阶之React核心源码解析(一)

React核心源码解析 react 特点CPU卡顿IO 卡顿 新老 react 架构对比v15v16.8Scheduler 调度器Reconciler 协调器 React fiber原理更新dommount 构建过程 render阶段 — scheduler reconcilerreact源码解析react-domreact-dom/src/client/ReactDOMRoot.js react-reconcilerreact-…...

用大模型学大模型03-数学基础 概率论 条件概率 全概率公式 贝叶斯定理

要深入浅出地理解条件概率与贝叶斯定理&#xff0c;可以从以下几个方面入手&#xff0c;结合理论知识和实例进行学习&#xff1a; 贝叶斯定理与智能世界的暗语 条件概率&#xff0c;全概率公式与贝叶斯公式的推导&#xff0c;理解和应用 拉普拉斯平滑 贝叶斯解决垃圾邮件分类 …...

C++ Primer 参数传递

欢迎阅读我的 【CPrimer】专栏 专栏简介&#xff1a;本专栏主要面向C初学者&#xff0c;解释C的一些基本概念和基础语言特性&#xff0c;涉及C标准库的用法&#xff0c;面向对象特性&#xff0c;泛型特性高级用法。通过使用标准库中定义的抽象设施&#xff0c;使你更加适应高级…...

Jupyter lab 无法导出格式 Save and Export Notebook As无法展开

本来尝试jypyter lab如何导出HTML带有侧边导航栏&#xff0c;一顿操作后发现还是没实现。 又突然发现导出其他格式地功能不能用了&#xff0c;浏览器里Save and Export Notebook As展开按钮为灰色打不开。 经典想实现的没实现还把原先的搞坏了。 看了jupyter lab的运行信息发…...

Mac之JDK安装

Mac之JDK安装 一.安装 jdk 打开终端输入命令:java -version 查看是否已安装 JDK Oracle 官方下载地址 根据自己Mac 系统安装 查看 Mac 系统&#xff0c;打开中断命令&#xff0c;输入: uname -a Compressed Archive 是压缩文档&#xff0c;下载的是一个 .tar.gz 压缩包 D…...

OpenEuler学习笔记(三十一):在OpenEuler上搭建仓颉语言开发环境

仓颉语言&#xff08;Cangjie programming language&#xff09;相对较为小众&#xff0c;截至2025年&#xff0c;并没有广泛的资料和成熟的通用搭建流程。不过下面为你提供一个较为通用的在OpenEuler上搭建开发环境的大致思路&#xff0c;你可以根据实际情况进行调整。 1. 安…...

2021年全国研究生数学建模竞赛华为杯E题信号干扰下的超宽带(UWB)精确定位问题求解全过程文档及程序

2021年全国研究生数学建模竞赛华为杯 E题 信号干扰下的超宽带(UWB)精确定位问题 原题再现&#xff1a; 一、背景   UWB&#xff08;Ultra-Wideband&#xff09;技术也被称之为“超宽带”&#xff0c;又称之为脉冲无线电技术。这是一种无需任何载波&#xff0c;通过发送纳秒…...

【电脑】u盘重装win7

u盘必须8GB以上 1. CPU型号 首先查看CPU的型号看看到底能不能装win7 2. 下载光盘映像文件 网址 看电脑是多少位的机器(32位下载x86 64位下载x64) 一共是这么多个版本按需下载对应的版本 电脑小白推荐无脑下载旗舰版 将链接复制到迅雷进行下载 3. 下载软碟通 网址 下…...

HCIA项目实践--RIP的拓展配置

9.4.7 RIP的拓展配置 &#xff08;1&#xff09;RIPV2的手工认证 RIPv2 的手工认证是增强网络安全性的手段。管理员手动配置密钥&#xff0c;路由器在收发 RIPv2 路由更新消息时&#xff0c;会对消息中的认证信息进行检查。发送方添加密钥&#xff0c;接收方用预设密钥验证。若…...

常用架构图:业务架构、产品架构、系统架构、数据架构、技术架构、应用架构、功能架构及信息架构

文章目录 引言常见的架构图I 业务架构图-案例模块功能说明1. 用户界面层 (UI)2. 应用服务层3. 数据管理层4. 基础设施层业务流程图示例技术实现II 功能架构图 -案例功能模块说明1. 船舶监控模块2. 报警管理模块3. 应急响应模块4. 通信管理模块5. 数据分析模块数据管理层基础设施…...

初阶c语言(练习题,猜随机数,关机程序)

目录 第一题&#xff0c;使用函数编写一个随机数&#xff0c;然后自己猜&#xff0c;猜随机数 第二道题&#xff08;关机程序&#xff09; 实现代码&#xff08;关机程序&#xff09; 实现代码&#xff08;猜数字&#xff09; 前言&#xff1a; 学习c语言&#xff0c;学习…...

三维重建(十二)——3D先验的使用

文章目录 零、最近感受和前言一、使用能够快速得到重建初始化的方法1.1 Colmap(多视角)1.2 深度估计(单视角)二、已知形状模板2.1 人脸2.2 人体2.3 动物三、刚性与非刚性约束(变形约束)3.1 刚性变形3.2 非刚性变形四、统计(深度学习)先验——从大量(3D)数据中提取信息…...

DDoS技术解析

这里是Themberfue 今天我们不聊别的&#xff0c;我们聊聊著名的网络攻击手段之一的 DDoS&#xff0c;看看其背后的技术细节。 DoS 了解 DDoS 前&#xff0c;先来讲讲 DoS 是什么&#xff0c;此 DoS 而不是 DOS 操作系统啊。1996年9月6日&#xff0c;世界第三古老的网络服务提供…...

总结:如何在SpringBoot中使用https协议以及自签证书?

总结&#xff1a;如何在SpringBoot中使用https协议以及自签证书&#xff1f; 前提一&#xff1a;什么是http协议&#xff1f;前提二&#xff1a;什么是https协议&#xff1f;一生成自签证书二 将证书转换为PKCS12格式三 配置SpringBoot&#xff08;1&#xff09;修改配置文件&a…...

关于iview组件中使用 table , 绑定序号分页后序号从1开始的解决方案

问题描述&#xff1a;iview使用table 中type: "index",分页之后 &#xff0c;索引还是从1开始&#xff0c;试过绑定后台返回数据的id, 这种方法可行&#xff0c;就是后台返回数据的每个页面id都不完全是按照从1开始的升序&#xff0c;因此百度了下&#xff0c;找到了…...

【第二十一章 SDIO接口(SDIO)】

第二十一章 SDIO接口 目录 第二十一章 SDIO接口(SDIO) 1 SDIO 主要功能 2 SDIO 总线拓扑 3 SDIO 功能描述 3.1 SDIO 适配器 3.2 SDIOAHB 接口 4 卡功能描述 4.1 卡识别模式 4.2 卡复位 4.3 操作电压范围确认 4.4 卡识别过程 4.5 写数据块 4.6 读数据块 4.7 数据流…...

多模态商品数据接口:融合图像、语音与文字的下一代商品详情体验

一、多模态商品数据接口的技术架构 &#xff08;一&#xff09;多模态数据融合引擎 跨模态语义对齐 通过Transformer架构实现图像、语音、文字的语义关联。例如&#xff0c;当用户上传一张“蓝色连衣裙”的图片时&#xff0c;接口可自动提取图像中的颜色&#xff08;RGB值&…...

Android Bitmap治理全解析:从加载优化到泄漏防控的全生命周期管理

引言 Bitmap&#xff08;位图&#xff09;是Android应用内存占用的“头号杀手”。一张1080P&#xff08;1920x1080&#xff09;的图片以ARGB_8888格式加载时&#xff0c;内存占用高达8MB&#xff08;192010804字节&#xff09;。据统计&#xff0c;超过60%的应用OOM崩溃与Bitm…...

技术栈RabbitMq的介绍和使用

目录 1. 什么是消息队列&#xff1f;2. 消息队列的优点3. RabbitMQ 消息队列概述4. RabbitMQ 安装5. Exchange 四种类型5.1 direct 精准匹配5.2 fanout 广播5.3 topic 正则匹配 6. RabbitMQ 队列模式6.1 简单队列模式6.2 工作队列模式6.3 发布/订阅模式6.4 路由模式6.5 主题模式…...

代码规范和架构【立芯理论一】(2025.06.08)

1、代码规范的目标 代码简洁精炼、美观&#xff0c;可持续性好高效率高复用&#xff0c;可移植性好高内聚&#xff0c;低耦合没有冗余规范性&#xff0c;代码有规可循&#xff0c;可以看出自己当时的思考过程特殊排版&#xff0c;特殊语法&#xff0c;特殊指令&#xff0c;必须…...

SQL Server 触发器调用存储过程实现发送 HTTP 请求

文章目录 需求分析解决第 1 步:前置条件,启用 OLE 自动化方式 1:使用 SQL 实现启用 OLE 自动化方式 2:Sql Server 2005启动OLE自动化方式 3:Sql Server 2008启动OLE自动化第 2 步:创建存储过程第 3 步:创建触发器扩展 - 如何调试?第 1 步:登录 SQL Server 2008第 2 步…...

JDK 17 序列化是怎么回事

如何序列化&#xff1f;其实很简单&#xff0c;就是根据每个类型&#xff0c;用工厂类调用。逐个完成。 没什么漂亮的代码&#xff0c;只有有效、稳定的代码。 代码中调用toJson toJson 代码 mapper.writeValueAsString ObjectMapper DefaultSerializerProvider 一堆实…...

【版本控制】GitHub Desktop 入门教程与开源协作全流程解析

目录 0 引言1 GitHub Desktop 入门教程1.1 安装与基础配置1.2 核心功能使用指南仓库管理日常开发流程分支管理 2 GitHub 开源协作流程详解2.1 Fork & Pull Request 模型2.2 完整协作流程步骤步骤 1: Fork&#xff08;创建个人副本&#xff09;步骤 2: Clone&#xff08;克隆…...

【Java多线程从青铜到王者】单例设计模式(八)

wait和sleep的区别 我们的wait也是提供了一个还有超时时间的版本&#xff0c;sleep也是可以指定时间的&#xff0c;也就是说时间一到就会解除阻塞&#xff0c;继续执行 wait和sleep都能被提前唤醒(虽然时间还没有到也可以提前唤醒)&#xff0c;wait能被notify提前唤醒&#xf…...