常見的SQL錯誤用法，導致效能變差/速度變慢

資料來源:https://mp.weixin.qq.com/s?__biz=MzU0MzQ5MDA0Mw==&mid=2247486096&idx=1&sn=59152b46d959beef285b07dfdb203eb0&chksm=fb0be004cc7c69123dfd307025cd7347fefa2da4f9cc18e38d79bc0c2944ba15ca5ffe829941&scene=0&xtrack=1&key=e85982dd5b83123835df7d2bcc1bacaf1ffbc3a6fd61fa237610e535dab7d0cbf7dde812d89559148ccfa2c29dfa54b22e6efe0d3b455226fc607f4dc84240953eda089bc867f91cf327cab299982b3e&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&version=62060833&lang=zh_TW&pass_ticket=UGOVeqbnEQvZcqrPQI6IxACV3ElFIZae1CncglTzPdNICFzlV3jxZuqTb9ss1BMg

01.LIMIT語句 拖慢速度 [沒有要分頁時，不要使用起始位置]

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

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

02.關聯更新，刪除 [更新時把IN 替換成JOIN]

//---
//X
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);
//---X


//---
//O
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' 
//---O