CTO 說，誰 SQL 有下面這 8 種寫法的直接開了...
?
1
MySQL在2016年仍然保持強勁的數(shù)據(jù)庫流行度增長趨勢。越來越多的客戶將自己的應用建立在MySQL數(shù)據(jù)庫之上，甚至是從Oracle遷移到MySQL上來。但也存在部分客戶在使用MySQL數(shù)據(jù)庫的過程中遇到一些比如響應時間慢，CPU打滿等情況。阿里云RDS專家服務團隊幫助云上客戶解決過很多緊急問題。現(xiàn)將《ApsaraDB專家診斷報告》中出現(xiàn)的部分常見SQL問題總結如下，供大家參考。
常見SQL錯誤用法
1. LIMIT 語句
分頁查詢是最常用的場景之一，但也通常也是最容易出問題的地方。比如對于下面簡單的語句，一般DBA想到的辦法是在type, name, create_time字段上加組合索引。這樣條件排序都能有效的利用到索引，性能迅速提升。
SELECT?*
FROM???operation
WHERE??type?= 'SQLStats'?
???????AND?name?= 'SlowLog'?
ORDER??BY?create_time
LIMIT??1000, 10;好吧，可能90%以上的DBA解決該問題就到此為止。但當 LIMIT 子句變成 “LIMIT 1000000,10” 時，程序員仍然會抱怨：我只取10條記錄為什么還是慢？
要知道數(shù)據(jù)庫也并不知道第1000000條記錄從什么地方開始，即使有索引也需要從頭計算一次。出現(xiàn)這種性能問題，多數(shù)情形下是程序員偷懶了。在前端數(shù)據(jù)瀏覽翻頁，或者大數(shù)據(jù)分批導出等場景下，是可以將上一頁的最大值當成參數(shù)作為查詢條件的。SQL重新設計如下：
SELECT???*
FROM?????operation
WHERE????type?= 'SQLStats'?
AND??????name?= 'SlowLog'?
AND??????create_time > '2017-03-16 14:00:00'?
ORDER?BY?create_time limit?10;在新設計下查詢時間基本固定，不會隨著數(shù)據(jù)量的增長而發(fā)生變化。
2. 隱式轉換
SQL語句中查詢變量和字段定義類型不匹配是另一個常見的錯誤。比如下面的語句：
mysql> explain extended SELECT *
?????> FROM my_balance b
?????> WHERE b.bpn = 14000000123?
?????> AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739?| Cannot use ref access on?index 'bpn'?due to type or?collation conversion on?field 'bpn'其中字段bpn的定義為varchar(20)，MySQL的策略是將字符串轉換為數(shù)字之后再比較。函數(shù)作用于表字段，索引失效。
上述情況可能是應用程序框架自動填入的參數(shù)，而不是程序員的原意。現(xiàn)在應用框架很多很繁雜，使用方便的同時也小心它可能給自己挖坑。
3. 關聯(lián)更新、刪除
雖然MySQL5.6引入了物化特性，但需要特別注意它目前僅僅針對查詢語句的優(yōu)化。對于更新或刪除需要手工重寫成JOIN。
比如下面UPDATE語句，MySQL實際執(zhí)行的是循環(huán)/嵌套子查詢（DEPENDENT SUBQUERY)，其執(zhí)行時間可想而知。
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);執(zhí)行計劃：
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
|?1??| PRIMARY |?o | index |???????????????| PRIMARY |?8???????| |?24???| Using where; Using temporary |
| 2 |?DEPENDENT SUBQUERY | |???????| |?????????| |???????| |?Impossible WHERE noticed after reading const tables |
|?3??| DERIVED |?o | ref |?idx_2,idx_5 | idx_5 |?8???????| const |?1????| Using where; Using filesort |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+重寫為JOIN之后，子查詢的選擇模式從DEPENDENT SUBQUERY變成DERIVED,執(zhí)行速度大大加快，從7秒降低到2毫秒。
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'執(zhí)行計劃簡化為：
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
|?1??| PRIMARY |???????| |???????????????| |?????????| |??????| Impossible WHERE noticed after reading const tables |
| 2 |?DERIVED | o |?ref | idx_2,idx_5 |?idx_5 | 8 |?const | 1 |?Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+4. 混合排序
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執(zhí)行計劃顯示為全表掃描：
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| 1 |?SIMPLE | a |?ALL | idx_orderid |?NULL | NULL |?NULL | 1967647 |?Using filesort |
|??1?| SIMPLE |?o | eq_ref |?PRIMARY | PRIMARY |?122?????| a.orderid |???????1?| NULL |
+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+由于is_reply只有0和1兩種狀態(tài)，我們按照下面的方法重寫后，執(zhí)行時間從1.58秒降低到2毫秒。
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;5. EXISTS語句
MySQL對待EXISTS子句時，仍然采用嵌套子查詢的執(zhí)行方式。如下面的SQL語句：
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執(zhí)行計劃為：
+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
|??1?| PRIMARY |?n | ALL |??| NULL |?NULL | NULL |?1086041?| Using where |
| 1 |?PRIMARY | sra |?ref | |?idx_user_id | 123 |?const | 1 |?Using where |
|??2?| DEPENDENT SUBQUERY |?m | ref |??| idx_message_info |?122?????| const |???????1?| Using index condition; Using where |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+去掉exists更改為join，能夠避免嵌套子查詢，將執(zhí)行時間從1.93秒降低為1毫秒。
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新的執(zhí)行計劃：
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
|??1?| SIMPLE |?m | ref |?| idx_message_info |?122?????| const |????1?| Using index condition |
| 1 |?SIMPLE | n |?eq_ref | |?PRIMARY | 122 |?ighbor_id | 1 |?Using where |
|??1?| SIMPLE |?sra | ref |?| idx_user_id |?123?????| const |????1?| Using where |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+6. 條件下推
外部查詢條件不能夠下推到復雜的視圖或子查詢的情況有：
聚合子查詢；
含有LIMIT的子查詢；
UNION 或UNION ALL子查詢；
輸出字段中的子查詢；
如下面的語句，從執(zhí)行計劃可以看出其條件作用于聚合子查詢之后：
SELECT?*
FROM???(SELECT?target,
???????????????Count(*)
????????FROM???operation
????????GROUP??BY?target) t
WHERE??target = 'rm-xxxx'+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
|?1?| PRIMARY |? | ref |?0 > | | ?514?????| const |?2?| Using where |
| 2 |?DERIVED | operation |?index | idx_4 |?idx_4 | 519 |?NULL | 20 |?Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+確定從語義上查詢條件可以直接下推后，重寫如下：
SELECT?target,
???????Count(*)
FROM???operation
WHERE??target = 'rm-xxxx'?
GROUP??BY?target執(zhí)行計劃變?yōu)椋?/span>
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
|?1?| SIMPLE |?operation | ref |?idx_4 | idx_4 |?514?| const |?1?| Using where; Using index |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+關于MySQL外部條件不能下推的詳細解釋說明請參考以前文章：MySQL · 性能優(yōu)化 · 條件下推到物化表
7. 提前縮小范圍
先上初始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該SQL語句原意是：先做一系列的左連接，然后排序取前15條記錄。從執(zhí)行計劃也可以看出，最后一步估算排序記錄數(shù)為90萬，時間消耗為12秒。
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
|??1?| SIMPLE |?o | ALL |?NULL | NULL |?NULL | NULL |?909119?| Using where; Using temporary; Using filesort |
| 1 |?SIMPLE | u |?eq_ref | PRIMARY |?PRIMARY | 4 |?o.uid | 1 |?NULL |
|??1?| SIMPLE |?p | ALL |?PRIMARY | NULL |?NULL | NULL |??????6?| Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+由于最后WHERE條件以及排序均針對最左主表，因此可以先對my_order排序提前縮小數(shù)據(jù)量再做左連接。SQL重寫后如下，執(zhí)行時間縮小為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再檢查執(zhí)行計劃：子查詢物化后（select_type=DERIVED)參與JOIN。雖然估算行掃描仍然為90萬，但是利用了索引以及LIMIT 子句后，實際執(zhí)行時間變得很小。
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id |?select_type | table |?type | possible_keys |?key | key_len |?ref | rows |?Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
|??1?| PRIMARY |? | ALL |?NULL | NULL |?NULL | NULL |?????15?| Using temporary; Using filesort |
| 1 |?PRIMARY | u |?eq_ref | PRIMARY |?PRIMARY | 4 |?o.uid | 1 |?NULL |
|??1?| PRIMARY |?p | ALL |?PRIMARY | NULL |?NULL | NULL |??????6?| Using where; Using join buffer (Block Nested Loop) |
| 2 |?DERIVED | o |?index | NULL |?idx_1 | 5 |?NULL | 909112 |?Using where |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+ 8. 中間結果集下推
再來看下面這個已經(jīng)初步優(yōu)化過的例子(左連接中的主表優(yōu)先作用查詢條件)：
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
???????????????????GROUP?BY?resourcesid) c
ON????????a.resourceid = c.resourcesid那么該語句還存在其它問題嗎？不難看出子查詢 c 是全表聚合查詢，在表數(shù)量特別大的情況下會導致整個語句的性能下降。
其實對于子查詢 c，左連接最后結果集只關心能和主表resourceid能匹配的數(shù)據(jù)。因此我們可以重寫語句如下，執(zhí)行時間從原來的2秒下降到2毫秒。
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但是子查詢 a 在我們的SQL語句中出現(xiàn)了多次。這種寫法不僅存在額外的開銷，還使得整個語句顯的繁雜。使用WITH語句再次重寫：
WITH a AS
(
?????????SELECT???resourceid
?????????FROM?????my_distribute d
?????????WHERE????isdelete = 0?
?????????AND??????cusmanagercode = '1234567'?
?????????ORDER?BY?salecode limit?20)
SELECT????a.*,
??????????c.allocated
FROM??????a
LEFT?JOIN?
??????????(
???????????????????SELECT???resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
???????????????????FROM?????my_resources r,
????????????????????????????a
???????????????????WHERE????r.resourcesid = a.resourcesid
???????????????????GROUP?BY?resourcesid) c
ON????????a.resourceid = c.resourcesidAliSQL即將推出WITH語法，敬請期待。
?
2
數(shù)據(jù)庫編譯器產(chǎn)生執(zhí)行計劃，決定著SQL的實際執(zhí)行方式。但是編譯器只是盡力服務，所有數(shù)據(jù)庫的編譯器都不是盡善盡美的。上述提到的多數(shù)場景，在其它數(shù)據(jù)庫中也存在性能問題。了解數(shù)據(jù)庫編譯器的特性，才能避規(guī)其短處，寫出高性能的SQL語句。
程序員在設計數(shù)據(jù)模型以及編寫SQL語句時，要把算法的思想或意識帶進來。編寫復雜SQL語句要養(yǎng)成使用WITH語句的習慣。簡潔且思路清晰的SQL語句也能減小數(shù)據(jù)庫的負擔 。
使用云上數(shù)據(jù)庫遇到難點（不局限于SQL問題），隨時尋求阿里云原廠專家服務的幫助。
評論
圖片
表情