MySQL 硬核解析专题 - 第三篇：性能优化的硬核技巧

前两章我们聊了 MySQL 的架构和 SQL 执行流程，现在到了“提速”的关键时刻。无论是单表查询慢得像乌龟爬，还是高并发下数据库喘不过气，性能优化都能救你于水火。这一篇，我会带你剖析 MySQL 的性能瓶颈，并给出硬核的优化招数，帮你把数据库调成“跑车”。

索引是 MySQL 提速的头号功臣，但用不好也可能是“坑”。我们从原理到实战，拆解几个硬核技巧。

covering index（覆盖索引）：不回表的神器
- 原理：查询所需的所有字段都在索引里，存储引擎不用“回表”查数据，省一次 IO。
- 例子：SELECT name FROM users WHERE age = 20;
  - 如果只有 age 的单列索引，查到 age=20 的行后，还得回表取 name。
  - 如果建个 (age, name) 的联合索引，索引里就有 name，直接返回，效率翻倍。
- 硬核点：用 EXPLAIN，看 Extra 列有 Using index 就说明命中覆盖索引。
index condition pushdown（ICP，索引条件下推）：过滤提速
- 原理：MySQL 5.6 引入，存储引擎在索引层就过滤掉不符合条件的数据，减少回表次数。
- 例子：SELECT * FROM users WHERE age > 18 AND name LIKE 'T%';
  - 没 ICP：用 age 索引查出所有 age > 18 的行，回表后再过滤 name。
  - 有 ICP：索引层先过滤 name LIKE 'T%'，再回表，数据量更少。
- 硬核点：EXPLAIN 的 Extra 列有 Using index condition 表示启用 ICP，默认开（optimizer_switch 里 index_condition_pushdown=on）。
索引设计原则
- 高选择性字段优先：比如 user_id 比 gender 更适合建索引，因为区分度高。
- 前缀索引：对长字符串（如 URL），用 CREATE INDEX idx_url ON table(url(10)); 只索引前 10 个字符，省空间。
- 避免冗余：(a, b) 的联合索引已经包含 a，别再单独建 a 的索引。

MySQL 的性能不只靠 SQL，还得靠配置“榨干”硬件资源。以下是几个关键参数的硬核调整。

innodb_buffer_pool_size：内存的命脉
- 作用：缓冲池缓存数据和索引，越大越能减少磁盘 IO。
- 调优：建议占物理内存的 60%-80%，比如 16GB 内存的机器，设成 10-12GB。
- 硬核点：用 SHOW STATUS LIKE 'Innodb_buffer_pool%'; 看命中率，Innodb_buffer_pool_reads（磁盘读）越少越好。
innodb_log_file_size：事务的加速器
- 作用：控制 redo log 文件大小，写密集场景下太小会导致频繁 checkpoint，拖慢性能。
- 调优：建议 128MB-512MB，视写负载调整，但别超缓冲池的 1/4。
- 硬核点：改之前备份，调整后重启生效，检查 SHOW VARIABLES LIKE 'innodb_log_file_size';。
tmp_table_size & max_heap_table_size：临时表优化
- 作用：控制内存临时表大小，GROUP BY 或 ORDER BY 时用得上。
- 调优：默认 16MB，太小会导致临时表落盘，建议调到 64MB 或更高。
- 硬核点：用 SHOW STATUS LIKE 'Created_tmp_disk_tables'; 看磁盘临时表数量，多了就得加内存。

慢查询是性能杀手，怎么揪出来并干掉它？

开启慢查询日志

命令：

1
2
3
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过 1 秒记为慢查询
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录没用索引的查询

日志位置：默认在 slow_query_log_file 里，可用 SHOW VARIABLES LIKE 'slow_query_log_file'; 查看。

分析工具
- mysqldumpslow：自带工具，汇总慢查询并排序。
  - 用法：mysqldumpslow -t 10 /path/to/slow.log（列出 Top 10）。
- pt-query-digest：Percona Toolkit 的神器，生成详细报告。
  - 用法：pt-query-digest /path/to/slow.log > report.txt。
- 硬核点：关注 Rows_examined（扫描行数）和 Query_time，找高频低效的 SQL。
优化实例
- 慢 SQL：SELECT * FROM orders WHERE order_date > '2024-01-01';
- 分析：EXPLAIN 显示 type=ALL，全表扫描。
- 优化：加索引 CREATE INDEX idx_date ON orders(order_date);，再跑 EXPLAIN，type 变 range，速度起飞。

连接池优化
- 参数：max_connections 默认 151，高并发下调到 500-1000。
- 硬核点：用 SHOW STATUS LIKE 'Threads_connected'; 监控，满了就报 Too many connections。
锁粒度调整
- 问题：写多时表锁拖慢速度。
- 解决：InnoDB 默认行锁，但大事务可能升级为表锁。缩短事务，或者用 SELECT ... FOR UPDATE 显式锁行。

场景：一张 5000 万行的 logs 表，查询 SELECT * FROM logs WHERE log_time > '2025-01-01' LIMIT 10; 慢得像蜗牛。
- 分析：EXPLAIN 显示全表扫描，rows 高达 5000 万。
- 优化：
  1. 加索引：CREATE INDEX idx_time ON logs(log_time);
  2. 改 SQL：SELECT id, log_time FROM logs WHERE log_time > '2025-01-01' LIMIT 10;（覆盖索引）。
  - 结果：查询从 10 秒降到 0.1 秒。
场景：高并发下数据库 CPU 100%。
- 分析：SHOW PROCESSLIST; 发现大量慢查询。
- 优化：调大 innodb_buffer_pool_size，加索引，缩短事务，CPU 降到 30%。

这一篇我们从索引、配置到慢查询，掏出了一套性能优化的硬核组合拳。掌握这些，你就能让 MySQL 在各种场景下跑得又快又稳。下一章我们会聊 高可用与分布式，看看 MySQL 怎么扛住大规模流量。

相关文章：