MySQL查询优化
查询优化概述
1. 什么是查询优化
flowchart TD
A[编写SQL] --> B[分析执行计划]
B --> C{性能达标?}
C -->|是| D[执行SQL]
C -->|否| E[优化SQL]
E --> B
查询优化是通过改进查询语句和数据库结构来提高查询效率的过程。
2. 查询优化的目标
- 提高查询速度
- 减少资源消耗
- 提高并发能力
- 优化用户体验
查询优化方法
1. 索引优化
| 方法 | 说明 | 示例 |
|---|---|---|
| 使用索引 | 避免全表扫描 | ALTER TABLE users ADD INDEX idx_name(name) |
| 覆盖索引 | 减少回表操作 | SELECT id,name FROM users WHERE name='John' |
| 复合索引 | 优化多条件查询 | CREATE INDEX idx_name_age ON users(name,age) |
| 前缀索引 | 减少索引大小 | ALTER TABLE users ADD INDEX idx_email(email(20)) |
2. SQL语句优化
-- 优化前
SELECT * FROM orders WHERE user_id = 100;
-- 优化后
SELECT id, order_no, amount
FROM orders
WHERE user_id = 100;
-- 优化前
SELECT * FROM products
WHERE price > 100
ORDER BY create_time DESC;
-- 优化后
SELECT id, name, price
FROM products
WHERE price > 100
ORDER BY create_time DESC
LIMIT 20;
3. 表结构优化
-- 优化前
CREATE TABLE logs (
id INT,
content TEXT,
create_time DATETIME
);
-- 优化后
CREATE TABLE logs (
id INT PRIMARY KEY,
content VARCHAR(1000),
create_time TIMESTAMP,
INDEX idx_create_time(create_time)
);
查询分析工具
1. EXPLAIN分析
-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- 查看详细执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;
2. 性能分析
-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 查看进程列表
SHOW PROCESSLIST;
-- 查看表状态
SHOW TABLE STATUS LIKE 'orders';
查询优化最佳实践
1. 索引使用原则
- 最左前缀原则
- 选择性原则
- 覆盖索引原则
- 索引列数限制
2. SQL编写原则
- 只查询需要的列
- 避免使用SELECT *
- 使用LIMIT限制结果集
- 避免使用函数运算
3. 表设计原则
- 选择合适的数据类型
- 控制字段长度
- 合理使用NULL
- 适当冗余设计
常见优化场景
1. 分页查询优化
-- 优化前
SELECT * FROM orders
ORDER BY create_time DESC
LIMIT 10000, 20;
-- 优化后
SELECT * FROM orders
WHERE create_time < '2023-01-01'
ORDER BY create_time DESC
LIMIT 20;
2. JOIN查询优化
-- 优化前
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';
-- 优化后
SELECT o.id, o.order_no, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';
3. 子查询优化
-- 优化前
SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users
WHERE status = 'active'
);
-- 优化后
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';
注意事项
-
性能监控
- 慢查询日志
- 执行计划分析
- 系统资源监控
- 性能指标跟踪
-
优化建议
- 定期优化表
- 更新统计信息
- 清理无用索引
- 维护数据分布
-
运维管理
- 监控告警
- 性能分析
- 容量规划
- 故障处理
-
重要参数
- innodb_buffer_pool_size
- innodb_log_file_size
- max_connections
- query_cache_size