mysql - 优化大型 MySQL 查询

Question

我正在尝试优化一个查询，该查询花费的时间太长而无法按原样运行。好像经常卡在Sending Data，跑了半个小时左右。

$campaignIDs = "31,36,37,40,41,42,43,50,51,62,64,65,66,67,68,69,84,338,339,355,431,505,530,549,563,694,752,754,755,760,769,772,777,798,799,800,806,816,821,855,856,945,989,1007,1030,1032,1047,1052,1054,1066,1182,1268,1281,1298,1301,1317,1348,1447,1461,1471,1589,1602,1604,1615,1622,1650,1652,1709";SELECT Email, Type, CampaignID FROM Refer WHERE (Type = 'V' OR Type = 'C')   AND (EmailDomain = 'yahoo.com')   AND (ListID = 1)   AND CampaignID IN ($campaignIDs)   AND Date >= DATE_SUB(NOW(), INTERVAL 90 DAY) 

Refer 表如下所示:

+-------------+------------------+------+-----+-------------------+----------------+| Field       | Type             | Null | Key | Default           | Extra          |+-------------+------------------+------+-----+-------------------+----------------+| ID          | int(10) unsigned | NO   | PRI | NULL              | auto_increment || CampaignID  | int(10) unsigned | NO   | MUL | NULL              |                || Type        | char(1)          | NO   | MUL | NULL              |                || Date        | timestamp        | NO   |     | CURRENT_TIMESTAMP |                || IP          | varchar(16)      | NO   |     | NULL              |                || Useragent   | varchar(200)     | YES  |     | NULL              |                || Referrer    | varchar(200)     | YES  |     | NULL              |                || Email       | varchar(200)     | NO   | MUL | NULL              |                || EmailDomain | varchar(200)     | YES  | MUL | NULL              |                || FolderName  | varchar(200)     | NO   |     | NULL              |                || ListID      | int(10) unsigned | NO   | MUL | 1                 |                |+-------------+------------------+------+-----+-------------------+----------------+

索引如下:

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| refer |          0 | PRIMARY        |            1 | ID          | A         |   148581841 |     NULL | NULL   |      | BTREE      |         || refer |          1 | id_email       |            1 | Email       | A         |    18572730 |     NULL | NULL   |      | BTREE      |         || refer |          1 | id_type        |            1 | Type        | A         |          19 |     NULL | NULL   |      | BTREE      |         || refer |          1 | id_emaildomain |            1 | EmailDomain | A         |          19 |     NULL | NULL   | YES  | BTREE      |         || refer |          1 | id_campaignid  |            1 | CampaignID  | A         |          19 |     NULL | NULL   |      | BTREE      |         || refer |          1 | id_listid      |            1 | ListID      | A         |          19 |     NULL | NULL   |      | BTREE      |         || refer |          1 | id_emailtype   |            1 | Email       | A         |    24763640 |     NULL | NULL   |      | BTREE      |         || refer |          1 | id_emailtype   |            2 | Type        | A         |    37145460 |     NULL | NULL   |      | BTREE      |         || refer |          1 | idx_cidtype    |            1 | CampaignID  | A         |          19 |     NULL | NULL   |      | BTREE      |         || refer |          1 | idx_cidtype    |            2 | Type        | A         |          19 |     NULL | NULL   |      | BTREE      |         |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

这是 EXPLAIN SELECT 的输出:

+----+-------------+-------+-------+------------------------------------------------------------+---------------+---------+------+---------+-------------+| id | select_type | table | type  | possible_keys                                              | key           | key_len | ref  | rows    | Extra       |+----+-------------+-------+-------+------------------------------------------------------------+---------------+---------+------+---------+-------------+|  1 | SIMPLE      | Refer | range | id_type,id_emaildomain,id_campaignid,id_listid,idx_cidtype | id_campaignid | 4       | NULL | 3605121 | Using where |+----+-------------+-------+-------+------------------------------------------------------------+---------------+---------+------+---------+-------------+

表中大约有 1.5 亿行。

我可以做些什么来优化相关查询吗？我需要添加索引还是什么？我怎样才能让事情变得更好？

Answer 1

您可以尝试使用以下索引来调整该语句

ALTER TABLE refer
  ADD INDEX so_suggested (EmailDomain, ListID, Date);

这只是我的第一个想法。

您还可以添加 CampaignID 和 Type 以提高效率 - 如果它们是选择性的。如果同时添加两者，您甚至可以尝试添加 Email 使其成为 covering index .

但是，该表上的索引数量相当多(八个)。其中两个是多余的(id_email、id_campaignid)，因为还有其他以同一列开头的(id_emailtype、idx_cidtype)。

请注意(原则上)一个表访问只使用一个索引。您的查询只有一个表访问权限(没有子查询、联接、UNION 等等)因此它只能使用一个索引。因此，您需要一个 索引来尽可能支持您的where 子句。

另请注意，该索引中列的顺序非常重要。我首先添加了完全匹配的那些(EmailDomain、ListID)，然后是使用不等式运算符的那些(Date) --假设子句 one Date 仍然相当有选择性。 in-equality 操作之后的所有内容都只是索引中的过滤器——如果需要，您可以在此处添加 IN 列表。

广告

以防万一您想了解有关数据库索引的更多信息:看看我的 free eBook on database indexing .