查询调优优化

Question

下面是查询。如何优化这个查询?

SELECT  representative.rep_name                 AS RNAME, 
        SUM(areawise_temp.quantity*product.ptr) AS TOTPTR, 
        SUM(areawise_temp.quantity*product.pts) AS TOTPTS 
FROM    areawise_temp, 
        product, 
        representative 
WHERE   (areawise_temp.bill_date BETWEEN '2015/04/01' AND '2015/04/30') 
AND     areawise_temp.our_cust_id <> '' 
AND     areawise_temp.our_product_id <> '' 
AND     areawise_temp.Pincode IN 
(
    SELECT  pincode_list.pincode 
    FROM    pincode_list 
    WHERE   pincode_list.pin_id IN 
    (
        SELECT  rep_area.pin_id 
        FROM    rep_area 
        WHERE   rep_id IN 
        (
            SELECT  id 
            FROM    representative
        )
    ) 
    GROUP BY pincode_list.pincode
)  
AND areawise_temp.our_product_id = product.id

Answer 1

编码模式IN ( SELECT ... )优化得非常差。改成JOIN

例如最里面的部分可以是

SELECT ra.pin_id
    FROM rep_area AS ra
    JOIN representative AS r  ON r.id = ra.rep_id

还要确保有必要的索引。让我们看看SHOW CREATE TABLE来帮助你。对于上面的代码片段，representative可能有PRIMARY KEY(id) ?

Answer 2

在"优化"此操作之前，请确保它返回正确的结果。对代表的交叉连接操作看起来很奇怪。没有GROUP BY，因此product和quantity中的"总数"有效地乘以representative中的行数。(这样做并不是无效的，但是结果很奇怪我们将会质疑它

已经是2015年了。早该抛弃用于连接操作的老式逗号语法了。使用JOIN关键字。并将连接谓词从WHERE子句重新定位到ON子句。

当我们省略连接谓词时，作为对将来读者的帮助，我们倾向于包含CROSS关键字，作为有意省略连接谓词的指示。

同样，我将避免使用IN (subquery)，并使用连接操作来获得等效的结果。

因此，首先，我重写查询:

 SELECT r.rep_name            AS RNAME    -- not deterministic, no GROUP BY
      , SUM(t.quantity*p.ptr) AS TOTPTR
      , SUM(t.quantity*p.pts) AS TOTPTS 
   FROM areawise_temp t
   JOIN product p
     ON p.id = t.our_product_id
   JOIN ( SELECT l.pincode 
            FROM pincode_list l
            JOIN rep_area a
              ON a.pin_id = l.pin_id
            JOIN representative e
              ON e.id = a.rep_id
           GROUP BY l.pincode
        ) c
     ON c.pincode = t.pincode
  CROSS 
   JOIN representative r
  WHERE t.bill_date      BETWEEN '2015/04/01' AND '2015/04/30' 
    AND t.our_cust_id    <> ''
    AND t.our_product_id <> ''

这应该等同于原始查询，并返回相同的结果(可能具有不同的RNAME值，因为这是不确定的。)

这一点我已经说过了，但是这个与representative的交叉连接看起来很奇怪。我强烈怀疑最初的查询是而不是返回您实际想要返回的结果。

---

在性能方面，我们的下一个问题是our_cust_id和our_product_id列的数据类型…如果它们是数字，则与空字符串的不相等比较是奇数。)bill_date的数据类型，如果它是DATE，那么我们真的希望字面值有破折号分隔符，而不是斜杠。(我认为MySQL可以很好地识别斜杠，但我们更习惯使用破折号来查看日期文字，我们知道这肯定是有效的。)

基本上，我们想知道我们强制MySQL执行的任何隐式数据类型转换，因为这些转换可能会影响索引是否可以使用。

"优化"的下一步是使用EXPLAIN，查看访问计划，并评估我们期望使用的索引是否没有被使用，或者添加合适的索引是否可以提高性能。

使用EXPLAIN优化查询 https://dev.mysql.com/doc/refman/5.5/en/using-explain.html