需要连接2个超过200K数据的表。如何准备查询？

Question

我必须连接 2 个表，每个表当前保存超过 200K 的数据。我有成员和订单表。我想要获得没有订购任何东西的会员。

我尝试了以下 SQL 查询；

结构（仅列出我需要的内容）

ORDERS                  MEMBERS
id                      id
member_id               fullName
                        email
                        landLine
                        cellPhone

查询1

$sql  = 'SELECT '.PREFIX.'members.fullName
           , '.PREFIX.'members.email
           , '.PREFIX.'members.landLine
           , '.PREFIX.'members.cellPhone, ';
$sql .= 'FROM '.PREFIX.'members';
$sql .= 'LEFT JOIN ';
$sql .= PREFIX.'orders';
$sql .= ' ON ';
$sql .= PREFIX.'members.id = '.PREFIX.'orders.member_id';
$sql .= ' WHERE '.PREFIX.'orders.member_id IS NULL LIMIT 50';

查询2

$sql2  = 'select id, fullName, email, landLine, cellphone 
          from '.PREFIX.'members 
          where '.PREFIX.'members.id not in 
             (select member_id from '.PREFIX.'orders) 
          LIMIT 50';

查询3

$sql3  = 'select id, fullName, email, landLine, cellphone 
          from '.PREFIX.'members m 
          where not exists 
            (select null from '.PREFIX.'orders o where s.members_id = o.id) 
          LIMIT 50';

到目前为止，所有3个查询都不起作用。一分多钟过去了，我还在等待结果。

最优雅的方法是什么？哪一个更快并且使用更少的资源？我在这里没有看到任何错误，为什么它会起作用，但由于某种原因它无法列出。

提前感谢您的帮助。

Answer 1

如果您在没有空格的情况下执行SELECT a,b FROM cLEFT JOIN d，则查询将永远无法工作。否则查询 1 应该没问题，并且在我看来这是更好的表达方式。

至于性能，你应该按照 Marco 的建议放置索引。优化器应该足够聪明，能够为所有三个查询发出相同的计划。

Answer 2

我应该尝试

SELECT id, fullName, email, landLine, cellphone 
FROM members
WHERE id NOT IN
    (SELECT DISTINCT member_id FROM orders)
LIMIT 50

确保在 members.id 和 orders.member_id 上建立索引。

Answer 3

好吧，就在这里。删除最后的“IS NULL”部分，以便您可以看到到底发生了什么；当你看到它时，它非常简单 =-)

select * from Members left join orders on Members.id=orders.member_id WHERE member_id IS NULL;

注意：我认为你应该更改你的 'id 的名称将订单表中的“order_id”列更改为“order_id”，将会员表中的“id”列更改为“member_id”，以避免很多混乱。另外不要忘记添加索引。

我使用你的表结构和我自己的值对其进行了测试。它返回迈克尔·乔丹和巴拉克·奥巴马，因为他们没有任何订单。

INSERT INTO orders (id, member_id) VALUES ('aa', '1');
INSERT INTO orders (id, member_id) VALUES ('bb', '1');
INSERT INTO orders (id, member_id) VALUES ('cc', '1');
INSERT INTO orders (id, member_id) VALUES ('dd', '1');
INSERT INTO orders (id, member_id) VALUES ('gg', '2');
INSERT INTO orders (id, member_id) VALUES ('hh', '2');
INSERT INTO orders (id, member_id) VALUES ('ii', '2');
INSERT INTO orders (id, member_id) VALUES ('xx', '3');
INSERT INTO orders (id, member_id) VALUES ('yy', '3');
INSERT INTO orders (id, member_id) VALUES ('zz', '3');

INSERT INTO members (id, fullName, email, landLine, cellPhone) VALUES ('1', 'John smith', '[email protected]', '1234567890', '1234567890');
INSERT INTO members (id, fullName, email, landLine, cellPhone) VALUES ('2', 'Person B', '[email protected]', '1234567890', '1234567890');
INSERT INTO members (id, fullName, email, landLine, cellPhone) VALUES ('3', 'Tom Brady', '[email protected]', '1234567890', '1234567890');
INSERT INTO members (id, fullName, email, landLine, cellPhone) VALUES ('4', 'Michael Jordan', '[email protected]', '1234567890', '1234567890');
INSERT INTO members (id, fullName, email, landLine, cellPhone) VALUES ('5', 'Barack Obama', '[email protected]', '1234567890', '1234567890');

Answer 4

首先确保您正在使用的所有列都有索引。

您想要做的是对成员的 ID 和订单进行左连接，并且每当成员在 ID 列中具有空值时，它们就不存在于其他表中。在 PHP 中执行查询之前，请先在 MySQL 工作台中测试查询，以便您可以了解发生了什么。

如果您向我展示您的表结构，我将为您写出一个有效的查询。

Answer 5

您的第一个查询似乎没问题。

确保：

1) orders.member_id 是 members.id 的 fk
2）在orders.member_id和members.id上有一个索引

我还建议您直接在mysql客户端上运行查询并在那里优化它，然后尝试从php。至少你会确保，在优化时，mysql不是瓶颈，而是其他东西。