思考大表统计的优化策略

前言

以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条件的也提前计数

此生必看的科学实验-水知道答案
http://v.youku.com/v_show/id_XMjgzMzcwNDk4OA
《了凡四训》详解之改过之法
https://v.youku.com/v_show/id_XMzY5NTcxMDIwOA
印光大师十念法（胡小林主讲第1集）
http://v.youku.com/v_show/id_XMzUwMzc4NzY4NA
精神病为什么治不好
https://v.qq.com/x/page/h0359zvupc0.html
百病之源
https://v.qq.com/x/page/o0359x7oy11.html