MySQL SQL进阶实战：10个查询优化与表结构变更技巧
?今日关键词：MySQL进阶、窗口函数、CTE、JOIN优化、在线DDL、生成列
关于SQL技巧，之前零零散散聊过窗口函数、CTE，也拆过JSON类型。单点技巧都懂了，但到了写SQL的时候，还是经常不知道什么场景该用什么工具。今天把这10个最实用的技巧按“解决什么问题”串起来梳理一下。之前详细聊过的内容，这里只提重点，重点展开那些没怎么深入讲过的。
一、先学会看：EXPLAIN
写SQL的第一步不是写，是看完执行计划再动手。
EXPLAIN SELECT * FROM ordersWHERE user_id = 10086AND create_time BETWEEN '2024-01-01' AND '2024-01-31'ORDER BY amount DESC;
输出里盯住三个字段就够：
- type：ALL是全表扫描，最差。至少要到ref或range级别
- Extra：出现
Using filesort或Using temporary得优化 - rows：扫描行数越大越有问题
之前文章专门拆过EXPLAIN的6个关键字段，也讲了生产环境怎么看执行计划，这里不展开了。记住一句话：不看EXPLAIN就加索引，跟不看地图就出门一样。
-- 统计信息过时会导致优化器选错索引ANALYZE TABLE orders;-- 执行完再跑一次EXPLAIN，看看执行计划有没有变
二、索引不只是建不建的问题
基础索引之前就讲过了，这里说三个进阶的。
覆盖索引：不回表就是快
如果查询的字段全在索引里，MySQL直接从索引返回数据，不用去聚簇索引捞——省了一次IO。
-- 常查这个SELECT name, email FROM users WHERE age > 20;-- 建一个覆盖索引CREATE INDEX idx_age_name_email ON users(age, name, email);-- age用于过滤，name和email在索引里直接返回-- Extra会显示Using index，说明走了覆盖索引
此前从B+树的角度解释过为什么覆盖索引能避免回表，不清楚可以翻翻之前的文章。
索引下推：5.6之后的隐藏优化
这个很少有人专门讲，但对复合索引的性能影响很大。
假设复合索引是 (a, b)，查询是 WHERE a = 10 AND b LIKE 'c'。
5.6之前的做法：引擎用 a = 10 在索引里找到所有记录，然后全部回表，再由Server层过滤 b LIKE 'c'。
5.6之后有了索引下推（ICP）：b LIKE 'c' 这个条件下推到存储引擎层，在索引扫描时就直接过滤。不需要的记录根本不回表。
看EXPLAIN的Extra字段，出现 Using index condition 说明在用索引下推。这不是问题，是好事。
前缀索引：长文本字段的折中方案
VARCHAR(500)的字段建全字段索引太臃肿了。可以只对前N个字符建索引。
-- content字段太长，只取前100个字符CREATE INDEX idx_content_prefix ON articles (content(100));
怎么确定取多少字符？看区分度：
-- 测试不同前缀长度的区分度SELECTCOUNT(DISTINCT LEFT(content, 50)) / COUNT(*) AS sel_50,COUNT(DISTINCT LEFT(content, 100)) / COUNT(*) AS sel_100,COUNT(DISTINCT LEFT(content, 150)) / COUNT(*) AS sel_150,COUNT(DISTINCT content) / COUNT(*) AS sel_fullFROM articles;
哪个长度的区分度接近sel_full，就取那个长度。一般到0.9以上就够了。
缺点：前缀索引不能用于ORDER BY和GROUP BY，也不能做覆盖索引。
三、窗口函数和CTE：SQL进阶的两道坎
窗口函数
跨行计算的利器。之前的文章讲了原理，也给了三个万能模板，这里不重复了。
一句话总结：需要“保留每一行的同时做分组计算”——排名、环比、累计求和、移动平均——用窗口函数。GROUP BY会丢行，窗口函数不会。
SELECTname, department, salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rankFROM employees;
CTE（WITH子句）
复杂查询拆成逻辑清晰的步骤。
WITHhigh_value AS (SELECT user_id, SUM(amount) AS totalFROM orders WHERE status = 'completed'GROUP BY user_id HA VING total > 10000),active AS (SELECT DISTINCT user_id FROM user_logsWHERE last_active_date > DATE_SUB(NOW(), INTERVAL 30 DAY))SELECT u.name, h.totalFROM users uJOIN high_value h ON u.id = h.user_idJOIN active a ON u.id = a.user_id;
比嵌套子查询好读十倍。之前的文章专门讲了递归CTE查组织架构树的用法。
四、JSON和分区表
JSON类型
动态字段不想建子表就用JSON。-> 返回JSON类型，->> 返回文本。关键字段可以建函数索引：
CREATE INDEX idx_color ON products ((attributes->>'$.color'));
分区表
之前讲过基础用法，以及局部索引和全局索引的区别。大表按时间分区，历史数据清理直接 ALTER TABLE sales DROP PARTITION p2023q1，比DELETE快得多。
五、JOIN优化——真正拉开差距的地方
三种JOIN算法
MySQL处理JOIN有三种方式，理解它们才能明白为什么有些JOIN快有些慢：
- 嵌套循环（Nested Loop Join）——最常见。外表取一行，去内表用索引找匹配。内表有索引就很快。
- 块嵌套循环（Block Nested Loop Join）——内表没索引时的退化方案。把外表的一批数据加载到join buffer里，然后跟内表逐行比。内表越大越慢。
- Hash Join（8.0.18+）——针对没有索引的等值JOIN做了优化。把小表构建成哈希表放内存里，大表逐行去查。比BNL快不少。
怎么知道用了哪种？EXPLAIN看Extra：
- 什么都没说 → 大概率是NLJ
Using join buffer (Block Nested Loop)→ BNL，该加索引了Using join buffer (hash join)→ Hash Join
四个JOIN实战技巧
技巧一：确保JOIN字段有索引
这条最基础，但线上还是经常看到没索引的JOIN。
-- order_items的order_id没索引？加！ALTER TABLE order_items ADD INDEX idx_order_id (order_id);
技巧二：小表驱动大表
MySQL优化器一般会自动选择，但偶尔会选错。
-- 强制small_table做驱动表SELECT * FROM small_table sSTRAIGHT_JOIN large_table l ON s.id = l.s_id;
怎么看谁是驱动表？EXPLAIN里，两行结果中第一行的就是驱动表。
技巧三：先过滤再JOIN
-- 不好：先JOIN两个大表，再过滤SELECT * FROM A JOIN B ON A.id = B.aid WHERE A.create_time > '2024-01-01';-- 好：先过滤再JOINSELECT * FROM (SELECT * FROM A WHERE create_time > '2024-01-01') filtered_AJOIN B ON filtered_A.id = B.aid;
核心思路：尽早过滤，减少中间数据量。
技巧四：EXISTS比IN更高效
判断“是否存在”的场景，子查询结果集大的时候EXISTS通常比IN快——EXISTS找到第一个匹配就停，IN要把子查询结果全部算出来。
-- 慢：IN要先算出整个子查询结果集SELECT * FROM ordersWHERE user_id IN (SELECT id FROM users WHERE status = 'vip');-- 快：EXISTS找到一个就停SELECT * FROM orders oWHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'vip');
不过8.0对IN子查询做了半连接优化，很多时候会自动转成EXISTS。保险起见写成JOIN更可控。
六、在线DDL——大表改结构不锁业务
7×24运行的系统，给大表加字段、改索引不能停服务。
MySQL自带的Online DDL
5.6开始支持，通过ALGORITHM和LOCK选项控制：
-- 加列：不锁表ALTER TABLE huge_tableADD COLUMN new_col VARCHAR(100) DEFAULT '',ALGORITHM=INPLACE, LOCK=NONE;-- 改列类型：可能必须锁表ALTER TABLE huge_tableMODIFY COLUMN old_col BIGINT,ALGORITHM=COPY, LOCK=SHARED;
两种算法的区别：
| 算法 | 行为 | 并发DML | 速度 |
|---|---|---|---|
| INPLACE | 引擎内部原地重建 | 允许 | 快 |
| COPY | 建新表复制数据 | 不允许（锁表） | 慢 |
执行前先用 SHOW CREATE TABLE 看看，或者在测试环境跑一下确认用的是哪种算法。
8.0的Instant DDL
8.0加了一个杀手级特性——Instant DDL。加列操作只改元数据，不碰数据文件，秒级完成：
-- 8.0+，加列用INSTANT算法ALTER TABLE huge_tableADD COLUMN new_col VARCHAR(100) DEFAULT '',ALGORITHM=INSTANT;
在一张3亿行的表上加列，Online DDL跑了40分钟，换成Instant不到1秒。但Instant有限制——只能加列、改列默认值这些轻量操作，改列类型还是得走INPLACE或COPY。
第三方工具
Online DDL对大表还是会占不少IO。更稳妥的选择是pt-online-schema-change或gh-ost：
- pt-osc：通过触发器实现，原表的增删改同步到临时表
- gh-ost：通过binlog实现，不依赖触发器，对主从复制更友好
七、用户自定义变量——8.0之前的黑魔法
这个技巧比较老派，8.0有了窗口函数之后用得少了。但了解一下没坏处，面试偶尔会问，而且有些老项目还是5.7。
模拟行号
-- 给每行打个序号SELECT @rownum := @rownum+1 AS row_num, name, salaryFROM employees, (SELECT @rownum := 0) initORDER BY salary DESC;
8.0直接用 ROW_NUMBER() OVER (ORDER BY salary DESC) 就行了。
计算行间差值
-- 日环比增长率SELECTsale_date,daily_amount,@prev AS prev_day,ROUND((daily_amount - @prev) / @prev * 100, 2) AS growth_rate,@prev := daily_amountFROM daily_sales, (SELECT @prev := 0) initORDER BY sale_date;
8.0用LAG()窗口函数一行搞定。
用户变量的坑
用户变量不是SQL标准，有个很大的坑——执行顺序不确定。优化器可能按它自己的顺序处理，变量的值就乱了。
-- 这样写结果可能不对SELECT @a := @a+1 AS rn, nameFROM employees, (SELECT @a := 0) init-- 不加ORDER BY的话，每次执行顺序可能不同-- 加了ORDER BY，@a的赋值顺序也可能不是你期望的
8.0环境下，老老实实用窗口函数。用户变量留着维护老代码就够了。
八、生成列和函数索引——索引的新玩法
这个之前完全没讲过，是很实用的一个特性。
生成列
生成列的值不是你手动写入的，而是由其他列的表达式自动计算。
-- 经常需要按全名查询ALTER TABLE usersADD COLUMN full_name VARCHAR(255)GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,ADD INDEX idx_full_name (full_name);-- 现在这个查询能走索引了SELECT * FROM users WHERE full_name = 'John Doe';
两种类型：
| 类型 | 存储 | 读取 | 适用场景 |
|---|---|---|---|
| VIRTUAL | 不占空间 | 每次读取都计算 | 查询少、字段多 |
| STORED | 占空间 | 直接读 | 查询频繁 |
大多数情况用STORED，因为需要在上面建索引。VIRTUAL列不能建普通索引（8.0+可以建函数索引）。
函数索引
8.0开始支持直接在表达式上建索引，不用绕一圈建生成列了：
-- 8.0+ 直接建函数索引CREATE INDEX idx_lower_email ON users ((LOWER(email)));-- 这个查询能走索引了SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
JSON字段的函数索引也是这个原理：
-- 在JSON字段的某个路径上建索引CREATE INDEX idx_json_color ON products ((attributes->>'$.color'));-- 能走索引的查询SELECT * FROM products WHERE attributes->>'$.color' = 'red';
本质上生成列和函数索引解决的是同一个问题：怎么在“计算出来的值”上建索引。8.0之前只能用生成列绕路，8.0之后函数索引更简洁。
九、三个容易忽略的优化技巧
技巧一：深分页优化
跳过100万行取20行，MySQL要扫描100万+20行再扔掉前面的。
-- 问题写法SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 20;-- 游标分页（推荐）SELECT * FROM orders WHERE id < 1000020 ORDER BY id DESC LIMIT 20;-- 延迟关联（产品非要跳页时用）SELECT o.* FROM orders oINNER JOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 1000000, 20) t ON o.id = t.id;
游标分页性能好，但前端得改成“加载更多”模式。微信朋友圈和Twitter就是这么做的。
技巧二：OR改写UNION
OR条件可能让MySQL放弃索引。
-- 可能全表扫描SELECT * FROM usersWHERE name = '张三' OR email = 'zhangsan@test.com' OR phone = '13800138000';-- 改成UNION，每个分支走各自索引SELECT * FROM users WHERE name = '张三'UNIONSELECT * FROM users WHERE email = 'zhangsan@test.com'UNIONSELECT * FROM users WHERE phone = '13800138000';
前提是三个字段都有独立索引。
技巧三：优化器选错索引怎么办
有时候MySQL会选错索引。先ANALYZE TABLE更新统计信息，如果还不行，可以用FORCE INDEX：
SELECT * FROM orders FORCE INDEX (idx_user_time)WHERE user_id = 10086 AND create_time > '2024-01-01';
FORCE INDEX是最后手段。用了之后表结构或数据分布变了，这个hint可能反而有害。用之前先确认执行计划确实选错了。
避坑清单
| 序号 | 坑点 | 后果 | 正确做法 |
|---|---|---|---|
| 1 | 不看EXPLAIN就写SQL | 全表扫描自己都不知道 | 先EXPLAIN再动手 |
| 2 | 索引区分度低还单独建 | 加了跟没加差不多，白白拖慢写入 | 看基数Cardinality，低于10%不单独建 |
| 3 | 用户变量在8.0里当窗口函数用 | 执行顺序不确定，结果可能错 | 8.0用窗口函数，用户变量只维护老代码 |
| 4 | 大表直接ALTER TABLE改结构 | 锁表或IO飙升 | 8.0用Instant DDL，其他用pt-osc或gh-ost |
| 5 | SELECT * 写习惯了 | 无法覆盖索引，多传数据 | 只查需要的字段 |
| 6 | 深分页用大offset硬扛 | 越到后面越慢 | 游标分页或延迟关联 |
| 7 | IN子查询不改写 | 老版本MySQL每行都要跑一次子查询 | 改成JOIN或EXISTS |
| 8 | 一个表建十几个索引 | 写入性能严重下降 | 定期审查，删掉不用的冗余索引 |
| 9 | 生成列选VIRTUAL但要建索引 | VIRTUAL列不能建普通索引（8.0之前） | 需要建索引的生成列用STORED |
| 10 | 用了FORCE INDEX不跟进维护 | 表结构或数据分布变了，hint反而有害 | 定期检查执行计划，确认hint还有效 |
总结
10个技巧按“解决什么问题”串一下：
- 看懂查询 → EXPLAIN（先看再动手）
- 索引进阶 → 覆盖索引（不回表）、索引下推（少回表）、前缀索引（长字段）
- 复杂查询 → 窗口函数（跨行计算）、CTE（拆解逻辑）、JSON（动态字段）
- JOIN → 确保有索引、小表驱动大表、先过滤再JOIN
- 表结构变更 → Online DDL、Instant DDL、pt-osc/gh-ost
- 冷门但好用 → 用户自定义变量（老代码维护）、生成列和函数索引（计算字段加索引）
SQL进阶不是一个点的突破，是工具箱越来越丰富。同样的查询需求，新手写出来全表扫描，老手三行搞定还走覆盖索引。差别不在智商，在于你知道多少种写法。