千万级数据avg()查询优化,求助

Question

表结构是
CREATE TABLE `NewTable` (
`id`  bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`channel_id`  int(11) UNSIGNED NOT NULL ,
`get_at`  datetime NOT NULL ,
`value`  double NOT NULL ,
PRIMARY KEY (`id`),
INDEX `get_at` USING BTREE (`get_at`) ,
INDEX `IX_avg` USING BTREE (`channel_id`, `get_at`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=82861956
ROW_FORMAT=COMPACT;
八千万条数据
其中，经常用到select avg(value) from NewTable where channel_id in(aa,bb,cc) and get_at between 'YYYY-MM-DD H:mm:ss' and 'YYYY-MM-DD H:mm:ss'这种查询，现有如上两个索引，查询效率很慢，求教各位大神如何优化？

Answer 1

你的语句比较简单，没有使用 group by所以 avg 不是什么问题。create index xxxxx on NewTable(channel_id,get_at);

Answer 2

已经有这个索引了，我现在把get_at那个索引删掉试试，除此之外还有没有更进一步的办法呢？
现在执行一次查询大概要1分钟多，这个速度有点不能接收，谢谢版主~

Answer 3

贴出 show index from NewTable;
explain select avg(value) from NewTable where channel_id in(aa,bb,cc) and get_at between 'YYYY-MM-DD H:mm:ss' and 'YYYY-MM-DD H:mm:ss'以供分析。

Answer 4

强制使用`IX_avg`　试试
select avg(value) from NewTable FORCE INDEX(`IX_avg`) where channel_id in(aa,bb,cc) and get_at between 'YYYY-MM-DD H:mm:ss' and 'YYYY-MM-DD H:mm:ss'

Answer 5

mysql> explain select avg(value) from data where channel_id in (58,74,90,106) and get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25';
+----+-------------+-------+-------+---------------+--------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+----------+-------------+
|  1 | SIMPLE      | data  | range | IX_avg        | IX_avg | 12      | NULL | 10185390 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+----------+-------------+
1 row in set

Answer 6

mysql> show index from data;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| data  |          0 | PRIMARY  |            1 | id          | A         |    83246361 | NULL     | NULL   |      | BTREE      |         |
| data  |          1 | IX_avg   |            1 | channel_id  | A         |          18 | NULL     | NULL   |      | BTREE      |         |
| data  |          1 | IX_avg   |            2 | get_at      | A         |    83246361 | NULL     | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set

Answer 7

EXPLAIN SELECT * FROM NewTable 
WHERE 
channel_id IN(58,74,90,106) AND get_at BETWEEN  '2012-8-26 17:25:07' AND '2013-5-5 15:09:25';贴结果

Answer 8

SELECT * FROM NewTable 
 WHERE 
 channel_id IN(58,74,90,106) AND get_at BETWEEN  '2012-8-26 17:25:07' AND '2013-5-5 15:09:25';
执行速度如何？

Answer 9

select sum(s)/sum(c) 
from (
select sum(value) as s,count(*) as c from data where channel_id in =58 and get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25'
union all
select sum(value) as s,count(*) as c from data where channel_id in =74 and get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25'
union all
select sum(value) as s,count(*) as c from data where channel_id in =90 and get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25'
union all
select sum(value) as s,count(*) as c from data where channel_id in =106 and get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25'
) t

Answer 10

结果如下：
id     select_type  table   type    possible_keys  key      key_len  ref    rows          Extra
1 SIMPLE data    range IX_avg     IX_avg     12    null 10284684   Using where另外直接执行SELECT * FROM NewTable 
 WHERE 
 channel_id IN(58,74,90,106) AND get_at BETWEEN  '2012-8-26 17:25:07' AND '2013-5-5 15:09:25';速度也很慢，四分钟了还没执行完: (

Answer 11

估计是 get_at BETWEEN  '2012-8-26 17:25:07' AND '2013-5-5 15:09:25';这句慢
建议新增1个字段INT型，建立索引，保存UNIX_TIMESTAMP(get_at)
新字段 BETWEEN  FROM_UNIXTIME('2012-8-26 17:25:07') AND 
FROM_UNIXTIME('2013-5-5 15:09:25')

Answer 12

explain select sum(value) as s,count(*) as c from data where channel_id in =58 and get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25'不所你表中所有记录都符合 get_at between '2012-8-26 17:25:07' and '2013-5-5 15:09:25' 这个吧。

Answer 13

alter table NewTable add index (get_at,channelid,value)

Answer 14

那当然不是...把EXPLAIN的结果贴出来啊。否则别人只能猜啊。

Answer 15

那当然不是...把EXPLAIN的结果贴出来啊。否则别人只能猜啊。悲剧，我在删除掉一个索引，然后重新建了一个新的索引之后，就访问不到原来的数据库了，现在只能访问那个服务器上的information_schema数据库，如果想访问原来的数据库的话会出现"access denied for user 'username'@'%' to database 'databasename'"的提示，而我show grants 的话却是"grant usage on *.* to 'username'@'%' identified by password 'xxxxxxxxx'"的提示，这是为什么，该怎么办啊，囧囧囧

Answer 16

用MYSQLDUMP能否备份？

Answer 17

看上去是登录账号不正确。'username'@'%'