搞数据库的都知道，线上业务卡了，第一反应不是加机器，而是回头看看SQL和索引。MySQL里索引优化可以说是一个永远聊不完的话题，但也是回报率最高的一项调优手段。一条慢SQL从5秒优化到50毫秒，很多时候不需要改一行业务代码，只要把索引建对就行。
这篇文章我就结合自己这些年踩过的坑，把MySQL索引从设计思路到落地实操完整梳理一遍。不是给你罗列“索引失效的N种情况”这种面面俱到但你记不住的口诀，而是讲清楚索引在MySQL里到底怎么工作、我们面对一个具体的慢SQL时应该按什么顺序去分析、去设计索引。这个过程适合刚接触MySQL的开发者，也适合写过一阵子SQL但总觉得索引这块“会建不会调”的朋友。
1. 索引优化的底层逻辑：先理解B+树在解决什么问题
很多人一上来就背“左前缀原则”“覆盖索引”“索引下推”，但这些名词背后其实只回答一个问题：MySQL怎么用最少的时间找到你想要的那几行数据。
我习惯用一个类比来解释索引：一本500页的技术书，你要找“覆盖索引”这个词的解释。没有目录的话，你得从第1页翻到第500页，这个就是全表扫描。有了目录，你先翻到目录页，定位到这个词在第247页，然后直接翻过去，这个就是索引查找。B+树在MySQL里扮演的就是这个“目录”的角色，只不过它比纸质目录更聪明——它能通过二分查找快速定位，而且叶子节点之间用指针串联，做范围查询的时候顺着链表一路往后读就行。
1.1 为什么索引能大幅提升查询性能
要理解索引的价值，得先搞清楚MySQL查数据的最小单位是“页”，默认16KB。假设一张表有1000万行数据，每行大概200字节，加上页之间的空洞，大概需要15万多个数据页。全表扫描意味着要读十几万个页；而如果你在某个高区分度的列上建了索引，B+树的高度通常只有3到4层，意味着只需要读3到4个索引页，就能定位到目标数据页的位置。
这就是几个数量级的差距。而且InnoDB的索引结构本身就是按主键聚簇的，数据行直接挂在主键索引的叶子节点上，所以通过主键查询通常是最快的路径——一次索引树查找，直接拿到整行数据，不需要额外的“回表”操作。
1.2 索引不是免费的午餐
我在实际工作里见过不少矫枉过正的案例：业务开发为了“性能保险”，把所有可能用到的列都建上索引，一张表搞出七八个索引。结果写入变慢、磁盘占用飙升、优化器反而不知道该选哪个索引。
每次写入（INSERT/UPDATE/DELETE）不仅要更新数据页，还要同步维护这张表上的所有索引。索引越多，写入放大越严重。更重要的是，MySQL优化器会基于统计信息估算各个索引的代价，索引太多反而可能选错执行计划。
所以说索引优化的第一步不是“怎么建索引”，而是“什么时候不建索引”。低区分度列（比如性别）、几乎不会被作为查询条件的列、数据量很小的表，这些场景建索引要么没用，要么性价比极低。
2. 设计索引前的准备工作：从慢查询日志开始
很多人拿到慢SQL直接就开始写CREATE INDEX语句，这是典型的跳过诊断直接开药。我自己的习惯是：先搞清楚这个SQL是怎么执行的，再判断瓶颈在哪，最后才决定建什么样的索引。
2.1 开启慢查询日志，找到真正的目标
MySQL自带慢查询日志，这是最直接的性能问题入口。一般在开发环境或者低峰期的生产环境，可以临时开启：
-- 查看当前慢查询设置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 开启慢查询日志，阈值设为1秒 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;注意long_query_time设完之后，已经打开的连接不会立即生效，需要新连接才按新阈值记录。另外建议同时打开log_queries_not_using_indexes，把那些没走索引的查询也记下来，它们往往是潜在隐患：
SET GLOBAL log_queries_not_using_indexes = 'ON';拿到慢查询日志之后，不要急着优化所有慢语句。先按两个维度排序：执行次数和单次耗时。有些SQL虽然单次只要200毫秒，但每秒调用上百次，累计消耗非常可观；有些SQL单次要5秒，但一个月才跑一次（比如月底报表），优先级反而没那么高。
2.2 用EXPLAIN建立执行计划的基线
定位到目标SQL后，用EXPLAIN看执行计划，注意不是EXPLAIN的结果，而是EXPLAIN ANALYZE，MySQL 8.0.18及以上版本支持：
EXPLAIN ANALYZE SELECT order_id, user_id, status FROM orders WHERE user_id = 1024 AND create_time > '2024-06-01' ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE会真实执行SQL并返回每个步骤耗时、扫描行数、实际循环次数。相比传统EXPLAIN只给估算值，它能直接告诉我们时间到底花在哪个环节：是回表太多，还是排序太贵，或者是扫描行数远超预期。
这一步最重要的产出是一个“基线”。优化完之后再跑一次同样的EXPLAIN ANALYZE，对比扫描行数和耗时，就能确认优化是否真的有效。
3. 高效索引设计的核心实操：从单列到复合索引
准备工作做完，接下来是核心环节：设计索引。这里我会结合一个电商订单表场景来讲。假设表结构大致长这样：
CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, seller_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, pay_time DATETIME DEFAULT NULL, KEY idx_user_id (user_id), KEY idx_create_time (create_time) ) ENGINE=InnoDB;线上反馈某条查询很慢，SQL大概是这样的：
SELECT order_id, amount, status FROM orders WHERE user_id = 1024 AND create_time > '2024-06-01' ORDER BY create_time DESC LIMIT 20;这条SQL看似简单，但已经有索引利用不充分的嫌疑。
3.1 复合索引的列顺序决定成败
最核心的原则是：等值条件的列放前面，范围条件的列放后面。
针对上面的SQL，user_id是等值条件，create_time是范围条件。所以复合索引应该这样建：
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);为什么是这个顺序？因为B+树索引本身是“从左到右”逐层精确定位的。等值条件可以把“搜索范围”缩到最小，然后在这个范围内再走范围条件，效率最高。反过来建（create_time放前面），优化器很可能会跳过这个索引，因为等值条件user_id在后面的列上，没办法感知，索引利用率大打折扣。
这也解释了为什么很多时候你给两个列分别建了单列索引，但查询还是慢。MySQL的优化器虽然能做Index Merge（即合并两个单列索引的扫描结果），但在大多数场景下效率不如一个设计良好的复合索引。
3.2 覆盖索引：让查询彻底告别回表
上面那条SQL还有个隐藏的优化点，就是查询涉及的列：order_id、amount、status，这三个字段都不在索引里。也就是说，即使通过idx_user_create定位到叶子节点，MySQL还得拿着主键去聚簇索引里回表，把这三个字段读出来。
如果这个查询非常高频，我们可以设计一个覆盖索引，把查询和过滤涉及的列都放进去：
ALTER TABLE orders ADD INDEX idx_user_create_cover (user_id, create_time, amount, status);这样索引的叶子节点上就已经有amount和status了，查询需要的所有数据都能从索引页直接拿到，回表操作全省了。注意order_id其实可以从主键拿，所以不用冗余进去。
覆盖索引的代价是索引会更大，写入更慢。所以我的做法是：只对高频且固定的查询做覆盖索引，宁缺毋滥。低频报表查询、字段经常变化的场景，不建议做。
3.3 排序优化：让索引替代文件排序
上面SQL里有ORDER BY create_time DESC。在MySQL中，如果ORDER BY的列不在索引中，或者排序顺序和索引扫描顺序不一致，就会触发filesort（文件排序）。文件排序不是不能用，但当排序数据量大时，它会把数据写到磁盘临时文件，开销非常大。
有了idx_user_create（user_id, create_time）之后，由于user_id等值命中了索引的前缀列，create_time这一列在索引中是“排好序的”。MySQL可以直接按索引顺序往回读，不需要额外的filesort步骤。
这里有个细节容易被忽略：DESC和ASC在MySQL 8.0中可以通过索引反向扫描实现，所以正向建的索引也能处理DESC排序。日常开发不需要为了排序方向专门建两个索引。但如果倒序和正序在业务中都很高频，并且还有多列混合排序（一列ASC另一列DESC），MySQL 8.0支持建“降序索引”：
ALTER TABLE orders ADD INDEX idx_user_create_mixed (user_id ASC, create_time DESC);大部分业务用不上这种索引，了解即可。
3.4 前缀索引：压缩大字段索引的体积
有时候我们需要给order_no这种VARCHAR(64)字段建索引。如果order_no整体很长，又只需要做等值匹配，那么可以考虑用前缀索引：
ALTER TABLE orders ADD INDEX idx_order_no_prefix (order_no(20));这里20表示只取前20个字符建索引。前缀索引能显著缩减索引体积，但有两个限制：一是它不能用于覆盖索引优化（因为索引里存的不是完整列值）；二是区分度需要验证。验证方法是分别统计全列的区分度和前缀的区分度：
-- 统计全列的基数 SELECT COUNT(DISTINCT order_no) / COUNT(*) FROM orders; -- 统计前20个字符的基数 SELECT COUNT(DISTINCT LEFT(order_no, 20)) / COUNT(*) FROM orders;两个比值接近，说明前缀长度够用。如果差距很大，说明前缀取短了，很容易碰撞出大量重复项。
3.5 唯一索引与普通索引的选择
从业务正确性角度看，order_no这种业务单号必须加唯一约束，理由不是性能，而是数据质量。从性能角度看，唯一索引在查询时多了一个“找到即停”的优化：普通索引在定位到第一条满足条件的记录后，还需要继续扫描下一条来判断是否结束；唯一索引因为逻辑上不会有重复值，找到第一条就可以直接返回。这个差距在数据量大且重复率高时会比较明显。
但写入性能上唯一索引是有代价的：每次插入或更新都要额外做一次唯一性检查，所以如果业务上并不需要唯一约束，就不要为了“查询快一点”强行加唯一索引。
3.6 索引下推和虚拟列：两个容易被忽略的高级功能
索引下推（Index Condition Pushdown，ICP）是MySQL 5.6引入的特性。简单说，在没有ICP之前，回表后才会去过滤未索引的字段；有了ICP，存储引擎可以在索引遍历过程中直接把不满足条件的记录跳过。这对复合索引中“没被用上的后续列”特别有效。
比如索引是（user_id, create_time），你执行WHERE user_id = 1024 AND amount > 100。amount不在索引里，但ICP可以在读取索引记录时先用user_id和create_time的索引定位，再在索引层面尽量提前判断可以过滤的索引字段；amount这些非索引字段的过滤依然要回表后做，但如果create_time条件是范围，它的过滤在索引层做已经能减少很多回表。这个特性默认开启（optimizer_switch='index_condition_pushdown=on'）。
虚拟列（generated column）适合处理“需要在一个表达式结果上查询”的场景。比如你想根据pay_time和create_time的差值筛选超时订单：
-- 添加虚拟列 ALTER TABLE orders ADD COLUMN payment_cost_seconds INT AS (TIMESTAMPDIFF(SECOND, create_time, IFNULL(pay_time, NOW()))) VIRTUAL; -- 对虚拟列建索引 ALTER TABLE orders ADD INDEX idx_payment_cost (payment_cost_seconds);虚拟列不占用InnoDB物理存储空间，查询时可以直接拿它当普通列来过滤，同时又能走索引，灵活度很高。不过要注意，虚拟列上的索引依赖MySQL 8.0对函数结果稳定性的判断，如果你用的是NOW()这种非常量函数，虚拟列不能建索引。
4. 实战复盘：一个慢查询从定位到优化的完整过程
前面讲了很多原则，这个章节我用一个真实场景走一遍完整流程，你会发现很多问题不是“不会建索引”，而是“没看清SQL的全貌”。
4.1 场景还原：后台订单列表查询卡顿
运营后台的订单列表页，筛选条件多、变化频繁，其中有一个组合条件的响应时间特别离谱，单次查询接近4秒。简化后的SQL如下：
SELECT id, order_no, user_id, amount, status, create_time FROM orders WHERE seller_id = 30211 AND status = 1 AND create_time >= '2024-05-01' AND create_time < '2024-06-01' ORDER BY create_time DESC LIMIT 30;orders表当时的数据量是3000万行左右，之前已经有一些单列索引（seller_id、status、create_time各一个）。EXPLAIN ANALYZE显示，这个查询实际扫描了约80万行，进行了filesort，回表次数惊人。
为什么会扫80万行？我当时看到执行计划里用的是idx_create_time，也就是单独走create_time的索引。因为条件里create_time是范围，优化器觉得先用时间过滤能筛掉大量数据，然后回表再过滤seller_id和status。5月份这一个月就有约80万行订单，所以这80万行全部被回表了一遍，filtered之后只剩下几百行符合seller_id和status条件。
这就是典型的“单列索引各自为政”导致的问题。这种情况下，无论你单独优化哪个单列索引，都无法根治扫描行数过大的问题。
4.2 第一步优化：建立复合索引
按照“等值在前，范围在后”的原则，等值条件是seller_id和status，范围条件是create_time。两个等值条件之间，谁放前面？
我用区分度来定：seller_id在店铺维度上的选择性明显更高（一个卖家在某时间段内的订单数量远小于状态1在总量中的占比），所以把seller_id放最前面：
ALTER TABLE orders DROP INDEX idx_seller_id; ALTER TABLE orders DROP INDEX idx_status; ALTER TABLE orders DROP INDEX idx_create_time; ALTER TABLE orders ADD INDEX idx_seller_status_time (seller_id, status, create_time);重新执行EXPLAIN ANALYZE，扫描行数从80万降到几万，filesort消失了——因为create_time已经在索引末尾，排序可以直接利用索引序。查询耗时从4秒降到了300毫秒左右。
4.3 第二步优化：分析是否值得做覆盖索引
300毫秒对于运营后台来说其实已经能用了，但因为是高频页面，我又继续看了一眼查询字段，发现SELECT列表里有order_no、amount这类的非索引字段。如果查询固定，可以考虑做覆盖索引：
ALTER TABLE orders ADD INDEX idx_seller_status_time_cover (seller_id, status, create_time, order_no, amount);这个索引的大小要比之前大不少，但查询可以直接从索引页返回所有字段，连聚簇索引都不用碰。实测下来这个SQL的耗时从300毫秒进一步降到了50毫秒以内。
注意这里我保留了原来的idx_seller_status_time，其实没有太大必要——覆盖索引的左侧前缀已经覆盖了原索引的功能。但我在生产环境是分两步操作：先加覆盖索引、观察一段时间的写入和存储影响，再决定是否删除旧索引，避免一步到位出问题没有快速回退方案。
4.4 翻车经验：一次优化失败带来的教训
在上面这个案例里我还踩过另一个坑。最初我以为status区分度太差（只有0/1/2三种值），建复合索引时把status排到了最后，建了（seller_id, create_time, status）这样的索引。结果跑了EXPLAIN发现，虽然也能用上索引，但MySQL在查询时对“范围条件后的等值条件”没法继续精确匹配，等于create_time范围之后，status只能当普通过滤条件用，回表的行数比预期多不少。
等值条件在范围条件之前才能被索引用于精确定位；等值条件在范围条件之后，就退化成普通过滤条件了。这点非常容易忽视，尤其当你有多个范围条件和一个等值条件时，一定要把等值条件放在最前面。
5. 索引优化高频问题与避坑指南
这节整理几个我经常在团队里被问到的问题，每个都对应过真实的故障或性能事故。
5.1 隐式类型转换导致索引失效
这是最常见的“索引明明建了却不走”的原因。典型场景是user_id是BIGINT，但业务代码里传参传成了字符串；或者order_no是VARCHAR，但SQL里直接写WHERE order_no = 10245555，没有加引号。
MySQL遇到字符串和数字比较时，会自动做类型转换。如果列是字符串类型、传入的数字，索引列上发生了隐式转换，优化器大概率会放弃索引。解决方法是统一参数类型，靠规范而不是靠索引来规避。
5.2 函数运算让索引白头忙活
对索引列做函数运算，同样会让索引失效。比如：
WHERE DATE(create_time) = '2024-06-01'这里create_time列被DATE函数包裹，索引无法用于范围定位。正确写法是：
WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02'包括对字段做加减乘除也一样，左值运算基本都能改写为右值运算（也就是对参数做运算，不对列做运算）。
5.3 LIKE模糊查询为什么慢
关于LIKE，最核心的一点是：只有“前缀匹配”才能走索引。
-- 可以走索引 WHERE order_no LIKE 'A1024%' -- 不能走索引 WHERE order_no LIKE '%A1024%'如果业务确实需要“包含”类搜索，不要强行建索引，考虑全文索引或外部搜索引擎方案会更合理。MySQL的全文索引（FULLTEXT）对中英文文本搜索有一定支持，但复杂查询场景成熟度不如专业搜索引擎，选型时要评估好。
5.4 低基数索引往往是负资产
说白了就是，性别列、状态列这种取值就几种的列，单独建索引基本没用。查询优化器看统计信息后，可能觉得直接扫全表比走索引回表更划算。索引的价值在于“快速缩小范围”，如果你的条件一筛就筛掉一半数据，索引就失去了意义。
这类低基数列在复合索引里也不是完全没用，但必须放在更优列的前面还是后面，要看它的过滤能力和数据分布。我的经验是：低基数列更适合放在最左侧作为分区裁剪（前提是业务查询必定带有它的等值条件），否则干脆不要进索引。
5.5 索引数量失控：一张表到底建几个索引合适
没有一个标准答案，但有一个经验法则：单表索引数量尽量控制在5个以内，复合索引尽量合并功能相似的列。索引和业务查询是“需求匹配”的关系，新增一个索引之前，先看看已有索引能否通过调整列顺序来满足新查询。
此外，索引长度也要控制。InnoDB单个索引键长度默认限制是3072字节，一个VARCHAR(255)且utf8mb4的列，255*4=1020字节，几个字段叠加很容易超限。所以设计表结构时，要考虑哪个字段值得做前缀索引。
5.6 主键选择对索引的影响被严重低估
InnoDB是聚簇索引组织表，数据按照主键顺序物理存储。自增主键可以保证新记录按顺序插入，减少页分裂。而UUID等随机主键会导致数据频繁在中间位置插入，页分裂率高，写放大明显，还会让所有二级索引的叶子节点变大（二级索引叶子节点存的是主键值）。
博主个人建议：日常业务表尽量用自增BIGINT做主键；需要分布式唯一ID的场景，也要考虑有序性，雪花ID这类带时间位的ID通常比纯随机UUID友好得多。
6. 从“建索引”到“管索引”：一些个人心得
最后聊点不太会被写进文档里的经验，但恰恰是这些经验让我在排查慢SQL时效率高了很多。
索引优化从来不是一次性的事。我见过太多团队上线时把索引建得漂漂亮亮，半年后表结构和查询逻辑变了，索引没人维护，性能又回去了。所以我会在团队里要求：任何表结构变更（新增字段、修改字段长度、删除字段），都必须同步review一遍相关SQL和索引。新增一个索引后，还要通过sys.schema_unused_indexes这类系统表关注它是否真的被用到，长期未使用的索引就是纯粹的浪费。
-- 查看从未使用过的索引 SELECT * FROM sys.schema_unused_indexes;另一个心得是不要迷信“万能索引”。没有哪种索引规则能覆盖所有SQL。优化器的执行计划受统计信息影响很大，所以每次优化后都要看看抽样数据量和执行计划是否超出预期。有时候表数据量到了亿级，普通索引已经不够用了，那时候要考虑分区表、归档表，甚至换存储引擎，但那是另一个话题。
我的习惯是每次做完索引优化，都把这个SQL和执行计划前后的对比记录到一个专门的文档里。不要觉得这是额外工作，三个月后同样模式的问题再出现时，翻一下笔记，基本能省掉半天排查时间。索引优化这件事，经验积累的比重比任何工具都大。
