上周有个同事甩给我一条慢查询日志说 MySQL 索引建了数据也就几百万行跑起来还是蜗牛速度。我打开 EXPLAIN 一看索引类型用得不对劲查询条件里对索引列套了一层 DATE 函数优化器根本没法用索引直接全表扫描。这种场景我在后端排查里见过太多次了——很多人对“建索引”的理解停留在“加上就完事”真碰上慢查询要选哪种索引、索引为什么会失效完全没概念。这篇文章把 5 种索引的“图书馆用法”一次讲明白从索引类型到慢查询定位再到索引失效避坑都覆盖。适合刚入门 MySQL 的菜鸟也适合被慢查询折磨过的开发。1. 为什么“建了索引还慢”先把索引理解成图书馆的检索系统1.1 索引的本质把“翻书架”变成“查目录”图书馆有几十万本书想找一本《高性能MySQL》你不会从一楼第一排开始挨本翻而会先去检索系统查索书号再按编号走到对应书架。MySQL 索引干的就是这件事提前给某一列或某几列排好序建立一套能快速定位的“目录”。InnoDB 引擎里索引底层结构是 BTree。以 InnoDB 为例一张表是按主键索引组织的数据行就存在主键索引的叶子节点上其他索引叫二级索引叶子节点存的是主键值。查询时先用索引找到主键再回表拿完整行。理解这个基本结构后面很多坑都能想通。1.2 慢查询不等于没建索引问题往往出在“用错目录”我把常见的“索引建了但查询还是慢”归成四类排查时按这个顺序对照自己的 SQL第一类是索引压根没被选中。字段确实有索引但查询条件写成了函数包裹、隐式类型转换、前导模糊匹配优化器评估后觉得用不了干脆走全表扫描。第二类是索引选中了但回表太多。比如 LIMIT 100000, 10 这种深分页前面十万行都要回表取一遍再丢弃自然快不了。第三类是索引区分度太低。性别、状态这类字段只有几个值优化器可能觉得走索引要读大量重复记录不如全表扫。第四类是复合索引设计不合理。索引列的顺序与查询条件不匹配最左前缀失效部分条件没法用索引过滤。这里想多说一句索引不是“建了就完事”索引类型、列顺序、查询写法、数据分布都影响最终效果。这也是我写这个系列第一篇的初衷先把索引类型选对后续搞优化才有意义。1.3 BTree 层数、回表和 IO 开销BTree 的每个节点是一个磁盘页默认 16KB。以三层高度为例第一层根节点和第二层内部节点加起来可以管理千万级别的主键记录也就是说在主键等值查询时大概 2~3 次磁盘 IO 就能定位到叶子。二级索引的查询路径是先查二级索引 BTree拿到主键再去主键索引 BTree 回表取完整行。如果一条 SQL 匹配了 5 万行那就可能回表 5 万次。每次回表都是一次随机 IO累积起来非常可观。所以看到执行计划里 rows 很大、Extra 里又没有 Using index就要有回表开销的警觉。这章想传达一个核心思路MySQL 慢查询调优不是“堆索引”而是搞清楚查询在索引结构上到底怎么走、走几步、每一步扫描多少行。带着这个思路我们看 5 种索引的用法。2. 5种索引的“图书馆用法”逐一拆解2.1 主键索引索书号一查一个准图书馆每本书都有唯一索书号按这个编号能精确找到书。MySQL 里主键索引就是这个角色而且 InnoDB 的数据行直接就存放在主键索引的叶子节点上所以你按主键查不需要回表。建表时建议用自增整型主键而不是 UUID。原因很简单BTree 的叶子节点是有序的自增主键插入时直接追加到最后一页写效率最高UUID 是随机字符串主键顺序被打乱插入会频繁触发页分裂产生碎片写入性能明显下降。当然分库分表场景会用雪花 ID 等方案这是业务妥协不是在常规单库场景里主动选 UUID 的理由。主键索引一般不需要手动加建表指定 PRIMARY KEY 就自动生成。CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;2.2 唯一索引图书馆的座位号/ISBN唯一索引和普通索引的核心区别就一个字值不能重复。你可以把唯一索引理解成图书馆里每个座位的固定编号或者一本书的 ISBN在整个馆里绝对唯一。它适合登录用户名、订单号、手机号这类业务上要求不重复的字段。建表时如果字段业务上必须唯一别只靠普通索引 应用层判断并发一高就容易出重复数据数据库层直接用唯一索引兜底更稳妥。-- 建表时指定 CREATE TABLE member ( id INT PRIMARY KEY, login_name VARCHAR(32) NOT NULL, UNIQUE KEY uk_login_name (login_name) ); -- 后续添加 ALTER TABLE member ADD UNIQUE INDEX uk_login_name (login_name);唯一索引也是可以联合的比如同一场活动下每个用户只能报名一次就建 (activity_id, user_id) 联合唯一索引。注意唯一索引在插入和更新时会多做一次唯一性检查写入性能略低于普通索引所以不要所有字段都加唯一索引只在业务真正需要时使用。2.3 普通索引书末目录找单列条件普通索引是最常见的单列索引。它不要求唯一可以重复就像一个书末主题索引你想找所有讲“慢查询优化”的内容翻目录能定位到一系列页码。什么时候建普通索引WHERE 条件里出现频率高的字段、JOIN 的关联字段、以及需要排序的字段都可以考虑。比如订单表查某用户的订单列表user_id 就该建普通索引。CREATE INDEX idx_user_id ON orders(user_id);这里要提一个菜鸟常犯的错不要看到查询慢就随便给一个列加索引。如果这个列区分度极低比如一张表的 status 字段只有 1 和 2 两个值加普通索引经常是浪费。优化器算一下扫描一半数据还不如直接全表扫索引就不会被用上。普通索引的最优候选是“区分度高、查询频次高、写入频率可接受”的列。2.4 复合索引多级目录最左前缀是核心规则复合索引也叫联合索引是多个列按顺序组成的索引就像图书馆里“分类-作者-书名”的多级目录。你只看作者名找书不提供分类这套目录几乎没用但你按“分类作者”查的时候就比单看作者快得多。MySQL 的复合索引遵循最左前缀原则查询条件必须从索引最左侧的列开始才能用到这个索引。假设建了索引 (col_a, col_b, col_c)where col_a ? 可以用where col_a ? and col_b ? 可以用where col_a ? and col_b ? and col_c ? 可以用where col_b ? and col_c ? 不能用where col_a ? and col_c ? 部分能用但 col_c 条件无法完全利用设计复合索引时我的一般套路是先看 WHERE 里有哪些等值条件把等值列放最前面再看有没有范围条件范围列放最后如果还有排序字段尽量让排序字段也走索引。一个经验原则等值优先、高频优先、范围永远放末尾。CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);上面的索引适合“user_id 等值 status 等值 created_at 范围”这类查询。但如果业务经常只用 status 过滤这个复合索引就不适用需要结合真实 SQL 再权衡。2.5 全文索引正文内容的关键词搜索引擎前面几种索引适合结构化字段的等值、范围、排序但碰到大文本字段的 LIKE %关键词% 这类需求普通索引完全失灵因为前导 % 让 BTree 无法利用有序性。这时该上全文索引。全文索引用倒排索引思路把文本内容切词后建立关键词到文档的映射配合 MATCH...AGAINST 语法速度比 LIKE %xx% 快很多。InnoDB 从 5.6 开始支持全文索引但中文分词默认要配置 ngram parser否则中文文本无法正确切词。ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content(title, content) WITH PARSER ngram; SELECT title FROM articles WHERE MATCH(title, content) AGAINST(MySQL 慢查询 IN NATURAL LANGUAGE MODE);全文索引不是万能的它的维护成本高写放大明显短文本、高频更新的业务不适合。类似搜索引擎能力比较重的场景更稳妥的方案是上 Elasticsearch但小项目、单表数据量可控、不想引入额外组件时MySQL 自带全文索引是最省事的。2.6 一张表看懂 5 种索引索引类型图书馆类比适合场景创建示例注意点主键索引索书号按主键定位PRIMARY KEY (id)建议自增整型唯一索引座位号/ISBN业务唯一约束UNIQUE KEY uk_name (login_name)写入有额外检查普通索引书末目录WHERE 高频单列INDEX idx_user_id (user_id)区分度低就别建复合索引多级目录多条件查询INDEX idx_a_b_c (a, b, c)必须守最左前缀全文索引正文关键词索引大文本模糊搜索FULLTEXT INDEX ft (content)注意中文分词3. 实操慢查询日志 EXPLAIN 定位“假索引”3.1 打开慢查询日志先把问题 SQL 找出来MySQL 的慢查询日志是排查性能的第一入口。默认情况下 slow_query_log 很可能是关闭的需要先开启。-- 查看当前状态 SHOW VARIABLES LIKE slow_query%; -- 动态开启重启失效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 建议先设置成 1 秒观察一段时间再调整。log_queries_not_using_indexes 可以打印“没走索引的查询”在生产环境一般不开免得刷屏在测试环境排错很有用。日志落盘后别用眼睛刷用自带工具mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log这个命令按平均执行时间倒序取出最慢的 20 条 SQL。先用它定位到需要优化的高嫌疑 SQL再进执行计划分析。3.2 EXPLAIN 执行计划type、key、rows、Extra 逐列看拿到问题 SQL 后前面加 EXPLAIN 即可EXPLAIN SELECT * FROM orders WHERE user_id 123 AND created_at 2024-01-01;初学者最容易盯着 key 列看发现 key 有值就以为走索引了。其实要组合看几个关键列列名含义怎么看type访问类型ALL 是全表扫描index 是扫整个索引树ref 是非唯一等值range 是范围const 是主键/唯一等值。从 ALL 到 const效率依次变好possible_keys可能用的索引不代表最终使用key实际选择的索引为 NULL 说明没用任何索引rows预估扫描行数越小越好是大致估算不能全信Extra附加信息Using index 是覆盖索引Using filesort 是文件排序Using temporary 是临时表比如 typeALL、rows2000000、keyNULL基本就是全表扫两百万行。如果 typeref、rows100、keyidx_xxx但 Extra 是 Using where还要看 where 条件里有没有非索引列过滤和回表。3.3 实战案例一条慢 SQL 从全表扫到 20ms我拿一个常见的订单查询举例。表数据 200 万行慢 SQL 是SELECT id, user_id, status, amount, created_at FROM orders WHERE user_id 123 AND status 1 ORDER BY created_at DESC LIMIT 20;优化前 EXPLAIN 大致是typeALLrows2000000ExtraUsing where; Using filesort也就是说全表扫一遍把所有 user_id123 的行找出来再做一次文件排序最后取 20 行。文件排序尤其贵数据量大时往往比索引查找慢一个量级。根据之前的原则user_id 是等值条件、status 是等值条件、created_at 是排序/范围条件所以我建了复合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);再跑 EXPLAINtyperefkeyidx_user_status_timerows 大概几十ExtraUsing index condition为什么 rows 只有几十因为 BTree 可以按 user_id 和 status 定位并且在叶子节点里按 created_at 有序排列ORDER BY created_at 不再需要额外文件排序。查询直接从最有价值的位置顺序读 20 条速度自然快了。如果业务高频 SELECT 的列都在索引里还能进一步做覆盖索引。比如只查 id, user_id, status, created_at 四列上面索引已经包含Extra 会显示 Using index连回表都省了。遇到这种写法建议把 SELECT 列表控制到尽量贴合索引列性能收益很直观。3.4 索引建了但优化器不用先 ANALYZE TABLE还有一种很气人的情况索引分明存在SQL 也没写错EXPLAIN 就是全表扫描。这时候常常是统计信息不准。InnoDB 的优化器依赖表统计信息来估算 rows。如果大量增删改后没有更新统计信息优化器可能认为“走这个索引要扫 80% 的行”于是选全表。解决办法是先做一次ANALYZE TABLE orders;如果表数据本身波动很大、写入频繁可以写个定时任务在低峰期周期执行。碎片也顺便说一下频繁删除会产生页碎片OPTIMIZE TABLE 能重建表和索引但会锁表必须在业务低峰做或者考虑用 pt-online-schema-change 这类工具在线处理。4. 索引失效的 7 个坑菜鸟必看的避雷清单4.1 复合索引不满足最左前缀这是索引失效里出现频率最高的一种。索引 (a, b, c)你查 where b? and c?优化器没得用直接全表扫。MySQL 8.0.13 之后优化器对部分查询支持跳跃扫描Skip Scan但这不代表可以随便乱写把 SQL 写成符合最左前缀的形态是成本最低的优化。另外有个小细节where a? and b? 和 where b? and a? 在等值条件下优化器会自动重排不影响索引使用。但一旦中间出现范围、排序条件顺序就很敏感。4.2 对索引列使用函数或计算最常见的写法WHERE DATE(created_at) 2024-01-01只要对索引列套了函数、做运算BTree 的有序排列就用不上。改成范围条件WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00同理避免在 WHERE 里写 amount * 0.9 100 这类表达式。要优化把表达式的量放到另一边让索引列保持原样。4.3 隐式类型转换如果字段 vc_code 是 VARCHAR查询写成 WHERE vc_code 123MySQL 会把字符串列转成数字比较索引大概率失效。排查时看到 typeALL 且 possible_keys 里有值可以查一下字段和常量的类型是否一致。反过来int 列用字符串常量 123 一般不会失效因为优化器会把字符串常量转换成数字但既然要避免问题最好还是让 SQL 里的类型和字段类型严格对应。这套经验在数据迁移、接口对接时尤其常见。4.4 LIKE 前导模糊查询普通 BTree 索引只能处理 LIKE keyword% 这种前缀匹配。WHERE name LIKE %keyword% 无法使用索引因为无法确定有序遍历的起点。如果确实需要模糊搜索要么用全文索引要么考虑引入外部搜索引擎或者把业务需求降级成前缀匹配。注意右模糊 LIKE keyword% 是可以走索引的因为 BTree 可以按前缀范围扫描。这个是菜鸟最容易被面试题吊打的地方也是实际操作中最容易优化的点。4.5 OR 连接非索引条件WHERE user_id 123 OR status 1user_id 有索引status 没索引OR 一连接优化器可能直接全表扫因为无法对两条路径做高效合并。改成两个查询 UNION ALL或者给 status 也建索引。能用 UNION 的前提是结果集可接受重复时用 UNION ALL避免去重排序的开销。4.6 范围查询导致后续列失效联合索引 (a, b, c)查询 WHERE a1 AND b BETWEEN 10 AND 20 AND c3。a 走等值b 走范围c 无法继续用索引过滤只能对符合 b 范围的行做回表再过滤。这是复合索引设计里最考验功力的一环。解决方案有三种一是把范围列 b 放到索引最后让 c 能继续利用二是把 c 列也做成“等值范围”两套索引三是评估 c 区分度低的话直接接受回表。具体选哪种没有银弹要在 EXPLAIN 里拿真实 rows 对比。4.7 深分页和大排序LIMIT 100000, 20 看着只是取 20 条实际要把前 100020 条都扫描回表后再丢弃。优化方案是延迟关联-- 先只取主键再关联回原表 SELECT t1.* FROM orders t1 JOIN ( SELECT id FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 100000, 20 ) t2 ON t1.id t2.id;子查询只走了索引不碰大字段拿到 20 个 id 后再回原表取完整行。这个模式在后台管理列表里非常实用。5. 索引设计经验与日常维护5.1 索引不是越多越好很多表被加了一堆冗余索引比如有了联合索引 (a, b)又单独建了索引 (a)。后者在绝大多数场景下是浪费因为联合索引已经可以服务前缀查询。索引越多写入时维护的成本越高磁盘占用越大一个表十几个索引写入性能会被明显拖慢。建议定期用 SQL 从 information_schema.statistics 里导出索引清单人工核对冗余。有条件的可以直接上 pt-duplicate-key-checker。删除索引前先确认没有历史慢查询在依赖它。5.2 用区分度决定索引值不值区分度 COUNT(DISTINCT 列值) / COUNT(*)。区分度 1 最好0.1 以下就要谨慎。前面提的状态字段只取 1/2COUNT(DISTINCT)2区分度极低单独建出来大概率没人用。但区分度不是唯一指标。区分度一般、却经常等值查询的字段放在联合索引最左边依然有价值因为等值条件下它能快速把范围缩到很小。真正不该做的是“低区分度字段 无其他条件”的纯单列索引。5.3 定期维护统计信息和碎片统计信息过期ANALYZE TABLE 表名;碎片整理OPTIMIZE TABLE 表名;低峰执行查看表大小和碎片information_schema.tables 里 DATA_LENGTH、TABLE_ROWS、DATA_FREE慢查询日志定期归档避免日志文件无限增长5.4 一套快速自查命令清单把下面这套流程存下来遇到慢查询照做-- 1.看慢查询是否开启 SHOW VARIABLES LIKE slow_query%; -- 2.分析最近慢 SQL -- mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log -- 3.查看表结构里的索引 SHOW INDEX FROM orders; -- 4.查看执行计划 EXPLAIN SELECT ...; -- 5.更新统计信息后再看一次 ANALYZE TABLE orders;这套流程我基本每周都在用。性能调优没有玄学无非是把问题缩小到一条 SQL、一张表、一个索引用 EXPLAIN 验证每一步的变化。最后分享一点个人体会我在实际项目中踩过最深的坑就是上线前从不看慢查询日志等到用户报页面卡死才去救火。后来和 DBA 约定每两周拉一次慢查询日志把 top SQL 和索引清单对照检查一遍大部分性能事故都能提前消灭。索引不是建完就一劳永逸它是跟着业务查询走的活文档把 5 种索引的“图书馆用法”吃透至少能救回 80% 的慢查询。