MySQL索引优化实战：从B+树原理到慢SQL调优

搞数据库的都知道线上业务卡了第一反应不是加机器而是回头看看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 ANALYZEMySQL 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) ) ENGINEInnoDB;线上反馈某条查询很慢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 复合索引的列顺序决定成败最核心的原则是等值条件的列放前面范围条件的列放后面。针对上面的SQLuser_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_createuser_id, create_time之后由于user_id等值命中了索引的前缀列create_time这一列在索引中是“排好序的”。MySQL可以直接按索引顺序往回读不需要额外的filesort步骤。这里有个细节容易被忽略DESC和ASC在MySQL 8.0中可以通过索引反向扫描实现所以正向建的索引也能处理DESC排序。日常开发不需要为了排序方向专门建两个索引。但如果倒序和正序在业务中都很高频并且还有多列混合排序一列ASC另一列DESCMySQL 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 PushdownICP是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_switchindex_condition_pushdownon。虚拟列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*41020字节几个字段叠加很容易超限。所以设计表结构时要考虑哪个字段值得做前缀索引。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和执行计划前后的对比记录到一个专门的文档里。不要觉得这是额外工作三个月后同样模式的问题再出现时翻一下笔记基本能省掉半天排查时间。索引优化这件事经验积累的比重比任何工具都大。