AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

MySQL SQL进阶最佳实践：从查询优化到表结构变更的10个核心技巧

2026-06-11 258
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 从EXPLAIN到生成列，从窗口函数到在线DDL，10个MySQL进阶技巧按"解决什么问题"串起来讲，配合面试话术和避坑清单，帮你在写SQL这件事上真正进阶

📌今日关键词：MySQL进阶、窗口函数、CTE、JOIN优化、在线DDL、生成列

大家好，我是数据库小学妹 👋

之前写过几篇关于单个SQL技巧的文章——聊了窗口函数、CTE，拆了JSON类型。这些单点技巧都懂了，但写SQL的时候还是不知道什么时候该用什么。

今天把我觉得最实用的10个技巧按"解决什么问题"串起来。已覆盖过的内容简要提一下重点，重点展开那些之前没怎么讲过的。

一、先学会看：EXPLAIN

写SQL的第一步不是写，是看完执行计划再动手。

EXPLAIN SELECT * FROM orders
WHERE user_id = 10086
  AND 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 '%abc'。

5.6之前的做法：引擎用 a = 10 在索引里找到所有记录，然后全部回表，再由Server层过滤 b LIKE '%abc'。

5.6之后有了索引下推（ICP）：b LIKE '%abc' 这个条件下推到存储引擎层，在索引扫描时就直接过滤。不需要的记录根本不回表。

看EXPLAIN的Extra字段，出现 Using index condition 说明在用索引下推。这不是问题，是好事。

前缀索引：长文本字段的折中方案

VARCHAR(500)的字段建全字段索引太臃肿了。可以只对前N个字符建索引。

-- content字段太长，只取前100个字符
CREATE INDEX idx_content_prefix ON articles (content(100));

怎么确定取多少字符？看区分度：

-- 测试不同前缀长度的区分度
SELECT
    COUNT(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_full
FROM articles;

哪个长度的区分度接近sel_full，就取那个长度。一般到0.9以上就够了。

缺点：前缀索引不能用于ORDER BY和GROUP BY，也不能做覆盖索引。

三、窗口函数和CTE：SQL进阶的两道坎

窗口函数

跨行计算的利器。之前的文章讲了原理，也给了三个万能模板，这里不重复了。

一句话总结：需要"保留每一行的同时做分组计算"——排名、环比、累计求和、移动平均——用窗口函数。GROUP BY会丢行，窗口函数不会。

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

CTE（WITH子句）

复杂查询拆成逻辑清晰的步骤。

WITH
  high_value AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders WHERE status = 'completed'
    GROUP BY user_id HAVING total > 10000
  ),
  active AS (
    SELECT DISTINCT user_id FROM user_logs
    WHERE last_active_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
  )
SELECT u.name, h.total
FROM users u
JOIN high_value h ON u.id = h.user_id
JOIN 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 s
STRAIGHT_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';

-- 好：先过滤再JOIN
SELECT * FROM (
    SELECT * FROM A WHERE create_time > '2024-01-01'
) filtered_A
JOIN B ON filtered_A.id = B.aid;

核心思路：尽早过滤，减少中间数据量。

技巧四：EXISTS比IN更高效

判断"是否存在"的场景，子查询结果集大的时候EXISTS通常比IN快——EXISTS找到第一个匹配就停，IN要把子查询结果全部算出来。

-- 慢：IN要先算出整个子查询结果集
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 'vip');

-- 快：EXISTS找到一个就停
SELECT * FROM orders o
WHERE 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_table
ADD COLUMN new_col VARCHAR(100) DEFAULT '',
ALGORITHM=INPLACE, LOCK=NONE;

-- 改列类型：可能必须锁表
ALTER TABLE huge_table
MODIFY 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_table
ADD 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, salary
FROM employees, (SELECT @rownum := 0) init
ORDER BY salary DESC;

8.0直接用 ROW_NUMBER() OVER (ORDER BY salary DESC) 就行了。

计算行间差值

-- 日环比增长率
SELECT
    sale_date,
    daily_amount,
    @prev AS prev_day,
    ROUND((daily_amount - @prev) / @prev * 100, 2) AS growth_rate,
    @prev := daily_amount
FROM daily_sales, (SELECT @prev := 0) init
ORDER BY sale_date;

8.0用LAG()窗口函数一行搞定。

用户变量的坑

用户变量不是SQL标准，有个很大的坑——执行顺序不确定。优化器可能按它自己的顺序处理，变量的值就乱了。

-- 这样写结果可能不对
SELECT @a := @a + 1 AS rn, name
FROM employees, (SELECT @a := 0) init
-- 不加ORDER BY的话，每次执行顺序可能不同
-- 加了ORDER BY，@a的赋值顺序也可能不是你期望的

8.0环境下，老老实实用窗口函数。用户变量留着维护老代码就够了。

八、生成列和函数索引——索引的新玩法

这个之前完全没讲过，是我觉得特别实用的一个特性。

生成列

生成列的值不是你手动写入的，而是由其他列的表达式自动计算。

-- 经常需要按全名查询
ALTER TABLE users
ADD 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 o
INNER 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 users
WHERE name = '张三' OR email = 'zhangsan@test.com' OR phone = '13800138000';

-- 改成UNION，每个分支走各自索引
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'zhangsan@test.com'
UNION
SELECT * 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进阶不是一个点的突破，是工具箱越来越丰富。同样的查询需求，新手写出来全表扫描，老手三行搞定还走覆盖索引。差别不在智商，在于你知道多少种写法。

我是数据库小学妹，咱们下篇见 👋

文章标签：
SQL
索引
关系型数据库
MySQL
JSON
数据库小学妹
目录
相关文章
数据库小学妹
|
1月前
|
Prometheus 监控 Cloud Native
MySQL 性能监控实战：从零搭建 Prometheus + Grafana 监控告警体系（附排查 SOP）
数据库小学妹带你从零学监控！本文详解MySQL五大核心指标维度（资源、连接、查询、InnoDB、主从），手把手配置PMM/Prometheus+Grafana监控栈，设置关键告警规则，并提供SQL快照脚本与三步排障SOP。新手友好，即装即用，让性能问题无所遁形！
数据库小学妹
274 0
数据库小学妹
|
3月前
|
SQL 关系型数据库 MySQL
数据量大查询慢？索引让你的SQL秒级响应！|转行学DB第9天
用生活化比喻（如字典目录）详解索引原理：它通过B+树结构加速查询，避免全表扫描；涵盖创建、查看、删除索引方法，联合索引的最左前缀原则，以及读写平衡等实战要点——让查询从“等几秒”变“秒出”！
数据库小学妹
350 3
数据量大查询慢？索引让你的SQL秒级响应！|转行学DB第9天
数据库小学妹
|
3月前
|
SQL 关系型数据库 MySQL
EXPLAIN 执行计划：一眼看穿你的SQL慢在哪
数据库小学妹带你轻松掌握SQL性能诊断！通过EXPLAIN查看执行计划，精准识别索引失效、全表扫描（ALL）、key为NULL等瓶颈。聚焦type、key、rows等6个关键字段，结合实战案例与避坑指南（如函数滥用、最左前缀破坏），让优化有的放矢。学完即用，告别盲目调优！
数据库小学妹
245 7
数据库小学妹
|
3月前
|
SQL 关系型数据库 MySQL
SQL优化十大技巧，查询速度提升10倍！
数据库小学妹带你轻松提速SQL！10个实战优化技巧：精简SELECT、善用LIMIT、巧用EXPLAIN、合理建索引、避开函数索引失效、JOIN优于子查询、IN替代OR、批量操作、EXISTS优化大子查询、定期OPTIMIZE。附避坑指南，新手也能秒上手！
数据库小学妹
782 4
数据库小学妹
|
3月前
|
SQL 关系型数据库 MySQL
WHERE、ORDER BY、LIMIT三大神器，让你的查询精准又高效！
本文介绍了SQL查询中的三大核心语句：WHERE（条件过滤）、ORDER BY（排序）和LIMIT（限制结果数）。通过电商订单查询、用户活跃度分析等实际案例，展示了如何组合使用这些语句实现精准查询。文章还分享了常见避坑技巧（如字符串引号使用、NULL值判断）和性能优化建议（如索引使用、分页查询优化）。
数据库小学妹
411 0
数据库小学妹
|
1月前
|
SQL 监控 关系型数据库
MySQL连接数管理最佳实践：从Too many connections应急到长期治理
本文详解MySQL“Too many connections”故障的应急排查与根治方案：30秒快速评估、杀Sleep连接止血、动态调参救急；深入分析慢查询、连接泄漏、短连接冲击、配置过小四大根因；附诊断脚本、监控告警规则及连接池最佳实践，助你从容应对生产危机。
数据库小学妹
448 0
数据库小学妹
|
1月前
|
SQL 中间件 关系型数据库
读写分离中间件怎么选？ProxySQL 落地踩坑与选型对比
ProxySQL是一款轻量高性能MySQL中间件，原生支持读写分离、自动故障切换与查询路由，相比MyCAT、ShardingSphere更专注、易用、低损耗，特别适合仅需主从读写分离的场景。
数据库小学妹
243 1
数据库小学妹
|
1月前
|
SQL 关系型数据库 MySQL
SQL代码审查指南：命名规范+10大反模式+四维检查清单，一篇全搞定
数据库小学妹带你攻克SQL规范难题！从命名、格式到10大反模式（如SELECT*、隐式转换、ORDER BY RAND等），结合真实踩坑案例，详解可读、可维护、高性能的SQL写法，并提供SQL Review四维审查清单与团队落地方法，助你写出工业级质量SQL。
数据库小学妹
227 2
数据库小学妹
|
2月前
|
canal 缓存 NoSQL
数据库扛不住高并发？Redis缓存+双写一致性：给你的系统装上“涡轮增压”
数据库小学妹带你破解Redis缓存一致性难题！面对高并发，如何确保Redis与数据库数据同步？详解“先更库后删缓”“延时双删”“Binlog异步同步”等4大方案，直击雪崩、击穿、穿透三座大山，助你构建又快又稳的数据库架构.
数据库小学妹
284 3
数据库小学妹
|
3月前
|
SQL 关系型数据库 MySQL
子查询：让SQL像俄罗斯套娃一样嵌套！｜转行学DB第8天
数据库小学妹带你轻松入门子查询！用“SELECT里套SELECT”，像俄罗斯套娃一样，一步解决“先算平均分、再查高分学生”等两步难题。支持WHERE（条件筛选）、FROM（临时表）、SELECT（标量列）三种用法，简洁直观，新手友好～
数据库小学妹
287 2

数据库

热门文章

最新文章

  • 1
    PostgreSQL 触发器用法详解 - 1
  • 2
    阿里云redis大key搜索工具
  • 3
    PostgreSQL 十亿级模糊查询最佳实践
  • 4
    八年磨一剑，阿里云ApsaraDB for HBase2.0正式上线
  • 5
    【直播预告】阿里高级开发工程师烛昭：Redis&MongoDB的同步、迁移以及混合云场景构建
  • 6
    是什么让TA蹲5个小时也要听完？--深圳云栖大会数据库技术专场8大爆点
  • 7
    PostgreSQL json 索引实践 - 检索(存在、包含、等值、范围等)加速
  • 8
    阿里云数据库自研产品亮相国际顶级会议ICDE 推动云原生数据库成为行业标准
  • 9
    分析型数据库+数据传输，构建企业级实时数仓
  • 10
    MySQL · 8.0新特性· Invisible Index
  • 1
    外卖配送平台如何快速完成从0到1搭建？
    24
  • 2
    湖库一体：2026年数据库架构的“终极答案”还是新瓶装旧酒？
    44
  • 3
    阿里云代理商零度云lingducloud教新手快速部署数据库！
    55
  • 4
    从DBA到数据架构师：数据库从业者的能力跃迁路径
    23
  • 5
    危机爆发前48小时，他们选择先把三种未来预演一遍
    31
  • 6
    从0到1完成跑腿系统开发需要注意哪些问题？
    28
  • 7
    危机爆发前 48 小时，他们选择先把三种未来预演一遍
    33
  • 8
    MySQL安全最佳实践：7道防线从网络隔离到自动化巡检的完整方案
    38
  • 9
    SQL Server数据库迁移方案全对比：5种主流方式怎么选？（附避坑清单）
    35
  • 10
    SQL调优的“二八法则”：用20%的投入解决80%的慢查询
    34

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！