mysql - 迁移后什么会导致mysql性能下降？

Question

我最近开始将我的应用程序从一台主机转移到另一台主机。从我的家用计算机到云中的虚拟机。在测试新节点上的性能时，我注意到严重下降。比较相同查询、相同数据、相同版本的mysql的结果。

在我的家用计算机上:

mysql> SELECT id FROM events WHERE id in (SELECT distinct event AS id FROM results WHERE status='Inactive') AND (DATEDIFF(NOW(), startdate) < 30) AND (DATEDIFF(NOW(), startdate) > -1) AND status <> 10 AND (form = 'IndSingleDay' OR form = 'IndMultiDay');
+------+
| id   |
+------+
| 8238 |
| 8369 |
+------+
2 rows in set (0,57 sec)

在新机器上:

mysql> SELECT id FROM events WHERE id in (SELECT distinct event AS id FROM results WHERE status='Inactive') AND (DATEDIFF(NOW(), startdate) < 30) AND (DATEDIFF(NOW(), startdate) > -1) AND status <> 10 AND (form = 'IndSingleDay' OR form = 'IndMultiDay');
+------+
| id   |
+------+
| 8369 |
+------+
1 row in set (26.70 sec)

这意味着速度慢了 46 倍。那不行。我试图解释为什么它这么慢。对于我的家用计算机:

mysql> explain SELECT id FROM events WHERE id in (SELECT distinct event AS id FROM results WHERE status='Inactive') AND (DATEDIFF(NOW(), startdate) < 30) AND (DATEDIFF(NOW(), startdate) > -1) AND status <> 10 AND (form = 'IndSingleDay' OR form = 'IndMultiDay');
+----+--------------+-------------+--------+---------------+------------+---------+-------------------+---------+-------------+
| id | select_type  | table       | type   | possible_keys | key        | key_len | ref               | rows    | Extra       |
+----+--------------+-------------+--------+---------------+------------+---------+-------------------+---------+-------------+
|  1 | SIMPLE       | events      | ALL    | PRIMARY       | NULL       | NULL    | NULL              |    5370 | Using where |
|  1 | SIMPLE       | <subquery2> | eq_ref | <auto_key>    | <auto_key> | 5       | eventor.events.id |       1 | NULL        |
|  2 | MATERIALIZED | results     | ALL    | idx_event     | NULL       | NULL    | NULL              | 1319428 | Using where |
+----+--------------+-------------+--------+---------------+------------+---------+-------------------+---------+-------------+
3 rows in set (0,00 sec)

对于我的虚拟节点:

mysql> explain SELECT id FROM events WHERE id in (SELECT distinct event AS id FROM results WHERE status='Inactive') AND (DATEDIFF(NOW(), startdate) < 30) AND (DATEDIFF(NOW(), startdate) > -1) AND status <> 10 AND (form = 'IndSingleDay' OR form = 'IndMultiDay');
+----+--------------------+---------+----------------+---------------+-----------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-----------+---------+------+------+-------------+
|  1 | PRIMARY            | events  | ALL            | NULL          | NULL      | NULL    | NULL | 7297 | Using where |
|  2 | DEPENDENT SUBQUERY | results | index_subquery | idx_event     | idx_event | 5       | func |  199 | Using where |
+----+--------------------+---------+----------------+---------------+-----------+---------+------+------+-------------+
2 rows in set (0.00 sec)

如您所见，结果有所不同。我一直无法弄清楚有什么区别。从所有其他角度来看，这两个系统设置看起来很相似。

Answer 1

这种情况下，最有可能出现的问题是子查询的处理。这在 MySQL 的一些最新版本之间发生了变化(旧版本在优化子查询方面做得很差，最新版本做得更好)。

一个简单的解决方案是将 in 替换为 exists 和相关子查询:

SELECT id
FROM events
WHERE exists (SELECT 1
              FROM results
              WHERE status='Inactive' and results.event = events.id
             ) AND
      (DATEDIFF(NOW(), startdate) < 30) AND (DATEDIFF(NOW(), startdate) > -1) AND status <> 10 AND (form = 'IndSingleDay' OR form = 'IndMultiDay');

这应该在两个版本中都能很好地工作，特别是如果您在结果(状态，事件)上有索引。