好嘞，各位老铁，今天咱们聊点刺激的，来一场 MySQL 性能优化的实战秀！主题就是：一个真实的 MySQL 性能优化案例，从发现问题到解决问题的全过程复盘。保证让你听完之后，感觉自己也能手撕性能瓶颈，走向架构师的巅峰！
开场白：谁还没碰到过“龟速”MySQL？
相信各位在座的，或多或少都遇到过这样的场景：
- 半夜被运维大哥的电话吵醒：“XX 系统响应慢得跟蜗牛爬似的，赶紧看看！”
- 用户疯狂投诉：“这页面加载速度，我还不如手写信寄过去快！”
- 看着 CPU 飙升、IO 告警，却一脸懵逼，不知道从何下手。
别慌，这都是常态。MySQL 性能问题就像感冒，谁也躲不过。关键在于，咱们要学会诊断、对症下药，而不是抱着服务器哭。
案例背景：电商秒杀活动
为了更好地说明问题，我们来模拟一个常见的场景：电商平台的秒杀活动。
- 业务场景： 用户抢购限量商品，需要在极短时间内完成下单。
- 数据库： MySQL (版本假设是 5.7，优化思路在不同版本上略有差异，但核心思想不变)。
-
表结构： 简化一下，主要涉及以下两张表：
product(商品表):id,name,stock(库存),priceorder(订单表):id,user_id,product_id,create_time
问题发现：慢查询日志，罪恶的源头
性能优化第一步，就是要找到问题所在。MySQL 提供了慢查询日志，记录执行时间超过 long_query_time (默认10秒) 的 SQL 语句。
-
开启慢查询日志：
-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE 'slow_query_log'; -- 开启慢查询日志 (临时生效，重启失效) SET GLOBAL slow_query_log = 'ON'; -- 设置慢查询时间阈值 (单位：秒) SET GLOBAL long_query_time = 1; -- 设置为1秒，方便演示 -- 指定慢查询日志文件 (可选) SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; -- 永久生效，需要修改 MySQL 配置文件 (my.cnf 或 my.ini) -
分析慢查询日志： 可以使用
mysqldumpslow工具或者第三方工具 (如 pt-query-digest) 分析慢查询日志。mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log # 按查询时间排序，显示前10条通过分析慢查询日志，我们发现以下 SQL 语句执行频率高，且耗时较长：
-- 扣减库存 UPDATE product SET stock = stock - 1 WHERE id = ? AND stock > 0; -- 创建订单 INSERT INTO order (user_id, product_id, create_time) VALUES (?, ?, NOW()); -- 查询商品信息 SELECT id, name, stock, price FROM product WHERE id = ?; -- 订单查询 SELECT id, user_id, product_id, create_time FROM `order` WHERE user_id = ?;
问题诊断：逐个击破，锁定瓶颈
有了慢查询日志，我们就可以逐个分析这些 SQL 语句，找到性能瓶颈。
-
UPDATE product语句：- 问题： 高并发下，行锁冲突严重。多个用户同时抢购同一商品，导致大量事务阻塞。
- 原因： MySQL 默认使用行锁，当多个事务同时修改同一行数据时，会发生锁竞争。
-
解决方案：
-
方案一：悲观锁优化 (不推荐)：
-- 使用 FOR UPDATE 显式加锁 SELECT stock FROM product WHERE id = ? FOR UPDATE; -- 扣减库存 UPDATE product SET stock = stock - 1 WHERE id = ? AND stock > 0;缺点： 更加剧了锁的竞争，在高并发场景下效果不佳。
-
方案二：乐观锁：
-- 在 product 表中增加 version 字段 ALTER TABLE product ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0; -- 扣减库存 UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = ? AND stock > 0 AND version = ?;优点： 减少了锁的持有时间，提高了并发性能。
代码示例 (Java):
public boolean decreaseStock(Long productId, int version) { String sql = "UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = ? AND stock > 0 AND version = ?"; int rowsAffected = jdbcTemplate.update(sql, productId, version); return rowsAffected > 0; } // 调用示例 int currentVersion = product.getVersion(); boolean success = productService.decreaseStock(productId, currentVersion); if (success) { // 扣减成功 } else { // 扣减失败，需要重试或者提示用户 }注意： 乐观锁需要应用程序处理并发冲突，通常需要重试机制。
-
方案三：Redis 预减库存 + Canal 同步 (推荐)：
- 原理： 将库存信息存储在 Redis 中，利用 Redis 的高性能特性进行预减库存。当库存足够时，再异步同步到 MySQL。
- 步骤：
- Redis 预减库存： 使用 Redis 的
DECR命令原子性地扣减库存。 - 判断库存是否足够： 如果 Redis 中的库存大于等于 0，则允许下单。
- 异步同步 MySQL： 使用 Canal 等工具监听 MySQL 的 binlog，将库存变更同步到 MySQL。
- Redis 预减库存： 使用 Redis 的
- 优点： 极大地提高了并发性能，降低了 MySQL 的压力。
- 缺点： 增加了系统的复杂度，需要考虑数据一致性问题。
代码示例 (Redis + Java):
@Autowired private RedisTemplate<String, Integer> redisTemplate; private static final String PRODUCT_STOCK_KEY_PREFIX = "product:stock:"; public boolean preDecreaseStock(Long productId) { String key = PRODUCT_STOCK_KEY_PREFIX + productId; Long stock = redisTemplate.opsForValue().decrement(key); if (stock >= 0) { // 预减库存成功 return true; } else { // 库存不足，回滚 redisTemplate.opsForValue().increment(key); return false; } } // 调用示例 boolean success = productService.preDecreaseStock(productId); if (success) { // 预减库存成功，可以下单 // ... } else { // 库存不足 // ... }
-
-
INSERT INTO order语句：- 问题： 高并发下，插入操作频繁，导致数据库压力增大。
- 原因： 每次下单都需要向
order表插入一条记录。 -
解决方案：
-
方案一：批量插入：
// 批量插入订单 String sql = "INSERT INTO order (user_id, product_id, create_time) VALUES (?, ?, NOW())"; List<Object[]> batchArgs = new ArrayList<>(); for (int i = 0; i < orders.size(); i++) { Order order = orders.get(i); Object[] args = {order.getUserId(), order.getProductId()}; batchArgs.add(args); } jdbcTemplate.batchUpdate(sql, batchArgs);优点： 减少了数据库连接次数，提高了插入效率。
注意： 批量插入的大小需要根据实际情况调整，避免一次性插入过多数据导致内存溢出。
-
方案二：消息队列异步处理：
- 原理： 将订单信息发送到消息队列 (如 Kafka, RabbitMQ)，由消费者异步处理订单创建。
- 优点： 解耦了下单流程和订单创建流程，提高了系统的吞吐量。
- 缺点： 增加了系统的复杂度，需要保证消息的可靠性。
-
-
SELECT id, name, stock, price FROM product WHERE id = ?语句：- 问题： 每次下单都需要查询商品信息，如果商品信息不经常变化，会造成不必要的数据库压力。
- 原因： 频繁查询相同的商品信息。
-
解决方案：
-
方案一：缓存：
- 原理： 将商品信息缓存到 Redis 或 Memcached 中，减少数据库查询。
- 优点： 提高了查询速度，降低了数据库压力。
- 缺点： 需要考虑缓存一致性问题。
代码示例 (Redis + Java):
@Autowired private RedisTemplate<String, Product> redisTemplate; private static final String PRODUCT_KEY_PREFIX = "product:"; public Product getProductById(Long productId) { String key = PRODUCT_KEY_PREFIX + productId; Product product = redisTemplate.opsForValue().get(key); if (product == null) { // 从数据库查询 product = productMapper.selectById(productId); if (product != null) { // 缓存到 Redis redisTemplate.opsForValue().set(key, product, 60, TimeUnit.SECONDS); // 设置过期时间 } } return product; }
-
-
SELECT id, user_id, product_id, create_time FROMorder` WHERE user_id = ?` 语句：- 问题： 如果订单量很大，查询用户订单会比较慢。
- 原因： 没有合适的索引。
-
解决方案：
-
方案一：添加索引：
-- 在 user_id 字段上添加索引 ALTER TABLE `order` ADD INDEX idx_user_id (user_id);优点： 显著提高了查询速度。
注意： 索引不是越多越好，过多的索引会影响插入和更新的性能。
-
方案二：分库分表：
- 原理： 将订单数据分散到多个数据库或表中，减少单个数据库或表的压力。
- 优点： 提高了系统的可扩展性和并发能力。
- 缺点： 增加了系统的复杂度，需要考虑分布式事务问题。
-
优化总结：一张表格搞定
| SQL 语句 | 问题 | 解决方案 | 备注 |
|---|---|---|---|
UPDATE product SET stock = stock - 1 ... |
高并发下，行锁冲突严重。 | 1. 乐观锁 2. Redis 预减库存 + Canal 同步 (推荐) | 乐观锁需要应用程序处理并发冲突，Redis 预减库存需要考虑数据一致性。 |
INSERT INTO order ... |
高并发下，插入操作频繁。 | 1. 批量插入 2. 消息队列异步处理 | 批量插入需要控制大小，消息队列需要保证消息的可靠性。 |
SELECT id, name, stock, price FROM product ... |
频繁查询商品信息。 | 缓存 (Redis 或 Memcached) | 需要考虑缓存一致性问题。 |
SELECT id, user_id, product_id, create_time ... |
订单量大，查询用户订单慢。 | 1. 添加索引 2. 分库分表 | 索引不是越多越好，分库分表增加了系统的复杂度。 |
优化效果：立竿见影
经过上述优化，秒杀系统的性能得到了显著提升：
- 响应时间： 从几秒降到几毫秒。
- 并发能力： 提高了数十倍。
- 数据库压力： 大幅降低。
经验教训：防微杜渐，未雨绸缪
- 监控先行： 提前监控数据库性能指标，及时发现潜在问题。
- 压测模拟： 在上线前进行充分的压力测试，模拟高并发场景，暴露问题。
- 代码规范： 编写高质量的 SQL 语句，避免全表扫描等低效操作。
- 架构设计： 在系统设计阶段就考虑到性能问题，选择合适的架构方案。
结尾：性能优化，永无止境
性能优化是一个持续不断的过程，需要不断学习、实践、总结。希望通过今天的分享，能够帮助大家更好地理解 MySQL 性能优化，提升自己的技术能力。
记住，没有银弹，只有不断尝试和学习！ 祝各位都能成为 MySQL 性能优化的大佬！ 下次见！