mysql - 提高 mysql 对 google cloud sql 上的表的查询性能

Question

我有一个包含“137678997”条记录但没有唯一主键的表。这是我的表格说明。

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| domain        | varchar(50)   | YES  | MUL | NULL    |       |
| guid          | varchar(100)  | YES  |     | NULL    |       |
| sid           | varchar(100)  | YES  | MUL | NULL    |       |
| url           | varchar(2500) | YES  |     | NULL    |       |
| ip            | varchar(20)   | YES  |     | NULL    |       |
| is_new        | varchar(20)   | YES  |     | NULL    |       |
| ref           | varchar(50)   | YES  |     | NULL    |       |
| user_agent    | varchar(255)  | YES  |     | NULL    |       |
| stats_time    | datetime      | YES  |     | NULL    |       |
| country       | varchar(50)   | YES  |     | NULL    |       |
| region        | varchar(50)   | YES  |     | NULL    |       |
| city          | varchar(50)   | YES  |     | NULL    |       |
| city_lat_long | varchar(50)   | YES  |     | NULL    |       |
| email         | varchar(100)  | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

域、电子邮件、stats_time 的索引

我的sql查询是

SELECT p1.guid, p1.email,MAX(mx_time) as latest_time, 
       p1.city_lat_long, p1.user_agent, 
       p1.city, p1.region, p1.country 
FROM(
  SELECT guid, email,
         MAX(stats_time)as mx_time, 
         city_lat_long, user_agent, 
         city, region, country 
    FROM page_views 
    WHERE domain ='our' 
      AND DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) 
        BETWEEN DATE('2013-06-21 00:00:00') 
        AND DATE('2013-08-21 00:00:00') 
    GROUP BY guid) p1 
WHERE p1.email !="" 
GROUP BY email 

UNION ALL 

SELECT p2.guid, p2.email,
       mx_time, p2.city_lat_long, 
       p2.user_agent, p2.city, 
       p2.region, p2.country 
FROM(
  SELECT guid, email,
         MAX(stats_time) as mx_time, 
         city_lat_long, user_agent, 
         city, region, country 
  FROM page_views 
  WHERE domain ='our' 
    AND DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) 
      BETWEEN DATE('2013-06-21 00:00:00') 
      AND DATE('2013-08-21 00:00:00') 
  GROUP BY guid) p2 
WHERE p2.email="";

抱歉这个大查询，目的是获取域的最新访问者(Max(stats_time))。在这里，我使用了 UNION ALL，因为我也必须获取所有匿名用户，而我无法通过电子邮件 ID 对他们进行分组。

我还使用没有 UNION ALL 的简单选择列进行了测试，这花费了超过 15 分钟。如何提高表上的查询性能？它实际上是一个具有 D2 层(1 GB RAM)的谷歌云 sql。非常感谢您的建议，我是 Mysql 的新手。

编辑::

SELECT p2.guid, p2.email,mx_time, p2.city_lat_long, p2.user_agent, p2.city, p2.region, p2.country
FROM
(SELECT guid, email,MAX(stats_time)as mx_time, city_lat_long, user_agent, city, region, country FROM page_views WHERE domain ='our' AND DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) BETWEEN DATE('2013-06-21 00:00:00')  AND DATE('2013-08-21 00:00:00') GROUP BY guid) p2 where p2.email=""

此查询为我提供了没有电子邮件的访问者的行。

和

SELECT p1.guid, p1.email,MAX(mx_time) as latest_time, p1.city_lat_long, p1.user_agent, p1.city, p1.region, p1.country
FROM
(SELECT guid, email,MAX(stats_time)as mx_time, city_lat_long, user_agent, city, region, country FROM page_views WHERE domain ='our' AND DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) BETWEEN DATE('2013-06-21 00:00:00')  AND DATE('2013-08-21 00:00:00') GROUP BY guid) p1 where p1.email !="" GROUP BY email

这一个给我非空的行，并按电子邮件分组。这两个是 UNION ALL，因为我需要特定日期范围内的所有匿名访问者 + 已知访问者(电子邮件!=“”)。

谢谢你:)

Answer 1

很难提高查询性能，因为您在 Where 子句 中有很多函数，但对您的性能不利，子查询中的最大函数和分组依据 我认为是也很糟糕， Union All 给你重复。实际上，我可以建议您避免在 Where 子句中进行日期时间转换 Here有用的链接如何避免它。

我想补充一些建议，在没有 Max 和 Group By 的情况下，如何获得域的最新访问者(Max(stats_time)) - 最好使用 Order By desk 和 Limit .

如果我误解了你，你可以添加你的逻辑和预期结果，我们会尝试更改你的查询。

谢谢。

更新

这是你的第一个查询

SELECT p2.guid, p2.email,mx_time, p2.city_lat_long, p2.user_agent, p2.city, p2.region, p2.country
FROM (SELECT guid, email,MAX(stats_time)as mx_time, 
                    city_lat_long, user_agent, city, region, country 
      FROM page_views 
      WHERE domain ='our' 
            AND DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) 
            BETWEEN DATE('2013-06-21 00:00:00')  AND DATE('2013-08-21 00:00:00') 
      GROUP BY guid) p2 
where p2.email=""

你可以把它改成

SELECT guid, email,MAX(stats_time)as mx_time, city_lat_long, user_agent, city, region, country 
FROM page_views 
WHERE domain ='our' 
  AND DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) 
  BETWEEN DATE('2013-06-21 00:00:00')  AND DATE('2013-08-21 00:00:00')
  AND email=""
GROUP BY guid

此时你不需要两个查询只是和 email="" 到 where 子句

更新 II

你应该避免像这样在 Where clae 中转换数据 DATE(CONVERT_TZ(stats_time,'+00:00','+05:30')) BETWEEN DATE('2013-06-21 00:00:00') AND DATE('2013-08-21 00:00:00')

我们可以把它改成stats_time > '2013-06-21 00:00:00' AND stats_time <= '2013-08-21 00:00:00'吗？

正如我所说，您应该阅读 This Link 这对你有好处!