性能优化之mysql大表count

一、前言

数据量大=字段多+数据条数+大字段类型=表性能差。

对于大表的定义，没有统一的标准，在mysql中一般是近千万数据，InnoDB的B+数层数过深会导致性能下降带来一系列的问题

二、优化方案

对于不同场景下的可考虑使用不同的方式来处理

• 对于不需要精确不高的场景，如图表类，可以采用根据查询条件生成唯一hash key，在一段时间内缓存count

该方案的缺点为：当缓存失效的这段时间内(取决于count的时间),所有的请求会直接打到数据库，即缓存击穿。 缓存击穿的常见解决方案：

1. 调整过期时间-加大 加大过期时间可以减少频率，缓解压力，但带来的问题会导致count 数据准确性进一步降低。
2. 读写分离，使用异步线程或定时任务来更新count数据，查询请求只读不写。该方案的核心在于保证读取的数据一定存在于缓存中，具体实现步骤如图

1. 使用互斥锁（Mutex Key），只让一个线程构建缓存，其他线程等待构建缓存执行完毕，重新从缓存中获取数据。单机通过synchronized或lock来处理，分布式环境采用分布式锁。

代码片段如下

public String get(key) {
    String value = redis.get(key);
     //缓存值过期
    if (value == null) {
        //设置3min的超时，防止del操作失败的时候，下次缓存过期一直不能load db
        if (redis.setnx(key_mutex, 1, 3 * 60) == 1) { 
            value = db.get(key);
                    redis.set(key, value, expire_time);
                    redis.del(key_mutex);
            } else {
                    sleep(80);
                    //重试
                    get(key);  
            }
        } else {
            return value;      
        }
}

• 上述办法解决有限已知的查询条件，对于用户输入未知类的查询效果仍然不佳，如分页列表用户输入订单明细行的名称等场景。

如果数据中包含不可变的时间字段（创建时间），可以使用缓存历史数据count+实时查询当前数据的方式来处理，不断累加更新count 和结束日期,当很久未再次使用时删除记录不在累加。此方案提升了数据的准确性于性能，唯有第一次key不存在的时候仍然会慢。

例：

key	查询条件json	count	结束日期	最后一次使用时间
a001	{"name":"张三"}	200	2023-01-01	2023-01-01
a002	{"name":"李四","age":18}	200	2023-01-02	2023-03-01

• 如果是列表接口，可去掉count，不在展示分页，修改为瀑布流式。类似于微信朋友圈、QQ空间、支付宝账单及银行流水等，一直加载固定条数，直到未查询到数据结束。

# MYSQL SQL
select  * from xx where ... limit 0,20; //第一次查询
select  * from xx where ... limit 20,20; //第二次查询

• 某些场景将历史数据备份迁移，或分库分表也是一种较为有效的方式。

• 结语

由于传统关系型数据库存在诸多限制和瓶颈，随着技术的演进，目前分布式数据库发展迅猛，相信在不久的将来此类问题终将不是问题。