149 lines
4.9 KiB
SQL
149 lines
4.9 KiB
SQL
-- 批次聚合查询性能优化 - 添加索引
|
||
-- 执行时间: 2026-01-09
|
||
-- 目的: 提升批次列表查询性能 10-100 倍
|
||
|
||
-- ============================================
|
||
-- 1. 检查当前索引
|
||
-- ============================================
|
||
SELECT '1. 当前索引列表' AS step;
|
||
SHOW INDEX FROM test_work_order;
|
||
|
||
-- ============================================
|
||
-- 2. 检查表数据量
|
||
-- ============================================
|
||
SELECT '2. 表数据量统计' AS step;
|
||
SELECT
|
||
COUNT(*) AS total_workorders,
|
||
COUNT(DISTINCT batch_id) AS total_batches,
|
||
COUNT(CASE WHEN order_id IS NULL THEN 1 END) AS unlinked_workorders
|
||
FROM test_work_order;
|
||
|
||
-- ============================================
|
||
-- 3. 添加 batch_id 索引
|
||
-- ============================================
|
||
SELECT '3. 添加 batch_id 索引' AS step;
|
||
CREATE INDEX IF NOT EXISTS idx_batch_id ON test_work_order(batch_id);
|
||
|
||
-- ============================================
|
||
-- 4. 添加 order_id 索引
|
||
-- ============================================
|
||
SELECT '4. 添加 order_id 索引' AS step;
|
||
CREATE INDEX IF NOT EXISTS idx_order_id ON test_work_order(order_id);
|
||
|
||
-- ============================================
|
||
-- 5. 添加 test_step 索引
|
||
-- ============================================
|
||
SELECT '5. 添加 test_step 索引' AS step;
|
||
CREATE INDEX IF NOT EXISTS idx_test_step ON test_work_order(test_step);
|
||
|
||
-- ============================================
|
||
-- 6. 添加组合索引（最优）
|
||
-- ============================================
|
||
SELECT '6. 添加组合索引' AS step;
|
||
CREATE INDEX IF NOT EXISTS idx_batch_order_step
|
||
ON test_work_order(batch_id, order_id, test_step);
|
||
|
||
-- ============================================
|
||
-- 7. 添加创建时间索引（用于排序）
|
||
-- ============================================
|
||
SELECT '7. 添加创建时间索引' AS step;
|
||
CREATE INDEX IF NOT EXISTS idx_create_time ON test_work_order(create_time);
|
||
|
||
-- ============================================
|
||
-- 8. 分析表（更新统计信息）
|
||
-- ============================================
|
||
SELECT '8. 分析表' AS step;
|
||
ANALYZE TABLE test_work_order;
|
||
|
||
-- ============================================
|
||
-- 9. 验证索引创建结果
|
||
-- ============================================
|
||
SELECT '9. 验证索引' AS step;
|
||
SHOW INDEX FROM test_work_order;
|
||
|
||
-- ============================================
|
||
-- 10. 测试查询性能（EXPLAIN）
|
||
-- ============================================
|
||
SELECT '10. 测试查询性能' AS step;
|
||
|
||
-- 测试 1: 全部批次
|
||
EXPLAIN SELECT
|
||
batch_id,
|
||
batch_name,
|
||
COUNT(*) as work_order_count,
|
||
SUM(CASE WHEN test_step = 0 THEN 1 ELSE 0 END) as pending_count,
|
||
SUM(CASE WHEN test_step = 1 THEN 1 ELSE 0 END) as testing_count,
|
||
SUM(CASE WHEN test_step = 5 THEN 1 ELSE 0 END) as completed_count
|
||
FROM test_work_order
|
||
WHERE order_id IS NULL
|
||
GROUP BY batch_id, batch_name
|
||
ORDER BY batch_id DESC
|
||
LIMIT 10;
|
||
|
||
-- 测试 2: 进行中的批次
|
||
EXPLAIN SELECT
|
||
batch_id,
|
||
batch_name,
|
||
COUNT(*) as work_order_count,
|
||
SUM(CASE WHEN test_step = 1 THEN 1 ELSE 0 END) as testing_count
|
||
FROM test_work_order
|
||
WHERE order_id IS NULL
|
||
GROUP BY batch_id, batch_name
|
||
HAVING SUM(CASE WHEN test_step = 1 THEN 1 ELSE 0 END) > 0
|
||
ORDER BY batch_id DESC
|
||
LIMIT 10;
|
||
|
||
-- ============================================
|
||
-- 11. 实际执行查询（测试速度）
|
||
-- ============================================
|
||
SELECT '11. 实际查询测试' AS step;
|
||
|
||
-- 记录开始时间
|
||
SET @start_time = NOW(6);
|
||
|
||
-- 执行查询
|
||
SELECT
|
||
batch_id,
|
||
batch_name,
|
||
COUNT(*) as work_order_count,
|
||
SUM(CASE WHEN test_step = 0 THEN 1 ELSE 0 END) as pending_count,
|
||
SUM(CASE WHEN test_step = 1 THEN 1 ELSE 0 END) as testing_count,
|
||
SUM(CASE WHEN test_step = 2 THEN 1 ELSE 0 END) as review1_count,
|
||
SUM(CASE WHEN test_step = 3 THEN 1 ELSE 0 END) as review2_count,
|
||
SUM(CASE WHEN test_step = 4 THEN 1 ELSE 0 END) as review3_count,
|
||
SUM(CASE WHEN test_step = 5 THEN 1 ELSE 0 END) as completed_count
|
||
FROM test_work_order
|
||
WHERE order_id IS NULL
|
||
GROUP BY batch_id, batch_name
|
||
ORDER BY batch_id DESC
|
||
LIMIT 10;
|
||
|
||
-- 记录结束时间
|
||
SET @end_time = NOW(6);
|
||
|
||
-- 显示执行时间
|
||
SELECT
|
||
'查询执行时间' AS metric,
|
||
TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) / 1000 AS milliseconds;
|
||
|
||
-- ============================================
|
||
-- 12. 索引大小统计
|
||
-- ============================================
|
||
SELECT '12. 索引大小统计' AS step;
|
||
SELECT
|
||
TABLE_NAME,
|
||
INDEX_NAME,
|
||
ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
|
||
FROM mysql.innodb_index_stats
|
||
WHERE TABLE_NAME = 'test_work_order'
|
||
AND DATABASE_NAME = DATABASE()
|
||
AND STAT_NAME = 'size'
|
||
ORDER BY size_mb DESC;
|
||
|
||
-- ============================================
|
||
-- 完成
|
||
-- ============================================
|
||
SELECT '索引优化完成！' AS status;
|
||
SELECT '预期性能提升: 10-100倍' AS result;
|
||
SELECT '建议: 定期执行 ANALYZE TABLE test_work_order' AS recommendation;
|