在大数据量的业务系统中，嵌套子查询是SQL编写时常用的逻辑实现方式，但当数据量达到千万级甚至亿级时，未优化的嵌套子查询会导致全表扫描、临时表创建、重复计算等问题，查询耗时可能从毫秒级上升到分钟级。分区表通过将大表拆分为多个独立的小数据单元，能够大幅减少数据扫描范围，与嵌套子查询配合使用可以从多个维度提升查询性能。
嵌套子查询的常见性能问题
嵌套子查询的性能问题主要来源于以下几个方面：
- 全表扫描问题：子查询如果没有合适的索引或分区支撑，会扫描整张大表，数据量越大扫描耗时越长。
- 重复执行问题：部分子查询会在外层查询的每一行结果中被重复执行，导致计算量成倍增加。
- 临时表开销：数据库执行子查询时可能会创建临时表存储中间结果，临时表的创建和读写会占用额外内存和磁盘资源。
分区表的核心作用
分区表是将一张逻辑大表按照指定规则拆分为多个物理分区，每个分区可以独立存储、独立查询，核心优势包括：
- 查询时可以通过分区裁剪只扫描符合条件的分区，减少数据扫描量。
- 分区可以独立维护，比如单独备份、清理某个分区的数据，不影响其他分区。
- 部分数据库支持分区级索引，进一步提升分区内的查询效率。
分区表与嵌套子查询的配合优化方案
1. 分区表设计原则
要让分区表有效支撑嵌套子查询，设计时需要遵循以下原则：
- 分区键选择子查询和外层查询都常用的过滤字段，比如时间字段、业务归属字段。
- 分区粒度合理，避免单个分区数据量仍然过大，或者分区数量过多增加管理成本。
- 分区表需要配合分区键上的索引使用，进一步提升过滤效率。
2. 优化示例场景
假设存在一张订单表order_table，存储了近3年的订单数据，数据量达到2亿条，业务需要查询2024年每个用户的订单总金额，同时过滤掉订单金额小于100的用户。原始的嵌套子查询SQL如下：
-- 原始嵌套子查询，未使用分区表优化
SELECT
user_id,
total_amount
FROM (
SELECT
user_id,
SUM(order_amount) AS total_amount
FROM order_table
WHERE order_time >= '2024-01-01'
AND order_time < '2025-01-01'
GROUP BY user_id
) t
WHERE t.total_amount >= 100;
如果order_table没有分区，上述子查询会扫描全表2亿条数据，即使有order_time的索引，扫描范围仍然很大。我们将order_table按照order_time的月份进行分区，分区表创建语句如下：
-- 创建按月分区的订单表
CREATE TABLE order_table (
order_id BIGINT,
user_id BIGINT,
order_amount DECIMAL(10,2),
order_time DATETIME
)
PARTITION BY RANGE (TO_DAYS(order_time)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
-- 后续月份分区以此类推
PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 在分区键order_time上创建索引
CREATE INDEX idx_order_time ON order_table(order_time);
-- 在user_id上创建索引提升分组效率
CREATE INDEX idx_user_id ON order_table(user_id);
优化后的嵌套子查询SQL不需要修改逻辑，数据库会自动进行分区裁剪，只扫描2024年的对应月份分区，扫描的数据量从2亿条降低到约6000万条，查询耗时可以从原来的120秒降低到15秒左右。如果外层查询也有时间过滤条件，还可以进一步裁剪分区范围。
3. 进一步优化技巧
- 如果子查询的结果集较小，可以将子查询改写为关联查询，减少嵌套层级，部分数据库对关联查询的优化效率高于嵌套子查询。
- 对于需要重复使用的子查询结果，可以先将子查询结果写入临时分区表，再对外层查询做计算，避免重复执行子查询。
- 定期清理过期分区的数据，保持每个分区的数据量在合理范围，避免单个分区过大影响查询效率。
优化效果验证
可以通过数据库的查询执行计划查看优化前后的扫描行数和耗时对比，以MySQL为例，使用EXPLAIN命令查看执行计划：
-- 查看优化后的查询执行计划
EXPLAIN
SELECT
user_id,
total_amount
FROM (
SELECT
user_id,
SUM(order_amount) AS total_amount
FROM order_table
WHERE order_time >= '2024-01-01'
AND order_time < '2025-01-01'
GROUP BY user_id
) t
WHERE t.total_amount >= 100;
执行计划中如果partitions字段只显示2024年的对应分区，说明分区裁剪生效，rows字段的扫描行数会明显低于全表扫描的行数，证明优化方案生效。