简单的mysql查询优化大表

问题描述：

我有一个简单的查询，需要2.5秒来评估。我想优化它，但我不知道如何做到这一点。任何人都可以建议如何优化？逻辑是下一个：计算指定广告系列的引荐来源分组的所有点击次数。有“解释”下面的mysql查询。表格点击和用户有很多行。简单的mysql查询优化大表

mysql> explain select count(*) as amount,ac.referrer from clicks ac 
    ->   inner join users a on a.id = ac.user_id 
    ->   where a.campaign_id = 26 group by ac.referrer 
    ->   order by amount desc; 

+----+-------------+-------+--------+-------------------+---------+---------+------------------------+---------+---------------------------------+ 
| id | select_type | table | type | possible_keys  | key  | key_len | ref     | rows | Extra       | 
+----+-------------+-------+--------+-------------------+---------+---------+------------------------+---------+---------------------------------+ 
| 1 | SIMPLE  | ac | ALL | ac_adv   | NULL | NULL | NULL     | 1607031 | Using temporary; Using filesort | 
| 1 | SIMPLE  | a  | eq_ref | PRIMARY,advoc_cam | PRIMARY | 4  | mydb.ac.user_id  |  1 | Using where      | 
+----+-------------+-------+--------+-------------------+---------+---------+------------------------+---------+---------------------------------+ 
2 rows in set (0.00 sec)

编辑

对不起，未提供shemas，但现在没有这个必要。现在我已经完全覆盖了索引列的建议在接受的答案。

这是正确的解决方案添加索引（c.user_id，c.referrer）。其他指标已经存在并且没有意义。但现在查询完成时间为0.2秒，而不是2.5-3秒。谢谢！

请向我们展示表格定义！ – undone 2014-10-02 09:04:56

请给我们表结构。试试这个：ALTER TABLE'clicks'ADD INDEX'referrer'（'referrer'）; – faster2b 2014-10-02 09:32:15

我真的不明白。据推测，你已经有一个复合主键（引用者，user_id），并且users.id必须已经是一个PK。所以唯一剩下的就是给campaign_id添加一个索引 - 但我怀疑这会产生多大的影响。 :-( – Strawberry 2014-10-02 09:54:44

答

嗯，我可能会重写查询为：

SELECT c.referrer, 
     count(*) amount, 
    FROM clicks c 
    JOIN users u 
     ON u.campaign_id = 26 
    AND u.id = c.user_id 
GROUP BY c.referrer 
ORDER BY amount DESC

只是为了能够清楚是怎么回事。

您的基本查询大概和它一样好，所以您需要索引来提高性能。

对于JOIN给用户，u.id上的主键应该不错，但是您可能会从(u.campaign_id, u.id)上的组合获得更好的性能。

对于主表的点击，您应该试试c.user_id和c.referrer上的索引，也可能是两个可能的复合物(c.user_id, c.referrer)和(c.referrer, c.user_id)。使用EXPLAIN计划来确定哪些是possible_keys，并删除那些没有帮助的。

如果查询不使用它们，但它们显示在possible_keys中（您只能为每个表使用一个或连接 - 计划的一行），您可能不得不暗示某些索引。

对于每个表上的组合，其中两个索引覆盖全部四个参考列，例如， (u.campaign_id, u.id)和(c.user_id, c.referrer)查询应该由索引覆盖，而不需要访问应该进一步提高性能的表行。另外请记住，任何新的索引都会减慢DML操作（INSERT，UPDATE，DELETE）的位置，因为它们包含由索引引用的列，因此如果您的写入速率很高，最好对此命中改为查询。

答

mysql> explain select count(*) as amount,ac.referrer from clicks ac 
->   inner join users a on a.id = ac.user_id AND a.campaign_id = 26 
->   group by ac.referrer 
->   order by amount desc;

在您的查询中更改WH和。可能是它会更快

这将会有没有影响，优化器会为你排序。 – Arth 2014-10-02 09:43:14

答

尝试

有场(CAMPAIGN_ID, user_id)

在用户表中的键 - >这将遍历用户表，然后添加点击每个用户找到。

简单的mysql查询优化大表

相关推荐