第 23 章:SQL 调优

学习目标

• 掌握 EXPLAIN 执行计划解读
• 学会索引设计原则
• 用慢查询日志找问题
• 避免索引失效、深分页的坑

一、EXPLAIN 执行计划

EXPLAIN SELECT * FROM order WHERE user_id = 100 AND status = 'PAID';

字段说明
├── id: SELECT 编号
├── select_type: SIMPLE / PRIMARY / SUBQUERY
├── table: 表名
├── partitions: 分区
├── type: 连接类型(关键)
│   ├── system > const > eq_ref > ref > range > index > ALL
├── possible_keys: 可能用到的索引
├── key: 实际用到的索引
├── key_len: 索引长度
├── ref: 索引比较的列
├── rows: 扫描行数(估值)
├── Extra: 额外信息
└── filtered: 过滤比

-- type 性能:system > const > eq_ref > ref > range > index > ALL
-- ⚠️ 出现 ALL = 全表扫描,必须优化

二、索引原则

-- 1. 最左前缀原则
CREATE INDEX idx_user_status ON order (user_id, status, created_at);
-- WHERE user_id = 1           ✅ 用到
-- WHERE user_id = 1 AND status = 'PAID'  ✅ 用到
-- WHERE status = 'PAID'        ❌ 不行

-- 2. 覆盖索引(避免回表)
CREATE INDEX idx_user_amount ON order (user_id, amount);
SELECT amount FROM order WHERE user_id = 1;  -- 索引里有,不回表

-- 3. 索引下推(ICP)
-- MySQL 5.6+ 自动
SELECT * FROM order WHERE user_id = 1 AND status = 'PAID' AND amount > 100;
-- 联合索引 (user_id, status, amount) 时,直接索引层过滤

⚠️ 坑 1:索引 (user_id, status, created_at),查询 WHERE user_id = 1 AND created_at > '2026-01-01'?不走索引,因为 status 跳过了。

三、索引失效场景

-- 1. 函数导致失效
SELECT * FROM order WHERE DATE(created_at) = '2026-01-15';  -- ❌
SELECT * FROM order WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16';  -- ✅

-- 2. 类型转换
SELECT * FROM order WHERE user_id = '100';  -- user_id 是 INT,触发隐式转换
SELECT * FROM order WHERE user_id = 100;    -- ✅

-- 3. LIKE 前缀
SELECT * FROM order WHERE order_no LIKE '%123%';  -- ❌ 全表扫描
SELECT * FROM order WHERE order_no LIKE '123%';   -- ✅

-- 4. 不等号
SELECT * FROM order WHERE amount != 100;  -- ❌
SELECT * FROM order WHERE amount > 100 OR amount < 100;  -- ✅

-- 5. IS NULL
SELECT * FROM order WHERE status IS NULL;  -- 索引可能失效

⚠️ 坑 2:字段 status 取值只有 5 个,即使有索引,MySQL 优化器认为全表扫描更快。离散度低(< 10%)的字段不适合单独建索引。

四、慢查询排查

-- 1. 开启慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 2. 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

// 3. Druid 监控
@Bean
public ServletRegistrationBean<StatViewServlet> druidStatView() {
    return new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
}

五、深分页优化

-- ❌ 慢:LIMIT 1000000, 10 扫描 1000010 行
SELECT * FROM order ORDER BY id LIMIT 1000000, 10;

-- ✅ 方案 1:主键游标
SELECT * FROM order WHERE id > 1000000 ORDER BY id LIMIT 10;

-- ✅ 方案 2:子查询
SELECT * FROM order WHERE id >= (
    SELECT id FROM order ORDER BY id LIMIT 1000000, 1
) LIMIT 10;

-- ✅ 方案 3:覆盖索引延迟关联
SELECT * FROM order o
INNER JOIN (
    SELECT id FROM order ORDER BY id LIMIT 1000000, 10
) t ON o.id = t.id;

// ✅ 业务方案:游标分页(推荐)
public Page<Order> listByCursor(Long lastId, int size) {
    return orderDao.findByIdLessThanOrderByIdDesc(lastId, PageRequest.of(0, size));
}
// 前端传"上一页最后一条的 id"

⚠️ 坑 3:深分页用 LIMIT 1000000, 10 必踩坑,即使加索引也要扫描 100 万行再丢。

六、JOIN 优化

-- 1. 小表驱动大表
SELECT * FROM small_table s
INNER JOIN big_table b ON s.id = b.small_id;
-- MySQL 会选小表驱动

-- 2. 避免 JOIN 字段类型不一致
-- user_id 是 INT,关联字段 VARCHAR,索引失效

-- 3. 适当冗余
-- 订单表冗余 user_name,避免 JOIN user 表

-- 4. 拆分复杂 JOIN
-- 业务层多次查询组装

// 业务层组装
public OrderDetail getOrderDetail(Long orderId) {
    Order order = orderRepository.findById(orderId);
    User user = userRepository.findById(order.getUserId());
    Product product = productRepository.findById(order.getProductId());
    return new OrderDetail(order, user, product);
}

七、事务隔离

-- MySQL 默认:REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 间隙锁(REPEATABLE READ 默认)
-- 范围查询会锁区间,影响并发

// 避免长事务
@Transactional
public void updateOrder(Long id) {
    Order order = orderRepository.findById(id);
    // 远程调用
    paymentService.refund(order);  // 锁一直持有
    order.setStatus("REFUNDED");
    orderRepository.save(order);
}

// 优化:远程调用放事务外
public void updateOrder(Long id) {
    Order order = orderRepository.findById(id);
    Long paymentId = order.getPaymentId();
    // 事务内只做 DB 操作
    txnTemplate.execute(status -> {
        order.markRefunded();
        orderRepository.save(order);
        return null;
    });
    // 事务外调用远程
    paymentService.refund(paymentId);
}

⚠️ 坑 4:@Transactional 内部发 HTTP/MQ,事务不提交锁不释放,长事务导致大量锁等待。

八、批量操作

// ❌ 慢:逐条 INSERT
for (Order order : orders) {
    orderRepository.save(order);  // 1000 次 INSERT
}

// ✅ 批量 INSERT
jdbcTemplate.batchUpdate(
    "INSERT INTO order (user_id, amount) VALUES (?, ?)",
    new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setLong(1, orders.get(i).getUserId());
            ps.setBigDecimal(2, orders.get(i).getAmount());
        }
        @Override
        public int getBatchSize() {
            return orders.size();
        }
    }
);

# MyBatis 批量
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
mybatis:
  executor-type: batch

九、读写分离

# 分库分表 + 读写分离
spring:
  datasource:
    order-master:
      url: jdbc:mysql://master:3306/order
    order-slave:
      url: jdbc:mysql://slave:3306/order

// ShardingSphere
@DS("order-master")
public void createOrder(OrderDto dto) {
    // 写主库
}

@DS("order-slave")
public List<Order> listOrders() {
    // 读从库
}

本章小结

优化点	收益
索引	10x
慢查询	5x
分库分表	10x
读写分离	3x
批量	5x

关键点	建议
type	大于 range
索引	联合索引 > 多个单列
字段	离散度 > 10%
事务	短小,不放远程调用

动手练习

1. EXPLAIN 分析:对一个慢查询,解读执行计划
2. 索引设计:为订单表设计合理索引,验证查询提速
3. 深分页优化:把 LIMIT 1000000, 10 改成游标分页
4. 批量操作:对比单条 INSERT vs 批量 INSERT 的耗时

---

下一章:第 24 章:缓存架构 →