你可以帮我优化这个查询 – 目前运行这个查询需要大约4分钟.它看起来像键:ip和timestamp是非唯一的,所以我应该添加一些索引吗?
mysql> EXPLAIN
SELECT max(url) as url,
max(title) as title,
keyword as keyword,
COALESCE(max(domain), 'example.com') as domain
FROM `test_url` as tu
JOIN `test_log` AS tl
ON tl.shorturl = tu.keyword
AND tl.click_id >
(SELECT option_value
from `test_options`
WHERE option_name = 'click_id' )
WHERE 1 = 1
GROUP BY keyword
HAVING count( keyword ) > 50
ORDER BY count( keyword ) DESC
LIMIT 10;
+----+-------------+--------------+--------+------------------+-------------+---------+-----------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+--------+------------------+-------------+---------+-----------------------+---------+----------------------------------------------+
| 1 | PRIMARY | tl | range | PRIMARY,shorturl | PRIMARY | 4 | NULL | 5748586 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | tu | eq_ref | PRIMARY | PRIMARY | 602 | test_urls.tl.shorturl | 1 | |
| 2 | SUBQUERY | test_options | ref | option_name | option_name | 194 | | 1 | Using where |
+----+-------------+--------------+--------+------------------+-------------+---------+-----------------------+---------+----------------------------------------------+
3 rows in set (0.00 sec)
–
mysql> SHOW CREATE TABLE test_url\G
*************************** 1. row ***************************
Table: test_url
Create Table: CREATE TABLE `test_url` (
`keyword` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`url` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`title` text,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ip` varchar(41) NOT NULL,
`clicks` int(10) unsigned NOT NULL,
`username` varchar(255) NOT NULL DEFAULT '',
`company_id` int(11) NOT NULL DEFAULT '0',
`domain` varchar(31) NOT NULL DEFAULT '',
PRIMARY KEY (`keyword`),
KEY `timestamp` (`timestamp`),
KEY `ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
–
mysql> SHOW INDEXES FROM test_url;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_url | 0 | PRIMARY | 1 | keyword | A | 176798 | NULL | NULL | | BTREE | | |
| test_url | 1 | timestamp | 1 | timestamp | A | 176798 | NULL | NULL | | BTREE | | |
| test_url | 1 | ip | 1 | ip | A | 11 | NULL | NULL | | BTREE | | |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.08 sec)
编辑:Tue Jun 17 15:17:36 BST 2014
mysql> SHOW CREATE TABLE test_log\G
*************************** 1. row ***************************
Table: test_log
Create Table: CREATE TABLE `test_log` (
`click_id` int(11) NOT NULL AUTO_INCREMENT,
`click_time` datetime NOT NULL,
`shorturl` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`referrer` varchar(200) NOT NULL,
`user_agent` varchar(255) NOT NULL,
`ip_address` varchar(41) NOT NULL,
`country_code` char(2) NOT NULL,
PRIMARY KEY (`click_id`),
KEY `shorturl` (`shorturl`)
) ENGINE=InnoDB AUTO_INCREMENT=51636348 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
–
mysql> SHOW CREATE TABLE test_options\G
*************************** 1. row ***************************
Table: test_options
Create Table: CREATE TABLE `test_options` (
`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`option_name` varchar(64) NOT NULL DEFAULT '',
`option_value` longtext NOT NULL,
PRIMARY KEY (`option_id`,`option_name`),
KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
编辑:Fri Jun 20 10:40:35 BST 2014
数据库大小为4975MB(3965MB数据大小,1010MB索引大小)
MySQL veriosn:5.5.35
操作系统:Ubuntu 12.04
主要问题是此查询使用在磁盘上创建的临时表导致服务器上的高负载(状态:复制到tmp表)