mysql进阶二

这个问题问得非常好，而且这恰恰是 高级 Java 工程师和 DBA 的分水岭。

因为：

80% 的 SQL 慢，不是因为没建索引，而是因为“建了索引，写法仍然让优化器没法高效利用”。

下面给你几个真实生产中非常经典的案例。

案例一：LIMIT 深分页

场景

用户表：

5000 万数据

SQL：

SELECT *
FROM user
ORDER BY id
LIMIT 10000000,20;

耗时：

15~30 秒

为什么慢？

虽然：

PRIMARY KEY(id)

存在。

但是：

MySQL 仍然要扫描前 10000020 条记录，

丢掉前 10000000 条，

返回最后 20 条。

EXPLAIN

type: index
rows: 10000020
Extra:

优化

Seek 分页：

SELECT *
FROM user
WHERE id > 10000000
ORDER BY id
LIMIT 20;

效果

30 秒

↓

10ms

生产：

聊天记录
无限滚动
订单列表

都这样做。

案例二：已经有索引，但用了函数

订单：

CREATE INDEX idx_create_time
ON orders(create_time);

慢 SQL：

SELECT *
FROM orders
WHERE DATE(create_time)='2026-06-14';

数据：

1 亿行

耗时：

20 秒

EXPLAIN：

type: ALL
rows: 100000000

全表扫描。

原因

DATE(create_time)

导致：

索引失效。

优化：

SELECT *
FROM orders
WHERE create_time >= '2026-06-14'
AND create_time < '2026-06-15';

效果：

20 秒

↓

50ms

案例三：LIKE 前缀 %

慢 SQL：

SELECT *
FROM user
WHERE phone LIKE '%1234';

数据：

3000 万用户

即使：

INDEX(phone)

存在。

EXPLAIN：

type: ALL

原因：

B+树无法定位：

%1234

优化：

增加冗余字段：

phone_reverse

保存：

4321xxxx

建立索引：

INDEX(phone_reverse)

查询：

WHERE phone_reverse
LIKE '4321%'

效果：

12 秒

↓

20ms

生产：

客服系统。

案例四：SELECT *

用户表：

1000 万数据

索引：

INDEX(status,create_time)

慢 SQL：

SELECT *
FROM user
WHERE status=1
ORDER BY create_time DESC
LIMIT 100;

EXPLAIN：

Using where

大量回表。

优化：

先查 ID：

SELECT id
FROM user
WHERE status=1
ORDER BY create_time DESC
LIMIT 100;

再：

SELECT *
FROM user
WHERE id IN (...);

效果：

2 秒

↓

40ms

生产：

后台列表。

案例五：OR 导致索引失效

慢 SQL：

SELECT *
FROM user
WHERE phone='138'
OR email='abc@qq.com';

索引：

INDEX(phone)

INDEX(email)

数据：

5000 万

EXPLAIN：

ALL

优化：

拆成：

SELECT ...
WHERE phone='138'

UNION ALL

SELECT ...
WHERE email='abc@qq.com';

效果：

6 秒

↓

15ms

案例六：IN 超长列表

慢 SQL：

SELECT *
FROM orders
WHERE id IN (
100万个ID
);

优化：

建立临时表：

CREATE TEMPORARY TABLE tmp_ids(
id BIGINT PRIMARY KEY
);

批量插入。

Join：

SELECT o.*
FROM orders o
JOIN tmp_ids t
ON o.id=t.id;

效果：

几十秒

↓

几百毫秒

案例七：COUNT(*)

慢 SQL：

SELECT COUNT(*)
FROM orders;

数据：

30 亿

耗时：

几十秒

优化：

统计表：

order_statistics

维护：

总订单数

查询：

SELECT total_count
FROM order_statistics;

效果：

30 秒

↓

1ms

生产：

运营后台。

案例八：Join 顺序错误

用户：

1000万

国家：

200条

错误：

SELECT *
FROM user u
JOIN country c
ON u.country_id=c.id
WHERE c.name='China';

优化：

SELECT *
FROM country c
JOIN user u
ON c.id=u.country_id
WHERE c.name='China';

效果：

3 秒

↓

50ms

因为：

小表驱动大表。

案例九：Join 无索引

订单：

2亿

用户：

5000万

慢 SQL：

SELECT *
FROM orders o
JOIN user u
ON o.user_id=u.id;

优化：

CREATE INDEX idx_user_id
ON orders(user_id);

效果：

数分钟

↓

几百毫秒

案例十：分组排序

慢 SQL：

SELECT user_id,
COUNT(*)
FROM orders
GROUP BY user_id
ORDER BY COUNT(*) DESC
LIMIT 100;

数据：

10 亿

优化：

每日预聚合：

daily_user_order_count

查询：

SELECT ...
FROM summary
ORDER BY count DESC;

效果：

几十秒

↓

几十毫秒

案例十一：删除大表数据

错误：

DELETE
FROM orders
WHERE create_time<'2020';

数据：

20 亿

结果：

锁表
redo 爆炸
复制延迟

优化：

DELETE
FROM orders
WHERE create_time<'2020'
LIMIT 5000;

循环。

效果：

稳定运行。

案例十二：分页 + 排序

慢 SQL：

SELECT *
FROM orders
WHERE status=1
ORDER BY create_time DESC
LIMIT 20;

索引：

INDEX(status)

INDEX(create_time)

仍然慢：

Using filesort

优化：

联合索引：

INDEX(status,create_time)

效果：

3 秒

↓

10ms

最经典的一道面试题

数据：

5000万订单

SQL：

SELECT *
FROM orders
WHERE user_id=1
ORDER BY create_time DESC
LIMIT 20;

已有：

INDEX(user_id)

INDEX(create_time)

仍然：

2 秒

优化：

INDEX(user_id,create_time)

效果：

2 秒

↓

5ms

原因：

原来：

user_id过滤

↓

回表

↓

filesort

现在：

user_id定位

↓

create_time天然有序

↓

直接取20条

这些案例真正体现的规律是：

性能提升最大的，往往不是：

没加索引

而是：

索引加错了

或者：

SQL 写法让索引失效了

如果让我选 生产中收益最大的 TOP10 优化，我会选：

排名	优化方式	性能提升
1	深分页改 Seek 分页	1000~10000倍
2	函数改范围查询	100~1000倍
3	联合索引代替单列索引	100~500倍
4	覆盖索引减少回表	10~100倍
5	预聚合代替实时 GROUP BY	1000倍+
6	LIKE `%xx` 改前缀匹配	100~1000倍
7	OR 改 UNION ALL	10~100倍
8	SELECT * 改延迟关联	10~50倍
9	批量删除 LIMIT 分批	避免雪崩
10	临时表 JOIN 替代超长 IN	10~100倍

这些基本上就是互联网公司里 真正能把 SQL 从几十秒优化到几十毫秒 的经典案例。

案例六：IN 超长列表建立临时表：请给出具体例子

这个优化在生产中非常经典，尤其是 Java 服务拿到一大批 ID 后去数据库查详情 的场景。

很多人写成：

SELECT *
FROM orders
WHERE id IN (...100万个ID...);

结果直接把 MySQL 打崩。

场景

订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(10,2),
    status TINYINT,
    create_time DATETIME
);

数据量：

orders：5000万条

Java 代码中：

List<Long> orderIds = xxx();

结果：

100万个ID

例如：

很多人会这样拼 SQL：

SELECT *
FROM orders
WHERE id IN (
1001,
1005,
1020,
1022,
1033,
......
);

为什么慢？

MySQL 需要：

解析超长SQL
↓

优化器构造执行计划

↓

对100万个值进行处理

↓

网络传输巨大

SQL 文本甚至可能达到：

几十MB

生产中经常出现：

max_allowed_packet exceeded

或者：

CPU飙高

优化方案：临时表 JOIN

第一步：创建临时表

CREATE TEMPORARY TABLE tmp_order_ids (
    id BIGINT PRIMARY KEY
);

特点：

只在当前连接存在。

连接关闭自动删除。

第二步：批量插入ID

假设有：

插入：

INSERT INTO tmp_order_ids(id)
VALUES
(1001),
(1005),
(1020),
(1022),
(1033);

生产中：

batchInsert();

一次：

5000条

或者：

10000条

第三步：Join 查询

SELECT o.*
FROM orders o
JOIN tmp_order_ids t
ON o.id = t.id;

EXPLAIN：

table: t
type: index

↓

table: o
type: eq_ref
key: PRIMARY

执行流程：

tmp_order_ids

↓

扫描100万个ID

↓

走orders主键查询

↓

返回结果

为什么快？

因为：

IN(...)

变成：

标准JOIN

MySQL 对 Join 优化非常成熟。

具体例子

假设：

orders：

id	amount
1001	10
1005	20
1020	30
1022	40
1033	50

tmp_order_ids：

id
1001
1020
1033

执行：

SELECT o.*
FROM orders o
JOIN tmp_order_ids t
ON o.id=t.id;

结果：

id	amount
1001	10
1020	30
1033	50

Java 中怎么做？

MyBatis：

jdbcTemplate.batchUpdate(
    "INSERT INTO tmp_order_ids(id) VALUES(?)",
    ids,
    5000,
    (ps,id)->{
        ps.setLong(1,id);
    });

然后：

jdbcTemplate.query(
"""
SELECT o.*
FROM orders o
JOIN tmp_order_ids t
ON o.id=t.id
""");

如果不能建临时表呢？

还有一种办法：

建永久表：

CREATE TABLE query_ids (
    request_id VARCHAR(64),
    id BIGINT,
    PRIMARY KEY(request_id,id)
);

插入：

request_id = UUID

例如：

abc123

插：

request_id	id
abc123	1001
abc123	1005
abc123	1020

查询：

SELECT o.*
FROM orders o
JOIN query_ids q
ON o.id=q.id
WHERE q.request_id='abc123';

查完：

DELETE
FROM query_ids
WHERE request_id='abc123';

生产：

很多大数据平台都是这么做的。

和超长 IN 对比

100万ID：

IN：

SQL长度：

20MB+

优化时间：

数秒

CPU：

极高

临时表：

SQL长度：

几十字节

优化时间：

几毫秒

CPU：

很低

什么时候该用？

经验值：

IN数量	建议
<100	直接IN
100~1000	问题不大
1000~10000	考虑批量拆分
>10000	建议临时表
>100000	强烈建议临时表

但是……

如果：

id 是主键
IN 只有几千个

例如：

WHERE id IN(...)

MySQL 会做：

range scan

实际上也很快。

真正需要临时表的场景是：

几十万

甚至

上百万ID

例如：

ES搜索返回100万订单ID

↓

去MySQL查详情

↓

导出Excel

这时候：

临时表 JOIN

往往能把：

几十秒

↓

几百毫秒

这是很多人不知道，但 DBA 经常使用的一个技巧。

不过，这里还有一个 更高级的问题：

tmp_order_ids 有 100 万行，orders 有 5000 万行，为什么 JOIN 不会变成 100万 × 5000万 的笛卡尔积？

如果你理解了 Join Buffer、Nested Loop Join、BKA、eq_ref 是怎么工作的，那么你对 MySQL Join 的理解就已经超过大部分 5~8 年的 Java 工程师了。

SELECT sales_date, amount, SUM(amount) OVER (ORDER BY sales_date) AS cumulative_total FROM sales ORDER BY sales_date;

这个 SQL 是 窗口函数（Window Function） 中最经典的应用之一：

SELECT 
    sales_date,
    amount,
    SUM(amount) OVER (ORDER BY sales_date) AS cumulative_total
FROM sales
ORDER BY sales_date;

它的作用是：

计算累计求和（Running Total / Cumulative Sum）

一、举个例子

假设销售表：

CREATE TABLE sales (
    sales_date DATE,
    amount INT
);

数据：

sales_date	amount
2026-06-01	100
2026-06-02	200
2026-06-03	50
2026-06-04	300

执行：

SELECT 
    sales_date,
    amount,
    SUM(amount) OVER (ORDER BY sales_date) AS cumulative_total
FROM sales
ORDER BY sales_date;

结果：

sales_date	amount	cumulative_total
2026-06-01	100	100
2026-06-02	200	300
2026-06-03	50	350
2026-06-04	300	650

计算过程：

6月1日：

100

──────────

6月2日：

100+200=300

──────────

6月3日：

100+200+50=350

──────────

6月4日：

100+200+50+300=650

二、OVER (ORDER BY ...) 到底是什么意思？

SUM(amount)
OVER (
    ORDER BY sales_date
)

意思是：

按照 sales_date 排序，

对于当前行，

计算从第一行到当前行的 SUM(amount)

实际上等价于：

SUM(amount)
OVER (
    ORDER BY sales_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
)

三、以前（MySQL 8 之前）怎么写？

没有窗口函数时：

SELECT
    s1.sales_date,
    s1.amount,
    (
        SELECT SUM(s2.amount)
        FROM sales s2
        WHERE s2.sales_date <= s1.sales_date
    ) cumulative_total
FROM sales s1
ORDER BY s1.sales_date;

假设：

100万条销售数据

那么：

每一行

↓

执行一次子查询

↓

100万次 SUM

复杂度：

O(N²)

非常慢。

窗口函数：

一次排序

↓

线性扫描

复杂度：

O(N log N)

性能提升巨大。

四、生产中的应用

1. 累计充值

充值记录：

日期	金额
6/1	100
6/2	50
6/3	200

SQL：

SELECT
    recharge_date,
    amount,
    SUM(amount)
    OVER (ORDER BY recharge_date)
    cumulative_recharge
FROM recharge;

结果：

日期	累计充值
6/1	100
6/2	150
6/3	350

2. 股票收益

日期	收益
1号	100
2号	-50
3号	200

累计收益：

SUM(profit)
OVER (ORDER BY trade_date)

结果：

日期	累计收益
1号	100
2号	50
3号	250

3. 用户成长值

用户成长记录：

时间	成长值
1月	20
2月	30
3月	50

累计：

SUM(exp)
OVER(ORDER BY month)

4. 游戏流水

每天流水：

日期	流水
1号	10万
2号	12万
3号	9万

累计流水：

SUM(revenue)
OVER(...)

运营月报经常这样做。

五、按用户分别累计

假设：

user_id	date	amount
1	1号	100
1	2号	50
2	1号	200
2	2号	30

SQL：

SELECT
    user_id,
    sales_date,
    amount,
    SUM(amount)
    OVER (
        PARTITION BY user_id
        ORDER BY sales_date
    ) cumulative_total
FROM sales;

结果：

user_id	日期	金额	累计
1	1号	100	100
1	2号	50	150
2	1号	200	200
2	2号	30	230

这里：

PARTITION BY user_id

表示：

每个用户单独计算累计值。

六、最近 7 天累计

如果要做滑动窗口：

SELECT
    sales_date,
    amount,
    SUM(amount)
    OVER (
        ORDER BY sales_date
        ROWS BETWEEN 6 PRECEDING
        AND CURRENT ROW
    ) seven_day_total
FROM sales;

假设：

日期	金额
1号	100
2号	200
...	...
7号	300

7号的结果：

1号+2号+...+7号

8号：

2号+3号+...+8号

生产中：

7日流水
7日DAU
7日GMV

非常常见。

七、执行顺序

对于：

SELECT
    sales_date,
    amount,
    SUM(amount) OVER (ORDER BY sales_date)
FROM sales
ORDER BY sales_date;

执行逻辑：

FROM sales

↓

排序（窗口函数需要）

↓

计算窗口函数

↓

SELECT 输出

↓

最终 ORDER BY

八、为什么比 GROUP BY 强？

GROUP BY：

SELECT
    sales_date,
    SUM(amount)
FROM sales
GROUP BY sales_date;

结果：

日期	sum
1号	100
2号	200

只能得到：

聚合后的结果。

窗口函数：

SUM(amount)
OVER(...)

结果：

日期	原金额	累计
1号	100	100
2号	200	300

既保留：

原始行

又得到：

统计结果。

这就是窗口函数最大的价值。

所以，这条 SQL：

SELECT 
    sales_date,
    amount,
    SUM(amount) OVER (ORDER BY sales_date) AS cumulative_total
FROM sales
ORDER BY sales_date;

本质上是在做：

“从第一天开始，到当前这一天为止，总共卖了多少钱？”

这是 MySQL 8 窗口函数中最经典、最常用于运营报表的写法之一。如果再配合：

ROW_NUMBER()（排名）
LAG()（环比）
LEAD()（预测）
DENSE_RANK()（TopN）
NTILE()（用户分层）

基本上就覆盖了大部分生产中的分析类 SQL 场景。

posted on 2026-07-05 15:38 日思日睿阅读(16) 评论(0) 收藏举报

刷新页面返回顶部

日思日睿

mysql进阶二

案例一：LIMIT 深分页

场景

为什么慢？

EXPLAIN

优化

效果

案例二：已经有索引，但用了函数

原因

案例三：LIKE 前缀 %

案例四：SELECT *

案例五：OR 导致索引失效

案例六：IN 超长列表

案例七：COUNT(*)

案例八：Join 顺序错误

案例九：Join 无索引

案例十：分组排序

案例十一：删除大表数据

案例十二：分页 + 排序

最经典的一道面试题

这些案例真正体现的规律是：

场景

为什么慢？

优化方案：临时表 JOIN

第一步：创建临时表

第二步：批量插入ID

第三步：Join 查询

为什么快？

具体例子

Java 中怎么做？

如果不能建临时表呢？

和超长 IN 对比

什么时候该用？

但是……

一、举个例子

二、OVER (ORDER BY ...) 到底是什么意思？

三、以前（MySQL 8 之前）怎么写？

四、生产中的应用

1. 累计充值

2. 股票收益

3. 用户成长值

4. 游戏流水

五、按用户分别累计

六、最近 7 天累计

七、执行顺序

八、为什么比 GROUP BY 强？

导航

公告