性能优化之mysql大表count

2023-05-13 09:50| 来源: 网络整理| 查看: 265

一、前言

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

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

二、优化方案

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

对于不需要精确不高的场景，如图表类，可以采用根据查询条件生成唯一hash key，在一段时间内缓存count graph LR 请求 --> 根据查询条件生成key 根据查询条件生成key --> conditionA{key 是否存在缓存中} conditionA -- YES --> 返回缓存的count conditionA -- NO --> 查询DB并存储至缓存查询DB并存储至缓存 --> 返回缓存的count 返回缓存的count--> 结束结束

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

调整过期时间-加大加大过期时间可以减少频率，缓解压力，但带来的问题会导致count 数据准确性进一步降低。读写分离，使用异步线程或定时任务来更新count数据，查询请求只读不写。该方案的核心在于保证读取的数据一定存在于缓存中，具体实现步骤如图 graph LR 查询请求线程 --> 根据查询条件生成key 根据查询条件生成key --读--> 缓存的count 其他线程 --> 查询DB 查询DB -- 写 --> 缓存的count 缓存的count--> 结束结束使用互斥锁（Mutex Key），只让一个线程构建缓存，其他线程等待构建缓存执行完毕，重新从缓存中获取数据。单机通过synchronized或lock来处理，分布式环境采用分布式锁。 graph LR 请求 --> 根据查询条件生成key 根据查询条件生成key --> conditionA{key 是否存在缓存中} conditionA -- YES --> 返回缓存的count conditionA -- NO --> conditionB{加锁} conditionB -- 加锁成功 --> 查询DB 查询DB --写入--> 返回缓存的count conditionB -- 加锁失败 --> 等待其他线程完成等待其他线程完成 -- 递归循环--> 返回缓存的count 返回缓存的count--> 结束结束

代码片段如下

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查询条件jsoncount结束日期最后一次使用时间a001{"name":"张三"}2002023-01-012023-01-01a002{"name":"李四","age":18}2002023-01-022023-03-01 graph TD 请求 --> 根据查询条件生成key 根据查询条件生成key --> conditionA{key 是否存在缓存表中} conditionA -- YES --> selectdb{将key的结束日期新增到参数中} selectdb -- 增量查询未缓存count -->更新缓存表使用日期更新缓存表使用日期 -- count 累加 --> 返回count conditionA -- NO --> 查询DB并存储至缓存表查询DB并存储至缓存表 --> 返回count 返回count--> 结束结束如果是列表接口，可去掉count，不在展示分页，修改为瀑布流式。类似于微信朋友圈、QQ空间、支付宝账单及银行流水等，一直加载固定条数，直到未查询到数据结束。 # MYSQL SQL select * from xx where ... limit 0,20; //第一次查询 select * from xx where ... limit 20,20; //第二次查询复制代码某些场景将历史数据备份迁移，或分库分表也是一种较为有效的方式。结语

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

【本文地址】

公司简介

联系我们