2013年中嗤瞎，畢業(yè)第三年，初入上海听系，入職某大型物流公司贝奇，開啟了一段成長之旅

入職第三個(gè)月，很焦灼跛锌，從一個(gè)小公司出來弃秆，原來天不怕地不怕，技術(shù)我就是老大髓帽，現(xiàn)在公司大了菠赚，感覺人人都是大牛，我只不過是混進(jìn)來的小老鼠郑藏。一段時(shí)間下來衡查，感覺總是慫著也不是辦法，怎么辦必盖？找活干,我們經(jīng)理倒是爽快拌牲，好硬一塊骨頭甩過來，那是完全超出自己能力范圍的一個(gè)東西歌粥，怎么說呢塌忽，就像，拳臺上的鄒市明突然聽到自己的對手是泰森失驶。土居。完全不是會不會被咬耳朵的問題，，擦耀，

怎么辦棉圈，不能慫啊，只能把千萬只羊駝關(guān)在心里眷蜓，讓他們自由奔跑分瘾，自己呢，默默的去解決問題吁系，不過說實(shí)話德召，碰到這種沒有一毛錢頭緒的問題，反而讓我很興奮垮抗，感覺腦細(xì)胞完全不受限制的恣意奔跑氏捞。。冒版。哎 哎 哎液茎，你左腦的細(xì)胞跑去腦干干嘛，辞嗡，捆等，，

冷靜下來续室，開始對問題分析栋烤。

業(yè)務(wù)需求:

一線部門，需要根據(jù)查詢條件挺狰，查詢出本部門不同級別用戶的數(shù)據(jù)情況明郭，多么簡單的一個(gè)需求，我看了下丰泊，無非是張幾5千萬級別的數(shù)據(jù)表查詢薯定，哼哼，瞳购。话侄。哼哼。学赛。年堆。哼哼。盏浇。

1.暴露問題变丧，用最蠢的辦法，把多個(gè)查詢語句一一寫出來绢掰，最復(fù)雜的一個(gè)sql500行左右锄贷，包含了各種子查詢译蒂，子查詢A統(tǒng)計(jì)表A，子查詢A嵌套子查詢B谊却，子查詢B統(tǒng)計(jì)查詢join表C，子查詢D分組嵌套子查詢C,DC查詢結(jié)果關(guān)聯(lián)子查詢B,子查詢A關(guān)聯(lián)表BED,得出查詢列F.....編不下去了哑芹，總之各種子查詢結(jié)果統(tǒng)計(jì)炎辨，分組，排序聪姿，時(shí)間的對比碴萧，

2.我在考慮要不要辭職，我的職業(yè)生涯可能就這么跪了末购，

3.冷靜一想破喻，不行我是要做DNA的男人

4.運(yùn)行各個(gè)查詢，保證整個(gè)查詢結(jié)果是和正確的盟榴，起碼保證了語句是可用的了

5.來來來曹质，我們用線上備份時(shí)間跑一下

6.開始！1秒擎场，2秒羽德，，迅办，宅静，125秒，我們?nèi)コ楦鶡熣酒郏f不定結(jié)果就出來了姨夹，

7.400秒，矾策，磷账，450秒，蝴韭，够颠，咱們再去抽一根把。榄鉴。履磨。

8.2058秒，兩盒煙抽完了庆尘。剃诅。。kill

9.explan 語句驶忌，一個(gè)索引都不走矛辕。笑跛。。怎么可能會走索引聊品，里面包含了group by飞蹂，時(shí)間函數(shù)，加減運(yùn)算翻屈。陈哑。還有不知名的排序。伸眶。惊窖。而表中的索引寥寥。厘贼。

10.

：DBA 這個(gè)表 這個(gè)表 還有這個(gè)表要建索引

：你搖頭什么意思界酒？什么線上數(shù)據(jù)會受影響？不就是建幾十個(gè)索引么嘴秸、毁欣、、

：.把你領(lǐng)導(dǎo)叫過來

：.jim哥赁遗，我錯(cuò)了署辉，我態(tài)度不好，恩 下次不敢了岩四，好哭尝，每人一個(gè)七喜沒問題

11.TMD 加了幾個(gè)索引還是不靠譜啊

12 ：DBA姐姐，這是怎么回事剖煌？

：.索引不生效材鹦？索引覆蓋數(shù)據(jù)太多的話，引擎會認(rèn)為全表比較靠譜耕姊？什么鬼邏輯桶唐，hit一下

：.數(shù)據(jù)庫卡死了？我錯(cuò)了茉兰，kill掉吧

第一階段結(jié)束：數(shù)據(jù)量多大尤泽，查詢邏輯復(fù)雜，導(dǎo)致建立的索引很多時(shí)候是失效的规脸，而且索引建立過多坯约，線上數(shù)據(jù)在新增和修改，會受到很大的影響莫鸭，在數(shù)據(jù)變更的過程中闹丐，不僅是對數(shù)據(jù)操作，相關(guān)的索引文件也有進(jìn)行變動被因，導(dǎo)致操作性能低下

也開始意識到卿拴，這種數(shù)據(jù)量下衫仑，復(fù)雜的數(shù)據(jù)查詢邏輯下，全靠索引解決不了一毛錢問題

不得不召喚我的腦干細(xì)胞堕花，來解決問題

不如就從表結(jié)構(gòu)入手吧文狱，既然數(shù)據(jù)量太大，那就分表怎么樣

13 ：.DBA姐姐航徙，我想到一個(gè)好辦法如贷，你覺得分表怎么樣

：分表？你咋不上天到踏？

：我剛從天上下來

：分手可以 分表不行

：為什么？

：因?yàn)槟闾珟浟俗逭梗覀儾缓线m

：我問你為啥分表不行？

：分表的話访忿，涉及到分表的規(guī)則凿掂，路由規(guī)則伴榔，另外你們之前寫的所有數(shù)據(jù)操作的邏輯都要重新寫，你問你們領(lǐng)導(dǎo)見

：不用問我們領(lǐng)導(dǎo)庄萎，這個(gè)事情我拍板踪少？

：？糠涛？援奢？

：不分就不分 有啥大不了

：分區(qū)總行吧

：分區(qū)可以

：那就分區(qū)吧

：回去把規(guī)則定了，看下查詢有沒有效果

14,：進(jìn)入到分區(qū)探索階段忍捡，問題：這么多張表集漾，按照什么進(jìn)行分區(qū)？那些表要分區(qū)砸脊？分區(qū)后sql語句如何調(diào)整

15：解決查詢問題：抽象一點(diǎn)來說具篇，每個(gè)查詢語句無論多復(fù)雜，包含三部分內(nèi)容：

一凌埂，結(jié)果集的行數(shù)（不考慮分頁驱显，當(dāng)未分頁的查詢速度提升了，分頁查詢更加快速）瞳抓，

二埃疫，結(jié)果集的列數(shù)（表字段或聚合函數(shù)），三挨下，排序分組

行數(shù)基本是有where條件決定的熔恢，盡量讓決定行數(shù)的sql效率最優(yōu)，減少此sql不必要的聚合排序操作臭笆，就讓他安安靜靜的做行數(shù)查詢（A）叙淌，這部分只查詢需要的列秤掌，不要貪多哦，以此查詢的結(jié)果集為基礎(chǔ)鹰霍，再與其他表或子查詢進(jìn)行關(guān)聯(lián)查詢（標(biāo)注1）闻鉴，去達(dá)到結(jié)果集列數(shù)的要求，最后對結(jié)果進(jìn)行統(tǒng)一的分組排序聚合茂洒，這是我對于sql優(yōu)化最主要的幾個(gè)心得：

其中有幾個(gè)地方需要注意的孟岛，sql索引建立：

最常用的：單列索引，組合索引督勺，函數(shù)索引

添加索引依據(jù)：where條件中的覆蓋度渠羞，索引的可選擇度

如果在大多數(shù)的查詢中會使用到某表某列的情況，建議添加索引智哀，但是在執(zhí)行sql的時(shí)候不一定會索引次询，在oracle中，大部分情況查詢結(jié)果超過全表的一定比例（10-15%）就不會走索引了瓷叫，屯吊，，尤其是幾千萬的表數(shù)據(jù)的情況

再說索引的可選擇度摹菠，當(dāng)一個(gè)列在標(biāo)準(zhǔn)重復(fù)度越低盒卸，表示此列作為索引的可選擇就越高，此列作為條件做=查詢時(shí)次氨，效率會更好蔽介，為什么？請參考唯一索引糟需。屉佳。。洲押。

16：回到正題武花，分區(qū)情況下，選擇了主表（基本靠這個(gè)表來過濾結(jié)果集行數(shù)）中覆蓋度最高的字段作為分區(qū)字段杈帐，進(jìn)行比較常規(guī)的range分區(qū)体箕，就是按照這個(gè)字段的從小到大，分成1000個(gè)區(qū)間挑童，每個(gè)區(qū)間為一個(gè)分區(qū)表累铅，其他幾張表中，存在這個(gè)字段則進(jìn)行分區(qū)站叼，否則暫時(shí)不分區(qū)娃兽，明確下整個(gè)查詢中使用率最高的兩個(gè)字段一個(gè)是部門id字段，一個(gè)是用戶id尽楔，部門字段為分區(qū)字段投储，用戶id字段作為非分區(qū)表的主要關(guān)聯(lián)字段第练，這樣問題就變成了，在一個(gè)分區(qū)表中的結(jié)果集與其他表或者分區(qū)表的聯(lián)合查詢玛荞，速度果然馬上上來娇掏，起碼一根煙的時(shí)間可以看到結(jié)果，平均在10秒勋眯，

17：已經(jīng)有很大進(jìn)展婴梧，感覺看到了希望，接下來就是怎么把查詢更進(jìn)一步客蹋，再說明下問題塞蹭，部門字段是有層級關(guān)系的，如果單純查詢指定的部門其實(shí)問題不大在2,3秒是可以的讶坯，但是如果去查詢這個(gè)部門的下屬部門然后進(jìn)行統(tǒng)計(jì)浮还，查詢效率直線下降，怎么辦闽巩，？

18：離線計(jì)算担汤，先通過之前準(zhǔn)備的各個(gè)sql生成的結(jié)果存儲在各個(gè)表中(表分區(qū))涎跨，在接受查詢時(shí)，不再通過各個(gè)sql實(shí)時(shí)計(jì)算而是去拿這些表中的數(shù)據(jù)崭歧，減少了很多不復(fù)雜的計(jì)算過程(聚合隅很，排序，函數(shù))率碾，然后用結(jié)果集去關(guān)聯(lián)一些表拉取需要的列數(shù)據(jù)叔营，這個(gè)離線表會每天進(jìn)行一次更新，只對有變更的用戶數(shù)據(jù)進(jìn)行所宰，只掃描前一天更新數(shù)據(jù)绒尊，屬于T_1的增量更新辦法

怎么更新？存儲過程啊仔粥，婴谱，千把行的存儲過程，每天更新一次躯泰，不知道現(xiàn)在維護(hù)這個(gè)代碼的同學(xué)是否還健在谭羔。我也來解釋下為什么要少用存儲過程，其實(shí)存儲過程對于數(shù)據(jù)操作性能很高麦向，畢竟是本地操作瘟裸，但是plsql本是也是種語言，要定義產(chǎn)量诵竭，要定義游標(biāo)话告，要關(guān)閉游標(biāo)兼搏，要做異常處理，要記錄錯(cuò)誤日志超棺，對于開發(fā)人員整體的代碼編寫和維護(hù)成本還是很高的向族，畢竟不是隨便拉一個(gè)人過來就可以噼里啪啦寫存儲過程的，這也導(dǎo)致了儲過程對程序員而言棠绘，可讀性很差導(dǎo)致做完這個(gè)需求后件相，我有段時(shí)間在回憶怎么寫java,,

完成這一步以后，基本已經(jīng)完成了一半氧苍，還要再找更憂的方式比如說部分離線計(jì)算的表（運(yùn)算量比較大）夜矗，換成物化視圖（區(qū)別于視圖），因?yàn)槲锘晥D支持自動更新(采用定時(shí)刷新)让虐，減少了存儲過程的維護(hù)量,而且物化視圖也支持索引紊撕，分區(qū)

1:8：還有什么問題？那就是線上數(shù)據(jù)的重定義

新需求上線時(shí)要保證之前需要分區(qū)的表把分區(qū)做好赡突，但是又不能影響線上的環(huán)境对扶，這個(gè)時(shí)候Oracle強(qiáng)大的地方就要體現(xiàn)了，在線重定義惭缰，mysql可沒有浪南，

此需求在線重定義的過程，其實(shí)就是將需要重定義的表進(jìn)行備份漱受，然后將備份表進(jìn)行分區(qū)络凿，分區(qū)完成后，重建索引(原有全局索引)昂羡，將備份表重命名為線上表名絮记，然后就沒有然后了，

細(xì)心的同學(xué)肯定會跳出來虐先，有bug怨愤，在重定位期間的，線上是有數(shù)據(jù)更新的赴穗，但是備份表拿不到這些數(shù)據(jù)憔四，這就是Oracle在線重定義的奧妙，數(shù)據(jù)的同步般眉，

如果換成mysql應(yīng)該怎么玩了赵？備份表，查詢當(dāng)前最大manxid甸赃，對備份表進(jìn)行分區(qū)操作柿汛，將原表中 id>maxId 的行復(fù)制到備份表，將原表重命名，然后備份表重命名络断，重命名階段會有短時(shí)間的間隔裁替，會有部分業(yè)務(wù)失敗，如果mysql現(xiàn)在又更高級的功能支持這個(gè)操作的貌笨，請指正

基本上這整個(gè)優(yōu)化以及完成弱判，在回頭看下結(jié)果，

首先對于部分表進(jìn)行分區(qū)锥惋，選擇那些表昌腰，當(dāng)時(shí)使用的原則是影響行數(shù)結(jié)果的表，查詢條件中使用最頻繁列膀跌，那些表分區(qū)遭商？包含這個(gè)列的都可以進(jìn)行分區(qū)，具體看業(yè)務(wù)捅伤，建立合適的分區(qū)索引

2.對于未分區(qū)的表劫流，建立合適的索引：組合索引? 函數(shù)索引

3.對于計(jì)算復(fù)雜部分進(jìn)行離線運(yùn)算，提前將查詢結(jié)果集中存放在表中或物化視圖丛忆，建立分區(qū)祠汇，分區(qū)索引

4.維護(hù)工作，存儲過程熄诡，在線重定義座哩，

5.各個(gè)分區(qū)表的后續(xù)維護(hù)，因?yàn)閿?shù)據(jù)時(shí)不斷增長的粮彤，分區(qū)列的數(shù)據(jù)也在增長，定時(shí)的進(jìn)行分區(qū)表的重新分區(qū)（或在建立分區(qū)時(shí)姜骡，將分區(qū)范圍放大导坟，比如目前分區(qū)列數(shù)據(jù)只有1-1000，分十個(gè)區(qū)圈澈，提取假想目前分區(qū)列數(shù)據(jù)時(shí)1-10000惫周，分一百個(gè)區(qū)，保證未來一段時(shí)間康栈，不需要重新分區(qū)操作）

根據(jù)這個(gè)結(jié)果递递，在未分頁情況下，查詢性能基本在1秒以內(nèi)啥么，經(jīng)過壓測登舞，各個(gè)場景在200次請求/S的并發(fā)下，也是在1.1秒內(nèi)悬荣，也是未分區(qū)情況菠秒，功能上線后，分頁情況下基本都是0.5左右氯迂，算是一個(gè)合格的交付

疑問解答践叠，群里很多伙伴言缤，提到緩存的方式，將查詢結(jié)果緩存起來禁灼，這個(gè)方案基本上不可行的管挟，查詢條件是多變的，結(jié)果是隨時(shí)變的弄捕，不太適合這個(gè)場景

復(fù)盤：

· 這是四年前 能拿出來最好的方案僻孝，現(xiàn)在看來總體思路是可以的，但是不夠fashion

如果是我現(xiàn)在要怎么做察藐？

大體思路皮璧，依然對需要的表進(jìn)行分區(qū)，統(tǒng)計(jì)結(jié)果集放在不同表中分飞，直接供查詢使用悴务，但是會放棄物化試圖和存儲過程，物化試圖沒有實(shí)質(zhì)性的提升譬猫，存儲過程成本太高不可控讯檐，總不能線上debug存儲過程，雖然oracle支持類似操作染服。離線更新方式用實(shí)時(shí)計(jì)算方式代替别洪，無論是采用流時(shí)計(jì)算還是看上去像流式計(jì)算，總有辦法達(dá)到目的柳刮，對統(tǒng)計(jì)的結(jié)果集表采用分表的方式挖垛，這些完成后，此類查詢可以獨(dú)立為一個(gè)服務(wù)秉颗，完全脫離了原有系統(tǒng)痢毒，無論從數(shù)據(jù)庫層還是代碼層面，可以獨(dú)立部署發(fā)布蚕甥，不會有后遺癥哪替，

缺點(diǎn)就是，相比之前的方式開發(fā)量更多菇怀，測試成本高凭舶，引入了諸多新功能風(fēng)險(xiǎn)也增加了，實(shí)時(shí)計(jì)算的冪等處理爱沟，數(shù)據(jù)丟失補(bǔ)償?shù)人蝗绱鎯^程高效直接，增加分表呼伸，表路由等問題

優(yōu)勢义屏，摒棄存儲過程將數(shù)據(jù)計(jì)算放入代碼中，可讀性高，采用分表的方式在實(shí)時(shí)計(jì)算方面闽铐，吞吐量也會上升蝶怔，統(tǒng)計(jì)過程更加平穩(wěn)，完全脫離原系統(tǒng)兄墅，不存在耦合問題

具體可以參考下圖

歡迎大家加微信騷擾：treenpool

請持續(xù)關(guān)注踢星，http://treenpool.com國內(nèi)第一款專注于知識體系搭建工具