myisam锁表机制
mysql存储引擎毫无疑问选择Innodb

思考大表统计的优化策略

妙音 posted @ 2018年9月15日 10:02 in mysql with tags 大数据 , 277 阅读

前言

以InnoDB表的count()统计为例子
 

方法一、直接count,全表扫描

 
* 分析
#一千万条数据count()耗费几十秒,基本不用干活
select count(*) from user
 

方法二、分段count,全表扫描转变为范围扫描

加上where条件,将全部查询,拆分成多个小范围查询。
#全表扫描
select count(*) from user;

#范围扫描
select count(*) from user where id < 1000000;
select count(*) from user where id>=1000000 and id < 200000;
 
实际中需要打开多个数据库连接,实际操作不是很好。
 
当然分表也属于这类方法
 
* 原理
大问题拆成多个小问题,将压力分散。
 
* 分析
如果是同一张表,需要打开多个连接(多个进程)
如果分库了,需要连接多个数据库
虽然能减少时间,但是不能减小压力,count()次数多了,系统也得嗝屁
 

方法三、增加tinyint类型字段并建立二级索引用于统计

 
二级索引key是索引列,value是主键,一个数据页上可以存储更多数据,减少了分页于页内移动
 
#加了之后再统计,mysql解析器会自动选择二级索引进行count
select count(*) from user
 
* 原理
利用二级索引减少io
 
* 分析
能够减少时间,也能减小io压力,但是时间还是挺长的,我的机器上一千万数据从5秒编成了1秒,1秒还是比较长
假如加上where条件呢,这个方法就用不上了
 

方法四、维护一张统计表

凡事预则立,不预则废,设计时加一张tablename_stats表(两个字段key,value),平时就维护好统计数据,增加时加1,删除时减1。
 
key value 
count                   1111
hash值(表名+where条件) 1111
 
* 原理
提前准备
 
* 分析
毫无压力,效果完美
需要提前预测好可能的统计以及条件
缺点存在统计数据与实际不一致,在大数据量情况下,也不算什么问题
 

总结

最终发现还是类似myisam维护一个计数器是最好的方法。
只是我们扩展了功能,加了where条件也提前计数
 
 
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)
此生必看的科学实验-水知道答案

 


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter