请根据所提到的行数告诉我哪个查询是有效的。

Question

行:客户- 3,000行代码- 40,000行库存警报- 11,000行:卡片批- 9,000,000行

以下查询所需时间超过2分钟。

SELECT cia.cia_orderamount,
       cia.cia_notes,
       cia.cia_mincount,
       cia.cia_customerid,
       cia.cia_id,
       cia.cia_locationid,
       lc.locationcode,
       T1.instock,
       c.id AS customerid,
       c.customer
FROM cardinventoryalerts cia
INNER JOIN customers c ON cia.cia_customerid = c.id
AND c.useautocardorder = 1
INNER JOIN locationcodes lc ON cia.cia_locationid = lc.id
LEFT JOIN
  (SELECT cb.customer,
          CASE
              WHEN cb.locationcode IS NULL
                   OR cb.locationcode = '' THEN NULL
              ELSE cb.locationcode
          END AS locationcode,
          sum(CASE WHEN cb.issued = 'no' THEN 1 ELSE 0 END) AS instock
   FROM cardbatch cb
   GROUP BY cb.customer,
            cb.locationcode ) AS T1 ON lc.locationcode = T1.locationcode
AND T1.customer = c.customer
WHERE (cia_mincount > T1.instock
       OR T1.instock IS NULL)
UNION
SELECT cia.cia_orderamount,
       cia.cia_notes,
       cia.cia_mincount,
       cia.cia_customerid,
       cia.cia_id,
       -1,
       NULL,
       T1.instock,
       c.id AS customerid,
       c.customer
FROM cardinventoryalerts cia
INNER JOIN customers c ON cia.cia_customerid = c.id
AND c.useautocardorder = 1
LEFT JOIN
  (SELECT cb.customer,
          -1,
          sum(CASE WHEN cb.issued = 'no' THEN 1 ELSE 0 END) AS instock
   FROM cardbatch cb
   WHERE cb.locationcode IS NULL
     OR cb.locationcode = ''
   GROUP BY cb.customer ) AS T1 ON c.customer = T1.customer
WHERE (cia_mincount > T1.instock
       OR T1.instock IS NULL)
  AND cia.cia_locationid IS NULL

我试图避免使用UNION来提高查询的性能。所以我使用了下面的左联接，但是它运行了2分钟以上，因为Card批处理包含了更多的行。

SELECT cia.cia_orderamount,
       cia.cia_notes,
       cia.cia_mincount,
       cia.cia_customerid,
       cia.cia_id,
       COALESCE(cia.cia_locationid,-1),
       COALESCE(lc.locationcode,NULL),
       T1.instock,
       c.id AS customerid,
       c.customer
FROM cardinventoryalerts cia
INNER JOIN customers c ON cia.cia_customerid = c.id
AND c.useautocardorder = 1
LEFT JOIN locationcodes lc ON cia.cia_locationid = lc.id
LEFT JOIN
  (SELECT cb.customer,
          CASE
              WHEN cb.locationcode IS NULL
                   OR cb.locationcode = '' THEN NULL
              ELSE cb.locationcode
          END AS locationcode,
          sum(CASE WHEN cb.issued = 'no' THEN 1 ELSE 0 END) AS instock
   FROM cardbatch cb
   GROUP BY cb.customer,
            cb.locationcode ) AS T1 ON lc.locationcode = T1.locationcode
AND T1.customer = c.customer
LEFT JOIN
  (SELECT cb.customer,
          -1,
          sum(CASE WHEN cb.issued = 'no' THEN 1 ELSE 0 END) AS instock
   FROM cardbatch cb
   WHERE cb.locationcode IS NULL
     OR cb.locationcode = ''
   GROUP BY cb.customer ) AS T2 ON c.customer = T2.customer
WHERE (cia_mincount > T1.instock
       OR T1.instock IS NULL)
  OR ((cia_mincount > T2.instock
       OR T2.instock IS NULL)
      AND cia.cia_locationid IS NULL)

现在我正在尝试这个方法，请让我知道这会很好。

CREATE TABLE `cardbatchtemp` ( `customer` varchar(100) DEFAULT NULL, `locationcode` varchar(50) DEFAULT NULL, `instock` int(11) DEFAULT NULL , KEY `cardnumber_customer` (`customer`,`locationcode`) );


INSERT INTO `cardbatchtemp`
SELECT cb.customer,
       COALESCE(cb.locationcode,' '),
       sum(CASE WHEN cb.issued = 'no' THEN 1 ELSE 0 END) AS instock
FROM cardbatch cb
GROUP BY cb.customer,
         COALESCE(cb.locationcode,' ');


SELECT cia.cia_orderamount,
       cia.cia_notes,
       cia.cia_mincount,
       cia.cia_customerid,
       COALESCE(cia.cia_id,-1),
       COALESCE(cia.cia_locationid,NULL),
       lc.locationcode,
       cb.instock,
       c.id AS customerid,
       c.customer
FROM cardinventoryalerts cia
INNER JOIN customers c ON cia.cia_customerid = c.id
AND c.useautocardorder = 1
LEFT JOIN locationcodes lc ON cia.cia_locationid = lc.id
LEFT JOIN cardbatchtemp cb ON (lc.locationcode = cb.locationcode
                               OR cb.locationcode IS NULL)
AND (cb.customer = c.customer)
WHERE (cia_mincount > cb.instock
       OR cb.instock IS NULL)

请给我建议。

Answer 1

在左侧联接查询中添加WHERE cb.issued = 'no'并将其替换为count，以减少已处理记录的数量

SELECT cb.customer,
      CASE
          WHEN cb.locationcode IS NULL
               OR cb.locationcode = '' THEN NULL
          ELSE cb.locationcode
      END AS locationcode,
      count(cb.issued) AS instock
FROM cardbatch cb
WHERE cb.issued = 'no'
GROUP BY cb.customer,
        cb.locationcode