跳转至

MySQL 性能优化

MySQL 是全球使用最广泛的开源关系型数据库之一，本文聚焦性能优化，从索引设计、查询优化、配置调优到监控诊断，帮你把慢查询变快、把数据库压力降下来。

核心知识点

知识点说明
最新版本MySQL 8.4 LTS / MySQL 9.x Innovation
存储引擎InnoDB（默认，支持事务和行锁）
优化方向索引优化、查询优化、配置调优、架构优化
核心工具EXPLAIN、慢查询日志、Performance Schema
黄金法则90% 的性能问题都是索引和查询写法的问题

安装配置

Docker 快速启动

# 用 Docker 运行 MySQL 8
docker run -d \
  --name mysql \
  -p 3306:3306 \                      # 端口映射
  -e MYSQL_ROOT_PASSWORD=rootpass \    # root 密码
  -e MYSQL_DATABASE=testdb \          # 默认数据库
  -v mysql_data:/var/lib/mysql \      # 数据持久化
  mysql:8.4                           # MySQL 8.4 LTS

# 连接
mysql -h 127.0.0.1 -u root -prootpass  # 命令行连接

性能相关配置（my.cnf）

[mysqld]
# === 内存配置（根据服务器内存调整）===
innodb_buffer_pool_size = 4G          # InnoDB 缓冲池，建议设为总内存的 60-70%
innodb_buffer_pool_instances = 4      # 缓冲池实例数（每个至少1GB）
innodb_log_file_size = 512M           # redo log 大小
innodb_log_buffer_size = 64M          # log 缓冲区

# === 连接配置 ===
max_connections = 200                 # 最大连接数
thread_cache_size = 50                # 线程缓存

# === 查询缓存（MySQL 8.0 已移除）===
# query_cache_type = 0                # 8.0+ 不需要此配置

# === 慢查询日志 ===
slow_query_log = 1                    # 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log  # 日志文件路径
long_query_time = 1                   # 超过 1 秒的查询记录

# === 临时表 ===
tmp_table_size = 256M                 # 内存临时表大小
max_heap_table_size = 256M            # HEAP 表最大大小

# === 排序和连接缓冲 ===
sort_buffer_size = 4M                 # 排序缓冲区
join_buffer_size = 4M                 # JOIN 缓冲区
read_buffer_size = 2M                 # 顺序读缓冲
read_rnd_buffer_size = 8M             # 随机读缓冲

基本使用（性能诊断）

1. EXPLAIN 分析查询

-- EXPLAIN 是性能优化最重要的工具
EXPLAIN SELECT * FROM samples WHERE diagnosis = 'T2D';

-- EXPLAIN 输出关键列含义：
-- type:    访问类型（从好到坏：const > eq_ref > ref > range > index > ALL）
-- key:     实际使用的索引
-- rows:    预计扫描的行数（越少越好）
-- Extra:   额外信息（Using index = 好，Using filesort = 需要优化）

-- 更详细的分析
EXPLAIN ANALYZE SELECT * FROM samples WHERE diagnosis = 'T2D';
-- 会实际执行并显示真实耗时

2. 慢查询日志分析

# 查看慢查询日志
cat /var/log/mysql/slow.log

# 用 mysqldumpslow 分析（MySQL 自带工具）
mysqldumpslow -s t /var/log/mysql/slow.log      # 按时间排序
mysqldumpslow -s c /var/log/mysql/slow.log      # 按次数排序
mysqldumpslow -t 10 /var/log/mysql/slow.log     # 只看前 10 条

3. 查看当前连接和查询

-- 查看当前正在执行的查询
SHOW PROCESSLIST;                     -- 查看所有连接

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;            -- InnoDB 引擎详细状态

-- 查看表状态
SHOW TABLE STATUS LIKE 'samples';     -- 表的行数、大小等信息

高级用法（优化策略）

1. 索引优化

-- === 创建合适的索引 ===

-- 单列索引
CREATE INDEX idx_diagnosis ON samples(diagnosis);  -- 经常查询的字段

-- 复合索引（遵循最左前缀原则）
CREATE INDEX idx_diag_bmi ON samples(diagnosis, bmi);
-- 这个索引能加速：
-- WHERE diagnosis = 'T2D'                    ✓ 用到
-- WHERE diagnosis = 'T2D' AND bmi > 25       ✓ 用到
-- WHERE bmi > 25                             ✗ 用不到（不满足最左前缀）

-- 覆盖索引（查询的字段全在索引里，不需要回表）
CREATE INDEX idx_cover ON samples(diagnosis, sample_id, bmi);
-- SELECT sample_id, bmi FROM samples WHERE diagnosis = 'T2D';
-- EXPLAIN 会显示 Using index（全从索引取，不回表）

-- === 索引使用注意事项 ===
-- 1. 不要在低基数字段（如 gender: M/F）上建索引
-- 2. 不要建太多索引（影响写入性能）
-- 3. 定期用 ANALYZE TABLE 更新统计信息
ANALYZE TABLE samples;                -- 更新表统计信息

2. 查询优化技巧

-- === 避免全表扫描 ===

-- 坏：函数包裹索引列，索引失效
SELECT * FROM samples WHERE YEAR(created_at) = 2025;
-- 好：用范围查询
SELECT * FROM samples
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- 坏：LIKE 以 % 开头，索引失效
SELECT * FROM samples WHERE sample_id LIKE '%001';
-- 好：LIKE 以 % 结尾，可以用索引
SELECT * FROM samples WHERE sample_id LIKE 'T2D_%';

-- 坏：隐式类型转换
SELECT * FROM samples WHERE sample_id = 123;  -- sample_id 是 VARCHAR
-- 好：类型匹配
SELECT * FROM samples WHERE sample_id = '123';

-- === 分页优化 ===
-- 坏：大偏移量分页
SELECT * FROM samples ORDER BY id LIMIT 10 OFFSET 100000;  -- 扫描 100010 行
-- 好：用游标分页
SELECT * FROM samples WHERE id > 100000 ORDER BY id LIMIT 10;  -- 只扫描 10 行

-- === JOIN 优化 ===
-- 确保 JOIN 条件列上有索引
-- 小表驱动大表（小表放 FROM，大表放 JOIN）
SELECT s.sample_id, r.result
FROM samples s                        -- 小表放前面
JOIN results r ON s.id = r.sample_id  -- 确保 r.sample_id 有索引
WHERE s.diagnosis = 'T2D';

3. 批量操作优化

-- 坏：逐条插入
INSERT INTO samples VALUES (...);
INSERT INTO samples VALUES (...);
INSERT INTO samples VALUES (...);

-- 好：批量插入
INSERT INTO samples VALUES
  (...), (...), (...), (...), (...);  -- 一次插入多行

-- 更好：关闭自动提交，批量提交
SET autocommit = 0;                   -- 关闭自动提交
INSERT INTO samples VALUES (...);     -- 多条 INSERT
INSERT INTO samples VALUES (...);
COMMIT;                               -- 一次提交

-- 大量数据导入用 LOAD DATA
LOAD DATA INFILE '/path/data.csv'
INTO TABLE samples
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;                       -- 跳过表头

4. 监控脚本

#!/bin/bash
# mysql_monitor.sh - MySQL 性能监控

# 连接信息
MYSQL="mysql -u root -prootpass"

echo "=== MySQL 性能监控 ==="

# 查看连接数
echo "--- 当前连接数 ---"
$MYSQL -e "SHOW STATUS LIKE 'Threads_connected';"

# 查看慢查询数
echo "--- 慢查询统计 ---"
$MYSQL -e "SHOW STATUS LIKE 'Slow_queries';"

# 查看缓冲池命中率
echo "--- InnoDB 缓冲池命中率 ---"
$MYSQL -e "
SELECT
  (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  ) * 100 AS hit_rate_percent;
"
# 命中率应 > 99%，否则需要增大 innodb_buffer_pool_size

常见报错与解决

报错信息原因解决方法
Too many connections连接数超限增大 max_connections 或用连接池
Lock wait timeout exceeded锁等待超时优化事务，减少锁定时间
Table is full表空间或临时表满增大 tmp_table_size 或磁盘空间
Deadlock found死锁优化事务顺序，重试失败事务
查询很慢但没走索引索引失效用 EXPLAIN 检查，避免函数/类型转换
Disk full磁盘满清理 binlog: PURGE BINARY LOGS BEFORE '2025-01-01'

速查表

-- ===== MySQL 性能优化速查表 =====

-- 诊断工具
EXPLAIN SELECT ...;                   -- 查看执行计划
EXPLAIN ANALYZE SELECT ...;           -- 实际执行并分析
SHOW PROCESSLIST;                     -- 当前连接
SHOW ENGINE INNODB STATUS;            -- InnoDB 状态

-- 索引管理
CREATE INDEX idx ON t(col);           -- 创建索引
DROP INDEX idx ON t;                  -- 删除索引
SHOW INDEX FROM t;                    -- 查看索引
ANALYZE TABLE t;                      -- 更新统计

-- 索引失效的常见原因
-- 1. WHERE 中对索引列用函数
-- 2. LIKE '%xxx'（前缀通配符）
-- 3. 隐式类型转换
-- 4. OR 条件（部分情况）
-- 5. 不满足最左前缀原则

-- 配置关键参数
-- innodb_buffer_pool_size = 总内存*60-70%
-- slow_query_log = 1
-- long_query_time = 1

-- 慢查询分析
-- mysqldumpslow -s t -t 10 slow.log

-- 优化口诀
-- 1. 先看 EXPLAIN，再动手优化
-- 2. 索引不是越多越好
-- 3. 小表驱动大表
-- 4. 避免 SELECT *
-- 5. 批量操作代替逐条