mysql - 相同的查询在 SQL Server/Postgres 上 10 秒内完成，但在 MySQL 上 30 分钟后仍在继续

Question

我试图理解为什么两者之间存在如此大的性能差异。这是我在没有任何更改的情况下运行的查询...

SELECT fs.person1, fs.person2, ls.artist
FROM friends AS fs
LEFT JOIN likes AS ls
ON fs.person2 = ls.person
WHERE NOT EXISTS
(select * from likes where fs.person1 = person and ls.artist = artist)

两者具有相同的数据。如果花费 2-3 倍的时间但从 10 秒到超过 30 分钟是一回事……这令人困惑。

每个表中的数据...

喜欢 = 3 INT 列和 750,000 行

friends = 2 个 INT 列和 150,000 行

Answer 1

我猜测了您的表定义并使用 EXPLAIN 进行了测试以查看优化器将如何处理它。

顺便说一句，当寻求查询优化帮助时，请始终运行 SHOW CREATE TABLE 并包括输出，这样我们就可以看到表定义、您的索引、数据类型和约束。同时为查询运行 EXPLAIN 并显示它。

这是我在使用 EXPLAIN 时得到的查询结果:

+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY            | fs    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | PRIMARY            | ls    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DEPENDENT SUBQUERY | likes | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                                        |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

该 EXPLAIN 报告中出现了几个危险信号。

• 首先，您没有索引这一事实使得所有三个表引用都进行表扫描(类型:ALL)。由于 MySQL 仅执行嵌套循环连接，这意味着您的查询将必须执行 150,000 x 750,000 x 750,000 行读取。难怪需要 30 分钟。

• 第二个是关于“使用连接缓冲区( block 嵌套循环)”的说明，它说它必须分批评估连接，因为没有索引可以在其中进行更有针对性的查找。

  <

创建索引:

ALTER TABLE likes ADD INDEX (person, artist);

那么 EXPLAIN 看起来更好:

+----+--------------------+-------+------------+------+---------------+--------+---------+--------------------------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type | possible_keys | key    | key_len | ref                            | rows | filtered | Extra                    |
+----+--------------------+-------+------------+------+---------------+--------+---------+--------------------------------+------+----------+--------------------------+
|  1 | PRIMARY            | fs    | NULL       | ALL  | NULL          | NULL   | NULL    | NULL                           |    1 |   100.00 | NULL                     |
|  1 | PRIMARY            | ls    | NULL       | ref  | person        | person | 5       | test.fs.person2                |    1 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | likes | NULL       | ref  | person        | person | 10      | test.fs.person1,test.ls.artist |    1 |   100.00 | Using index              |
+----+--------------------+-------+------------+------+---------------+--------+---------+--------------------------------+------+----------+--------------------------+

这消除了两次表扫描和连接缓冲区的使用。

但它仍然留下另一个危险信号:DEPENDENT SUBQUERY。一般来说，MySQL 运行依赖子查询的效率很低，对外部查询的每一行执行一次。因此，您将执行子查询数千次，即使有索引查找的帮助。

我使用 LEFT OUTER JOIN 在 MySQL 中实现反连接。这里有一个详尽的解释:https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

SELECT fs.person1, fs.person2, ls1.artist
FROM friends AS fs
JOIN likes AS ls1
  ON fs.person2 = ls1.person
LEFT OUTER JOIN likes AS ls2
  ON fs.person1 = ls2.person AND ls1.artist = ls2.artist
WHERE ls2.person IS NULL;

这是解释:

+----+-------------+-------+------------+------+---------------+--------+---------+---------------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref                             | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+--------+---------+---------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | fs    | NULL       | ALL  | NULL          | NULL   | NULL    | NULL                            |    1 |   100.00 | Using where              |
|  1 | SIMPLE      | ls1   | NULL       | ref  | person        | person | 5       | test.fs.person2                 |    1 |   100.00 | Using index              |
|  1 | SIMPLE      | ls2   | NULL       | ref  | person        | person | 10      | test.fs.person1,test.ls1.artist |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+---------------------------------+------+----------+--------------------------+

根本没有更多的子查询，并且使用带有索引查找的简单连接解决了反连接。

这应该运行得更快，假设您的索引适合为缓冲池分配的内存。

And any action takes way too much time - like alter table primary key or creating index.

这让我觉得您还没有针对内存分配对 MySQL 进行任何配置。您可能有默认的缓冲池大小 (128MB)。这是您应该根据系统上的可用内存设置的内容。参见 https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/

您可能还想阅读 https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

据我所知read , Microsoft SQL Server 会不时自动调整其缓冲池和其他内存的大小，因此无需手动调整。

在 MySQL 上学习调整配置选项是必要的。他们选择默认调整设置来帮助确保 MySQL 可以在适度的服务器上运行，因为如果你没有那么多物理内存，默认分配 100GB 服务器 RAM 并不是很友好，因为它会导致交换或崩溃。

有一些关于让 MySQL 动态调整自身的讨论，但这是一项非常复杂的任务。也许您不希望 MySQL 使用您系统上的所有可用内存，因为您还运行其他进程。很难为每个人的服务器猜测正确的自动调整值，这样做可能会鼓励人们避免学习如何分配和监控自己的系统资源。