性能优化之mysql大表count
站长
· 阅读数 14
一、前言
数据量大=字段多+数据条数+大字段类型=表性能差。
对于大表的定义,没有统一的标准,在mysql中一般是近千万数据,InnoDB的B+数层数过深会导致性能下降带来一系列的问题
二、优化方案
对于不同场景下的可考虑使用不同的方式来处理
-
对于不需要精确不高的场景,如图表类,可以采用根据查询条件生成唯一hash key,在一段时间内缓存count
YES
NO
请求
根据查询条件生成key
key 是否存在缓存中
返回缓存的count
查询DB并存储至缓存
结束
该方案的缺点为:当缓存失效的这段时间内(取决于count的时间),所有的请求会直接打到数据库,即缓存击穿。 缓存击穿的常见解决方案:
- 调整过期时间-加大 加大过期时间可以减少频率,缓解压力,但带来的问题会导致count 数据准确性进一步降低。
- 读写分离,使用异步线程或定时任务来更新count数据,查询请求只读不写。该方案的核心在于保证读取的数据一定存在于缓存中,具体实现步骤如图
读
写
查询请求线程
根据查询条件生成key
缓存的count
其他线程
查询DB
结束
- 使用互斥锁(Mutex Key),只让一个线程构建缓存,其他线程等待构建缓存执行完毕,重新从缓存中获取数据。单机通过synchronized或lock来处理,分布式环境采用分布式锁。
YES
NO
加锁成功
写入
加锁失败
递归循环
请求
根据查询条件生成key
key 是否存在缓存中
返回缓存的count
加锁
查询DB
等待其他线程完成
结束
代码片段如下
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 |
YES
增量查询未缓存count
count 累加
NO
请求
根据查询条件生成key
key 是否存在缓存表中
将key的结束日期新增到参数中
更新缓存表使用日期
返回count
查询DB并存储至缓存表
结束
-
如果是列表接口,可去掉count,不在展示分页,修改为瀑布流式。类似于微信朋友圈、QQ空间、支付宝账单及银行流水等,一直加载固定条数,直到未查询到数据结束。
# MYSQL SQL
select * from xx where ... limit 0,20; //第一次查询
select * from xx where ... limit 20,20; //第二次查询
-
某些场景将历史数据备份迁移,或分库分表也是一种较为有效的方式。
-
结语
由于传统关系型数据库存在诸多限制和瓶颈,随着技术的演进,目前分布式数据库发展迅猛,相信在不久的将来此类问题终将不是问题。