下面的sql语句是查询在线用户在一天之内所用到的应用流量总和
1.explain select * from(select a.id,sum(a.in_bytes) as in_bytes,sum(a.out_bytes) as out_bytes,sum(a.in_bytes + a.out_bytes) as total_bytes from t_connect as a where a.record_time between '2011-06-30 00:00:00'and '2011-06-30 14:04:55' group by a.app_id) as c order by total_bytes desc
对应的结果如下:
+----+-------------+------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 101 | Using filesort |
| 2 | DERIVED | a | ALL | record_time | NULL | NULL | NULL | 10989554 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+----------+----------------------------------------------+
2 rows in set (16.59 sec)2.explain select * from(select a.id,sum(a.in_bytes) as in_bytes,sum(a.out_bytes) as out_bytes,sum(a.in_bytes + a.out_bytes) as total_bytes from t_connect as a where a.record_time between '2011-06-30 00:00:00'and '2011-06-30 11:04:55' group by a.app_id) as c order by total_bytes desc
对应的结果如下:
+----+-------------+------------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 93 | Using filesort |
| 2 | DERIVED | a | range | record_time | record_time | 9 | NULL | 1882981 | Using where; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
2 rows in set (2.08 sec)
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| app_id | int(10) unsigned | YES | MUL | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| src_addr | int(10) unsigned | YES | | 0 | |
| dst_addr | int(10) unsigned | YES | | 0 | |
| dst_port | smallint(5) unsigned | YES | | 0 | |
| protocol | char(32) | YES | | NULL | |
| in_bytes | int(10) unsigned | YES | | 0 | |
| out_bytes | int(10) unsigned | YES | | 0 | |
| record_time | datetime | YES | MUL | NULL | |
| end_time | datetime | YES | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
t_connect表中对app_id、user_id、record_time3个字段建立了索引
但是第一条sql语句和第二天sql语句的区别就是时间改了下,第二天sql语句时间改短了,所以数据量少一些,而第一条sql语句时间间隔长些,数据量稍微大些。为什么会出现第一条sql不会运用到索引record_time,而第二条sql语句会用到索引record_time呢?而且第二条sql运行的速度明显比第一条快(快了14s)。这就导致了我页面中查询数据时,大数据量的情况下(>1000w)有时候数据10几秒就能加载出来,有时候却要将近1分钟。 求高手帮忙解决!小弟不胜感激!
1.explain select * from(select a.id,sum(a.in_bytes) as in_bytes,sum(a.out_bytes) as out_bytes,sum(a.in_bytes + a.out_bytes) as total_bytes from t_connect as a where a.record_time between '2011-06-30 00:00:00'and '2011-06-30 14:04:55' group by a.app_id) as c order by total_bytes desc
对应的结果如下:
+----+-------------+------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 101 | Using filesort |
| 2 | DERIVED | a | ALL | record_time | NULL | NULL | NULL | 10989554 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+----------+----------------------------------------------+
2 rows in set (16.59 sec)2.explain select * from(select a.id,sum(a.in_bytes) as in_bytes,sum(a.out_bytes) as out_bytes,sum(a.in_bytes + a.out_bytes) as total_bytes from t_connect as a where a.record_time between '2011-06-30 00:00:00'and '2011-06-30 11:04:55' group by a.app_id) as c order by total_bytes desc
对应的结果如下:
+----+-------------+------------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 93 | Using filesort |
| 2 | DERIVED | a | range | record_time | record_time | 9 | NULL | 1882981 | Using where; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+-------------+---------+------+---------+----------------------------------------------+
2 rows in set (2.08 sec)
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| app_id | int(10) unsigned | YES | MUL | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| src_addr | int(10) unsigned | YES | | 0 | |
| dst_addr | int(10) unsigned | YES | | 0 | |
| dst_port | smallint(5) unsigned | YES | | 0 | |
| protocol | char(32) | YES | | NULL | |
| in_bytes | int(10) unsigned | YES | | 0 | |
| out_bytes | int(10) unsigned | YES | | 0 | |
| record_time | datetime | YES | MUL | NULL | |
| end_time | datetime | YES | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
t_connect表中对app_id、user_id、record_time3个字段建立了索引
但是第一条sql语句和第二天sql语句的区别就是时间改了下,第二天sql语句时间改短了,所以数据量少一些,而第一条sql语句时间间隔长些,数据量稍微大些。为什么会出现第一条sql不会运用到索引record_time,而第二条sql语句会用到索引record_time呢?而且第二条sql运行的速度明显比第一条快(快了14s)。这就导致了我页面中查询数据时,大数据量的情况下(>1000w)有时候数据10几秒就能加载出来,有时候却要将近1分钟。 求高手帮忙解决!小弟不胜感激!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货