MySQL索引失效场景总结

发表于2020-02-28|数据库

|浏览量:

索引失效不是神秘现象，多是谓词写法、类型或统计信息让优化器放弃索引。

高频失效场景

对索引列使用函数或运算：WHERE YEAR(d)=2026。
隐式类型转换（见专题文）。
联合索引不满足最左前缀。
使用 !=、NOT IN、OR（部分可 merge，部分全扫）。
LIKE '%xx' 前缀通配。
优化器判断回表成本高于全表扫（小表或选择性差）。

验证方法

1
2
3

EXPLAIN SELECT * FROM t WHERE ...;
SHOW INDEX FROM t;
ANALYZE TABLE t;

优化思路

改写 SQL 让谓词「索引用得上」；增加覆盖索引减少回表；必要时分区或归档冷数据。

优化器成本模型

rows 估算是统计信息驱动，数据分布突变后需 ANALYZE。histogram（8.0）改善选择性估计。

索引合并

index merge union/intersection 有时替代联合索引，但不一定稳定。

实践复习清单

收集 ten 条失效 SQL 改写对照；ANALYZE 后 replan；覆盖索引设计练习；filesort 告警；冗余索引清理；最左前缀 violations 扫描。

常见坑

见 key 列有值就认为走索引，忽略 rows 与 Extra 中 Using where/filesort。
盲目加索引导致写入变慢与优化器选择混乱。

总结与自测

背出八种失效场景；会用 EXPLAIN 验证；知道何时 statistics 过期；理解优化器选全表扫原因。每周选一条生产慢 SQL 做失效分析练习，进步最快。

原理延伸

InnoDB 的核心竞争力在于把 B+ 树索引、缓冲池、redo/undo 与 MVCC 组合成可预测的 OLTP 引擎。读路径尽量走缓冲池与覆盖索引，写路径则要在行锁、日志刷盘与复制延迟之间取平衡。排查问题时不要只盯单条 SQL，还要看事务边界、锁等待、buffer pool 命中率与磁盘 fsync 延迟。性能优化永远遵循「先正确、再可测、后优化」：用慢日志和 EXPLAIN 定位瓶颈，用规范设计减少回表与锁竞争，用架构手段（读写分离、缓存、分片）承接流量，而不是在未测量前堆参数。

文章作者: Dai Wei

文章链接: https://www.daiwei.biz/knowledge/database/mysql/mysql-index-invalidation/

版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Dai Wei！

MySQL 索引失效

扫码打赏

微信
支付宝

相关推荐

MySQL查询缓存详解

Query Cache 曾是「相同 SQL 直接返结果」的尝试，但因锁竞争与失效粒度粗，已在 8.0 彻底移除。曾经如何工作5.7 及以前，SELECT 命中缓存则跳过解析执行；任何表写操作会使涉及该表的缓存条目失效。缓存 key 由 SQL 文本+库+字符集等构成。为何被弃用表级失效：写多读少场景命中率极低。全局锁竞争：高并发下 cache lock 成为瓶颈。内存碎片与维护成本高于收益。现代替代应用层/Redis 缓存热点对象。 InnoDB buffer pool 缓存数据页（与 SQL 文本无关）。 8.0 移除后 query_cache_type 等变量不再存在。实践建议别在旧文档里找 query cache 调优；把精力放在 SQL 优化、合适索引与外部缓存策略。历史调优误区曾建议调 query_cache_size，现应彻底移除相关配置项。文档与旧博客需甄别 MySQL 版本。应用侧缓存键设计缓存键应含业务维度与版本，避免发布后读旧结构对象。实践复习清单确认 MySQL 版本无 query cache；应用缓存键规范；buff...

一千行 MySQL 学习笔记

长篇笔记的价值在于「可检索的操作清单」。本文按连接、DDL/DML、权限与状态查看重组，方便日常查阅。连接与基础操作12345mysql -h127.0.0.1 -P3306 -uroot -pSHOW DATABASES;USE app;SHOW TABLES;DESC users; DDL 与 DML 要点建表时明确字符集、主键策略与必要索引；更新删除务必带 WHERE 并先 SELECT 验证影响行数。批量变更大表用 pt-online-schema-change 或 gh-ost，避免长时间锁表。用户与权限最小权限原则：应用账号仅授予所需库的 SELECT/INSERT/UPDATE/DELETE；DDL 与 SUPER 权限隔离给运维账号。状态诊断入口123SHOW PROCESSLIST;SHOW ENGINE INNODB STATUS\GSHOW VARIABLES LIKE 'innodb%'; 常用信息_schema 查询查表大小、索引Cardinality、未使用索引等可从 infor...

数据库知识体系：SQL、MySQL、Redis、MongoDB 与 Elasticsearch

后端工程师的数据库能力往往决定系统上限。本文把关系型、缓存与 NoSQL 串成一条可执行的学习主线。学习主线怎么排建议按「通用概念 → SQL 基本功 → MySQL 内核 → Redis 工程化 → 按需补 NoSQL」推进。先搞清 ACID、范式、索引与事务，再进入执行计划与日志；缓存部分重点理解穿透/击穿/雪崩与一致性策略，而不是死记命令。各模块要抓什么 SQL：会写不等于会优化，连接、子查询与聚合的语义要能和执行计划对应。 MySQL：索引、隔离级别、MVCC、redo/undo/binlog 是一条链，慢 SQL 排查从这里串。 Redis：数据结构选型、持久化、阻塞排查、集群分片是面试与线上并重的高频区。 MongoDB/ES：按岗位补充，文档模型与倒排索引各自解决不同检索场景。实践建议每学一个主题，用一个小例子验证：建表加索引看 EXPLAIN；开两个会话验证隔离级别；用 Redis 实现缓存并故意制造击穿场景。只看不练，面试和排障都容易卡壳。常见误区把「会用 ORM」当成「懂数据库」。索引越多越好...

MySQL索引详解

索引是 InnoDB 性能的第一杠杆。理解 B+ 树与聚簇/二级索引差异，设计才不会拍脑袋。 B+ 树为何适合磁盘多路平衡、叶子链表便于范围扫；非叶只存键减少 IO。InnoDB 聚簇索引叶子存整行，二级索引叶子存主键值需回表。最左前缀与覆盖索引联合索引 (a,b,c) 可支持 a、a+b、a+b+c 前缀查询；覆盖索引指 SELECT 列全在索引内，Extra 出现 Using index。 123CREATE INDEX idx_user_status ON orders(user_id, status, created_at);-- 覆盖列尽量放进索引SELECT user_id, status, created_at FROM orders WHERE user_id=1 AND status=1; 索引下推 ICP5.6+ 在存储引擎层先过滤二级索引条件，减少回表次数。设计原则高选择性列在前；控制宽度；避免重复索引；监控 duplicate index。前缀索引与区分度INDEX(name(10)) 要算 distinct prefix 选择性。过...

MySQL数据同步到Elasticsearch详解：常见方案与一致性处理

搜索与分析常需要 ES，但主库仍是 MySQL。关键是选对接同步通道并设计好一致性边界。常见方案方案优点风险应用双写简单不一致、失败补偿复杂定时扫库易实现延迟大、扫库压力 Canal/Debezium 近实时、解耦运维与顺序性要设计 Flink CDC 流式清洗聚合架构较重一致性策略以 MySQL 为准；ES 作最终一致读模型。失败重试+死信队列；全量重建与增量消费分离；版本号或 ts 防乱序覆盖。实践建议12PUT /orders/_doc/1001{ "order_id": 1001, "status": "PAID", "updated_at": "2026-09-02T10:00:00Z" } mapping 提前定义；bulk 写入；监控 lag 与 rejected 任务。映射与分词mapping 中 keyword vs text 决定聚合与搜索；中文分词器选型影响召回。全量 ...

MySQL隐式转换造成索引失效

WHERE 明明有索引却全表扫描，经常是列类型与常量类型不一致触发隐式转换。机制说明当比较两侧类型不同时，MySQL 按转换规则把一侧转为另一侧。若对索引列做函数或转换，优化器往往无法使用该索引，因为索引存的是原始值顺序。典型场景123456789-- phone 为 VARCHAR，常量写数字 → 列被转数字，索引失效SELECT * FROM user WHERE phone = 13800138000;-- 正确写法SELECT * FROM user WHERE phone = '13800138000';-- 对列做函数同样失效SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';-- 改为范围查询SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'; 排查步骤EXPLAIN 看 type&#x...

评论

数据加载中