案例背景
订单统计报表出现性能问题，查询涉及：
订单表（orders）：2亿条记录，按时间范围分区
用户表（users）：5000万条记录
商品表（products）：1000万条记录
原始SQL （执行时间 >10秒）：
SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount, MAX(o.create_time) AS last_order_time FROM users u LEFT JOIN orders o ON u.user_id = o.user_id LEFT JOIN products p ON o.product_id = p.product_id WHERE u.vip_level > 3 AND o.create_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND p.category_id IN (1001, 1002, 1005) GROUP BY u.user_id ORDER BY total_amount DESC LIMIT 1000;
优化步骤
一、执行计划分析（EXPLAIN）
通过
EXPLAIN FORMAT=JSON发现：
-
orders表使用低效的索引（index_merge） -
products表出现临时表（Using temporary） -
排序未使用索引（Using filesort）
users表全表扫描（type=ALL）
二、索引优化
用户表优化
ALTER TABLE users ADD INDEX idx_vip_user (vip_level, user_id, user_name);
订单表优化
ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time) ADD INDEX idx_product_time (product_id, create_time);
商品表优化
ALTER TABLE products ADD INDEX idx_category (category_id, product_id);
三、查询重写
提前过滤数据
WITH filtered_products AS ( SELECT product_id FROM products WHERE category_id IN (1001, 1002, 1005) ), filtered_orders AS ( SELECT o.user_id, o.order_id, o.amount, o.create_time FROM orders o JOIN filtered_products fp ON o.product_id = fp.product_id WHERE o.create_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) ) SELECT u.user_id, u.user_name, COUNT(fo.order_id) AS order_count, SUM(fo.amount) AS total_amount, MAX(fo.create_time) AS last_order_time FROM users u LEFT JOIN filtered_orders fo ON u.user_id = fo.user_id WHERE u.vip_level > 3 GROUP BY u.user_id ORDER BY total_amount DESC LIMIT 1000;
三、分区表优化
-- 修改orders表为双层级分区
ALTER TABLE orders
PARTITION BY RANGE (TO_DAYS(create_time))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 8 (
PARTITION p2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01'))
);
四、参数调优
SET SESSION sort_buffer_size = 64*1024*1024; -- 提升排序性能 SET SESSION optimizer_switch = 'prefer_ordering_index=on';
优化效果对比
优化后执行时间从原来的 12.8s提升到 0.52s，整体速度提升两个数量级，效果明显。
![[20250418]绑定变量太多的限制.txt](/d/file/efpub/2026/03-03/small85d6e1154f1b9627b9d78570d310ec4c1772545943.jpg "[20250418]绑定变量太多的限制.txt")