优化大型MySQL查询
我试图优化一个查询,花费太长时间才能运行它。它似乎陷入了很多发送数据中,需要大约半个小时才能运行。优化大型MySQL查询
$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)
下面介绍一下参考表如下所示:
+-------------+------------------+------+-----+-------------------+----------------+ | 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 | +----+-------------+-------+-------+------------------------------------------------------------+---------------+---------+------+---------+-------------+
有表中约有150M行。
有什么我可以做的,以优化有问题的查询?我是否需要添加索引或其他内容?我怎样才能让事情变得更好?
你可以尝试以下指标来调整这种说法
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
)的那个 - 假设子句Date
仍然非常有选择性。不平等操作之后的所有内容只是索引中的一个过滤器 - 如果需要,您可以在此处添加IN
列表。
广告
万一你想了解更多有关数据库索引:看一看我的free eBook on database indexing。
调整查询的范围很小,但通过调整数据库模式可以大大提高速度 - 诀窍在于尽可能确定潜在的索引。
例如
和日期> = DATE_SUB(NOW(),INTERVAL 90 DAY)
表明,在 '日期' 的索引可以帮助 - 但只有当你的数据以及分布在至少4年。
在实践中,特别是当您只需要针对特定查询时,复合索引是一个好主意 - 但索引的最佳选择不仅取决于数据的大小和形状,还取决于您运行的其他查询你的数据库。在查询
展望:
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)
你可以简单地在(类型,emailDomain,ListId,CAMPAIGNID和日期)添加索引,但是我怀疑CAMPAIGNID和日期有最大的基数,因此应出现在索引的前面 - 索引应按输入数据集(表格)中的基数与查询的输出的比率进行排序。例如如果您经常使用以下方式运行查询:
AND Date >= DATE_SUB(NOW(), INTERVAL 90000 DAY)
然后,您不会从在索引前面添加日期中获得太多好处。同样,Type看起来好像有一组非常有限的值,并且应该比CampaignId稍后出现在索引中(假设您只是随时查看相对少量的CampaignIds)。
为了得到基数的估计,考虑:
SELECT COUNT(records_of_type)/SUM(records_of_type)
FROM (SELECT afield, COUNT(*) AS records_of_type
FROM atable)
(高值是更具选择性,通常应该出现在索引的前面)。
但请记住,您偶尔会看到跨列的函数依赖关系。
按基数对索引字段顺序排序不会减少DBMS为满足查询而必须访问的索引节点的数量,但应导致所需的磁盘I/O操作数量减少。
然而,在担心订单之前,确定哪些字段出现在索引中更重要。
可以尝试几种不同的方法。
有一两件事你可以尝试:
$date = mysql_query("SELECT DATE_SUB(NOW(), INTERVAL 90 DAY) AS date");
SELECT * FROM (
SELECT Email, Type, CampaignID
FROM Refer
WHERE (Type = 'V' OR Type = 'C')
AND (EmailDomain = 'yahoo.com')
AND (ListID = 1)
)
WHERE Date >= $date
AND CampaignID IN ($campaignIDs)
指数在此查询(类型EmailDomain ListID),你应该会看到一个显著的性能增益。你也可以玩索引的排序(但要确保查询匹配)。 这样做的目标是取得查询的快速部分,并对较大数量的记录运行该查询,然后将查询的较慢部分与较小的一部分进行比较。
您可能需要创建一个临时表才能让sql执行此操作;然而,我不必为我的测试集。还要注意的是,我把这个大的慢查询函数调出来,并把它变成一个常量。