800人蔘賽只有1人全部答對，題目卻是現實中發生過的故障？
- 2019 年 11 月 5 日
- 筆記
喜大普奔，騰訊雲首屆資料庫診斷賽初賽圓滿結束啦~經過一個月緊鑼密鼓的比賽，騰訊雲智慧資料庫管家DBbrain對每個參賽用戶的實例進行了健康巡檢和打分，恭喜以下30名選手進入決賽！
有朋友問了：雖然我沒有進入決賽，但是重在參與，對於初賽的題目我還有很多疑惑，到底應該怎麼調優呢？
那麼今天除了公布成績，數據君也為大家帶來了初賽的賽題解析和優秀選手的解題思路分享。
一、賽題設置
首先我們來回顧一下比賽規則：騰訊雲在雲伺服器上模擬業務訪問，造成每組的MySQL雲資料庫上出現故障、異常、隱患等現象，參賽者需根據云基礎監控和日誌資訊，在不降低業務訪問量的情況下，對業務程式碼或資料庫進行優化。騰訊雲智慧資料庫管家DBbrain對每個參賽用戶的實例進行健康巡檢和打分，比賽結束後，按照DBbrain的健康打分確定晉級/排名。如分數相同的，用時最短的參賽者獲勝。本文的賽題解析中的優化方案，由DBbrain給出。
比賽共設置了兩道題目，分別關於更新語句和查詢語句。
先來看一下題目：
一、庫表結構
1. Order表
CREATE TABLE `order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `creator` varchar(24) NOT NULL, `price` varchar(64) NOT NULL, `create_time` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP, `status` tinyint(1) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
2. Order_item表
CREATE TABLE `order_item` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `parent` bigint(20) NOT NULL, `status` int(11) NOT NULL, `type` varchar(12) NOT NULL DEFAULT '0', `quantity` int(11) NOT NULL DEFAULT '1', `update_time` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
二、待優化的SQL
1. 更新語句
1.1）SQL語句
update `order` setcreate_time = now()where id in ( select parent from order_itemwhere type = 2)
1.2）執行時間：執行時間很長，甚至臨時空間滿
1.3）執行計劃
2. 查詢語句
2.1）SQL語句
SELECT *FROM `order` o INNER JOIN order_itemi ON i.parent = o.idORDER BY o.status ASC, i.update_timeDESCLIMIT 0, 20
2.2）執行時間：2.6秒
2.3）執行計劃
二、賽題解析
一、更新語句
1. 優化思路
對這一個常見的更新語句，我們首先想到的是利用好mysql在semijoin上的優化能力。不少參賽者將其改成如下形式，性能會變得更差。這種方式導致semijoin失效，聚合查詢dependent subquery被執行多次。
update `order` setcreate_time = now()where id in ( select distinct(parent) from order_item where type = 2)
其次是要根據實際數據量的大小判斷是否有必要將其改寫成join，以便更改驅動順序，但前提是要估計好子查詢聚合帶來的性能開銷。在這裡我們可以利用好合適的索引將這一開銷減小到最小。創建索引時首先要注意到order_item表中欄位type的定義為varchar，但是SQL語句中的條件值卻是整數，類型不匹配會導致該條件隱式轉換；其次最好創建組合索引，以便 "Using index for group-by"。 2. DBbrain優化方案
2.1）增加索引
alter table `order` add index idx_1(type,parent);
2.2）SQL改寫
update `order` o inner join ( select type, parent from `order_item` where type = '2' group by type, parent) i on o.id = i.parent set create_time = now();
3. 優化效果
3.1）執行時間：毫秒級
3.2）參考執行計劃
二、查詢語句
1. 優化思路
參賽者回饋該SQL的數據模型存在很大問題，有無從下手的感覺。但是該SQL語句來自一個實際用戶的業務場景。開發同學有他建立這一數據模型的理由，找DBA幫忙也是希望我們能以最小代價快速的出效果。這個時候DBA的現場結合業務場景應變能力就很重要。status只有兩種狀態，通過一個unin all就可以將混合排序簡單化解。除此之外，需要利用好索引的排序能力。
2. DBbrain優化方案
2.1）增加索引
alter table order_item add index `item_idx_1` (`update_time`,`parent`);
2.2）SQL改寫
SELECT o.*,i.*FROM ( (SELECT o.id, i.id item_id FROM `order` o INNER JOIN order_item i ON i.parent =o.id WHERE o.status = 0 ORDER BY i.update_time DESC LIMIT 0, 20) UNION ALL (SELECT o.id, i.id item_id FROM `order` o INNER JOIN order_item i ON i.parent =o.id WHERE o.status = 1 ORDER BY i.update_time DESC LIMIT 0, 20) ) tmp INNER JOIN `order` o ON tmp.id = o.id INNER JOIN order_item i ON tmp.item_id =i.idORDER BY o.status ASC, i.update_time DESCLIMIT 0, 20
3. 優化效果
3.1）執行時間：毫秒級
3.2）參考執行計劃
對於本次賽題有選手回饋覺得「有點難」，數據君給大家劃個重點：初賽題目均是從DBbrain為雲上客戶提供資料庫智慧優化服務中遇到的實際業務問題提煉出來的，而且DBbrain能夠實時給出優化建議，在日常工作中有了DBbrain的加持，DBA可以快速解決資料庫問題。如果未來公司業務也遇到了類似問題，建議大家使用DBbrain來為老闆分憂~ 值得一提的是，DBbrain可以為雲上雲下客戶提供7*24小時無人值守的資料庫保障，通過AI技術和DBA經驗庫的結合能為資料庫實例提供日常健康巡檢等多項資料庫服務。DBbrain的用戶價值也是顯而易見的，它不僅能夠幫助DBA高效的管理資料庫和解決資料庫疑難雜症，為企業進行技術賦能，同時也能夠結合客戶的有效回饋，共同構建資料庫完善的生態體系。 歡迎大家點擊「騰訊雲資料庫」公眾號的「一鍵管理」進行體驗，為了讓DBbrain為您提供更好的服務，可以回饋使用建議哦~
另有激動人心的福利放送：騰訊雲將每月開展一次「DBbrain診斷日」活動，精選實際業務中會遇到的資料庫運維難題為例題，特邀資深資料庫專家結合DBbrain的智慧診斷進行業務場景優化解析，歡迎大家後台提供自己遇到的難題，鵝廠專家全天online~
活動預告
沒有進入決賽或者沒來得及參賽的小夥伴也不要沮喪，雙十一瘋狂剁手節來啦，媳婦兒買買買，咱也可以來幾斤資料庫試試，悄悄告訴你，數據君的運營小夥伴幾乎是咬著牙定的折扣，買到就是賺到！
瘋狂11.11
10月21-31日，騰訊雲MySQL低至1.5折起，7元/月；SQL Server全場2折，91元/月，企業新用戶及個人新用戶可領取千元代金券。
，
↓↓點擊閱讀原文購買
