Question

我已经使用SQL好几年了，但很少有什么比简单的插入和选择等更重要的了……所以我不是SQL专家。我想知道是否可以在优化我在SQLite上执行的更复杂的SQL语句方面得到一些帮助，从PHP到PDO。

这个陈述似乎是正确的，只是似乎比我预期的要花更长的时间（或者也许我只是期望太多了）。

这是SQL：

INSERT OR IGNORE INTO MailQueue(SubscriberID, TemplateID)
    SELECT Subscribers.ID,  1  AS TemplateID
    FROM Subscribers 
    INNER JOIN SubscriberGroups ON Subscribers.ID=SubscriberGroups.SubscriberID
    WHERE SubscriberGroups.GroupID IN ( 1 ,  2 ,  3 )
    AND Subscribers.ID NOT IN 
        ( 
        SELECT Subscribers.ID FROM Subscribers 
        INNER JOIN SubscriberGroups ON Subscribers.ID=SubscriberGroups.SubscriberID
        WHERE SubscriberGroups.GroupID IN ( 4 ,  5 ,  6 )
        );

我得到的是一个订阅者列表，在一个或多个组中。我想将订阅者添加到邮件队列中，选择属于一个或多个组（1,2,3）的订阅者，但排除也在另一组组（4,5,6）中的订阅者。

首先，上面的SQL是如何做到这一点的典型方法吗？

其次，我应该有哪些指标才能使这项工作尽可能有效？

目前，在平均规格的LAMP上，大约需要30秒才能通过大约5000个订户记录（以及少数组）。

说到底，表现并不是那么关键，但我想更好地理解这些东西，所以非常感谢任何见解。

布拉德

Answer 1

额外的加入很可能会让你丧命。如果你这样做：

SELECT Subscribers.ID,  1  AS TemplateID
FROM Subscribers 
WHERE EXISTS( SELECT *
                FROM SubscriberGroups
               WHERE Subscribers.ID=SubscriberGroups.SubscriberID
                                 AND SubscriberGroups.GroupID IN ( 1 ,  2 ,  3 ) )

  AND NOT EXISTS( SELECT *
                    FROM SubscriberGroups
                   WHERE Subscribers.ID=SubscriberGroups.SubscriberID 
                     AND SubscriberGroups.GroupID IN ( 4 ,  5 ,  6 )
    );

您还需要确保在SubscriberGroups（SubscriberID，GroupID）上有索引

我的猜测是订阅服务器已经在ID上有了索引，对吧？

EDIT: Another option, which may or may not be faster. Look at the query plans of each to see...

这可能是一次单索引扫描，可能比两次索引搜索更快，但这取决于SQLite的优化器。。。

SELECT Subscribers.ID, 1 AS TemplateID FROM Subscribers INNER JOIN( SELECT SUM( CASE WHEN GroupID IN( 1 , 2 , 3 ) THEN 1 ELSE 0 END ) AS inGroup, SUM( CASE WHEN GroupID IN( 4 , 5 , 6 ) THEN 1 ELSE 0 END ) AS outGroup, SubscriberID FROM SubscriberGroups WHERE SubscriberGroups.GroupID IN ( 1 , 2 , 3 , 4 , 5 , 6 ) ) SubscriberGroups ON Subscribers.ID=SubscriberGroups.SubscriberID AND inGroup > 0 AND outGroup = 0

Answer 2

编写SQL的另一种方法可能更快（我没有要测试的SQLite）：

SELECT
     S.ID,
      1  AS TemplateID     -- Is this really a string? Does it need to be?
FROM
     Subscribers S
LEFT OUTER JOIN SubscriberGroups SG ON
     SG.SubscriberID = S.ID
WHERE
     SG.SubscriberID IS NULL AND
     EXISTS
     (
          SELECT
               *
          FROM
               SubscriberGroups SG2
          WHERE
               SG2.SubscriberID = S.ID AND
               SG2.GroupID IN ( 1 ,  2 ,  3 )  -- Again, really strings?
     )

马特的方法也应该很有效。这一切都取决于SQLite如何决定创建查询计划。

此外，请注意我的评论。如果这些数据类型在数据库中真的被定义为INT数据类型，那么将有一些额外的处理来在两种不同的数据类型之间进行转换。如果它们是数据库中的字符串，有原因吗？这些列中有非数字值吗？

友情链接