数据库性能优化：从索引到慢查询分析

数据库是后端系统的核心瓶颈所在。当应用规模增长时，最先出现性能问题的往往不是代码逻辑，而是数据库查询。本文将从索引设计、慢查询分析、连接池调优和分页优化四个维度，系统性地梳理数据库性能优化的实战方法。

一、索引设计原则

索引是数据库优化的第一道防线。合理的索引可以将查询时间从秒级降至毫秒级，但不当的索引反而会拖慢写入性能。

1.1 联合索引与最左前缀原则

MySQL 的 B+ 树索引遵循最左前缀原则。创建联合索引时，将区分度高、查询频繁度高的列放在前面：

-- 推荐的联合索引顺序
CREATE INDEX idx_user_status_created
ON users (status, created_at DESC);

-- 以下查询可以使用该索引
SELECT * FROM users
WHERE status = 'ACTIVE'
ORDER BY created_at DESC
LIMIT 20;

-- 以下查询也可以使用（最左前缀匹配）
SELECT * FROM users
WHERE status = 'ACTIVE';

1.2 覆盖索引

当查询的所有字段都包含在索引中时，MySQL 无需回表，直接通过索引即可返回结果：

-- 覆盖索引示例
CREATE INDEX idx_user_cover
ON users (status, username, email);

-- 该查询不会回表（所有字段都在索引中）
SELECT status, username, email
FROM users
WHERE status = 'ACTIVE';

1.3 避免过度索引

每张表的索引数量建议不超过 5 个。每次 INSERT / UPDATE / DELETE 都需要更新所有相关索引，过多索引会显著降低写入性能。

二、慢查询分析

开启 MySQL 慢查询日志是发现性能问题的最有效手段：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录为慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 使用 EXPLAIN 分析查询执行计划
EXPLAIN
SELECT u.*, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'ACTIVE'
ORDER BY u.created_at DESC
LIMIT 100;

EXPLAIN 输出中需要重点关注的列：

type — 连接类型，从优到差：system > const > eq_ref > ref > range > index > ALL
key — 实际使用的索引，如果为 NULL 说明走了全表扫描
rows — 预估扫描行数，越大越危险
Extra — 额外信息，出现 "Using filesort" 或 "Using temporary" 意味着需要优化

三、连接池调优

数据库连接池的配置直接影响应用的吞吐量和响应时间。以 HikariCP 为例：

# application.yml 中的 HikariCP 配置
spring:
  datasource:
    hikari:
      minimum-idle: 5          # 最小空闲连接
      maximum-pool-size: 20    # 最大连接数（公式：CPU核心数 * 2 + 磁盘数）
      idle-timeout: 600000     # 空闲连接超时（毫秒）
      max-lifetime: 1800000    # 连接最大生命周期（30 分钟）
      connection-timeout: 30000 # 获取连接超时时间
      pool-name: "MainPool"

连接池大小的计算公式：

CPU 密集型：连接数 = CPU 核心数 + 1
IO 密集型：连接数 = CPU 核心数 * 2 + 磁盘数

对于大多数 Web 应用（IO 密集型），20 个连接是一个合理的起点，可以通过压测逐步调整。

四、分页优化

深分页（OFFSET 过大）是 MySQL 最常见的性能陷阱。当 OFFSET 达到万级时，MySQL 会扫描并丢弃大量行：

-- 慢查询：OFFSET 100000 时性能急剧下降
SELECT * FROM orders
WHERE status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 10 OFFSET 100000;

-- 优化方案一：延迟关联（子查询先定位 ID）
SELECT t.* FROM orders t
INNER JOIN (
    SELECT id FROM orders
    WHERE status = 'COMPLETED'
    ORDER BY created_at DESC
    LIMIT 10 OFFSET 100000
) tmp ON t.id = tmp.id;

-- 优化方案二：游标分页（基于上一页最后一条记录的 ID）
SELECT * FROM orders
WHERE status = 'COMPLETED'
  AND created_at < '2026-03-15 10:30:00'
ORDER BY created_at DESC
LIMIT 10;

游标分页的优势在于性能不随页码增长而下降，但牺牲了"跳转到任意页"的能力。在实际应用中，大部分用户只会浏览前几页，因此游标分页是更好的选择。

五、总结

数据库优化不是一蹴而就的工作，而是一个持续监控、分析、调整的过程。建议建立以下习惯：

上线前用 EXPLAIN 检查所有新查询的执行计划
定期 review 慢查询日志，针对性地添加索引
使用 EXPLAIN 验证索引调整效果，不要凭感觉优化
深分页场景优先考虑游标分页方案

记住：最好的优化是在设计阶段做出的决策，而不是上线后的补救。