一、执行计划分析与索引原理

数据库优化的起点是理解查询的执行路径。EXPLAIN 是诊断 MySQL 性能问题最重要的一把钥匙。

1.1 EXPLAIN 深度解读

EXPLAIN FORMAT=JSON SELECT ...
-- 关键字段解析：
-- type: ref / range / index / ALL（从优到劣）
-- key: 实际使用的索引
-- rows: 估算扫描行数（越少越好）
-- Extra: Using filesort / Using temporary（危险信号）
-- Using index condition (ICP) — 索引下推，优）

-- 通过 optimizer_trace 查看代价模型
SET optimizer_trace="enabled=on";
SELECT ...;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

1.2 索引内部结构：B+Tree vs Hash

MySQL InnoDB 采用 B+Tree，聚簇索引叶子节点存储完整行数据，二级索引叶子节点存储主键值。理解这个模型才能写出高效的查询。

  • 聚簇索引：数据文件本身就是索引文件，叶子节点包含完整数据行，按主键排序
  • 二级索引：叶子节点存储主键值，查询需要回表（先查索引再查主键索引）
  • 覆盖索引：查询列全部在索引中，无需回表，Using index
  • 最左前缀原则：联合索引 (A,B,C) 可命中 A、(A,B)、(A,B,C)，无法命中 (B,C)

1.3 索引失效的常见场景

-- ❌ 索引失效场景
SELECT * FROM orders WHERE YEAR(create_time) = 2026;  -- 函数运算
SELECT * FROM users WHERE name LIKE '%张%';            -- 左通配符
SELECT * FROM products WHERE price > 100 AND seller_id = 5; -- 范围断链
SELECT * FROM t WHERE id + 1 = 100;                   -- 表达式
SELECT * FROM t WHERE status IN ('a','b') OR city = 'BJ'; -- OR破坏

-- ✅ 正确做法
-- 用范围列放在联合索引末尾
ALTER TABLE orders ADD INDEX idx_seller_time(seller_id, create_time);
SELECT * FROM orders WHERE seller_id = 5 AND create_time BETWEEN '2026-01-01' AND '2026-12-31';

二、SQL 语句优化

2.1 分页优化（深度分页）

当 OFFSET 很大时（ OFFSET 1000000），MySQL 需要先扫描跳过前100万行，极其低效：

-- ❌ 深度分页：扫描100万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 方案A：游标分页（最优，适用于有单调递增主键）
SELECT * FROM orders WHERE id > #{lastId} ORDER BY id LIMIT 20;

-- ✅ 方案B：子查询延迟关联
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

-- ✅ 方案C：记录总数缓存（前端只展示页码，不查总数）
SELECT SQL_CALC_FOUND_ROWS * FROM orders LIMIT 1000000, 20;
SELECT FOUND_ROWS(); -- 单独查询（MySQL 8.0.17废弃，推荐方案B）

2.2 JOIN 优化与 MRR

-- MRR（Multi-Range Read）：将随机磁盘读转化为顺序读
SET optimizer_switch='mrr=on,mrr_cost_based=off';

-- 小表驱动大表：EXPLAIN 查看 join_type
-- 当 t1(100行) JOIN t2(1000万行) 时：
SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
-- t1 为驱动表，t2 为被驱动表（用索引）
-- 原则：驱动表尽可能小，被驱动表关联列必须有索引

-- ✅ Batched Key Access（BAK）：一次批量获取多行
SET optimizer_switch='batched_tree_access=on';

-- Java 中使用 JPA 写高效 JOIN 查询
@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.userId = :uid")
List findOrdersWithItems(@Param("uid") Long userId);

三、连接池调参与监控

3.1 HikariCP 核心参数调优

# application.yml
spring:
  datasource:
    hikari:
      # 连接池大小 = (核心数 * 2) + 有效磁盘数
      maximum-pool-size: 20
      minimum-idle: 5
      # 连接最大生命周期（短于MySQL wait_timeout）
      max-lifetime: 1800000   # 30分钟
      connection-timeout: 30000  # 获取连接超时
      idle-timeout: 600000      # 空闲超时
      # 验证连接有效性
      connection-test-query: SELECT 1
      # 监控
      pool-name: OrderHikariPool

# 监控指标（JMX/Micrometer）
# - Active Connections：活跃连接数，接近 max 时说明瓶颈
# - Idle Connections：空闲连接，接近 0 说明连接不够用
# - Threads Awaiting Connection：等待获取连接的线程数
# - Connection Timeout Count：连接超时次数

3.2 Druid 连接池防泄漏

// Druid 配置：连接泄漏检测
DruidDataSource ds = new DruidDataSource();
ds.setRemoveAbandoned(true);
ds.setRemoveAbandonedTimeout(180);  // 180秒未归还则强制回收
ds.setLogAbandoned(true);           // 打印堆栈追踪泄漏源头

// 配合 DruidStatFilter 做 SQL 监控
@WebFilter(urlPatterns = "/*", initParams = {
    @WebInitParam(name = "profileEnable", value = "true")
})
public class DruidStatFilter extends DruidStatFilter {}

// 获取慢 SQL 记录
List slowSqls = ds.getProperty().getSlowSQLRecords();

四、分库分表与 ShardingSphere

4.1 分片策略选型

策略适用场景数据均匀性
Hash分片用户ID/订单ID分片✅ 均匀
按时间分片日志、流水、订单⚠️ 冷热不均
Range分片ID范围有序访问❌ 易倾斜
多字段自定义复杂查询路由✅ 可控

4.2 ShardingSphere-JDBC 配置

-- 分表 DDL
CREATE TABLE orders_2026 (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    create_time DATETIME NOT NULL,
    amount DECIMAL(10,2),
    INDEX idx_user_id(user_id),
    INDEX idx_create_time(create_time)
) ENGINE=InnoDB;

-- Java ShardingSphere 配置
ShardingRuleConfiguration config = new ShardingRuleConfiguration();
config.getTables().add(orderTableRuleConfig());

TableRuleConfiguration orderRule = new TableRuleConfiguration("t_order", "ds_${0..1}.t_order_${0..15}");
orderRule.setDatabaseShardingStrategyConfig(
    new StandardShardingStrategyConfiguration("user_id",
        new PreciseShardingValueAlgorithm() {
            @Override
            public String doSharding(String targetNames, PreciseShardingValue value) {
                long id = Long.parseLong(value.getValue().toString());
                return "ds_" + (id % 2);
            }
        }));
orderRule.setTableShardingStrategyConfig(
    new StandardShardingStrategyConfiguration("create_time",
        new RangeShardingValueAlgorithm() {
            @Override
            public Collection doSharding(String targetNames,
                    RangeShardingValue value) {
                // 按年月路由到对应分表
            }
        }));

// 分布式唯一ID：Snowflake + 时钟回拨处理
public class SnowflakeIdGenerator {
    private final long twepoch = 1609459200000L; // 2021-01-01
    private final long workerIdBits = 5;
    private final long datacenterIdBits = 5;
    private final long sequenceBits = 12;
    // 解决时钟回拨：等待补偿或拒绝服务
}

五、线上 SQL 质量门禁

-- 质量门禁规则（MyBatis-Plus 拦截器实现）
@Intercepts({
    @Signature(type = StatementHandler.class, method = "query",
               args = {Statement.class, ResultHandler.class})
})
public class SQLAuditInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation inv) throws Throwable {
        StatementHandler sh = (StatementHandler) PluginUtil.getTarget(inv.getTarget());
        String sql = sh.getBoundSql().getSql().toLowerCase();

        // 规则1：禁止 SELECT *
        if (sql.contains("select *")) {
            throw new IllegalSQLException("禁止 SELECT *，必须指定字段");
        }

        // 规则2：扫描行数阈值
        RoutingConnection rc = (RoutingConnection) getConnection();
        ExplainResult er = rc.explain(sql);
        if (er.getEstimatedRows() > 10000) {
            throw new IllegalSQLException("估算扫描行数 " + er.getEstimatedRows() + " 超过阈值");
        }

        // 规则3：禁止无 LIMIT 的全表 DELETE/UPDATE
        if ((sql.contains("delete") || sql.contains("update"))
                && !sql.contains("where") && !sql.contains("limit")) {
            throw new IllegalSQLException("DML必须带WHERE或LIMIT");
        }
        return inv.proceed();
    }
}