单个表上的 MySQL 性能 ORDERBY 日期时间

Question

我有一个像这样的 MySql 事件表:

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| EventId             | int(11)      | NO   | PRI | NULL    | auto_increment |
| ControllerId        | int(11)      | NO   | MUL | NULL    |                |
| EventTypeId         | int(11)      | NO   | MUL | NULL    |                |
| DateTime            | datetime(3)  | NO   | MUL | NULL    |                |
| InputId             | int(11)      | YES  | MUL |         |                |
...
| AdditionalInfo      | text         | YES  |     |         |                |
+---------------------+--------------+------+-----+---------+----------------+

目前里面有200M条记录，一直在运行文件。为了加快速度，我不使用任何连接查询它，但现在我开始进行一些长时间运行的查询。一个运行缓慢的查询的例子如下:

SELECT E.* 
FROM Event E
WHERE (E.EventTypeId != 4 OR (E.EventTypeId = 4 AND E.InputId IS NOT NULL)) 
AND E.EventTypeId != 27 AND E.EventTypeId != 12  
AND E.ControllerId in (5190, 5191, 5192, 5193)
ORDER BY E.DateTime DESC
LIMIT 0, 200

该查询耗时超过 5 分钟!解释的重要(我认为)部分如下所示:

"key_length": "7",
"rows_examined_per_scan": 180071,
"rows_produced_per_join": 125770,
"filtered": "0.06",
"cost_info": {
    "read_cost":    "284389.84",
    "eval_cost":    " 25154.17",
    "prefix_cost":  "309544.01",
    "data_read_per_join": "20M"
},

现在，如果我删除查询末尾的 ORDER BY E.DateTime DESC，大约需要 0.1 秒才能完成。我已经有了 DateTime 的索引。

我想我理解服务器必须读取所有 180k 的概念？ WHERE 子句返回的行在将它们返回给客户端之前对其进行排序，但为什么要花这么长时间？有什么我可以做的吗？复合索引在这里有帮助吗？

Answer 1

排序 180k 行应该不需要 5 分钟，除非您的硬件真的非常慢。对于这个查询:

SELECT E.* 
FROM Event E
WHERE (E.EventTypeId <> 4 OR (E.EventTypeId = 4 AND E.InputId IS NOT NULL)
      )  AND
      E.EventTypeId NOT IN (12, 27) AND
      E.ControllerId in (5190, 5191, 5192, 5193)
ORDER BY E.DateTime DESC
LIMIT 0, 200;

您可以尝试索引 (ControllerId, EventTypeId, InputId)。但是，我猜这不会很好。

一种可能是使用上面的索引，然后一次做一个 Controller :

(SELECT E.* 
 FROM Event E
 WHERE (E.EventTypeId <> 4 OR (E.EventTypeId = 4 AND E.InputId IS NOT NULL)
       )  AND
       E.EventTypeId NOT IN (12, 27) AND
       E.ControllerId  = 5190
 ORDER BY E.DateTime DESC
 LIMIT 0, 200
) UNION ALL
(SELECT E.* 
 FROM Event E
 WHERE (E.EventTypeId <> 4 OR (E.EventTypeId = 4 AND E.InputId IS NOT NULL)
       )  AND
       E.EventTypeId NOT IN (12, 27) AND
       E.ControllerId  = 5191
 ORDER BY E.DateTime DESC
 LIMIT 0, 200
) 
. . . 
ORDER BY DateTime DESC
LIMIT 0, 200;

索引可以更有效地用于每个子查询。