mysql group 优化

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

V2EX 提问指南

这是一个创建于 2807 天前的主题，其中的信息可能已经有所发展或是发生改变。

表结构

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

7 条回复 • 2017-03-09 19:45:38 +08:00

ebony0319

2017-03-09 14:52:03 +08:00

'''
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

2017-03-09 14:54:39 +08:00

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

ebony0319

2017-03-09 15:27:56 +08:00

@lianxiaoyi 应该用 distinct 把。

liprais

2017-03-09 16:24:39 +08:00

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

liprais

2017-03-09 16:25:29 +08:00

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

zeraba

2017-03-09 19:08:45 +08:00 via Android

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

2017-03-09 19:45:38 +08:00

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 ；

看看走没走索引