表结构:
CREATE TABLE tab_log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
time TIME NOT NULL,
ip BIGINT UNSIGNED NOT NULL,
domain VARCHAR(1000) NOT NULL,
htype TINYINT UNSIGNED NOT NULL,
tld TINYINT UNSIGNED NOT NULL,
qtype TINYINT UNSIGNED NOT NULL,
flag TINYINT UNSIGNED NOT NULL,
PRIMARY KEY(id),
);
CREATE INDEX idx_dmn ON tab_log (domain);
CREATE INDEX idx_ip ON tab_log (ip);
CREATE INDEX idx_time ON tab_log (time);
由于htype、tld、qtype、flag 4 列的基数很小,所以没有建立索引。
SQL语句如下:
select domain,count(*) as COUNT from tab_log where tld = 0 group by domain order by COUNT desc;目的是查询每个域名的被查询次数,并按照次数降序排列。可是很慢,很久都不出结果,表中的记录不是很多,有17680001条。
请高人帮忙,怎样才能优化这条SQL语句,提高查询性能?期待
CREATE TABLE tab_log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
time TIME NOT NULL,
ip BIGINT UNSIGNED NOT NULL,
domain VARCHAR(1000) NOT NULL,
htype TINYINT UNSIGNED NOT NULL,
tld TINYINT UNSIGNED NOT NULL,
qtype TINYINT UNSIGNED NOT NULL,
flag TINYINT UNSIGNED NOT NULL,
PRIMARY KEY(id),
);
CREATE INDEX idx_dmn ON tab_log (domain);
CREATE INDEX idx_ip ON tab_log (ip);
CREATE INDEX idx_time ON tab_log (time);
由于htype、tld、qtype、flag 4 列的基数很小,所以没有建立索引。
SQL语句如下:
select domain,count(*) as COUNT from tab_log where tld = 0 group by domain order by COUNT desc;目的是查询每个域名的被查询次数,并按照次数降序排列。可是很慢,很久都不出结果,表中的记录不是很多,有17680001条。
请高人帮忙,怎样才能优化这条SQL语句,提高查询性能?期待
运行速度如何?
有排序的语句运行是比较慢
select domain,count(domain) as COUNT from tab_log where tld = 0 group by domain
explain select domain,count(*) as COUNT from tab_log where tld = 0 group by domain order by COUNT desc;看一下情况。
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+----------+---------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | tbl_20090703 | ref | idx_mtld | idx_mtld | 1 | const | 10105316 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+----------+---------+-------+----------+----------------------------------------------+
这是explain后的结果,
我在tld列还是加上了索引
CREATE INDEX idx_dmn_tld ON tab_log (domain,tld);
select domain,count(*) as COUNT
from tab_log FORCE INDEX FOR GROUP BY(idx_dmn)
where tld = 0
group by domain ;
ORDER BY 没办法,这是个统计结果,没办法利用任何索引。
+----+-------------+--------------+------+---------------+----------+---------+-------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+----------+---------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | tbl_20090703 | ALL | NULL | NULL | NULL| NULL| 17680001| Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+----------+---------+-------+----------+----------------------------------------------+
from tab_log FORCE INDEX FOR GROUP BY(idx_dmn)
where tld = 0
group by domain ;
就是这一句。。
-> from tab_log FORCE INDEX FOR GROUP BY(idx_dmn)
-> where tld = 0
-> group by domain ;
+----+-------------+---------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | tab_log | ref | idx_mtld | idx_mtld | 1 | const | 1 | Using where; Using temporary; Using filesort |
+----+-------------+---------+------+---------------+----------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)
(domain) 的索引只能加快一下group by, 或者 (tld)的索引只能先做个筛选,之后的合计还是会花时间。