数据库PV从十万飙到千万网站却卡到崩溃资深DBA分享三个实战优化技巧

一、那个深夜，数据库直接趴了

凌晨两点，我手机狂震。运营群里已经炸锅了，”网站打不开了”、”用户投诉太多了”、”怎么办怎么办”。

公司旗下的内容平台，PV从每天的十万级突然飙到五百万，第三天更是突破了两千万。原本部署在腾讯云上的MySQL集群，扛不住这么大的流量冲击，CPU直接打满，连接数爆表，查询响应时间从几十毫秒变成了几十秒，甚至超时。

那一刻，整个技术团队都慌了。

但作为DBA，我知道慌也没用。数据库出了问题，就得冷静分析。于是，我开始了长达三天的”抢救”工作。

二、第一个问题：慢查询像病毒一样扩散

2.1 问题的表象

网站崩溃的前兆，其实是运营反馈的一个小问题：首页加载变慢了。

起初，大家以为只是图片加载慢，或者是CDN的问题。但仔细排查后，我们发现是数据库查询越来越慢。

用SHOW PROCESSLIST看一下，大量的查询处于Sending data状态，还有部分查询在Locked状态等待锁。

SHOW FULL PROCESSLIST;

看到的结果是：几百个连接同时卡在慢查询上，整个数据库几乎被拖死。

2.2 问题的根源：一条SQL拖垮整个系统

经过分析，最要命的是这条查询：

SELECT 
    p.id,
    p.title,
    p.summary,
    p.cover_url,
    p.create_time,
    c.name AS category_name,
    u.nickname,
    COUNT(DISTINCT cmt.id) AS comment_count
FROM post p
LEFT JOIN category c ON p.category_id = c.id
LEFT JOIN user u ON p.user_id = u.id
LEFT JOIN comment cmt ON cmt.post_id = p.id
WHERE p.status = 1
ORDER BY p.create_time DESC
LIMIT 0, 20;

这条SQL出现在首页的推荐列表查询中。

问题在哪里？

让我一条一条说：

1. LEFT JOIN comment表导致结果集爆炸

这条SQL用LEFT JOIN去关联comment表，目的是统计每篇文章的评论数量。

但问题是，一篇文章可能有几百条评论，这一关联，返回的行数就会爆炸。

比如：

文章A有50条评论
文章B有30条评论
文章C有80条评论

那么，即使只查20篇文章，返回的行数也可能是50 + 30 + 80 = 160行，甚至更多。

这就导致：

查询结果集很大
需要排序的数据量很大
内存占用很高

2. ORDER BY create_time DESC 导致文件排序

当数据量变大时，ORDER BY操作会导致MySQL进行文件排序（filesort），而不是使用索引排序。

文件排序的速度很慢，尤其是在大数据量的情况下。

3. 没有有效的索引覆盖

这条SQL没有合适的索引来支持，导致全表扫描。

2.3 优化方案：分拆查询

对于这种”列表 + 统计”的场景，正确的做法是：

先查列表，再单独查统计。

优化后的方案：

-- 第一步：查询文章列表
SELECT 
    p.id,
    p.title,
    p.summary,
    p.cover_url,
    p.create_time,
    c.name AS category_name,
    u.nickname
FROM post p
LEFT JOIN category c ON p.category_id = c.id
LEFT JOIN user u ON p.user_id = u.id
WHERE p.status = 1
ORDER BY p.create_time DESC
LIMIT 0, 20;

-- 第二步：批量查询评论数（使用子查询优化）
SELECT 
    post_id,
    COUNT(*) AS comment_count
FROM comment
WHERE post_id IN (101, 102, 103, ..., 120)  -- 第一步查到的20篇文章ID
GROUP BY post_id;

这样，原本一个复杂的JOIN查询，变成了两个简单的查询。

第一步：查询文章列表，只关联category和user表，数据量小，速度快。

第二步：批量查询评论数，使用IN子句，避免大量的JOIN。

2.4 索引优化

除了拆分查询，还需要加上合适的索引。

post表需要以下索引：

-- 用于WHERE条件过滤
ALTER TABLE post ADD INDEX idx_status_create_time (status, create_time);

-- 用于关联查询
ALTER TABLE post ADD INDEX idx_category_id (category_id);
ALTER TABLE post ADD INDEX idx_user_id (user_id);

comment表需要以下索引：

-- 用于批量查询评论数
ALTER TABLE comment ADD INDEX idx_post_id (post_id);

2.5 效果

优化后，这条SQL的查询时间从原来的2-3秒，降到了50毫秒以内。

数据库的负载也明显下降，网站的响应速度恢复正常。

三、第二个问题：连接数爆满

3.1 问题的表象

第一次优化慢查询后，网站恢复了一些。但第二天，问题又来了。

这次的问题是：连接数爆满。

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

查询结果：

Threads_connected: 480
Max_used_connections: 480
max_connections: 500

连接数几乎要用完了。

3.2 问题的根源：连接泄漏

连接数爆满，通常有两种可能：

真实的并发连接数太高
连接泄漏（申请了连接但没有释放）

经过排查，我们发现是第二种情况。

代码中存在的问题：

很多开发人员在使用数据库连接时，没有正确释放连接。

比如，这种写法：

// 错误的写法
Connection conn = DataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();

// 忘记关闭连接，或者在异常时没有关闭
// 这就导致连接泄漏

正确的写法：

// 正确的写法：使用try-with-resources自动关闭
try (Connection conn = DataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {
    
    // 处理结果集
    while (rs.next()) {
        // ...
    }
    
} catch (SQLException e) {
    e.printStackTrace();
}
// try-with-resources会自动关闭连接

3.3 优化方案：连接池优化

除了修复代码中的连接泄漏问题，还需要优化连接池的配置。

使用HikariCP连接池（Spring Boot默认）：

spring:
  datasource:
    hikari:
      # 连接池最大连接数（根据实际情况调整）
      maximum-pool-size: 20
      # 最小空闲连接数
      minimum-idle: 5
      # 连接超时时间（毫秒）
      connection-timeout: 30000
      # 空闲连接超时时间（毫秒）
      idle-timeout: 600000
      # 连接最大生命周期（毫秒）
      max-lifetime: 1800000
      # 连接测试查询
      connection-test-query: SELECT 1

关键配置说明：

maximum-pool-size：最大连接数。需要根据实际业务场景调整，不要设置太大。
minimum-idle：最小空闲连接数。保持一定的空闲连接，避免频繁创建连接。
connection-timeout：连接超时时间。如果获取连接超时，会抛出异常。
idle-timeout：空闲连接超时时间。超过这个时间的空闲连接会被释放。
max-lifetime：连接最大生命周期。超过这个时间的连接会被强制释放。

3.4 效果

优化连接池配置后，数据库的连接数稳定在合理范围内，不再有连接爆满的问题。

四、第三个问题：缓存没用好

4.1 问题的表象

慢查询优化了，连接数问题也解决了，但网站还是不够快。

特别是首页的热门内容，每次查询都要打到数据库，性能还是不够好。

4.2 问题的根源：缓存缺失

对于这种高频读取、低频写入的数据，应该使用缓存来减轻数据库的压力。

我们的方案：使用Redis缓存热门内容

缓存策略：

缓存热门文章列表：首页推荐的文章列表，缓存1分钟。
缓存文章内容：文章详情，缓存5分钟。
缓存评论数：文章的评论数，缓存10分钟。

代码实现（Spring Boot）：

@Service
public class PostService {
    
    @Autowired
    private PostMapper postMapper;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    private static final String POST_LIST_KEY = "post:list:home";
    private static final String POST_DETAIL_KEY = "post:detail:";
    private static final String COMMENT_COUNT_KEY = "post:comment:count:";
    
    /**
     * 获取首页文章列表
     */
    public List<Post> getHomePostList() {
        // 先从缓存获取
        String key = POST_LIST_KEY + ":1min";
        List<Post> cached = (List<Post>) redisTemplate.opsForValue().get(key);
        if (cached != null) {
            return cached;
        }
        
        // 缓存没有，从数据库查询
        List<Post> posts = postMapper.selectHomePostList();
        
        // 写入缓存，1分钟过期
        redisTemplate.opsForValue().set(key, posts, 1, TimeUnit.MINUTES);
        
        return posts;
    }
    
    /**
     * 获取文章详情
     */
    public Post getPostDetail(Long postId) {
        // 先从缓存获取
        String key = POST_DETAIL_KEY + postId;
        Post cached = (Post) redisTemplate.opsForValue().get(key);
        if (cached != null) {
            return cached;
        }
        
        // 缓存没有，从数据库查询
        Post post = postMapper.selectById(postId);
        
        // 写入缓存，5分钟过期
        redisTemplate.opsForValue().set(key, post, 5, TimeUnit.MINUTES);
        
        return post;
    }
    
    /**
     * 获取文章评论数
     */
    public long getCommentCount(Long postId) {
        // 先从缓存获取
        String key = COMMENT_COUNT_KEY + postId;
        Long cached = (Long) redisTemplate.opsForValue().get(key);
        if (cached != null) {
            return cached;
        }
        
        // 缓存没有，从数据库查询
        long count = postMapper.countComments(postId);
        
        // 写入缓存，10分钟过期
        redisTemplate.opsForValue().set(key, count, 10, TimeUnit.MINUTES);
        
        return count;
    }
    
    /**
     * 文章更新时，清除缓存
     */
    public void invalidateCache(Long postId) {
        // 清除文章详情缓存
        redisTemplate.delete(POST_DETAIL_KEY + postId);
        
        // 清除评论数缓存
        redisTemplate.delete(COMMENT_COUNT_KEY + postId);
        
        // 清除首页列表缓存（因为数据可能变化）
        redisTemplate.delete(POST_LIST_KEY + ":1min");
    }
}

4.3 缓存穿透问题

缓存穿透是指：查询的数据在缓存和数据库中都不存在，每次查询都会打到数据库。

解决方案：缓存空值

public Post getPostDetail(Long postId) {
    String key = POST_DETAIL_KEY + postId;
    
    // 先从缓存获取
    Object cached = redisTemplate.opsForValue().get(key);
    if (cached != null) {
        // 如果是空值，说明数据不存在
        if ("NULL".equals(cached)) {
            return null;
        }
        return (Post) cached;
    }
    
    // 缓存没有，从数据库查询
    Post post = postMapper.selectById(postId);
    
    if (post == null) {
        // 数据库也没有，缓存空值，5分钟过期
        redisTemplate.opsForValue().set(key, "NULL", 5, TimeUnit.MINUTES);
        return null;
    }
    
    // 写入缓存，5分钟过期
    redisTemplate.opsForValue().set(key, post, 5, TimeUnit.MINUTES);
    
    return post;
}

4.4 缓存击穿问题

缓存击穿是指：某个热点数据在过期瞬间，大量请求同时打到数据库。

解决方案：互斥锁

public Post getPostDetailWithLock(Long postId) {
    String key = POST_DETAIL_KEY + postId;
    
    // 先从缓存获取
    Post cached = (Post) redisTemplate.opsForValue().get(key);
    if (cached != null) {
        return cached;
    }
    
    // 使用分布式锁
    String lockKey = "lock:" + key;
    boolean locked = redisTemplate.opsForValue()
        .setIfAbsent(lockKey, "1", 10, TimeUnit.SECONDS);
    
    if (locked) {
        try {
            // 双重检查
            cached = (Post) redisTemplate.opsForValue().get(key);
            if (cached != null) {
                return cached;
            }
            
            // 从数据库查询
            Post post = postMapper.selectById(postId);
            
            // 写入缓存
            redisTemplate.opsForValue().set(key, post, 5, TimeUnit.MINUTES);
            
            return post;
        } finally {
            // 释放锁
            redisTemplate.delete(lockKey);
        }
    } else {
        // 获取锁失败，休眠后重试
        try {
            Thread.sleep(100);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
        return getPostDetailWithLock(postId);
    }
}

4.5 效果

使用Redis缓存后，数据库的查询压力减少了90%以上，网站的响应速度提升了5-10倍。

五、总结一下

经历了这次从十万PV到千万PV的冲击，我总结了三个核心的优化技巧：

慢查询优化：拆分复杂查询，加上合适的索引，避免全表扫描和大量的JOIN操作。
连接数管理：修复连接泄漏，优化连接池配置，避免连接爆满。
缓存策略：对于高频读取的数据，使用Redis缓存，减轻数据库压力。

当然，这三个技巧只是我们优化过程中的冰山一角。在实际生产中，还有很多的细节需要注意，比如：

数据库主从复制的延迟问题
分库分表的方案选择
读写分离的配置
数据库参数的调优
监控告警的设置

总之，数据库优化是一个持续的过程，需要不断地监控、分析、优化。

希望这篇分享能对你有所帮助。如果有问题，欢迎在评论区交流。

正文

数据库PV从十万飙到千万网站却卡到崩溃资深DBA分享三个实战优化技巧

数据库PV从十万飙到千万网站却卡到崩溃资深DBA分享三个实战优化技巧

一、那个深夜，数据库直接趴了

二、第一个问题：慢查询像病毒一样扩散

2.1 问题的表象

2.2 问题的根源：一条SQL拖垮整个系统

2.3 优化方案：分拆查询

2.4 索引优化

2.5 效果

三、第二个问题：连接数爆满

3.1 问题的表象

3.2 问题的根源：连接泄漏

3.3 优化方案：连接池优化

3.4 效果

四、第三个问题：缓存没用好

4.1 问题的表象

4.2 问题的根源：缓存缺失

4.3 缓存穿透问题

4.4 缓存击穿问题

4.5 效果

五、总结一下

相关阅读

揭秘网站流量密码：如何通过接口PV提升网站访问量

拿铁咖啡杯尺寸揭秘：常见长宽比例大公开

揭秘国产光伏：如何从幕后走向台前，领跑绿色能源新时代？

长安欧尚7座家用车价格揭秘：全面解析不同配置与优惠信息

热力学PV图轻松计算温度：掌握公式，一看便知！

热力学PV图轻松算温度，快速掌握物理奥秘

揭秘王者荣耀李逍遥PV：技能解析与实战技巧全攻略

孩子考试，家长如何轻松掌握学习进度？一招教你轻松审核学习成果

激光雷达技术如何改变光伏发电效率与成本效益

银河笑星大揭秘：喜剧短片背后的那些事

数据库PV从十万飙到千万网站却卡到崩溃 资深DBA分享三个实战优化技巧

一、那个深夜，数据库直接趴了

二、第一个问题：慢查询像病毒一样扩散

2.1 问题的表象

2.2 问题的根源：一条SQL拖垮整个系统

2.3 优化方案：分拆查询

2.4 索引优化

2.5 效果

三、第二个问题：连接数爆满

3.1 问题的表象

3.2 问题的根源：连接泄漏

3.3 优化方案：连接池优化

3.4 效果

四、第三个问题：缓存没用好

4.1 问题的表象

4.2 问题的根源：缓存缺失

4.3 缓存穿透问题

4.4 缓存击穿问题

4.5 效果

五、总结一下

相关阅读

揭秘网站流量密码：如何通过接口PV提升网站访问量

拿铁咖啡杯尺寸揭秘：常见长宽比例大公开

揭秘国产光伏：如何从幕后走向台前，领跑绿色能源新时代？

长安欧尚7座家用车价格揭秘：全面解析不同配置与优惠信息

热力学PV图轻松计算温度：掌握公式，一看便知！

热力学PV图轻松算温度，快速掌握物理奥秘

揭秘王者荣耀李逍遥PV：技能解析与实战技巧全攻略

孩子考试，家长如何轻松掌握学习进度？一招教你轻松审核学习成果

激光雷达技术如何改变光伏发电效率与成本效益

银河笑星大揭秘：喜剧短片背后的那些事

数据库PV从十万飙到千万网站却卡到崩溃资深DBA分享三个实战优化技巧