mysql - 在大型数据库上编写更快的选择查询

Question

我有两张表，一张大约有 1,000 行，一张有 700,000 行；分别为表1和表2。我写了一个简单的选择查询:

SELECT DISTINCT name1
FROM table1, table2
WHERE table1.name1 = table2.name2;

查询得到了我想要的结果，但花了 91 秒!我尝试了这个子查询:

SELECT DISTINCT name1
FROM table1
WHERE table1.name1 IN(SELECT DISTINCT name2 FROM table2);

该查询持续花费了 37 秒。因此，编写选择查询的方式可以提高性能。我写了第三个查询:

CREATE TEMPORARY TABLE IF NOT EXISTS t1qry
    (SELECT DISTINCT table1.name1 FROM table1);

CREATE TEMPORARY TABLE IF NOT EXISTS t2qry
    (SELECT DISTINCT table2.name2 FROM table2);

SELECT name2 FROM t2qry JOIN t1qry ON name1 = name2;

DROP TABLE t1qry, t2qry;

最后一个查询运行时间为 0.4 秒，并产生与其他两个查询相同的结果。

我知道每个“选择不同”查询的运行时间不到一秒，因此我试图制作一个可以找到表之间共同的不同值的查询。我的问题是为什么我写的东西有效？如何在不创建临时表的情况下编写更快的选择查询？

我一直在使用 MySQL 和 MariaDB，但我将在此处获取任何与 SQL 相关的帮助。我是 SQL 新手，一直在努力学习尽可能多的知识，因此我会获取有关这方面的任何指示或信息。

Answer 1

如果您的后一个版本(包括创建临时表)运行得如此快，那么您可能在两个表中都对 nameX 建立了索引。

我建议使用exists:

SELECT DISTINCT name1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.name1 = table2.name2);

对于这些查询，您确实需要在 table1(name1) 和 table2(name2) 上建立索引。

并且，如果 table1 没有重复项，则省略 DISTINCT。