mysql group 优化

[lianxiaoyi]

表结构

表中大概有 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 秒内？？？或者换别的某种数据库能加快？？？？

[ebony0319]

'''
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')
执行结果一样的哇。

[lianxiaoyi]

@ebony0319 排重 device,device 会存在重复情况

[ebony0319]

@lianxiaoyi 应该用 distinct 把。

[liprais]

为啥不每天计算下然后加起来呢?

[liprais]

@liprais 如果是按天新增的话历史数据不需要每次查询的时候都算啊，算一次存起来就好了

[zeraba]

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 也行

[abc123ccc]

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 ；

看看走没走索引