做过几年的 MySQL 开发和 DBA 工作之后我越来越发现一个现象很多人写 SQL 很熟练但只要一遇到慢查询第一反应就是“加个索引试试”。索引这东西用好了确实是灵丹妙药用不好反而会拖垮写性能。所以这篇文章不打算像教科书那样从头到尾讲一遍索引定义我就直接结合这些年实际踩过的坑把 MySQL 索引从设计思路、实操落点到失效排查整个链路拆开来讲力求你看完能直接拿来用。这篇文章适合这几类人看刚入门 MySQL、搞不懂为什么明明建了索引查询还是慢的后端开发被线上慢查询逼到加班、却只会往表上乱加索引的运维同学以及准备面试、想系统梳理索引知识点的求职者。内容不啰嗦每一节都是能落地的经验尤其最后的失效场景排查基本覆盖了我这几年在真实环境里遇到过的绝大多数情况。1. 先弄清楚索引到底在解决什么问题1.1 索引的本质让数据库走捷径很多人都知道索引类似于书的目录这个类比没有错但我想往深处再走一步。书的目录帮你定位到某一页数据库的索引则帮你定位到某一行数据所在的物理位置。在没有索引的情况下MySQL 要做全表扫描——也就是把整张表的每一行都读一遍然后过滤出符合条件的记录。表小的时候无所谓但一旦数据量到百万、千万级别全表扫描的耗时几乎不可接受。索引的本质是拿额外的存储空间和维护成本换取查询时更少的磁盘 I/O 和更快的定位速度。我们常说“空间换时间”这是最核心的底层逻辑。每次插入、更新、删除数据时索引结构都要同步维护所以索引不是免费的午餐它是有代价的。1.2 从数据结构和磁盘IO角度理解为什么是B树MySQL 的 InnoDB 存储引擎默认索引结构是 B 树。为什么不是二叉树不是哈希表不是跳表这个问题的答案直接决定了你对索引机制的深度理解。先说哈希表。哈希索引的查询速度理论上是 O(1)但它只能做等值查询范围查询比如age 18就废了。而且哈希表是无序存储排序也帮不上忙。所以 InnoDB 并没有把哈希作为默认索引只在自适应哈希索引的场景下作为辅助优化。再说二叉树。二叉搜索树的查询效率是 O(log n)但这个复杂度是建立在树高度“矮”的前提下的。如果数据是递增插入的二叉树会退化成链表树的高度变得非常深。盘 I/O 是按页读的树每深一层就可能多一次 I/O。一个两千万行的表如果树高度是 20 多层查询一次就要做 20 多次磁盘 I/O这在生产环境里是灾难。B 树把多个数据放在一个节点里每个节点对应一个磁盘页默认 16KB一次 I/O 就能读入大量键值。它的核心特点有三个非叶子节点只存索引键不存真实数据所以一个节点能容纳更多键树更矮更宽。叶子节点之间通过双向指针串联范围查询和排序可以直接顺序遍历。所有数据都落在叶子节点查询路径稳定IO 次数基本等于树的高度。记住这几个特点后面理解联合索引、覆盖索引、索引下推都会容易很多。B 树的高度一般 3 到 4 层就能支撑千万级数据也就是说走索引查询通常只需要 3 到 4 次磁盘 I/O相比全表扫描性能差距是指数级的。2. MySQL索引的几种类型和选型逻辑2.1 主键索引、唯一索引、普通索引的差异MySQL 的索引类型很多但建索引之前你得先搞懂每种类型的定位。用错了轻则浪费空间重则影响写入性能。主键索引PRIMARY KEY每个 InnoDB 表只能有一个主键它的特点是不能为空且值唯一。InnoDB 是聚集索引组织表数据行本身就按主键的 B 树排序存储所以主键索引的叶子节点存的是整行数据。这就是为什么主键查询往往极快的原因——不需要回表直接拿到全部列。唯一索引UNIQUE KEY保证列或列组合的值唯一但允许 NULL 值存在多个 NULL 不算冲突。它在业务上的价值是防止重复数据比如用户表的手机号字段很适合加唯一索引。普通索引INDEX只加速查询不约束唯一性。它的叶子节点存的是主键值查询时如果需要的列不在索引里就要根据主键回表查整行。全文索引FULLTEXT专门做文本匹配用的在 MySQL 5.7 之后也支持中文分词。日常的LIKE %关键词%无法走普通索引如果业务确实需要全文检索要么上全文索引要么引入 ES 之类的搜索引擎。空间索引SPATIAL处理地理位置数据平时业务里用得很少这里就不展开了。2.2 联合索引设计与最左前缀原则真实业务里单列索引往往搞不定复杂查询。比如最常见的订单查询WHERE user_id ? AND status ? ORDER BY create_time DESC。这时候就轮到联合索引出场了。联合索引也叫多列索引本质上是把多个列按顺序拼成一个复合键存到一棵 B 树里。举个例子联合索引(user_id, status, create_time)它的排序逻辑是先按user_id排序user_id相同的再按status排序status也相同的继续按create_time排序。这个排序逻辑直接决定了最左前缀原则查询条件里必须包含联合索引最左边的列索引才会被用到。比如WHERE user_id ?能走索引。WHERE user_id ? AND status ?能走索引。WHERE status ?不能走索引。WHERE status ? AND user_id ?在 MySQL 优化器有条件下可能走索引因为优化器会做等价改写但这不代表你可以乱写条件顺序。联合索引的列顺序是设计的关键。通常遵循两个原则区分度高的列放前面比如用户ID、订单号这类列能快速缩小范围。经常用于排序、分组的列尽量包含进来让 B 树直接利用索引完成排序避免 filesort。这里还要重点说一个很多人忽略的问题联合索引不是建得越长越好。每多一个列索引占用的空间就大一圈插入和更新的维护成本也更高。我见过有人把一张表 20 个字段建了 8 个联合索引最后写入延迟高得吓人。索引设计一定要克制。2.3 全文索引和哈希索引的使用边界聊到哈希索引最容易踩坑的是把 MySQL 的哈希索引和 InnoDB 的自适应哈希索引混为一谈。InnoDB 的自适应哈希索引是存储引擎内部自动优化的你感知不到也不需要干预。而真正能手动创建的哈希索引只存在于 MEMORY 存储引擎里InnoDB 并不支持直接创建哈希索引。如果你确实需要等值查询的极致速度又不想引入 Redis可以考虑在表上冗余一个字段存哈希值然后用普通索引去索引这个哈希列。比如 URL 这种内容长又需要精确匹配的字段做法是加一个url_hash列存 CRC32 或 MD5 值再对这个列建索引。MySQL 8.0 还支持函数索引也可以直接对CRC32(url)建索引。不过这个方案只解决等值匹配范围查询还是得靠 B 树。全文索引现在用得也不少尤其是对长文本字段做站内搜索。但要注意全文索引并不是银弹它对短文本的效率一般分词器的效果也需要调。真正信息量大的全文搜索还是建议交给专业的搜索引擎。3. 添加索引的正确姿势与实操细节3.1 创建索引的标准语法与命名规范先给出一套可以直接抄的语法。-- 创建普通索引 ALTER TABLE order ADD INDEX idx_user_id (user_id); -- 创建唯一索引 ALTER TABLE user ADD UNIQUE KEY uk_mobile (mobile); -- 创建联合索引 ALTER TABLE order ADD INDEX idx_user_status_time (user_id, status, create_time); -- 删除索引 ALTER TABLE order DROP INDEX idx_user_id;如果你在建表时就确定索引也可以在CREATE TABLE语句里直接定义CREATE TABLE order ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_user_status_time (user_id, status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;索引命名这块团队之间最好统一规范。我常用的规则是普通索引用idx_前缀 业务字段名。唯一索引用uk_前缀 业务字段名。联合索引把多个列名缩写后用下划线连接比如idx_user_status_time。命名规范看起来是小事但等你接手一个几十张表的老项目看到一个索引叫index2或者Index_3想死的心都有。3.2 如何通过EXPLAIN判断索引是否生效索引建好了它到底有没有被查询走不能靠猜要用EXPLAIN看执行计划。EXPLAIN SELECT user_id, status FROM order WHERE user_id 100 AND status 1;执行结果里重点关注这几列type这是访问类型性能由好到差依次是systemconsteq_refrefrangeindexALL。如果看到ALL说明是全表扫描索引基本没起作用。key实际用到的索引名。如果为 NULL说明没走索引。rows预估扫描的行数。值越小越好如果扫描行数接近全表行数就要反思查询条件了。Extra这个列信息量很大。出现Using filesort说明排序没用上索引出现Using temporary说明用了临时表出现Using index说明是覆盖索引。举个例子假如之前那条 SQL 的type是ALLrows是 500 万说明这条 SQL 正在全表扫描 500 万行这就是慢查询的根源。加了联合索引之后再看type变成了refrows缩减到几十行这个优化效果肉眼可见。3.3 一个慢查询优化的完整案例这里分享一个真实业务里优化过的场景非常典型。有一张订单流水表数据量 1500 万左右线上突然出现一条慢查询耗时稳定在 3 秒以上。原 SQL 大概是这样的SELECT id, user_id, order_no, amount, status, create_time FROM order_flow WHERE status 1 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 10;表上原有的索引是idx_create_time (create_time)。用EXPLAIN查看后发现这条 SQL 走了create_time索引但rows依然高达几十万。原因也很简单status 1的数据非常多即使只查最近半年的数据也要在索引里扫描大量记录然后再回表过滤。当时我给的优化方案是建一个联合索引ALTER TABLE order_flow ADD INDEX idx_status_create_time (status, create_time);这背后的思路是先通过status快速定位到等于 1 的区间再在create_time上做范围扫描。因为联合索引已经包含了create_time的排序ORDER BY create_time DESC直接走索引就完成了不需要 filesort。优化后这条 SQL 的耗时从 3 秒降到 20 毫秒左右效果非常明显。这个案例其实告诉我们一个常见的经验当单列索引遇到多条件过滤时往往力不从心这时候要考虑把过滤条件里的多个列组合成联合索引。但注意也不能一上来就无脑加联合索引还需要结合实际业务里查询条件的频率来决定列的顺序。3.4 索引维护碎片、统计信息和冗余索引很多人对索引的认知停留在“建完就完了”其实索引也需要日常维护。第一个问题碎片。InnoDB 在频繁的插入、删除操作后索引页可能出现碎片导致扫描效率下降。碎片严重时可以使用OPTIMIZE TABLE重建表和索引。OPTIMIZE TABLE order_flow;但要注意这个操作会锁表在线业务要选在低峰期执行或者用 Percona Toolkit 里的pt-online-schema-change做在线操作。另外MySQL 8.0 之后OPTIMIZE TABLE对 InnoDB 的支持已经改善但仍然建议评估好窗口再执行。第二个问题统计信息过期。优化器要决定走哪个索引靠的是表的统计信息。当数据量大变之后统计信息如果太老优化器可能选错索引。不需要手动频繁操作ANALYZE TABLE可以重新收集统计信息ANALYZE TABLE order_flow;第三个问题冗余索引。这是最容易忽略的。比如你已经建了idx_user_status_time (user_id, status, create_time)又顺手建了idx_user (user_id)后者基本是冗余的。因为联合索引最左列就是user_id单独建一个user_id单列索引完全多余纯属浪费空间和维护成本。我常用的查冗余索引的姿势是看information_schema.statistics表配合sys.schema_redundant_indexes视图MySQL 5.7 起自带快速找出冗余索引。SELECT * FROM sys.schema_redundant_indexes;4. 索引失效的典型场景和排查技巧4.1 哪些写法会导致索引失效这部分是面试高频题也是日常开发最常踩的坑。我按高频程度排个序。第一类在索引列上做函数操作或计算。比如WHERE DATE(create_time) 2024-01-01这种写法导致索引失效是因为 MySQL 对列进行了函数转换B 树里存的原始值和函数结果不在同一个排序空间。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00第二类隐式类型转换。如果索引列是字符串类型但查询条件传的是数字MySQL 会将字符串转为数字然后索引就废了。比如-- mobile 是 varchar 类型 WHERE mobile 13800138000mobile列上明明有索引但因为类型转换全表扫描了。正确写法WHERE mobile 13800138000第三类LIKE 以通配符开头。LIKE %abc因为无法确定前缀B 树无从定位起点索引失效。但LIKE abc%是可以走索引的因为前缀是可确定的。第四类OR 条件里存在非索引列。比如WHERE user_id 100 OR status 1如果status没有索引优化器大概率选择全表扫描。正确做法是把status也加上索引或者用UNION改写。第五类联合索引违反最左前缀原则。前面已经专门讲过这里再强调一下联合索引(a, b, c)你只查b和c索引一定失效。第六类索引列参与范围查询的另一边。比如WHERE a 100 AND b 1如果联合索引是(a, b)那b的等值条件很可能用不上索引。原因是 B 树先按a排序a的范围扫描已经锁定了一个区间这个区间内b不是有序的。4.2 常见问题速查表把上面提到的坑整理成一张速查表方便你排查问题的时候直接对照。场景索引是否生效优化思路WHERE id 100主键等值生效无需处理WHERE user_id 100 AND status 1有联合索引生效确认联合索引列顺序WHERE status 1只有联合索引但没有最左列失效补单列索引或调整联合索引WHERE DATE(create_time) 2024-01-01失效改写成范围查询WHERE mobile 13800138000mobile是字符串失效修改参数类型或显式加引号WHERE name LIKE %张三%失效考虑全文索引或搜索引擎WHERE user_id 100 OR status 1status无索引失效给status加索引或用UNIONWHERE a 100 AND b 1联合索引(a,b)部分生效调整列顺序等值在前范围在后SELECT ... ORDER BY create_time DESC有create_time索引生效索引天然有序避免filesort这张表是我平时做慢查询分析时最快能派上用场的参考。建议你把它存下来写 SQL 之前对照一遍能省掉很多上线后才发现问题的尴尬。4.3 几条线下线上排查经验最后分享几条平时不怎么写进文档、但非常实用的排查经验。一是升级 MySQL 到 8.0 之后一定要用EXPLAIN ANALYZE验证实际耗时和行数。MySQL 8.0 的EXPLAIN ANALYZE会真正执行 SQL并输出实际执行时间和各步骤的实际行数比传统EXPLAIN的预估值准确得多。EXPLAIN ANALYZE SELECT id, user_id, status FROM order_flow WHERE user_id 100 AND status 1;输出里会标明每一步耗时直接能看出来瓶颈在哪一步。二是有些“失效”其实是优化器的主动选择。比如你查一个小表表里只有 100 行数据优化器算了一下觉得全表扫描比走索引更快于是EXPLAIN结果就是全表扫描。这种情况不算索引失效不需要强行走索引。除非你用FORCE INDEX强制指定否则优化器有最终决定权。三是注意回表次数。联合索引如果只写了两个列但查询要把整行数据都拿出来就必须回表。如果业务查询频繁且需要返回的列不多可以考虑把需要的列也加进联合索引形成覆盖索引让Extra列变成Using index。四是看索引基数Cardinality。你可以通过SHOW INDEX FROM 表名查看Cardinality字段的值。这个值反映索引列的区分度如果Cardinality很小说明索引列的重复值很多走索引的效果就不好。举个例子一个性别字段gender只有 0 和 1 两个值那对gender建索引几乎没有意义优化器大概率也不会走。5. 一些关于索引设计的个人体会说句实话索引设计没有银弹也没有一套规则能适配所有业务。同样的表结构和查询在 A 业务里适合的索引到 B 业务可能就成了负担。我个人的体会是索引设计一定要结合真实业务数据特征和查询模式来做不要凭感觉。刚接手一个项目时我习惯先开慢查询日志收集一周内的慢 SQL再结合业务方的高频查询清单统一做一轮索引设计。这样做的好处是不会为了一个一天只跑一次的后台统计任务给核心大表加一个影响写入性能的冗余索引。另外线上变更索引一定要走工单流程评估影响面。加索引在数据量大的表上可能会锁表或产生较大 IO 压力务必选在低峰期操作。MySQL 8.0 的 InnoDB 已经支持在线 DDL很多操作不会阻塞读写但仍然建议你先在测试环境用生产数据量做一次演练确认耗时和资源消耗。最后还想强调一点索引是优化手段不是万能药。当你发现一条 SQL 怎么优化索引都效果有限时退一步想想是不是表结构设计本身就有问题是不是查询逻辑写得过于复杂是不是应该做数据归档或拆分。索引优化永远只是整个数据库性能优化里的一环把它放在合适的位置才能发挥最大的价值。 MySQL索引优化实战：从设计到失效排查全攻略
做过几年的 MySQL 开发和 DBA 工作之后我越来越发现一个现象很多人写 SQL 很熟练但只要一遇到慢查询第一反应就是“加个索引试试”。索引这东西用好了确实是灵丹妙药用不好反而会拖垮写性能。所以这篇文章不打算像教科书那样从头到尾讲一遍索引定义我就直接结合这些年实际踩过的坑把 MySQL 索引从设计思路、实操落点到失效排查整个链路拆开来讲力求你看完能直接拿来用。这篇文章适合这几类人看刚入门 MySQL、搞不懂为什么明明建了索引查询还是慢的后端开发被线上慢查询逼到加班、却只会往表上乱加索引的运维同学以及准备面试、想系统梳理索引知识点的求职者。内容不啰嗦每一节都是能落地的经验尤其最后的失效场景排查基本覆盖了我这几年在真实环境里遇到过的绝大多数情况。1. 先弄清楚索引到底在解决什么问题1.1 索引的本质让数据库走捷径很多人都知道索引类似于书的目录这个类比没有错但我想往深处再走一步。书的目录帮你定位到某一页数据库的索引则帮你定位到某一行数据所在的物理位置。在没有索引的情况下MySQL 要做全表扫描——也就是把整张表的每一行都读一遍然后过滤出符合条件的记录。表小的时候无所谓但一旦数据量到百万、千万级别全表扫描的耗时几乎不可接受。索引的本质是拿额外的存储空间和维护成本换取查询时更少的磁盘 I/O 和更快的定位速度。我们常说“空间换时间”这是最核心的底层逻辑。每次插入、更新、删除数据时索引结构都要同步维护所以索引不是免费的午餐它是有代价的。1.2 从数据结构和磁盘IO角度理解为什么是B树MySQL 的 InnoDB 存储引擎默认索引结构是 B 树。为什么不是二叉树不是哈希表不是跳表这个问题的答案直接决定了你对索引机制的深度理解。先说哈希表。哈希索引的查询速度理论上是 O(1)但它只能做等值查询范围查询比如age 18就废了。而且哈希表是无序存储排序也帮不上忙。所以 InnoDB 并没有把哈希作为默认索引只在自适应哈希索引的场景下作为辅助优化。再说二叉树。二叉搜索树的查询效率是 O(log n)但这个复杂度是建立在树高度“矮”的前提下的。如果数据是递增插入的二叉树会退化成链表树的高度变得非常深。盘 I/O 是按页读的树每深一层就可能多一次 I/O。一个两千万行的表如果树高度是 20 多层查询一次就要做 20 多次磁盘 I/O这在生产环境里是灾难。B 树把多个数据放在一个节点里每个节点对应一个磁盘页默认 16KB一次 I/O 就能读入大量键值。它的核心特点有三个非叶子节点只存索引键不存真实数据所以一个节点能容纳更多键树更矮更宽。叶子节点之间通过双向指针串联范围查询和排序可以直接顺序遍历。所有数据都落在叶子节点查询路径稳定IO 次数基本等于树的高度。记住这几个特点后面理解联合索引、覆盖索引、索引下推都会容易很多。B 树的高度一般 3 到 4 层就能支撑千万级数据也就是说走索引查询通常只需要 3 到 4 次磁盘 I/O相比全表扫描性能差距是指数级的。2. MySQL索引的几种类型和选型逻辑2.1 主键索引、唯一索引、普通索引的差异MySQL 的索引类型很多但建索引之前你得先搞懂每种类型的定位。用错了轻则浪费空间重则影响写入性能。主键索引PRIMARY KEY每个 InnoDB 表只能有一个主键它的特点是不能为空且值唯一。InnoDB 是聚集索引组织表数据行本身就按主键的 B 树排序存储所以主键索引的叶子节点存的是整行数据。这就是为什么主键查询往往极快的原因——不需要回表直接拿到全部列。唯一索引UNIQUE KEY保证列或列组合的值唯一但允许 NULL 值存在多个 NULL 不算冲突。它在业务上的价值是防止重复数据比如用户表的手机号字段很适合加唯一索引。普通索引INDEX只加速查询不约束唯一性。它的叶子节点存的是主键值查询时如果需要的列不在索引里就要根据主键回表查整行。全文索引FULLTEXT专门做文本匹配用的在 MySQL 5.7 之后也支持中文分词。日常的LIKE %关键词%无法走普通索引如果业务确实需要全文检索要么上全文索引要么引入 ES 之类的搜索引擎。空间索引SPATIAL处理地理位置数据平时业务里用得很少这里就不展开了。2.2 联合索引设计与最左前缀原则真实业务里单列索引往往搞不定复杂查询。比如最常见的订单查询WHERE user_id ? AND status ? ORDER BY create_time DESC。这时候就轮到联合索引出场了。联合索引也叫多列索引本质上是把多个列按顺序拼成一个复合键存到一棵 B 树里。举个例子联合索引(user_id, status, create_time)它的排序逻辑是先按user_id排序user_id相同的再按status排序status也相同的继续按create_time排序。这个排序逻辑直接决定了最左前缀原则查询条件里必须包含联合索引最左边的列索引才会被用到。比如WHERE user_id ?能走索引。WHERE user_id ? AND status ?能走索引。WHERE status ?不能走索引。WHERE status ? AND user_id ?在 MySQL 优化器有条件下可能走索引因为优化器会做等价改写但这不代表你可以乱写条件顺序。联合索引的列顺序是设计的关键。通常遵循两个原则区分度高的列放前面比如用户ID、订单号这类列能快速缩小范围。经常用于排序、分组的列尽量包含进来让 B 树直接利用索引完成排序避免 filesort。这里还要重点说一个很多人忽略的问题联合索引不是建得越长越好。每多一个列索引占用的空间就大一圈插入和更新的维护成本也更高。我见过有人把一张表 20 个字段建了 8 个联合索引最后写入延迟高得吓人。索引设计一定要克制。2.3 全文索引和哈希索引的使用边界聊到哈希索引最容易踩坑的是把 MySQL 的哈希索引和 InnoDB 的自适应哈希索引混为一谈。InnoDB 的自适应哈希索引是存储引擎内部自动优化的你感知不到也不需要干预。而真正能手动创建的哈希索引只存在于 MEMORY 存储引擎里InnoDB 并不支持直接创建哈希索引。如果你确实需要等值查询的极致速度又不想引入 Redis可以考虑在表上冗余一个字段存哈希值然后用普通索引去索引这个哈希列。比如 URL 这种内容长又需要精确匹配的字段做法是加一个url_hash列存 CRC32 或 MD5 值再对这个列建索引。MySQL 8.0 还支持函数索引也可以直接对CRC32(url)建索引。不过这个方案只解决等值匹配范围查询还是得靠 B 树。全文索引现在用得也不少尤其是对长文本字段做站内搜索。但要注意全文索引并不是银弹它对短文本的效率一般分词器的效果也需要调。真正信息量大的全文搜索还是建议交给专业的搜索引擎。3. 添加索引的正确姿势与实操细节3.1 创建索引的标准语法与命名规范先给出一套可以直接抄的语法。-- 创建普通索引 ALTER TABLE order ADD INDEX idx_user_id (user_id); -- 创建唯一索引 ALTER TABLE user ADD UNIQUE KEY uk_mobile (mobile); -- 创建联合索引 ALTER TABLE order ADD INDEX idx_user_status_time (user_id, status, create_time); -- 删除索引 ALTER TABLE order DROP INDEX idx_user_id;如果你在建表时就确定索引也可以在CREATE TABLE语句里直接定义CREATE TABLE order ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_user_status_time (user_id, status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;索引命名这块团队之间最好统一规范。我常用的规则是普通索引用idx_前缀 业务字段名。唯一索引用uk_前缀 业务字段名。联合索引把多个列名缩写后用下划线连接比如idx_user_status_time。命名规范看起来是小事但等你接手一个几十张表的老项目看到一个索引叫index2或者Index_3想死的心都有。3.2 如何通过EXPLAIN判断索引是否生效索引建好了它到底有没有被查询走不能靠猜要用EXPLAIN看执行计划。EXPLAIN SELECT user_id, status FROM order WHERE user_id 100 AND status 1;执行结果里重点关注这几列type这是访问类型性能由好到差依次是systemconsteq_refrefrangeindexALL。如果看到ALL说明是全表扫描索引基本没起作用。key实际用到的索引名。如果为 NULL说明没走索引。rows预估扫描的行数。值越小越好如果扫描行数接近全表行数就要反思查询条件了。Extra这个列信息量很大。出现Using filesort说明排序没用上索引出现Using temporary说明用了临时表出现Using index说明是覆盖索引。举个例子假如之前那条 SQL 的type是ALLrows是 500 万说明这条 SQL 正在全表扫描 500 万行这就是慢查询的根源。加了联合索引之后再看type变成了refrows缩减到几十行这个优化效果肉眼可见。3.3 一个慢查询优化的完整案例这里分享一个真实业务里优化过的场景非常典型。有一张订单流水表数据量 1500 万左右线上突然出现一条慢查询耗时稳定在 3 秒以上。原 SQL 大概是这样的SELECT id, user_id, order_no, amount, status, create_time FROM order_flow WHERE status 1 AND create_time 2024-01-01 00:00:00 ORDER BY create_time DESC LIMIT 10;表上原有的索引是idx_create_time (create_time)。用EXPLAIN查看后发现这条 SQL 走了create_time索引但rows依然高达几十万。原因也很简单status 1的数据非常多即使只查最近半年的数据也要在索引里扫描大量记录然后再回表过滤。当时我给的优化方案是建一个联合索引ALTER TABLE order_flow ADD INDEX idx_status_create_time (status, create_time);这背后的思路是先通过status快速定位到等于 1 的区间再在create_time上做范围扫描。因为联合索引已经包含了create_time的排序ORDER BY create_time DESC直接走索引就完成了不需要 filesort。优化后这条 SQL 的耗时从 3 秒降到 20 毫秒左右效果非常明显。这个案例其实告诉我们一个常见的经验当单列索引遇到多条件过滤时往往力不从心这时候要考虑把过滤条件里的多个列组合成联合索引。但注意也不能一上来就无脑加联合索引还需要结合实际业务里查询条件的频率来决定列的顺序。3.4 索引维护碎片、统计信息和冗余索引很多人对索引的认知停留在“建完就完了”其实索引也需要日常维护。第一个问题碎片。InnoDB 在频繁的插入、删除操作后索引页可能出现碎片导致扫描效率下降。碎片严重时可以使用OPTIMIZE TABLE重建表和索引。OPTIMIZE TABLE order_flow;但要注意这个操作会锁表在线业务要选在低峰期执行或者用 Percona Toolkit 里的pt-online-schema-change做在线操作。另外MySQL 8.0 之后OPTIMIZE TABLE对 InnoDB 的支持已经改善但仍然建议评估好窗口再执行。第二个问题统计信息过期。优化器要决定走哪个索引靠的是表的统计信息。当数据量大变之后统计信息如果太老优化器可能选错索引。不需要手动频繁操作ANALYZE TABLE可以重新收集统计信息ANALYZE TABLE order_flow;第三个问题冗余索引。这是最容易忽略的。比如你已经建了idx_user_status_time (user_id, status, create_time)又顺手建了idx_user (user_id)后者基本是冗余的。因为联合索引最左列就是user_id单独建一个user_id单列索引完全多余纯属浪费空间和维护成本。我常用的查冗余索引的姿势是看information_schema.statistics表配合sys.schema_redundant_indexes视图MySQL 5.7 起自带快速找出冗余索引。SELECT * FROM sys.schema_redundant_indexes;4. 索引失效的典型场景和排查技巧4.1 哪些写法会导致索引失效这部分是面试高频题也是日常开发最常踩的坑。我按高频程度排个序。第一类在索引列上做函数操作或计算。比如WHERE DATE(create_time) 2024-01-01这种写法导致索引失效是因为 MySQL 对列进行了函数转换B 树里存的原始值和函数结果不在同一个排序空间。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00第二类隐式类型转换。如果索引列是字符串类型但查询条件传的是数字MySQL 会将字符串转为数字然后索引就废了。比如-- mobile 是 varchar 类型 WHERE mobile 13800138000mobile列上明明有索引但因为类型转换全表扫描了。正确写法WHERE mobile 13800138000第三类LIKE 以通配符开头。LIKE %abc因为无法确定前缀B 树无从定位起点索引失效。但LIKE abc%是可以走索引的因为前缀是可确定的。第四类OR 条件里存在非索引列。比如WHERE user_id 100 OR status 1如果status没有索引优化器大概率选择全表扫描。正确做法是把status也加上索引或者用UNION改写。第五类联合索引违反最左前缀原则。前面已经专门讲过这里再强调一下联合索引(a, b, c)你只查b和c索引一定失效。第六类索引列参与范围查询的另一边。比如WHERE a 100 AND b 1如果联合索引是(a, b)那b的等值条件很可能用不上索引。原因是 B 树先按a排序a的范围扫描已经锁定了一个区间这个区间内b不是有序的。4.2 常见问题速查表把上面提到的坑整理成一张速查表方便你排查问题的时候直接对照。场景索引是否生效优化思路WHERE id 100主键等值生效无需处理WHERE user_id 100 AND status 1有联合索引生效确认联合索引列顺序WHERE status 1只有联合索引但没有最左列失效补单列索引或调整联合索引WHERE DATE(create_time) 2024-01-01失效改写成范围查询WHERE mobile 13800138000mobile是字符串失效修改参数类型或显式加引号WHERE name LIKE %张三%失效考虑全文索引或搜索引擎WHERE user_id 100 OR status 1status无索引失效给status加索引或用UNIONWHERE a 100 AND b 1联合索引(a,b)部分生效调整列顺序等值在前范围在后SELECT ... ORDER BY create_time DESC有create_time索引生效索引天然有序避免filesort这张表是我平时做慢查询分析时最快能派上用场的参考。建议你把它存下来写 SQL 之前对照一遍能省掉很多上线后才发现问题的尴尬。4.3 几条线下线上排查经验最后分享几条平时不怎么写进文档、但非常实用的排查经验。一是升级 MySQL 到 8.0 之后一定要用EXPLAIN ANALYZE验证实际耗时和行数。MySQL 8.0 的EXPLAIN ANALYZE会真正执行 SQL并输出实际执行时间和各步骤的实际行数比传统EXPLAIN的预估值准确得多。EXPLAIN ANALYZE SELECT id, user_id, status FROM order_flow WHERE user_id 100 AND status 1;输出里会标明每一步耗时直接能看出来瓶颈在哪一步。二是有些“失效”其实是优化器的主动选择。比如你查一个小表表里只有 100 行数据优化器算了一下觉得全表扫描比走索引更快于是EXPLAIN结果就是全表扫描。这种情况不算索引失效不需要强行走索引。除非你用FORCE INDEX强制指定否则优化器有最终决定权。三是注意回表次数。联合索引如果只写了两个列但查询要把整行数据都拿出来就必须回表。如果业务查询频繁且需要返回的列不多可以考虑把需要的列也加进联合索引形成覆盖索引让Extra列变成Using index。四是看索引基数Cardinality。你可以通过SHOW INDEX FROM 表名查看Cardinality字段的值。这个值反映索引列的区分度如果Cardinality很小说明索引列的重复值很多走索引的效果就不好。举个例子一个性别字段gender只有 0 和 1 两个值那对gender建索引几乎没有意义优化器大概率也不会走。5. 一些关于索引设计的个人体会说句实话索引设计没有银弹也没有一套规则能适配所有业务。同样的表结构和查询在 A 业务里适合的索引到 B 业务可能就成了负担。我个人的体会是索引设计一定要结合真实业务数据特征和查询模式来做不要凭感觉。刚接手一个项目时我习惯先开慢查询日志收集一周内的慢 SQL再结合业务方的高频查询清单统一做一轮索引设计。这样做的好处是不会为了一个一天只跑一次的后台统计任务给核心大表加一个影响写入性能的冗余索引。另外线上变更索引一定要走工单流程评估影响面。加索引在数据量大的表上可能会锁表或产生较大 IO 压力务必选在低峰期操作。MySQL 8.0 的 InnoDB 已经支持在线 DDL很多操作不会阻塞读写但仍然建议你先在测试环境用生产数据量做一次演练确认耗时和资源消耗。最后还想强调一点索引是优化手段不是万能药。当你发现一条 SQL 怎么优化索引都效果有限时退一步想想是不是表结构设计本身就有问题是不是查询逻辑写得过于复杂是不是应该做数据归档或拆分。索引优化永远只是整个数据库性能优化里的一环把它放在合适的位置才能发挥最大的价值。