MySQL查询优化

查询优化是提高数据库性能的关键技术。通过合理的查询优化，可以显著提升数据库的响应速度和吞吐量。本文档将详细介绍MySQL查询优化的原理、技术和最佳实践。

查询执行过程

理解MySQL如何执行查询是优化的基础：

连接：客户端连接到MySQL服务器
解析：解析SQL语句，验证语法
预处理：检查权限和表结构
优化：查询优化器生成执行计划
执行：执行器执行查询计划
返回结果：将结果返回给客户端

查询优化器

MySQL查询优化器负责选择最优的执行计划，主要考虑：

选择算法

全表扫描：扫描整个表
索引扫描：使用索引访问数据
范围扫描：在索引上进行范围查找
唯一键查找：通过唯一索引查找单行

连接算法

嵌套循环连接（NLJ）：最常用的连接算法
索引嵌套循环连接（INLJ）：使用索引优化连接
块嵌套循环连接（BNLJ）：减少磁盘I/O
哈希连接：MySQL 8.0.20+支持

索引优化

索引类型与选择

B-Tree索引：适用于等值、范围、排序查询

sql

-- 创建B-Tree索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_created ON users(created_at);

复合索引：多列索引，遵循最左前缀原则

sql

-- 复合索引示例
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 有效使用复合索引的查询
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending' AND created_at > '2023-01-01';

-- 无法有效使用复合索引的查询
SELECT * FROM orders WHERE status = 'pending'; -- 跳过了最左列

前缀索引：为长字符串列创建索引

sql

-- 为长字符串创建前缀索引
CREATE INDEX idx_title_prefix ON articles(title(20)); -- 只索引前20个字符

-- 计算合适的前缀长度
SELECT 
    COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS sel20,
    COUNT(DISTINCT LEFT(title, 30)) / COUNT(*) AS sel30
FROM articles;

索引设计原则

选择性原则：选择性高的列优先创建索引

sql

-- 高选择性列（如邮箱）
CREATE INDEX idx_email ON users(email); -- 好的索引

-- 低选择性列（如性别）
-- CREATE INDEX idx_gender ON users(gender); -- 通常不建议

查询模式原则：根据查询模式创建索引

sql

-- 如果经常按用户ID和状态查询
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 如果经常按状态统计
CREATE INDEX idx_status ON orders(status);

覆盖索引原则：索引包含查询所需的所有列

sql

-- 查询只需要索引中的列，无需回表
SELECT user_id, status FROM orders WHERE user_id = 123;

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

查询语句优化

WHERE子句优化

避免在索引列上使用函数

sql

-- 不好的写法
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 好的写法
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

使用合适的比较操作符

sql

-- 使用IN代替多个OR
SELECT * FROM users WHERE status IN ('active', 'pending', 'suspended');

-- 不好的写法
-- SELECT * FROM users WHERE status = 'active' OR status = 'pending' OR status = 'suspended';

**避免SELECT ***

sql

-- 好的写法：只选择需要的列
SELECT id, username, email FROM users WHERE active = 1;

-- 不好的写法：选择所有列
-- SELECT * FROM users WHERE active = 1;

JOIN优化

确保JOIN条件上有索引

sql

-- 确保关联列上有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id); -- 通常主键已自动创建索引

SELECT u.username, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.active = 1;

选择合适的JOIN类型

sql

-- 内连接：只返回匹配的记录
SELECT u.username, o.total 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接：返回左表所有记录
SELECT u.username, o.total 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

使用STRAIGHT_JOIN控制连接顺序

sql

-- 强制按照指定顺序连接表
SELECT /*+ USE_INDEX(orders, idx_orders_user_id) */ u.username, o.total
FROM users u 
STRAIGHT_JOIN orders o ON u.id = o.user_id;

子查询优化

使用JOIN代替子查询

sql

-- 使用EXISTS子查询
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending');

-- 使用JOIN优化
SELECT DISTINCT u.* 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'pending';

相关子查询vs非相关子查询

sql

-- 非相关子查询（执行一次）
SELECT * FROM users 
WHERE department_id = (SELECT id FROM departments WHERE name = 'IT');

-- 相关子查询（对每行执行）
SELECT u.*, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

性能分析工具

EXPLAIN命令

使用EXPLAIN分析查询执行计划：

sql

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

EXPLAIN输出字段解释：

id：查询序列号
select_type：查询类型（SIMPLE, PRIMARY, SUBQUERY等）
table：访问的表
partitions：匹配的分区
type：连接类型（system > const > eq_ref > ref > range > index > ALL）
possible_keys：可能使用的索引
key：实际使用的索引
key_len：使用的索引长度
ref：连接条件
rows：扫描的行数
filtered：通过条件过滤的行百分比
Extra：额外信息

Performance Schema

启用Performance Schema监控查询性能：

sql

-- 查看慢查询
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_time_ms,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest 
WHERE DIGEST_TEXT LIKE '%SELECT%' 
ORDER BY AVG_TIMER_WAIT DESC 
LIMIT 10;

慢查询日志

启用和分析慢查询日志：

sql

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录执行时间超过1秒的查询

-- 分析慢查询日志
mysqldumpslow /path/to/slow.log

索引优化策略

复合索引优化

最左前缀原则

sql

-- 复合索引 (a, b, c)
CREATE INDEX idx_abc ON table1(a, b, c);

-- 有效使用索引
SELECT * FROM table1 WHERE a = 1;           -- ✓
SELECT * FROM table1 WHERE a = 1 AND b = 2; -- ✓
SELECT * FROM table1 WHERE a = 1 AND b = 2 AND c = 3; -- ✓

-- 无法有效使用索引
SELECT * FROM table1 WHERE b = 2;           -- ✗
SELECT * FROM table1 WHERE c = 3;           -- ✗
SELECT * FROM table1 WHERE b = 2 AND c = 3; -- ✗

索引列顺序

sql

-- 根据查询频率和选择性安排列顺序
CREATE INDEX idx_status_user_created ON orders(status, user_id, created_at);
-- 假设查询模式：WHERE status = ? AND user_id = ? ORDER BY created_at

索引维护

定期分析表统计信息

sql

-- 更新表统计信息
ANALYZE TABLE users, orders;

-- 查看索引使用情况
SHOW INDEX FROM users;

避免过度索引

sql

-- 过多索引会影响INSERT/UPDATE/DELETE性能
-- 一般建议单表索引数量不超过5-7个

查询重写技术

使用UNION优化OR条件

sql

-- 使用OR条件（可能无法使用索引）
SELECT * FROM users WHERE city = 'Beijing' OR age > 30;

-- 使用UNION优化（可以使用不同索引）
SELECT * FROM users WHERE city = 'Beijing'
UNION
SELECT * FROM users WHERE age > 30;

使用EXISTS优化IN

sql

-- 使用IN（可能性能较差）
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');

-- 使用EXISTS优化（通常性能更好）
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending'
);

分区表优化

sql

-- 按日期分区优化查询
CREATE TABLE sales (
    id INT PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 分区裁剪：只扫描相关分区
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

配置优化

查询缓存（MySQL 5.7及以下）

sql

-- 启用查询缓存
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 268435456; -- 256MB

排序优化

sql

-- 增加排序缓冲区大小
SET SESSION sort_buffer_size = 2097152; -- 2MB

-- 对于大结果集排序，考虑创建索引
CREATE INDEX idx_created_status ON orders(created_at, status);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;

监控和诊断

识别慢查询

sql

-- 使用sys schema识别慢查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- 查看全表扫描查询
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

监控索引使用

sql

-- 查看未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL 
AND count_star = 0;

优化案例

案例1：优化分页查询

sql

-- 传统分页（在大数据集上性能差）
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 优化的分页（使用游标）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 或使用延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) AS lim ON o.id = lim.id;

案例2：优化聚合查询

sql

-- 慢查询：在大表上进行聚合
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- 优化：确保分组列有索引
CREATE INDEX idx_status ON orders(status);

-- 使用覆盖索引优化
CREATE INDEX idx_status_created ON orders(status, created_at);
SELECT status, COUNT(*), MAX(created_at) FROM orders GROUP BY status;

最佳实践总结

索引策略：
- 为WHERE、JOIN、ORDER BY、GROUP BY子句中的列创建索引
- 使用复合索引时注意列的顺序
- 定期检查和删除未使用的索引
查询编写：
- 避免在索引列上使用函数
- 使用适当的LIMIT限制结果集
- 避免不必要的列选择
表设计：
- 选择合适的数据类型
- 考虑分区策略
- 合理使用冗余字段以避免复杂JOIN
监控维护：
- 定期分析表统计信息
- 监控慢查询日志
- 使用性能分析工具

通过遵循这些优化原则和实践，可以显著提升MySQL数据库的查询性能，确保系统高效稳定运行。

MySQL查询优化 #

查询执行过程 #

查询优化器 #

选择算法 #

连接算法 #

索引优化 #

索引类型与选择 #

索引设计原则 #

查询语句优化 #

WHERE子句优化 #

JOIN优化 #

子查询优化 #

性能分析工具 #

EXPLAIN命令 #

Performance Schema #

慢查询日志 #

索引优化策略 #

复合索引优化 #

索引维护 #

查询重写技术 #

使用UNION优化OR条件 #

使用EXISTS优化IN #

分区表优化 #

配置优化 #

查询缓存（MySQL 5.7及以下） #

排序优化 #

监控和诊断 #

识别慢查询 #

监控索引使用 #

优化案例 #

案例1：优化分页查询 #

案例2：优化聚合查询 #

最佳实践总结 #