今天早上看到同事的一個(gè)優(yōu)化需求，優(yōu)化的時(shí)間其實(shí)不多，但是對(duì)于這條SQL的優(yōu)化思考了很多，希望有一些參考。

[[282433]]

業(yè)務(wù)同學(xué)提供的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; 

根據(jù)反饋，這條SQL的執(zhí)行時(shí)長(zhǎng)在200毫秒，在壓測(cè)情況下會(huì)到500毫秒左右，從業(yè)務(wù)層面來(lái)看，目前是不滿足需求的，想看看我們有沒(méi)有優(yōu)化的建議。

第一印象這條SQL執(zhí)行時(shí)長(zhǎng)200~500毫秒，要優(yōu)化好像可打的牌不多啊,如果要想得到一個(gè)可接受的基準(zhǔn)值，當(dāng)然反饋會(huì)是越快越好。所以從這個(gè)角度來(lái)看，我們不妨按照毫秒級(jí)優(yōu)化的標(biāo)準(zhǔn)來(lái)看，這條SQL需要做哪些補(bǔ)充的工作。

首先通過(guò)SQL看下邏輯情況，整體的邏輯是按照用戶id去查詢兩個(gè)數(shù)據(jù)源(trade_order和virtual_order)，從兩個(gè)數(shù)據(jù)源查詢出10條單號(hào)數(shù)據(jù)返回。這個(gè)用戶在兩個(gè)數(shù)據(jù)源中可能有單號(hào)，也可能沒(méi)有，只要有匹配的就返回，累計(jì)返回10條，看起來(lái)是為了去重才選擇了union的組合方式。

先不看表結(jié)構(gòu)信息，我大體有了如下的建議：

1. union的模式更建議采用union all,兩個(gè)數(shù)據(jù)源存在數(shù)據(jù)重合應(yīng)該是不合理的。
2. 查詢語(yǔ)句里面使用了order_time但是數(shù)據(jù)返回壓根沒(méi)有用到，建議去掉
3. SQL層面承載了太多的數(shù)據(jù)處理壓力，比如多數(shù)據(jù)源，去重和過(guò)濾，分頁(yè)，是不是可以做下精簡(jiǎn)。

當(dāng)然到了這里，和業(yè)務(wù)的需求就產(chǎn)生了脫節(jié)，這就屬于那種看啥都不順眼的狀態(tài)，總想找出點(diǎn)問(wèn)題來(lái)，而且對(duì)于業(yè)務(wù)同學(xué)來(lái)說(shuō)，哪怕十個(gè)八個(gè)需求，你得有一個(gè)需求的收益更高，他們采用其他需求的可能性才越大，否則就是不作為了。

所以到了這里，我們開(kāi)始做下分析，要優(yōu)化SQL不看看執(zhí)行計(jì)劃是不過(guò)關(guān)的，在執(zhí)行前，我的大體感覺(jué)表數(shù)據(jù)量很大，應(yīng)該是生成了派生表，然后在數(shù)據(jù)去重過(guò)濾層面的消耗比較大，而兩個(gè)子查詢來(lái)說(shuō)，返回的結(jié)果集應(yīng)該很少。 預(yù)測(cè)的執(zhí)行情況是：

1)子查詢trade_order應(yīng)該很快，毫米級(jí)響應(yīng)

2)子查詢virtual_order應(yīng)該也很快，但是最后有一個(gè)order by操作，可能代價(jià)略高

3)union的去重過(guò)濾代價(jià)相對(duì)較大，涉及到兩個(gè)結(jié)果集的合并，如果返回結(jié)果較多，可能是瓶頸

從執(zhí)行結(jié)果來(lái)看，讓我有些意外，其中virtual_order的返回結(jié)果竟然有40多萬(wàn)行，相當(dāng)于直接走了全表掃描。

而其他的部分也會(huì)收到相關(guān)影響，所以后續(xù)的處理都會(huì)受到影響。

為了快速定位問(wèn)題，我把兩個(gè)子查詢拆開(kāi)單獨(dú)執(zhí)行，查看執(zhí)行計(jì)劃，這是分析瓶頸最快的一種處理思路。

>>explain SELECT 
    -> v.order_id, 
    -> v.create_time 
    -> FROM 
    -> virtual_order v 
    -> WHERE 
    -> v.user_id = 12345678 
    。。。; 

執(zhí)行計(jì)劃如下：

可以看到是直接走了全表掃描，這是一個(gè)基礎(chǔ)需求，不會(huì)業(yè)務(wù)同學(xué)漏了索引吧，然后查看表結(jié)構(gòu)：

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:無(wú),2:是)', 
  `atc_pay_status` int(3) NOT NULL DEFAULT '0' COMMENT '支付狀態(tài)', 
。。。 
  PRIMARY KEY (`order_id`), 
  KEY `order_status` (`order_status`), 
  KEY `user_id` (`user_id`), 
  KEY `prepaid_account` (`prepaid_account`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8； 

發(fā)現(xiàn)user_id是走了索引的，那么問(wèn)題來(lái)了，user_id既然是索引，但是為什么SQL語(yǔ)句中依然走了全表掃描呢?

此處思考10秒鐘，繼續(xù)往下看。

其實(shí)這個(gè)時(shí)候問(wèn)題的邊界都很清晰了，SQL語(yǔ)句很簡(jiǎn)單，索引也存在，走了全表掃描，在MySQL中可以暫時(shí)排除直方圖的影響，目前在5.7版本中還不存在直方圖的特性，那么結(jié)果只有一個(gè)：字段的類型產(chǎn)生了隱式類型轉(zhuǎn)換。

這個(gè)部分可以參考這篇的一篇文章

MySQL中需要重視的隱式轉(zhuǎn)換

比如初始化語(yǔ)句如下：

create table test(id int primary key,name varchar(20) ,key idx_name(name)); 
insert into test values(1,'10'),(2,'20'); 

然后我們使用如下的兩條語(yǔ)句進(jìn)行執(zhí)行計(jì)劃的對(duì)比測(cè)試。

explain select * from test where name=20; 
explain select * from test where  name=’20’; 

在name列為字符類型時(shí)，得到的執(zhí)行計(jì)劃列表如下：

可以很明顯的看到，在name為字符串類型時(shí)，如果where條件為name=20,則執(zhí)行全索引掃描，查看warning信息會(huì)明確提示：

Message: Cannot use range access on index 'idx_name' due to type or collation conversion on field 'name'

所以此處的問(wèn)題也顯而易見(jiàn)了。

修改了子查詢的條件為字符后，整個(gè)SQL的執(zhí)行效率就立馬好多了。

使用sql_no_cache的方式測(cè)試。

SQL修改前性能：

+-----------------------+ 
 
2 rows in set (0.27 sec) 
 
修改后性能： 
 
+-----------------------+ 
 
2 rows in set (0.00 sec) 

然后再次查看執(zhí)行計(jì)劃，就都規(guī)規(guī)矩矩了,這樣我們就解決了瓶頸問(wèn)題，而那些規(guī)范，更好的改進(jìn)就可以逐步展開(kāi)了，而從建議的角度來(lái)看，采用的概率也會(huì)高一些。

當(dāng)然在這個(gè)基礎(chǔ)上確實(shí)有一些補(bǔ)充的建議，在定位瓶頸之后也可以攤開(kāi)來(lái)說(shuō)了。

優(yōu)化不是一錘子買賣，在這個(gè)基礎(chǔ)上，也發(fā)現(xiàn)了一些其他的問(wèn)題，可以看下這個(gè)表的表結(jié)構(gòu)信息，其實(shí)能夠發(fā)現(xiàn)一些設(shè)計(jì)上的小問(wèn)題。

1) 表字段的字符型基本都是varchar(255),需要盡可能避免這種使用習(xí)慣，對(duì)于存儲(chǔ)性能的開(kāi)銷會(huì)有顯著影響

2)使用的int類型 int(3)，這種使用對(duì)于int還是存儲(chǔ)4個(gè)字節(jié)，但是有限范圍大大減少，可以考慮更小的數(shù)值類型

3)表的索引比較松散，可以根據(jù)業(yè)務(wù)模型創(chuàng)建復(fù)合索引，比如user_id和status的結(jié)合場(chǎng)景更多，應(yīng)該創(chuàng)建的是(user_id,status)的復(fù)合索引