表结构
表中大概有 500 多万条数据,表每天在以 11 万条数据增长
现在执行 sql
SELECT COUNT(*) AS `num`, `trail` FROM (
SELECT `device`, `trail` FROM `ts_devices_count`
WHERE (`created_at` >= '2017-02-07') AND (`created_at` <= '2017-02-14')
GROUP BY `device`
) `temp` GROUP BY `trail`
在子查询中的表存在索引,子查询返回的结果表 temp 大概 11 万条数据,
整个 sql 执行下来大概需要 10 秒钟左右执行完,
如果单独执行子查询中 sql
SELECT `device`, `trail` FROM `ts_devices_count`
WHERE (`created_at` >= '2017-02-07') AND (`created_at` <= '2017-02-14')
GROUP BY `device` limit 200 offset 27384;
耗时大概是 1.39 秒
有没有办法能够优化到 1 秒内???或者换别的某种数据库能加快????
1
ebony0319 2017-03-09 14:52:03 +08:00 1
'''
SELECT `device`, `trail` FROM `ts_devices_count` WHERE (`created_at` >= '2017-02-07') AND (`created_at` <= '2017-02-14') GROUP BY `device` ''' 先说这一段有什么意义哇,我刚试了一下跟 SELECT `device`, `trail` FROM `ts_devices_count` WHERE (`created_at` >= '2017-02-07') AND (`created_at` <= '2017-02-14') 执行结果一样的哇。 |
2
lianxiaoyi OP @ebony0319 排重 device,device 会存在重复情况
|
3
ebony0319 2017-03-09 15:27:56 +08:00 1
@lianxiaoyi 应该用 distinct 把。
|
4
liprais 2017-03-09 16:24:39 +08:00 1
为啥不每天计算下然后加起来呢?
|
6
zeraba 2017-03-09 19:08:45 +08:00 via Android 1
SELECT COUNT(*) AS `num`, `trail` FROM ts_devices_count ts inner join (
SELECT distinct `device` FROM `ts_devices_count` WHERE `created_at` between '2017-02-07' and '2017-02-14') ) `temp` on temp.device = ts.devive group by 2 试试? 大概是表自连接去重 或者使用 having count 也行 |
7
abc123ccc 2017-03-09 19:45:38 +08:00 1
explain SELECT `device`, `trail` FROM `ts_devices_count`
WHERE (`created_at` >= '2017-02-07') AND (`created_at` <= '2017-02-14') GROUP BY `device` limit 200 offset 27384 ; explain SELECT distinct `device`, `trail` FROM `ts_devices_count` WHERE (`created_at` between '2017-02-07' AND '2017-02-14') limit 200 offset 27384 ; 看看走没走索引 |