社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL的10种高级SQL,性能飞升

鸭哥聊Java • 8 月前 • 189 次点击  

那天晚上我正准备关电脑回家,产品突然一句:“东哥,这个列表怎么点一下就要转半天圈?” 我心里一紧:完了,又是 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。

很多人做“插入或更新”的逻辑是这样的:

  1. 先 select 看看有没有
  2. 有就 update,没有就 insert

这在高并发下面,非常适合制造锁冲突和幻读现场。

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 ...

然后重点看几个东西:

  • type 是不是 all;
  • key / possible_keys 用的是哪个索引;
  • rows 预估行数离谱不离谱;
  • Extra 里面有没有 “Using temporary; Using filesort”。

有一次线上那条 SQL 怎么改都不快,最后一看 rows 预估 100,实际扫了 2w 行,统计信息都飘了,跑个 analyze table 立马恢复正常,差点就怀疑人生了。

差不多就这 10 招,我自己日常写业务 SQL,大部分“性能飞升”的感觉都是从这些小细节堆出来的。 说难也不难,就是要养成几个习惯:

  • 不写 select *;
  • 大分页不用 offset;
  • 写之前先想索引长啥样;
  • 写完顺手 explain 一眼。

行了,我去泡个茶,等会儿要把我们组那谁的 select * from xxx limit 100000, 20 抄到周会 PPT 上公开处刑一下,你们先把自己的 SQL 翻翻,有没有类似的黑历史 😏

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/193614