企业官网建设流程全解析

MySQL性能优化这件事，我做了快十年，踩过的坑比很多人见过的表都多。坦白讲，大部分性能问题根本轮不到“调参”和“换硬件”，十有八九是慢查询和表结构设计挖的坑。这篇指南我按“20个核心技巧”为主线，把索引、SQL写法、表结构、事务锁、配置、监控一条龙讲透，每一条都附上我实际验证过的方案和排查思路。不管你是刚接手一个慢到怀疑人生的老系统，还是想在开发阶段就把隐患摁死，这篇都值得你花半小时读完——读完直接照着做，效果立竿见影。

1. 先把思路捋清楚：性能优化不是“调参数”，而是“系统作战”

很多人一谈MySQL性能优化，第一反应就是改innodb_buffer_pool_size，或者纠结max_connections该设多少。我见过最离谱的一次，有人把buffer_pool调到机器物理内存的90%，结果直接OOM，整个业务挂了半小时。调参是最容易的，也是最不该先做的。

真正的性能优化是一个排查链路：先从慢查询日志里找到最耗时的SQL，再用EXPLAIN看执行计划，发现问题往往集中在三处——索引没走对、查询写了太多无用功、锁竞争太严重。这三处解决掉，性能至少提升一个数量级。之后再谈配置、谈硬件，才有意义。

我一般把优化分成五个层次，按优先级排：

这个顺序很重要。先做前三层，再动配置和硬件，否则你花大价钱升级了机器，慢查询还是慢查询，只是从“特别慢”变成“稍微慢”。

这篇文章的20个技巧，我就按这个优先级来排，每一层都给出可直接复制的操作。

2. 20个核心技巧逐条拆解：从SQL到架构的完整打法

2.1 索引设计：从“随便建”到“按需建”（技巧1-3）

技巧1：优先为WHERE、JOIN、ORDER BY的列建索引

这条看似基础，但执行不到位的人特别多。我见过生产库里有几十个索引，但核心查询的WHERE条件列一个索引都没有——因为建索引的人只给主键和唯一键建了。判断标准很简单：打开慢查询日志，找出那些rows_examined特别大的SQL，WHERE条件里的列、JOIN的连接列、ORDER BY排序的列，这三类就是索引的“第一优先级”。

技巧2：联合索引要遵循“最左前缀”原则

联合索引是新手最容易犯迷糊的地方。(a, b, c)联合索引，实际上等于建了(a)、(a, b)、(a, b, c)三个索引。所以查询里如果只用了b列或者c列，索引就用不上。我常用的一个口诀：联合索引的列顺序，按“等值条件列优先、范围条件列其次、排序列最后”来排。

注意：这条口诀不是绝对的。如果某个列是范围查询（比如>、<、BETWEEN），把它放在前面会导致后面的列索引失效。例如(a, b)，查询WHERE a > 100 AND b = 1，b的等值条件用不上索引。遇到这种，把等值条件列放前面更稳。

技巧3：覆盖索引能“让数据在索引里直接查到”

覆盖索引是我个人最推荐的一项优化。它指的是：查询的SELECT列、WHERE列、ORDER BY列全部包含在同一个索引内，这样MySQL引擎只需要扫索引页，不需要回表查聚簇索引。举个例子，表里有id, user_id, order_amount三列，查询SELECT user_id, order_amount FROM orders WHERE id = 123，如果建个(id, user_id, order_amount)的索引，这条查询全程不碰数据行。

实测下来，覆盖索引能把查询耗时从几十毫秒压到几毫秒，尤其在数据量千万级的时候。怎么判断是否命中了覆盖索引？EXPLAIN输出里Extra列显示Using index，就是命中了。

2.2 查询语句写法：同样的结果，不同的代价（技巧4-6）

技巧4：只查需要的字段，不要无脑SELECT *

这条我强调过无数次。SELECT *会把所有列的数据从存储引擎捞出来，传到Server层，再做过滤——哪怕你只关心其中一列。数据行宽了，回表成本、网络传输成本、排序临时文件体积全都会变大。有人反驳说“我们表就5个字段，无所谓”，等表扩到30个字段、数据量到千万级，你就知道SELECT *有多疼了。只列出你真正用到的字段，是零成本的优化。

技巧5：分页查询别用LIMIT 1000000, 20

大分页是经典性能杀手。LIMIT 1000000, 20意味着MySQL要扫描前1000020行，然后丢弃前1000000行，代价极高。我常用的替代方案有两个：

• 延迟关联：先只查出主键ID，再用主键JOIN回原表取数据。
• 基于游标的分页：记住上一页最后一条ID，用WHERE id > 上一个ID ORDER BY id LIMIT 20。

第二种方案最彻底，但要求排序字段唯一且有序；第一种方案通用性更强。实际项目中我用延迟关联把一次5秒的深分页查询降到了200毫秒。

技巧6：用EXPLAIN看执行计划，慢之前就发现问题

我要求团队里任何人写SQL之前，必须先把EXPLAIN跑一遍。重点看四个字段：type（最好到ref或range，ALL是全表扫描要警惕）、key（实际用到的索引）、rows（预估扫描行数）、Extra（有没有Using filesort、Using temporary）。一旦看到Using filesort或Using temporary，就要立刻警觉：这条SQL在排序或者建临时表，数据量一大必慢。

实操心得：rows是预估数，不一定准，但rows和实际慢查询日志里的Rows_examined差距太大的时候，说明统计信息过旧，跑一次ANALYZE TABLE刷新统计信息往往能解决。

2.3 表结构设计：字段选错了，后面全是债（技巧7-9）

技巧7：字段类型宁小勿大，能定长尽量定长

很多表设计者习惯性地上来就VARCHAR(255)，主键一律BIGINT，时间字段全部DATETIME。实际上：

• 状态、枚举、小小的数字用TINYINT就行，别用INT。
• IP地址用INT UNSIGNED存，配合INET_ATON/INET_NTOA转换，比VARCHAR(45)省不少空间。
• 时间字段能用TIMESTAMP就别用DATETIME——前者4字节，后者8字节，同样的索引，前者能存更多key page，扫描更快。

技巧8：避免NULL列的滥用

理论上MySQL对NULL有处理逻辑，索引对NULL的处理也有限制。我见过有人设计表时，几乎所有字段都允许NULL，结果每个查询都得额外判断。能用NOT NULL默认值的，就设上默认值（比如状态默认0、时间默认CURRENT_TIMESTAMP）。这能减少存储层的判断，也能避免WHERE col IS NULL走不好索引的问题。

技巧9：不要过度拆分表，也不要单表无限膨胀

很多人一听性能优化就想着“分库分表”。分库分表是最后的手段，它带来的分布式事务、跨表JOIN查询、全局唯一ID问题，任何一个都比性能问题更难处理。单表数据量在千万级以下、索引合理的情况下，MySQL完全扛得住。真到需要分的时候，优先考虑按时间归档历史数据、用分区表，都比直接上中间件稳妥。

2.4 让MySQL“少干活”：缓存、排序与聚合（技巧10-13）

技巧10：排序别让数据库硬扛，能走索引就走索引

ORDER BY的列如果不在索引里，MySQL就得把数据放到内存或磁盘做filesort。数据量小没事，数据量大就直接让CPU和IO飙高。解决思路：给排序列建索引，或者减少排序的数据集（先过滤再排序）。

技巧11：GROUP BY和DISTINCT要看清“去重”逻辑

热搜词里有“mysql的or能去重吗”，这个问题其实就是DISTINCT和GROUP BY的区别：DISTINCT是对查询结果去重，GROUP BY是分组后再聚合。它们在执行计划里都可能产生临时表。优化方式：给GROUP BY的列建索引，或者用GROUP BY替代DISTINCT时注意索引匹配情况。另外，UNION默认自带去重效果，但会有排序去重的开销；如果你业务上能接受重复数据，用UNION ALL替代UNION，性能提升极其显著。

技巧12：大结果集聚合，考虑拆分批次

比如统计一张千万级订单表的月销售额，一个SUM下去可能扫描全表。我的做法是：如果业务对实时性要求不高，就建立汇总表，按小时/天定时增量统计；如果必须实时，就缩小扫描范围（比如只扫描当天的分区），并配合覆盖索引。

技巧13：再利用OPTIMIZER_TRACE分析“优化器没选对索引”

有的SQL明明有索引，执行计划就是不走。我遇到过很多次，是因为统计信息不准或者条件里写了函数导致索引失效。排查这类问题的利器是OPTIMIZER_TRACE：

SET optimizer_trace='enabled=on'; -- 执行你的查询 SELECT * FROM information_schema.OPTIMIZER_TRACE;

它能显示优化器为什么会选择某个执行计划，以及为什么放弃了某个索引。这个信息在常规的EXPLAIN里是看不到的。

2.5 事务、锁与并发控制（技巧14-16）

技巧14：事务要短平快，别把业务逻辑塞进事务里

我踩过一个特别典型的坑：代码里一个大事务，包含几万行数据的更新，还调了外部接口，接口超时3秒，事务就开了3秒以上，直接导致连接池耗尽，整个服务雪崩。事务的原则是：能拆短就拆短，只把必须原子化的操作放进去。长事务会持有锁不释放，阻塞其他事务，还让undo log无限膨胀。

技巧15：合理使用索引减少锁范围

InnoDB的行锁是建立在索引上的。如果你的WHERE条件没有索引，MySQL会走全表扫描，把所有匹配的行都锁住——实际上因为扫描全表，几乎相当于锁了全表。我处理过一个死锁案例，根因就是DELETE语句的WHERE条件列没索引，导致间隙锁范围扩大，两个事务互相锁等待。给筛选列加索引，是降低锁竞争最直接的手段。

技巧16：了解锁分类，死锁了才知道往哪查

MySQL的锁大致分为：表锁、行锁、间隙锁、意向锁。间隙锁是RR隔离级别下防治幻读用的，但它也是死锁的头号源头。排查死锁的固定流程：

SHOW ENGINE INNODB STATUS;

看输出的LATEST DETECTED DEADLOCK段，里面会记录两个事务各自的SQL和持有/等待的锁。我把这个输出取关键字“lock_mode”、“waiting”过一遍，基本能定位到是哪两条SQL互相打架。日常预防死锁的方法：多个事务访问同一组表的时候，按相同顺序操作；更新数据尽量走主键或唯一索引。

2.6 配置与架构层面的兜底优化（技巧17-20）

技巧17：innodb_buffer_pool_size是内存里最值钱的一分钱

这个参数决定InnoDB缓存表数据和索引的内存大小。建议设为机器物理内存的50%-70%，但要留出足够余量给操作系统和连接线程。设置完用下面的SQL验证命中率：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

计算(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests，如果命中率低于95%，说明缓存池太小，或者数据访问太分散。

技巧18：慢查询日志必须常开，阈值设在1秒以内

很多人在生产上把long_query_time设成5秒、10秒，等于把慢查询日志当摆设。我建议开发环境设成0.1秒，生产也至少设1秒，这样你能提前感知到索引失效、数据量膨胀等趋势。开启方法：

slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ON

log_queries_not_using_indexes是我特别推荐的，它会把所有没走索引的查询全部记录下来，哪怕查询本身很快——这些才是潜在的地雷。

技巧19：连接数不是越大越好，连接池要配合

把max_connections从默认151调到1000，看起来很豪横，实际上每条连接都占用内存和线程资源，连接数一上来CPU就飙了。真正要做的是：应用层连接池控制活跃连接数（比如HikariCP默认10个就够），数据库端max_connections只作为兜底上限。我见过一个诡异的问题：MySQL CPU不高，但应用响应极慢，最后发现是连接池配置了200个，全挤在SLEEP状态，把线程调度拖垮了。

技巧20：从EXPLAIN到PROFILING，用数据说话

遇到难缠的慢查询，我会开PROFILING看每个阶段的耗时占比：

SET profiling = 1; -- 执行慢SQL SHOW PROFILES; SHOW PROFILE FOR QUERY 1;

输出里能看到Sending data、Sorting result、Creating tmp table各花了多少时间。比如我看到Sorting result占比70%，就知道排序是瓶颈，优先给排序列加索引；看到Sending data占比高，则考虑是不是扫描行数太多、需要优化WHERE条件。

3. 实战排查：一次慢查询的完整“缉凶”过程

3.1 从慢查询日志里找“嫌疑犯”

我最近处理的一个项目，业务方反馈“报表查询越来越慢”，慢查询日志里一眼就看到这条：

SELECT a.id, a.order_no, b.user_name, SUM(a.amount) FROM orders a LEFT JOIN users b ON a.user_id = b.id WHERE a.created_at BETWEEN '2024-06-01' AND '2024-06-30' GROUP BY a.user_id ORDER BY SUM(a.amount) DESC LIMIT 50;

日志显示扫描了800万行，耗时11秒。这是个很典型的问题集：大表JOIN、范围查询、GROUP BY聚合、ORDER BY排序，全齐了。

3.2 用EXPLAIN锁定问题点

跑一遍EXPLAIN，结果是这样的关键信息：

两个信号非常明确：ALL说明没用上索引，Using temporary; Using filesort说明聚合和排序都在磁盘临时表里完成。

3.3 一步步修复，每步都验证效果

第一步：给created_at加索引

ALTER TABLE orders ADD INDEX idx_created_at (created_at);

但EXPLAIN依然显示rows很大——因为范围查询还是扫了全月的数据，有600万行，查询降到6秒。这不够。

第二步：改成覆盖索引

我把索引改成(created_at, user_id, amount)，让聚合需要的列都在索引里，避免回表。查询降到2.5秒。但Using filesort还在，因为排序的SUM(amount)是计算出来的，索引帮不上忙。

第三步：去掉不必要的LEFT JOIN

我发现user_name只是展示字段，不需要参与过滤。于是改写为先聚合订单，再关联用户：

SELECT t.id, t.order_no, u.user_name, t.total_amount FROM ( SELECT id, order_no, user_id, SUM(amount) AS total_amount FROM orders WHERE created_at BETWEEN '2024-06-01' AND '2024-06-30' GROUP BY user_id ORDER BY total_amount DESC LIMIT 50 ) t LEFT JOIN users u ON t.user_id = u.id;

子查询里只处理订单表，聚合完只剩少量结果，再JOIN用户表。这个版本跑到了300毫秒以内，提速接近40倍。

这个案例我复盘过很多次，核心教训是：不要一上来就调配置，先用EXPLAIN把SQL本身的问题揪出来。这个查询改完索引结构+SQL写法，再用慢查询日志复查，Rows_examined从800万降到60万，问题彻底解决。

4. 高频故障场景复盘：锁、索引失效、深分页

4.1 锁等待与死锁：一条UPDATE引发的“雪崩”

之前线上出现过一次大量“Lock wait timeout exceeded”报错。排查过程：先SHOW ENGINE INNODB STATUS看锁信息，发现两个事务都在争用同一张表的同一行。进一步看代码逻辑，A事务先UPDATE订单再UPDATE用户，B事务先UPDATE用户再UPDATE订单——两个事务的加锁顺序相反，死锁条件成立。

解决方案分两步：

• 应用层统一加锁顺序，所有事务都先操作用户表再操作订单表。
• 数据库层把隔离级别从REPEATABLE READ降到READ COMMITTED（如果业务允许），减少间隙锁的冲突概率。这能显著降低死锁发生频率，但要注意需要业务侧确认“不可重复读”的影响可控。

4.2 索引失效的四个“隐形杀手”

我归纳了日常最容易导致索引失效的四个写法，你们可以对照自查：

修复方式很简单：函数式写法改成范围条件（created_at >= '2024-06-01' AND created_at < '2024-06-02'）；字符串字段查询时带上引号；前导模糊查询考虑全文索引；OR的两边都能用索引时，优化器才会考虑走索引。

4.3 深分页：LIMIT 500000, 20的性能拐点

有个订单列表接口，用户翻到第100页就开始卡。SQL长这样：

SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 500000, 20;

这条查询要排序后扫描50万行，再丢弃。我改成延迟关联的写法：

SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 500000, 20 ) t ON o.id = t.id;

内层只查主键ID，走create_time索引，扫描的代价小很多；外层用主键回表取20行。实测接口从4秒降到300毫秒。

5. 常见问题速查表：照着排查就行

这张表我贴在公司项目组的wiki上，每次有人报“数据库慢”，先让对表自查，解决率很高。

6. 额外建议：把性能优化“前置”而不是“救火”

在我自己的项目里，加了三条强制规矩：第一，任何人提交SQL之前导出EXPLAIN贴到代码评审里；第二，所有新索引要说明是为哪条慢查询服务的，避免无效索引泛滥；第三，每张核心表每季度跑一次ANALYZE TABLE和慢日志复盘。这套机制比任何单次优化都管用，它让性能问题在开发阶段就被挡住。

最后分享一个小技巧：你在优化一个查询时，改一步、验证一步、记录一步，不要想着一次性把SQL、索引、配置全改完。我见过太多人一口气加了三个索引、改了两条SQL、调了四个参数，结果变快了都不知道是哪一步的功劳，回滚时更是灾难。性能优化的每一步都应该有数据支撑，用EXPLAIN的rows、用慢查询日志的耗时做前后对比，这比任何玄学都靠谱。