记单表数据较多的数据库查询实例及测试结果

【背景条件】

1.需要关联查询两张表icm和crd
2.crd表和icm表分属不同数据库
3.crd表很大，用mycat按月分库处理（500W条数据平均每月）

【业务逻辑实现流程】
1.首先从icm表中做一次呼叫类型统计

mysql> select call_type,count(1) from icm where add_time >'2017-03-01 23:55:00' and add_time<'2017-03-01 23:59:00' group by call_type;
+--------------+--------+
| call_type    | COUNT1 |
+--------------+--------+
| 2            |      2 |
| 4            |      3 |
| 6            |      1 |
+--------------+--------+

2.取出每一种呼叫类型的呼叫编号（ucid）

mysql> select ucid from icm where add_time >'2017-03-01 23:55:00' and add_time<'2017-03-01 23:59:00' and call_type='4';
+----------------------+
| ucid                 |
+----------------------+
| 00100068631488383689 |
| 00100068711488383746 |
| 00100068881488383905 |
+----------------------+

3.根据呼叫编号从crd表中算出这些呼叫的总的时间（步骤2得到的呼叫编号和此表的CallID项目一致）

mysql> select sum(callTimeLen) from crd where StatisticDate >'2017-03-01 23:55:00' and StatisticDate<'2017-03-01 23:59:00' and CallID in('00100068631488383689','00100068711488383746','00100068881488383905');
+------+
| SUM0 |
+------+
| 551  |
+------+

【测试结果】

3/1~3/8 一周
the operation take 13.0s

3/1~3/15 二周
the operation take 32.5s

3/1~3/22 三周
the operation take 45.5s

3/1~3/29 一个月
the operation take 65.0s

【问题】

很显然用户体验不好。
是数据多的必然吗？
还是有更优雅的方法或者逻辑？
请问这些查询逻辑如何从sql层面优化？
望恰好看到这篇文章的你不吝赐教，感谢。

 

【思路】

一、把数据根据需求，选择一个合适的细粒度提前计算出来。

因为历史数据的统计是固定的。即同样的数据同样的统计条件，统计100次的结果是一样的。

在这次需求中，我按照天的细粒度提前将数据计算出来，放到一张新的表中。效果是不言而喻的。

一个月500万条的数据。

按照天来计算整理，得到一个30*6（一天6条数据）=180条的数据。

再统计细粒度为整数天的时间就变的轻松又愉快了。

而小于一天的统计仍然调用原有逻辑由于数据量500/30=不到20万不大，统计时间也是可以接受的。