那天晚上我正准备关电脑回家,产品突然一句:“东哥,这个列表怎么点一下就要转半天圈?” 我心里一紧:完了,又是 SQL 在挨打。
后来一查,经典场景:一条 select * from big_table where xxx,配合一个“创纪录”的分页 offset,直接把 MySQL 干到 80% CPU。那一刻我决定把这几年在线上折腾出来的那“10 招高级 SQL 提升性能”的小本本翻出来,给你们唠一唠。
顺便说一句,之前我还专门压过一轮 MySQL 和 Postgres 的性能,当时就发现 SQL 写得好不好,差距真能到好几倍。
先说第一个,最容易被忽略的:只查你真的要的字段。
很多同学写查询就一个习惯用语:select *,眼睛一闭,星号一敲,心里还挺踏实。结果线上一 explain: Extra 里一串 “Using temporary; Using filesort”,IO 抖得飞起。
举个特别日常的例子,订单列表页只要展示:订单号、创建时间、金额、状态。结果表里还有 10 个 JSON 字段、一个长文本备注,全给你拉出来了。
我一般会这么改:
SELECT
id, order_no, created_at, amount, status
FROM t_order
WHERE user_id = ?
AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC
LIMIT ?, ?;
Java 里配合 RowMapper,查什么映射什么:
public List listOrders(Long userId, LocalDateTime start, LocalDateTime end,
int offset, int pageSize) {
String sql = "SELECT id, order_no, created_at, amount, status " +
"FROM t_order " +
"WHERE user_id = ? AND created_at BETWEEN ? AND ? " +
"ORDER BY created_at DESC LIMIT ?, ?";
return jdbcTemplate.query(sql, ps -> {
ps.setLong(
1, userId);
ps.setObject(2, start);
ps.setObject(3, end);
ps.setInt(4, offset);
ps.setInt(5, pageSize);
}, (rs, rowNum) -> {
OrderBrief o = new OrderBrief();
o.setId(rs.getLong("id"));
o.setOrderNo(rs.getString("order_no"));
o.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
o.setAmount(rs.getBigDecimal("amount"));
o.setStatus(rs.getInt("status"));
return o;
});
}
配合一个 (user_id, created_at) 的联合索引,这种查询基本就是“嗖”的一下。
第二个,分页不要拿 offset 当健身器材用。
offset 小还行,一旦到十万、百万,那就是“数据库给你把前面的记录数完再丢掉”,人家 MySQL 是数据库,又不是垃圾分类工人对吧。
比较常见的写法:
SELECT id, name, created_at
FROM t_user
ORDER BY created_at DESC
LIMIT 100000, 20;
这种线上被我抓到都会扣鸡腿。
我一般会改成“基于上次翻页的游标”,比如用 id 或时间当锚点:
SELECT id, name, created_at
FROM t_user
WHERE created_at < ? -- 上一页最后一条的时间
ORDER BY created_at DESC
LIMIT 20;
Java 这边就是多传一个 lastCreatedAt:
public List loadNextPage(LocalDateTime lastCreatedAt, int pageSize) {
String sql = "SELECT id, name, created_at " +
"FROM t_user " +
"WHERE created_at < ? " +
"ORDER BY created_at DESC LIMIT ?";
return jdbcTemplate.query(sql, ps -> {
ps.setObject(1, lastCreatedAt);
ps.setInt(2, pageSize);
}, userRowMapper);
}
这种叫“基于游标的分页”,数据量上去之后,性能差异是能肉眼看出来的那种。
第三个,count 别乱写,尤其是那个 count(字段)。
有一次排查慢 SQL,一条 select count(user_id) from t_login where xxx,跑半天。 仔细一看,user_id 允许为 null,又没有用上合适的索引,硬生生全表扫。
大部分场景,你其实想的是“统计行数”,老老实实 count(*),MySQL 已经做了很多优化。
如果想要更狠一点,可以走覆盖索引,比如有 (user_id, created_at) 索引,那你就:
SELECT COUNT(*)
FROM t_login
FORCE INDEX(idx_user_created)
WHERE user_id = ?
AND created_at >= ?;
再配个 explain 看一下:
EXPLAIN SELECT COUNT(*)
FROM t_login FORCE INDEX(idx_user_created)
WHERE user_id = 123 AND created_at >= '2024-01-01';
看 rows、Extra 基本就知道是不是在“好好数数”还是在“瞎逛大街”。
第四个,批量插入别 for 循环一条一条怼。
这个真的是菜鸟成长的分水岭。 下面这种写法我看到会原地给你改:
for (User u : users) {
jdbcTemplate.update("INSERT INTO t_user(name, age) VALUES(?, ?)",
u.getName(), u.getAge());
}
改成批量插入:
public void saveUsers(List users) {
String sql = "INSERT INTO t_user(name, age) VALUES(?, ?)";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
User u = users.get(i);
ps.setString(1, u.getName());
ps.setInt(2, u.getAge());
}
@Override
public int getBatchSize() {
return users.size();
}
});
}
后端写着写着,你就能明显感觉到: “我不光在优化代码,我是在爱护 DBA 的头发。”
第五个,善用
INSERT ... ON DUPLICATE KEY UPDATE 做 upsert。
很多人做“插入或更新”的逻辑是这样的:
这在高并发下面,非常适合制造锁冲突和幻读现场。
MySQL 都给你准备好语法了:
INSERT INTO t_user_stat(user_id, login_count, last_login)
VALUES(?, 1, NOW())
ON DUPLICATE KEY UPDATE
login_count = login_count + 1,
last_login = VALUES(last_login);
Java 里就简单一条:
public void increaseLoginCount(long userId) {
String sql = "INSERT INTO t_user_stat(user_id, login_count, last_login) " +
"VALUES(?, 1, NOW()) " +
"ON DUPLICATE KEY UPDATE " +
"login_count = login_count + 1, " +
"last_login = VALUES(last_login)";
jdbcTemplate.update(sql, userId);
}
前提是 user_id 上有唯一键,这种写法高并发下既优雅,又不容易打爆行锁。
第六个,用 exists 别用 in 把自己玩死。
有一次同事写了个:
SELECT * FROM t_order
WHERE user_id IN (SELECT id FROM t_user WHERE status = 1);
数据一多,MySQL 的执行计划就开始“胡来”,内外层切换不定,性能飘忽不定。 我一般改成:
SELECT o.*
FROM t_order o
WHERE EXISTS (
SELECT 1
FROM t_user u
WHERE u.id = o.user_id
AND u.status = 1
);
或者直接 join:
SELECT o.*
FROM t_order o
JOIN t_user u ON u.id = o.user_id
WHERE u.status = 1;
解释执行计划的时候,你会发现 exists / join 更容易命中合适的索引。
第七个,“延迟关联”,先把小结果集挑出来再 join 大表。
典型场景:订单明细 join 大商品表。 很多人一上来就:
SELECT *
FROM t_order_item oi
JOIN t_product p ON oi.product_id = p.id
WHERE oi.user_id = ?
AND oi.created_at BETWEEN ? AND ?
ORDER BY oi.created_at DESC
LIMIT 20;
如果这个 where 条件选出来的其实就 100 行,那问题不大。 但很多时候条件放在 join 后面,导致先 join 再过滤,成本会非常高。
我自己比较喜欢两段式:
-- 先只查出本次需要的明细主键
SELECT id
FROM t_order_item
WHERE user_id = ?
AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC
LIMIT 20;
-- 然后再用这些主键做关联
SELECT oi.*, p.name, p.price
FROM t_order_item oi
JOIN t_product p ON oi.product_id = p.id
WHERE oi.id IN (?,?,?,?,? ...);
Java 里就两步走:
List ids = queryItemIds(...);
if (ids.isEmpty()) {
return Collections.emptyList();
}
return queryItemsWithProduct(ids);
看起来 SQL 变多了,但每条 SQL 都在“索引友好区”里,整体延迟反而更稳。
第八个,窗口函数该上就上,别死磕子查询。
MySQL 8 之后终于有窗口函数了,结果很多团队还在用一堆子查询算排名。 比如统计每个用户最近一条登录记录,以前很多人这么写:
SELECT t.*
FROM t_login t
JOIN (
SELECT user_id, MAX(login_time) AS max_time
FROM t_login
GROUP BY user_id
) x ON t.user_id = x.user_id AND t.login_time = x.max_time;
现在完全可以:
SELECT *
FROM (
SELECT
user_id,
login_time,
ip,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn
FROM t_login
) t
WHERE
t.rn = 1;
这玩意 explain 一下你会发现更直观,语义也清晰很多。
第九个,合理利用覆盖索引 + 回表,别一下全怼主键。
简单说就是:where / order by 的字段尽量塞入同一个联合索引里,select 的字段能被这个索引覆盖就更爽了。
比如你有一个用户账单表,经常按 user_id + month 查最新几条:
SELECT id, user_id, month, amount
FROM t_bill
WHERE user_id = ?
ORDER BY month DESC
LIMIT 6;
你直接建 (user_id, month, amount) 的联合索引,这条 SQL 可以完全在索引上完成,不用回表,MySQL 官方叫“覆盖索引”。
建索引语句你自己脑补一下,我就不贴了,主要是懒。
第十个,也是最容易被忽略的:定期看 explain,别自己脑补“它肯定走索引”。
我有个小习惯,写完一条核心 SQL,哪怕在本地,也会先来一发:
EXPLAIN ANALYZE
SELECT ...
然后重点看几个东西:
- key / possible_keys 用的是哪个索引;
- Extra 里面有没有 “Using temporary; Using filesort”。
有一次线上那条 SQL 怎么改都不快,最后一看 rows 预估 100,实际扫了 2w 行,统计信息都飘了,跑个 analyze table 立马恢复正常,差点就怀疑人生了。
差不多就这 10 招,我自己日常写业务 SQL,大部分“性能飞升”的感觉都是从这些小细节堆出来的。 说难也不难,就是要养成几个习惯:
行了,我去泡个茶,等会儿要把我们组那谁的 select * from xxx limit 100000, 20 抄到周会 PPT 上公开处刑一下,你们先把自己的 SQL 翻翻,有没有类似的黑历史 😏