温馨提示:本文翻译自stackoverflow.com,查看原文请点击:select - optimize a query in MySQL 5.7
mysql optimization select groupwise-maximum

select - 在MySQL 5.7中优化查询

发布于 2020-04-07 11:12:57

这是我的系统:

Linux Ubuntu 18.04 LTS
MySQL 5.7

我有一个查询(如下)花了太长时间才能完成。

大约需要9秒钟才能完成。对于用户来说,在等待网页完成加载时,这是不可行的时间。

同样,数据集很大并且还在增长。事件表有250,000行。我倾向于每天增加1200至1800行。

为了帮助优化,我想添加索引等,但是对于如何(以及是否)可以使用派生/联合选择查询来做到这一点感到困惑。

这是我的查询。(任何人都知道如何限制输出的宽度,以便我们不必向右滚动即可看到完整的行?)

mysql> explain select OUTSIDE.ownerUID as Owner, 
  OUTSIDE.propUID as Property, 
  OUTSIDE.camname as 'Camera Name', 
  OUTSIDE.direction as Direction, 
  OUTSIDE.camtimestamp as 'Event Time', 
  convert_tz(now(),'UTC','US/Central') as Now, 
  sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time' 
from events OUTSIDE, 
  (select camname,max(camtimestamp) as maxtimestamp from events group by camname) as INSIDE 
where OUTSIDE.camname = INSIDE.camname 
  AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+----+-------------+------------+------------+-------+----------------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys        | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                 | NULL         | NULL    | NULL                | 263103 |   100.00 | Using where |
|  1 | PRIMARY     | OUTSIDE    | NULL       | ref   | camtimestamp,camname | camtimestamp | 6       | INSIDE.maxtimestamp |      1 |    99.73 | Using where |
|  2 | DERIVED     | events     | NULL       | index | camname              | camname      | 257     | NULL                | 263103 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+----------------------+--------------+---------+---------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.03 sec)

这是查询的结果:

mysql> select OUTSIDE.ownerUID as Owner, 
  OUTSIDE.propUID as Property, 
  OUTSIDE.camname as 'Camera Name', 
  OUTSIDE.direction as Direction, 
  OUTSIDE.camtimestamp as 'Event Time', 
  convert_tz(now(),'UTC','US/Central') as Now, 
  sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time' 
from events OUTSIDE, 
  (select camname,max(camtimestamp) as maxtimestamp from events group by camname) as INSIDE 
where OUTSIDE.camname = INSIDE.camname 
  AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
| Owner | Property | Camera Name | Direction | Event Time          | Now                 | Elapsed Time |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
|     1 |        1 | wls1        | In        | 2020-01-30 12:27:31 | 2020-01-30 12:29:53 | 00:03:42     |
|     1 |        1 | wls2        | Out       | 2020-01-30 12:25:29 | 2020-01-30 12:29:53 | 00:07:04     |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
2 rows in set (6.49 sec)

================================================== ========谢谢所有回答问题或发表评论的人。我接受了Uueerdo关于复合索引的建议,并得出以下结果:

mysql> create index CamNameCamTime on events (camname,camtimestamp);

mysql> select OUTSIDE.ownerUID as Owner,
           OUTSIDE.propUID as Property,
           OUTSIDE.camname as 'Camera Name',
           OUTSIDE.direction as Direction,
           OUTSIDE.camtimestamp as 'Event Time',
           convert_tz(now(),'UTC','US/Central') as Now,
           sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time' 
      from events OUTSIDE,
         (select camname,max(camtimestamp) as maxtimestamp 
           from events group by camname) as INSIDE
      where OUTSIDE.camname = INSIDE.camname AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
| Owner | Property | Camera Name | Direction | Event Time          | Now                 | Elapsed Time |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
|     1 |        1 | wls1        | In        | 2020-01-30 18:43:19 | 2020-01-30 18:44:33 | 00:01:54     |
|     1 |        1 | wls2        | Out       | 2020-01-30 18:41:51 | 2020-01-30 18:44:33 | 00:04:42     |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
2 rows in set (0.00 sec)

mysql> explain select OUTSIDE.ownerUID as Owner,
    ->            OUTSIDE.propUID as Property,
    ->            OUTSIDE.camname as 'Camera Name',
    ->            OUTSIDE.direction as Direction,
    ->            OUTSIDE.camtimestamp as 'Event Time',
    ->            convert_tz(now(),'UTC','US/Central') as Now,
    ->            sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time'
    ->       from events OUTSIDE,
    ->          (select camname,max(camtimestamp) as maxtimestamp
    ->            from events group by camname) as INSIDE
    ->       where OUTSIDE.camname = INSIDE.camname AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+----+-------------+------------+------------+-------+-------------------------------------+----------------+---------+------------------------------------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys                       | key            | key_len | ref                                | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+-------------------------------------+----------------+---------+------------------------------------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                                | NULL           | NULL    | NULL                               |    2 |   100.00 | Using where              |
|  1 | PRIMARY     | OUTSIDE    | NULL       | ref   | camtimestamp,camname,CamNameCamTime | CamNameCamTime | 263     | INSIDE.camname,INSIDE.maxtimestamp |    1 |   100.00 | NULL                     |
|  2 | DERIVED     | events     | NULL       | range | camname,CamNameCamTime              | CamNameCamTime | 257     | NULL                               |    2 |   100.00 | Using index for group-by |
+----+-------------+------------+------------+-------+-------------------------------------+----------------+---------+------------------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)




成功!

查看更多

提问者
user3055756
被浏览
81
Uueerdo 2020-02-04 01:06

MySQL不能在查询中对表的每个引用上使用多个索引,因此在这种情况下camnamecamtimestamp分别使用上的两个简单索引的用途可能非常有限。这样的索引非常适合选择日期范围内的记录或名称的所有记录。但是在搜索每个名称的最大值时,camname索引的用途会令人怀疑(因为无论如何它必须检查每个名称的每个camtimestamp),并且a camstimestamp仍然需要搜索每条记录以确保所有的camname都被考虑了(最小的camtimestamp可能是特定camname的唯一一个)。

通过在(camname, camtimestamp)每个camname子查询的最大值上有一个复合索引,可以快速确定每个子查询的最大值,然后可以再次使用该索引来选择表中与子查询的结果匹配的行。

最新帖子

最新文章