嵌套查询计算invCount时SQL查询速度异常问题求助

问题现象 #

• 原本查询耗时0.03秒，添加计算invCount的子查询后，耗时直接涨到14秒；移除该子查询后，就算保留第二个子查询，耗时还是0.03秒。
• 所有内连接都已经设置了外键，但找不到第一个子查询的性能瓶颈在哪。

已做的优化 #

• 把packageID的空值判断逻辑从AND (b.packageID = a.packageID OR (b.packageID IS NULL AND a.packageID IS NULL))改成AND (b.packageID <=> a.packageID)，耗时降到了7.8秒；
• 用COUNT(*)替换COUNT(b.inventoryID)，耗时又少了大概0.5秒；
• 删掉AND (b.inCart < '$cartTime' OR b.inCart IS NULL)条件能快1秒，但结果就不对了。

疑问 #

• 有没有支持小于比较同时包含NULL的操作符？
• 复合索引和现有的外键是不是一回事？

初始查询代码 #

SELECT  a.*,  
    f.name, 
    f.partNumber, 
    f.showInAdminStore, 
    f.showInPublicStore, 
    f.productImage, 
    r.mastCatID,
    (SELECT COUNT(b.inventoryID) 
        FROM storeInventory b 
        INNER JOIN events c ON c.eventID = b.eventID
        WHERE b.pluID = a.pluID 
        AND b.listPrice = a.listPrice
        AND b.unlimitedQty = a.unlimitedQty
        AND (b.packageID = a.packageID OR (b.packageID IS NULL AND a.packageID IS NULL))
        AND b.orderID IS NULL
        AND c.isOpen = '1'
        AND b.paymentTypeID <= '2'
        AND (b.inCart < '$cartTime' OR b.inCart IS NULL) ) AS invCount,
    (SELECT COUNT(x.inventoryID) 
        FROM storeInventory x 
        WHERE x.packageID = a.inventoryID) AS packageCount  
    FROM storeInventory a 
    INNER JOIN storePLUs f ON f.pluID = a.pluID
    INNER JOIN storeCategories r ON r.catID = f.catID
    INNER JOIN events d ON d.eventID = a.eventID
    WHERE a.storeFrontID = '1' 
    AND a.orderID IS NULL
        AND a.paymentTypeID <= '2'
    AND d.isOpen = '1'
    GROUP BY a.packageID, a.unlimitedQty, a.listPrice, a.pluID

优化后查询代码 #

SELECT  a.*,  
    f.name, 
    f.partNumber, 
    f.showInAdminStore, 
    f.showInPublicStore, 
    f.productImage, 
    r.mastCatID,
    (SELECT COUNT(*) 
        FROM storeInventory b 
        INNER JOIN events c ON c.eventID = b.eventID
        WHERE b.pluID = a.pluID 
        AND b.listPrice = a.listPrice
        AND b.unlimitedQty = a.unlimitedQty
        AND (b.packageID <=> a.packageID)
        AND b.orderID IS NULL
        AND c.isOpen = '1'
        AND b.paymentTypeID <= '2'
        AND (b.inCart < '$cartTime' OR b.inCart IS NULL) ) AS invCount,
    (SELECT COUNT(x.inventoryID) 
        FROM storeInventory x 
        WHERE x.packageID = a.inventoryID) AS packageCount  
    FROM storeInventory a 
    INNER JOIN storePLUs f ON f.pluID = a.pluID
    INNER JOIN storeCategories r ON r.catID = f.catID
    INNER JOIN events d ON d.eventID = a.eventID
    WHERE a.storeFrontID = '1' 
    AND a.orderID IS NULL
        AND a.paymentTypeID <= '2'
    AND d.isOpen = '1'
    GROUP BY a.packageID, a.unlimitedQty, a.listPrice, a.pluID

内容的提问来源于stack exchange，提问作者Bo Harris