性能调优 100 条
MySQL 调优 100 条
来自生产环境的 100 条调优经验，每条一句话原则 + 可执行 SQL/命令。覆盖索引、查询、Schema、配置、运维五大方向。
一、索引类（1-25）
1
用 EXPLAIN 验证索引，不要靠猜
修改任何 SQL 或索引前后，都要 EXPLAIN 对比 type、rows、Extra 三个关键字段。
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
2
主键使用自增整数（BIGINT AUTO_INCREMENT）
避免 UUID 作为主键——随机性导致 B+Tree 频繁页分裂，写入性能下降 3-5 倍，二级索引体积翻倍。
CREATE TABLE t (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ...);
3
联合索引把选择性最高的列放最前面（等值条件）
高选择性列放前面能更快过滤，减少扫描行数。范围条件列放最后。
-- 好：status 选择性低(3个值)，user_id 高(百万) CREATE INDEX idx ON orders (user_id, status, created_at);
4
用覆盖索引消灭回表
把 SELECT 的列也加入索引，EXPLAIN Extra 出现 "Using index" 代表零回表。
-- SELECT user_id, status, amount FROM orders WHERE user_id=1 CREATE INDEX idx_cover ON orders (user_id, status, amount);
5
不要对索引列使用函数
WHERE YEAR(created_at) = 2024 导致全表扫，改为范围查询才能用索引。
-- 错：WHERE YEAR(created_at) = 2024 -- 对：WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
6
检查 VARCHAR 列与整数值比较是否有隐式转换
phone VARCHAR 但 WHERE phone = 13812345678（整数），MySQL 会把所有 phone 值转整数再比较，全表扫。
-- 错：WHERE phone = 13812345678 -- 对：WHERE phone = '13812345678'
7
LIKE '%xxx' 左模糊无法用索引，考虑全文索引或 ES
右模糊 LIKE 'xxx%' 可以走索引。两端模糊必须全文检索方案。
-- 可用索引：WHERE name LIKE '张%' -- 无法用索引：WHERE name LIKE '%张' or LIKE '%张%'
8
定期用 sys.schema_unused_indexes 清理无用索引
每个多余索引都会拖慢写入速度。先设为 INVISIBLE 观察一周，确认安全再删除。
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb'; -- 先设不可见：ALTER TABLE t ALTER INDEX idx_old INVISIBLE; -- 确认无问题再：ALTER TABLE t DROP INDEX idx_old;
9
检查重复/冗余索引
INDEX(a) 和 INDEX(a,b) 同时存在，前者完全被后者覆盖，可以删除。
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'mydb';
10
大表加索引用 ALGORITHM=INPLACE, LOCK=NONE
明确指定，若不支持则 MySQL 报错而非默默锁表。生产大表推荐 pt-online-schema-change 或 gh-ost。
ALTER TABLE orders ADD INDEX idx_new (col), ALGORITHM=INPLACE, LOCK=NONE;
11
统计信息过期时手动 ANALYZE TABLE
优化器依赖统计信息估算行数。大量数据变化后，统计信息可能过期，导致错误的执行计划。
ANALYZE TABLE orders; -- 查看索引统计：SHOW INDEX FROM orders;
12
前缀索引节省空间，但不能用于覆盖索引
对 VARCHAR(255) 只建前 20 个字符的索引，节省 3/4 空间，但无法做覆盖索引，永远需要回表。
-- 前缀索引（适合长字符串，低基数场景） CREATE INDEX idx_email_prefix ON users (email(20)); -- 查看合适前缀长度： SELECT COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel FROM users;
13
哈希索引适合等值查找，B+Tree 更通用
Memory 引擎支持 HASH 索引，O(1) 等值查找。但无法范围查询和排序，InnoDB 不原生支持（有自适应哈希索引 AHI，但不可控）。
-- 查看自适应哈希索引状态 SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
14
用函数索引（MySQL 8.0）解决表达式查询
WHERE LOWER(email) = '[email protected]' 过去无法用索引，8.0 可以建函数索引直接支持。
CREATE INDEX idx_lower_email ON users ((LOWER(email))); -- 或虚拟列方式： ALTER TABLE users ADD COLUMN email_lower VARCHAR(255) AS (LOWER(email)) VIRTUAL, ADD INDEX idx_email_lower (email_lower);
15
ORDER BY 与 WHERE 联合索引能避免 filesort
INDEX(user_id, created_at)，WHERE user_id=1 ORDER BY created_at 可以利用索引有序性，不需要额外排序。
-- 无 filesort： CREATE INDEX idx ON orders (user_id, created_at); SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at LIMIT 10;
二、查询类（26-50）
16
避免 SELECT *，只取需要的列
SELECT * 破坏覆盖索引，传输多余数据，增加 Buffer Pool 压力，JOIN 时内存消耗翻倍。
-- 错：SELECT * FROM orders WHERE user_id = 1 -- 对：SELECT order_id, amount, status FROM orders WHERE user_id = 1
17
深分页用游标分页替代 LIMIT OFFSET
LIMIT 1000000, 10 需要扫描丢弃 100 万行。游标分页只扫描目标行，无论第几页性能恒定。
-- 游标分页（记录上次最后一条） SELECT * FROM orders WHERE (created_at, id) > (:last_time, :last_id) ORDER BY created_at, id LIMIT 10;
18
批量 INSERT 比逐条 INSERT 快 10-100 倍
每次 INSERT 都是一个事务，有提交开销。批量 INSERT 一次提交，大幅减少 COMMIT 次数。
-- 一次插入 1000 行 INSERT INTO logs (user_id, action, created_at) VALUES (1, 'login', NOW()), (2, 'logout', NOW()), ...; -- 或 LOAD DATA INFILE（百万级最快）
19
大批量 DELETE 分批操作，避免长事务和锁表
一次 DELETE 100 万行会持有大量锁，Undo Log 膨胀，可能触发主从延迟。应分批每次删 1000-10000 行。
-- 分批删除（循环直到 affected_rows = 0） DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000; -- 配合 sleep(0.1) 降低 I/O 压力
20
子查询优先改写为 JOIN
相关子查询（Correlated Subquery）会对外层每一行执行一次内层查询，复杂度 O(n²)。JOIN 通常更快。
-- 相关子查询（慢） SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); -- JOIN 改写（快） SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;
21
COUNT(*) 优于 COUNT(column)，不要用 COUNT(DISTINCT) 做行数估算
COUNT(*) InnoDB 优化器会自动选最小索引扫描。COUNT(col) 需要判断 NULL，略慢。
-- 推荐 SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 大表近似行数（极快） SELECT table_rows FROM information_schema.TABLES WHERE table_name = 'orders';
22
OR 改为 UNION ALL（当两边都有索引时更快）
WHERE a=1 OR b=2 无法同时用两个索引。UNION ALL 让两个查询各走各的索引，合并结果。
SELECT * FROM t WHERE a = 1 UNION ALL SELECT * FROM t WHERE b = 2 AND a != 1; -- 去重用 UNION
23
EXISTS 通常比 IN 快（大数据集时）
IN 需要把子查询结果完全物化到内存。EXISTS 找到第一个匹配就停止，适合外层小、内层大的情况。
-- IN 的问题：子查询结果集大时全部加载 SELECT * FROM users WHERE id IN (SELECT user_id FROM logs WHERE level='ERROR'); -- EXISTS 改写 SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM logs l WHERE l.user_id = u.id AND l.level = 'ERROR');
24
用 EXPLAIN ANALYZE 获取真实执行数据（MySQL 8.0.18+）
普通 EXPLAIN 是估算值，EXPLAIN ANALYZE 真实执行并返回实际行数和时间，是排查执行计划问题的利器。
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at;
25
利用 FORCE INDEX 验证索引效果，但不要在生产 SQL 中长期使用
FORCE INDEX 是诊断工具：确认索引确实更快后，应通过 ANALYZE TABLE 或 Hint 优化，而非硬编码 FORCE INDEX。
-- 诊断用 SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1;
三、Schema 类（51-70）
26
用 TINYINT(1) 存布尔值，不要用 CHAR(1) 或 VARCHAR
TINYINT 1字节，范围 0-255，性能最好。CHAR/VARCHAR 浪费空间，字符串比较慢。
ALTER TABLE users ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1;
27
金钱用 DECIMAL(精度, 2)，绝不用 FLOAT/DOUBLE
FLOAT/DOUBLE 有浮点精度问题，0.1+0.2 ≠ 0.3。金融计算必须用 DECIMAL 或整数（存分/厘）。
-- 存储到分（整数，最快）：amount INT, 单位是分 -- 存储精确小数：amount DECIMAL(12, 2)
28
DATETIME vs TIMESTAMP：跨时区系统用 DATETIME
TIMESTAMP 自动转换时区，范围到 2038 年。DATETIME 存原始值，不受时区影响，范围到 9999 年。全球化系统统一 UTC，用 DATETIME。
-- 新系统推荐 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
29
NOT NULL 列比 NULL 列索引更紧凑
NULL 值在索引中需要额外 1 字节标记。尽量将业务逻辑不允许 NULL 的列定义为 NOT NULL DEFAULT ''（字符串）或 NOT NULL DEFAULT 0（数字）。
-- 查找可以改为 NOT NULL 的列 SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='mydb' AND IS_NULLABLE='YES';
30
枚举值用 TINYINT 而非 ENUM 类型
MySQL ENUM 修改枚举值需要 ALTER TABLE，高成本。用 TINYINT + 应用层映射更灵活，ALTER 代价低。
-- 用 TINYINT：1=pending, 2=paid, 3=shipped, 4=completed status TINYINT NOT NULL DEFAULT 1 COMMENT '1=待付款 2=已付款 3=已发货 4=已完成'
四、配置类（71-85）
31
innodb_buffer_pool_size 设为物理内存的 70-80%（专用 DB 服务器）
Buffer Pool 是 InnoDB 最重要的内存配置，命中率低于 99% 时应优先增大。
-- 查看当前命中率 SELECT ROUND(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') ), 4) AS hit_ratio;
32
innodb_flush_log_at_trx_commit=1 确保不丢数据（生产必须）
0=每秒刷一次（可能丢 1 秒数据），1=每次提交 fsync（最安全），2=OS 缓冲（OS crash 丢数据）。
-- 验证 SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- 应为 1
33
max_connections 不要设太大，连接池才是正确解法
每个连接消耗约 1MB 内存，大量空闲连接浪费资源，上下文切换也有代价。应通过连接池（HikariCP/Druid）控制实际连接数。
SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数 SHOW STATUS LIKE 'Max_used_connections'; -- 历史峰值
34
开启慢查询日志（生产必须）
慢查询日志是发现性能问题的第一手资料，设 long_query_time=1（秒），每天用 pt-query-digest 分析 Top-N。
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON; SHOW VARIABLES LIKE 'slow_query_log_file';
35
tmp_table_size 和 max_heap_table_size 设为相同值
ORDER BY/GROUP BY 产生的内存临时表大小受这两个参数中较小值限制。超过后溢出磁盘（Using temporary + filesort）。
SET GLOBAL tmp_table_size = 6410241024; -- 64MB SET GLOBAL max_heap_table_size = 6410241024; -- 需相同
五、运维类（86-100）
36
定期用 pt-query-digest 分析慢查询日志
按 Query_time_sum 排序 Top-N，优先优化总耗时最多的查询（不一定是单次最慢的）。
pt-query-digest /var/log/mysql/slow.log
--limit=20
--order-by Query_time:sum > report.txt
37
SHOW PROCESSLIST 快速查看当前正在执行什么
CPU 高时第一反应，找 State 为 "executing"/"Sorting result"/"Writing to net" 的长时间查询，KILL 前先 EXPLAIN。
SHOW FULL PROCESSLIST; -- 或 SELECT * FROM information_schema.PROCESSLIST WHERE Time > 5 ORDER BY Time DESC;
38
死锁排查看 INNODB STATUS 的最后一次死锁记录
SHOW ENGINE INNODB STATUS 包含最近一次死锁的完整事务信息，加锁顺序一目了然。
SHOW ENGINE INNODB STATUS\G -- 找 LATEST DETECTED DEADLOCK 节
39
用 performance_schema.data_locks 查当前持锁情况（MySQL 8.0）
比老的 information_schema.INNODB_LOCKS 更详细，包含锁类型、锁定的 key 值。
SELECT * FROM performance_schema.data_locks\G SELECT * FROM performance_schema.data_lock_waits\G
40
定期检查主从延迟，及时发现复制问题
延迟超过阈值（通常 30s）需要告警。Seconds_Behind_Master=NULL 表示复制已断。
SHOW SLAVE STATUS\G -- 看 Seconds_Behind_Master -- 或 Percona PMM 监控面板实时可视化