1. 索引设计前先想清楚这几个问题做 MySQL 开发或者 DBA 的朋友应该都有体会索引这东西用好了是神器用不好就是定时炸弹。面试题里问建索引有哪些注意事项看起来是个基础题但真正能把这个问题讲透的人不多。很多人背了一堆规则什么不要建太多索引不要对大字段建索引但真到实际场景里还是不知道该怎么取舍。先说个最常见的场景。线上有个订单表数据量到了千万级别查询越来越慢。开发同学一看慢查询日志发现某个 where 条件频繁出现随手就加了个索引。结果呢查询是快了但写入变慢了磁盘占用也涨了而且因为这个索引建的太宽优化器反而不走这个索引走了全表扫描。这种案例我见过太多次了。所以建索引之前先把几个基础问题想清楚这个索引到底服务哪些查询是单条件查询还是多条件组合索引建在哪些列上列的选择性怎么样这个表的写入频率高不高索引数量多了能不能扛住会不会出现索引失效的情况比如隐式类型转换、函数运算这些坑。这篇文章就围绕这几个问题展开结合我实际踩过的坑把 MySQL 建索引的注意事项一次性说清楚。不管你是初中级开发还是已经在带团队的技术负责人这里面的内容都能用得上。2. 核心思路拆解索引不是越多越好也不是越窄越好2.1 先理解索引的本质用空间换时间的取舍索引的本质是数据结构底层是 B Tree。B Tree 的特点是数据都存在叶子节点并且叶子节点之间用指针串联这样范围查询和排序就特别快。当你执行select * from orders where user_id 10086 and status 1时如果没有索引MySQL 只能全表扫描一行一行地比对数据量大时就是灾难。有了索引之后MySQL 先通过 B Tree 快速定位到符合条件的记录位置再去主表回表查询完整数据。这个过程就像查字典一样先通过偏旁部首定位到页码范围再一页一页翻效率就上来了。但索引不是白给的。每建一个索引就意味着写入数据时要额外维护一颗 B Tree更新数据时要同步修改索引结构磁盘上要多占用一份空间查询时优化器要多一个选择选错了反而变慢所以索引设计本质上就是一场读写权衡。读多写少的表索引可以多建几个写多读少的表索引要谨慎再谨慎。2.2 复合索引最核心的一个原则最左前缀面试里还有个高频问题是where 条件 a and b应该怎么建索引很多人的第一反应是分别给 a 和 b 各建一个单列索引。这个思路不能说完全错但绝大多数情况下不是最优解。MySQL 查询优化器面对多个单列索引时虽然可能走 index merge但更多时候只能选一个索引用另一个就被浪费了。更合理的做法是建一个复合索引(a, b)。这里面的关键就是最左前缀原则。复合索引(a, b, c)实际生效的场景是where a ?where a ? and b ?where a ? and b ? and c ?where a ? and b ? and c ?注意范围查询后面的列不生效但下面的场景就用不上这个复合索引where b ? and c ?没有 a 开头索引直接废掉where a ? and c ?只有 a 能用到c 用不上中间断了这个坑我见得太多太多了。很多人建了复合索引写 SQL 的时候却不注意字段顺序导致索引根本没被用到。优化器虽然在某些版本里能做一定程度的调整但最保险的做法永远是查询条件的字段顺序尽量和索引字段顺序保持一致。2.3 区分度和选择性索引列怎么选索引列不是随便选的。一个列的区分度决定了索引的查询效率。如果一列只有两个值比如 status 只有 0 和 1那这一列单独建索引几乎没有意义选择性太低了。查询的时候MySQL 通过索引可能还是要过滤掉一半以上的数据还不如直接全表扫。区分度好的列是什么样比如 user_id、order_no、手机号、身份证号这类唯一性强的字段。一个判断经验是区分度 COUNT(DISTINCT column) / COUNT(*)这个值越接近 1说明这个列选择性越好越适合建索引。经验上这个比值大于 0.1 就算不错了低于 0.01 就要慎重考虑。我们从一个实际案例来看这个问题CREATE TABLE orders ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint(20) NOT NULL, status tinyint(4) NOT NULL DEFAULT 0, sku_id bigint(20) DEFAULT NULL, amount decimal(10,2) NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;假设业务上高频查询是查某个用户最近的订单和根据订单号查订单那么合理的索引设计是ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time), ADD UNIQUE KEY uk_order_no (order_no);第一个复合索引支撑按用户查找并按时间排序第二个唯一索引支撑订单号的精确查询。这个设计不是拍脑袋想的是基于查询模式推导出来的。3. 建索引时必须避开的几个细节坑3.1 隐式类型转换索引失效的第一大元凶索引失效的情况有很多但日常开发中最常见的就是隐式类型转换。比如表里user_id是 varchar 类型查询时写了where user_id 10086MySQL 会把字符串和数字比较时自动把字段转换成数字导致字段上的索引失效。为什么因为索引是基于原始字段值构建的一旦字段本身被函数或类型转换处理过B Tree 里的有序排列就失效了。优化器只能放弃索引走全表扫描。我见过一个真实案例某用户表手机号字段是 varchar(20)查询条件写成where phone 13800138000明明有索引但 EXPLAIN 出来 type 是 ALL数据量几百万时查询要好几秒。后来把 SQL 改成where phone 13800138000加上引号之后立刻走了索引耗时降到毫秒级。排查思路也很简单用 EXPLAIN 看执行计划EXPLAIN SELECT * FROM user WHERE phone 13800138000;如果看到 type 是 ALL 或者 key 为 NULL优先怀疑字段类型和值类型不一致。3.2 函数运算和表达式索引的隐形杀手除了类型转换针对索引列使用函数也是常见的失效场景WHERE DATE(create_time) 2024-01-01 WHERE YEAR(create_time) 2024 WHERE amount 100 500这些都让索引列参与了运算B Tree 里存的是原始值没法直接用到。正确做法是把函数运算移到等号的另一边WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这个改写既保持了业务逻辑又让索引列保持裸状态索引就能用上了。3.3!、NOT IN、LIKE %xxx这几个操作符要小心很多优化器版本中对!和NOT IN的处理并不理想往往放弃了索引。范围查询、不等值查询本身就意味着 B Tree 的最优匹配失效优化器需要扫描的数据范围会变大。LIKE查询也是一样。LIKE abc%是可以用到索引的因为前缀是确定的。但LIKE %abc和LIKE %abc%就不行了开头是通配符B Tree 无法定位起点。如果业务上确实需要模糊搜索尤其是全文搜索场景建议引入全文索引或者外部搜索组件而不是硬扛着用 LIKE。3.4 索引列上做排序ORDER BY 也能走索引ORDER BY 能不能用到索引很多人忽略了。其实如果排序字段正好满足最左前缀MySQL 就可以利用索引的有序性直接返回结果避免 filesort。举个例子SELECT * FROM orders WHERE user_id 10086 ORDER BY create_time DESC;如果索引是(user_id, create_time)那这个查询既可以用 user_id 等值匹配定位又可以利用 create_time 在索引中的有序性直接排序性能非常好。而如果是where user_id 10086 order by amountamount 不在索引里就需要额外做 filesort虽然文件排序在小数据量下问题不大但数据量大时也是性能瓶颈。所以设计复合索引时要把查询条件字段和 ORDER BY 字段一起考虑。条件字段放前面排序字段放后面这是最优解。3.5 前缀索引大字段的折中方案有些大字段比如长文本、长字符串直接建索引空间占用太大索引效率也不高。这时候可以用前缀索引只取字段的前 N 个字符建立索引。ALTER TABLE article ADD INDEX idx_title_prefix (title(20));注意前缀索引有代价无法用于 ORDER BY 和 GROUP BY也无法用于覆盖索引。选择前缀长度时需要测试区分度比如SELECT COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS ratio10, COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS ratio20 FROM article;找到区分度可接受又节省空间的最短前缀长度即可。比如 10 个字符区分度就到了 0.9520 个字符才 0.97那就选 10。4. 实操过程从慢查询 SQL 到一个完整索引方案4.1 第一步拿到慢查询日志定位高频 SQL我平时做索引优化第一步从来不是急着建索引而是把慢查询日志和分析结果先拉出来。SHOW VARIABLES LIKE slow_query_log%; SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;当然生产环境一般都已经配置好了直接去 slow log 里捞。把耗时超过 1 秒的 SQL 都收集起来按执行次数和总耗时排序。注意一件事高频但单次很快的查询可能不在慢日志里但它占用的总时间不可忽略。所以还要配合 performance_schema 或者 events_statements_summary_by_digest 这类统计表来看。4.2 第二步用 EXPLAIN 分析执行计划拿到 SQL 之后最核心的一步是用 EXPLAIN 看执行计划。EXPLAIN SELECT order_no, amount, create_time FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;重点关注这几个字段字段含义好结果差结果type访问类型ref, range, eq_refALL, indexkey实际使用的索引有索引名NULLrows预计扫描行数越小越好非常大Extra附加信息Using indexUsing filesort, Using temporary还有一个容易被忽略的细节key_len。它表示索引使用的字节数可以帮助判断复合索引到底用到了几列。比如idx_user_create(user_id, create_time)user_id 是 bigint长度为 8 字节create_time 是 datetime长度为 5 字节。如果 key_len 只有 8说明只用了 user_id 这一列create_time 没有参与索引检索。这个细节对于排查复合索引是否完整生效特别有用。4.3 第三步结合 SQL 模式推出候选索引来看一个我在实际项目中处理的案例。业务表结构大概是这样的CREATE TABLE payment_record ( id bigint(20) NOT NULL AUTO_INCREMENT, payment_no varchar(64) NOT NULL, merchant_id bigint(20) NOT NULL, channel tinyint(4) NOT NULL, pay_status tinyint(4) NOT NULL, pay_amount decimal(12,2) NOT NULL, pay_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;高频查询有三类-- Q1: 商户查询某时间段支付记录 SELECT * FROM payment_record WHERE merchant_id ? AND pay_time BETWEEN ? AND ?; -- Q2: 根据支付单号查记录 SELECT * FROM payment_record WHERE payment_no ?; -- Q3: 商户统计某天的交易金额和笔数 SELECT merchant_id, COUNT(*), SUM(pay_amount) FROM payment_record WHERE merchant_id ? AND pay_time BETWEEN ? AND ?;针对 Q1 和 Q3它们都是merchant_id pay_time的组合查询所以建一个(merchant_id, pay_time)复合索引就够了。Q2 是等值查询payment_no 唯一性强建一个唯一索引即可。ALTER TABLE payment_record ADD UNIQUE KEY uk_payment_no (payment_no), ADD INDEX idx_merchant_time (merchant_id, pay_time);这里有一个优化点值得说Q3 只查merchant_id, COUNT(*), SUM(pay_amount)这三个字段都能覆盖在idx_merchant_time上吗不能因为 pay_amount 不在索引里。如果想让 Q3 完全走覆盖索引可以改成(merchant_id, pay_time, pay_amount)三列复合索引。但这样索引会更宽写入成本更高。权衡之后如果 Q3 的执行频率非常高宽索引更值得如果 Q1 Q2 才是大头那就保持两列索引让 Q3 回表代价也不大。4.4 第四步验证索引效果别只看执行计划索引加完之后我建议大家做一次真实的压测或抽样测试不要只看 EXPLAIN 结果。EXPLAIN 只是预估真实执行时间还受缓存、并发、数据分布的影响。SELECT * FROM payment_record WHERE merchant_id 12345 AND pay_time BETWEEN 2024-01-01 AND 2024-01-31 LIMIT 10;多次执行对比平均耗时。同时注意看 profilingSET profiling 1; SELECT * FROM payment_record WHERE merchant_id 12345 AND pay_time BETWEEN 2024-01-01 AND 2024-01-31 LIMIT 10; SHOW PROFILES;如果优化后 SQL 的耗时从几百毫秒下降到几毫秒说明索引方案是成功的。如果没有明显改善就要重新检查是不是索引失效了或者数据分布导致优化器判断错了。另外记得用optimizer_trace看优化器的决策过程。有时候优化器就是不走你建的索引原因可能是统计信息不准确。这时候ANALYZE TABLE payment_record;可以刷新统计信息。这个手段常被忽略效果却很明显。4.5 第五步上线前的索引管理规范最后说点管理层面的经验。建索引不能只建不拆。我见过一张表开发迭代了好几个版本索引建了十几个其中很多已经没人用了。不仅占空间还影响写入性能。所以我一般建议团队里运维一个索引治理机制每次索引变更都要留 SQL 脚本和说明文档定期用慢查询日志和sys.schema_unused_indexes视图找未使用索引确认无业务依赖的索引在业务低峰期删除查看未使用索引SELECT * FROM sys.schema_unused_indexes;这个视图能直接列出哪些索引自服务器启动以来从未被使用过删之前再确认一次基本不会有问题。5. 常见问题与排查技巧实录整理一下我在工作中经常遇到的索引问题以及对应的排查方法问题现象可能原因排查方法明明有索引EXPLAIN 却显示 typeALL隐式类型转换、函数运算、like 前缀通配检查字段类型与查询值类型检查索引列是否有函数包裹复合索引只生效了一部分查询条件顺序不符合最左前缀调整 SQL 条件顺序或索引字段顺序加了索引后写入变慢索引数量太多、索引列过多评估是否删除冗余索引只在必要场景建索引查询走了索引但还是很慢回表次数过多、扫描行数巨大考虑覆盖索引或把大字段移出 select 列表排序很慢ORDER BY 字段不在索引中复合索引末尾加入排序字段优化器选择另一个索引统计信息过期执行 ANALYZE TABLE索引字段有空值导致统计不准大量 NULL 值评估是否给默认值比如 0、空串下面再展开几个重点问题给出更具体的操作方案。5.1 为什么有时候 MySQL 有索引却不用这是新手最困惑的地方。明明加了索引EXPLAIN 出来还是全表扫描。常见原因有三个第一数据量太小。如果表只有几百行MySQL 优化器认为直接全表扫描比走索引回表更快。索引访问本身有开销需要先在 B Tree 上查找再去主表读取数据。对小表来说这个开销比全表扫描还大。这种情况不用强求走索引。第二区分度太低。比如性别字段只有两个值走索引返回的数据量可能是全表的一半优化器还不如全表扫。这种列就不该建索引。第三隐式转换或函数运算。前面详细说过这是最常见的坑。还有一种情况容易被忽略统计信息过期。MySQL 的优化器基于表的统计信息来估算扫描行数如果统计信息陈旧优化器可能误判。解决办法就是ANALYZE TABLE table_name;5.2 主键索引和唯一索引到底怎么选面试也经常问主键索引和唯一索引的区别是什么主键索引是聚簇索引InnoDB 中数据行本身就按主键组织主键索引的叶子节点存的是整行数据。所以通过主键查询是最快的直接用主键就能定位到数据行不需要回表。唯一索引是非聚簇索引叶子节点存的是主键值。查询时先走唯一索引找到主键再回表查到完整数据。唯一索引和普通索引的区别在于它保证了唯一性约束允许有一个 NULL 值如果列允许 NULL。实际业务中主键尽量选择自增或者趋势递增的值不要用随机字符串、UUID。为什么因为 InnoDB 聚簇索引是有序的如果主键随机插入会导致 B Tree 频繁分裂、页分裂产生大量碎片写入性能和空间利用率都会受影响。自增主键能顺序写入减少页分裂。有一种情况用 UUID 也有合理性分布式场景需要全局唯一 ID而且不想暴露自增规律。这种可以接受 UUID 的写放大代价但一定要知道它的代价是什么。5.3 联合索引 vs 多个单列索引真实场景怎么选前面说where a and b建议建联合索引但真实场景不是这么绝对的。举个例子一张订单表有的查询是where user_id ?有的是where status ?还有的是where user_id ? and status ?三个查询频次都很高。第一种做法建idx_user和idx_status两个单列索引。那么where user_id and status时MySQL 有两种选择选一个索引回表过滤或者走 index merge索引合并。index merge 在某些场景下有效但不是所有版本和所有查询都稳定。而且 status 区分度低单独索引价值不大。第二种做法建idx_user_status(user_id, status)一个复合索引。它可以覆盖where user_id ?和where user_id ? and status ?但对于纯粹的where status ?这个索引用不上因为最左前缀断了。所以正确思路是优先分析高频查询模式用查询日志统计出 where 条件里字段组合出现的频率。选择出现最频繁的字段作为复合索引的引导列再叠加其他高频过滤字段。而不是机械地每个 where 字段建一个索引。5.4 哪些情况下我坚决不建索引我自己定的几个原则分享出来给大家参考表数据量很小比如千行以内不用建索引全表扫描更快频繁更新的列不适合建索引因为索引维护成本高而且更新慢区分度极低的列比如性别、布尔值除非配合其他列组成复合索引否则不单独建大文本字段比如 TEXT、超长 VARCHAR除非用前缀索引否则不建全文索引不划算索引数量超过 5 个甚至更多时要先评估现有索引是否冗余而不是继续叠加5.5 MySQL 8.0 里可以用不可见索引做安全变更最后分享一个很实用的技巧。MySQL 8.0 支持不可见索引invisible index这是一个我强烈推荐大家在线上环境使用的功能。流程是这样的先加一个不可见索引观察一段时间确认查询确实会用到它再把它设为可见。如果发现问题直接删除即可中间不会影响任何线上流量。-- 创建不可见索引 ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time) INVISIBLE; -- 确认执行计划会走这个索引 SELECT /* SET_VAR(optimizer_switchuse_invisible_indexeson) */ * FROM orders WHERE user_id 10086 AND create_time 2024-01-01; -- 没问题设为可见 ALTER TABLE orders ALTER INDEX idx_user_create VISIBLE;线上加索引最怕的就是加的时候锁表或者加了之后反而更慢。不可见索引相当于一个灰度发布的机制反复测试确认无副作用再让优化器真正使用它。这个做法在 MySQL 8.0 中算是比较稳的方案了。我个人在这些年的索引优化里最大的体会是索引设计不是一次性的而是一个持续迭代的过程。业务在变查询模式在变数据量在变索引方案也要跟着调整。刚接手一个系统时不要急着大改索引先花时间把慢查询日志和业务查询模式摸清楚再做针对性设计。很多时候一个复合索引的字段顺序调整带来的性能收益比新建三个索引都明显。另外还想强调一点任何索引优化都要以真实数据量为准。开发环境建索引快得飞起不代表生产环境也一样。有条件的话尽量在和生产数据量级相当的环境里验证或者至少在压测环境里经过充分测试再上线。这也是我前面反复提到 EXPLAIN 和 profiling 的原因——没有数据支撑的优化基本都是在碰运气。