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