之前已經講過mysql的效能優化，感興趣的朋友可以看看之前的文章，《史上最全的MySQL高效能優化實戰總結！》。但是有些問題其實是我們自身的SQL語句有問題導致的。今天就來總結哪些經常被我們忽視的SQL錯誤寫法，看看你都踩過哪些坑？
一、LIMIT語句
Limit是分頁查詢是最常用的場景之一，但也通常也是最容易出問題的地方。比如對於下面簡單的語句，一般我們覺得在type, name, create_time欄位上加組合索引。這樣條件排序都能有效的利用到索引，效能迅速提升。
SELECT * FROM operation WHERE type = 'xxx' AND name = 'xxx' ORDER BY create_time LIMIT 1000, 10;
但是當資料量很大的時候，當我們查詢最後幾頁資料時，分頁會越來越慢。這就是我們經常碰到的海量資料的分頁問題。這是為什麼呢？
優化方案
因為資料庫也並不知道第1000000條記錄從什麼地方開始，即使有索引也需要從頭計算一次，即進行全表掃描。出現這種效能問題，主要還是我們沒有考慮到大量資料的情況。
其實在前端資料瀏覽翻頁時，是可以將上一頁的最大值作為查詢條件傳給後臺的。SQL 重新設計如下：
select *
from operation
where id>1000000
AND type = 'xxx'
AND name = 'xxx'
ORDER BY create_time
limit 10
經過這種優化，可以保證系統不會隨著資料量的增長而變慢。
二、隱式轉換
SQL語句中查詢變數和欄位定義型別不匹配是另一個常見的錯誤。比如下面的語句：
explain extended select * from my_balance b where b.bpn = 14000000123 and b.isverified is null;
欄位 bpn 的定義為 varchar 型別，而查詢條件傳入的卻是int 型別。MySQL 會將字串轉換為數字之後再比較。函式作用於表欄位，導致所以索引失效。如下圖所示：
這個坑我們以前也遇見過，花了好半天才發現是這個問題。 所以程式設計師在開發的過程中，一定要認真仔細，確保查詢變數和欄位型別匹配。
優化方案
保證傳入的引數型別和欄位定義的型別一致。
所以，上面的sql語句改為如下即可：
explain extended select * from my_balance b where b.bpn = '14000000123' and b.isverified is null;
三、關聯更新、刪除
MySQL5.6之後有個新特性，會自動把SQL語句中的巢狀子查詢優化為關聯查詢（join），所以有些時候你會發現巢狀子查詢的效率和關聯查詢的效率差不多。但是需要特別注意mysql目前僅僅針對查詢語句的優化。對於更新或刪除需要手工重寫成 JOIN。
比如下面 UPDATE 語句，MySQL 實際執行的還是巢狀子查詢（DEPENDENT SUBQUERY)，其執行時間可想而知。
explain extended
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);
執行計劃：
優化方案
改為 JOIN 之後，子查詢的選擇模式從巢狀子查詢(DEPENDENT SUBQUERY) 變成了關聯查詢(DERIVED)，執行速度大大加快
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 = 'applying1
執行計劃簡化為：
四、Order by
MySQL中的兩種排序方式：
1、通過有序索引順序掃描直接返回有序資料,因為索引的結構是B+樹，索引中的資料是按照一定順序進行排列的，所以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using index。
2、Filesort排序，對返回的資料進行排序,所有不是通過索引直接返回排序結果的操作都是Filesort排序，也就是說進行了額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using filesort。
優化方案
一般排序的原則就是：儘量減少額外的排序，通過索引直接返回有序資料。
所以我們需要注意以下這些情況：
1、排序欄位在多個索引中，無法使用索引排序，查詢一次只能使用一個索引：
explain select store_id,email,last_name from my_user order by store_id,email,last_name;
查詢計劃顯示，沒有走所以直接返回有序資料，額外排序放回結果：
2、排序欄位順序與索引列順序不一致，同樣也無法利用索引排序。這個就不舉例了跟where條件是一樣的。
需要注意的是：這些都是細節的東西，經常會在開發過程中忽略。然後SQL就莫名其妙的不走索引了。
五、混合排序
索引中的資料是按照一定順序進行排列的，所以在排序查詢中如果能利用索引直接返回資料，就能避免額外的排序操作。但是如果出現這種混合了升序和降序的情況，MySQL 無法利用索引直接返回排序結果的。
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
執行計劃顯示為全表掃描：
優化方案
此類問題根據實際常見優化，原則就是應該避免這種排序的情況。如果確實有多種排序的需求，最好使用其他的方法提升效能。
六、EXISTS語句
MySQL 對待 EXISTS 子句時，會採用巢狀子查詢的執行方式。如下面的 SQL 語句：
explain SELECT * FROM my_order n LEFT JOIN my_appraise sra ON n.id = sra.orderid WHERE 1=1 AND EXISTS(SELECT 1 FROM my_user m WHERE n.user_id = m.id AND m.usercode = '111' ) AND n.id <> 5
執行計劃為：
優化方案
去掉 exists 更改為 join，能夠避免巢狀子查詢，這樣會大大提高查詢效率。
SELECT * FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbored AND sra.user_id = 'xxx' INNER JOIN message_info m on n.id = m.neighbor_id AND m.inuser = 'xxx' WHERE n.topic_status < 4 AND n.topictype <> 5
新的執行計劃顯示沒有了巢狀子查詢：
七、條件下推
外部查詢條件不能夠下推到複雜的檢視或子查詢的情況有：
-
聚合子查詢；
-
含有 LIMIT 的子查詢；
-
UNION 或 UNION ALL 子查詢；
-
輸出欄位中的子查詢；
如下面的語句，從執行計劃可以看出其條件作用於聚合子查詢之後
SELECT * FROM(SELECT target, Count(*) FROM operation GROUPBY target) t WHERE target = 'rm-xxxx'
優化方案
確定從語義上查詢條件可以直接下推後，重寫如下：
SELECT target, Count(*) FROM operation WHERE target = 'rm-xxxx' GROUPBY target
執行計劃變為：
八、提前縮小資料範圍
先上初始 SQL 語句：
SELECT * FROM my_order o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid WHERE o.display = 0 AND o.ostaus = 1 ORDER BY o.selltime DESC LIMIT 0, 15
數為90萬，時間消耗為12秒。
優化方案
由於最後 WHERE 條件以及排序均針對最左主表，因此可以先對 my_order 排序提前縮小資料量再做左連線。SQL 重寫後如下，執行時間縮小為1毫秒左右。
SELECT * FROM (SELECT * FROM my_order o WHERE o.display = 0 AND o.ostaus = 1 ORDER BY o.selltime DESC LIMIT 0, 15 )o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid ORDER BY o.selltime DESC limit 0, 15
再檢查執行計劃：
子查詢物化後（select_type=DERIVED)參與 JOIN，雖然估算行掃描仍然為90萬，但是利用了索引以及 LIMIT 子句後，實際執行時間變得很小。
九、中間結果集下推
再來看下面這個已經初步優化過的例子(左連線中的主表優先作用查詢條件)：
SELECT a.* c.allocated FROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20 ) a LEFT JOIN ( SELECT resourcesid, sum(allocation) allocated FROM my_resources GROUP BY resourcesid ) c ON a.resourceid = c.resourcesid
那麼該語句還存在其它問題嗎？不難看出子查詢 c 是全表聚合查詢，在表數量特別大的情況下會導致整個語句的效能下降。
其實對於子查詢 c，左連線最後結果集只關心能和主表 resourceid 能匹配的資料。因此我們可以重寫語句如下，執行時間大大降低 。
SELECT a.*, c.allocated FROM ( SELECT resourceid FROM my一distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567* ORDER BY salecode limit 20) a LEFT JOIN ( SELECT resourcesid, sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20 ) a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid ) c ON a.resourceid = c.resourcesid
最後
以上總結了一些sql語句常見的坑。裡面很多都是不仔細導致的。只有仔細認真儘量考慮一些大資料的情況，這樣才能寫出高效能的SQL語句。
同時，程式設計師在設計資料模型以及編寫SQL語句時，要把索引及效能提前考慮進去，這樣才能避免後期遇到的一些坑。