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 分析发现 type 是 ALL,说明 MySQL 正在执行全表扫描。
4.2 优化步骤
-
避免使用函数:
将查询条件改为范围查询:SELECT * FROM test_table WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'; -
创建索引:
为created_at创建索引:CREATE INDEX idx_created_at ON test_table(created_at); -
使用
EXPLAIN验证:
确认索引是否生效:EXPLAIN SELECT * FROM test_table WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
五、总结
索引失效是 MySQL 查询性能优化的常见问题。通过本文的学习,你可以掌握以下技能:
- 识别索引失效的常见原因。
- 使用
EXPLAIN分析查询执行计划。 - 优化查询语句,避免索引失效。
- 强制使用索引或更新统计信息。
希望本文能帮助你更好地处理 MySQL 索引失效问题,提升数据库查询性能!
相关文章:
MySQL 索引失效处理:原因分析与优化实战
MySQL 索引失效处理:原因分析与优化实战 MySQL 索引失效处理:原因分析与优化实战引言一、什么是索引失效?二、索引失效的常见原因2.1 查询条件中使用函数或表达式示例:原因: 2.2 数据类型不匹配示例:原因&a…...
基于Python的AI代码审计工具实现方案,结合DeepSeek API和商业化设计
以下是一个基于Python的AI代码审计工具实现方案,结合DeepSeek API和商业化设计,分为基础功能版和进阶扩展方向: 基础版实现代码 (命令行工具) import os import requests from dotenv import load_dotenv import hashlib import json from t…...
用Python实现线性回归:从数学原理到代码实战
一、前言:为什么线性回归是AI必修课? 作为机器学习领域的"Hello World",线性回归算法具有三大核心价值: 1️⃣ 理解监督学习的底层逻辑(特征工程→模型训练→预测输出) 2️⃣ 掌握梯度下降等优化…...
系统可观测性(1)基础概念
系统可观测性(1)基础概念(Log/Tracing/Metrics) Author: Once Day Date: 2025年2月8日 一位热衷于Linux学习和开发的菜鸟,试图谱写一场冒险之旅,也许终点只是一场白日梦… 漫漫长路,有人对你微笑过嘛… 全系列文章可参考专栏: 十年代码训练…...
Redis未授权访问漏洞导致getshell
一、漏洞信息 redis默认情况下会绑定在本地6379端口,如果没有进行采用相关的策略,就会将redis服务暴露到公网上,如果再没有设置密码认证(一般为空)的情况下,会导致任意用户可以访问到目标服务器的情况下未授权访问redis以及读取r…...
Electron 全面解析:跨平台桌面应用开发指南
引言 在当今多平台并存的数字时代,如何高效开发跨平台桌面应用成为开发者面临的重要挑战。Electron作为GitHub开源的跨平台框架,凭借其独特的Web技术融合能力,已成为构建桌面应用的热门选择。本文将深入探讨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-数学基础 概率论 条件概率 全概率公式 贝叶斯定理
要深入浅出地理解条件概率与贝叶斯定理,可以从以下几个方面入手,结合理论知识和实例进行学习: 贝叶斯定理与智能世界的暗语 条件概率,全概率公式与贝叶斯公式的推导,理解和应用 拉普拉斯平滑 贝叶斯解决垃圾邮件分类 …...
C++ Primer 参数传递
欢迎阅读我的 【CPrimer】专栏 专栏简介:本专栏主要面向C初学者,解释C的一些基本概念和基础语言特性,涉及C标准库的用法,面向对象特性,泛型特性高级用法。通过使用标准库中定义的抽象设施,使你更加适应高级…...
Jupyter lab 无法导出格式 Save and Export Notebook As无法展开
本来尝试jypyter lab如何导出HTML带有侧边导航栏,一顿操作后发现还是没实现。 又突然发现导出其他格式地功能不能用了,浏览器里Save and Export Notebook As展开按钮为灰色打不开。 经典想实现的没实现还把原先的搞坏了。 看了jupyter lab的运行信息发…...
Mac之JDK安装
Mac之JDK安装 一.安装 jdk 打开终端输入命令:java -version 查看是否已安装 JDK Oracle 官方下载地址 根据自己Mac 系统安装 查看 Mac 系统,打开中断命令,输入: uname -a Compressed Archive 是压缩文档,下载的是一个 .tar.gz 压缩包 D…...
OpenEuler学习笔记(三十一):在OpenEuler上搭建仓颉语言开发环境
仓颉语言(Cangjie programming language)相对较为小众,截至2025年,并没有广泛的资料和成熟的通用搭建流程。不过下面为你提供一个较为通用的在OpenEuler上搭建开发环境的大致思路,你可以根据实际情况进行调整。 1. 安…...
2021年全国研究生数学建模竞赛华为杯E题信号干扰下的超宽带(UWB)精确定位问题求解全过程文档及程序
2021年全国研究生数学建模竞赛华为杯 E题 信号干扰下的超宽带(UWB)精确定位问题 原题再现: 一、背景 UWB(Ultra-Wideband)技术也被称之为“超宽带”,又称之为脉冲无线电技术。这是一种无需任何载波,通过发送纳秒…...
【电脑】u盘重装win7
u盘必须8GB以上 1. CPU型号 首先查看CPU的型号看看到底能不能装win7 2. 下载光盘映像文件 网址 看电脑是多少位的机器(32位下载x86 64位下载x64) 一共是这么多个版本按需下载对应的版本 电脑小白推荐无脑下载旗舰版 将链接复制到迅雷进行下载 3. 下载软碟通 网址 下…...
HCIA项目实践--RIP的拓展配置
9.4.7 RIP的拓展配置 (1)RIPV2的手工认证 RIPv2 的手工认证是增强网络安全性的手段。管理员手动配置密钥,路由器在收发 RIPv2 路由更新消息时,会对消息中的认证信息进行检查。发送方添加密钥,接收方用预设密钥验证。若…...
常用架构图:业务架构、产品架构、系统架构、数据架构、技术架构、应用架构、功能架构及信息架构
文章目录 引言常见的架构图I 业务架构图-案例模块功能说明1. 用户界面层 (UI)2. 应用服务层3. 数据管理层4. 基础设施层业务流程图示例技术实现II 功能架构图 -案例功能模块说明1. 船舶监控模块2. 报警管理模块3. 应急响应模块4. 通信管理模块5. 数据分析模块数据管理层基础设施…...
初阶c语言(练习题,猜随机数,关机程序)
目录 第一题,使用函数编写一个随机数,然后自己猜,猜随机数 第二道题(关机程序) 实现代码(关机程序) 实现代码(猜数字) 前言: 学习c语言,学习…...
三维重建(十二)——3D先验的使用
文章目录 零、最近感受和前言一、使用能够快速得到重建初始化的方法1.1 Colmap(多视角)1.2 深度估计(单视角)二、已知形状模板2.1 人脸2.2 人体2.3 动物三、刚性与非刚性约束(变形约束)3.1 刚性变形3.2 非刚性变形四、统计(深度学习)先验——从大量(3D)数据中提取信息…...
DDoS技术解析
这里是Themberfue 今天我们不聊别的,我们聊聊著名的网络攻击手段之一的 DDoS,看看其背后的技术细节。 DoS 了解 DDoS 前,先来讲讲 DoS 是什么,此 DoS 而不是 DOS 操作系统啊。1996年9月6日,世界第三古老的网络服务提供…...
总结:如何在SpringBoot中使用https协议以及自签证书?
总结:如何在SpringBoot中使用https协议以及自签证书? 前提一:什么是http协议?前提二:什么是https协议?一生成自签证书二 将证书转换为PKCS12格式三 配置SpringBoot(1)修改配置文件&a…...
Linux 文件类型,目录与路径,文件与目录管理
文件类型 后面的字符表示文件类型标志 普通文件:-(纯文本文件,二进制文件,数据格式文件) 如文本文件、图片、程序文件等。 目录文件:d(directory) 用来存放其他文件或子目录。 设备…...
基于距离变化能量开销动态调整的WSN低功耗拓扑控制开销算法matlab仿真
目录 1.程序功能描述 2.测试软件版本以及运行结果展示 3.核心程序 4.算法仿真参数 5.算法理论概述 6.参考文献 7.完整程序 1.程序功能描述 通过动态调整节点通信的能量开销,平衡网络负载,延长WSN生命周期。具体通过建立基于距离的能量消耗模型&am…...
Qt Widget类解析与代码注释
#include "widget.h" #include "ui_widget.h"Widget::Widget(QWidget *parent): QWidget(parent), ui(new Ui::Widget) {ui->setupUi(this); }Widget::~Widget() {delete ui; }//解释这串代码,写上注释 当然可以!这段代码是 Qt …...
【快手拥抱开源】通过快手团队开源的 KwaiCoder-AutoThink-preview 解锁大语言模型的潜力
引言: 在人工智能快速发展的浪潮中,快手Kwaipilot团队推出的 KwaiCoder-AutoThink-preview 具有里程碑意义——这是首个公开的AutoThink大语言模型(LLM)。该模型代表着该领域的重大突破,通过独特方式融合思考与非思考…...
C++.OpenGL (14/64)多光源(Multiple Lights)
多光源(Multiple Lights) 多光源渲染技术概览 #mermaid-svg-3L5e5gGn76TNh7Lq {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-3L5e5gGn76TNh7Lq .error-icon{fill:#552222;}#mermaid-svg-3L5e5gGn76TNh7Lq .erro…...
IP如何挑?2025年海外专线IP如何购买?
你花了时间和预算买了IP,结果IP质量不佳,项目效率低下不说,还可能带来莫名的网络问题,是不是太闹心了?尤其是在面对海外专线IP时,到底怎么才能买到适合自己的呢?所以,挑IP绝对是个技…...
【C++进阶篇】智能指针
C内存管理终极指南:智能指针从入门到源码剖析 一. 智能指针1.1 auto_ptr1.2 unique_ptr1.3 shared_ptr1.4 make_shared 二. 原理三. shared_ptr循环引用问题三. 线程安全问题四. 内存泄漏4.1 什么是内存泄漏4.2 危害4.3 避免内存泄漏 五. 最后 一. 智能指针 智能指…...
Vue3 PC端 UI组件库我更推荐Naive UI
一、Vue3生态现状与UI库选择的重要性 随着Vue3的稳定发布和Composition API的广泛采用,前端开发者面临着UI组件库的重新选择。一个好的UI库不仅能提升开发效率,还能确保项目的长期可维护性。本文将对比三大主流Vue3 UI库(Naive UI、Element …...
【实施指南】Android客户端HTTPS双向认证实施指南
🔐 一、所需准备材料 证书文件(6类核心文件) 类型 格式 作用 Android端要求 CA根证书 .crt/.pem 验证服务器/客户端证书合法性 需预置到Android信任库 服务器证书 .crt 服务器身份证明 客户端需持有以验证服务器 客户端证书 .crt 客户端身份…...
echarts使用graphic强行给图增加一个边框(边框根据自己的图形大小设置)- 适用于无法使用dom的样式
pdf-lib https://blog.csdn.net/Shi_haoliu/article/details/148157624?spm1001.2014.3001.5501 为了完成在pdf中导出echarts图,如果边框加在dom上面,pdf-lib导出svg的时候并不会导出边框,所以只能在echarts图上面加边框 grid的边框是在图里…...
