共计 3304 个字符，预计需要花费 9 分钟才能阅读完成。

开篇：从电商秒杀看性能瓶颈

去年双 11 我们系统出现了一个典型问题：秒杀活动开始后，数据库 CPU 瞬间飙升到 90%，订单创建接口平均响应时间从 50ms 恶化到 2 秒。通过监控发现，问题集中在商品库存查询 SQL 上：

SELECT stock FROM items WHERE item_id=12345;

看似简单的查询，在 QPS 达到 5000 时却成了瓶颈。本文将分享我们最终落地的全链路优化方案。

核心优化方案

1. 索引设计的艺术

覆盖索引实战

原商品表索引：

ALTER TABLE items ADD INDEX idx_item_id (item_id);

优化后使用覆盖索引：

ALTER TABLE items ADD INDEX idx_cover (item_id, stock);

执行计划对比（关键字段）：

-- 原索引
| id | select_type | type  | key        | Extra       |
|----|-------------|-------|------------|-------------|
| 1  | SIMPLE      | const | idx_item_id| Using where |

-- 覆盖索引
| id | select_type | type  | key       | Extra       |
|----|-------------|-------|-----------|-------------|
| 1  | SIMPLE      | const | idx_cover | Using index |

Extra 列出现 ”Using index” 意味着所有需要的数据都在索引中，无需回表。实测 QPS 提升 40%。

最左前缀原则

用户查询场景：

SELECT * FROM orders 
WHERE user_id=1001 
AND create_time > '2023-01-01'
ORDER BY amount DESC;

错误索引：

INDEX (create_time, user_id, amount)

正确索引：

INDEX (user_id, create_time, amount)

2. 执行计划深度解读

关键字段说明：

• type：从最好到最差排序
  system > const > eq_ref > ref > range > index > ALL

• possible_keys：可能使用的索引

• key_len：使用的索引长度（单位字节）

案例分析：

EXPLAIN SELECT * FROM users 
WHERE age > 18 
AND status=1 
LIMIT 100;

结果：

| id | select_type | type | key     | rows | Extra          |
|----|-------------|------|---------|------|----------------|
| 1  | SIMPLE      | ref  | idx_age | 5000 | Using filesort |

问题诊断：
1. Using filesort 表示需要额外排序
2. 扫描行数过多（5000 行）

优化方案：

ALTER TABLE users ADD INDEX idx_status_age (status, age);

3. 连接池优化（HikariCP 配置）

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("pass");

// 核心参数
config.setMaximumPoolSize(20);  // 建议 =(CPU 核心数 *2)+ 有效磁盘数
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);  // 30 秒
config.setIdleTimeout(600000);  // 10 分钟
config.setMaxLifetime(1800000); // 30 分钟
config.setConnectionTestQuery("SELECT 1");

// 重要优化参数
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

实战代码优化

解决 N + 1 查询问题

原始代码（Java+MyBatis）：

List<Order> orders = orderMapper.getByUserId(userId); // 1 次查询
for(Order order : orders) {List<Item> items = itemMapper.getByOrderId(order.getId()); // N 次查询
    order.setItems(items);
}

优化方案：

<!-- MyBatis 映射文件 -->
<resultMap id="orderWithItems" type="Order">
    <collection property="items" column="id" 
                select="selectItemsByOrderId" fetchType="eager"/>
</resultMap>

<select id="getOrderWithItems" resultMap="orderWithItems">
    SELECT * FROM orders WHERE user_id = #{userId}
</select>

<select id="selectItemsByOrderId" resultType="Item">
    SELECT * FROM items WHERE order_id = #{orderId}
</select>

批量插入优化

Python 示例（对比单条 vs 批量）：

# 低效方式（1000 次网络 IO）for i in range(1000):
    cursor.execute("INSERT INTO logs VALUES (%s, %s)", (i, f"log{i}"))

# 高效方式（1 次网络 IO）data = [(i, f"log{i}") for i in range(1000)]
cursor.executemany("INSERT INTO logs VALUES (%s, %s)", data)

性能压测数据

索引策略对比（TPS）

锁竞争解决方案

1. 乐观锁实现（Java）：

   // 更新时检查版本号
   UPDATE products 
   SET stock = stock - 1, version = version + 1
   WHERE id = 1001 AND version = 123;

2. 悲观锁注意事项：

   -- 使用 FOR UPDATE 时要确保走索引，否则会锁全表
   SELECT * FROM orders WHERE user_id=1001 FOR UPDATE;

生产环境避坑指南

1. 隐式类型转换 ：

   -- user_id 是 varchar 类型时（错误示范）SELECT * FROM users WHERE user_id = 1001;
   
   -- 正确写法
   SELECT * FROM users WHERE user_id = '1001';

2. 字符集统一 ：

   -- JOIN 字段字符集不一致会导致索引失效
   ALTER TABLE orders MODIFY user_id VARCHAR(32) CHARACTER SET utf8mb4;

3. 避免 SELECT *：

   -- 不推荐
   SELECT * FROM products WHERE category='electronics';
   
   -- 推荐
   SELECT id, name, price FROM products WHERE category='electronics';

思考题

1. 如何优化 SELECT * FROM large_table LIMIT 1000000, 20 这类深分页查询？
2. 在读写分离架构下，如何确保刚写入的数据能立即被查询到？
3. 对于 JSON 类型的字段，如何建立高效查询索引？

（欢迎在评论区分享你的解决方案）