1. 从业务现象到优化目标：一张慢 SQL 引发的血案
做后端开发的，多少都有过这种经历：线上系统毫无征兆地开始卡顿，接口响应从几十毫秒变成几秒甚至几十秒，用户投诉电话一个接一个，运维盯着监控大屏一脸惊慌，老板站在身后问“怎么回事”。最后翻出慢查询日志一看，罪魁祸首往往就是那么几条 SQL——明明就是简单的 where 查询，却在几百毫秒甚至几秒的时间里扫描了几十万行数据。
我以前接手过一个订单查询系统，列表页只需要按照用户 ID 和订单状态筛数据，结果一个接口动辄 3 秒以上。打开慢查询日志一看，核心查询走了全表扫描，扫描行数 60 万+，而且页面还会按创建时间排序、按金额做统计，每个子查询都在重复“搬砖”。那段时间我几乎每天都在跟索引打交道：建索引、调索引、拆 SQL、改存储过程，一圈折腾下来，接口耗时从 2.8 秒降到 250 毫秒左右，整整快了 10 倍不止。
这篇文章不打算写成教科书，那玩意儿翻三遍也救不了线上。我想用实际经验把“数据库优化”的完整链路讲清楚：怎么用慢查询日志定位病根、怎么根据 where 条件设计索引、主键索引和唯一索引到底该怎么选、常见哪些写法会让索引失效。如果你是刚接触数据库优化的工程师，或者被线上慢 SQL 折磨得焦头烂命，这篇文章应该能给你一套拿来就能用的实操思路。
2. 先做体检：慢查询日志与分析工具的正确打开方式
搞数据库优化，最忌讳的就是“感觉”。感觉这里慢、感觉那里该加索引，最后往往南辕北辙。正规做法是先让数据库自己把“慢”的 SQL 写下来，然后针对性地分析。
2.1 慢查询日志三步开启法
MySQL 的慢查询日志是一把手术刀，它会把超过指定阈值的 SQL 原原本本记录下来。开启方法很简单，在 MySQL 配置文件（通常是/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf）中加入这么几行：
[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1参数含义我逐个拆一下：slow_query_log是总开关，1表示开启；slow_query_log_file指定日志文件路径；long_query_time是阈值，单位是秒，我一般习惯设成1，也就是超过 1 秒的 SQL 都会被记录，线上环境如果日志量太大可以放宽到2；最后一个log_queries_not_using_indexes比较“狠”，它会额外记录所有没有走索引的查询，哪怕执行时间只有 10 毫秒——这也就是全表扫描、没命中索引的 SQL 一个都跑不掉。
配置完成后重启 MySQL 服务，等个一两天，日志里就会攒下大量真实业务的慢 SQL。这里要注意一个问题：慢查询日志本质是磁盘 IO 的额外开销，生产环境不建议一直全量开着，比较稳妥的做法是周期性开启（比如压测期、大促前），分析完立刻关掉。
2.2 手工慢查询模拟：先见见“病根”
如果你担心日志里的数据不够典型，或者干脆想主动复现慢 SQL，可以直接在命令行里跑一遍：
SET profiling = 1; SELECT o.order_id, u.user_name FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.status = 'PENDING' AND o.created_at > '2025-01-01' ORDER BY o.total_amount DESC;查询执行完之后，执行SHOW PROFILES;会列出所有已执行查询的耗时明细和对应的 Query ID，再执行：
SHOW PROFILE FOR QUERY 1;就能看到这条 SQL 内部各个阶段的耗时，比如Sending data时间特别长，往往意味着数据量大且索引不佳，排查重点就往索引方向上走。
2.3 日志分析的“主菜”：pt-query-digest
日志攒了一大堆之后，直接肉眼读是读不出什么的。Percona Toolkit 里的pt-query-digest是分析 MySQL 慢查询日志的神器，安装方式不赘述了（一般yum install percona-toolkit或官方源安装即可），用法也不复杂：
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt跑完之后打开slow_report.txt，你会看到两份关键内容：一份是全量慢查询的汇总排序，哪条 SQL 累计消耗时间最长、按执行次数和平均耗时的排名一目了然；另一份是每条 SQL 的执行计划概览，包括扫描行数、Rows examined、Rows sent 的比例关系。这里特别值得留意的是“扫描行数与返回行数”的对比——如果扫描 5 万行只返回 50 行，说明索引选择性太差，或者查询条件压根没走索引。
提示：除了 pt-query-digest，MySQL 8.0 以上还自带了
performance_schema和sys库，通过sys.statement_analysis视图同样可以查看高频慢 SQL 的排名。工具选哪个不重要，重要的是养成“先定位再优化”的习惯。
3. 索引选择的核心方法论：复合索引怎么建才科学
日志把慢 SQL 揪出来了，接下来才是真正的重头戏——怎么建索引。这一步直接决定系统能不能快起来。
3.1 从一条典型慢 SQL 拆起
我看过的慢 SQL 里，出现频率最高的是这种多条件组合查询：
SELECT * FROM orders WHERE user_id = 12345 AND status = 'PAID' AND created_at >= '2025-03-01' ORDER BY created_at DESC;没索引的时候，MySQL 只能全表扫描，逐行比对三个字段，再排序。如果订单表数据量是百万级别，这个查询就会卡得让你怀疑人生。
面对这样的 SQL，很多人第一反应是：三个条件字段都建单列索引，user_id建一个、status建一个、created_at再建一个。看起来没毛病，但实际效果往往很差。原因在于：MySQL 每个查询一般只能利用一个索引（基于索引合并优化的情况除外，而且效果远不如复合索引稳定），也就是说，哪怕三个单列索引全都建好，查询时 MySQL 也只能挑最有效的一个用，剩下两个字段只能回表再去过滤，依然会产生大量随机 IO。
正确的姿势是建一个覆盖三个字段的复合索引：
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);3.2 复合索引字段顺序：等值条件放前面，范围条件放后面
为什么顺序是“user_id、status、created_at”？这是由 B+ 树索引的存储方式决定的——复合索引的叶子节点按索引字段从左到右的顺序排序，先按第一个字段排，第一个字段相同再按第二个字段排，以此类推。这种排序方式意味着索引能直接“命中”前缀匹配的查询条件组合，但无法直接跳过前置字段去用后置字段。
所以设计复合索引时要记住两条铁律：
- 等值条件（
=或IN）的字段放在最前面，因为它们可以精确锁定索引区间； - 排序和范围条件（
>、<、BETWEEN、ORDER BY）的字段放在后面，让索引既做过滤又能直接提供排序结果，避免额外的 filesort。
用我上面的例子来说，user_id=12345是等值条件，放在索引第一个位置后，MySQL 可以直接在 B+ 树中定位到 user_id=12345 的连续区间；接着在区间内用status='PAID'这个等值条件继续缩小范围；最后用created_at做范围过滤。整个过程就是沿着索引从左往右“一层层剥”，每一层都能利用索引的有序性减少扫描量。
3.3 还要不要单列索引？组合与取舍的平衡
复合索引建好之后，单列索引怎么处理？我通常遵从三条原则：
- 如果复合索引的第一个字段恰好是高频查询条件，那单列索引可以删除，避免重复维护；
- 如果某个字段经常单独出现在 where 条件里，且不在其他复合索引的最左前缀中，那就保留单列索引；
- 如果某个字段只是偶尔用一下，建索引的收益不划算，可以直接不加。
索引不是越多越好。每建一个索引，写操作（INSERT/UPDATE/DELETE）都要额外维护一棵 B+ 树，磁盘占用也会明显增加。一个表上七八个索引，写性能下降是必然的。所谓“让系统快 10 倍”，往往不是加索引，而是合理地删掉很多没用的索引，同时留下几个最优的复合索引。
3.4 更狠的一招：覆盖索引直接消除回表
如果一个索引包含了查询需要返回的所有字段，那 MySQL 就完全不需要回表去读数据行，仅靠索引页就能返回结果。这种索引就叫覆盖索引，性能提升非常明显，尤其在统计类查询上效果堪称“暴力”。
还是拿订单表举例，如果列表页只需要展示order_id、total_amount、created_at三个字段，并且 where 条件依然是那块复合索引，那我们可以调整索引：
ALTER TABLE orders DROP INDEX idx_user_status_time; ALTER TABLE orders ADD INDEX idx_user_status_time_cover (user_id, status, created_at, total_amount);这样查询过程中，MySQL 从索引里拿到 user_id、status、created_at 做筛选，同时索引页里已经带着 total_amount，连回表都省了。我在实际项目里做过对比：同样的统计 SQL，普通索引模式下扫描 80 万行并回表，耗时 1.6 秒；改成覆盖索引后，耗时直接掉到 300 毫秒左右。
注意：覆盖索引不适合无脑覆盖所有字段，尤其不要包含大字段（如长 VARCHAR、TEXT）。索引页存储空间有限，字段越多，单个索引页能存放的条目越少，索引树层级会变深，反而拖慢查询。
4. 主键索引与唯一索引的区别，以及底层存储的视角
很多初学者一直分不清主键索引和唯一索引的区别，甚至以为建了唯一索引就等于建了主键。这个认知偏差在线上优化时很容易埋坑，值得单独拿出来讲。
4.1 两者到底差在哪
主键索引和唯一索引都能保证数据的唯一性，但本质上有几条差异：
| 对比项 | 主键索引 | 唯一索引 |
|---|---|---|
| 数量限制 | 每张表最多一个 | 一张表可以有多个 |
| 能否为空 | 不允许 NULL | 允许 NULL（且 NULL 可以重复） |
| 底层存储 | InnoDB 聚簇索引，数据行直接存在主键索引的叶子节点 | 普通二级索引，叶子节点存储主键值 |
| 作用 | 决定数据行的物理存储顺序 | 仅辅助快速查询定位，不影响物理存储结构 |
| 自动创建 | 建表时指定主键会自动创建 | 需要主动 CREATE UNIQUE INDEX |
这张表里最值得玩味的是“底层存储”那一行。InnoDB 引擎中，表数据本身就是按主键索引组织的，也就是所谓的聚簇索引。主键索引的叶子节点存的是整行数据，而所有二级索引（包括唯一索引）的叶子节点存的是主键值——所以走二级索引查询时，MySQL 会先在二级索引里找到对应主键，再回聚簇索引里搜一次整行数据。这就是回表行为的底层原理。
4.2 为什么 InnoDB 非要有主键索引
InnoDB 的聚簇索引设计决定了“物理数据行根据主键值排序存储”，如果没有显式定义主键，InnoDB 会选择一个非空且唯一的列做主键，如果连这样的列都没有，就隐式生成一个 RowID 作为主键。所以不要觉得“我的表可以不设主键”——从 InnoDB 的存储逻辑来看，它始终有一个隐式主键，只是不在业务表象里而已。
既然物理行按主键排序，那主键的设计就有讲究：建议使用自增整数或单调递增的 ID，而不是随机字符串或 UUID。原因很简单：插入新行时，如果主键值单调递增，新数据总是追加在当前 B+ 树最右侧的叶子节点上，不需要大量移动已有数据；如果主键是无序 UUID，新插入的数据可能落在树中间任意位置，频繁触发页分裂和节点重排，写性能下降非常明显。
4.3 视图加索引？索引和视图的边界
热搜词里有“oracle 视图加索引”这种说法，这里顺手把边界盘一下：Oracle 中普通视图本质上只是一个保存好的查询定义，本身不存储数据，自然谈不上“给视图加索引”；真正可以加索引的是物化视图，它把查询结果物化成了物理表。MySQL 目前没有物化视图的开箱特性（8.0 部分场景可以用生成列和索引模拟），所以如果听到“给 MySQL 视图加索引”这种说法，大概率是在混淆概念——你索引的实际上是把视图展开后的基表字段。
5. 常见索引失效场景与排查技巧实录
索引建好了，但线上慢 SQL 依然存在，最常见的玄学就是“我明明建了索引，查询怎么还是全表扫？”这个问题的背后，几乎都是索引失效场景在捣鬼。
5.1 索引失效“整活”排行榜
我整理了一份高频踩坑对照表，你在排查慢查询时可以逐项比对：
| 失效场景 | 典型写法 | 失效原因与解决思路 |
|---|---|---|
| 隐式类型转换 | WHERE user_id = '123'（user_id 是整型但传了字符串） | MySQL 会自动把字符串转成数字，导致无法利用索引；建议应用层传参严格对齐字段类型 |
| 对索引列做函数运算 | WHERE DATE(created_at) = '2025-03-01' | 对索引列套函数会让优化器无法直接利用 B+ 树的有序结构；改造为created_at >= ... AND created_at < ...范围查询 |
| LIKE 前置通配符 | WHERE name LIKE '%张三%' | 最左前缀匹配规则被破坏，索引无法从开头定位；如业务允许，改为'张三%'形式 |
| OR 连接非索引列 | WHERE status = 'PAID' OR amount > 1000 | 优化器可能放弃索引；可改写为UNION ALL两条独立查询，或对 OR 两侧字段都建立合适索引 |
| 索引列参与了计算 | WHERE user_id + 100 = 200 | 表达式导致索引失效；把计算挪到等号右侧 |
| IS NULL / IS NOT NULL 使用不当 | WHERE phone IS NULL | 单个索引对 NULL 的筛选能力有限，具体看优化器和数据分布；必要时可以设计“哨兵值”替代 NULL |
| NOT IN / <> 范围过大 | WHERE status NOT IN ('PAID') | 优化器认为回表成本太高，可能直接选择全表扫描；可改写为等值或范围查询 |
5.2 一条真实案例的排查过程
之前接手过一个类似“红包发放记录”的表，线上发现按 user_id 和活动日期查特别慢，慢查询日志显示扫描行数一直居高不下。我第一反应是索引没建对，但查了表结构发现(user_id, act_date)复合索引是建了的，于是用 EXPLAIN 看了一下执行计划：
EXPLAIN SELECT * FROM red_packet_records WHERE user_id = 10001 AND act_date = '2025-03-01';结果显示type = ALL，也就是全表扫描。这个结果很奇怪，于是我仔细检查了这条 SQL 的调用代码，发现应用层传进来的act_date是一个类似2025-03-01 12:00:00的完整时间字符串，而表字段本身是 DATE 类型。这里 MySQL 会对字段做隐式类型转换，导致索引失效。
解决方式很简单，要么应用层传入YYYY-MM-DD格式的日期，要么 SQL 改成WHERE act_date = DATE('2025-03-01')。改完之后再用 EXPLAIN，type变成了ref，扫描行数从几十万降到 3 行，耗时自然也就掉到了毫秒级。
这个案例很典型——索引结构没动，只是修正了查询写法，性能就差了百倍。所以遇到“建了索引但没效果”的诡异问题，先不要怀疑索引，而是先看 SQL 写法有没有破坏索引可用性。
5.3 SELECT * 和分页的隐藏杀手
除了索引失效，很多人还会忽略两个“慢性杀手”：无脑SELECT *和深分页。
先说SELECT *。返回所有字段意味着大量无关字段要进入网络传输和内存，尤其有 TEXT 或 JSON 类型字段时，回表数据量直接暴涨。正确的做法是在满足业务展示需求的前提下，只查必要的列，并且尽量让这些列包含在索引中（即覆盖索引）。
再谈深分页。LIMIT 100000, 20在老数据表上能跑出十几秒，原因在于 MySQL 是先扫描出前 100020 行，再丢弃前 100000 行，扫描行数等于偏移量加返回数。优化手段常见有两种：第一种，用记录上一个查询最大 ID（或最小 ID）的方式，把分页改成WHERE id > max_id LIMIT 20；第二种，用延迟关联，先在索引上找到符合条件的 ID 集，再回表取其他字段，减少回表行数。
-- 延迟关联示例 SELECT t.* FROM ( SELECT id FROM orders WHERE user_id = 12345 AND created_at > '2025-03-01' ORDER BY id LIMIT 100000, 20 ) tmp JOIN orders t ON tmp.id = t.id;5.4 存储引擎差异：索引是 InnoDB 的“主场”
既然聊到这里，顺便把热搜词里的“MySQL 的存储引擎”也说一下。绝大多数线上业务表应该用 InnoDB，它支持事务、支持行级锁、崩溃恢复能力强，而且聚簇索引设计让查询走主键速度飞快。MyISAM 虽然在某些只读场景下查询可能更快，但它没有事务，表级锁在并发条件下就是灾难，现在基本只在一些历史遗留系统里存在了。
索引相关操作在不同存储引擎下表现也不同：InnoDB 的二级索引叶子节点存主键值，所以主键不要太长；MyISAM 的索引叶子节点存数据行物理地址，数据结构上是非聚簇的。如果你在和别人聊索引表空间问题，大概率也是 InnoDB 的聚簇索引机制在起作用——索引页和数据页都保存在表空间中，页大小和缓存命中率直接影响查询性能。
提示：全文检索要求“双向索引”类似的能力时，传统 B+ 树并不适合，MySQL 需要借助 FULLTEXT 索引或外部搜索引擎（如 Elasticsearch）。普通等值、范围、排序场景，B+ 树索引就是最优解。
6. 说一些 SQL 优化上面真正有效的“潜规则”
网上聊 SQL 优化，十篇文章恨不得八篇抄概念，真正到实战层面有用的往往就那几条老规矩，我在这里做个“黑话翻译”。
第一，能用等值不用范围，能用范围不用模糊。=和IN是最适合 B+ 树索引的定位方式，范围查询可以走索引但锁定区间变大，模糊匹配前置通配符直接废掉索引。所以设计查询时，与其说“怎么优化这条 SQL”，不如反思“这个查询条件能不能改成可索引的等值条件”。
第二，ORDER BY 和 GROUP BY 也要进索引设计。很多人只考虑 where 条件的索引，忽略了排序字段。如果 ORDER BY 字段包含在复合索引中且方向和索引一致，MySQL 可以直接按索引顺序读取数据，省去 filesort 的额外排序开销。GROUP BY 同理，本质上也是对字段做分组排序。
第三，能用 JOIN 小表驱动大表，别让大表驱动小表。优化器多数时候会自己判断，但 SQL 写法也可以引导：过滤条件更严格、预计返回行数更少的表应当作为驱动表。老版本的 MySQL 里，如果 JOIN 的关联字段上缺乏索引，强行用小表驱动大表效果也很差，所以关联字段必须建索引。
第四，不要一次 WHERE 后面挂十几二十个过滤条件。尽可能精简条件，让优化器能明确选择有效索引。条件越多，各字段的区分度越模糊，优化器走错索引的概率也越大。遇到“全字段搜索”的需求，优先接入搜索引擎，而不是硬靠 MySQL 扛。
第五，EXPLAIN 是检查 SQL 质量的通用语言。每次上线慢查询或重构 SQL，都养成看 EXPLAIN 的习惯，重点看type、key、rows、Extra四列。type从好到坏大致是const、eq_ref、ref、range、index、ALL，凡是看到ALL或rows高得离谱，赶紧回头查索引设计。
7. 优化完怎么量化和验证：避免“优化了个寂寞”
数据库优化最怕的情况是：一顿操作猛如虎，上线之后该慢还是慢。所以验证环节一点都不能省。
7.1 基于 EXPLAIN 的预验证
每次改完索引或 SQL，我至少会跑一次 EXPLAIN，看三个核心变化：type是否从ALL变成ref或range；key列是否显示命中了我们新加的索引；rows是否显著下降。如果索引建了但rows依然很高，可能需要重新评估索引字段的选择性。
7.2 真实耗时对比的注意事项
EXPLAIN 看的是执行计划，真实耗时还得用压测或实际请求来验证。我在本地通常用SET profiling = 1查看单条 SQL 耗时，到测试环境则用 JMeter 或简单的并发脚本模拟真实流量，记录 P95 和 P99 延迟。核心指标就两个：单次查询耗时、单位时间吞吐量（TPS/QPS）。
需要注意的是，索引优势在小数据量下不明显，甚至因为要额外查索引页，性能会略微劣于全表扫。所以测试环境的数据量一定要和生产量级相当，否则测试结果没有参考价值。生产环境做变更时，也要挑业务低峰期，先在预发环境小流量验证，再逐步放开。
7.3 监控与回归：让慢 SQL 无处遁形
慢查询日志不是开一次就关了，优化完还要持续观察。我建议在监控系统里加一条“慢查询数量”和“Top 慢 SQL 耗时”的看板，阈值设置为业务可接受上限。每天晚上定时扫描慢查询日志，把新增的慢 SQL 自动告警到 IM 群。这样一旦有同事写了不走索引的新 SQL，第二天早上就能被发现，而不是等线上被打爆了才仓促处理。
8. 我个人踩过的几个坑，以及最后恢复线上“门诊”的一点心得
文章写到这里，最后分享几条纯个人经验教训，比任何理论都有参考价值。
第一，索引不是银弹，过度索引反而拖垮写入。有一段时间我为了优化一个报表查询，给一张每天都在大量写入的表连加了四五个索引，结果报表查询是快了，但业务方的写入延迟涨了 30%。后来删掉了两个低效单列索引，只保留最关键的复合索引，写入和查询才回到平衡区间。优化永远是取舍，不存在“既要又要”。
第二，先看 SQL 再建索引，先改写法再改结构。很多慢查询是因为应用层写了不该写的逻辑，比如在循环里反复查同一类数据，或者把一个本可以在内存中处理的分组交给了数据库。优化 SQL 永远是成本最低、见效最快的操作，不到万不得已不要新增索引。
第三，线上加索引这种事，别手滑。在千万级数据量的表上直接ALTER TABLE ADD INDEX会锁表（MySQL 8.0 之前尤其明显），必须用在线 DDL 工具（如 pt-online-schema-change）或者选择低峰期操作。我有一次真的因为大表加索引导致线上读写阻塞了几分钟，从那以后就养成了“任何 DDL 变更都必须走变更评审”的习惯。
第四，慢查询日志里的时间阈值不要设太低。很多人图省事直接设成0.1，结果日志每秒钟刷出几百条无意义记录，磁盘很快被撑爆，反而掩盖了真正的问题。阈值先设 1 秒跑几天，观察 Top 慢 SQL 的量级，再动态调整。
数据库优化这件事，本质上就是一场“建模比赛”——理解数据量的量级，理解 B+ 树存储特点，理解业务查询模式，然后针对性地设计索引和 SQL 写法。没有一招鲜的绝活，但按照“定位慢查询 -> 分析执行计划 -> 设计复合索引 -> 验证对比 -> 持续监控”这个流程走下来，绝大多数慢 SQL 都能在半小时内找到出路。希望我这篇文章能帮你少踩几个我当年踩过的坑。
