共計 3172 個字符，預計需要花費 8 分鐘才能閱讀完成。
這期內容當中丸趣 TV 小編將會給大家帶來有關 MySQL 中 SQL 優化建議的示例分析，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。
今天早上看到同事的一個優化需求，優化的時間其實不多，但是對于這條 SQL 的優化思考了很多，希望有一些參考。
業務同學提供的 SQL 如下：
SELECT b.order_id FROM ( SELECT a.order_id, a.order_time AS create_time FROM trade_order a WHERE a.user_id = 12345678 。。。。。。 AND a.deleted = 0 UNION SELECT v.order_id, v.create_time FROM virtual_order v WHERE v.user_id = 12345678 。。。。 ORDER BY order_id DESC ) AS b LIMIT 0, 10;根據反饋，這條 SQL 的執行時長在 200 毫秒，在壓測情況下會到 500 毫秒左右，從業務層面來看，目前是不滿足需求的，想看看我們有沒有優化的建議。
第一印象這條 SQL 執行時長 200~500 毫秒，要優化好像可打的牌不多啊, 如果要想得到一個可接受的基準值，當然反饋會是越快越好。所以從這個角度來看，我們不妨按照毫秒級優化的標準來看，這條 SQL 需要做哪些補充的工作。
首先通過 SQL 看下邏輯情況，整體的邏輯是按照用戶 id 去查詢兩個數據源(trade_order 和 virtual_order)，從兩個數據源查詢出 10 條單號數據返回。這個用戶在兩個數據源中可能有單號，也可能沒有，只要有匹配的就返回，累計返回 10 條，看起來是為了去重才選擇了 union 的組合方式。
先不看表結構信息，我大體有了如下的建議：
union 的模式更建議采用 union all, 兩個數據源存在數據重合應該是不合理的。
查詢語句里面使用了 order_time 但是數據返回壓根沒有用到，建議去掉
SQL 層面承載了太多的數據處理壓力，比如多數據源，去重和過濾，分頁，是不是可以做下精簡。
當然到了這里，和業務的需求就產生了脫節，這就屬于那種看啥都不順眼的狀態，總想找出點問題來，而且對于業務同學來說，哪怕十個八個需求，你得有一個需求的收益更高，他們采用其他需求的可能性才越大，否則就是不作為了。
所以到了這里，我們開始做下分析，要優化 SQL 不看看執行計劃是不過關的，在執行前，我的大體感覺表數據量很大，應該是生成了派生表，然后在數據去重過濾層面的消耗比較大，而兩個子查詢來說，返回的結果集應該很少。 預測的執行情況是：
1)子查詢 trade_order 應該很快，毫米級響應
2)子查詢 virtual_order 應該也很快，但是最后有一個 order by 操作，可能代價略高
3)union 的去重過濾代價相對較大，涉及到兩個結果集的合并，如果返回結果較多，可能是瓶頸
從執行結果來看，讓我有些意外，其中 virtual_order 的返回結果竟然有 40 多萬行，相當于直接走了全表掃描。
而其他的部分也會收到相關影響，所以后續的處理都會受到影響。
為了快速定位問題，我把兩個子查詢拆開單獨執行，查看執行計劃，這是分析瓶頸最快的一種處理思路。
explain SELECT - v.order_id, - v.create_time - FROM - virtual_order v - WHERE - v.user_id = 12345678 。。。;執行計劃如下：
可以看到是直接走了全表掃描，這是一個基礎需求，不會業務同學漏了索引吧，然后查看表結構：
CREATE TABLE `virtual_order` ( `order_id` varchar(255) NOT NULL COMMENT 訂單 ID , 。。。 `user_id` varchar(255) DEFAULT NULL COMMENT 用戶 ID , 。。。 `refund` tinyint(3) DEFAULT NULL COMMENT 是否退款(1: 無,2: 是) , `atc_pay_status` int(3) NOT NULL DEFAULT 0 COMMENT 支付狀態 , 。。。 PRIMARY KEY (`order_id`), KEY `order_status` (`order_status`), KEY `user_id` (`user_id`), KEY `prepaid_account` (`prepaid_account`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8；發現 user_id 是走了索引的，那么問題來了，user_id 既然是索引，但是為什么 SQL 語句中依然走了全表掃描呢?
此處思考 10 秒鐘，繼續往下看。
其實這個時候問題的邊界都很清晰了，SQL 語句很簡單，索引也存在，走了全表掃描，在 MySQL 中可以暫時排除直方圖的影響，目前在 5.7 版本中還不存在直方圖的特性，那么結果只有一個：字段的類型產生了隱式類型轉換。
這個部分可以參考這篇的一篇文章
MySQL 中需要重視的隱式轉換
比如初始化語句如下：
create table test(id int primary key,name varchar(20) ,key idx_name(name)); insert into test values(1, 10),(2, 20然后我們使用如下的兩條語句進行執行計劃的對比測試。
explain select * from test where name=20; explain select * from test where name= rsquo;20 rsquo;;在 name 列為字符類型時，得到的執行計劃列表如下：
可以很明顯的看到，在 name 為字符串類型時，如果 where 條件為 name=20, 則執行全索引掃描，查看 warning 信息會明確提示：
Message: Cannot use range access on index idx_name due to type or collation conversion on field name
所以此處的問題也顯而易見了。
修改了子查詢的條件為字符后，整個 SQL 的執行效率就立馬好多了。
使用 sql_no_cache 的方式測試。
SQL 修改前性能：
+-----------------------+ 2 rows in set (0.27 sec) 修改后性能： +-----------------------+ 2 rows in set (0.00 sec)然后再次查看執行計劃，就都規規矩矩了, 這樣我們就解決了瓶頸問題，而那些規范，更好的改進就可以逐步展開了，而從建議的角度來看，采用的概率也會高一些。
當然在這個基礎上確實有一些補充的建議，在定位瓶頸之后也可以攤開來說了。
優化不是一錘子買賣，在這個基礎上，也發現了一些其他的問題，可以看下這個表的表結構信息，其實能夠發現一些設計上的小問題。
1) 表字段的字符型基本都是 varchar(255), 需要盡可能避免這種使用習慣，對于存儲性能的開銷會有顯著影響
2)使用的 int 類型 int(3)，這種使用對于 int 還是存儲 4 個字節，但是有限范圍大大減少，可以考慮更小的數值類型
3)表的索引比較松散，可以根據業務模型創建復合索引，比如 user_id 和 status 的結合場景更多，應該創建的是 (user_id,status) 的復合索引
上述就是丸趣 TV 小編為大家分享的 MySQL 中 SQL 優化建議的示例分析了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注丸趣 TV 行業資訊頻道。
