MySQL中的SQL優(yōu)化建議那么多，該如何有的放矢
今天早上看到同事的一個(gè)優(yōu)化需求，優(yōu)化的時(shí)間其實(shí)不多，但是對(duì)于這條SQL的優(yōu)化思考了很多，希望有一些參考。
業(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)看，目前是不滿足需求的，想看看我們有沒有優(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)，也可能沒有，只要有匹配的就返回，累計(jì)返回10條，看起來(lái)是為了去重才選擇了union的組合方式。
先不看表結(jié)構(gòu)信息，我大體有了如下的建議：
- union的模式更建議采用union all,兩個(gè)數(shù)據(jù)源存在數(shù)據(jù)重合應(yīng)該是不合理的。
- 查詢語(yǔ)句里面使用了order_time但是數(shù)據(jù)返回壓根沒有用到，建議去掉
- 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è)需求的收益更高，他們采用其他需求的可能性才越大，否則就是不作為了。
所以到了這里，我們開始做下分析，要優(yōu)化SQL不看看執(zhí)行計(jì)劃是不過(guò)關(guān)的，在執(zhí)行前，我的大體感覺表數(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è)子查詢拆開單獨(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)題也顯而易見了。
修改了子查詢的條件為字符后，整個(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)就可以逐步展開了，而從建議的角度來(lái)看，采用的概率也會(huì)高一些。
當(dāng)然在這個(gè)基礎(chǔ)上確實(shí)有一些補(bǔ)充的建議，在定位瓶頸之后也可以攤開來(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ǔ)性能的開銷會(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ù)合索引
