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-digestMySQL 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_id12345是等值条件放在索引第一个位置后MySQL 可以直接在 B 树中定位到 user_id12345 的连续区间接着在区间内用statusPAID这个等值条件继续缩小范围最后用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 123user_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 的筛选能力有限具体看优化器和数据分布必要时可以设计“哨兵值”替代 NULLNOT 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)。改完之后再用 EXPLAINtype变成了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或rangekey列是否显示命中了我们新加的索引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 都能在半小时内找到出路。希望我这篇文章能帮你少踩几个我当年踩过的坑。