查询性能调优技巧

查询性能调优需要从SQL改写、索引设计、执行计划分析等多角度入手。

SQL改写技巧

避免SELECT *

-- ❌ 查询所有列，增加IO和网络开销
SELECT * FROM orders WHERE user_id = 1;

-- ✅ 只查询需要的列
SELECT id, user_id, amount FROM orders WHERE user_id = 1;

使用LIMIT限制结果

-- ✅ 分页查询，避免大量数据返回
SELECT * FROM orders ORDER BY id LIMIT 100 OFFSET 0;

-- ✅ 深分页优化
SELECT * FROM orders WHERE id > 1000 ORDER BY id LIMIT 100;

小表驱动大表

-- ❌ 大表驱动小表
SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id;

-- ✅ 小表驱动大表（当users表更小时）
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;

避免子查询，使用JOIN

-- ❌ 子查询效率低
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- ✅ 使用JOIN替代
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

避免OR，使用UNION ALL

-- ❌ OR可能导致索引失效
SELECT * FROM orders WHERE user_id = 1 OR status = 2;

-- ✅ 使用UNION ALL
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 2;

使用EXISTS替代IN

-- ❌ IN查询效率低（大结果集）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE created_at > '2026-01-01');

-- ✅ EXISTS效率更高
SELECT o.* FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.created_at > '2026-01-01');

索引优化技巧

确保查询使用索引

-- 检查执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 确认type列：
-- system/const: 最佳
-- eq_ref/ref: 好
-- range: 可接受
-- index: 全索引扫描
-- ALL: 全表扫描（需优化）

使用覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- 查询只访问索引，无需回表
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

优化ORDER BY

-- ✅ ORDER BY使用索引
CREATE INDEX idx_user_time ON orders(user_id, created_at);
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at;

-- ❌ ORDER BY与索引顺序不一致
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;  -- 降序可能不走索引

-- ✅ 创建降序索引（MySQL 8.0+）
CREATE INDEX idx_user_time_desc ON orders(user_id, created_at DESC);

优化GROUP BY

-- ✅ GROUP BY使用索引
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status;

-- ⚠️ 大分组结果使用临时表
-- 可调整 tmp_table_size 或使用松散索引扫描

执行计划分析技巧

关键列解读

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- id: 查询顺序
-- select_type: 查询类型（SIMPLE最好）
-- table: 访问表
-- type: 访问类型（ref/range/ALL）
-- key: 使用索引
-- rows: 预估扫描行数
-- Extra: 额外信息

Extra列常见值

配置优化技巧

调整Buffer Pool

-- Buffer Pool设为物理内存的70%-80%
SET GLOBAL innodb_buffer_pool_size = 4G;

-- 多实例减少竞争
SET GLOBAL innodb_buffer_pool_instances = 8;

调整查询缓存（MySQL 5.7及以下）

-- 查询缓存（高频相同查询场景）
SET GLOBAL query_cache_size = 64M;
SET GLOBAL query_cache_type = ON;

-- 注意：MySQL 8.0已移除查询缓存

调整临时表大小

-- 增大内存临时表大小
SET GLOBAL tmp_table_size = 256M;
SET GLOBAL max_heap_table_size = 256M;

分页优化

深分页问题

-- ❌ 深分页扫描大量数据
SELECT * FROM orders LIMIT 10000, 100;  -- 扫描10010行

-- ✅ 使用主键定位
SELECT * FROM orders WHERE id > 10000 LIMIT 100;

-- ✅ 使用子查询延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 10000, 100) t ON o.id = t.id;

COUNT优化

-- ❌ COUNT(*)统计所有列
SELECT COUNT(*) FROM orders;

-- ⚠️ COUNT(1)与COUNT(*)性能相当

-- ✅ 使用覆盖索引计数
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(status) FROM orders WHERE status = 1;

-- ✅ 预估统计（不精确）
SHOW TABLE STATUS LIKE 'orders';

要点总结

• 避免SELECT *，只查询必要列
• 小表驱动大表，JOIN替代子查询
• 使用覆盖索引减少回表
• 确保ORDER BY/GROUP BY使用索引
• 分析执行计划确认索引使用
• 深分页使用主键定位或延迟关联优化
• 调整Buffer Pool等参数提升整体性能