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

---

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

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

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

---

## 一、先学会看：EXPLAIN

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

```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就加索引，跟不看地图就出门一样。

```sql
-- 统计信息过时会导致优化器选错索引
ANALYZE TABLE orders;
-- 执行完再跑一次EXPLAIN，看看执行计划有没有变
```

---

## 二、索引不只是建不建的问题

基础索引之前就讲过了，这里说三个进阶的。

### **覆盖索引：不回表就是快**

如果查询的字段全在索引里，MySQL直接从索引返回数据，不用去聚簇索引捞——省了一次IO。

```sql
-- 常查这个
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个字符建索引。

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

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

```sql
-- 测试不同前缀长度的区分度
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会丢行，窗口函数不会。

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

### **CTE（WITH子句）**

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

```sql
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类型，`->>` 返回文本。关键字段可以建函数索引：

```sql
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。

```sql
-- order_items的order_id没索引？加！
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);
```

**技巧二：小表驱动大表**

MySQL优化器一般会自动选择，但偶尔会选错。

```sql
-- 强制small_table做驱动表
SELECT * FROM small_table s
STRAIGHT_JOIN large_table l ON s.id = l.s_id;
```

怎么看谁是驱动表？EXPLAIN里，两行结果中第一行的就是驱动表。

**技巧三：先过滤再JOIN**

```sql
-- 不好：先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要把子查询结果全部算出来。

```sql
-- 慢：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选项控制：

```sql
-- 加列：不锁表
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。加列操作只改元数据，不碰数据文件，秒级完成：

```sql
-- 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。

### 模拟行号

```sql
-- 给每行打个序号
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)` 就行了。

### 计算行间差值

```sql
-- 日环比增长率
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标准，有个很大的坑——执行顺序不确定。优化器可能按它自己的顺序处理，变量的值就乱了。

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

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

---

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

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

### **生成列**

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

```sql
-- 经常需要按全名查询
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开始支持直接在表达式上建索引，不用绕一圈建生成列了：

```sql
-- 8.0+ 直接建函数索引
CREATE INDEX idx_lower_email ON users ((LOWER(email)));

-- 这个查询能走索引了
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
```

JSON字段的函数索引也是这个原理：

```sql
-- 在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行再扔掉前面的。

```sql
-- 问题写法
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放弃索引。

```sql
-- 可能全表扫描
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：

```sql
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进阶不是一个点的突破，是工具箱越来越丰富。同样的查询需求，新手写出来全表扫描，老手三行搞定还走覆盖索引。差别不在智商，在于你知道多少种写法。

---


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


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

MySQL高级SQL实战：10个覆盖索引优化到在线DDL的生产环境必备技巧

从EXPLAIN到生成列，从窗口函数到在线DDL，10个MySQL进阶技巧按"解决什么问题"串起来讲，配合面试话术和避坑清单，帮你在写SQL这件事上真正进阶

数据库

后端

MySQL进阶技巧：掌握10大实用SQL优化方法，包括EXPLAIN执行计划分析、覆盖索引与索引下推、窗口函数与CTE应用、JOIN优化策略、在线DDL操作技巧、生成列与函数索引等。帮助开发者解决复杂查询性能问题，提升数据库操作效率，适用于MySQL5.6+和8.0版本。

MySQL

JSON

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

MySQL高级SQL实战：10个覆盖索引优化到在线DDL的生产环境必备技巧-腾讯云开发者社区-腾讯云

MySQL高级SQL实战：10个覆盖索引优化到在线DDL的生产环境必备技巧

MySQL高级SQL实战：10个覆盖索引优化到在线DDL的生产环境必备技巧

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐