思考大表统计的优化策略


前言

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



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