系列:MySQL 从基础到精深——Java SaaS 实战系列 · 番外篇
难度:⭐⭐⭐⭐☆ 适合有一定经验、希望系统提升的开发者
预计阅读时间:50 分钟
关键词:MySQL 性能优化、SQL 提速技巧、索引优化、查询改写、连接池调优、分页优化、SaaS 数据库提速、Java MySQL 实战
速览摘要
本篇是系列的实战提炼篇,从前十篇中萃取出 30 条有直接工程价值的优化技巧。不同于"最佳实践列表"式的罗列,每一条都包含三层结构:问题场景(这个坑在什么情况下会踩到)、根本原因(为什么会慢、为什么会出错)、解决方案(附可直接复用的 SQL 或 Java 代码)。30 条分为六个维度:索引优化(8 条)、SQL 改写(7 条)、表设计(5 条)、连接与事务(5 条)、架构层提速(3 条)、工具与诊断(2 条)。
第一维度:索引优化(8 条)
索引是 MySQL 性能优化里投入产出比最高的方向,大多数生产慢查询问题,最终都能追溯到索引设计或索引使用上的问题。这八条覆盖了最高发的索引失效场景和最值得掌握的索引技巧。
第 01 条:联合索引列顺序放错,等于索引白建
问题场景:建了联合索引,EXPLAIN 显示 key 有值(确实用了索引),但 key_len 比预期小,rows 估算很高,查询依然慢。
联合索引有最左前缀原则——B+树是按索引列的先后顺序排序的,跳过前面的列就无法利用后面列的有序性。但很多开发者以为"只要 WHERE 里用到了这些列就能走索引",忽略了列的顺序是影响索引效率的关键变量。
一个经常被忽视的细节是:等值查询的列要排在范围查询的列前面。因为范围查询之后,后续列的索引就用不上了(范围扫描的结果集里,后续列不是全局有序的)。
— 场景:查询某租户、某状态、某时间范围内的订单
— 错误的索引列顺序(created_at 是范围条件,放中间会让 status 失效)
CREATE INDEX idx_wrong ON orders(tenant_id, created_at, status);
— 执行以下查询时,status 的过滤无法用到索引
SELECT * FROM orders
WHERE tenant_id = 1001
AND status = 1
AND created_at >= '2024-01-01';
— key_len 只覆盖 tenant_id + created_at,status 退化为 Using where 过滤
— 正确的索引列顺序:等值条件列(tenant_id、status)放前面,范围条件列(created_at)放后面
CREATE INDEX idx_correct ON orders(tenant_id, status, created_at);
— 现在三列都能被索引利用,key_len 覆盖全部三列
调整顺序之后,还需要用 EXPLAIN 验证 key_len 是否符合预期。每次建联合索引,都应该把目标查询的 EXPLAIN 跑一遍,确认索引被"完整使用"而不是"部分使用"。
第 02 条:隐式类型转换,索引失效且功能上完全正常
问题场景:慢查询日志出现了一条按手机号查用户的 SQL,EXPLAIN 显示全表扫描,但 phone 列明明有索引。
这是线上最隐蔽的索引失效场景之一——功能完全正确(查询结果对),性能却极差,而且在测试数据量小时根本发现不了。
根本原因:phone 列定义为 VARCHAR(20),但 Java 代码传入了数值类型(如 Long)。MySQL 在比较时会把 VARCHAR 列的每个值转换为数值后再比较,等同于对 phone 列做了隐式函数调用,B+树索引完全失效。
// 反例:Long 类型的手机号传给 VARCHAR 列查询
// MyBatis 参数:@Param("phone") Long phone ← 类型错了
// 生成的 SQL:WHERE phone = 13800138000 (数值,不带引号)
// MySQL 对 phone 列每行做类型转换后比较 → 全表扫描
// 正例:始终用 String 类型传手机号
// MyBatis 参数:@Param("phone") String phone
// 生成的 SQL:WHERE phone = '13800138000' (字符串,带引号)
// 直接走索引 → 毫秒级响应
防范方法:在 DTO 和 Entity 中,凡是数据库字段类型是 VARCHAR 的,Java 侧属性类型也应该是 String,不要用数值类型存"本质上是字符串"的数据(手机号、身份证号、订单号等)。
第 03 条:对索引列用函数,B+树的有序性被破坏
问题场景:按月份统计订单数量,SQL 里写了 WHERE MONTH(created_at) = 6,全表扫描,慢到超时。
原因是 B+树索引按原始列值排序,对列应用 MONTH()、YEAR()、DATE()、LOWER() 等函数后,排序关系不再成立,MySQL 无法从 B+树中快速定位目标行。
改写方法是把函数移到等号右边,让左边的列保持"裸列"形态:
— 反例:对列用函数,索引失效
SELECT COUNT(*) FROM orders WHERE MONTH(created_at) = 6;
SELECT * FROM user WHERE LOWER(email) = 'user@example.com';
— 正例改写一:把函数逻辑转移到条件右侧(用范围条件替代函数)
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-06-01'
AND created_at < '2024-07-01';
— 正例改写二:MySQL 8.0 函数索引(当 SQL 无法改写时,对表达式建索引)
ALTER TABLE user
ADD UNIQUE INDEX uk_lower_email ((LOWER(email)));
— 此后以下查询可以走索引:
SELECT * FROM user WHERE LOWER(email) = LOWER('User@Example.com');
函数索引是 MySQL 8.0 才有的特性,如果还在 5.7,只能通过改写 SQL 解决。如果查询来自第三方系统无法改写,可以考虑添加一个存储 LOWER(email) 结果的虚拟生成列,并对该列建索引,效果等同于函数索引。
第 04 条:覆盖索引,消除回表开销
问题场景:某个分页查询 EXPLAIN 显示 type=ref(走了索引),但查询仍然比预期慢,Extra 列没有 Using index。
原因是查询虽然用了索引定位到了目标行,但索引里没有 SELECT 需要的所有列,因此必须对每一条匹配的行做"回表"——用主键再去聚簇索引取完整行数据。每次回表是一次额外的 B+树查找,结果集有 1000 行就是 1000 次回表。
解法是建覆盖索引——把 SELECT 中需要的列也加入索引,让 MySQL 直接从索引中获取所有数据,跳过回表:
— 场景:分页展示订单列表,只需要 id、order_no、status、total_amount、created_at 五列
— 当前索引:(tenant_id, status)
— 查询 SELECT id, order_no, status, total_amount, created_at 时需要回表取 order_no、total_amount
SELECT id, order_no, status, total_amount, created_at
FROM orders
WHERE tenant_id = 1001 AND status = 1
ORDER BY created_at DESC LIMIT 20;
— EXPLAIN Extra: NULL(需要回表)
— 覆盖索引:把 SELECT 里的列也加进来
CREATE INDEX idx_covering
ON orders(tenant_id, status, created_at, order_no, total_amount);
— 现在 SELECT 的所有列都在索引里,不需要回表
— EXPLAIN Extra: Using index(覆盖索引命中)
覆盖索引会让索引文件更大,写入时维护成本更高,所以只对"高频、对延迟敏感"的核心查询建覆盖索引,不要给所有查询都建。
第 05 条:OR 条件中有一列没有索引,整个条件退化为全表扫描
问题场景:一个搜索接口,条件是 WHERE order_no = ? OR remark LIKE ?,order_no 有索引,remark 没有,查询极慢。
OR 的语义是"满足任意一个条件的行都要返回",当其中一个条件无法用索引快速定位时,MySQL 必须扫描全表才能保证不遗漏任何行,于是索引完全用不上。
有两种修复方式:给 remark 也加索引(如果查询足够频繁),或者把 OR 改写为 UNION(让两个条件分别走各自的索引,结果合并去重):
— 反例:remark 无索引,OR 导致全表扫描
SELECT * FROM orders
WHERE tenant_id = 1001
AND (order_no = 'ORD-2024-001' OR remark LIKE '%重要%');
— 正例改写:UNION 让两个条件分别走各自索引
SELECT * FROM orders WHERE tenant_id = 1001 AND order_no = 'ORD-2024-001'
UNION
SELECT * FROM orders WHERE tenant_id = 1001 AND remark LIKE '%重要%';
— 两个子查询分别走 order_no 的索引和 remark 的前缀索引(如果有)
— 注意:UNION 自带去重(UNION ALL 不去重,性能更好,但需要业务上保证无重复)
如果 remark 需要做关键词搜索(任意位置匹配),前缀索引也用不上,这时应该考虑 MySQL 全文索引(FULLTEXT)或接入 Elasticsearch,而不是用 LIKE '%关键词%' 打穿整张表。
第 06 条:前缀索引节省空间,但要权衡区分度
问题场景:email 列是 VARCHAR(255),建唯一索引后,索引文件很大,插入性能下降明显。
对于长字符串列(URL、邮箱、描述),建全值索引会让索引文件很大,消耗大量 Buffer Pool 空间。前缀索引只取列值的前 N 个字符建索引,体积小得多,但必须选择合适的前缀长度——太短则区分度不够(不同值有相同前缀,索引命中后还需要回表过滤),太长则和全值索引没什么区别:
— 计算不同前缀长度下的区分度(越接近 1.0 越好)
SELECT
COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS prefix_8,
COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
COUNT(DISTINCT email) / COUNT(*) AS full_column
FROM user
WHERE tenant_id = 1001;
— 如果 prefix_8 已经接近 full_column,说明取前 8 个字符就足够了
— 建前缀索引
CREATE INDEX idx_email_prefix ON user(email(8));
前缀索引有一个重要限制:不能用于覆盖索引。即使 SELECT 的列都在索引里,MySQL 也无法确认前缀匹配是否等价于全值匹配,必须回表验证。所以前缀索引更适合"降低索引体积、提升 Buffer Pool 命中率"的场景,不追求覆盖索引效果。
第 07 条:不可见索引,让删索引有了后悔药
问题场景:某个索引看起来长时间没被使用,想删掉节省写入开销,但又担心有遗漏的查询依赖它,直接删了回滚成本很高。
MySQL 8.0 的不可见索引(Invisible Index)完美解决这个问题:先把索引设为不可见,优化器不再使用它,但索引本身继续维护。观察一到两周,确认没有查询性能下降后,再正式删除:
— Step 1:设为不可见,观察效果(不会影响现有查询的语法,只影响优化器选择)
ALTER TABLE orders ALTER INDEX idx_old_remark INVISIBLE;
— Step 2:观察期间,用 sys schema 确认没有查询在使用它
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'saas_demo'
AND index_name = 'idx_old_remark';
— Step 3:如果发现有查询变慢了(说明确实有依赖),立刻改回可见
ALTER TABLE orders ALTER INDEX idx_old_remark VISIBLE;
— Step 4:观察期安全通过,正式删除
ALTER TABLE orders DROP INDEX idx_old_remark;
在 5.7 时代,删索引之前只能靠 sys.schema_unused_indexes 判断,但这个视图的数据来源是 Performance Schema 的采集窗口,如果某个查询是月末才跑的报表,平时根本看不到它。8.0 的不可见索引给了"先让优化器看不见它、观察真实影响"的机会,是一个实质性的运维安全改进。
第 08 条:索引合并(Index Merge)是双刃剑
问题场景:EXPLAIN 的 type 显示 index_merge,Extra 显示 Using union(idx_a, idx_b),查询比预期慢,但看起来"用了两个索引"。
索引合并是 MySQL 优化器的一种策略:分别用两个索引扫描,然后对结果做交集(AND 条件)或并集(OR 条件),再回表。听起来很聪明,但实际上这往往意味着索引设计不够合理——两次索引扫描 + 合并操作,通常比一个设计良好的联合索引更慢。
遇到 index_merge 时,首先分析这两个索引列能否合并成一个联合索引,通常这是更好的解法:
— 出现 index_merge 的场景
— EXPLAIN Extra: Using union(idx_status, idx_tenant_id)
SELECT * FROM orders WHERE status = 1 OR tenant_id = 1001;
— 这里的 OR 让 MySQL 用了两个索引,对结果取并集
— 这种情况下,改写为 UNION 通常更高效(可控、可预测)
SELECT * FROM orders WHERE status = 1
UNION ALL
SELECT * FROM orders WHERE tenant_id = 1001 AND status != 1;
— 注意:UNION ALL 后面的条件要排除第一个子查询已经覆盖的行,避免重复
— 如果是 AND 条件触发了 index_merge,考虑建联合索引
— 例:WHERE tenant_id = 1001 AND status = 1
— 如果出现 index_merge(idx_tenant_id, idx_status),应该建 (tenant_id, status) 联合索引
当然,也有 index_merge 合理的情况(确实没有更好的索引可以建),这时不需要强行改写,因为它确实比全表扫描好。判断标准是:如果 rows 和 filtered 乘积比全表扫描小很多,说明索引合并是有价值的。
第二维度:SQL 改写(7 条)
索引是地基,SQL 是在地基上的建筑。即使索引设计完美,写出了低效 SQL 一样慢。这七条覆盖了最高发的 SQL 写法问题,每条都是从生产慢查询日志里反复出现的模式中提炼的。
第 09 条:SELECT * 的三重代价
问题场景:代码里的 SELECT * 慢慢变成了习惯,数据库表加了新列,查询返回的字段越来越多,接口越来越慢,但原因定位不到。
SELECT * 有三重代价,每一重都是性能杀手。第一重是网络传输:把不需要的列也传到应用层,浪费带宽和序列化时间。第二重是阻断覆盖索引:SELECT 里有任何一列不在索引中,就必须回表,数据量大时回表开销显著。第三重是破坏 Buffer Pool 效率:读取了大量不需要的列数据,把有限的 Buffer Pool 空间占满,降低真正需要数据的命中率。
// 反例:JPA 接口返回完整 Entity(SELECT *)
List<Order> orders = orderRepository.findByTenantIdAndStatus(tenantId, 1);
// 如果 Order 有 20 个字段,但页面只展示 5 个,剩下 15 个字段的传输全是浪费
// 正例:定义 Projection 接口,只查需要的列
public interface OrderListProjection {
Long getId();
String getOrderNo();
BigDecimal getTotalAmount();
Integer getStatus();
LocalDateTime getCreatedAt();
}
List<OrderListProjection> orders = orderRepository.findProjectedByTenantIdAndStatus(
tenantId, 1, OrderListProjection.class);
// 生成的 SQL:SELECT id, order_no, total_amount, status, created_at FROM orders WHERE…
// 只传输需要的 5 列,配合覆盖索引效果更佳
养成"SELECT 具体列"的习惯,是一个低成本、高回报的编码规范,建议写入团队的 Code Review Checklist。
第 10 条:大偏移量分页是隐形炸弹
问题场景:列表页刚上线时分页很快,随着数据增长,翻到后几百页时接口开始超时。
LIMIT offset, size 的执行原理是:扫描前 offset + size 行,丢弃前 offset 行,返回最后 size 行。偏移量是 10 万时,MySQL 必须扫描 10 万零 20 行,然后丢掉 10 万行只返回 20 行——这是纯粹的计算浪费。
对于无限滚动加载(App 下拉、数据导出)场景,游标分页是正确方案;对于需要跳页的后台管理界面,可以用"延迟关联"技巧,先用覆盖索引找到目标主键,再回表取完整数据:
— 反例:大偏移量扫描大量数据再丢弃
SELECT * FROM orders WHERE tenant_id = 1001 ORDER BY id DESC LIMIT 100000, 20;
— 正例一:游标分页(适合只有上下翻页的场景)
— 把"上一页最后一条的 id"传入,直接从那里开始
SELECT * FROM orders
WHERE tenant_id = 1001 AND id < :lastId — lastId 是上一页最后一条的主键
ORDER BY id DESC LIMIT 20;
— 无论数据量多大,始终只扫描 20 行,性能恒定
— 正例二:延迟关联(适合需要跳页但仍要用 OFFSET 的场景)
— 先用覆盖索引只取主键(极快),再回表取完整数据(只回表 20 次)
SELECT o.*
FROM orders o
JOIN (
SELECT id FROM orders
WHERE tenant_id = 1001
ORDER BY id DESC
LIMIT 100000, 20 — 只取主键,覆盖索引,扫描快得多
) ids ON ids.id = o.id;
延迟关联的原理是:子查询只扫描覆盖索引(索引文件远小于数据文件),速度比直接扫描数据快 5~10 倍;外层 JOIN 只对 20 条主键做回表,代价固定。两者相乘,比直接 LIMIT 100000, 20 快一个数量级。
第 11 条:IN 的列表不要太长,超过一定数量优化器会放弃索引
问题场景:批量查询时,IN 列表里有几千个 ID,查询极慢,EXPLAIN 显示全表扫描。
MySQL 优化器在估算 IN (v1, v2, …, vN) 的代价时,如果 IN 列表覆盖的比例超过表的一定阈值(通常是 20%~30%),优化器会认为全表扫描比走索引更便宜。此外,IN 列表过长(几千个值)本身也有解析和匹配的 CPU 开销。
解决方式是把大批量查询拆成小批量:
// 反例:一次性把几千个 ID 传给 IN
List<Long> allIds = // …几千个 ID
List<Order> orders = orderMapper.selectByIds(allIds);
// 生成 SQL:SELECT * FROM orders WHERE id IN (1,2,3,…,5000)
// 可能走索引也可能全表扫描,且 SQL 本身解析慢
// 正例:分批查询,每批不超过 500 个
private static final int BATCH_SIZE = 500;
List<Order> orders = new ArrayList<>();
for (int i = 0; i < allIds.size(); i += BATCH_SIZE) {
List<Long> batch = allIds.subList(i, Math.min(i + BATCH_SIZE, allIds.size()));
orders.addAll(orderMapper.selectByIds(batch));
}
// 每批 500 个 ID,MySQL 稳定走主键索引,合计查询次数 = allIds.size() / 500
分批大小 500 是一个经验值,不是绝对标准。实际上取决于你的主键类型(BIGINT 8字节 × 500 = 4KB,远小于 MySQL 的 max_allowed_packet 默认值 64MB)和业务可接受的查询次数。如果 allIds 有几万个,分批查询可能需要几十次网络往返,这时候应该考虑能否通过业务逻辑的重设计,避免把这么多 ID 在应用层传来传去。
第 12 条:COUNT(*) 和 COUNT(列名) 的语义差异
问题场景:想统计订单数量,有同事用了 COUNT(id),有同事用了 COUNT(*),有人说前者更快,有人说没区别,争论没有结论。
这个问题有明确答案。COUNT(*) 统计表中所有行数(NULL 行也算);COUNT(列名) 统计该列非 NULL 的行数。如果你要统计"这张表有多少行",应该用 COUNT(*),语义最准确,而且 MySQL 对 COUNT(*) 做了专门优化(InnoDB 使用最小的可用索引扫描,而不必读数据行)。
COUNT(1) 和 COUNT(*) 在性能上完全等价,MySQL 优化器会把两者都处理成同一种执行计划,所以不需要为"哪个更快"纠结,按语义选就行:
— 统计总行数:用 COUNT(*)
SELECT COUNT(*) FROM orders WHERE tenant_id = 1001;
— 统计某列非 NULL 的行数:用 COUNT(列名)
SELECT COUNT(completed_at) FROM orders WHERE tenant_id = 1001;
— 只统计 completed_at 不为 NULL 的行数
— 常见的性能优化:如果只需要大概的行数(不要求精确),
— 可以从 information_schema 读取统计信息(几乎零开销)
SELECT table_rows
FROM information_schema.tables
WHERE table_schema = 'saas_demo' AND table_name = 'orders';
— 注意:这个值是估算值(和 EXPLAIN 的 rows 一样),不精确,只适合"大概多少"的场景
第 13 条:NOT IN / NOT EXISTS 的替代方案
问题场景:查询"没有下过订单的租户",用了 WHERE tenant_id NOT IN (SELECT tenant_id FROM orders),全表扫描,超时。
NOT IN 和 NOT EXISTS 在语义上相似,但性能特征不同。NOT IN 在子查询结果集里有 NULL 时行为诡异(任何值和 NULL 比较都返回 UNKNOWN,导致整个 NOT IN 返回空集),而且通常无法高效利用索引。NOT EXISTS 通常比 NOT IN 更安全(NULL 处理正确)。但两者在大数据量下都不如 LEFT JOIN + IS NULL 高效:
— 反例:NOT IN(有 NULL 风险 + 性能差)
SELECT * FROM tenant
WHERE id NOT IN (SELECT DISTINCT tenant_id FROM orders);
— 如果 orders.tenant_id 有 NULL 值,整个查询返回空集!
— 正例一:LEFT JOIN + IS NULL(性能最好,语义清晰)
SELECT t.*
FROM tenant t
LEFT JOIN orders o ON o.tenant_id = t.id
WHERE o.tenant_id IS NULL;
— LEFT JOIN 保留左表所有行,未匹配的右表列为 NULL
— WHERE o.tenant_id IS NULL 筛选出"没有订单的租户"
— 正例二:NOT EXISTS(比 NOT IN 安全,性能介于两者之间)
SELECT * FROM tenant t
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.tenant_id = t.id
);
三种写法中,LEFT JOIN + IS NULL 通常性能最好,因为 MySQL 对 LEFT JOIN 的优化已经很成熟,可以利用被驱动表的索引做 exists 测试(而不是物化整个子查询结果)。
第 14 条:LIMIT 1 让"找到就停",省去不必要的扫描
问题场景:代码里需要判断"某个租户是否有未支付的订单",查询写成了 SELECT COUNT(*) FROM orders WHERE … > 0,要扫描所有匹配行才能得到结果。
COUNT(*) 必须把所有满足条件的行都数完,才能返回总数。如果你只关心"有没有"而不关心"有多少",加 LIMIT 1 让 MySQL 找到第一条匹配行就立刻停止,大幅减少扫描量:
// 反例:COUNT 要扫所有匹配行
boolean hasUnpaid = orderMapper.countUnpaidByTenant(tenantId) > 0;
// 生成:SELECT COUNT(*) FROM orders WHERE tenant_id=? AND status=0
// 哪怕有 10 万条未支付订单,也要全部扫完才能返回
// 正例:EXISTS 语义 + LIMIT 1,找到一条即返回
boolean hasUnpaid = orderMapper.existsUnpaidByTenant(tenantId);
// MyBatis SQL:SELECT 1 FROM orders WHERE tenant_id=#{tenantId} AND status=0 LIMIT 1
// 找到第一条未支付订单就立刻停止,无论后面还有多少条
// Spring Data JPA 的等价写法
boolean hasUnpaid = orderRepository.existsByTenantIdAndStatus(tenantId, 0);
// JPA 自动生成 COUNT 查询,但如果用 existsBy,某些版本会生成 LIMIT 1 优化
这个改写对"判断存在性"的场景有非常显著的效果,特别是当满足条件的行很多时。
第 15 条:用 UNION ALL 代替 UNION,除非确实需要去重
问题场景:用 UNION 合并两个子查询,却不知道 UNION 默认做去重操作,在大数据量下去重非常耗资源。
UNION 会对合并后的结果集做去重(相当于 DISTINCT),需要建临时表、排序、对比,时间复杂度 O(N log N)。如果两个子查询的结果集本身就不会有重复(比如分别取不同 ID 范围的数据,或者业务保证了不重叠),用 UNION ALL 直接合并,跳过去重步骤:
— 场景:把近 30 天的订单和历史归档订单合并展示
— 如果业务保证两张表的订单 ID 不重叠,用 UNION ALL
— 反例:UNION 去重,大结果集时极慢
SELECT id, order_no FROM orders WHERE created_at >= '2024-06-01'
UNION
SELECT id, order_no FROM orders_archive WHERE created_at < '2024-06-01';
— 正例:UNION ALL,跳过去重
SELECT id, order_no FROM orders WHERE created_at >= '2024-06-01'
UNION ALL
SELECT id, order_no FROM orders_archive WHERE created_at < '2024-06-01';
— 如果需要最终结果按时间排序,在最外层加 ORDER BY
— SELECT * FROM (…UNION ALL…) combined ORDER BY created_at DESC LIMIT 20;
第三维度:表设计(5 条)
表设计的问题通常在系统运行初期看不出来,等到数据量大了之后才会以性能问题的形式爆发,而且修改成本极高。提前把这些坑堵上,比事后修复省十倍的力气。
第 16 条:逻辑删除用 deleted_at,不用 is_deleted
问题场景:逻辑删除用 is_deleted TINYINT DEFAULT 0,唯一索引在删除后无法复用被删除的值。
is_deleted 的问题是:一旦记录被删除(is_deleted=1),唯一索引(如 (tenant_id, order_no))仍然会被这条"已删除"的记录占用,新建相同 order_no 的订单会因为唯一键冲突而失败。
deleted_at 方案利用了 MySQL 的一个特性:唯一索引中,NULL 值之间不被视为相同值,所以两条 deleted_at = NULL 的记录不会冲突,但 deleted_at = <某个时间戳> 与其他非 NULL 值是不同的,也不冲突:
— is_deleted 方案:删除后唯一索引继续占用,无法复用
UNIQUE KEY uk_tenant_order (tenant_id, order_no, is_deleted);
— 问题:is_deleted=0 和 is_deleted=1 是不同值,但这只是绕过了冲突,不是真正的复用
— 如果同一个 order_no 被删除两次(数据修复场景),is_deleted=1 的记录会有两条,唯一性失效
— deleted_at 方案:未删除的记录 deleted_at=NULL,NULL 之间不冲突
UNIQUE KEY uk_tenant_order (tenant_id, order_no, deleted_at);
— deleted_at = NULL 的记录之间互相唯一约束,是真正有效的业务唯一性保证
— 被删除的记录 deleted_at 有具体时间戳,不影响新记录的唯一约束
此外,deleted_at 同时记录了"何时删除",审计日志更完整;is_deleted 只有 0/1,没有时间信息。
第 17 条:大字段分离,减少主表的行宽
问题场景:订单表里有一个 extra_info TEXT 列存储扩展信息,大多数查询都不需要这个字段,但每次查询都把它传输了过来,占用了大量带宽和 Buffer Pool。
InnoDB 在读取数据行时,如果行宽超过一定阈值(约 8KB),大字段的数据会存储在"溢出页"中,主行只存储指针。即使这样,传输时这些字段也会被读取,白白消耗 I/O 和网络资源。
解决方案是把不常用的大字段拆到单独的扩展表,主表只保留高频访问的核心字段:
— 把 TEXT、JSON、BLOB 类型的扩展字段单独建表
CREATE TABLE order_extra (
order_id BIGINT UNSIGNED NOT NULL,
tenant_id BIGINT UNSIGNED NOT NULL,
extra_info TEXT NULL COMMENT '扩展信息,低频访问',
raw_request MEDIUMTEXT NULL COMMENT '原始请求报文,调试用',
PRIMARY KEY (order_id),
INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB;
— 主表只保留高频字段
— 绝大多数列表查询、统计查询不需要 JOIN order_extra
— 只有"查看订单详情"时才 LEFT JOIN order_extra
这个设计模式被称为"垂直分表",核心价值是让主表的行宽保持小,相同 Buffer Pool 能缓存更多行,查询命中率更高。
第 18 条:DATETIME(3) 而不是 DATETIME,时间精度很重要
问题场景:系统在一秒内创建了多条记录,按 created_at 排序后,结果顺序不稳定(同一秒内的记录随机排列)。
默认的 DATETIME(没有括号)精度是秒,同一秒创建的多条记录有相同的 created_at 值,MySQL 无法通过这个字段区分它们的创建顺序。DATETIME(3) 精度到毫秒,能区分同一秒内的时序:
— 不推荐:秒级精度,高并发时排序不稳定
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
— 推荐:毫秒级精度,存储多用 2 字节(可以接受)
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
ON UPDATE CURRENT_TIMESTAMP(3),
对于需要做分页排序的列表(订单列表、日志列表),排序列的精度非常重要——如果大量记录的排序列值相同,每页的边界行每次可能不一样,分页结果不稳定,用户体验极差。
第 19 条:合理使用 ENUM,不要用 ENUM 存可能变化的状态
问题场景:订单状态最初只有 3 个,用了 ENUM('pending', 'paid', 'cancelled'),后来业务增加了"部分退款"状态,修改 ENUM 定义需要重建表,对大表来说是个灾难。
ENUM 类型存储空间小(最多 2 字节),按索引顺序存储(有序,适合范围查询),但修改 ENUM 的值列表(添加非末尾的值、删除值)需要重建整张表,在大表上可能锁定几十分钟。
建议原则:对确定不会变化的状态用 ENUM(如性别、布尔值),对可能扩展的业务状态用 TINYINT,并用常量类或枚举类在 Java 侧维护含义:
// Java 侧的状态枚举(数据库存 TINYINT,应用层维护含义)
public enum OrderStatus {
PENDING(0, "待支付"),
PAID(1, "已支付"),
SHIPPED(2, "已发货"),
COMPLETED(3, "已完成"),
CANCELLED(4, "已取消"),
PARTIAL_REFUND(5, "部分退款"); // 后来新增,数据库不需要任何改动
private final int code;
private final String desc;
// …
}
这样,业务状态扩展时只需要在 Java 枚举里加一个值,不涉及任何 DDL 变更。
第 20 条:归档历史数据,让热表保持"小而快"
问题场景:订单表几年下来累积了几亿行,90% 是两年前的历史数据,查询效率越来越低,索引文件越来越大。
B+树索引的查找效率与数据量的对数成正比——数据量翻倍,索引高度增加一层,每次查询的磁盘 I/O 多一次。更重要的是,两年前的历史数据几乎从不被查询,但它们占用了大量 Buffer Pool 空间,把热数据挤出去,导致命中率下降。
解决方案是定期把历史数据归档到独立的归档表,保持主表的数据量在一个合理范围内(通常保留最近 6 个月到 1 年的数据):
— 创建归档表(结构和主表相同,但可以去掉某些写密集索引)
CREATE TABLE orders_archive LIKE orders;
— 定期把 2 年前的数据迁移到归档表(用 pt-archiver 工具最安全,不锁表)
— 手动迁移示例(生产建议用 pt-archiver 或写脚本分批执行,不要一次 INSERT 太多)
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
LIMIT 10000; — 分批,每批 1 万条,避免大事务
DELETE FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
AND id IN (SELECT id FROM orders_archive WHERE ...);
— 注意:迁移和删除要在同一个事务里,防止数据丢失
归档策略需要和业务方确认"多久的数据是’热数据’",不同业务差异很大。归档后,针对历史数据的查询要改为查 orders_archive,或者通过应用层路由(先查主表,不够再查归档表),或者用数据库 VIEW 把两张表做 UNION ALL 对外暴露一个统一视图。
第四维度:连接与事务(5 条)
连接和事务的配置问题,在功能测试时几乎发现不了,但在生产高并发下会以"连接池耗尽"、“死锁”、"慢事务"等形式爆发。
第 21 条:事务范围越小越好,不要在事务里调用外部服务
问题场景:一个下单接口把"查库存 → 创建订单 → 调用支付 API → 更新库存"全都放在一个 @Transactional 方法里,偶发的支付 API 超时导致数据库事务长时间不提交,锁住了大量行。
事务持有数据库锁的时间,等于事务开始到提交(或回滚)的时间。在事务里调用外部 HTTP 接口,如果接口响应慢(几秒甚至超时),数据库锁就被持有几秒,高并发时其他事务等待锁,连接池很快被打满。
原则是:事务里只放"必须原子提交"的数据库操作,外部 API 调用放在事务之外:
// 反例:事务范围包含了外部 API 调用
@Transactional
public OrderResult createOrder(CreateOrderRequest req) {
inventoryService.lockStock(req.getProductId(), req.getQty()); // 数据库操作
Order order = orderService.save(req); // 数据库操作
PaymentResult result = paymentClient.charge(order); // HTTP 调用!
// 如果 paymentClient 超时 5 秒,数据库锁被持有 5 秒以上
orderService.updateStatus(order.getId(), result.getStatus()); // 数据库操作
return OrderResult.from(order);
}
// 正例:把外部 API 调用移到事务外,事务只包含数据库操作
public OrderResult createOrder(CreateOrderRequest req) {
// Phase 1:事务内创建订单(快,锁时间极短)
Order order = createOrderInTransaction(req);
// Phase 2:事务外调用支付(耗时操作,不持有数据库锁)
PaymentResult result = paymentClient.charge(order);
// Phase 3:事务内更新状态(快)
updateOrderStatusInTransaction(order.getId(), result.getStatus());
return OrderResult.from(order);
}
@Transactional
private Order createOrderInTransaction(CreateOrderRequest req) { ... }
@Transactional
private void updateOrderStatusInTransaction(Long orderId, String status) { ... }
第 22 条:连接池大小不是越大越好
这个问题在第 09 篇已经详细讲过,这里给出一个简明的计算公式,方便直接用:
HikariCP maximum-pool-size 推荐公式:
单节点推荐:最大连接数 = CPU 核心数 × 2 + 磁盘数
(来自 HikariCP 作者的建议,适合大多数 OLTP 场景)
多节点 SaaS:
MySQL max_connections = (节点数 × 单节点最大连接数 × 1.5) + 20(DBA 预留)
示例:4 核 CPU,3 台应用服务器,每台 HikariCP 最大连接数 = 4×2+1 = 9
MySQL max_connections = 3 × 9 × 1.5 + 20 = 60.5,取整 65
很多团队把 max_connections 设为 1000 甚至 2000,实际上同时活跃的连接很少超过 50 个。连接数设太大的副作用是:发生连接泄漏时,要很久才能触发"Too many connections"错误(因为上限太高),使得问题发现得太晚。
第 23 条:长事务是隐形的性能杀手
问题场景:某个定时任务会批量处理大量数据,把所有操作放在一个事务里,运行期间 information_schema.innodb_trx 里有一个持续几分钟的长事务,导致 Undo Log 大量积累,MySQL 内存压力增大,其他查询变慢。
InnoDB 的 MVCC 机制要求:只要有事务存在,事务开始之前的所有行版本(Undo Log)都不能被清理,否则该事务的快照读会找不到应该看到的版本。长事务会让 Undo Log 持续积累,不能被 Purge 线程回收,占满 ibdata1 文件。
解法是把长批量操作改为"小批量 + 分批提交":
// 反例:一个大事务处理所有数据
@Transactional
public void processBatch(List<Order> orders) {
for (Order order : orders) {
processOne(order); // 可能有几万条,事务持续几分钟
}
}
// 正例:分批提交,每批是一个独立事务
// 不加 @Transactional,自己控制事务边界
@Autowired
private TransactionTemplate transactionTemplate;
public void processBatch(List<Order> orders) {
int batchSize = 500;
for (int i = 0; i < orders.size(); i += batchSize) {
List<Order> batch = orders.subList(i, Math.min(i + batchSize, orders.size()));
transactionTemplate.execute(status -> {
batch.forEach(this::processOne);
return null;
});
// 每 500 条提交一次,Undo Log 及时被清理
log.info("已处理 {}/{}", Math.min(i + batchSize, orders.size()), orders.size());
}
}
分批提交的代价是:如果中途失败,已提交的批次无法回滚(不是一个原子操作了)。需要在业务上设计幂等性(每条数据记录处理状态,失败后重跑时跳过已处理的),或者记录断点(记录处理到第几条,重跑时从断点继续)。
第 24 条:autocommit 关闭时别忘了手动提交
问题场景:某个接口偶发地比平时慢 10 倍,重启应用后恢复正常。查看 SHOW PROCESSLIST 发现有很多 Sleep 状态的连接,info 里显示事务没有提交。
autocommit=0(通常是某些 ORM 框架或连接池配置)会让每条 SQL 都处于隐式事务中,必须手动 COMMIT 才能提交。忘记 COMMIT 的连接会一直持有行锁,其他需要这些锁的事务只能等待,积累够了就是一场"连接雪崩"。
不要主动关闭 autocommit,让 Spring 的 @Transactional 管理事务边界是最安全的方式:
— 验证当前会话的 autocommit 状态
SHOW VARIABLES LIKE 'autocommit';
— 如果是 OFF,很危险
— 查看有没有长时间未提交的事务
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY duration_sec DESC;
第 25 条:批量写入使用 rewriteBatchedStatements=true
这条在第 02 篇详细讲过,这里用一个对比数据说明它的价值:在一个实测场景(插入 1 万条记录,网络 RTT 约 2ms),不开 rewriteBatchedStatements,即使用了 addBatch() / executeBatch(),实际上每条 INSERT 仍然单独发送,耗时约 20 秒;开启后,多条 INSERT 被合并为一条多值 INSERT,耗时降到约 1.5 秒。差距 13 倍,仅仅是在 JDBC URL 里加了一个参数。
# application.yml
spring:
datasource:
url: jdbc:mysql://host:3306/db?rewriteBatchedStatements=true&…
# 开启这个参数后,JDBC 驱动会把:
# INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); INSERT INTO t VALUES (3);
# 合并为:
# INSERT INTO t VALUES (1),(2),(3);
# 减少了网络往返次数和 MySQL 的 SQL 解析次数
前提:必须关闭自动提交(conn.setAutoCommit(false)),并使用 addBatch() / executeBatch(),两个条件缺一不可。
第五维度:架构层提速(3 条)
当 SQL 优化和索引设计都已经做到极致,某些场景仍然无法满足性能要求时,需要从架构层寻找突破口。
第 26 条:热点数据用 Redis 缓存,而不是把 MySQL 当 Redis 用
问题场景:某些配置数据(租户套餐限制、全局字典、权限配置)被每个请求反复读取,虽然数据量小,但高并发时数据库连接被大量消耗在这些"简单但高频"的查询上。
MySQL 是面向磁盘设计的,即使有 Buffer Pool 缓存,每次查询仍然有 SQL 解析、锁检测、行读取的 CPU 开销。Redis 是纯内存的,单个 GET 操作的时间在微秒级,吞吐量是 MySQL 的几十倍。
把"高频读取、低频变更"的数据放到 Redis,是最经济的减压方案:
@Service
public class TenantConfigService {
@Autowired
private StringRedisTemplate redis;
@Autowired
private TenantRepository tenantRepository;
// 缓存租户配置,TTL 10 分钟(配置变更后等待最多 10 分钟生效)
public TenantConfig getConfig(Long tenantId) {
String key = "tenant:config:" + tenantId;
String cached = redis.opsForValue().get(key);
if (cached != null) {
return JSON.parseObject(cached, TenantConfig.class); // 缓存命中,微秒级
}
// 缓存未命中,查数据库
TenantConfig config = tenantRepository.findConfigById(tenantId);
// 写入缓存,TTL 10 分钟
redis.opsForValue().set(key, JSON.toJSONString(config), 10, TimeUnit.MINUTES);
return config;
}
// 配置变更时主动失效缓存(而不是等 TTL 过期)
public void updateConfig(Long tenantId, TenantConfig newConfig) {
tenantRepository.save(newConfig);
redis.delete("tenant:config:" + tenantId); // 主动删除,下次查询时刷新
}
}
引入 Redis 缓存时要解决"缓存一致性"问题——数据库更新后缓存什么时候失效。"主动删除"比"TTL 自然过期"更及时,但"先更新数据库后删缓存"的顺序有一个小概率的不一致窗口(更新 DB 成功但删 Cache 失败)。如果业务对一致性要求极高,可以用"Cache Aside + 延迟双删"或"Binlog + Canal 订阅自动失效"等更复杂但更可靠的方案。
第 27 条:读写分离减压主库,但要处理写后读一致性
读写分离的配置和写后读一致性问题在第 06 篇有完整讲解,这里补充一个工程实践中常见的决策框架:不是所有读请求都适合走从库。
读写分离路由决策矩阵:
场景 推荐路由 原因
─────────────────────────────────────────────────────
列表查询(订单列表、用户列表) 从库 可以容忍秒级延迟,减压主库
统计报表(日/月/年统计) 从库 数据延迟几秒不影响报表价值
写完立刻展示详情 主库 写后立刻读,从库可能还没同步
支付/下单核心流程 主库 数据一致性要求高,不走从库
搜索/自动补全 ES 不走 MySQL(走专用搜索引擎)
实时监控/大屏 独立只读实例 读压力大且可以接受延迟
把这个决策矩阵落地成代码,可以用注解方式标注每个查询方法应该走主库还是从库,并通过 AOP 拦截器实现自动路由,不需要每个方法都手动调用 HintManager:
// 自定义注解标注路由策略
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadPrimary {} // 标注需要强制走主库的方法
// AOP 拦截器:遇到 @ReadPrimary 注解,自动设置 HintManager
@Aspect
@Component
public class ReadRoutingAspect {
@Around("@annotation(ReadPrimary)")
public Object routeToPrimary(ProceedingJoinPoint pjp) throws Throwable {
try (HintManager hintManager = HintManager.getInstance()) {
hintManager.setWriteRouteOnly();
return pjp.proceed();
}
}
}
// 使用方式
@Service
public class OrderService {
@ReadPrimary // 标注这个方法走主库
public OrderVO getOrderAfterCreate(Long orderId) {
return orderRepository.findById(orderId).map(OrderVO::from).orElseThrow();
}
}
第 28 条:全文搜索不要用 MySQL,LIKE 打穿大表是慢查询温床
问题场景:用户搜索订单支持关键词搜索,用了 WHERE remark LIKE '%关键词%',随着数据量增长越来越慢,加了索引也没用(前缀通配符导致索引失效)。
LIKE '%关键词%' 无法利用 B+树索引,必须全表扫描,时间复杂度 O(N),N 是表的行数。这是 MySQL 全文搜索能力的硬边界,再多的索引优化都无法突破。
真正的全文搜索有两条路:MySQL 的 FULLTEXT 索引(适合中小数据量、搜索需求简单的场景),或者 Elasticsearch(适合大数据量、复杂搜索、需要高亮、聚合统计的场景):
— MySQL FULLTEXT 索引(需要 ngram 分词器支持中文)
ALTER TABLE orders
ADD FULLTEXT INDEX ft_remark(remark) WITH PARSER ngram;
— 使用全文搜索(替代 LIKE '%关键词%')
SELECT * FROM orders
WHERE tenant_id = 1001
AND MATCH(remark) AGAINST('重要货物' IN BOOLEAN MODE)
ORDER BY created_at DESC LIMIT 20;
MySQL FULLTEXT 的限制是:不支持实时更新(有同步延迟)、不支持复杂的搜索逻辑(如多字段权重、模糊匹配)、不支持搜索结果高亮。如果业务对搜索功能有持续增长的需求,尽早接入 Elasticsearch,技术债越积越难清。
第六维度:工具与诊断(2 条)
工具用得好,排查问题的时间可以从几小时压缩到几分钟。
第 29 条:pt-query-digest,慢查询分析的瑞士军刀
问题场景:慢查询日志里有几千条记录,用 mysqldumpslow 输出的汇总信息太粗,看不出哪条 SQL 最值得优化。
pt-query-digest(Percona Toolkit 的一部分)能把慢查询日志按 SQL 模式(摘要)汇总,统计每种 SQL 的执行次数、总耗时、平均耗时、最慢一次,并按"对整体影响最大"排序,精确定位最值得优化的 SQL:
# 安装 Percona Toolkit
# CentOS:yum install percona-toolkit
# Ubuntu:apt-get install percona-toolkit
# 分析慢查询日志,输出 TOP 10 最影响性能的 SQL 模式
pt-query-digest \\
–limit 10 \\
–output report \\
/var/log/mysql/slow.log \\
> slow_query_report_$(date +%Y%m%d).txt
# 输出示例(每种 SQL 模式的关键指标):
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ========== ============== ====== ====== ==== ====
# 1 0x123… 45.2188 38.4% 3421 0.0132 0.04 SELECT orders…
# 2 0x456… 28.1023 23.9% 891 0.0315 0.12 SELECT user…
# 针对最慢的 SQL,拿出来做 EXPLAIN 分析
# pt-query-digest 还能直接输出每种 SQL 模式的 EXPLAIN 结果(加 –explain 参数)
pt-query-digest \\
–explain h=127.0.0.1,u=root,p=${MYSQL_ROOT_PASSWORD} \\
–limit 5 \\
/var/log/mysql/slow.log
pt-query-digest 输出的 “R/Call”(单次平均响应时间)和 “Calls”(执行次数)两个维度要综合看。"平均慢但执行次数少"的 SQL,对整体影响有限;"平均不算慢但执行次数极多"的 SQL,累计对数据库的压力可能更大。Response time 列显示的"总时间占比"是最直接反映"哪条 SQL 对整体性能影响最大"的指标,从这里开始优化,投入产出比最高。
第 30 条:用 EXPLAIN ANALYZE 看清优化器估算 vs 实际执行的偏差
这是 MySQL 8.0.18 引入的功能,在第 07 篇提到过,这里给出更实用的使用技巧。EXPLAIN ANALYZE 真正执行查询,然后对每个步骤同时输出优化器的估算值和实际值,是发现"优化器选错执行计划"的最直接手段:
— EXPLAIN ANALYZE 示例(只在测试/开发环境用,会真正执行查询)
EXPLAIN ANALYZE
SELECT o.id, o.total_amount, t.tenant_name
FROM orders o JOIN tenant t ON t.id = o.tenant_id
WHERE o.tenant_id = 1001 AND o.status = 1
ORDER BY o.created_at DESC LIMIT 20\\G
— 输出解读要点:
— 每个步骤都有两组数字:(estimated cost=X rows=Y) (actual time=A..B rows=C loops=D)
— 比对 estimated rows 和 actual rows:
— 相差 10 倍以内:正常
— 相差 100 倍以上:统计信息严重不准,先 ANALYZE TABLE
—
— actual time 的格式是 "第一行返回时间..最后一行返回时间"
— 如果 "第一行时间" 很长,说明这一步启动代价高(如建临时表、全排序)
— 如果两个时间差很大,说明这一步处理了很多行
— 典型案例:优化器估算 rows=5,实际 rows=50000
— 说明统计信息不准,执行:
ANALYZE TABLE orders; — 更新统计信息
— 然后重新 EXPLAIN ANALYZE,看执行计划是否改变
EXPLAIN ANALYZE 对 SELECT 是安全的(只是多消耗一次真实查询的资源),但对 INSERT、UPDATE、DELETE 会真正执行并修改数据,使用时务必确认当前环境是测试库。如果一定要在生产环境分析 DML 语句的执行计划,用 EXPLAIN FORMAT=TREE 代替(只给出树形执行计划,不执行)。
总结:30 条优化技巧的优先级排序
把这 30 条按"紧急程度"和"投入产出比"归类,给出一个落地时的优先级建议:
立刻检查、高优先级修复(发现即改,风险高):
第 02 条 隐式类型转换导致索引失效(功能正常但性能差,最难发现)
第 10 条 大偏移量分页(数据量小时无感,数据量大后定时爆炸)
第 21 条 事务里调用外部服务(高并发下会引发连接雪崩)
第 23 条 长事务导致 Undo Log 膨胀(监控告警中发现后立刻处理)
Code Review 阶段就能发现、低成本预防:
第 01 条 联合索引列顺序(建索引时就要考虑)
第 03 条 对索引列用函数(SQL 写出来就能看到)
第 09 条 SELECT *(Review 时加入检查项)
第 12 条 COUNT(*) 和 COUNT(列名) 语义混淆(规范化)
第 14 条 用 LIMIT 1 替代 COUNT 判断存在性(改写成本极低)
第 25 条 rewriteBatchedStatements(配置一次,永久受益)
设计阶段就要决策、改造成本高:
第 16 条 逻辑删除用 deleted_at(建表时就定)
第 17 条 大字段分离(表设计阶段的决策)
第 19 条 业务状态用 TINYINT 不用 ENUM(灵活性决策)
第 20 条 归档历史数据(运营一段时间后的架构演进)
第 26 条 热点数据引入 Redis 缓存(架构层决策)
第 28 条 全文搜索迁移到 ES(搜索需求增长时的技术选型)
这 30 条不是一次性完成的清单,而是一个随着系统成长持续检查的框架。把它们内化成日常 Code Review 和技术评审的一部分,MySQL 的性能问题就会越来越少地"突然爆发",越来越多地"在萌芽阶段被发现"。
FAQ
Q:这 30 条优化,哪几条在实际生产中最高频地被忽略?
A:根据生产事故的频率统计,排名前三的是:第 02 条(隐式类型转换,因为功能正常所以容易被忽略)、第 10 条(大偏移量分页,数据量小时无感,上线几个月后才爆发)、第 21 条(事务里调用外部服务,在单元测试中根本发现不了,只有高并发压测才能暴露)。这三条共同特点是"功能上完全正确、只有在特定条件下才暴露性能问题",因此最难被发现。
Q:这些优化技巧在 MySQL 5.7 和 8.0 上都适用吗?
A:大部分适用于两个版本。以下几条是 8.0 专有的:第 03 条的函数索引方案(5.7 只能改写 SQL)、第 07 条的不可见索引(5.7 没有这个特性)。其余 28 条在 5.7 和 8.0 上都完全适用。
Q:应该先优化 SQL 还是先加索引?
A:通常的优先级是:先确认索引设计合理(第 01 条列顺序、第 04 条覆盖索引),再优化 SQL 写法(第 09 条 SELECT *、第 10 条分页、第 13 条 NOT IN),最后考虑架构层改造(缓存、读写分离、搜索引擎)。因为索引设计是基础,SQL 在没有合适索引的情况下再怎么改写也有限;而架构层改造成本最高,应该在"单机 MySQL 确实到瓶颈了"才引入,而不是一开始就过度设计。
30 条提炼自真实生产经验,如果你在项目中踩到了其中某个坑,或者有新的优化经验想补充,欢迎在评论区留言交流。

