内容数据库调优100例：从报警到修复

这篇文章，不讲玄学、不讲空话。 100 个真实可复用的数据库调优案例，带你从报警 → 定位 → 修复 → 复盘，建立一套真正能救命的数据库调优方法论。

文章结构

为了方便查阅,我把100个案例分成了10大类:

第一章:索引优化(案例1-15)
最常见也最容易被忽视的问题,80%的慢查询都能通过优化索引解决

第二章:SQL语句优化(案例16-30)
写出高性能SQL的技巧,避开那些看似正常实则有大问题的写法

第三章:锁和并发控制(案例31-45)
处理高并发场景下的锁冲突、死锁、幻读等问题

第四章:连接池与资源管理(案例46-55)
连接池配置、连接泄漏检测、资源隔离等实战经验

第五章:表结构设计(案例56-65)
什么样的表结构能支撑亿级数据,什么样的设计是在给自己挖坑

第六章:大表优化(案例66-75)
千万级、亿级数据表的优化策略,分库分表的最佳实践

第七章:缓存策略(案例76-82)
缓存穿透、缓存雪崩、缓存一致性等经典问题的解决方案

第八章:监控告警(案例83-90)
建立完善的监控体系,问题还没爆发就提前发现

第九章:容灾与高可用(案例91-95)
主从同步、故障切换、数据一致性保障

第十章:疑难杂症(案例96-100)
那些难以归类但极具参考价值的特殊案例

每一章都可以独立阅读,遇到问题时可以直接跳到对应章节查找解决方案。

适合谁看?

如果你是:

后端开发,想系统提升数据库调优能力
DBA,需要积累更多实战案例和解决方案
技术leader,要为团队建立数据库规范和最佳实践
创业公司CTO,系统出问题时需要快速定位和修复
应届生/初级工程师,想快速积累数据库经验避免踩坑

那这篇文章就是为你准备的。

第一章:索引优化(案例1-15)

案例1:没有索引的噩梦

报警现象:用户列表查询接口响应时间从50ms飙升到12秒,CPU使用率98%

排查过程:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- 发现type=ALL,扫描了整个表的800万行数据

问题根源:email字段没有建立索引,每次查询都全表扫描

修复方案:

CREATE INDEX idx_email ON users(email);
-- 查询时间降至5ms,CPU降至15%

预防措施:

WHERE、JOIN、ORDER BY中的字段必须考虑索引
上线前用EXPLAIN检查所有查询的执行计划
监控慢查询日志,及时发现全表扫描

血泪教训:这个问题在测试环境完全发现不了,因为测试数据只有几千条。只有生产环境百万级数据时才暴露。所以测试环境也要灌入足量数据。

案例2:索引失效的隐形杀手

报警现象:明明建了索引,查询还是慢得要死

问题代码:

SELECT * FROM orders 
WHERE YEAR(create_time) = 2024;
-- 索引完全失效!

根本原因:对索引列使用函数,导致索引失效

正确写法:

SELECT * FROM orders 
WHERE create_time >= '2024-01-01' 
  AND create_time < '2025-01-01';
-- 索引生效,查询时间从8秒降至20ms

其他导致索引失效的情况:

使用OR连接不同列:WHERE name='张三' OR age=25
隐式类型转换:WHERE user_id='123'(user_id是int类型)
LIKE以通配符开头:WHERE name LIKE '%张三%'
使用NOT IN、!=、<>

案例3:联合索引的顺序陷阱

问题场景:订单查询,已建立联合索引(status, create_time),但查询还是很慢

问题SQL:

SELECT * FROM orders 
WHERE create_time > '2024-01-01';
-- 索引没用上!

原因分析:联合索引遵循"最左前缀原则",必须从最左边的列开始使用

解决方案:调整索引顺序

-- 删除旧索引
DROP INDEX idx_status_time;
-- 创建新索引
CREATE INDEX idx_time_status ON orders(create_time, status);

索引顺序选择原则:

区分度高的列放前面(能过滤掉更多数据的)
经常单独查询的列放前面
范围查询的列放最后

经验值:

status只有5个值,区分度低
create_time几乎每条记录都不同,区分度高
所以create_time应该放前面

案例4:过多索引拖垮写入性能

报警现象:双11凌晨,订单写入速度突然暴跌,从5000/秒降至200/秒

排查发现:订单表上有17个索引!

问题本质:每插入一条数据,需要更新17个索引,磁盘IO直接爆炸

优化方案:

-- 删除几乎不用的索引
DROP INDEX idx_never_used_1;
DROP INDEX idx_never_used_2;
-- 合并相似索引
DROP INDEX idx_user_id;
DROP INDEX idx_user_status;
CREATE INDEX idx_user_status ON orders(user_id, status);

最终结果:保留8个真正有用的索引,写入速度恢复至4200/秒

经验总结:

索引不是越多越好,每个索引都有维护成本
定期检查索引使用率:SELECT * FROM sys.schema_unused_indexes;
删除使用率<1%的索引

案例5:索引覆盖的威力

场景:商品列表页,需要显示商品ID、名称、价格

原始SQL(慢):

SELECT id, name, price FROM products 
WHERE category_id = 10 
ORDER BY price DESC 
LIMIT 20;
-- 执行时间:450ms

问题:虽然category_id有索引,但还需要回表查询name和price

优化方案:使用覆盖索引

CREATE INDEX idx_category_price_cover 
ON products(category_id, price, name, id);
-- 执行时间降至8ms

原理:索引中已包含所有需要的字段,不需要回表查询

适用场景:

查询字段少(2-4个)
查询频率高
字段总长度不超过1KB

注意事项:覆盖索引会增大索引体积,需权衡

案例6:前缀索引的妙用

问题:用户表的address字段平均长度200字符,建立索引后占用空间巨大

传统索引:

CREATE INDEX idx_address ON users(address);
-- 索引大小:8GB

优化方案:使用前缀索引

CREATE INDEX idx_address_prefix ON users(address(20));
-- 索引大小:800MB

前缀长度如何选择:

-- 查看不同前缀长度的区分度
SELECT 
  COUNT(DISTINCT LEFT(address, 10)) / COUNT(*) as prefix_10,
  COUNT(DISTINCT LEFT(address, 20)) / COUNT(*) as prefix_20,
  COUNT(DISTINCT LEFT(address, 30)) / COUNT(*) as prefix_30
FROM users;
-- 选择区分度>0.95的最短前缀

适用字段:邮箱、URL、地址等长字符串

案例7:唯一索引引发的死锁

报警:支付回调接口频繁出现死锁

代码逻辑:

-- 线程1
INSERT INTO payment_log (order_id, amount) 
VALUES ('ORDER001', 100);

-- 线程2(几乎同时)
INSERT INTO payment_log (order_id, amount) 
VALUES ('ORDER001', 100);

问题根源:order_id有唯一索引,两个线程同时插入相同order_id时产生死锁

解决方案1:使用INSERT IGNORE

INSERT IGNORE INTO payment_log (order_id, amount) 
VALUES ('ORDER001', 100);

解决方案2:先查后插

SELECT id FROM payment_log WHERE order_id = 'ORDER001' FOR UPDATE;
-- 如果不存在,再INSERT

更优方案:使用分布式锁控制并发

案例8:模糊查询的优化技巧

原始需求:搜索商品名称,支持模糊匹配

无解写法:

SELECT * FROM products 
WHERE name LIKE '%手机%';
-- 索引失效,全表扫描

优化方案1:改成前缀匹配

SELECT * FROM products 
WHERE name LIKE '手机%';
-- 可以使用索引

优化方案2:使用全文索引

ALTER TABLE products ADD FULLTEXT INDEX ft_name(name);

SELECT * FROM products 
WHERE MATCH(name) AGAINST('手机' IN BOOLEAN MODE);
-- 速度提升100倍

优化方案3:引入Elasticsearch

数据量>1000万时,MySQL全文索引性能下降
复杂搜索场景建议使用ES

案例9:主键选择的坑

问题场景:使用UUID作为主键,插入性能极差

测试数据:

自增主键: 10万条/秒
UUID主键: 2000条/秒

原因分析:

UUID是随机的,插入时导致页分裂
自增主键是顺序的,直接追加

最佳实践:

优先使用自增主键
如需全局唯一,使用雪花算法生成趋势递增的ID
避免使用UUID、随机字符串作为主键

业务ID如何处理:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) UNIQUE,  -- 业务单号,建唯一索引
  ...
);

案例10:索引失效的隐式类型转换

报警:用户登录接口突然变慢

问题SQL:

SELECT * FROM users WHERE phone = 13800138000;

看似没问题,实际:phone字段是VARCHAR,传入了INT类型

MySQL会自动转换:

SELECT * FROM users WHERE CAST(phone AS SIGNED) = 13800138000;
-- 索引失效!

正确写法:

SELECT * FROM users WHERE phone = '13800138000';

血泪教训:这种问题代码审查很难发现,必须在开发规范中明确要求类型匹配

案例11:分页查询的深度陷阱

问题场景:查询第10000页数据时,接口超时

问题SQL:

SELECT * FROM articles 
ORDER BY create_time DESC 
LIMIT 100000, 20;
-- 需要扫描100020行,非常慢

优化方案1:使用ID游标

SELECT * FROM articles 
WHERE id < 12345  -- 上一页最后一条的ID
ORDER BY id DESC 
LIMIT 20;

优化方案2:延迟关联

SELECT a.* FROM articles a
INNER JOIN (
  SELECT id FROM articles 
  ORDER BY create_time DESC 
  LIMIT 100000, 20
) b ON a.id = b.id;
-- 子查询使用覆盖索引,速度快

产品层面:限制翻页深度,超过100页不允许继续翻页

案例12:冗余索引浪费资源

发现问题:数据库磁盘占用异常高

排查发现:

KEY idx_user (user_id)
KEY idx_user_time (user_id, create_time)
-- idx_user是冗余的!

原因:联合索引的最左前缀已包含单列索引的功能

清理方案:

DROP INDEX idx_user;
-- 释放磁盘空间,加快写入速度

检查方法:

SELECT * FROM sys.schema_redundant_indexes;

案例13:索引碎片导致性能下降

现象:表刚优化完很快,过几个月又变慢了

原因:频繁的INSERT、DELETE导致索引碎片

查看碎片率:

SHOW TABLE STATUS LIKE 'orders';
-- 查看Data_free字段

解决方案:

OPTIMIZE TABLE orders;
-- 重建索引,清理碎片

自动化运维:每月凌晨自动执行OPTIMIZE

案例14:NULL值对索引的影响

问题:status字段建了索引,但查询NULL值很慢

原因:

SELECT * FROM orders WHERE status IS NULL;
-- 某些MySQL版本不会使用索引

解决方案:避免使用NULL

ALTER TABLE orders 
MODIFY status INT NOT NULL DEFAULT 0;
-- 用0表示初始状态

设计原则:

所有字段都加NOT NULL约束
用有意义的默认值代替NULL
减少存储空间和索引复杂度

案例15:函数索引的使用(MySQL 8.0+)

场景:经常查询某个字段的JSON属性

传统方式(慢):

SELECT * FROM users 
WHERE JSON_EXTRACT(profile, '$.city') = '北京';
-- 无法使用索引

MySQL 8.0解决方案:

CREATE INDEX idx_city 
ON users((JSON_EXTRACT(profile, '$.city')));

-- 现在可以使用索引了!

其他应用场景:

小写转换:CREATE INDEX idx_lower ON users((LOWER(email)));
日期提取:CREATE INDEX idx_year ON orders((YEAR(create_time)));

第二章:SQL语句优化(案例16-30)

案例16:SELECT * 的性能陷阱

问题代码:

SELECT * FROM users WHERE id = 1;

看似简单,实则:

传输了大量不需要的数据
无法使用覆盖索引
网络IO和内存占用增加

测试对比:

-- 查询全部字段:45ms
SELECT * FROM users WHERE id = 1;

-- 只查需要的字段:3ms
SELECT id, name, phone FROM users WHERE id = 1;

优化原则:明确指定需要的字段,禁用SELECT *

案例17:OR条件的优化

慢SQL:

SELECT * FROM orders 
WHERE status = 1 OR status = 2 OR status = 3;
-- 无法有效使用索引

优化方案1:使用IN

SELECT * FROM orders 
WHERE status IN (1, 2, 3);
-- 可以使用索引

优化方案2:UNION ALL

SELECT * FROM orders WHERE status = 1
UNION ALL
SELECT * FROM orders WHERE status = 2
UNION ALL
SELECT * FROM orders WHERE status = 3;
-- 每个子查询都能用上索引

性能对比:

OR: 850ms
IN: 120ms
UNION ALL: 45ms

案例18:子查询的性能灾难

问题代码:

SELECT * FROM orders o
WHERE o.user_id IN (
  SELECT id FROM users WHERE city = '北京'
);
-- 执行时间:12秒

优化方案:改用JOIN

SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = '北京';
-- 执行时间:180ms

原理:子查询会为每一行外部查询执行一次,效率极低

例外情况:使用EXISTS时,某些场景性能更好

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id AND o.status = 1
);

案例19:COUNT的优化技巧

需求:统计订单总数

最慢写法:

SELECT COUNT(*) FROM orders;
-- InnoDB需要扫描整个表

优化方案1:使用近似值

SELECT table_rows FROM information_schema.tables 
WHERE table_name = 'orders';
-- 快但不精确

优化方案2:维护计数表

CREATE TABLE order_count (
  total INT,
  update_time DATETIME
);
-- 用触发器或定时任务更新

优化方案3:Redis缓存

INCR order:count  # 每下单+1
GET order:count   # 查询瞬间返回

经验值:千万级以上的表,不要实时COUNT

案例20:GROUP BY的性能优化

慢查询:

SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id;
-- 需要创建临时表,很慢

优化方案1:增加索引

CREATE INDEX idx_user ON orders(user_id);
-- 使用索引扫描,不需要临时表

优化方案2:限制结果集

SELECT user_id, COUNT(*) FROM orders 
WHERE create_time > '2024-01-01'
GROUP BY user_id 
HAVING COUNT(*) > 10;
-- 先过滤再分组

优化方案3:使用SQL_BIG_RESULT

SELECT SQL_BIG_RESULT user_id, COUNT(*) 
FROM orders 
GROUP BY user_id;
-- 提示MySQL使用磁盘临时表

案例21:DISTINCT的滥用

问题代码:

SELECT DISTINCT user_id FROM orders;
-- 需要排序去重,很慢

更好的写法:使用GROUP BY

SELECT user_id FROM orders GROUP BY user_id;
-- 性能更好

最佳方案:在应用层去重

Set<Long> userIds = orders.stream()
    .map(Order::getUserId)
    .collect(Collectors.toSet());

案例22:LIMIT的优化

问题:随机获取10条记录

错误写法:

SELECT * FROM articles 
ORDER BY RAND() 
LIMIT 10;
-- 需要排序全表,非常慢

优化方案:

-- 先获取随机ID
SELECT id FROM articles 
WHERE id >= (
  SELECT FLOOR(RAND() * (
    SELECT MAX(id) FROM articles
  ))
) LIMIT 10;

-- 再根据ID查询
SELECT * FROM articles WHERE id IN (...);

案例23:JOIN的优化技巧

慢查询:

SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.city = '北京';
-- 全表扫描

问题:小表驱动大表的原则被违反

优化方案:

-- 先过滤小表
SELECT * FROM orders o
INNER JOIN (
  SELECT id FROM users WHERE city = '北京'
) u ON o.user_id = u.id;

JOIN优化原则:

小表驱动大表
被驱动表的连接字段必须有索引
能用INNER JOIN就不用LEFT JOIN

案例24:IN vs EXISTS的选择

场景1:外表大,内表小 - 用IN

SELECT * FROM orders 
WHERE user_id IN (
  SELECT id FROM vip_users  -- 1000条
);

场景2:外表小,内表大 - 用EXISTS

SELECT * FROM vip_users u
WHERE EXISTS (
  SELECT 1 FROM orders o  -- 1000万条
  WHERE o.user_id = u.id
);

原理:

IN: 子查询先执行,结果集在内存中匹配
EXISTS: 外查询驱动,对每行检查是否存在

案例25:避免在WHERE中使用表达式

错误示例:

SELECT * FROM orders 
WHERE amount + tax > 1000;
-- 索引失效

正确写法:

SELECT * FROM orders 
WHERE amount > 1000 - tax;

通用原则:让索引列单独出现在比较符左侧

案例26:UNION vs UNION ALL

场景:合并两个查询结果

低效写法:

SELECT * FROM orders WHERE status = 1
UNION
SELECT * FROM orders WHERE status = 2;
-- 会去重,需要排序

高效写法:

SELECT * FROM orders WHERE status = 1
UNION ALL
SELECT * FROM orders WHERE status = 2;
-- 不去重,直接合并

性能差异:UNION ALL比UNION快3-5倍

使用原则:确定无重复时,永远用UNION ALL

案例27:避免使用负向查询

慢查询:

SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status NOT IN (1,2,3);
-- 无法使用索引

优化方案:改成正向查询

SELECT * FROM users WHERE status IN (0,4,5,6);

案例28:ORDER BY的优化

问题SQL:

SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY amount DESC 
LIMIT 10;
-- 使用了文件排序,很慢

优化方案:建立联合索引

CREATE INDEX idx_user_amount 
ON orders(user_id, amount);
-- 利用索引有序性,避免排序

注意:ORDER BY字段必须是索引的一部分,且顺序一致

案例29:避免多次查询

低效代码(N+1问题):

List<Order> orders = orderDao.findByUserId(123);
for (Order order : orders) {
    User user = userDao.findById(order.getUserId());
    order.setUser(user);
}
// 执行了1+N次查询

优化方案:一次查询

List<Order> orders = orderDao.findByUserIdWithUser(123);
// 使用JOIN一次查询所有数据

案例30:批量操作的优化

低效代码:

for (int i = 0; i < 10000; i++) {
    jdbcTemplate.execute("INSERT INTO logs VALUES (...)");
}
// 10000次网络IO

优化方案:批量插入

INSERT INTO logs VALUES 
(1, 'log1'),
(2, 'log2'),
...
(10000, 'log10000');
-- 一次网络IO

性能提升:100倍以上

注意事项:

每批不超过1000条
超大批量分批执行

第三章:锁和并发控制(案例31-45)

案例31:经典死锁案例

报警:订单系统出现大量死锁

场景还原:

-- 事务1
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 1;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 2;
COMMIT;

-- 事务2(同时执行)
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 2;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

死锁原因:两个事务以不同顺序锁定资源

解决方案:统一加锁顺序

-- 所有事务都按product_id从小到大加锁
UPDATE inventory SET stock = stock - 1 
WHERE product_id IN (1, 2) 
ORDER BY product_id;

案例32:行锁升级为表锁

问题:执行UPDATE时整张表被锁住

问题SQL:

UPDATE orders SET status = 2 
WHERE order_no = 'ORDER001';
-- order_no没有索引,触发全表扫描,锁住整张表

解决方案:

CREATE INDEX idx_order_no ON orders(order_no);
-- 使用索引,只锁定匹配的行

关键点:InnoDB的行锁是基于索引实现的,没有索引就会退化成表锁

案例33:间隙锁引发的阻塞

场景:

-- 会话1
BEGIN;
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- id=10的记录不存在,锁定了(5, 15)的间隙

-- 会话2
INSERT INTO users (id, name) VALUES (8, '张三');
-- 被阻塞!

原因:RR隔离级别下,为防止幻读,锁定了间隙

解决方案1:使用RC隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

解决方案2:避免锁不存在的记录

SELECT * FROM users WHERE id = 10;
IF (record_exists) {
  SELECT * FROM users WHERE id = 10 FOR UPDATE;
}

案例34:长事务导致的锁等待

报警:大量请求堆积,数据库连接池耗尽

排查命令:

SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 10;
-- 发现有个事务已经运行了2小时!

问题代码:

@Transactional
public void processOrder() {
    updateOrder();        // 1ms
    callThirdPartyAPI();  // 5秒
    sendEmail();          // 10秒
    // 事务持续15秒,期间一直持有锁
}

优化方案:缩小事务范围

public void processOrder() {
    updateOrderInTransaction();  // 单独事务,1ms完成
    callThirdPartyAPI();         // 事务外执行
    sendEmail();                 // 事务外执行
}

@Transactional
private void updateOrderInTransaction() {
    updateOrder();
}

案例35:乐观锁vs悲观锁

场景:秒杀扣库存

悲观锁(并发低):

BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
IF (stock > 0) {
  UPDATE products SET stock = stock - 1 WHERE id = 1;
}
COMMIT;

乐观锁(并发高):

-- 先查询
SELECT stock, version FROM products WHERE id = 1;

-- 更新时检查版本号
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = {old_version} AND stock > 0;

-- 如果affected_rows=0,说明被别人抢先了,重试

性能对比:

悲观锁:1000 QPS
乐观锁:15000 QPS

案例36:MVCC解决读写冲突

问题:读操作被写操作阻塞

传统锁机制:

-- 会话1
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 持有写锁

-- 会话2
SELECT balance FROM users WHERE id = 1;
-- 被阻塞,等待会话1释放锁

MVCC机制(InnoDB默认):

-- 会话2的SELECT不会被阻塞
-- 读取的是事务开始时的快照版本
SELECT balance FROM users WHERE id = 1;
-- 立即返回,不受会话1影响

原理:每行记录保存多个版本,读取时根据事务ID选择合适版本

案例37:SELECT FOR UPDATE的使用陷阱

错误用法:

SELECT * FROM orders WHERE status = 1 FOR UPDATE;
-- 锁定了几千行数据!

正确用法:精确锁定

SELECT * FROM orders WHERE id = 12345 FOR UPDATE;
-- 只锁定1行

业务场景:

// 防止重复支付
Order order = orderDao.findByIdForUpdate(orderId);
if (order.getStatus() == UNPAID) {
    order.setStatus(PAID);
    orderDao.update(order);
}

案例38:分布式锁解决跨库并发

场景:分库分表后,无法使用数据库锁

Redis实现:

String lockKey = "lock:order:" + orderId;
boolean locked = redis.setNX(lockKey, "1", 10); // 10秒过期

if (locked) {
    try {
        // 执行业务逻辑
        processOrder(orderId);
    } finally {
        redis.del(lockKey);
    }
}

注意事项:

必须设置过期时间,防止死锁
解锁时要验证是否是自己加的锁
考虑使用Redisson等成熟方案

案例39:批量更新的锁优化

低效代码:

for (int i = 0; i < 10000; i++) {
    orderDao.updateStatus(orderIds.get(i), 2);
    // 每次都加锁、释放锁
}

优化方案:

UPDATE orders SET status = 2 
WHERE id IN (1,2,3,...,10000);
-- 一次性获取所有锁,批量更新

性能提升:从5分钟降至3秒

案例40:热点行更新的优化

问题:商品详情页的浏览计数,高并发下锁冲突严重

传统方式(慢):

UPDATE products SET view_count = view_count + 1 
WHERE id = 1;
-- 1000个并发请求排队更新同一行

优化方案1:分段计数

-- 将计数分散到10个字段
UPDATE products SET view_count_{random(0-9)} = view_count_{random(0-9)} + 1 
WHERE id = 1;
-- 查询时汇总10个字段

优化方案2:异步更新

redis.incr("product:1:view_count");
// 定时任务每分钟从Redis同步到MySQL

案例41:INSERT的锁冲突

场景:高并发插入订单

问题:自增主键锁冲突

-- innodb_autoinc_lock_mode=0时,每次INSERT都锁表
INSERT INTO orders (...) VALUES (...);

优化方案:

SET GLOBAL innodb_autoinc_lock_mode=2;
-- 使用轻量级锁,提升并发性能

注意:主从复制使用ROW格式,避免数据不一致

案例42:锁等待超时的处理

报警:Lock wait timeout exceeded

排查:

-- 查看正在执行的事务
SELECT * FROM information_schema.innodb_trx;

-- 查看锁等待关系
SELECT * FROM information_schema.innodb_lock_waits;

-- 找到阻塞源头
SELECT * FROM information_schema.innodb_locks;

解决方案:

-- 杀死阻塞的事务
KILL {trx_mysql_thread_id};

预防措施:

增加锁等待超时时间:SET innodb_lock_wait_timeout=50;
优化慢SQL,减少事务执行时间
避免大事务

案例43:幻读的处理

场景:统计订单数量

问题代码:

-- 事务1
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 123; -- 结果:10

-- 事务2(同时执行)
INSERT INTO orders (user_id, ...) VALUES (123, ...);
COMMIT;

-- 事务1再次查询
SELECT COUNT(*) FROM orders WHERE user_id = 123; -- 结果:11
-- 出现幻读!

解决方案:使用锁

BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 123 FOR UPDATE;
-- 锁定查询范围,防止新增数据

案例44:MDL锁导致的库崩溃

场景:执行ALTER TABLE时,数据库卡死

原因:

-- 会话1:长事务未提交
BEGIN;
SELECT * FROM users;
-- 持有MDL读锁

-- 会话2:执行DDL
ALTER TABLE users ADD COLUMN age INT;
-- 等待MDL写锁

-- 会话3-1000:正常查询
SELECT * FROM users;
-- 全部被阻塞!

解决方案:

杀死长事务
使用pt-online-schema-change工具
在业务低峰期执行DDL

案例45:MySQL 8.0的锁改进

新特性:SKIP LOCKED

场景:任务队列

传统方式:

SELECT * FROM tasks WHERE status = 0 LIMIT 1 FOR UPDATE;
-- 如果这行被锁,整个查询阻塞

MySQL 8.0:

SELECT * FROM tasks WHERE status = 0 LIMIT 1 
FOR UPDATE SKIP LOCKED;
-- 跳过被锁的行,直接处理下一个任务

应用场景:

多个Worker并发处理任务
秒杀库存扣减
分布式任务调度

第四章:连接池与资源管理(案例46-55)

案例46:连接池耗尽导致雪崩

报警:应用无法连接数据库,大量请求超时

排查:

// 连接池配置
maxActive=20
maxWait=5000

// 数据库最大连接数
max_connections=100

问题现象:

20个连接被长时间占用
新请求拿不到连接,超时失败
用户重试,请求堆积,系统崩溃

解决方案:

// 增加连接池大小
maxActive=50

// 设置连接超时回收
removeAbandonedTimeout=60
removeAbandoned=true

// 测试连接有效性
testWhileIdle=true
timeBetweenEvictionRunsMillis=60000

案例47:连接泄漏检测

问题:连接数持续增长,最终耗尽

定位代码:

Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.executeQuery();
// 忘记关闭conn!

监控方案:

// Druid连接池配置
removeAbandoned=true
removeAbandonedTimeout=180
logAbandoned=true
// 超过3分钟未归还的连接,打印堆栈日志

日志输出:

Abandoned connection detected at:
  com.example.OrderService.createOrder(OrderService.java:45)
  ...

根本解决:使用try-with-resources

try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.executeQuery();
}
// 自动关闭连接

案例48:连接池参数优化

默认配置(性能差):

initialSize=1
minIdle=1
maxActive=8
maxWait=10000

优化后:

# 初始连接数(应用启动立即可用)
initialSize=10

# 最小空闲连接(快速响应突发流量)
minIdle=10

# 最大连接数(根据业务峰值调整)
maxActive=50

# 获取连接超时时间(快速失败)
maxWait=3000

# 连接有效性检测
testOnBorrow=false
testWhileIdle=true
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000

# 性能监控
filters=stat,wall,log4j

计算公式:

maxActive = (核心数 * 2) + 磁盘数量
// 例如:8核CPU + 2块磁盘 = 18个连接

案例49:读写分离的连接池配置

场景:主库写,从库读

错误配置:

// 主库和从库共用一个连接池
DataSource ds = new DruidDataSource();
ds.setMaxActive(50);

问题:读操作占满连接池,写操作无法执行

正确配置:

// 主库连接池
DataSource master = new DruidDataSource();
master.setMaxActive(20); // 写操作少,连接少

// 从库连接池
DataSource slave = new DruidDataSource();
slave.setMaxActive(100); // 读操作多,连接多

// 动态路由
@Transactional(readOnly = true)
public List<Order> findOrders() {
    // 自动路由到从库
}

案例50:连接池预热

问题:应用刚启动时,第一批请求很慢

原因:连接池是懒加载,首次请求才建立连接

解决方案:启动时预热

@PostConstruct
public void warmUp() {
    List<Connection> connections = new ArrayList<>();
    try {
        // 创建initialSize个连接
        for (int i = 0; i < dataSource.getInitialSize(); i++) {
            connections.add(dataSource.getConnection());
        }
    } finally {
        // 归还连接
        for (Connection conn : connections) {
            conn.close();
        }
    }
}

案例51:数据库连接数监控

监控指标:

-- 当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';

-- 拒绝连接次数
SHOW STATUS LIKE 'Connection_errors_max_connections';

告警规则:

当前连接数 > max_connections * 0.8 → 预警
当前连接数 > max_connections * 0.9 → 严重告警
拒绝连接次数 > 0 → 紧急告警

Grafana监控面板:

(mysql_global_status_threads_connected / 
 mysql_global_variables_max_connections) * 100

案例52:连接超时参数调优

网络抖动导致连接失败:

问题配置:

connectTimeout=1000 # 1秒太短
socketTimeout=3000  # 3秒也不够

优化配置:

# 连接建立超时(考虑网络延迟)
connectTimeout=5000

# 读取数据超时(考虑慢查询)
socketTimeout=30000

# 空闲连接检测
testWhileIdle=true
validationQuery=SELECT 1
validationQueryTimeout=3

案例53:连接池隔离

场景:批量任务和在线业务共用连接池

问题:批量任务占满连接池,影响在线业务

解决方案:连接池隔离

// 在线业务连接池
@Bean("onlineDataSource")
public DataSource onlineDataSource() {
    DruidDataSource ds = new DruidDataSource();
    ds.setMaxActive(50);
    return ds;
}

// 批量任务连接池
@Bean("batchDataSource")
public DataSource batchDataSource() {
    DruidDataSource ds = new DruidDataSource();
    ds.setMaxActive(10);
    return ds;
}

案例54:连接池与事务的配合

问题:连接池连接不够,但数据库连接数很少

原因:长事务持有连接不释放

@Transactional
public void importData() {
    for (int i = 0; i < 100000; i++) {
        save(data[i]); // 事务持续1小时
    }
}

解决方案:分批提交

public void importData() {
    for (int i = 0; i < 100000; i += 1000) {
        importBatch(data, i, i + 1000);
    }
}

@Transactional
public void importBatch(Data[] data, int start, int end) {
    for (int i = start; i < end; i++) {
        save(data[i]);
    }
    // 每1000条提交一次,释放连接
}

案例55:连接池的优雅关闭

问题:应用重启时,连接池强制关闭,导致数据丢失

解决方案:

@PreDestroy
public void shutdown() {
    // 1. 停止接收新请求
    tomcat.pause();
    
    // 2. 等待现有请求处理完成
    Thread.sleep(10000);
    
    // 3. 关闭连接池
    dataSource.close();
}

Spring Boot配置:

server.shutdown=graceful
spring.lifecycle.timeout-per-shutdown-phase=30s

第五章:表结构设计(案例56-65)

案例56:VARCHAR长度的选择

问题:VARCHAR(255) vs VARCHAR(1000)

错误认知:"反正实际存储按内容长度,设大点没关系"

真相:

-- VARCHAR(255):索引最多占用 255*3+2 = 767字节
-- VARCHAR(1000):索引最多占用 1000*3+2 = 3002字节

-- InnoDB索引长度限制:767字节(MySQL 5.6)
CREATE INDEX idx_long ON users(description(1000));
-- 报错:Specified key was too long

最佳实践:

手机号:VARCHAR(11)
邮箱:VARCHAR(100)
姓名:VARCHAR(50)
地址:VARCHAR(200)
长文本:TEXT

原则:够用就好,不要预留太多冗余

案例57:INT vs BIGINT的选择

场景:用户ID字段

错误选择:INT (42亿)

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT
);
-- 用户量超过21亿后溢出!

正确选择:BIGINT

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT
);
-- 支持到 9223372036854775807

存储对比:

TINYINT: 1字节 (-128 ~ 127)
SMALLINT: 2字节 (-32768 ~ 32767)
INT: 4字节 (-21亿 ~ 21亿)
BIGINT: 8字节 (足够使用)

经验值:

状态字段:TINYINT
数量字段:INT
ID字段:BIGINT
金额字段:DECIMAL

案例58:DECIMAL vs FLOAT的金额存储

致命错误:用FLOAT存储金额

CREATE TABLE orders (
  amount FLOAT
);

INSERT INTO orders VALUES (0.1);
SELECT amount FROM orders;
-- 结果:0.100000001490116
-- 精度丢失,资金损失!

正确方案:DECIMAL

CREATE TABLE orders (
  amount DECIMAL(10, 2)
);
-- 10位总长度,2位小数
-- 存储范围:-99999999.99 ~ 99999999.99

案例:

商品价格:DECIMAL(10, 2)
账户余额:DECIMAL(15, 2)
汇率:DECIMAL(10, 6)

案例59:时间字段的选择

方案对比:

1. INT存储时间戳

created_at INT -- 存储1702531200

优点:存储小(4字节),跨时区方便
缺点:可读性差,范围有限(2038年问题)

2. DATETIME

created_at DATETIME -- 存储'2024-12-14 10:30:00'

优点:可读性好,范围大(1000-9999年)
缺点:占用8字节,不支持时区

3. TIMESTAMP(推荐)

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

优点:自动管理,支持时区,占用4字节
缺点:范围限制(1970-2038)

最佳实践:

创建时间:TIMESTAMP
生日:DATE
历史事件:DATETIME

案例60:枚举字段的设计

方案1:ENUM(不推荐)

status ENUM('待支付', '已支付', '已退款')

问题:新增状态需要ALTER TABLE

方案2:TINYINT + 配置表(推荐)

-- 订单表
status TINYINT COMMENT '1待支付 2已支付 3已退款'

-- 状态配置表
CREATE TABLE order_status (
  id TINYINT PRIMARY KEY,
  name VARCHAR(20),
  description VARCHAR(100)
);

方案3:字符串(灵活但占空间)

status VARCHAR(20)

案例61:JSON字段的使用

场景:商品的扩展属性(颜色、尺寸等)

传统方案(EAV模型):

CREATE TABLE product_attrs (
  product_id BIGINT,
  attr_name VARCHAR(50),
  attr_value VARCHAR(200)
);
-- 查询复杂,性能差

JSON方案(MySQL 5.7+):

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100),
  attrs JSON
);

INSERT INTO products VALUES (
  1, 'iPhone', 
  '{"color":"黑色","storage":"256GB","screen":"6.1英寸"}'
);

-- 查询
SELECT * FROM products 
WHERE JSON_EXTRACT(attrs, '$.color') = '黑色';

-- 创建虚拟索引(MySQL 8.0)
ALTER TABLE products ADD color VARCHAR(20) 
AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.color')));
CREATE INDEX idx_color ON products(color);

适用场景:

属性不固定
查询频率低
业务快速迭代

注意:不要滥用,核心字段仍用普通列

案例62:冗余字段的权衡

场景:订单表需要显示用户姓名

方案1:每次JOIN查询(规范但慢)

SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id;

方案2:冗余用户名(快但冗余)

CREATE TABLE orders (
  id BIGINT,
  user_id BIGINT,
  user_name VARCHAR(50), -- 冗余字段
  amount DECIMAL(10,2)
);

决策依据:

✅ 用户名几乎不变 → 可以冗余
✅ 查询频繁,性能要求高 → 可以冗余
❌ 字段经常变化 → 不要冗余
❌ 数据一致性要求严格 → 不要冗余

经验值:

订单冗余用户名:✅
订单冗余用户余额:❌
订单冗余商品名称:✅
订单冗余商品库存:❌

案例63:软删除的实现

方案1:deleted字段

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  deleted TINYINT DEFAULT 0,
  INDEX idx_deleted (deleted)
);

-- 查询未删除
SELECT * FROM users WHERE deleted = 0;

-- 软删除
UPDATE users SET deleted = 1 WHERE id = 123;

问题:唯一索引失效

UNIQUE KEY uk_email (email)
-- 删除后无法用相同邮箱注册

方案2:deleted_at + 组合唯一索引

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(100),
  deleted_at TIMESTAMP NULL,
  UNIQUE KEY uk_email_deleted (email, deleted_at)
);

-- 未删除:deleted_at = NULL
-- 已删除:deleted_at = 删除时间

案例64:分表字段的选择

场景:订单表按月分表

方案1:按创建时间

orders_202401
orders_202402
...

问题:查询"某个用户的所有订单"需要查N张表

方案2:按user_id哈希

orders_0  # user_id % 10 = 0
orders_1  # user_id % 10 = 1
...

问题:按时间查询需要查所有表

折衷方案:时间 + 用户ID组合

orders_202401_0
orders_202401_1
...
-- 先按时间定位月份,再按user_id定位分表

选择原则:根据最常见的查询条件决定

案例65:字符集与排序规则

问题:表情符号存储报错

原因:

-- UTF8最多3字节,无法存储4字节的emoji
CREATE TABLE posts (
  content VARCHAR(1000) CHARACTER SET utf8
);

INSERT INTO posts VALUES ('今天很开心😊');
-- Error: Incorrect string value

解决方案:

CREATE TABLE posts (
  content VARCHAR(1000) CHARACTER SET utf8mb4
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci;

字符集选择:

utf8mb4:支持emoji,推荐
utf8:兼容老系统
utf8mb4_0900_ai_ci:MySQL 8.0默认,性能更好

全库统一:

ALTER DATABASE mydb CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

第六章:大表优化(案例66-75)

案例66:单表数据量控制

血泪教训:订单表5亿数据,查询超时,备份失败

分析:

单表 < 2000万:性能良好
2000万 - 5000万:性能下降明显
> 5000万:维护困难,建议分表

方案1:按时间归档

-- 保留近3个月数据
orders (活跃表)
orders_archive_2024Q1 (归档表)
orders_archive_2024Q2
...

方案2:垂直拆分

-- 常用字段
orders (id, user_id, amount, status, create_time)
-- 详细信息
orders_detail (order_id, address, remark, ...)

案例67:在线DDL的实践

传统DDL:(锁表,业务中断)

ALTER TABLE orders ADD COLUMN remark VARCHAR(200);
-- 5000万数据,锁表2小时

pt-osc工具:(不锁表)

pt-online-schema-change \
  --alter "ADD COLUMN remark VARCHAR(200)" \
  D=mydb,t=orders \
  --execute

原理:

创建新表(带新字段)
创建触发器,同步新旧表数据
分批复制历史数据
切换表名

MySQL 8.0 Online DDL:

ALTER TABLE orders 
ADD COLUMN remark VARCHAR(200),
ALGORITHM=INSTANT; -- 秒级完成

案例68:大表删除数据

错误方式:

DELETE FROM orders WHERE create_time < '2020-01-01';
-- 删除500万数据,锁表,主从延迟

优化方案:分批删除

WHILE (affected_rows > 0) DO
  DELETE FROM orders 
  WHERE create_time < '2020-01-01' 
  LIMIT 1000;
  -- 每批1000条,休眠1秒
  SELECT SLEEP(1);
END WHILE;

更好方案:分区表

ALTER TABLE orders DROP PARTITION p2020Q1;
-- 秒级删除整个分区的数据

案例69:分区表的应用

场景:日志表,按天分区

创建分区表:

CREATE TABLE access_log (
  id BIGINT,
  user_id BIGINT,
  access_time DATETIME,
  ...
) PARTITION BY RANGE (TO_DAYS(access_time)) (
  PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-02')),
  PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03')),
  ...
);

查询优化:

SELECT * FROM access_log 
WHERE access_time BETWEEN '2024-01-01' AND '2024-01-02';
-- 只扫描p20240101分区,速度快100倍

删除旧数据:

ALTER TABLE access_log DROP PARTITION p20231201;
-- 秒删

自动化管理:

-- 每天创建新分区
CREATE EVENT create_partition
ON SCHEDULE EVERY 1 DAY
DO CALL create_partition_for_tomorrow();

案例70:分库分表策略

场景:用户表1亿数据

分表方案:

user_0, user_1, ..., user_99
-- 按 user_id % 100 路由

Java代码:

public UserMapper getUserMapper(Long userId) {
    int tableIndex = userId % 100;
    return mappers.get("user_" + tableIndex);
}

分库分表:

db0.user_0 ~ db0.user_9   (10个表)
db1.user_10 ~ db1.user_19 (10个表)
...
db9.user_90 ~ db9.user_99 (10个表)
-- 10库100表

中间件:

ShardingJDBC:客户端分片
MyCat:代理层分片
TDDL:阿里开源

案例71:分表后的分页查询

问题:跨分表分页

需求:查询第2页,每页20条

-- 需要查询所有分表,取排序后的第21-40条
SELECT * FROM (
  SELECT * FROM user_0 ORDER BY create_time LIMIT 40
  UNION ALL
  SELECT * FROM user_1 ORDER BY create_time LIMIT 40
  ...
  UNION ALL
  SELECT * FROM user_99 ORDER BY create_time LIMIT 40
) t ORDER BY create_time LIMIT 20 OFFSET 20;

性能问题:查询100张表,非常慢

优化方案:限制跳页深度,只允许翻前10页

折中方案:使用ID游标

Long lastUserId = getLastUserIdFromPreviousPage();
// 每个分表查询 user_id > lastUserId 的前20条
// 合并排序后取前20条

案例72:分表后的聚合查询

需求:统计所有用户的订单总金额

方案1:应用层聚合

BigDecimal total = BigDecimal.ZERO;
for (int i = 0; i < 100; i++) {
    total = total.add(queryTable("orders_" + i));
}

方案2:汇总表

-- 定时任务每小时汇总
CREATE TABLE orders_summary (
  stat_time DATETIME,
  total_amount DECIMAL(20,2)
);

方案3:实时流计算(Flink)

案例73:大表count(*)优化

问题:

SELECT COUNT(*) FROM orders;
-- 1亿数据,执行30秒

方案1:使用近似值

SELECT table_rows FROM information_schema.tables 
WHERE table_name = 'orders';
-- 误差5%-10%,但瞬间返回

方案2:缓存计数

// Redis存储
redis.set("orders:count", 100000000);

// 增删时更新
redis.incr("orders:count");
redis.decr("orders:count");

方案3:定时统计

-- 每小时更新一次精确值
INSERT INTO statistics 
SELECT NOW(), COUNT(*) FROM orders;

案例74:大表JOIN优化

慢查询:

SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id;
-- orders 1000万, order_items 5000万
-- 执行2分钟

优化方案1:分批JOIN

for (int i = 0; i < 10; i++) {
    Long startId = i * 1000000;
    Long endId = (i + 1) * 1000000;
    String sql = "SELECT * FROM orders o " +
        "JOIN order_items oi ON o.id = oi.order_id " +
        "WHERE o.id BETWEEN " + startId + " AND " + endId;
}

优化方案2:宽表设计

-- 订单创建时,直接写入宽表
CREATE TABLE order_detail (
  order_id BIGINT,
  user_name VARCHAR(50),
  product_name VARCHAR(200),
  amount DECIMAL(10,2),
  ...
);
-- 空间换时间,查询瞬间完成

案例75:大表全表扫描优化

场景:数据导出

慢方式:

SELECT * FROM orders; -- 5000万数据,OOM

优化方案:流式查询

// MyBatis配置
@Options(resultSetType = ResultSetType.FORWARD_ONLY, 
         fetchSize = 1000)
List<Order> streamOrders();

// 使用
try (SqlSession session = sqlSessionFactory.openSession()) {
    session.select("streamOrders", resultContext -> {
        Order order = resultContext.getResultObject();
        // 处理单条数据
        exportToFile(order);
    });
}

第七章:缓存策略(案例76-82)

案例76:缓存穿透

问题:查询不存在的数据,缓存和数据库都查不到,每次请求都打到数据库

攻击场景:

for (int i = -100000; i < 0; i++) {
    getUser(i); // 查询负数ID,肯定不存在
    // 每次都查数据库,数据库被打挂
}

解决方案1:缓存空值

User user = redis.get("user:" + id);
if (user == null) {
    user = db.query(id);
    if (user == null) {
        // 缓存空值,过期时间短
        redis.setex("user:" + id, "null", 60);
    } else {
        redis.setex("user:" + id, user, 3600);
    }
}

解决方案2:布隆过滤器

// 启动时加载所有user_id到布隆过滤器
BloomFilter<Long> filter = BloomFilter.create(...);
allUserIds.forEach(filter::put);

// 查询前先判断
if (!filter.mightContain(userId)) {
    return null; // 100%不存在,直接返回
}

案例77:缓存击穿

问题:热点key过期瞬间,大量请求打到数据库

场景:

// 00:00:00 热门商品缓存过期
// 00:00:01 1000个请求同时发现缓存不存在
// 1000个请求同时查数据库,数据库瞬间崩溃

解决方案1:永不过期

redis.set("hot:product:1", productJson);
// 定时任务后台刷新

解决方案2:分布式锁

String lockKey = "lock:product:" + id;
if (redis.setNX(lockKey, "1", 10)) {
    try {
        Product p = db.query(id);
        redis.setex("product:" + id, p, 3600);
        return p;
    } finally {
        redis.del(lockKey);
    }
} else {
    Thread.sleep(100);
    return getProduct(id); // 重试
}

解决方案3:提前刷新

// 缓存过期时间30分钟
// 但在25分钟时异步刷新
if (redis.ttl(key) < 300) {
    asyncRefresh(key);
}

案例78:缓存雪崩

问题:大量key同时过期,数据库瞬间压力剧增

场景:

// 批量导入数据,所有缓存过期时间都是3600秒
for (Product p : products) {
    redis.setex("product:" + p.getId(), p, 3600);
}
// 1小时后,所有缓存同时失效

解决方案:过期时间加随机值

int expire = 3600 + random.nextInt(300);
redis.setex(key, value, expire);
// 分散过期时间

高可用方案:Redis集群

主从 + 哨兵
Redis Cluster

案例79:缓存与数据库一致性

问题:更新数据库后,缓存未更新

方案1:先删缓存,再更新数据库(不推荐)

redis.del("user:" + id);
db.update(user);
// 问题:并发时可能读到旧数据

方案2:先更新数据库,再删缓存(推荐)

db.update(user);
redis.del("user:" + id);
// 下次查询时重新加载

方案3:延时双删

db.update(user);
redis.del("user:" + id);
Thread.sleep(500);
redis.del("user:" + id); // 再删一次

方案4:订阅binlog(最佳)

// Canal监听MySQL binlog
canal.subscribe("user", event -> {
    redis.del("user:" + event.getUserId());
});

案例80:热key问题

问题:某个key访问量巨大,单个Redis节点成为瓶颈

场景:明星微博、热门商品详情

监控:

redis-cli --hotkeys

解决方案1:本地缓存

// Caffeine本地缓存
LoadingCache<String, Product> cache = Caffeine.newBuilder()
    .expireAfterWrite(1, TimeUnit.MINUTES)
    .build(id -> redis.get("product:" + id));

解决方案2:多级缓存

请求 → 本地缓存(Caffeine) 
     → Redis缓存 
     → 数据库

解决方案3:热key拆分

// 原来
redis.get("hot:product:1");

// 拆分为10个副本
int shard = random.nextInt(10);
redis.get("hot:product:1:" + shard);

案例81:大key问题

问题:单个key存储数据过大,导致网络传输慢、阻塞其他请求

案例:

// 一个List存储100万条数据,占用500MB
redis.lrange("big:list", 0, -1);
// 单次查询5秒,阻塞整个Redis

监控:

redis-cli --bigkeys

解决方案1:拆分

// 按时间分片
redis.lpush("list:2024-01-01", item);
redis.lpush("list:2024-01-02", item);

解决方案2:压缩

String json = JSON.toJSONString(data);
byte[] compressed = gzip(json);
redis.set(key, compressed);

解决方案3:限制大小

// List限制1000条
if (redis.llen(key) > 1000) {
    redis.rpop(key); // 删除最老的
}

案例82:缓存预热

问题:系统刚启动,缓存为空,大量请求打到数据库

解决方案:启动时预热

@PostConstruct
public void warmUp() {
    // 加载热门商品
    List<Product> hotProducts = productService.getHotProducts();
    hotProducts.forEach(p -> {
        redis.setex("product:" + p.getId(), p, 3600);
    });
    
    // 加载配置
    Map<String, String> configs = configService.getAll();
    redis.hmset("configs", configs);
}

定时预热:

@Scheduled(cron = "0 0 2 * * ?") // 每天凌晨2点
public void refreshCache() {
    // 刷新全量缓存
}

第八章:监控告警(案例83-90)

案例83:慢查询监控

开启慢查询日志:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

分析工具:

mysqldumpslow -s t -t 10 slow.log
# 按时间排序,取前10条

告警规则:

慢查询数 > 100/小时 → 预警
单条SQL > 10秒 → 严重告警
全表扫描 > 50次/小时 → 预警

自动化处理:

# 每小时分析慢查询,自动创建优化工单
def analyze_slow_query():
    slow_queries = parse_slow_log()
    for query in slow_queries:
        if not has_index(query):
            create_ticket(f"请为{query}添加索引")

案例84:连接数监控

监控指标:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';

Prometheus配置:

- job_name: 'mysql'
  static_configs:
    - targets: ['mysql-exporter:9104']
  metrics_path: /metrics

Grafana面板:

mysql_global_status_threads_connected
mysql_global_status_threads_running
mysql_global_status_max_used_connections

告警规则:

- alert: MySQLTooManyConnections
  expr: mysql_global_status_threads_connected > 80
  for: 5m
  annotations:
    summary: "MySQL连接数过高"

案例85:主从延迟监控

查看延迟:

SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 5

延迟原因:

主库写入量大
从库配置低
大事务阻塞
网络延迟

优化方案:

-- 从库开启并行复制
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

告警:

延迟 > 5秒 → 预警
延迟 > 30秒 → 严重告警
延迟 > 60秒 → 紧急,禁止从库查询

案例86:磁盘空间监控

监控命令:

SELECT 
  table_schema,
  SUM(data_length + index_length) / 1024 / 1024 / 1024 AS size_gb
FROM information_schema.tables
GROUP BY table_schema;

自动清理:

-- 删除7天前的日志
DELETE FROM access_log 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)
LIMIT 10000;

告警规则:

磁盘使用率 > 70% → 预警
磁盘使用率 > 85% → 严重告警
磁盘使用率 > 95% → 紧急,禁止写入

案例87:QPS/TPS监控

计算QPS:

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';
-- QPS = Questions / Uptime

实时监控:

mysqladmin -uroot -p extended-status -r -i 1 | grep Questions

性能基准:

普通服务器:5000 QPS
高性能SSD:20000 QPS
分布式集群:100000+ QPS

告警:

QPS突增 > 平时2倍 → 可能被攻击
QPS突降 < 平时50% → 服务异常

案例88:死锁监控

查看死锁日志:

SHOW ENGINE INNODB STATUS;
-- 查找 LATEST DETECTED DEADLOCK 部分

自动化监控:

def check_deadlock():
    status = mysql.query("SHOW ENGINE INNODB STATUS")
    if "DEADLOCK" in status:
        alert("检测到死锁", status)
        analyze_deadlock(status)

预防策略:

统一加锁顺序
缩短事务时间
降低隔离级别(RC)
使用乐观锁

案例89:表碎片监控

检查碎片:

SELECT 
  table_name,
  data_free / 1024 / 1024 AS fragment_mb,
  data_free / (data_length + index_length) * 100 AS fragment_pct
FROM information_schema.tables
WHERE table_schema = 'mydb'
  AND data_free > 0
ORDER BY fragment_mb DESC;

自动优化:

-- 碎片率 > 20% 的表,定期优化
OPTIMIZE TABLE orders;

调度任务:

# crontab
0 3 * * 0 mysql -e "OPTIMIZE TABLE mydb.orders"

案例90:业务指标监控

核心指标:

-- 订单量
SELECT COUNT(*) FROM orders 
WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 成功率
SELECT 
  COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) * 100.0 / COUNT(*) AS success_rate
FROM orders
WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 平均订单金额
SELECT AVG(amount) FROM orders
WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);

异常检测:

订单量突降 > 30% → 系统故障
成功率 < 95% → 支付异常
平均金额突增 > 5倍 → 可能被刷单

第九章:容灾与高可用(案例91-95)

案例91:主从切换

故障场景:主库宕机

手动切换:

-- 从库提升为主库
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = 0;

-- 应用连接切换到新主库

自动切换(MHA):

# MHA监控主库
# 检测到主库宕机,自动:
# 1. 选举最新的从库
# 2. 补齐差异日志
# 3. 提升为新主库
# 4. 其他从库指向新主库
# 5. 修改VIP指向

业务无感知:

客户端连接VIP → VIP自动漂移到新主库
切换时间 < 30秒

案例92:数据备份策略

全量备份:

# 每天凌晨2点
mysqldump -uroot -p --single-transaction \
  --master-data=2 --all-databases > backup.sql

# 上传到OSS
ossutil cp backup.sql oss://backup/$(date +%Y%m%d).sql

增量备份:

# 开启binlog
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7

# 定时归档binlog
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

备份验证:

# 每周从备份恢复到测试环境
mysql test_db < backup.sql
# 验证数据完整性

案例93:误删数据恢复

场景:DELETE语句没加WHERE,删除了全表数据

恢复步骤:

1. 立即停止应用写入

dataSource.close(); // 防止继续破坏数据

2. 从binlog恢复

# 找到误删除的时间点
mysqlbinlog mysql-bin.000010 | grep "DELETE FROM orders"

# 恢复到误删除前一刻
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" \
  mysql-bin.* | mysql -uroot -p

# 跳过误删除语句,继续恢复后续正常操作
mysqlbinlog --start-datetime="2024-01-15 10:30:05" \
  mysql-bin.* | mysql -uroot -p

3. 验证数据完整性

案例94:跨机房容灾

架构:

机房A: 主库 + 从库1
机房B: 从库2 + 从库3

异地容灾:

-- 机房B的从库配置
CHANGE MASTER TO
  MASTER_HOST='机房A主库',
  MASTER_AUTO_POSITION=1,
  MASTER_DELAY=3600; -- 延迟1小时同步

用途:

机房A整体故障,切换到机房B
延迟同步可防止误操作立即同步

演练:每季度进行一次容灾切换演练

案例95:双主互备架构

配置:

-- 主库A
server_id = 1
auto_increment_increment = 2
auto_increment_offset = 1

-- 主库B
server_id = 2
auto_increment_increment = 2
auto_increment_offset = 2

避免主键冲突:

主库A生成: 1, 3, 5, 7...
主库B生成: 2, 4, 6, 8...

应用层:

// 读写分离
@Transactional(readOnly = false)
public void write() {
    // 写主库A
}

@Transactional(readOnly = true)
public List<Order> read() {
    // 读主库B或从库
}

第十章:疑难杂症(案例96-100)

案例96:诡异的查询结果不一致

问题:同一个SQL,执行结果时对时错

场景:

SELECT * FROM orders WHERE user_id = 123;
-- 有时返回10条,有时返回8条

排查过程:

检查主从延迟 → 正常
检查事务隔离级别 → 正常
检查读写分离配置 → 发现问题!

真相:

// 代码中用了随机读
@Transactional(readOnly = true)
public List<Order> findOrders() {
    // 随机选择从库
    // 但从库1已同步,从库2还在延迟
}

解决方案:

// 强制读主库
@Transactional(readOnly = false)
public List<Order> findOrders() {
    return orderDao.find(userId);
}

案例97:emoji导致的插入失败

问题:

user.setName("张三😊");
userDao.insert(user);
// SQLException: Incorrect string value

原因:表字符集是utf8,不支持4字节emoji

临时方案:过滤emoji

String name = user.getName().replaceAll("[^\\u0000-\\uFFFF]", "");

彻底解决:

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;

案例98:时区问题导致的数据错乱

问题:TIMESTAMP字段存储时间不对

场景:

// 服务器时区:UTC+8
// 数据库时区:UTC+0
// 存入:2024-01-15 10:00:00
// 读出:2024-01-15 02:00:00 (??)

原因:TIMESTAMP会自动转换时区

解决方案1:统一时区

SET GLOBAL time_zone = '+8:00';

解决方案2:用DATETIME

ALTER TABLE orders 
MODIFY create_time DATETIME NOT NULL;

案例99:SQL注入漏洞

危险代码:

String sql = "SELECT * FROM users WHERE name = '" + userName + "'";
// 用户输入: admin' OR '1'='1
// 实际SQL: SELECT * FROM users WHERE name = 'admin' OR '1'='1'
// 查出所有用户!

正确做法:预编译

String sql = "SELECT * FROM users WHERE name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, userName);

自动检测:

// 使用SQL防火墙
spring.datasource.druid.filter.wall.enabled=true

案例100:数据库连接未关闭引发的内存泄漏

问题:应用运行几天后,内存占用持续增长,最终OOM

排查:

// 内存分析发现大量Connection对象未释放
try {
    Connection conn = dataSource.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    // 忘记关闭!
} catch (Exception e) {
    e.printStackTrace();
}

正确写法:

try (Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    // 处理结果
} // 自动关闭所有资源

监控告警:

// 连接泄漏检测
dataSource.setRemoveAbandoned(true);
dataSource.setRemoveAbandonedTimeout(300);
dataSource.setLogAbandoned(true);

全文总结

这100个案例,覆盖了数据库调优的方方面面:

技术层面:

索引设计与优化
SQL语句调优
锁和并发控制
连接池管理
表结构设计
大表处理
缓存策略
监控告警
高可用架构

能力提升:

快速定位问题的能力
系统性思考的能力
架构设计的能力
故障应急的能力

经验积累:

每个案例都是真实踩坑经验
包含问题现象、排查思路、解决方案
举一反三,应对各种变种问题

防患未然:

建立完善的监控体系
制定合理的开发规范
定期进行技术演练
持续优化系统架构

Q.E.D.

Hi,Friend

文章结构

适合谁看?

第一章:索引优化(案例1-15)

案例1:没有索引的噩梦

案例2:索引失效的隐形杀手

案例3:联合索引的顺序陷阱

案例4:过多索引拖垮写入性能

案例5:索引覆盖的威力

案例6:前缀索引的妙用

案例7:唯一索引引发的死锁

案例8:模糊查询的优化技巧

案例9:主键选择的坑

案例10:索引失效的隐式类型转换

案例11:分页查询的深度陷阱

案例12:冗余索引浪费资源

案例13:索引碎片导致性能下降

案例14:NULL值对索引的影响

案例15:函数索引的使用(MySQL 8.0+)

第二章:SQL语句优化(案例16-30)

案例16:SELECT * 的性能陷阱

案例17:OR条件的优化

案例18:子查询的性能灾难

案例19:COUNT的优化技巧

案例20:GROUP BY的性能优化

案例21:DISTINCT的滥用

案例22:LIMIT的优化

案例23:JOIN的优化技巧

案例24:IN vs EXISTS的选择

案例25:避免在WHERE中使用表达式

案例26:UNION vs UNION ALL

案例27:避免使用负向查询

案例28:ORDER BY的优化

案例29:避免多次查询

案例30:批量操作的优化

第三章:锁和并发控制(案例31-45)

案例31:经典死锁案例

案例32:行锁升级为表锁

案例33:间隙锁引发的阻塞

案例34:长事务导致的锁等待

案例35:乐观锁vs悲观锁

案例36:MVCC解决读写冲突

案例37:SELECT FOR UPDATE的使用陷阱

案例38:分布式锁解决跨库并发

案例39:批量更新的锁优化

案例40:热点行更新的优化

案例41:INSERT的锁冲突

案例42:锁等待超时的处理

案例43:幻读的处理

案例44:MDL锁导致的库崩溃

案例45:MySQL 8.0的锁改进

第四章:连接池与资源管理(案例46-55)

案例46:连接池耗尽导致雪崩

案例47:连接泄漏检测

案例48:连接池参数优化

案例49:读写分离的连接池配置

案例50:连接池预热

案例51:数据库连接数监控

案例52:连接超时参数调优

案例53:连接池隔离

案例54:连接池与事务的配合

案例55:连接池的优雅关闭

第五章:表结构设计(案例56-65)

案例56:VARCHAR长度的选择

案例57:INT vs BIGINT的选择

案例58:DECIMAL vs FLOAT的金额存储

案例59:时间字段的选择

案例60:枚举字段的设计

案例61:JSON字段的使用

案例62:冗余字段的权衡

案例63:软删除的实现

案例64:分表字段的选择

案例65:字符集与排序规则

第六章:大表优化(案例66-75)

案例66:单表数据量控制

案例67:在线DDL的实践

案例68:大表删除数据

案例69:分区表的应用

案例70:分库分表策略

案例71:分表后的分页查询