优化mysql 聚合_mysql – 聚合查询优化

你可以帮我优化这个查询 – 目前运行这个查询需要大约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表)