MySQL性能调优实战：我从慢查询日志里挖出的那些隐藏杀手

小编 • • 数据库

说句实话，我做数据库运维这些年，最深的一个体会就是：性能问题从来不会写在脸上。你盯着Grafana面板看CPU、内存、IO都正常，但业务方就是反馈接口慢。最后翻出来一看，全是藏在慢查询日志里的”隐形杀手”。今天这篇文章，我就把这几年排查过的几个典型案例掰开揉碎了讲讲，包括pt-query-digest分析、索引优化、隐式类型转换、分库分表跨片查询、Redis缓存三大问题，以及MySQL高可用和备份策略。都是真实踩过的坑，不是教科书搬运。

一、慢查询日志分析：pt-query-digest是我的救命稻草

很多人开了慢查询日志就完事了，打开一看几千行SQL，根本不知道从哪下手。我之前也这样，后来发现pt-query-digest这个工具简直是神器。它能把慢查询日志按执行频率、平均耗时、总耗时等多个维度聚合，直接告诉你哪些SQL最值得优化。

先开启慢查询日志：

-- my.cnf 配置
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

-- 或者动态开启（不重启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

然后分析慢查询日志，我一般用这个命令：

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 只看Top 10慢查询
pt-query-digest --limit 10 /var/log/mysql/slow.log

# 按数据库过滤
pt-query-digest --filter '$event->{db} && $event->{db} eq "order_db"' /var/log/mysql/slow.log

报告里有个东西我特别关注——”Rows examined”和”Rows sent”的比值。如果扫描了10万行只返回3行，那基本上就是索引没建对或者写法有问题。我见过最夸张的一个案例，一条查询扫了800万行返回2行，就因为WHERE条件里少了一个索引。

二、复合索引优化：orders表的教训

去年我们有个订单系统，用户查”最近30天某状态的订单”，接口响应从200ms涨到了3秒。pt-query-digest一跑，发现就是这条SQL：

SELECT * FROM orders 
WHERE user_id = 12345 
  AND order_status = 'PAID' 
  AND create_time >= '2025-06-01' 
  AND create_time < '2025-07-01'
ORDER BY create_time DESC 
LIMIT 20;

我当时用EXPLAIN看了下，type是ALL，全表扫描。orders表有3000万行数据，全扫一遍能不慢吗？

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND order_status = 'PAID' 
  AND create_time >= '2025-06-01' 
  AND create_time < '2025-07-01'
ORDER BY create_time DESC 
LIMIT 20;

-- 结果：type=ALL, rows=30000000, Extra=Using where; Using filesort

问题很明显，user_id上虽然有单列索引，但order_status和create_time没有走索引，而且还有filesort。最左前缀原则大家都懂，但关键是索引列的顺序。我加了一个复合索引：

-- 复合索引：等值条件放前面，范围条件放后面
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, order_status, create_time);

-- 再次EXPLAIN
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND order_status = 'PAID' 
  AND create_time >= '2025-06-01' 
  AND create_time < '2025-07-01'
ORDER BY create_time DESC 
LIMIT 20;

-- 结果：type=ref, rows=856, Extra=Using index condition

加完索引之后，扫描行数从3000万降到800多行，响应时间从3秒降到15ms。一个索引搞定。这里有个细节我想强调：复合索引的列顺序很重要，等值查询的列放前面，范围查询的列放后面，否则范围条件后面的列就用不上索引了。我见过不少人把create_time放前面，user_id放后面，结果只命中了时间范围，后面全白搭。

三、隐藏杀手#1：隐式类型转换

这个坑我是真被坑过，而且排查了很久。有个接口偶发慢查询，大部分时候几十毫秒，偶尔飙到2-3秒。表结构是这样的：

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) NOT NULL,
    username VARCHAR(50),
    -- ... 其他字段
    INDEX idx_phone (phone)
) ENGINE=InnoDB;

phone字段是VARCHAR类型，索引也建了。但是Java代码里的查询长这样：

-- 应用层传过来的SQL（参数绑定类型不对）
SELECT id, username, phone FROM users WHERE phone = 13800138000;

-- 而不是
SELECT id, username, phone FROM users WHERE phone = '13800138000';

看到区别了吗？phone字段是VARCHAR，但查询条件传了个数字。MySQL会做隐式类型转换，把phone列的每一行都转成数字再去比较。这下索引直接废了，变成全表扫描。

-- 隐式转换：索引失效，全表扫描
EXPLAIN SELECT id, username, phone FROM users WHERE phone = 13800138000;
-- type=ALL, rows=5000000, key=NULL

-- 正确写法：索引命中
EXPLAIN SELECT id, username, phone FROM users WHERE phone = '13800138000';
-- type=ref, rows=1, key=idx_phone

这个问题在MyBatis里尤其容易踩。如果你的Mapper写的是`WHERE phone = #{phone}`，而Java层的phone字段类型是Long而不是String，MyBatis会把它当数字传进去。后来我把所有涉及字符串字段的查询参数都检查了一遍，统一用String类型，还在代码review里加了一条规则：WHERE条件的参数类型必须和字段类型一致。

还有一个小技巧，可以用SHOW WARNINGS来看MySQL实际执行的SQL：

EXPLAIN EXTENDED SELECT id FROM users WHERE phone = 13800138000;
SHOW WARNINGS;
-- 会显示MySQL内部转换后的SQL：CAST(phone AS ...) = 13800138000

四、隐藏杀手#2：ShardingSphere跨片查询

我们做分库分表的时候，用了ShardingSphere-JDBC。分片键是user_id，按user_id取模分了16个库128张表。大部分查询都带着user_id，路由到单表，没啥问题。但有几个统计类查询没带user_id，直接全片扫描。

-- 这种查询在分库分表后性能急剧下降
SELECT order_status, COUNT(*) as cnt 
FROM orders 
WHERE create_time >= '2025-07-01' 
  AND create_time < '2025-07-23'
GROUP BY order_status;

这条SQL在单库的时候走create_time索引，200ms返回。分了128张表之后，ShardingSphere要把它下推到128张表分别执行，然后归并结果。网络开销加归并排序，直接飙到4-5秒。

我的解决方案是分两步走。第一步，对于实时性要求不高的统计，用定时任务预计算：

// Spring Boot 定时任务，每小时跑一次
@Scheduled(cron = "0 0 * * * ?")
public void aggregateOrderStats() {
    // XxlJob 或 Spring Scheduled 触发
    // 分别查询各分片，本地归并
    Map<String, Long> result = new HashMap<>();
    
    for (int i = 0; i < 16; i++) {
        List<OrderStatDTO> shardResult = orderMapper.countByStatusOnShard(i, startDate, endDate);
        for (OrderStatDTO dto : shardResult) {
            result.merge(dto.getOrderStatus(), dto.getCnt(), Long::sum);
        }
    }
    
    // 写入汇总表
    orderStatsMapper.batchInsert(result, statDate);
}

第二步，对实时性要求高的，引入ES做查询。订单写入MySQL的同时，异步同步到Elasticsearch。统计类查询直接走ES的聚合，毫秒级返回。

// ShardingSphere 数据分片配置
// application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,...,ds15
    sharding:
      tables:
        orders:
          actual-data-nodes: ds${0..15}.orders_${0..127}
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds${user_id % 16}
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: orders_${user_id % 128}
          key-generator:
            column: id
            type: SNOWFLAKE

分库分表之后，我学到最重要的一课就是：分片键的选择决定了你后面80%的查询性能。选错了分片键，后面全是补救。我们当时选user_id就是对的，因为绝大部分业务场景都围绕用户展开。但如果你有大量不带分片键的查询需求，要么改业务逻辑带上分片键，要么上ES/ClickHouse做查询层，别硬扛跨片查询。

五、Redis缓存策略：穿透、击穿、雪崩一个都不能漏

数据库优化到极致之后，下一个瓶颈就是QPS。我们核心接口的读QPS在高峰期能到3-4万，MySQL扛不住，必须上Redis缓存。但缓存不是加上去就完事了，三大经典问题你绕不过去。

缓存穿透——查一个根本不存在的key，每次都打到数据库。黑客拿一堆不存在的ID请求，数据库直接被打挂。我的方案是布隆过滤器：

@Service
public class OrderCacheService {

    @Autowired
    private RedissonClient redissonClient;
    
    @Autowired
    private OrderMapper orderMapper;
    
    private RBloomFilter<Long> bloomFilter;
    
    @PostConstruct
    public void init() {
        bloomFilter = redissonClient.getBloomFilter("order:bloom");
        // 预计元素5000万，误判率0.01%
        bloomFilter.tryInit(50000000L, 0.0001);
        
        // 启动时加载所有订单ID到布隆过滤器
        List<Long> orderIds = orderMapper.selectAllOrderIds();
        for (Long id : orderIds) {
            bloomFilter.add(id);
        }
    }
    
    public Order getOrderById(Long orderId) {
        // 第一层：布隆过滤器拦截不存在的ID
        if (!bloomFilter.contains(orderId)) {
            return null;  // 一定不存在，直接返回
        }
        
        // 第二层：查Redis缓存
        String cacheKey = "order:detail:" + orderId;
        Order order = (Order) redissonClient.getBucket(cacheKey).get();
        if (order != null) {
            return order;
        }
        
        // 第三层：查数据库
        order = orderMapper.selectById(orderId);
        if (order != null) {
            // 写入缓存，随机过期时间防止雪崩
            int ttl = 1800 + ThreadLocalRandom.current().nextInt(600);
            redissonClient.getBucket(cacheKey).set(order, ttl, TimeUnit.SECONDS);
        } else {
            // 空值缓存，短TTL
            redissonClient.getBucket(cacheKey).set(NullObject.INSTANCE, 60, TimeUnit.SECONDS);
        }
        return order;
    }
}

缓存击穿——某个热点key突然过期，大量请求同时打到数据库。我用Redisson的分布式锁来保证只有一个请求去查数据库：

public Order getHotOrder(Long orderId) {
    String cacheKey = "order:detail:" + orderId;
    RBucket<Order> bucket = redissonClient.getBucket(cacheKey);
    
    Order order = bucket.get();
    if (order != null) {
        return order;
    }
    
    // 获取分布式锁，防止缓存击穿
    RLock lock = redissonClient.getLock("lock:order:" + orderId);
    try {
        // 尝试加锁，等待3秒，锁自动释放10秒
        if (lock.tryLock(3, 10, TimeUnit.SECONDS)) {
            try {
                // 双重检查
                order = bucket.get();
                if (order != null) {
                    return order;
                }
                
                // 查数据库并回写缓存
                order = orderMapper.selectById(orderId);
                if (order != null) {
                    bucket.set(order, 1800, TimeUnit.SECONDS);
                }
                return order;
            } finally {
                if (lock.isHeldByCurrentThread()) {
                    lock.unlock();
                }
            }
        } else {
            // 没拿到锁，短暂等待后重试读缓存
            Thread.sleep(50);
            return bucket.get();
        }
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        return null;
    }
}

缓存雪崩——大量key同时过期，请求全部打到数据库。解决办法就是过期时间加随机值，上面代码里已经体现了。另外我还配了Hystrix做熔断降级，数据库扛不住的时候直接返回降级数据：

@HystrixCommand(
    fallbackMethod = "getOrderFallback",
    commandProperties = {
        @HystrixProperty(name = "circuitBreaker.requestVolumeThreshold", value = "20"),
        @HystrixProperty(name = "circuitBreaker.errorThresholdPercentage", value = "50"),
        @HystrixProperty(name = "circuitBreaker.sleepWindowInMilliseconds", value = "5000")
    }
)
public Order getOrderWithCircuitBreaker(Long orderId) {
    return getOrderById(orderId);
}

public Order getOrderFallback(Long orderId) {
    // 返回降级数据或默认值
    Order fallback = new Order();
    fallback.setId(orderId);
    fallback.setOrderStatus("UNKNOWN");
    fallback.setNote("数据暂时不可用，请稍后重试");
    return fallback;
}

六、MySQL高可用：MHA方案实践

单点MySQL迟早会出事，我们用的是MHA（Master High Availability）做主从切换。一主两从的架构，MHA Manager监控主库健康状态，主库挂了自动选举最新的从库提升为新主库。

# MHA Manager 配置 /etc/mha/app1.cnf
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
user=mha_monitor
password=YourMhaPassword123
repl_user=repl
repl_password=ReplPassword123
ssh_user=mysql

[server1]
hostname=192.168.1.101
port=3306
candidate_master=1
check_repl_delay=0

[server2]
hostname=192.168.1.102
port=3306
candidate_master=1

[server3]
hostname=192.168.1.103
port=3306
# 不参与主库选举，纯备库
no_master=1

# 启动MHA监控
masterha_manager --conf=/etc/mha/app1.cnf &

# 检查SSH免密配置
masterha_check_ssh --conf=/etc/mha/app1.cnf

# 检查主从复制状态
masterha_check_repl --conf=/etc/mha/app1.cnf

# 手动切换主库（维护时用）
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.1.102

MHA的切换时间一般在10-30秒，配合应用层的连接池重连机制，基本能做到用户无感知。但我建议切换后要做一次数据一致性校验，用pt-table-checksum和pt-table-sync来对比主从数据：

# 检查主从数据一致性
pt-table-checksum --host=192.168.1.102 --user=checksum_user --password=xxx \
  --databases=order_db --no-check-binlog-format

# 如果发现不一致，同步修复
pt-table-sync --execute --replicate percona.checksums \
  --host=192.168.1.102 --user=sync_user --password=xxx

七、数据备份恢复：3-2-1原则不能忘

最后说备份，这是底线。不管你架构多牛，备份没做好就是裸奔。我一直遵循3-2-1备份原则：3份数据副本，2种不同存储介质，1份异地存储。

我的备份方案是这样的：

#!/bin/bash
# MySQL全量备份脚本，每天凌晨2点执行
BACKUP_DIR=/data/backup/mysql
DATE=$(date +%Y%m%d)
BACKUP_FILE=$BACKUP_DIR/full_backup_$DATE.sql.gz
RETENTION_DAYS=7

# 使用xtrabackup做物理备份（比mysqldump快很多）
innobackupex --user=backup_user --password=BackupPassword123 \
  --compress --compress-threads=4 \
  --stream=xbstream \
  /tmp/backup | gzip > $BACKUP_FILE

# 上传到对象存储（异地备份）
ossutil cp $BACKUP_FILE oss://company-backup/mysql/$(hostname)/full_backup_$DATE.sql.gz

# 清理本地超过7天的备份
find $BACKUP_DIR -name "full_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 记录备份日志
echo "[$DATE] Backup completed: $BACKUP_FILE" >> /var/log/mysql_backup.log

-- 增量备份用binlog，配合全量备份做时间点恢复
-- 查看当前binlog位置
SHOW MASTER STATUS;

-- 基于时间点的恢复
mysqlbinlog --start-datetime="2025-07-23 10:00:00" \
  --stop-datetime="2025-07-23 14:30:00" \
  /var/lib/mysql/mysql-bin.000123 | mysql -u root -p

-- 基于位置的恢复（更精确）
mysqlbinlog --start-position=123456 \
  --stop-position=234567 \
  /var/lib/mysql/mysql-bin.000123 | mysql -u root -p

但备份不是存了就万事大吉，一定要定期做恢复演练。我每个季度都会搞一次故障演练，模拟主库宕机，验证MHA切换是否正常，验证备份能否成功恢复。去年有一次真出事了，主库磁盘故障，因为做过恢复演练，20分钟就把业务恢复了，没出大事故。如果没演练过，真到出事的时候手忙脚乱，恢复流程都对不上。

总结：数据库优化是个系统工程

回过头看，这些年的数据库优化经验可以归纳成几条：第一，慢查询日志是你最好的朋友，pt-query-digest帮你找到方向；第二，索引优化是性价比最高的手段，但要注意复合索引的列顺序和类型匹配；第三，隐式类型转换和跨片查询是两个最容易忽略的杀手，排查时务必检查；第四，缓存策略要系统设计，穿透击穿雪崩都得有方案；第五，高可用和备份是最后的保险，3-2-1原则不能打折扣。

数据库运维没有银弹，每个系统都有自己的特点。但方法论是通用的：先定位问题，再分析原因，最后对症下药。希望这些实战经验能帮到正在踩坑的你。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-wo-cong-man-cha-xun-ri/

赞 (0)
小编小编
0
上一篇 17小时前
下一篇 17小时前

相关推荐