MySQL 性能优化
性能优化是数据库管理中最重要的技能之一。本章将详细介绍 MySQL 性能优化的方法和技巧。
性能优化概述
MySQL 性能优化是一个系统工程，涉及多个层面：
┌─────────────────────────────────────────────────────────────┐
│ 性能优化层次 │
├─────────────────────────────────────────────────────────────┤
│ 1. SQL 优化 → 查询语句优化、索引使用 │
│ 2. 表结构优化 → 数据类型、范式设计、反范式 │
│ 3. 参数优化 → Buffer Pool、连接数、日志配置 │
│ 4. 硬件优化 → 内存、磁盘、CPU │
│ 5. 架构优化 → 读写分离、分库分表、缓存 │
└─────────────────────────────────────────────────────────────┘
EXPLAIN 执行计划分析
EXPLAIN 是 MySQL 提供的查询分析工具，用于查看 MySQL 如何执行 SQL 语句。
基本使用
-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- MySQL 8.0.18+ 支持更详细的分析
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
-- 以 JSON 格式输出（更详细）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';
-- 以树形结构输出
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE name = '张三';
EXPLAIN 输出列详解
执行 EXPLAIN 后会返回以下列：
| 列名 | 说明 |
|---|---|
| id | 查询标识符，相同 id 表示同一查询 |
| select_type | 查询类型 |
| table | 访问的表 |
| partitions | 匹配的分区 |
| type | 访问类型（重要） |
| possible_keys | 可能使用的索引 |
| key | 实际使用的索引 |
| key_len | 使用的索引长度 |
| ref | 索引比较的列 |
| rows | 预估扫描行数 |
| filtered | 条件过滤的百分比 |
| Extra | 额外信息 |
type 访问类型（从优到差）
type 列表示 MySQL 如何查找数据，是判断查询效率的关键指标：
| type 值 | 说明 | 性能 |
|---|---|---|
NULL | 不需要访问表，在优化阶段就完成 | 最佳 |
system | 表只有一行（系统表） | 极好 |
const | 主键或唯一索引的常量查询 | 极好 |
eq_ref | 主键或唯一索引的关联查询 | 很好 |
ref | 非唯一索引的等值查询 | 好 |
fulltext | 全文索引查询 | 好 |
ref_or_null | 类似 ref，额外查找 NULL | 一般 |
index_merge | 索引合并优化 | 一般 |
range | 索引范围扫描 | 一般 |
index | 全索引扫描 | 较差 |
ALL | 全表扫描 | 最差 |
各类型示例：
-- const：主键查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type = const
-- eq_ref：JOIN 时使用主键
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- type = eq_ref（对于 users 表）
-- ref：非唯一索引等值查询
CREATE INDEX idx_name ON users(name);
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type = ref
-- range：范围查询
EXPLAIN SELECT * FROM users WHERE id BETWEEN 1 AND 100;
-- type = range
-- index：全索引扫描
EXPLAIN SELECT id FROM users;
-- type = index（覆盖索引）
-- ALL：全表扫描（需要优化）
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type = ALL（函数导致索引失效）
Extra 额外信息
Extra 列提供额外的执行信息：
| Extra 值 | 说明 | 建议 |
|---|---|---|
Using index | 使用覆盖索引，不回表 | 好 |
Using where | 服务器层过滤 | 正常 |
Using temporary | 使用临时表 | 需关注 |
Using filesort | 文件排序 | 需优化 |
Using index condition | 索引下推优化 | 好 |
Using join buffer | 使用连接缓冲区 | 需关注 |
Impossible WHERE | WHERE 条件不可能为真 | 检查逻辑 |
Extra 信息示例：
-- Using index：覆盖索引
CREATE INDEX idx_name_email ON users(name, email);
EXPLAIN SELECT name, email FROM users WHERE name = '张三';
-- Extra: Using index
-- Using filesort：需要额外排序
EXPLAIN SELECT * FROM users ORDER BY created_at;
-- Extra: Using filesort
-- Using temporary：使用临时表
EXPLAIN SELECT DISTINCT status FROM orders;
-- Extra: Using temporary
-- Using index condition：索引下推
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- Extra: Using index condition
EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE 会实际执行查询并显示真实的时间开销：
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
-- 输出示例：
-> Index lookup on users using idx_name (name='张三')
(cost=0.35 rows=1) (actual time=0.015..0.016 rows=1 loops=1)
-- 解读：
-- cost=0.35：预估成本
-- rows=1：预估行数
-- actual time=0.015..0.016：实际执行时间（毫秒）
-- rows=1：实际返回行数
-- loops=1：循环次数
注意：EXPLAIN ANALYZE 会真正执行查询，对于大数据量修改操作要谨慎使用。
索引优化
索引是提升查询性能最有效的方法之一。
索引类型
MySQL 支持多种索引类型：
-- 主键索引（聚簇索引）
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 普通索引
CREATE INDEX idx_name ON users(name);
-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));
索引设计原则
1. 选择合适的列
-- 好的索引列选择：
-- 1. WHERE 条件中的列
-- 2. JOIN 关联的列
-- 3. ORDER BY 排序的列
-- 4. 区分度高的列
-- 查看列的区分度
SELECT
COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM users;
-- selectivity 越接近 1，区分度越高，越适合建索引
2. 遵循最左前缀原则
-- 复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);
-- 能使用索引的查询
WHERE name = '张三'
WHERE name = '张三' AND age = 25
WHERE name = '张三' AND age = 25 AND city = '北京'
-- 不能使用索引的查询
WHERE age = 25 -- 跳过了 name
WHERE city = '北京' -- 跳过了 name 和 age
WHERE name = '张三' AND city = '北京' -- 跳过了 age，只能用到 name
3. 覆盖索引优化
-- 普通查询需要回表
SELECT * FROM users WHERE name = '张三';
-- 覆盖索引避免回表
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index（无需回表）
-- 尽量只查询需要的列
-- 不好的写法
SELECT * FROM users WHERE name = '张三';
-- 好的写法
SELECT id, name, email FROM users WHERE name = '张三';
索引失效场景
以下情况会导致索引失效：
-- 1. 在索引列上使用函数
-- 索引失效
WHERE YEAR(created_at) = 2024
-- 优化
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
-- 2. 隐式类型转换
-- 假设 phone 是 VARCHAR 类型
-- 索引失效
WHERE phone = 13800138000
-- 优化
WHERE phone = '13800138000'
-- 3. LIKE 以通配符开头
-- 索引失效
WHERE name LIKE '%张%'
-- 能用索引
WHERE name LIKE '张%'
-- 4. 使用 OR（部分情况）
-- 如果 OR 前后列不同，可能失效
WHERE name = '张三' OR age = 25
-- 优化为 UNION
WHERE name = '张三'
UNION
WHERE age = 25
-- 5. 使用 NOT IN、NOT EXISTS、<>
-- 可能导致索引失效
WHERE status NOT IN (1, 2)
-- 优化
WHERE status IN (0, 3, 4, 5)
-- 6. 对索引列进行计算
-- 索引失效
WHERE age + 1 = 26
-- 优化
WHERE age = 25
-- 7. IS NULL 和 IS NOT NULL
-- 可能导致索引失效（取决于数据分布）
WHERE name IS NOT NULL
索引维护
-- 查看索引使用情况
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';
-- 查看未使用的索引
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_READ = 0
AND COUNT_FETCH = 0;
-- 分析表（更新统计信息）
ANALYZE TABLE users;
-- 优化表（重建表，释放空间）
OPTIMIZE TABLE users;
-- 检查索引基数
SHOW INDEX FROM users;
慢查询日志
慢查询日志是定位性能问题的重要工具。
启用慢查询日志
-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 动态启用（无需重启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';
配置文件方式
[mysqld]
# 启用慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值（秒，支持小数）
long_query_time = 1
# 记录未使用索引的查询
log_queries_not_using_indexes = 1
# 限制每分钟记录的索引查询数量
log_throttle_queries_not_using_indexes = 10
# 记录慢管理语句
log_slow_admin_statements = 1
# 输出目标（FILE、TABLE 或 FILE,TABLE）
log_output = 'FILE'
分析慢查询日志
# 使用 mysqldumpslow 汇总分析
# 按平均查询时间排序，显示前 10 条
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
# 按总查询时间排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 按平均扫描行数排序
mysqldumpslow -s ar -t 10 /var/log/mysql/slow.log
mysqldumpslow 输出示例：
Count: 1523 Time=2.45s (3735s) Lock=0.00s Rows=1.0 (1523), webapp[webapp]@localhost
SELECT * FROM orders WHERE customer_id = N AND status = 'S'
Count: 查询执行次数Time: 平均时间（总时间）Lock: 锁等待时间Rows: 返回行数
查询慢日志表
如果 log_output = 'TABLE'，可以查询表：
-- 查询最近的慢查询
SELECT
start_time,
user_host,
query_time,
lock_time,
rows_sent,
rows_examined,
sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 10;
-- 查询扫描行数过多的查询
SELECT
sql_text,
rows_examined,
rows_sent,
rows_examined / rows_sent AS ratio
FROM mysql.slow_log
WHERE rows_sent > 0
ORDER BY ratio DESC
LIMIT 10;
使用 pt-query-digest
Percona Toolkit 提供更强大的分析工具：
# 安装 Percona Toolkit
sudo apt-get install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > analysis.txt
# 只分析最近 1 小时的查询
pt-query-digest --since '1h' /var/log/mysql/slow.log
# 分析特定数据库
pt-query-digest --filter '$event->{db} =~ /mydb/' /var/log/mysql/slow.log
查询优化技巧
1. 只查询需要的列
-- 不好：SELECT *
SELECT * FROM users WHERE id = 1;
-- 好：只查询需要的列
SELECT id, name, email FROM users WHERE id = 1;
2. 使用 LIMIT 限制结果
-- 限制返回行数
SELECT * FROM orders WHERE status = 'pending' LIMIT 100;
-- 分页查询
SELECT * FROM orders ORDER BY id LIMIT 0, 20;
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 0;
3. 优化分页查询
-- 传统分页（偏移量大时性能差）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 优化方案 1：使用 WHERE 子查询
SELECT * FROM orders
WHERE id > 1000000
ORDER BY id LIMIT 20;
-- 优化方案 2：使用 INNER JOIN
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;
4. 避免 SELECT DISTINCT
-- DISTINCT 需要排序和去重
SELECT DISTINCT name FROM users;
-- 使用 GROUP BY 可能更高效
SELECT name FROM users GROUP BY name;
-- 或确保索引覆盖
CREATE INDEX idx_name ON users(name);
5. 使用 UNION ALL 代替 UNION
-- UNION 会去重，需要临时表
SELECT name FROM users WHERE age > 20
UNION
SELECT name FROM admins WHERE age > 20;
-- UNION ALL 不去重，性能更好
SELECT name FROM users WHERE age > 20
UNION ALL
SELECT name FROM admins WHERE age > 20;
6. 批量插入优化
-- 单条插入（效率低）
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');
INSERT INTO users (name, email) VALUES ('李四', '[email protected]');
-- 批量插入（效率高）
INSERT INTO users (name, email) VALUES
('张三', '[email protected]'),
('李四', '[email protected]'),
('王五', '[email protected]');
-- 使用 LOAD DATA INFILE（最快）
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, email);
7. 使用 JOIN 代替子查询
-- 子查询（可能效率低）
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
-- JOIN（通常更高效）
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';
8. 避免大事务
-- 大事务会长时间持有锁
BEGIN;
-- 大量操作...
UPDATE orders SET status = 'completed' WHERE status = 'pending';
-- 更多操作...
COMMIT;
-- 分批处理
-- 每次处理 1000 条
UPDATE orders SET status = 'completed'
WHERE status = 'pending'
LIMIT 1000;
服务器参数优化
Buffer Pool 配置
[mysqld]
# InnoDB Buffer Pool 大小（建议物理内存的 70-80%）
innodb_buffer_pool_size = 4G
# Buffer Pool 实例数（每个实例至少 1GB）
innodb_buffer_pool_instances = 4
# 预读优化
innodb_read_ahead_threshold = 56
-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 计算 Buffer Pool 命中率
SELECT
(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
AS hit_rate
FROM (
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) t;
-- 命中率应该在 99% 以上
连接参数优化
[mysqld]
# 最大连接数
max_connections = 500
# 等待超时（秒）
wait_timeout = 28800
interactive_timeout = 28800
# 连接缓冲区
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
日志配置
[mysqld]
# 慢查询日志
slow_query_log = 1
long_query_time = 1
# 二进制日志
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
# 错误日志
log_error = /var/log/mysql/error.log
# InnoDB 日志
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
性能监控
使用 Performance Schema
-- 启用 Performance Schema
-- 在 my.cnf 中确保：
-- performance_schema = ON
-- 查看等待事件
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT / 1000000000 AS total_seconds
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 查看语句执行统计
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT / 1000000000 AS total_seconds,
AVG_TIMER_WAIT / 1000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
使用 sys 数据库
MySQL 8.0 提供了 sys 数据库，简化性能分析：
-- 查看最慢的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans;
-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;
-- 查看表访问统计
SELECT * FROM sys.schema_table_statistics;
-- 查看内存使用
SELECT * FROM sys.memory_global_by_current_bytes;
小结
本章我们学习了：
- EXPLAIN 执行计划：分析查询的执行方式
- 索引优化：设计原则、索引失效场景
- 慢查询日志：定位和分析慢查询
- 查询优化技巧：实用的 SQL 优化方法
- 服务器参数优化：Buffer Pool、连接、日志配置
- 性能监控：Performance Schema 和 sys 数据库
练习
- 使用 EXPLAIN 分析一个复杂查询的执行计划
- 为一个慢查询设计合适的索引
- 配置慢查询日志并分析结果
- 计算当前数据库的 Buffer Pool 命中率