作者介紹
黃浩，現任職于中國惠普，從業十年，始終專注于sql。在華為做項目的兩年多，做過大大小小的sql多達1500個。閑暇之餘，喜歡将部分案例寫成部落格發表在華為内部資料庫官方社群，反響強烈，已連續四個月蟬聯該社群最佳部落客。目前已開設專欄“優哉悠齋”，成為首個受邀社群“專家訪談”的外協人員。
這是一次值得紀念的優化，值得回憶的内容非常豐富，雖然這個sql本身并不複雜，幾乎是一個相對規範式的sql，是以，這次優化的重點并不是sql的改寫，而更多的是業務需求、實體模型的優化。在長達3個月，曆經5個版本的優化過程中，也不泛優化與開發、功能與性能、測試與開發間關系的微妙變化，其間各方的博弈也耐人尋味。
事出有因
系統存在一個功能，“編輯日志查詢”，顧名思義就是查詢被修改的曆史記錄（這個功能的存在性有待商榷）。功能剛上線的時候，由于資料量少，相安無事，使用甚歡。由于基本上都是大批量的編輯，導緻了日志資料量急劇增長，每天的增量大概在100萬左右，兩個月後，資料量無情的增長到了6000萬，性能隐患也日益凸顯，終于換來了一封來自業務使用者的郵件，于是我也收到了一個需要優化的sql，如下：
大膽假設，小心求證
我做sql優化有個習慣，拿到sql的時候，并不急于去看執行計劃，而是先要快速浏覽一遍sql，一看結構，二看内容，因為我堅信80%的性能問題是由于sql寫法不當導緻的，比如備援的對象通路、備援的關聯條件、備援的過濾條件、無意義的distinct\union\group\order、自定義函數等等這些常見的問題。這個sql也不例外，很明顯timezone_date_translator這個自定義函數可能會是性能瓶頸。
為了驗證自己的判斷，我将函數去掉後，再執行，果然性能得到了質的提升。因為符合了性能名額，我也沒有深入的分析原因。
問題嚴重化
逝者如斯，rp_plan_log_t表的資料量日複一日的增加，一個月後，又收到了一封來自一線業務使用者的郵件，這次的郵件内容措辭相對上一封，要嚴厲了很多，大意是：該功能的性能問題已經嚴重影響到一線業務效率，查詢資料居然要等待30s之久，更有甚者直接逾時報錯（120s），是以強烈要求該功能的性能要在5s内。
這封郵件猶如一顆巨石，在平靜的水面炸開了鍋。
sql還是那個sql，我在pl sql裡面執行，平均耗時在10秒内，也沒有郵件中說的30s之久呀。難道是執行計劃的走偏導緻的？因為這是動态拼湊的sql，sqlid變化無常，是以分析當時執行計劃是否走偏的難度很高。
辦案講究的是犯罪現場，而現在“犯罪現場”肯定是不存在的了，那能否可以重制“犯罪現場”呢？雖然此種方案也并不能支撐“執行計劃走偏”的原因分析，但是至少可以為我們拓展思維：會不會是查詢條件變化？會不會是網絡原因？
于是，根據郵件裡面零碎的資訊，我們在生産環境的功能界面上重制了“現場”，但是結果并沒有“犯罪”，也就是說并沒有出現郵件中說的達到30s之久。因為是根據隻言片語拼湊的“現場”，是以可能存在模拟失真的可能性。
為了模拟的真實性，我們聯系上了“案發”當事人，在詢問了“案發”條件後，才得知：原來使用者是在標明某個“項目編碼”下查詢條件下檢索了近一年的日志資料。而由于該日志功能才啟用了不到4個月，也就是說是查詢了某個項目下所有的日志資料。根據使用者提供的資訊，我們在pl sql中執行了sql，确實達到了30s之久，結果資料集的量也達到了500萬+。
至此，我們可以得出這樣的結論：本次查詢的性能問題的原因歸結于資料量，基表的資料量（近一個億）及結果集資料量（500萬+）。
那麼，如何解決呢？一方面是如洪水般迅猛增漲的基表資料，另一方面是超大的結果集傳回。針對這兩個問題，我給出了如下的解決方案：
引入表分區技術，即将基表rp_plan_log_t表按照operate_time字段按月分區，以實作資料的分區命中
為實作資料的分區命中，在查詢界面将operate_time作為必選條件，而且盡量做到不跨月
為配合1、2兩點，建立project_number和operate_time的聯合索引
事情往往是從扯皮開始的
現在，問題來了，這些事情誰來落實呢？先說第二點吧，這是改需求呀，需要與ba協商，找到了ba，ba說自己也做不了主呀，還得要跟業務使用者去确認，這一來二往的，開發人員性子急，就不耐煩了：還是不改了吧，太麻煩了。
再說第一點，資料分區的責任定位也不明确，開發人員說這需要dba來做，dba又說這屬于應用範疇，理應開發人員寫腳本，他們負責執行就好了。扯來扯去，最後又把ba扯出來了：這個事情需要時間來做，ba應該下個需求單，有了需求單，就能評估人天，這樣有人天了，自然就有人來做了。
而ba也在為自己辯護：這屬于純技術範疇，與業務需求無關，說白了是當初在設計模型的時候就該考慮分區技術，是以這個需求單不能下。
最後，開發、ba、dba、使用者及我達成協定：分區由dba來實施，不過需要在下個版本實施；使用者确認可以将operate_time作為必選條件，并且盡量做到壓縮查詢周期；開發人員在project_number和operate_time字段上建立聯合索引。
自查的勇氣
為了避免使用者由“怨責”轉變成“投訴”，項目組對該功能的性能也重視起來，要求性能測試人員嚴格把關，如果性能超過5s就不放行。這樣，開發人員就開始對該功能的性能自檢自查，測試人員也在積極的準備資料做性能驗證。我的責任還是對sql進行分析并優化。
第一次是粗略的過了一遍sql，發現了timezone_date_translator自定義函數；第二次直接是優化了對象模型；這一次才是真正的正面又深入的打量這個sql，其中一段代碼引起了我的興趣：
這段代碼是擷取字段subtitlename值的标量子查詢，從代碼看，該值的擷取邏輯如下：
以operate_type為“其他”為例，在展開之前，我們先看看相關的模型結構。
rp_plan_log_t的模型如下：
該模型中有個business_id的字段，這個字段存放業務id：屬性類型（即operate_type=1）對應的是rp_plan_extension_t.plan_extension_id,其他類型（即operate_type in(2,3,4,5,7,8,9)）對應的是rp_task_t.task_id,site owner(即operate_type = 6)為-100，是以，在擷取字段subtitlename值的時候需要根據operate_type的值分别到不同的表中擷取對應的name值。
我們再看看rp_task_t和rp_plan_extension_t的模型結構
先看rp_task_t表模型：
模型中task_id是主鍵，但是如果你按照正常了解task_id與task_name存在一對一的關系的話，那你就錯了，這也是玄機所在。在rp_task_t表中，task_name與task_id是一對多的關系，即同一個task_name對應多個task_id。
事實上，task_name作為一個實體，也是存在一個獨立的模型，即sds_activity_t，其結構如下：
在這個模型裡，actvity_name就是對應rp_task_t中的task_name，并且該模型裡面的actvity_id與actvity_name在同一個project_number下是一一對應的。
看到這裡就清晰了，原來，為了擷取subtitlename字段值，我們還可以從sds_activity_t表中拿actvity_name字段，如果在rp_plan_log_t表中存放了actvity_id字段值的話。
也就是說，目前operate_type in(2,3,4,5,7,8,9)的情況下，有兩種途徑可以擷取到subtitlename字段值。而這兩種途徑的優劣在哪裡呢？我們對比下rp_task_t和sds_activity_t表的資料量就知道了：
表
存量
增量
rp_task_t
2千萬+
大
rp_task_his_t
420萬+
sds_activity_t
11萬+
小
由此可見，兩種途徑孰優孰劣顯而易見。
無獨有偶，operate_type = 6的情況與此同出一轍，也是存在另一種通過小資料量的表擷取subtitlename的途徑。
正當其時，測試人員提了一個性能bug單，内容是：當選擇了subtitlename查詢條件時，查詢響應非常慢，達到了20s之久。這也印證了我的分析：目前擷取subtitlename字段值是一個潛在的性能瓶頸。
說是潛在的，原因是如果該字段不作為查詢條件，則不會觸發，因為該sql的結果集是分頁的，每次隻傳回15條資料，而作為标量子查詢，也就是執行15次而已；但是，一旦作為了查詢條件，則執行的次數則是巨大的，而标量子查詢中的表都是千萬級的大表。是以就成為了嚴重的性能瓶頸。
至此，我以bug單為契機，适時的提出了優化方案：将business_id拆分成兩個字段，分别存儲actvity_id和attribute_id。
看熱鬧的不嫌事大
當我送出這個方案時，開發人員甚為激動，倒不是因為有了方案而激動，而是因為這個方案于他們而言有點不太靠譜，他給出了如下理由：
增加字段，這是傷筋動骨之舉，隻能在萬不得已的情況下才能實施；
該表的資料并非一個來源，表結構改動後，會涉及到多個來源的代碼同步修改；
目前rp_plan_log_t表的資料量已經上億了，增加字段，就意味着需要對曆史資料進行初始化，動作太大；
現在已接近版本上線日，如此大動作在短時間内完成，風險太大。
其實，說白了，就是這個版本的工作計劃無法承受該方案，是以站在他們的立場，目前正在如火如荼進行版本的功能開發，這是優先保障的，而我的方案被當成了：看熱鬧不嫌事大；但是如果是在原有模型的基礎上，我實難完成優化目标。
時間一天一天過去了，待到上線前一天，這個bug單依然open着，而按照上線變更條例，如果有bug單沒有close掉，是不能上線的。最後關頭，在測試人員的緊逼之下，開發的se動用了“特權”：将該bug單移至到下個版本。盡管測試人員強烈反對，但是功能優先性能的大條件不容挑釁。
斷腕的決心
測試人員上個版本吃了啞巴虧，在版本上線後，第一時間盯着開發人員優化該功能。畢竟頭上懸着業務使用者這把利劍，開發人員也不敢馬虎，也投入了人力優化。方案很簡單，但是對于整個功能代碼而言，涉及到的内容就遠比在表上增加兩個字段複雜得多，從如下郵件截圖可窺一斑：
總結
該功能的性能優化在經曆了自定義函數、分區、索引、業務方案、模型方案後，性能總算是穩定了下來，但是仍然留給了我很多疑問：
模型設計初期是否能考慮全面？是否能做到一步到位？
日志查詢的意義何在？一次性查詢十萬百萬的資料意義何在？
能否有一套成熟的方案來應對查詢條件的動态化？查詢條件是動态組合的，顯然索引不可能動态組合。
本文就先到這。關于以上疑問，後續會另寫文章繼續分享。
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-11-03</b>