刚入行那几年我在一个电商项目的数据库里盯过一个慢查询一张八十多万行的订单表走的是「订单号 状态」的组合过滤SQL 一跑就是六秒多。当时我的第一反应是加内存、升配置带我的老哥看了一眼执行计划说了句「你这条语句在全表扫描」。加了一个联合索引之后同一条 SQL 掉到十几毫秒。那次之后我才真正意识到数据库索引这件事看着门槛很低一条CREATE INDEX谁都会敲但决定它到底管不管用的从来不是敲语句那一下而是建之前对字段的判断、建的时候对顺序的取舍以及建完之后对执行计划的验证。这篇内容我就把这几年的索引实操经验完整梳理一遍从 B 树的原理说到生产环境大表加索引的注意事项适合刚接触 SQL 优化的后端同学也适合做了几年但没系统梳理过索引的开发者对照着检查自己的库。1. 索引把查询变快的真实原因1.1 一次全表扫描的成本到底花在哪很多人知道「全表扫描慢」但说不出慢在哪。我给你算一笔账。假设一张订单表有 100 万行每行平均 200 字节那这张表的数据量就是 200MB 左右。数据库的存储引擎无论是 InnoDB 还是别的按页读取磁盘一页默认 16KB200MB 就是大约 12800 个数据页。一次不带索引条件的查询引擎要做的事情就是把这 12800 个页从头到尾读一遍每读到一行就拿出来跟你的WHERE条件比一下匹配就留下不匹配就丢掉。这里的关键在于磁盘 IO 的代价和内存计算的代价差了好几个数量级。读一个页即使命中了操作系统的文件缓存也要走一次内存拷贝和页解析如果没命中那就是一次真实的磁盘寻道加读取机械盘上动辄几毫秒SSD 也要几十微秒。12800 个页乘下来几百毫秒到几秒都很正常而且这只是单次查询。并发一上来这些页会被反复读进缓冲池把真正热点的数据挤出内存连锁反应会拖垮整个实例。所以索引优化的本质不是让计算变快而是把「读 12800 个页」变成「读 3 到 4 个页」。1.2 B 树凭什么成为索引的默认结构既然要减少页的读取次数那就要找一个能快速定位的数据结构。哈希表看起来不错等值查找是 O(1)但它不支持范围查询也没法做排序而BETWEEN、、ORDER BY在实际业务里占比极高所以哈希索引只在少数场景比如内存表里用。红黑树这类二叉结构的问题是层高太高100 万条数据就是 20 层20 次页读取照样不划算。B 树的设计思路是「用多叉降低层高用有序支撑范围」。它的非叶子节点只存键值和指针不存真实数据所以一个 16KB 的页里能塞下几百甚至上千个键扇出非常大。100 万行的表如果每个非叶子节点能存 500 个键那么两层就能覆盖 25 万个叶子位置三层稳稳超过一千万行。这意味着一次精确查找只需要 3 次页访问而且根节点和第二层通常常驻在缓冲池里真实磁盘 IO 往往只有最后一次。另外B 树的叶子节点之间是用双向链表串起来的这一点对范围查询特别友好。当你执行WHERE order_no BETWEEN A001 AND A999时引擎先定位到 A001 所在的叶子然后顺着链表往后扫就行了不需要回到根节点重新找。很多人嘴里说的「双向索引」本质上就是在描述这个叶子层的双向链表结构——它让「定位起点 顺序扫描」成为一个连续动作而不是 N 次独立查找。1.3 聚簇索引与二级索引回表是怎么发生的InnoDB 的索引分两类理解这两类的区别是后面所有优化的基础。第一类是聚簇索引也就是主键索引。它的叶子节点直接存了整行数据所以按主键查一行找到叶子就等于拿到了全部字段。这也是为什么 InnoDB 必须有主键即使你不显式定义它也会自己造一个隐藏的 6 字节 rowid。第二类是二级索引也就是你自己建的普通索引。它的叶子节点只存两样东西索引列的值以及该行对应的主键值。当你用二级索引查一个不在索引里的列时引擎必须先用二级索引找到主键再拿着主键回聚簇索引里捞完整行这个动作就叫「回表」。回表是一次额外的 B 树查找如果命中的行数很多回表的开销可能比走索引本身还大。所以优化器在做选择时会估算「走索引 回表」和「直接全表扫描」哪个更便宜。当你的条件过滤后剩下的行占比很高比如超过 20%30%优化器很可能直接放弃索引去全表扫这是正常的成本决策不是索引失效。理解了回表你就能明白两个常见的经验为什么成立一是主键要尽量短因为每个二级索引的叶子都存了一份主键主键越长所有二级索引就越臃肿二是主键最好是自增的因为随机主键比如 UUID会导致插入时不断在 B 树中间位置分裂页面产生大量页碎片和随机 IO。1.4 索引不是免费的写入放大与空间代价新手最容易犯的错是把索引当成纯收益。实际上每建一个索引就等于多维护一棵 B 树增删改都要同步更新。你插入一行数据如果有 5 个索引引擎就要在 6 棵树里各找一个位置插进去每次插入还可能触发页分裂。写入放大就是这么来的——写一行实际写了好几倍的数据。空间上同样不可忽视。二级索引存了索引列加主键一个联合索引 (a, b, c) 的体积可能接近甚至超过原表。我见过一张 40GB 的表上挂了十几个索引索引占用比数据本身还多。注意写多读少的表比如日志表、埋点表、消息流水表一定要克制索引数量。这类表加索引前先想清楚它到底是被查得多还是被写得多。至于「win10 的索引关闭有必要吗」这种讨论说的是操作系统的文件搜索索引和数据库索引完全是两码事别混在一起。数据库索引只在明确有查询压力时才考虑删减不能因为「听说索引有代价」就一刀切。2. 建索引之前的判断哪些字段值得建2.1 三看原则看过滤、看连接、看排序我判断一个字段该不该建索引基本就三个维度。第一看过滤也就是它是否高频出现在WHERE里并且过滤后能筛掉大部分数据。第二看连接也就是它是否经常作为JOIN的关联列出现关联列两边都建索引能把嵌套循环从 O(n×m) 降到接近 O(n log m)。第三看排序也就是它是否出现在ORDER BY或GROUP BY里有序的索引可以省掉一次昂贵的 filesort。这三个维度里过滤的价值最大但也最容易被误判。很多人看到WHERE里有字段就加索引结果发现加了也没用因为那个字段的过滤能力太弱比如status 1命中了 90% 的行。所以第二个维度出来之前你得先算选择性。2.2 选择性怎么算低区分度字段怎么安放选择性selectivity的定义很简单不重复值的数量除以总行数。值越接近 1说明这个字段越能唯一定位数据索引效果越好越接近 0说明大量重复索引用处越小。-- 直接算出几个候选字段的选择性 SELECT COUNT(DISTINCT order_no) / COUNT(*) AS sel_order_no, COUNT(DISTINCT user_id) / COUNT(*) AS sel_user_id, COUNT(DISTINCT status) / COUNT(*) AS sel_status FROM orders;跑出来大概会是这样的order_no接近 1user_id可能是 0.3status只有 0.004假设只有 5 种状态。结论很清楚status单独建索引毫无意义因为无论你查哪个状态都要捞出表里五分之一甚至更多的行回表成本比全表扫还高。但这里有个容易被忽略的点低区分度字段不是永远不能进索引而是不能当联合索引的「前导列」。它完全可以作为联合索引的最后一列起到进一步过滤和「索引覆盖」的作用。比如(user_id, status, create_time)这个索引里status就是在前两列已经把范围收窄之后再帮忙筛一遍它的弱选择性在这个位置反而没坏处。2.3 联合索引的字段顺序等值在前范围在后联合索引的核心规则是最左前缀索引(a, b, c)只能从最左边开始连续使用中间断了就用不上后面的列。我整理了一张表把常见写法能不能用上索引列清楚。查询条件能否使用 (a, b, c)实际用到的索引部分a 1能aa 1 AND b 2能a, ba 1 AND b 2 AND c 3能a, b, cb 2不能无缺最左列 ab 2 AND c 3不能无a 1 AND c 3部分ac 断在 b 之后a 1 AND b 2部分a, bb 用于范围a 1 AND b 2 AND c 3部分a, bc 无法再用于定位a 1 AND b 2 AND c 3能a, b, ca 1 AND b 2部分ab 失效从表里能总结出一条规律等值条件放在前面范围条件放在最后。因为一旦某一列用了范围条件、、BETWEEN、LIKE x%它右边的列就没办法再用于精确定位了只能靠索引下推或者回表过滤。所以如果你有个查询是WHERE user_id ? AND create_time ? AND status ?那索引顺序应该是(user_id, status, create_time)把status这个等值条件提到create_time前面。2.4 冗余索引识别别让索引自己打架建索引的另一个隐形坑是冗余。如果已经有(a, b)再建一个(a)就是纯浪费因为前者天然能覆盖后者的所有能力。同理(a, b)和(b, a)在只按 a 查、只按 b 查的场景下看起来都有用但大多数情况下你只需要根据查询的实际组合保留一个。识别冗余索引有个简单办法查information_schema里的索引定义把每个索引的列序列出来做前缀比对SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name ORDER BY table_name, cols;导出结果之后把列序列靠前的索引标记出来凡是能被其他索引的前缀完全包含的基本都可以考虑下线。我一般会先观察一两周用SHOW INDEX FROM ...看Cardinality是不是长时间为 0 或者极低再用慢查询日志确认没有语句依赖它才动手删除。删除冗余索引带来的最直接收益是写入变快、备份变小。3. 动手建索引四种典型场景的完整写法3.1 普通索引、唯一索引、主键索引的语法与选择这三种索引在语法上差别很小但语义差别很大。主键索引通过PRIMARY KEY定义一张表只能有一个它既是唯一约束也决定了聚簇索引的物理顺序。唯一索引用UNIQUE INDEX定义作用是「允许 NULL但非 NULL 值必须唯一」它承担的是业务约束职责。普通索引就是INDEX或者KEY纯粹为了加速查询不承担任何约束。-- 主键 ALTER TABLE users ADD PRIMARY KEY (id); -- 唯一索引业务上订单号必须唯一 ALTER TABLE orders ADD UNIQUE INDEX uk_order_no (order_no); -- 普通索引只是加速查询 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);我在实操中的原则是约束用唯一索引表达性能用普通索引表达两者不要混为一谈。有人为了加速查询给一个本来可能重复的字段建了唯一索引结果上线后业务插入重复数据直接报错这是典型的职责错位。3.2 联合索引实战把 6 秒的订单查询压到 15 毫秒拿开头那个真实场景举例。表结构简化成这样CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, status TINYINT NOT NULL, amount DECIMAL(12,2) NOT NULL, create_time DATETIME NOT NULL, remark VARCHAR(200) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB;慢查询是这样的SELECT id, order_no, amount, create_time FROM orders WHERE user_id 10248 AND status 2 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;用EXPLAIN看type是ALL全表扫描rows逼近百万Extra里带着Using filesort。加索引之前先想顺序user_id是等值且选择性中等status是等值但选择性极低create_time是范围。按照「等值在前、范围在后、低区分度靠后」的原则顺序定为(user_id, status, create_time)。ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);建成之后重新EXPLAINtype变成了rangekey显示idx_user_status_timerows降到几十Extra里的Using filesort消失了——因为create_time在索引里就是有序的ORDER BY ... DESC可以直接顺着索引反向扫不需要额外排序。这条语句从六千多毫秒降到十几毫秒收益几乎全来自索引顺序选对了。3.3 覆盖索引让查询彻底不回表上面那条 SQL 还有一个可以继续压榨的空间。它SELECT了id, order_no, amount, create_time其中amount和order_no不在索引里所以每一行都要回表。如果我们把查询用到的列全部放进索引引擎就不用回聚簇索引了Extra会显示Using index这是索引优化的最高效状态。ALTER TABLE orders ADD INDEX idx_cover (user_id, status, create_time, amount, order_no);这里注意一个细节id不用写进去因为二级索引叶子天然带主键。用覆盖索引之后回表次数从「命中多少行就回多少次」变成 0在高并发场景下提升非常明显。注意覆盖索引不是越大越好。把宽字段比如VARCHAR(200)塞进索引会让索引体积膨胀写入变慢缓冲池命中率下降。我的经验是索引总长度控制在能覆盖核心查询即可超过三四个业务列就要谨慎评估。3.4 长字符串字段前缀索引怎么定长度有个常见需求是给邮箱、URL、商品名称这类长字符串建索引。直接全字段建索引太占空间用前缀索引就够。前缀索引的语法是INDEX idx_name (col(n))其中 n 是截取长度。难点在于 n 该取多少。方法是算不同 n 下的选择性找到那个「再往上加长度选择性也不怎么涨」的拐点SELECT COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel6, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(email, 14)) / COUNT(*) AS sel14, COUNT(DISTINCT email) / COUNT(*) AS sel_full FROM users;假设结果是 0.72、0.94、0.98、0.992、0.995那取 10 就是性价比最高的位置选择性已经接近全字段索引体积却只有原来的几分之一。定好之后ALTER TABLE users ADD INDEX idx_email_prefix (email(10));前缀索引有个副作用要记住它不能用于覆盖索引也不能支持ORDER BY email这种排序因为索引里存的只是前缀不是完整值。所以它只适合纯过滤场景。4. 索引建成之后怎么验证它真的生效了4.1 EXPLAIN 里真正该盯的五个字段很多同学跑EXPLAIN只看key有没有值这是不够的。我一般盯五个字段。type表示访问类型从好到坏大致是system、const、eq_ref、ref、range、index、ALL。出现ALL就是全表扫描出现index是全索引扫描扫整棵索引树通常也不好这两个是要重点处理的对象。key是实际使用的索引名。如果possible_keys有值但key是 NULL说明优化器算完成本之后放弃了这个索引这时候要去看rows和过滤比例。key_len能反推索引实际用了多少列这是验证最左前缀有没有断掉的关键。计算规则我列成表列类型是否可空key_len 贡献TINYINTNOT NULL1INTNOT NULL4INTNULL5BIGINTNOT NULL8DATENOT NULL3DATETIMENOT NULL5CHAR(10)utf8mb4NOT NULL40VARCHAR(20)utf8mb4NOT NULL20×42 82VARCHAR(20)utf8mb4NULL821 83举个例子索引(user_id BIGINT NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL)如果key_len显示 10说明只用了user_id8status1 可空标记1create_time没用上多半是某处写法导致范围条件提前了。rows是估算的扫描行数注意是估算不是精确值但量级上有参考意义。Extra里的信息最有价值Using index表示覆盖索引Using where表示还要回表后在 Server 层过滤Using filesort表示有额外排序Using temporary表示用了临时表后两个都是要尽量消掉的。4.2 慢查询日志先找到该建索引的语句索引优化最怕无的放矢。你不知道哪条 SQL 慢就只能凭感觉加加完还不知道有没有用。慢查询日志就是解决这个问题的。-- 打开慢查询日志阈值设为 1 秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;如果临时想定位「哪些语句完全没走索引」可以开log_queries_not_using_indexes但我要提醒一句这个开关会把所有小表的全扫语句也记下来日志量会爆炸排查完记得立刻关掉。日志攒下来之后别一行行看用聚合工具。MySQL 自带的mysqldumpslow就能按耗时排序mysqldumpslow -s t -t 20 /var/log/mysql/slow.log这个命令会列出耗时最高的 20 条语句并且把参数占位符归一化方便你看出同一类查询。拿到 Top 清单之后先处理那些「执行次数多 单次耗时高」的因为它们的总耗时贡献最大。至于IDEA 插件拼装 SQL这类开发工具主要作用是在写代码阶段提示语法和索引建议最终判断还是要回到真实执行计划上。4.3 索引失效的六种典型写法索引建了不等于用得上。下面这六种写法是我在实际项目里遇到频率最高的失效原因。第一种是给索引列套函数。WHERE DATE(create_time) 2024-01-01会让索引失效改成WHERE create_time 2024-01-01 AND create_time 2024-01-02就能用上范围扫描。第二种是隐式类型转换。字段是VARCHAR却写成WHERE phone 13800138000数字不加引号比较时会把字段转成数字索引直接废掉。反过来字段是INT写成WHERE user_id 123通常没事因为常量会被转换但依赖这个行为并不稳妥老老实实对齐类型才是正解。第三种是前导模糊匹配。LIKE %abc无法用索引因为索引是按前缀有序的LIKE abc%可以。第四种是OR连接了未建索引的列。WHERE a 1 OR b 2只要b没索引整个条件就可能退化成全表扫。这种情况可以考虑改写成UNION ALL或者给b也补上索引。第五种是负向条件。!、NOT IN、NOT EXISTS通常会导致优化器放弃索引因为它们的过滤逻辑在 B 树里没法做区间收敛。第六种是索引列参与运算。WHERE amount 100 500改成WHERE amount 400就好。提示判断索引有没有真正生效别靠感觉一定跑EXPLAIN看key和Extra。我曾经花了半天排查一条「明明加了索引还是慢」的语句最后发现是字段编码不一致导致的隐式转换。5. 生产环境加索引的踩坑记录5.1 大表加索引Online DDL 的正确姿势与观察要点在几十行数据的开发库上ALTER TABLE ADD INDEX是瞬间完成的但在生产环境一张上千万行的表上这件事可能锁表几十分钟直接引发线上事故。所以大表加索引的第一原则是先确认版本和存储引擎再选执行方式。从 MySQL 5.6 开始支持 Online DDL加二级索引这个操作可以做到不阻塞写入。显式声明方式是ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time), ALGORITHMINPLACE, LOCKNONE;如果数据库不支持ALGORITHMINPLACE语句会直接报错而不是悄悄降级这是好事能让你在真正执行前发现问题。加索引期间我会盯着两张表information_schema.innodb_trx看有没有长事务卡住 DDL以及操作系统的磁盘 IO 监控确认没有把 IO 打满。如果版本比较老或者表实在太大就要考虑第三方工具做在线变更比如gh-ost或者pt-online-schema-change。它们的原理是建一张影子表把索引建在影子表上然后一边同步增量数据一边拷贝存量数据最后短暂加锁切换表名。代价是多占一份存储空间执行时间更长但业务几乎无感。注意无论用哪种方式都要在业务低峰期执行并且提前确认表的磁盘剩余空间足够至少能容纳一份额外数据。我有一次差点因为空间不足把变更搞挂幸好提前查了df -h。5.2 索引建完还是不生效先更新统计信息有时候索引明明建对了EXPLAIN里也没报错但优化器就是不选它。这种情况下十有八九是统计信息过期了。InnoDB 靠统计信息估算每个索引的过滤行数如果统计信息还是几天前的优化器可能严重高估或低估从而选错执行计划。手动刷新统计信息ANALYZE TABLE orders;如果是分区表或者数据变化特别频繁的表建议把它放进定期维护任务里。另外 MySQL 8.0 引入了直方图histogram对数据分布严重倾斜的列特别有用ANALYZE TABLE orders UPDATE HISTOGRAM ON status, user_id;如果刷了统计信息还是不对可以在紧急情况下用FORCE INDEX强制执行但这是个临时手段长期方案还是要弄清楚优化器为什么算错。SELECT ... FROM orders FORCE INDEX (idx_user_status_time) WHERE ...;5.3 常见问题速查表现象可能原因排查方向处理办法type ALL无可用索引或索引被放弃看possible_keys是否为空补索引或改写 SQLkey为 NULL 但possible_keys有值优化器估算走索引更贵看rows和过滤比例补覆盖索引或刷新统计信息Extra出现Using filesort排序字段不在索引有序路径上看ORDER BY列顺序把排序列加入索引尾部Extra出现Using temporaryGROUP BY或DISTINCT无法用索引看分组列建对应的联合索引key_len小于预期最左前缀被断掉对比查询条件与索引列顺序调整索引列顺序大表加索引长时间不结束有长事务或锁等待查innodb_trx杀掉阻塞事务后重试加了索引反而变慢写入放大、索引过多数一下表上索引数量删除冗余索引这张表我建议贴在自己的工作笔记里遇到问题按顺序过一遍大部分索引问题都能定位到。最后分享一个我自己的习惯每次上线新索引我都会在变更后的一周内回看一次慢查询日志确认目标 SQL 的实际耗时确实降下来了。因为执行计划是会随数据分布漂移的今天生效的索引三个月后数据长了几倍可能就换了个走法。索引从来不是「建完就完事」的一次性动作它更像是一个需要定期回访的长期维护项。我踩过最深的坑就是「加完不管」等到某天监控告警才发现那条 SQL 早就悄悄退化成全表扫描了。