2、ndition>
SELECT
DISTINCT
ORDER BY
LIMIT
1、LIMIT 語句
分頁查詢是最常用的場景之一，但也通常也是最容易出問題的地方。比如對于下面簡單的語句，一般 DBA 想到的辦法是在 type, name, create_time 字段上加組合索引。這樣條件排序都能有效的利用到索引，性能迅速提升。
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND
3、 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
4、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
5、 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)在應用框
6、架很多很繁雜，使用方便的同時也小心它可能給自己挖坑。
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,
7、 o.status
FROM operation o
WHERE o.group = 123
AND o.status NOT IN ( 'done' )
ORDER BY o.parent,
o.id
LIMIT 1) t);
8、
執(zhí)行計劃：
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
9、 |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | o | index | | PRIMARY | 8 | | 24 | Using where; Using temporary
10、 |
| 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
11、 |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
重寫為 JOIN 之后，子查詢的選擇模式從 DEPENDENT SUBQUERY 變成 DERIVED，執(zhí)行速度大大加快，從7秒降低到2毫秒。
UPDATE operation o
JOIN (SELECT o.id,
12、 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.
13、id
SET status = 'applying'
執(zhí)行計劃簡化為：
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
14、 |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1 | PRIMARY | | | | | | | | Impossible WHERE noticed after reading const tables
15、 |
| 2 | DERIVED | o | ref | idx_2,idx_5 | idx_5 | 8 | const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
4、混合排序
MySQL
16、不能利用索引進行混合排序。但在某些場景，還是有機會使用特殊方法提升性能的。
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í)行計劃顯示為全表掃描：
+----+-------------+-------+--------+-------------+---------+---------+---------------+-
17、--------+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL
18、| 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毫秒。
S
19、ELECT *
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 *
FR
20、OM 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語句
My
21、SQL 對待 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
22、 WHERE n.id = m.neighbor_id
AND m.inuser = 'xxx')
AND n.topic_type <> 5
執(zhí)行計劃為：
+----+--------------------+-------+------+-----+------------------------------------------+---------+-------+---------+ -----+
| id | select_type | table | t
23、ype | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+ -----+------------------------------------------+---------+-------+---------+ -----+
| 1 | PRIMARY | n | ALL | | NULL | NULL | NULL | 1086041 | Using where
24、 |
| 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 |
+----+--------------------+-------+
25、------+ -----+------------------------------------------+---------+-------+---------+ -----+
去掉 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'
26、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í)行計劃：
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ --
27、---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+ -----+------------------------------------------+---------+ -----+------+ -----+
| 1 | SIMPLE | m | ref | | idx_message_in
28、fo | 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 |
+----+-------------+-------+---
29、-----+ -----+------------------------------------------+---------+ -----+------+ -----+
6、條件下推
外部查詢條件不能夠下推到復雜的視圖或子查詢的情況有：
1、聚合子查詢；2、含有 LIMIT 的子查詢；3、UNION 或 UNION ALL 子查詢；4、輸出字段中的子查詢；
如下面的語句，從執(zhí)行計劃可以看出其條件作用于聚合子查詢之后：
SELECT *
FROM (SELECT target,
Count(*)
FROM ope
30、ration
GROUP BY target) t
WHERE target = 'rm-xxxx'
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--
31、-----------+------------+-------+---------------+-------------+---------+-------+------+-------------+
| 1 | PRIMARY | | ref | | | 514 | const | 2 | Using where |
| 2 | DERIVED | operation | index | idx_4 | idx_4 | 519 | NU
32、LL | 20 | Using index |
+----+-------------+------------+-------+---------------+-------------+---------+-------+------+-------------+
確定從語義上查詢條件可以直接下推后，重寫如下：
SELECT target,
Count(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target
執(zhí)行計劃變?yōu)椋?
+----+-------------+--
33、---------+------+---------------+-------+---------+-------+------+--------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
| 1 | SIMP
34、LE | operation | ref | idx_4 | idx_4 | 514 | const | 1 | Using where; Using index |
+----+-------------+-----------+------+---------------+-------+---------+-------+------+--------------------+
關于 MySQL 外部條件不能下推的詳細解釋說明請參考以前文章：MySQL · 性能優(yōu)化 · 條件下推到物化表 http://mysql.taobao.org/monthly/2016/07/08
35、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語句原意是：先做一系列的
36、左連接，然后排序取前15條記錄。從執(zhí)行計劃也可以看出，最后一步估算排序記錄數(shù)為90萬，時間消耗為12秒。
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
37、 rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL
38、 | 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
39、 | 6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
由于最后 WHERE 條件以及排序均針對最左主表，因此可以先對 my_order 排序提前縮小數(shù)據(jù)量再做左連接。SQL 重寫后如下，執(zhí)
40、行時間縮小為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
41、.selltime DESC
limit 0, 15
再檢查執(zhí)行計劃：子查詢物化后（select_type=DERIVED)參與 JOIN。雖然估算行掃描仍然為90萬，但是利用了索引以及 LIMIT 子句后，實際執(zhí)行時間變得很小。
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table
42、 | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| 1 | PRIMARY |
43、ed2> | 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
44、 | 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 |
+----+-------------+-------
45、-----+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
8、中間結果集下推
再來看下面這個已經初步優(yōu)化過的例子(左連接中的主表優(yōu)先作用查詢條件)：
SELECT a.*,
c.allocated
FROM (
SELECT resourceid
FROM my_distribute d
46、
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
47、 GROUP BY resourcesid) c
ON a.resourceid = c.resourcesid
那么該語句還存在其它問題嗎？不難看出子查詢 c 是全表聚合查詢，在表數(shù)量特別大的情況下會導致整個語句的性能下降。
其實對于子查詢 c，左連接最后結果集只關心能和主表 resourceid 能匹配的數(shù)據(jù)。因此我們可以重寫語句如下，執(zhí)行時間從原來的2秒下降到2毫秒。
SELECT a.*,
c.allocated
FROM (
SELECT resou
48、rceid
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)
49、allocated
FROM my_resources r,
(
SELECT resourceid
FROM my_distribute d
WHERE isdelete = 0
50、 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)了多次。這種寫法不僅存在額外的開銷，還使得整個語句
51、顯的繁雜。使用 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
(
52、 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.resourcesid
總結
數(shù)據(jù)庫編譯器產生執(zhí)行計劃，決定著SQL的實際執(zhí)行方式。但是編譯器只是盡力服務，所有數(shù)據(jù)庫的編譯器都不是盡善盡美的。
上述提到的多數(shù)場景，在其它數(shù)據(jù)庫中也存在性能問題。了解數(shù)據(jù)庫編譯器的特性，才能避規(guī)其短處，寫出高性能的SQL語句。
程序員在設計數(shù)據(jù)模型以及編寫SQL語句時，要把算法的思想或意識帶進來。
編寫復雜SQL語句要養(yǎng)成使用 WITH 語句的習慣。簡潔且思路清晰的SQL語句也能減小數(shù)據(jù)庫的負擔 。
SQL 書寫規(guī)范與各關鍵字詳解
