常被忽視的SQL錯誤用法/語法執行先後次序 (壓低資料維度/效率/速度 提升/加速/加快)

資料來源:https://mp.weixin.qq.com/s?__biz=MzA3MTM3NTA5Ng==&mid=2651062028&idx=4&sn=7603d62c6c580daf52800bf576b11ecd&chksm=84d9de9bb3ae578d521aa5a27c0f951a59fb9a182f4bc13e1ec309c4116332373dfb33a4bde0&scene=0&xtrack=1&key=025fc9d00514bc5b5fd803dc13535ab81f3261230f385570d4f3bb3f53ebb74e7ffc717adda778689abc5857316d899c9c9cacafe3f7bc7813095e146849438bb7791d526f189567277d5a58793572e5&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&version=62060833&lang=zh_TW&pass_ticket=1AMJ6z41rnWl0xV806qMh%2BsjvZ9MJckpAWxn%2B1d9Yh%2BOyq0IxmMnjMHdmWYT8GHN

▲SQL執行順序[由上而下]

    FROM 
    <left_table>

    ON 
    <join_condition>

    <join_type>
     JOIN 
    <right_table>

    WHERE 
    <where_condition>

    GROUP BY 
    <group_by_list>

    HAVING 
    <having_condition>

    SELECT

    DISTINCT 
    <select_list>

    ORDER BY 
    <order_by_condition>

    LIMIT 
    <limit_number>

    

▲LIMIT語句[使用前儘量用WHERE把BASE筆數壓低]

SELECT * 
FROM   operation 
WHERE  type = 'SQLStats' 
       AND name = 'SlowLog' 
ORDER  BY create_time 
LIMIT  1000, 10; 

SELECT   * 
FROM     operation 
WHERE    type = 'SQLStats' 
AND      name = 'SlowLog' 
AND      create_time > '2017-03-16 14:00:00' 
ORDER BY create_time
LIMIT 10;

▲關聯更新，刪除[用JOIN取代WHERE]

UPDATE operation o 
SET    status = 'applying' 
WHERE  o.id IN (SELECT id 
                FROM   (SELECT o.id, 
                               o.status 
                        FROM   operation o 
                        WHERE  o.group = 123 
                               AND o.status NOT IN ( 'done' ) 
                        ORDER  BY o.parent, 
                                  o.id 
                        LIMIT  1) t); 
                        

UPDATE operation o 
       JOIN  (SELECT o.id, 
                            o.status 
                     FROM   operation o 
                     WHERE  o.group = 123 
                            AND o.status NOT IN ( 'done' ) 
                     ORDER  BY o.parent, 
                               o.id 
                     LIMIT  1) t
         ON o.id = t.id 
SET    status = 'applying' 

▲混合排序[使用UNION ALL+虛擬表取代單一WHERE]

SELECT * 
FROM   my_order o 
       INNER JOIN my_appraise a ON a.orderid = o.id 
ORDER  BY a.is_reply ASC, 
          a.appraise_time DESC 
LIMIT  0, 20

SELECT * 
FROM   ((SELECT *
         FROM   my_order o 
                INNER JOIN my_appraise a 
                        ON a.orderid = o.id 
                           AND is_reply = 0 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20) 
        UNION ALL 
        (SELECT *
         FROM   my_order o 
                INNER JOIN my_appraise a 
                        ON a.orderid = o.id 
                           AND is_reply = 1 
         ORDER  BY appraise_time DESC 
         LIMIT  0, 20)) t 
ORDER  BY  is_reply ASC, 
          appraisetime DESC 
LIMIT  20; 

▲EXISTS語句[使用JOIN取代EXISTS]

SELECT *
FROM   my_neighbor n 
       LEFT JOIN my_neighbor_apply sra 
              ON n.id = sra.neighbor_id 
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND EXISTS(SELECT 1 
                  FROM   message_info m 
                  WHERE  n.id = m.neighbor_id 
                         AND m.inuser = 'xxx') 
       AND n.topic_type <> 5  

SELECT *
FROM   my_neighbor n 
       INNER JOIN message_info m 
               ON n.id = m.neighbor_id 
                  AND m.inuser = 'xxx' 
       LEFT JOIN my_neighbor_apply sra 
              ON n.id = sra.neighbor_id 
                 AND sra.user_id = 'xxx' 
WHERE  n.topic_status < 4 
       AND n.topic_type <> 5