MySQL性能调优实战：慢查询诊断、索引优化与分库分表方案全解

MySQL性能问题的定位方法论

数据库运维中，性能问题80%由慢查询导致，20%由配置和硬件瓶颈导致。MySQL性能调优必须从慢查询诊断入手，而非盲目调整参数。一条查询从客户端发起到返回结果，经历连接器、解析器、优化器、执行器四个阶段，性能瓶颈可能出现在任何一个环节。以下从诊断、优化、扩展三个层面展开。

慢查询诊断与EXPLAIN深度分析

开启慢查询日志是第一步：

# my.cnf慢查询配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5      # 超过500ms记录
log_queries_not_using_indexes = 1
min_examined_row_limit = 100  # 扫描行数低于100不记录

# 运行时动态调整
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;

使用mysqldumpslow分析慢查询Top N：

# 按查询时间排序取Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

EXPLAIN输出中各列的含义决定了优化方向：

-- 查看执行计划
EXPLAIN ANALYZE
SELECT o.order_id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-07-01'
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;

关键字段解读：

• type：访问类型，从优到差：system > const > eq_ref > ref > range > index > ALL
• key：实际使用的索引，NULL表示未命中索引
• rows：预估扫描行数，偏差大说明统计信息过期
• Extra：Using filesort（额外排序）和Using temporary（临时表）是性能杀手

索引优化策略

SQL查询优化中索引设计是核心。复合索引需遵循最左前缀原则：

-- 订单表常见查询场景
-- 场景1: 按用户+状态查询
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';

-- 场景2: 按用户+创建时间范围查询
SELECT * FROM orders WHERE user_id = 1001
  AND created_at >= '2026-07-01';

-- 场景3: 按用户+状态+时间排序
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

-- 最优复合索引设计（覆盖上述三个场景）
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

索引选择性和基数决定了索引效率：

-- 查看索引基数（Cardinality越高越好）
SHOW INDEX FROM orders;

-- 计算选择性
SELECT
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM orders;

-- 低选择性字段不适合建索引（如status只有5个值）
-- 但在复合索引中作为第二列是合理的

索引失效的常见陷阱：

-- 1. 隐式类型转换（user_id是varchar，传入整数）
SELECT * FROM orders WHERE user_id = 1001;    -- 索引失效
SELECT * FROM orders WHERE user_id = '1001';  -- 索引命中

-- 2. 函数运算导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-30';  -- 索引失效
SELECT * FROM orders WHERE created_at >= '2026-07-30'
  AND created_at < '2026-07-31';                                -- 索引命中

-- 3. LIKE前缀通配符
SELECT * FROM products WHERE name LIKE '%手机';   -- 索引失效
SELECT * FROM products WHERE name LIKE '华为%';   -- 索引命中

-- 4. OR条件中有一列无索引
SELECT * FROM orders WHERE user_id = 1001
  OR amount > 10000;  -- 全表扫描
-- 优化：UNION ALL替代
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE amount > 10000 AND user_id != 1001;

分库分表方案实战

单表数据量超过2000万行后，B+Tree索引层级增加导致查询性能下降。分库分表方案的选型取决于数据增长速度和查询模式：

# ShardingSphere-JDBC配置分片规则
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.0.1:3306/order_db_0
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.0.2:3306/order_db_1
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds${0..1}.orders_${0..15}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: orders-db-mod
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: orders-tbl-mod
        sharding-algorithms:
          orders-db-mod:
            type: MOD
            props:
              sharding-count: 2
          orders-tbl-mod:
            type: MOD
            props:
              sharding-count: 16

数据库高可用架构：MySQL MGR部署

MySQL Group Replication提供多主写入和自动故障转移，是数据备份恢复之外的高可用方案：

# my.cnf MGR配置
[mysqld]
plugin_load = 'group_replication.so'
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "10.0.0.1:33061"
group_replication_group_seeds = "10.0.0.1:33061,10.0.0.2:33061,10.0.0.3:33061"
group_replication_bootstrap_group = OFF
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF

# 启动MGR
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='password'
  FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

# 检查成员状态
SELECT * FROM performance_schema.replication_group_members;

Redis缓存策略与数据一致性

NoSQL选型应用中，Redis作为MySQL前置缓存是标准架构。缓存更新的核心矛盾是性能与一致性：

# Cache-Aside模式实现（Python伪代码）
def get_order(order_id):
    # 1. 查缓存
    cache_key = f"order:{order_id}"
    data = redis.get(cache_key)
    if data:
        return json.loads(data)

    # 2. 查数据库
    data = mysql.query("SELECT * FROM orders WHERE id = ?", order_id)
    if data:
        # 3. 写缓存，设置合理过期时间防雪崩
        redis.setex(cache_key, 300 + random.randint(0, 60), json.dumps(data))
    return data

def update_order(order_id, updates):
    # 1. 先更新数据库
    mysql.update("UPDATE orders SET ... WHERE id = ?", updates, order_id)
    # 2. 再删除缓存（而非更新缓存）
    redis.delete(f"order:{order_id}")

MySQL性能调优是数据库运维的核心技能。从慢查询日志定位问题，到索引优化消除全表扫描，再到分库分表突破单库上限，每个阶段都有明确的技术手段和验证方法。索引设计遵循最左前缀和高选择性优先原则，分片策略按业务查询模式选择分片键，缓存方案在一致性和性能间取权衡——掌握这些，就能应对绝大多数数据库性能问题。