mysql 百万级联表查询的优化

Question

两表进行关联查询 其中host_traffic_time表数据在一百三十万左右 CREATE TABLE `host` (
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `groupid` int(10) NOT NULL DEFAULT '1',
 `host_template_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `description` varchar(150) NOT NULL DEFAULT '',
 `hostname` varchar(250) DEFAULT NULL,
 `imsi` varchar(128) NOT NULL,
 `notes` text,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3259 DEFAULT CHARSET=utf8CREATE TABLE `host_traffic_time` (
 `host_id` mediumint(8) NOT NULL,
 `rrd_name` varchar(19) NOT NULL,
 `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `num` bigint(32) NOT NULL,
 KEY `host_id` (`host_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
sql语句为SELECT a.host_id, SUM( a.num ) num, b.hostname, b.description
FROM host_traffic_time a
LEFT JOIN host b ON b.id = a.host_id
WHERE a.start_time >= '2012-12-17 00:00:00'
AND a.start_time <= '2012-12-17 23:59:59'
GROUP BY a.host_id
LIMIT 0 , 30就是查出当天的数据 start_time字段类型无法改变（用时间戳会更好），该如何优化，出手吧 

Answer 1

host_traffic_time:host_id start_time　复合索引

Answer 2

explain select ...执行计划贴出。

Answer 3

EXPLAIN SELECT a.host_id, SUM( a.num ) num, b.hostname, b.description
FROM host_traffic_time a
LEFT JOIN host b ON b.id = a.host_id
WHERE a.start_time >= '2012-12-17 00:00:00'
AND a.start_time <= '2012-12-17 23:59:59'
GROUP BY a.host_id
LIMIT 0 , 30
mysql> EXPLAIN SELECT a.host_id, SUM( a.num ) num, b.hostname, b.description FROM host_traffic_time a LEFT JOIN host b ON b.id = a.host_id WHERE a.start_time >= '2012-12-17 00:00:00' AND a.start_time <= '2012-12-17 23:59:59' GROUP BY a.host_id LIMIT 0 , 30;
+----+-------------+-------+--------+---------------+---------+---------+---------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | a     | index  | NULL          | host_id | 3       | NULL          | 18060 | Using where |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 3       | nms.a.host_id |     1 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+-------+-------------+
2 rows in set (0.00 sec)

Answer 4

ALTER TABLE `host_traffic_time`
ADD INDEX `index` ( `host_id` , `start_time` ) 索引已加上 貌似没有什么变化啊mysql> explain SELECT a.host_id, sum( a.num ) AS num, b.hostname, b.description, b.disabled FROM host_traffic_time a, host b WHERE a.host_id = b.id AND start_time >= '2012-12-17 00:00:00' AND start_time <= '2012-12-17 23:59:59' GROUP BY a.host_id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------------------------------------+
|  1 | SIMPLE      | a     | ALL    | host_id,index | NULL    | NULL    | NULL          | 703104 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 3       | nms.a.host_id |      1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------------------------------------+
2 rows in set (0.00 sec)

Answer 5

host_traffic_time表上对start_time创建一个索引，再试试

Answer 6

去掉KEY `host_id` (`host_id`)

Answer 7

+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| host_traffic_time |          1 | host_id  |            1 | host_id     | A         |        1603 |     NULL | NULL   |      | BTREE      |         |
| host_traffic_time |          1 | index    |            1 | host_id     | A         |        1603 |     NULL | NULL   |      | BTREE      |         |
| host_traffic_time |          1 | index    |            2 | start_time  | A         |      708759 |     NULL | NULL   |      | BTREE      |         |
| host_traffic_time |          1 | time     |            1 | start_time  | A         |       15079 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+依旧是5秒多......

Answer 8

哥哥 你狠啊....显示行 0 - 29 (1,600 总计, 查询花费 0.0003 秒)
SELECT a.host_id, sum( a.num ) AS num, b.hostname, b.description, b.disabled
FROM host_traffic_time a, host b
WHERE a.host_id = b.id
AND start_time >= '2012-12-17 00:00:00'
AND start_time <= '2012-12-17 23:59:59'
GROUP BY a.host_id

Answer 9

呵呵，简单地讲，复合索引中2个字段在WHERE中均可使用
单独host_id索引，start_time >= '2012-12-17 00:00:00'
AND start_time <= '2012-12-17 23:59:59'
这个条件无法用到索引

Answer 10

楼主能再帮做一实验吗？
除了index: KEY `host_id` (`host_id`)外，再单独为start_time建一索引，不用建复合索引，看看是时间是多少

Answer 11

mysql> show index from host_traffic_time;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| host_traffic_time |          1 | time     |            1 | start_time  | A         |       15196 |     NULL | NULL   |      | BTREE      |         |
| host_traffic_time |          1 | host_id  |            1 | host_id     | A         |        1601 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)显示行 0 - 29 (1,600 总计, 查询花费 0.0003 秒)
SELECT a.host_id, sum( a.num ) AS num, b.hostname, b.description, b.disabled
FROM host_traffic_time a, host b
WHERE a.host_id = b.id
AND start_time >= '2012-12-17 00:00:00'
AND start_time <= '2012-12-17 23:59:59'
GROUP BY a.host_id好吧 你俩都狠 能不能解释一下呢

Answer 12

帮我们再explain一下，看看它的查询计划

Answer 13

mysql> explain SELECT a.host_id, sum( a.num ) AS num, b.hostname, b.description, b.disabled FROM host_traffic_time a, host b WHERE a.host_id = b.id AND start_time >= '2012-12-17 00:00:00' AND start_time <= '2012-12-17 23:59:59' GROUP BY a.host_id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------------------------------------+
|  1 | SIMPLE      | a     | ALL    | time,host_id  | NULL    | NULL    | NULL          | 716952 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 3       | nms.a.host_id |      1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------------------------------------+

Answer 14

还有一个问题是limit变得很慢显示行 270 - 299 (1,600 总计, 查询花费 5.8722 秒)
SELECT a.host_id, sum( a.num ) AS num, b.hostname, b.description, b.disabled
FROM host_traffic_time a, host b
WHERE a.host_id = b.id
AND start_time >= '2012-12-17 00:00:00'
AND start_time <= '2012-12-17 23:59:59'
GROUP BY a.host_id
LIMIT 270 , 30

Answer 15

只用复合索引(start_time, hostid), 对上述limit查询有无改善？

Answer 16

对只用符合索引的话对limit有改善显示行 390 - 419 (1,600 总计, 查询花费 0.4777 秒)
SELECT a.host_id, sum( a.num ) AS num, b.hostname, b.description, b.disabled
FROM host_traffic_time a, host b
WHERE a.host_id = b.id
AND start_time >= '2012-12-17 00:00:00'
AND start_time <= '2012-12-17 23:59:59'
GROUP BY a.host_id
LIMIT 390 , 30

Answer 17

嗯，或许下边这篇对这个主题有帮助：http://grb12508.blog.163.com/blog/static/273784582009102448061/

Answer 18

sorry 结贴给分给错了 下次补上 哈哈