MySQL 性能优化实战手册

性能优化的核心思路

数据库性能优化的本质是用最小的系统资源消耗，在最短时间内返回正确的结果。遵循"由浅入深、由点及面"的原则，先定位瓶颈，再对症下药。

索引设计原则

索引是数据库优化中最常用、最有效的手段。合理的索引可以将查询时间从秒级降至毫秒级，但不合理的索引会降低写入性能并浪费存储空间。

-- 最左前缀原则示例
CREATE TABLE user_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    -- 联合索引：user_id + status + create_time
    INDEX idx_user_status_time (user_id, status, create_time)
);

-- 能命中索引的查询
SELECT * FROM user_orders WHERE user_id = 100 AND status = 1;
SELECT * FROM user_orders WHERE user_id = 100;
SELECT * FROM user_orders WHERE user_id = 100 AND status = 1 AND create_time > '2024-01-01';

-- 不能命中索引的查询（缺少最左列）
SELECT * FROM user_orders WHERE status = 1;
SELECT * FROM user_orders WHERE create_time > '2024-01-01';

执行计划分析：EXPLAIN

EXPLAIN 是 MySQL 提供的查询分析工具，通过分析执行计划可以判断查询是否走了索引、扫描了多少行、是否使用了临时表等关键信息。

EXPLAIN SELECT * FROM user_orders 
WHERE user_id = 100 
ORDER BY create_time DESC 
LIMIT 20;

-- 关注以下字段：
-- type: range / ref / index / ALL（ALL 代表全表扫描，需优化）
-- key: 实际使用的索引名
-- rows: 预估扫描行数
-- Extra: Using index（覆盖索引）/ Using filesort（需要排序优化）

慢查询定位与优化

开启慢查询日志，持续监控超过阈值的 SQL：

-- my.cnf 配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1    -- 超过 1 秒的 SQL 会被记录
log_queries_not_using_indexes = 1

-- 常用分析命令
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log   -- 按时间排序 Top 10
pt-query-digest /var/log/mysql/slow.log             -- Percona 工具详细分析

连接池调优

HikariCP 是目前性能最好的连接池。合理配置连接池参数对高并发系统至关重要：

spring:
  datasource:
    hikari:
      maximum-pool-size: 20          -- 最大连接数（公式：CPU核数 * 2 + 有效磁盘数）
      minimum-idle: 10               -- 最小空闲连接
      connection-timeout: 30000      -- 获取连接超时时间（毫秒）
      idle-timeout: 600000         -- 空闲连接存活时间
      max-lifetime: 1800000        -- 连接最大生命周期（小于 MySQL wait_timeout）
      pool-name: HikariPool-Primary
      leak-detection-threshold: 60000  -- 连接泄漏检测阈值

分库分表策略

当单表数据量超过 500 万行或单库写入 QPS 超过 2000 时，需要考虑分库分表。ShardingSphere 提供了透明化的分片能力：

-- 水平分表示例：按 user_id 取模分 4 表
CREATE TABLE user_order_0 LIKE user_order_template;
CREATE TABLE user_order_1 LIKE user_order_template;
CREATE TABLE user_order_2 LIKE user_order_template;
CREATE TABLE user_order_3 LIKE user_order_template;

-- ShardingSphere 配置
spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
    rules:
      sharding:
        tables:
          user_order:
            actual-data-nodes: ds$->{0..1}.user_order_$->{0..3}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: mod-4
        sharding-algorithms:
          mod-4:
            type: INLINE
            props:
              algorithm-expression: user_order_$->{user_id % 4}

读写分离

读多写少的场景下，读写分离可以显著提升查询吞吐量。ShardingSphere 支持自动路由：

-- 强制走主库（避免延迟）
HintManager hintManager = HintManager.getInstance();
hintManager.setWriteRouteOnly();
try {
    return orderMapper.selectById(orderId);
} finally {
    hintManager.close();
}