刚接手一个老订单系统的维护日志里出现一条跑了两三秒的查询排查了半天发现不是没建索引而是建了单列索引却被优化器放弃最后换成联合索引才把耗时压到几十毫秒。这种问题在日常开发里太常见了今天把MySQL创建索引这件事从头到尾捋一遍从底层原理到选型、实操、失效排查都写清楚。1. 为什么索引不是越多越好先弄懂B树与回表很多人对索引的理解停留在“查得快”三个字一遇到慢查询就顺手加索引结果表上堆了十几个索引写入越来越慢查询也没快多少。实际上索引是一把双刃剑想用好它得先知道它的代价和原理。1.1 从“书本目录”说起索引的底层逻辑最直白的类比是纸质书的目录。目录能帮你快速翻到目标章节但每本书出版时额外写目录也要增加成本。数据库索引也是一样索引的本质是一棵排序好的查找树MySQL InnoDB引擎里用的是B树。B树有几个特点值得记住。数据只存储在叶子节点非叶子节点只存键值。每个叶子节点用单向链表相连方便范围查询时顺序扫描。树的高度一般很矮常用的假设是三层结构——你只需要从根节点出发大概做三次磁盘I/O就能定位到数据。为什么三层就够InnoDB默认页大小是16KB假设主键是8字节的bigint加上6字节的行指针一个非叶子节点大约能容纳1170个键值对16384除以14约等于1170。叶子节点里一条记录连同其他列数据算1KB大小能放16条数据。三层算下来1170乘以1170再乘以16大约是2190万条记录。也就是说一张两千万行左右的表用主键查询最多三次I/O就能找到数据这就是索引快的原因。1.2 聚簇索引与二级索引以及回表InnoDB的每张表都有且只有一个聚簇索引通常建立在主键上。聚簇索引的叶子节点直接存着这一行的全部数据所以走主键查询拿到叶子节点就等于拿到了整行。二级索引则是我们平时CREATE INDEX创建的索引它的叶子节点只存索引列和主键值。这里就引出了一个关键动作回表。用二级索引查数据先在二级索引的B树里找到匹配的主键再拿着主键去聚簇索引里把整行数据取出来。一次回表就是一次额外的磁盘I/O如果查询命中了上千个主键就要回表上千次性能自然下降。所以设计索引时核心目标之一是尽量减少回表次数。如果能做到“覆盖索引”也就是SELECT需要的列全部包含在索引里那查询就根本不需要回表性能会好一个档次。后面我会专门讲这个。1.3 索引的真实成本清单我经常跟团队里的小伙伴说建索引之前先想想这三笔账第一笔是存储成本。每多一个二级索引就多一棵B树磁盘占用直接翻倍都有可能。一张500万行的订单表加一个大字段的索引可能多占几百MB空间不能忽视。第二笔是写放大。每次INSERT、DELETE或者UPDATE涉及索引列InnoDB都要同步维护所有相关索引树。索引越多写入路径就越长高并发写入场景下这是实打实的性能损耗。第三笔是缓冲池挤出效应。InnoDB有内存缓冲池索引数据也缓存它在里面。索引越多、占用越大热数据能留在内存里的就越少反而可能导致命中率下降。这也是为什么“索引不是越多越好”而且是“少而精”。理解了这些再回头看那些“一张表塞了十几个索引、写入慢到怀疑人生”的案例问题就很清楚了。2. 索引选型普通索引、唯一索引、联合索引到底怎么选建索引之前必须先想清楚查询模式。我会按三个顺序来考虑查询条件是什么排序分组用什么哪些列经常一起出现在WHERE里。2.1 从查询模式反推索引最忌讳的做法是拍脑袋决定“这个字段用得频繁加个索引吧”。正确做法是把线上慢查询日志捞出来看看真实SQL长什么样再根据高频查询的WHERE条件、JOIN字段、ORDER BY字段来设计索引。比如某购物系统的订单查询慢日志里最频繁的是这么几条按user_id查某用户的所有订单按status查某种状态的订单并按created_time倒序按订单号精确查单笔订单那么索引设计就应该是user_id单列索引status和created_time联合索引订单号字段建唯一索引。这样设计每个查询都有索引可走覆盖了绝大多数场景。2.2 什么时候用唯一索引唯一索引比普通索引更严格要求列值不能重复。适合的业务场景很明确业务上本身就要求唯一的字段比如用户表的手机号、订单表的订单号、支付表的支付流水号。这里有个细节值得注意唯一索引除了约束数据唯一性对查询优化也有帮助。优化器知道唯一索引最多命中一行所以按唯一索引等值查询时通常能直接判定最多返回一条记录执行计划会更高效。如果一个字段业务上允许重复比如用户昵称就别硬上唯一索引否则大量写入会因为重复值校验而变慢。2.3 联合索引的顺序怎么安排联合索引是很多新手最容易踩坑的地方。它遵循最左匹配原则查询条件里必须从联合索引最左边的列开始连续匹配索引才能生效。比如建了(status, created_time)联合索引可以用上status某个值和created_time范围查询的组合条件但如果没有status条件只想按created_time查这个索引就用不上。那字段顺序怎么定我的经验是两条原则区分度高的列放前面等值条件优先于范围条件。比如status的区分度可能只有几个值而user_id的区分度很高如果经常按user_id等值匹配user_id就应排在左边如果经常按status加时间范围查status在前、created_time在后更合理。注意区分度不是唯一标准还要结合真实查询的频率。如果一个字段区分度高但很少出现在WHERE里光“区分度高”没什么用。2.4 覆盖索引与字段裁剪前面说过二级索引会回表。想彻底避免回表就用覆盖索引让索引里的列覆盖SELECT要查询的所有列。举个例子订单表上经常执行SELECT order_no, created_time FROM t_order WHERE user_id ?如果建了(user_id, order_no, created_time)联合索引这个查询就可以完全在索引树里完成不需要回表。所以建索引时不要只盯着WHERE字段多看两眼SELECT字段。在保证索引不臃肿的前提下尽量让常用查询直接命中覆盖索引。下面这张表是我平时选型时的对照参考索引类型适用场景注意事项普通索引查询频率高、允许重复的字段如昵称、状态注意区分度区分度太低的字段慎用唯一索引业务唯一性约束如手机号、订单号写入时有唯一性校验开销联合索引多条件组合查询、排序场景字段顺序按区分度和等值优先前缀索引长字符串字段如邮箱、URL只能用于排序和等值不能用于覆盖索引覆盖索引SELECT字段可以完全被索引包含的查询索引列不宜过多避免臃肿3. 创建索引实操与线上大表避坑这一章偏操作但最容易被忽视的不是语法而是细节和线上安全。3.1 常用创建语法MySQL里创建索引的常见写法有三种。建表时直接定义索引适合新表上线CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64) NOT NULL, created_at DATETIME NOT NULL, INDEX idx_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;已存在的表用CREATE INDEX或者ALTER TABLE都行效果等价CREATE INDEX idx_username ON t_user (username); ALTER TABLE t_user ADD INDEX idx_username (username);我个人偏好CREATE INDEX因为它可以单独管理回滚也方便。ALTER TABLE除了加索引还能顺便改表结构语义不够纯粹。索引命名也有讲究我习惯用“idx_表名_字段名”这种格式比如idx_user_username。命名清晰一点后续排查问题看着就不会混淆也方便定位哪个索引属于哪张表。3.2 字段长度、字符集与索引长度限制字符串字段建索引要留意长度限制。老版本InnoDB的索引键长度限制是767字节新版本放开到3072字节。如果是utf8mb4字符集一个字符最多占4字节那么varchar(255)全字段建索引光索引键就可能超过1020字节快逼近新版上限。所以长字符串字段我用前缀索引比较多只取前几十个字符既能保证大部分区分度又能减少索引占用空间和执行开销CREATE INDEX idx_user_email ON t_user (email(20));要注意前缀索引不能用于覆盖索引也无法做精确的排序优化。所以如果业务对排序要求高前缀索引并不合适。到底取多长通常我会跑一条SQL统计不同前缀长度的区分度取一个区分度接近全字段的值。3.3 线上大表加索引最大的坑是锁表这个坑我踩过也是想重点提醒的。给一张几百万行甚至上千万行的表直接执行ALTER TABLE ADD INDEXMySQL早期版本会重建整张表期间加排他锁读写全部被卡住业务直接中断。这在大促期间是不可接受的。MySQL 5.6之后支持了ONLINE DDL部分操作可以用INPLACE算法和NO LOCK策略比如新增二级索引。即便如此也要谨慎因为执行过程中仍然有复制延迟、I/O开销、日志量放大等问题。主从架构下DDL在主库执行完从库还要重放延迟可能飙升。所以我的线上操作流程是先在测试库或备份环境模拟执行一遍拿到大致的执行时间窗口期选在业务低峰执行时关注主库告警和从库延迟必要时用在线DDL辅助工具分批处理把对线上的影响降到最低。总之线上加索引不是随手跑一条SQL的事需要走审批和演练。3.4 区分度太低的字段干脆别建有时候一个字段明明经常出现在WHERE里但区分度极低比如订单表的status字段可能只有“待支付、已支付、已退款”几个值。这种情况建单列索引优化器算一下扫描行数发现还不如全表扫描直接放弃索引。白建还浪费空间。所以说索引设计一定要结合数据分布。区分度低于某个阈值我习惯看大约20%以下的单列字段优先级应该往后放或者考虑放进联合索引做前缀过滤配合其他高区分度字段一起生效。4. 索引不生效一条慢查询的完整排查链路这一节直接给一条典型的慢查询排查过程照着这个思路走能少踩很多坑。4.1 案发现场日志里的慢SQL某项目上线后慢查询日志里频繁出现下面这条SQL耗时在1.8秒左右SELECT * FROM t_user WHERE phone_no 13800138000;第一反应是没索引。一查表结构phone_no建了索引字段类型是VARCHAR(64)。那为什么没走索引EXPLAIN看执行计划EXPLAIN SELECT * FROM t_user WHERE phone_no 13800138000;结果type为ALLkey为NULLrows显示全表扫描。4.2 第一重暗坑隐式转换问题出在传参类型上。phone_no是字符串类型SQL里传入的是整数13800138000。MySQL做比较时会把字符串值转成数值来比较相当于在索引列上做了一次隐式类型转换函数作用在索引列上索引自然失效。修复方式很简单把参数改成字符串或者写SQL时就用引号包起来SELECT * FROM t_user WHERE phone_no 13800138000;改完后EXPLAIN显示type为constkey命中了索引查询降到毫秒级。这类因为隐式转换导致索引失效的案例在代码里特别隐蔽因为Java、Python这类语言传参时经常会把字符串和数字混在一起DB层一转换就完蛋。4.3 第二重暗坑函数运算与前导模糊跟隐式转换同类的还有函数运算。比如查询里写了SELECT * FROM t_user WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2025-01-01;created_at建了索引也没用因为索引列套上了DATE_FORMAT函数优化器没法直接利用B树的排序结构。正确的写法是改成范围查询SELECT * FROM t_user WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00;前导模糊查询也一样。LIKE abc%还能借索引的顺序性走范围扫描LIKE %abc%就没办法了前面有通配符B树无从匹配。遇到难以避免的前导模糊可以额外考虑全文索引或者干脆依赖外部搜索服务别死磕MySQL。4.4 第三重暗坑OR条件的陷阱OR也是让人头疼的写法。比如SELECT * FROM t_user WHERE username abc OR status 1;即使username建了索引OR另一个条件是status而status又不在索引里优化器通常会把两个条件合并判断最后选择全表扫描。处理办法一般是改写为UNION拼接SELECT * FROM t_user WHERE username abc UNION SELECT * FROM t_user WHERE status 1;两个分支各自走各自的索引效果立竿见影。当然如果两个OR条件都是索引列而且优化器能确认合并后效率更高也可能走索引合并index merge但这需要看实际执行计划不能想当然。4.5 最迷惑的一种优化器觉得“全扫更划算”还有一类情况索引本身建得没问题但优化器算了一笔账认为全表扫描代价更低于是弃用索引。典型场景是单列索引区分度太低。上面提到status例子就是如此假如一张表500万行95%的行status都是“已支付”那按status过滤的“收益”太小优化器宁可全表扫。碰到这种情况把单列索引改成联合索引让过滤条件组合起来提高区分度是更合理的解法。这也是为什么我一直强调索引要跟着查询模式走不能被“哪个字段常用”误导。下面这张表汇总了常见失效场景和处理思路失效场景示例根因修复思路隐性类型转换phone_no 13800138000索引列发生类型转换参数类型与列类型保持一致函数运算DATE_FORMAT(created_at, ...)索引列被函数包裹改为范围查询前导模糊LIKE %abc%无法使用B树顺序结构全文索引/外部搜索引擎OR非索引列usernameabc OR status1OR条件破坏了索引路径拆成UNION低区分度status已支付优化器预估全扫更优联合索引提升选择性5. 用EXPLAIN验证索引效果别靠感觉优化加完索引不要只是“看起来行了”。我的习惯是每次调整索引之后都用EXPLAIN确认执行计划并记录优化前后耗时心里才踏实。5.1 EXPLAIN里最值得盯的几个字段type列取值从好到坏大致是const、eq_ref、ref、range、index、ALL。出现ALL说明全表扫要警惕。key列实际命中的索引名。如果是NULL说明压根没用到索引。rows列优化器预估要扫描的行数。这个值越小越好跟查询耗时强相关。Extra列出现Using index说明覆盖索引生效Using filesort说明文件排序Using temporary说明临时表这两个都值得优化。举个实际例子同样一条等值查询优化前后的EXPLAIN对比如下优化前type: ALLkey: NULLrows: 500万优化后type: refkey: idx_user_usernamerows: 1这一眼就知道索引生效了。5.2 加了索引却不用先刷统计信息有一种情况索引刚创建完跑EXPLAIN仍然没走索引。别急着删索引先想一下统计信息。InnoDB的优化器依赖统计信息来判断执行成本统计信息慢了或失真优化器就可能选错执行计划。这时候手动刷新一下统计信息往往就有改善ANALYZE TABLE t_user;还有更细致的排查方式用OPTIMIZER_TRACE看优化器的决策过程能看到它到底在选择什么、为什么放弃索引比盯着执行计划猜来猜去高效多了。线上遇到“加了索引不走”的诡异问题我建议第一步就打开trace。5.3 永远不要在索引列上做“加工”这一条算是最有价值的心法也是贯穿全篇的底层原则保持索引列干干净净。无论什么数据库索引列上做了运算、类型转换、函数套用都会破坏索引的有序性导致优化器无法匹配。写SQL时把计算、转换的任务交给“值的这一侧”而不是“列的这一侧”这是决定索引能不能生效的关键一环。我的最后一点实际体会建索引这件事写几行SQL简单真正考验的是对整个系统数据分布和查询模式的理解。我自己的习惯是每次建索引都在表结构的注释文档里写清楚“这个索引给哪类查询服务”避免几个月后没人知道某个索引该不该留、能不能删。同时在新上线前拉一批线上真实SQL样本逐一跑EXPLAIN并记录执行计划有对比、有依据后续出现性能问题追溯起来也有章可循。这样一套习惯下来SQL慢查询明显少了线上出问题的概率也低了很多。