SQL优化实战

2025/04/19

最近在优化一个SQL查询时，学到了不少东西，感觉这个过程挺值得记录的。优化SQL不光是改改语句，还得搞清楚数据库是怎么想的，哪些地方能省力，哪些地方容易翻车。下面是我从分析到优化的全过程。

一、从缓冲池看查询

以前优化SQL只喜欢看耗时，最近学到了还可以从缓冲池（Buffer Pool）入手，因为它能直观告诉你查询到底“拖”了多少数据进来。这次优化的例子是test表的一个分页查询，过程大概是这样的：

1. 先创建表

-- 创建test表，非常简单
CREATE TABLE test (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
    val INT NOT NULL COMMENT '分类',
    data VARCHAR(100) COMMENT '数据',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id),
    INDEX idx_val (val),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB 

-- 清理一下之前的数据
DELETE FROM test;
DROP PROCEDURE IF EXISTS insert_million_records;

-- 创建存储过程，插入1kw条数据
DELIMITER //
CREATE PROCEDURE insert_million_records()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 10000000 DO
        INSERT INTO test (val, data) VALUES (FLOOR(RAND() * 100), CONCAT('data_', i));
        SET i = i + 1;
    END WHILE;
    COMMIT;
END //
DELIMITER ;

-- 调用存储过程
CALL insert_million_records();  

创建1kw数据大约3min

1. 先看看缓冲池里有什么 在跑查询之前，先查test表在缓冲池里占了多少数据页。

SELECT COUNT(*) 
FROM information_schema.innodb_buffer_page 
WHERE TABLE_NAME LIKE '%test%';

这时候缓冲池基本是空的，数据页数量为0，说明还没加载啥数据。

1. 跑原查询 原查询：SELECT * FROM test WHERE val = 4 LIMIT 300000, 5; 看着简单，但偏移量300000太夸张了，数据库得扫描一大堆数据。

2. 再查缓冲池 跑完查询后又查了一次缓冲池，结果发现加载了4098个数据页和208个索引页 说明数据库把一大堆数据都拖进来了。

3. 清空缓冲池，重新来过 为了公平对比，重启了数据库，把缓冲池清空。清之前得把innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup关掉。

4. 跑优化后的查询 优化后的查询我用了子查询的思路，先找到符合条件的id，再关联回去拿完整数据，SQL是这样的：

SELECT * 
FROM test a 
INNER JOIN (SELECT id FROM test WHERE val = 4 LIMIT 300000, 5) b 
ON a.id = b.id;

跑完记录耗时，惊喜地发现只用了零点几秒！再查缓冲池，加载的数据页只有5个，索引页390个。这说明优化后的查询精准多了，基本只拿了需要的数据。

二、进一步分析？

这个例子是基于财务流水表的分页查询，感觉在实际业务里还挺常见的。我分析了一下它的适用性和局限性，分享下我的想法：

1. 场景很真实 像财务流水表、订单表、日志表这种存了海量数据的表，在公司里几乎无处不在。分页查询也是标配需求，比如报表系统要展示“第100页的交易记录”，这种场景我自己在项目里就遇到过好几次。所以，这个例子的性能问题很有代表性。

2. 优化思路很实用 用子查询先拿主键id，再关联查询的办法，真的挺好使。它不用改表结构，也不用加索引，属于“轻量级优化”，在MySQL、PostgreSQL上都能用。我试过在公司的一个订单查询上用类似的方法，效果也很明显。

3. 但也有局限性 假设val的重复率很高（比如一半记录都是val = 4），子查询返回的id就会很多🤔，内连接的性能可能还不如原查询。 还有，LIMIT 300000, 5这种超大偏移量在现实中不太常见，通常分页会用WHERE id > last_id的方式优化，避免这么大的偏移量。 另外，例子没考虑并发和事务，生产环境里其他查询可能会干扰缓冲池，优化效果没这么“纯净”。

三、怎么让查询更快？

优化SQL不是一蹴而就的，我在这个过程中踩了不少坑，也学到了不少东西。下面是我总结的一些优化方向（还有一些想继续探索的想法

1. 深入剖析执行计划

一开始优化的时候，我光盯着耗时看，后来发现EXPLAIN能直接告诉你数据库是怎么执行查询的。拿原查询来说：

EXPLAIN SELECT * FROM test WHERE val = 4 LIMIT 300000, 5;

输出：

• type: ALL（全表扫描，说明没用索引）

• rows: 几百万（扫描的行数很多）

• Extra: Using where（说明有过滤，但没啥优化）

优化后的查询呢：

EXPLAIN SELECT * 
FROM test a 
INNER JOIN (SELECT id FROM test WHERE val = 4 LIMIT 300000, 5) b 
ON a.id = b.id;

显示：

• 子查询部分：type: INDEX（用上了val的索引）

• 外层连接：type: eq_ref（通过主键关联，效率很高）

• rows: 5（扫描行数大大减少）

通过EXPLAIN，我发现原查询慢是因为扫描了太多行，而优化后的查询精准定位了数据。

还试了EXPLAIN ANALYZE（MySQL 8.0支持），能看到每步的实际耗时，帮我搞清楚子查询和内连接的开销。以后在想能不能把EXPLAIN的结果画成执行路径图，方便跟同事讨论优化方案🤔

踩坑经验：最开始直接给val加了个普通索引，以为能解决问题，结果EXPLAIN显示还是ALL，因为偏移量太大，数据库压根没用索引，查询还更慢了。后来才明白，偏移量大的问题得从查询逻辑下手，子查询才是王道。

2. 资源监控：别光看耗时

光看耗时还不够，还可以用SHOW STATUS和SHOW ENGINE INNODB STATUS监控了查询的资源消耗，比如CPU、内存、磁盘I/O。

原查询的I/O开销特别大，因为它把一大堆数据页读进了缓冲池。优化后，I/O开销几乎忽略不计，CPU占用也低了不少。

想试试的：以后或许可以尝试用性能分析工具（比如MySQL的Performance Schema）看看查询的详细开销，比如锁等待时间、临时表使用情况。这样能更全面地评估优化效果。

3. 复杂场景的优化：不止单表

这次例子是单表查询，但现实中多表关联、子查询嵌套、聚合函数啥的都可能有。我在想，如果是订单表和用户表关联查询，优化思路会不会变？比如，可以先用子查询过滤订单，再关联用户表，减少扫描量。

踩坑经验：有次我写了个嵌套三层的子查询，觉得自己特聪明，结果数据库直接卡退了哈哈😓。后来发现，子查询太复杂会导致临时表开销暴增。

优化得一步步试，不能一上来就写“花里胡哨”的SQL。

4. 索引优化：选对索引很重要

val字段的索引类型对性能影响很大。如果val的重复率高，普通索引可能没啥用，联合索引（比如val+id）可能会更好。如果业务允许，能不能把val做成分区键，按值分区，查询时直接定位到分区，效率更高。

想试试的：研究下索引的维护成本，比如索引多了会不会拖慢写操作？有没有办法动态调整索引策略？

5. 跨数据库对比：MySQL不是唯一选择

MySQL的优化已经让我学到不少，但也可以试试其他数据库的玩法。比如，PostgreSQL对分页查询的优化很强，可以用ROW_NUMBER()做分页，写法更直观：

SELECT * 
FROM test 
WHERE row_number() OVER (PARTITION BY val ORDER BY id) BETWEEN 300001 AND 300005 
AND val = 4;

还看了点ClickHouse，听说它用列式存储，特别适合大表查询。

（以后可以找个开源数据集，在MySQL、PostgreSQL、ClickHouse上跑跑对比实验，看看谁更牛。

6. 业务场景的延伸：从流水表到订单表

这个例子的优化思路可以迁移到其他业务场景，比如电商订单查询。假设订单表有order_status和create_time，用户想查“最近一个月已支付订单”的第100页，我可能会写：

SELECT * 
FROM orders a 
INNER JOIN (SELECT order_id FROM orders 
            WHERE order_status = 'paid' AND create_time >= '2025-03-18' 
            ORDER BY create_time DESC LIMIT 990, 10) b 
            ON a.order_id = b.order_id;

为了让它更快，我会给(order_status, create_time)加个联合索引。不过，如果订单表有几亿条数据，单表可能扛不住，得考虑分库分表。

想试试的：搭个测试环境，模拟大表查询的场景，试试分表后跨表查询的性能。

7. 数据增长的应对：未雨绸缪

数据量大了以后，单表查询肯定会越来越慢。那能不能按年份分表，比如finance_2024、finance_2025，查询时动态拼接表名？不过分表后，跨表查询可能是个麻烦。另外，读写分离+Redis缓存热点数据感觉也很香，能大大减轻数据库压力。

8. 踩坑总结：失败是成功之母

优化SQL的过程真是“痛并快乐着”。除了前面提到的索引和子查询的坑，我还遇到过把LIMIT写错的情况。有一次我把LIMIT 300000, 5写成了LIMIT 5, 300000，结果查出来的数据完全不对，调试了半天才发现问题。这让我意识到，写SQL得细心，跑之前最好先跑个小数据集验证。

还有一次太迷信子查询，写了个超级复杂的SQL，结果性能还不如原查询。后来我学会了先用EXPLAIN看执行计划，再决定怎么改。这次优化让我对SQL的“套路”更有感觉了，以后肯定会多总结、多实践。

四、写在最后

这次SQL优化的过程让我学到了很多，从缓冲池到执行计划，从索引到业务场景。

SQL优化不光是技术活，还得懂业务、会思考。

接下来也想多研究点数据库底层原理，比如查询优化器的逻辑，还要多试试其他数据库的优化方法。

希望以后能把这些经验用在更大的项目里，解决更复杂的性能问题！