企业官网建设流程全解析

你有没有遇到过这样的场景：上午十点半业务高峰期，监控后台突然飘红，某条SQL平均执行时间从200毫秒飙到7.8秒，数据库CPU瞬间打满，一堆请求在连接池排队，紧接着就是线上告警轰炸。等你火急火燎地查完慢日志，发现就是一条查订单的语句，开发同事盯着执行计划一脸无辜：主键索引明明建了，条件也写对了，怎么就慢成这样？

这是我在好几个团队里都撞过的“经典剧本”。SQL优化这个东西，网上讲理论的帖子一大堆，什么B+树、什么最左前缀，背起来头头是道，真到了线上，大多数人还是不知道怎么定位问题、怎么下手改。原因很简单：优化不是一个“知道几个知识点”就能搞定的操作，它是一个完整的排查、分析、设计、验证链路。这篇东西，我不打算给你念教科书，我想把我在真实生产环境里做SQL优化的一套打法完整地拆开讲——从索引的底层原理，到慢SQL排查的具体工具和步骤，再到索引失效的典型场景和几个实战改写案例，每一步都给你看实际的数据和执行计划。不管你是刚接触数据库的初级开发，还是带团队的老手，这套方法论应该都能直接用得上。

1. 慢SQL问题为什么会成为生产事故的元凶

1.1 一条慢SQL的连锁反应：从连接池排队到数据库雪崩

很多人对慢SQL的危害没有体感，觉得“就慢几秒嘛，用户等一会就好了”。但你如果经历过一次真实的线上故障，就会发现事情远没有那么简单。

数据库处理请求是并发的，但每个连接在同一时刻只能执行一个语句。假设你的应用连接池配置的是50个连接，正常情况下每条SQL执行50毫秒，一个连接一秒能处理20个请求，50个连接就是1000 QPS，完全够用。现在突然来了一条执行10秒的慢SQL，它霸占着一个连接长达10秒，这期间这个连接什么事都干不了。如果业务高峰期同时有几条这种SQL跑起来，连接池的可用连接迅速被占满，后面的请求全部要排队等空闲连接。此时应用层的表现就是：接口响应时间从100毫秒涨到3秒、5秒、10秒，监控上看过去就像一道陡峭的悬崖线。

更麻烦的是连锁效应。连接池满了之后，新的请求进不来，应用服务器的线程也跟着堆积，请求积压到一定量就会拖垮应用本身的CPU和内存。紧接着其他的快SQL也因为抢不到连接而变慢，数据库负载持续升高——最终形成“慢SQL占满连接 → 快SQL被拖慢 → 连接池彻底被占满 → 数据库和应用双双崩溃”的雪崩链路。我在生产环境里见过一次最夸张的案例：一条因为索引失效导致全表扫描的查询，单次执行也就2秒出头，但它在凌晨的数据批处理任务里被循环调用了3000多次，直接把整个库的IO拖到100%，第二天早上业务方打开后台发现所有报表都是空的。

所以第一条原则请你记住：慢SQL不是“用户等一等”的问题，它是数据库系统稳定性问题的根因之一。对待慢SQL的态度应该是“零容忍”，而不是“反正还能跑”。

1.2 为什么业务代码没变，SQL却越来越慢

还有一种非常常见的困惑：这段SQL上线一年多了，一直跑得好好的，最近怎么突然就慢了？代码一行没改，索引一个没动，数据库还是那个数据库，问题出在哪？

答案就藏在数据量里。关系型数据库的索引结构是B+树，数据量小的时候，比如表里只有1万行，哪怕没有索引，全表扫描也就扫一万行，快得很。但表数据涨到500万行、1000万行的时候，没有索引的全表扫描意味着要一页一页地把整个表的数据从磁盘读到内存，再逐行过滤一遍。这个IO开销是指数级增长的——数据量涨十倍，扫描成本可能涨几十倍。

另一个隐蔽原因是统计信息过期。MySQL的优化器决定走不走索引，依赖的是表统计信息（行数、索引基数、数据分布等）。当表数据量发生较大变化，而统计信息没有及时更新时，优化器拿着过期的统计信息做判断，就可能做出错误选择——明明有索引可用，它偏要全表扫描。这就是为什么有些SQL今天走索引、明天就走了全表扫描，看起来很玄学，其实是统计信息在作祟。

还有一类原因是数据分布偏移。比如一个订单状态字段，90%的订单都是“已完成”，你查“已完成”的订单，优化器一算，走索引还得回表好几百万次，还不如直接全表扫一遍，于是它果断放弃了索引。这种情况下不是索引失效，而是“索引选择性太低导致优化器主动弃用”，这也是SQL优化里最容易被误解的地方之一，后面我会专门展开讲。

提示：排查慢SQL一定要带着“时间维度和数据维度的变化”去看问题，不要只盯着SQL本身。同一句SQL，今天慢和昨天快，背后的原因大概率差在数据上，而不是差在写法上。

2. InnoDB索引底层原理：为什么B+树能扛住千万级数据

2.1 聚簇索引与二级索引：一张表的主键和数据到底怎么存

要理解SQL优化，绕不开索引的底层结构。MySQL默认的存储引擎InnoDB用的索引结构是B+树，你可以把B+树想象成一个多层的图书检索系统：顶层目录页告诉你哪个区域放哪类书，中间层目录页告诉你具体书架号，叶子节点才是真正的图书位置。和普通二叉树不一样的是，B+树的叶子节点之间是有指针串联的，这就能高效地做范围查询。

InnoDB里有两个核心概念你必须搞清楚：聚簇索引（Clustered Index）和二级索引（Secondary Index）。

聚簇索引就是主键索引，它的叶子节点存的是整行数据。换句话说，InnoDB的表本身就是按主键顺序组织存储的，你建了一个主键，就相当于给整张表的行数据排好了物理顺序。这带来两个特性：第一，通过主键查数据，直接能拿到整行，不需要额外跳转；第二，插入数据如果主键不是递增的（比如随机UUID），就会造成页分裂和碎片，写入性能会明显下降。

二级索引的叶子节点存的是“索引列的值 + 主键值”。这里没有整行数据，只有索引字段值和对应的主键。所以当你通过二级索引查数据时，流程是：先在二级索引的B+树里找到匹配的叶子节点，拿到主键值，然后再回到聚簇索引里去取整行数据。这个“再回去取一次”的动作，就是SQL优化里臭名昭著的回表（Bookmark Lookup）。

回表不是免费的。每回表一次，就是一次随机的IO操作——注意是“随机IO”，而顺序IO比随机IO快一个数量级。一个查询命中了1万条二级索引记录，就要回表1万次，每次都像在图书馆里随机抽一本书，成本可想而知。

2.2 回表与覆盖索引：为什么覆盖索引能带来数量级的性能提升

既然回表这么伤，那就不回表呗。这就是**覆盖索引（Covering Index）**的来头。

假设你有一条查询：SELECT user_name FROM user WHERE status = 1。如果user表上有一个(status, user_name)的联合索引，那么B+树的二级索引叶子节点里就同时包含了status、user_name和主键id。优化器一看，你要的字段在索引里全都有，压根不需要回表去聚簇索引拿数据，直接扫描二级索引就完事了。

这个提升有多大呢？我做过一个实际测试：一张580万行的订单表，查询条件是order_status = 3，需要返回order_no字段。不带覆盖索引时，这条SQL要回表约14万次，执行时间820毫秒；加上覆盖索引之后，执行时间直接降到41毫秒。整整20倍的差距，靠的就是“少回表14万次”。

这就是为什么我一直强调：你所写的SELECT字段列表，应该只包含你真正需要的列，而不是一律SELECT *。字段越多，覆盖索引的命中概率就越低。很多开发图省事写SELECT *，结果就是每个二级索引查询都伴随大量回表，白白浪费性能。

2.3 联合索引的最左前缀原则：为什么查b=1用不上(a,b)索引

聊到联合索引，就必须讲清楚最左前缀原则，这是面试重点，也是实际优化中最容易踩坑的地方。

假设你建了一个联合索引(a, b, c)，这个索引内部的排序规则是：先按a排序，a相同再按b排序，b相同再按c排序。类比查字典，先查首字母，首字母相同再查第二个字母，依次类推。所以这个索引能高效支持的查询组合是：

• WHERE a = 1：直接定位
• WHERE a = 1 AND b = 2：a定位后再b定位
• WHERE a = 1 AND b = 2 AND c = 3：完全匹配
• WHERE a = 1 AND c = 3：a定位，c用不上索引排序但在a的范围内过滤，还是能大幅缩小范围

但如果你只查b = 2或者c = 3，即查询条件里没有包含最左边的a列，那么这个联合索引就无能为力了。原理不复杂：B+树是先按a排序的，你现在跳过a直接按b去查，就好比你在一本按“首字母-次字母-第三字母”排序的词典里找一个确定的次字母，但你不知道首字母是什么，只能整本翻。

实际工作中“最左前缀失效”最常见的有两种错误：一种是开发不看索引定义，随意调整WHERE条件的列顺序——注意，WHERE里条件的书写顺序不影响索引，优化器会帮你调整；真正影响索引使用的是“查询条件里缺没缺最左列”。另一种是范围查询放在联合索引中间，比如(a, b)索引碰上WHERE a > 100 AND b = 5，a的范围条件导致b的排序在a的范围内不再全局有效，b这个条件只能作为filter过滤，无法继续走索引定位——这就是“范围查询中断索引后续列”的规则。

3. 一条慢SQL的完整排查链路：从发现问题到定位根因

3.1 开启慢查询日志与阈值设置：先学会让数据库“主动汇报”

SQL优化不是靠猜的，第一步永远是数据采集。生产环境里我建议把慢查询日志、performance_schema、监控平台这三件事都配好，否则等用户反馈“系统卡了”再回头看，往往已经错过第一手现场。

MySQL的慢查询日志配置很简单，核心参数有这几个：

-- 开启慢查询日志 SET GLOBAL slow_query_log = ON; -- 慢查询阈值，单位秒，线上建议设成1秒甚至0.3秒 SET GLOBAL long_query_time = 1; -- 记录没走索引的SQL SET GLOBAL log_queries_not_using_indexes = ON; -- 慢查询日志文件路径 SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

注意，long_query_time单位是秒，线上业务我的习惯是先从1秒开始看，如果系统流量大、慢SQL多，再逐步降到0.5秒甚至0.1秒。设太严会生成海量日志，淹没有价值的信息；设太松又刷不到那些“慢性病”SQL。

日志拿到了之后怎么看？慢查询日志里每一行记录都包含SQL文本、执行时间、锁等待时间、扫描行数、返回行数等关键信息。我最关注三个数字：Rows_examined（扫描了多少行）、Rows_sent（返回了多少行）、Query_time（执行耗时）。

如果Rows_examined是几百万而Rows_sent只有几十，这个比例一出来，几乎可以立刻判定：查询在扫描大量无效数据，索引设计或者SQL写法有问题。反过来，如果Rows_examined很小但Query_time仍然很高，那问题可能不在SQL本身，而在锁等待或者IO资源争抢上——这时候就得看Lock_time和执行期间数据库的负载状态了。

3.2 EXPLAIN执行计划逐行解读：type、key、rows、Extra到底说明什么

拿到慢SQL之后，下一步就是用EXPLAIN去看执行计划。这是SQL优化里最核心的一项技能，我见过不少初级开发会执行EXPLAIN，但只会看一眼key列有没有索引，说实话这个姿势有点浪费。

一张执行计划表，我们一列一列来过：

• type列：这是访问类型，性能从好到差大致是：system>const>eq_ref>ref>range>index>ALL。const和eq_ref是最理想的状态，说明通过主键或唯一索引精确定位单行；ref是普通二级索引等值匹配；range是索引范围扫描，也能接受；看到index和ALL就要警惕了，ALL就是全表扫描，index虽然扫的是索引，但如果索引很大，同样不便宜。
• key列：表示实际用到的索引。如果这一列是NULL，说明这条SQL没走任何索引。
• rows列：优化器预估扫描的行数。这是一个预估值，不一定准确，但量级非常有参考价值。预估几十万行和预估几十行，性能差距天壤之别。
• Extra列：这里信息量巨大。看到Using index，说明这个查询是覆盖索引扫描，很健康；看到Using index condition，说明走了索引下推优化，也还不错；看到Using filesort，说明有额外的排序操作，如果排序数据量大就会很慢；看到Using temporary说明用到了临时表（常见于GROUP BY、DISTINCT），数据量大时会非常致命；最怕的是Using where; Using index; Using filesort三件套同时出现，后面我会用一个案例细讲。

举个实操例子，我之前处理过一条线上慢SQL：

EXPLAIN SELECT order_no, amount FROM trade_order WHERE user_id = 12345 AND status = 2 ORDER BY create_time DESC LIMIT 20;

执行计划显示type = ref，key = idx_user_id，rows = 128000，Extra = Using filesort。这就是很典型的问题结构：用了二级索引user_id过滤出了12.8万行，然后还要在内存里对这12.8万行做一次文件排序，最后才取20行。12.8万行的排序，一次请求没事，并发一上来，CPU就要烧起来了。

这种问题的标准解法，我会在第5节的实战案例里专门拆解，涉及(user_id, status, create_time)联合索引的重构。

3.3 用Performance Schema和Profile定位耗时黑洞

EXPLAIN告诉我们“怎么走”，但有时候还需要知道“时间花在哪”，这时候就需要Performance Schema或者SHOW PROFILE。

比较老的MySQL版本（5.x），可以用SET profiling = 1开启会话级性能分析，然后执行SQL后用SHOW PROFILE FOR QUERY 1;查看每个阶段的耗时。比如：

+----------------------+----------+ | Status | Duration | +----------------------+----------+ | Sending data | 0.823025 | | Sorting result | 0.214401 | | Statistics | 0.042125 | +----------------------+----------+

如果Sending data占了80%以上的时间，说明大量时间花在“读取和返回数据”上，这和扫描行数过多强相关；如果Sorting result占比高，那就是排序本身的问题，得从索引上想办法消除排序。

MySQL 8.0以后SHOW PROFILE被标记为废弃，推荐直接用performance_schema的events_statements_history_long表，或者更简单的方案：结合slow log里的Rows_examined和Lock_time判断。我的经验是，90%的慢SQL都能通过“扫描行数 + 执行计划 + 排序/临时表”这三个维度定位到根因，Profile属于进阶手段，碰到疑难杂症再上不迟。

4. 索引失效的七种常见场景与根因分析

4.1 函数包裹索引列：为什么DATE(create_time)会让索引直接报废

这是生产环境里出现频率最高的索引失效场景。开发同学为了方便，在查询条件里写：

SELECT * FROM trade_order WHERE DATE(create_time) = '2024-06-01';

听起来好像没什么问题，create_time本来就有索引，查某一天的订单不是很正常吗？但实际上，这条SQL走不了索引。原因在于索引B+树里存的是create_time的原始值，而你在查询条件里给这个列套了一个DATE()函数，相当于对索引列的每一行都要先做一次函数变换，再拿变换结果去和'2024-06-01'比较。这种情况下，B+树的“按值排序查找”机制就彻底失效了——你没法在不知道函数计算结果的情况下直接定位到某个范围，只能全表扫一遍，逐行算、逐行比。

解决办法很简单，就是把函数从列上挪走，改写范围查询：

SELECT * FROM trade_order WHERE create_time >= '2024-06-01 00:00:00' AND create_time < '2024-06-02 00:00:00';

同样的道理，WHERE YEAR(create_time) = 2024、WHERE MONTH(order_time) = 6、WHERE LEFT(id_card, 4) = '1101'这一类写法，全部都会让索引失效。这类问题的核心规律是：索引列不要做任何运算操作，无论是函数、算术运算还是隐式类型转换。

4.2 隐式类型转换：字符串列不加引号的代价

这个坑尤其隐蔽，因为SQL还能正常跑，结果也完全对，只是慢。

看这个例子：

SELECT * FROM user WHERE phone = 13800138000;

phone字段在表里定义的是VARCHAR(20)，但传入的是整数13800138000。MySQL的优化器在比较时，会把字段的类型转换成数值类型再比对，也就是对索引列phone做了隐式的类型转换。结果和上面一样——索引列被“加工”了，索引失效，全表扫描。

判断有没有这种问题很简单：执行计划里key为NULL，而你的查询条件明明就是一个普通列名。预防方法说白了就一条：写SQL时严格按字段定义的类型传参，字符串就是字符串，老老实实加引号。

这看上去是个三秒钟就能解决的小问题，但我在线上见过太多因为这个导致全表扫描拖垮IO的例子。尤其是一些团队用ORM框架写查询时，自动绑定参数的类型来自前端传参，前端传了个数字，底层就变成了隐式转换。所以从框架层面规范参数绑定逻辑也很重要。

4.3 前导模糊查询：为什么LIKE '%张%'注定走不了索引

关于LIKE查询，很多文章一句话概括：%写在左边索引失效，写在右边索引有效。这个说法基本对，但我想补充说明底层的“为什么”，理解了原因你就能举一反三。

B+树索引是按“从左到右”的顺序存储数据的。LIKE '张%'在B+树里可以理解为“查找所有以‘张’开头的字符串”，这种前缀匹配天然契合B+树的排序结构——先定位到第一个“张”的位置，然后顺序向后扫描直到‘张’字区间结束。但如果LIKE '%张%'或者LIKE '%张'，字符串的第一个字符是不确定的，B+树没法找一个确定的起点，只能全索引/全表扫描。

那实际业务里就是要做模糊搜索怎么办？几个替代思路：

• 反向记录：如果只需要后缀匹配，可以把字符串reverse一份存起来，然后用前缀匹配查反转后的值。
• 全文索引：MySQL的全文索引支持包含式搜索，适合文本检索场景。
• 搜索引擎/ES：这是更彻底的方案，上述方案覆盖不了的时候考虑引入外部搜索组件。

我的建议是：线上核心业务表，尽量避免大表的全字段模糊搜索，要么用前缀匹配，要么把搜索场景剥离到专门的搜索服务里。否则随着数据量增长，每一句LIKE '%xxx%'都是一个定时炸弹。

4.4 联合索引的顺序错位与范围查询中断

这一块我在2.3节已经讲了原理，这里补充一个具体案例。假设表上有联合索引(user_id, status, create_time)，下面的查询能用好它：

WHERE user_id = 100 AND status = 1 AND create_time > '2024-01-01'

完全匹配前两列再走范围第3列，最健康。但如果把条件顺序换成：

WHERE user_id = 100 AND create_time > '2024-01-01' AND status = 1

有多少人会觉得这个查询也能全用到索引？答案是：user_id定位之后，紧接着是create_time的范围条件，范围条件一出现，status就无法继续走索引精确定位了，只能作为普通过滤条件。Extra里大概率会出现Using index condition，status的过滤被下推到存储引擎层，它还是能帮忙减少返回行数，但索引“精确定位”的能力打折了。

实际工程里，联合索引既要管等值条件又要管范围条件的时候，一定要把等值条件的列放在索引的前面，范围条件的列放后面。顺序反了，索引就算没完全失效，也发挥不了全部威力。

4.5 OR条件连接：一个非索引条件让索引全面失效

OR这个运算符在SQL优化里是个“坑王”——它和AND完全不是一种待遇。

SELECT * FROM trade_order WHERE order_no = '20240601001' OR status = 3;

假设order_no上有唯一索引，status上没有索引。你会不会以为这条SQL会先走order_no索引查出结果，再和status = 3的结果合并？不会的。优化器面对OR的两个分支时，如果要走索引，它需要把两个分支分别查出来再去重合并。有些情况下优化器确实会这么做（比如两个分支都有索引，用index_merge策略），但更多时候，只要有一个分支不能使用索引，优化器就会干脆放弃索引，选择全表扫描。因为逻辑上它必须把status = 3的行全部捞出来，这本身就是一个全表扫描的工程量。

所以我的习惯是：能用UNION ALL改写就改写，尤其是OR的其中一个分支条件没有索引时。比如上面的SQL可以改成：

SELECT * FROM trade_order WHERE order_no = '20240601001' UNION ALL SELECT * FROM trade_order WHERE status = 3 AND order_no != '20240601001';

两个分支各自用自己的最优路径，再去重合并。但这个改写本身也有操作系统开销，只有数据量大的时候才值得。还有一个更一劳永逸的办法：给status也建上合适的索引，让index_merge能自己玩得转。

4.6 NOT IN、NOT EXISTS与NULL判断：优化器最不省心的三类查询

NOT IN和NOT EXISTS在日常业务里用来查“不在某个集合里”的数据，但它们的优化空间往往很有限。主要原因在于：要证明一行“不在”某个集合，理论上得把整个集合都排查一遍，无法像等值匹配那样快速命中。MySQL优化器对NOT IN的实现方式通常是全表扫描加子查询过滤，数据一大就凉。

IS NULL判断同样需要小心。很多开发以为有索引就能快速定位NULL值，但InnoDB的二级索引默认不存NULL值（具体行为和列是否允许NULL有关），所以WHERE column IS NULL经常走不了索引。如果这类查询是核心路径，我的建议通常是：改造表结构，把NULL替换成默认值，比如空字符串、0或者一个特殊标记，然后查询就用普通的等值匹配。

4.7 优化器“主动”弃用索引：索引选择性太低的隐情

这是最反直觉的一种“失效”。索引明明存在，优化器看了它一眼，然后非常嫌弃地走全表扫描去了。你一看执行计划气得不行：这不是有索引吗？怎么不用？

原因在于优化器的成本计算。当一个索引的区分度（基数/行数）很低时——比如一个status字段只有“已创建、处理中、已完成、已失败”四个值，而表里1000万行数据、900万行都是“已完成”——你用status = '已完成'去查，走二级索引意味着要扫描几百万个索引条目、再回表几百万次，每次都是随机IO，成本高得离谱。而全表扫描是顺序读的主场，成本反而低得多。优化器一算账，果断选择全表扫描。

这个不是bug，这是优化器正常工作。遇到这类场景的正确解法是：一是换一个高选择性的查询维度（比如联合其他字段一起过滤）；二是重新设计索引，让查询落在局部范围里更高效；三是考虑OLAP场景用汇总表之类的方案，别让在线事务库硬扛这种查询。

5. 实战优化案例：三个经典的慢SQL改写与索引重构

5.1 案例一：大表JOIN排序深翻页，从临时文件排序到覆盖索引

回到我在3.2节抛出的案例：

SELECT order_no, amount FROM trade_order WHERE user_id = 12345 AND status = 2 ORDER BY create_time DESC LIMIT 20;

这条SQL的问题我已经点过：type = ref，rows = 128000，Extra = Using filesort。生产环境的表现是：单次执行300～800毫秒，QPS一高就飘到1秒以上，成了慢查询日志的常客。

我的改写思路分两步。第一步，分析排序字段create_time和过滤字段user_id、status能不能整合到一个联合索引里。答案是可以——建一个(user_id, status, create_time)的联合索引。这样查询先按user_id精确定位、再按status精确定位，最后在create_time上已经天然排好序，优化的执行计划变成：type = ref，rows = 50（预估命中行数大幅下降），Extra = Using index condition，而且**Using filesort消失了**，因为排序被索引的天然顺序替代了。

第二步，进一步看能不能连回表都省掉。原始查询需要的字段是order_no、amount，而索引(user_id, status, create_time)里没有这两个字段，所以过滤完之后还得回表拿数据。如果这个查询是高频热点，我倾向把索引设计为(user_id, status, create_time, order_no, amount)，变成名副其实的覆盖索引，回表也省了。当然，覆盖索引不是越多越好，每多一个索引写入就要多维护一份数据，这个决策要看“读多写少”还是“写多读少”的业务特性。就这个案例来说，订单查询是高频核心路径，多维护一两个冗余字段的写入成本是可接受的。

5.2 案例二：COUNT(*)统计慢到没朋友，根本原因是扫描行数爆炸

业务方经常要统计：今天新增了多少单、完成了多少单、某个用户有多少未读消息。然后开发直接写：

SELECT COUNT(*) FROM trade_order WHERE user_id = 10086 AND status = 0;

这个查询如果表很大、加上status = 0的行很多，执行计划大概率是type = ref、rows = 50万、Extra = NULL。是的，它走了索引，但50万行索引逐行统计，依然很慢。

这个问题的优化思路和普通查询不太一样。COUNT必须要统计真实行数，索引覆盖能减少回表，但无法减少“数一遍”的成本。我的做法一般有三个层次：

第一层：保证统计走一个尽量小的索引。比如(user_id, status)的联合索引，过滤之后只需要在二级索引上数行数，而不需要回表。这样COUNT虽然还是要数50万行，但每行成本已经很低。

第二层：如果数据量太大，实时COUNT已经无法接受，就把计数结果做成“汇总表”，用事务或者异步任务增量维护。比如每天一个小定时任务，把订单按状态汇总，业务端查汇总表而不是查明细表。这就是经典的“空间换时间”。

第三层：在真正海量数据场景，考虑把统计逻辑放到OLAP系统或者搜索引擎里承担，在线库只负责事务型读写。这个方案动架构，代价大，但也是最终解法。

5.3 案例三：去重查询的SQL改写，从临时表地狱里捞出来

还有一个高频踩坑场景：DISTINCT和GROUP BY去重。比如：

SELECT DISTINCT user_id FROM trade_order WHERE status = 2;

如果status = 2的行数很大，执行计划里经常能看到Extra = Using temporary和Using filesort同时出现。为啥？因为DISTINCT要去重，MySQL的实现方案常常是把数据丢到临时表里建哈希索引或者排序去重，数据量大时临时表还会从内存落到磁盘，性能直接崩。

这里我推荐两个优化方向：一个是在status和user_id上建联合索引。索引本身有序，MySQL走索引扫描时天然能拿到有序的user_id序列，去重的压力会大幅下降，甚至Using temporary会消失。第二个方向是如果status = 2的数据量极其庞大，且结果集不需要100%精确，可以考虑用“先取一页优化掉”的思路，把问题放到业务层分批处理。

6. 优化不能只看执行时间：性能验证与长期维护

6.1 建立基线：EXPLAIN分析加多轮压测，别被单次执行骗了

改完索引和SQL，怎么验证真的有效？只看一次执行时间很容易被缓存因素骗了。数据库的buffer pool会把经常访问的数据页缓存起来，第二次跑同样语句可能数据全在内存里，快得离奇。这不代表它在生产环境下同样快。

我推荐的做法是分三步验证：

第一步，看执行计划。key列有没有正常使用新索引，type是不是从ALL变成了ref或range，rows预估是不是大幅下降，Extra里的Using filesort、Using temporary有没有消失。执行计划能直观反映“优化是否生效”。

第二步，多次执行取中位数。同一个查询至少连续跑5次，去掉最高和最低，取中间3次的平均值作为基准。有条件的用sysbench或者mysqlslap做简单压测，观察不同并发下的响应时间曲线。

第三步，关注并发场景下的系统指标。CPU使用率、磁盘IO、InnoDB的行锁等待这些指标在高并发下才能暴露真实性能。我见过不少SQL单跑很快、一压就崩的案例，基本都是并发放大效应。

注意：优化上线后，务必观察至少一个业务周期（比如一个完整的交易高峰日）再下结论。索引优化可能影响写性能，万一你的业务是高频写场景，新索引带来的写入开销可能需要一段时间才能体现出来。

6.2 索引维护策略：冗余索引、无用索引与统计信息刷新

索引不是越多越好，这是一个非常容易被忽视的铁律。我来算一笔账：一张1000万行的表，每多一个二级索引，每次写操作（INSERT/UPDATE/DELETE）就要额外维护一个B+树结构。这个维护不是免费的，它是实实在在的写入放大。如果表本身就是写多读少的业务，你加的那两个“为了以防万一”的索引，可能每天要付出几百万次额外的B+树节点更新开销。

所以我建议每个季度做一次索引健康检查，重点看三类问题：

一是冗余索引。比如已经有(a, b)联合索引了，又单独建了一个(a)索引——前者完全覆盖后者，后者是纯浪费。MySQL可以通过sys.schema_redundant_indexes视图查出来。

二是无用索引。通过performance_schema.table_io_waits_summary_by_index_usage看每个索引的读写使用频率，那些长期只出现在INSERT、UPDATE维护成本里、却从未被SELECT命中的索引，就是可以考虑删除的对象。

三是统计信息过旧。MySQL InnoDB默认会自动更新统计信息，但频率不算高，在大表发生大量变更后，你可以手动执行ANALYZE TABLE来刷新。这个操作成本很低，经常能“治疗”一些莫名其妙的执行计划跳变。

6.3 SQL优化面试高频题背后，到底在考什么

最后扯两句和面试相关的内容，因为热搜词里出现了不少SQL面试题。你可能会发现，各大公司面试题翻来覆去问的其实就那几件事：“主键索引和唯一索引有什么区别”、“什么场景会导致索引失效”、“MySQL的存储引擎有什么差异”、“说一下一条SQL的执行过程”。这些问题表面在考知识点，实际上在考你“有没有系统性地理解过数据库是怎么工作的”。

• 主键索引和唯一索引的区别在于：主键索引是聚簇索引，既管索引又管数据存储，而且一张表只能有一个；唯一索引只是保证数据唯一性的二级索引，可以有多个。追问下去，还会涉及到NULL值处理、数据的物理组织方式这些细节。
• 索引失效的问答，考的就是你在4.2到4.7讲到的那些场景有没有真踩过坑。
• 存储引擎的差异，InnoDB和MyISAM的区别核心在于事务、外键、行级锁和崩溃恢复能力，InnoDB全面胜出，但MyISAM的读性能在某些纯读场景仍有存在价值。

说实话，面试题只是一个入口，真正拉开差距的不是“背出答案”，而是“讲清楚原理和场景”。能把“为什么查字典要先查首字母”讲明白，能把“回表一次为什么比顺序读慢一个数量级”说清楚，面试官基本就知道你是真干过还是临时背的。

做SQL优化这几年，我最大的体会是：这件事没有什么天才手段，就是一套“观察、定位、分析、设计、验证、复盘”的循环。你要做的不是记住某个索引的写法，而是理解数据是怎么被组织和读取的，然后让你的查询尽可能贴近数据的组织方式。

所以如果你正在准备优化自己负责的系统的SQL，我的建议是：别急着抄网上的优化口诀，先打开慢查询日志，把自己的业务里最慢的那十句SQL找出来，逐条过一遍执行计划，搞清楚它们慢在扫描行数太多、排序太贵还是回表太多。等你把这十句处理完，你对SQL性能的认知会上一个档次——因为这时候你看到的就不再是抽象的概念，而是你自己业务里的具体问题，以及你和数据库之间正在达成的某种默契。