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

Question

行:客户 - 3,000 行位置代码 - 40,000 行卡片库存警报 - 11,000 行:cardbatch - 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 以提高查询性能。所以我使用了下面的Left Join，但它运行了超过2分钟，因为Cardbatch包含更多行。

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