小茵：要不你來講講你們對MySQL是怎么調(diào)優(yōu)的缩抡？

小奧：哇，這命題很大阿…我認為包颁，對于開發(fā)者而言缝其，對MySQL的調(diào)優(yōu)重點一般是在「開發(fā)規(guī)范」、[數(shù)據(jù)庫索引]又或者說解決線上慢查詢上徘六。

小奧：而對于MySQL內(nèi)部的[參數(shù)調(diào)優(yōu)]，由專業(yè)的DBA來搞榴都。

小茵：扯了這么多待锈，你就是想表達你不會MySQL參數(shù)調(diào)優(yōu)，對吧

小奧：草嘴高，被發(fā)現(xiàn)了竿音。

小茵：那你來聊聊你們平時開發(fā)的規(guī)范和索引這塊和屎，平時是怎么樣的吧。

小奧：嗯春瞬，首先柴信，我們在生產(chǎn)環(huán)境下，創(chuàng)建[數(shù)據(jù)庫表]宽气，都是在工單系統(tǒng)下完成的（那就自然需要DBA審批）随常。如果在創(chuàng)建表時檢測到?jīng)]有創(chuàng)建索引，那就會直接提示warning（：

image.png

小奧：理論上來說萄涯，如果表有一定的數(shù)據(jù)量绪氛，那就應該要創(chuàng)建對應的索引。從數(shù)據(jù)庫查詢數(shù)據(jù)需要注意的地方還是蠻多的涝影，其中很多都是平時積累來的枣察。比如說：

小奧：1. 是否能使用[覆蓋索引]，減少「回表」所消耗的時間燃逻。意味著序目，我們在select 的時候，一定要指明對應的列伯襟，而不是select *

小奧：2. 考慮是否組建[聯(lián)合索引]猿涨，如果組建「聯(lián)合索引」，盡量將區(qū)分度最高的放在最左邊逗旁，并且需要考慮[最左匹配原則]

小奧：3.對索引進行函數(shù)操作或者表達式計算會導致索引失效

小奧：4.利用[子查詢]優(yōu)化超多分頁場景嘿辟。比如 limit offset , n 在MySQL是獲取 offset + n的記錄，再返回n條片效。而利用子查詢則是查出n條红伦，通過ID檢索對應的記錄出來，提高查詢效率淀衣。

小茵：嗯…

小奧：5.通過[explain命令]來查看SQL的執(zhí)行計劃昙读，看看自己寫的SQL是否走了索引，走了什么索引膨桥。通過[show profile]來查看SQL對系統(tǒng)資源的損耗情況（不過一般還是比較少用到的）

小奧：6.在開啟事務后蛮浑，在事務內(nèi)盡可能只操作數(shù)據(jù)庫，并有意識地減少鎖的持有時間（比如在事務內(nèi)需要插入&&修改數(shù)據(jù)只嚣，那可以先插入后修改沮稚。因為修改是更新操作，會加行鎖册舞。如果先更新蕴掏，那并發(fā)下可能會導致多個事務的請求等待[行鎖]釋放）

image.png

小茵：嗯，你提到了事務，之前也講過了事務的隔離級別嘛盛杰，那你線上用的是什么隔離級別挽荡？

小奧：嗯，我們這邊用的是Read Commit（讀已提交）即供，MySQL默認用的是Repeatable read（可重復讀）定拟。選用什么[隔離]級別，主要看應用場景嘛逗嫡，因為隔離級別越低青自，事務并發(fā)性能越高。

小奧：（一般互聯(lián)網(wǎng)公司都選擇Read Commit作為主要的隔離級別）

小奧：像Repeatable read（可重復讀）隔離級別祸穷，就有可能因為[間隙鎖]導致的死鎖問題性穿。

小奧：但可能你已經(jīng)知道，MySQL默認的隔離級別為Repeatable read雷滚。很大一部分原因是在最開始的時候需曾，MySQL的binlog沒有[row模式]，在read commit隔離級別下會存在「主從數(shù)據(jù)不一致」的問題

小奧：[binlog]記錄了數(shù)據(jù)庫表結構和表數(shù)據(jù)「變更」祈远，比如update/delete/insert/truncate/create呆万。在MySQL中，主從同步實際上就是應用了binlog來實現(xiàn)的（：

小奧：有了該歷史原因车份，所以MySQL就將默認的隔離級別設置為Repeatable read

image.png

小茵：嗯谋减，那我順便想問下，你們遇到過類似的問題嗎：即便走對了索引扫沼，[線上查詢]還是慢出爹。

小奧：嗯嗯，當然遇到過了

小茵：那你們是怎么做的缎除？

小奧：如果走對了索引严就，但查詢還是慢，那一般來說就是表的數(shù)據(jù)量實在是太大了器罐。

小奧：首先梢为，考慮能不能把「舊的數(shù)據(jù)」給”刪掉”，對于我們公司而言轰坊，我們都會把數(shù)據(jù)同步到Hive铸董，說明已經(jīng)[離線存儲]了一份了。

小奧：那如果「舊的數(shù)據(jù)」已經(jīng)沒有查詢的業(yè)務了肴沫，那最簡單的辦法肯定是”刪掉”部分數(shù)據(jù)咯粟害。數(shù)據(jù)量降低了，那自然颤芬，檢索速度就快了…

小茵：嗯我磁，但一般不會刪的

小奧：沒錯孽文，只有極少部分業(yè)務可以刪掉數(shù)據(jù)（：

小奧：隨后，就考慮另一種情況夺艰，能不能在查詢之前，直接走一層[緩存]（Redis）沉衣。

小奧：而走緩存的話郁副，又要看業(yè)務能不能忍受讀取的「非真正實時」的數(shù)據(jù)（畢竟Redis和MySQL的數(shù)據(jù)一致性需要保證），如果查詢條件相對復雜且多變的話（涉及各種group by 和sum）豌习，那走緩存也不是一種好的辦法存谎，維護起來就不方便了…

小奧：再看看是不是有[字符串]檢索的場景導致查詢低效，如果是的話肥隆，可以考慮把表的數(shù)據(jù)導入至Elasticsearch類的搜索引擎既荚，后續(xù)的線上查詢就直接走Elasticsearch了。

小奧：MySQL->Elasticsearch需要有對應的[同步程序] (一般就是監(jiān)聽MySQL的binlog栋艳，解析binlog后導入到Elasticsearch)

小奧：如果還不是的話恰聘，那考慮要不要根據(jù)查詢條件的維度，做相對應的[聚合表]吸占，線上的請求就查詢聚合表的數(shù)據(jù)晴叨，不走原表。

小奧：比如矾屯，用戶下單后兼蕊，有一份訂單明細，而訂單明細表的量級太大件蚕。但在產(chǎn)品側(前臺)透出的查詢功能是以「天」[維度]來展示的孙技，那就可以將每個用戶的每天數(shù)據(jù)聚合起來，在聚合表就是一個用戶一天只有一條匯總后的數(shù)據(jù)排作。

小奧：查詢走聚合后的表牵啦，那速度肯定杠杠的（聚合后的表數(shù)據(jù)量肯定比原始表要少很多）

小奧：思路大致的就是「以空間換時間」，相同的數(shù)據(jù)換別的地方也存儲一份纽绍，提高查詢效率

image.png

小茵：那我還想問下蕾久，除了讀之外，寫性能同樣有瓶頸拌夏，怎么辦僧著？

小奧：你說到這個，我就不困了障簿。

小奧：如果在MySQL讀寫都有瓶頸盹愚，那首先看下目前MySQL的架構是怎么樣的。

小奧：如果是單庫的站故，那是不是可以考慮升級至主從架構皆怕，實現(xiàn)[讀寫分離] 毅舆。

小奧：簡單理解就是：主庫接收寫請求，從庫接收讀請求愈腾。從庫的數(shù)據(jù)由主庫發(fā)送的binlog進而更新憋活，實現(xiàn)主從數(shù)據(jù)一致（在一般場景下，主從的數(shù)據(jù)是通過異步來保證最終一致性的）

image.png

小茵：嗯…

小奧：如果在主從架構下虱黄，讀寫仍存在瓶頸悦即，那就要考慮是否要[分庫分表]了

小奧：至少在我前公司的架構下，業(yè)務是區(qū)分的橱乱。流量有流量數(shù)據(jù)庫辜梳，廣告有廣告的數(shù)據(jù)庫，商品有商品的數(shù)據(jù)庫泳叠。所以作瞄，我這里講的分庫分表的含義是：在原來的某個庫的某個表進而拆分。

小奧：比如危纫，現(xiàn)在我有一張業(yè)務訂單表宗挥，這張訂單表在廣告庫中，假定這張業(yè)務訂單表已經(jīng)有1億數(shù)據(jù)量了叶摄，現(xiàn)在我要分庫分表

小奧：那就會將這張表的數(shù)據(jù)分至多個廣告庫以及多張表中（：

小奧：分庫分表的最明顯的好處就是把請求進行均攤（本來單個庫單個表有一億的數(shù)據(jù)属韧，那假設我分開8個庫，那每個庫1200+W的數(shù)據(jù)量蛤吓，每個庫下分8張表宵喂，那每張表就150W的數(shù)據(jù)量）。

image.png

小茵：你們是以什么來作為[分庫鍵]的会傲？

小奧：按照我們這邊的經(jīng)驗锅棕，一般來說是按照userId的（因為按照用戶的維度查詢比較多），如果要按照其他的維度進行查詢淌山，那還是參照上面的的思路（以空間換時間）裸燎。

小茵：那分庫分表后的ID是怎么生成的？

小奧：這就涉及到[分布式ID]生成的方式了泼疑，思路有很多德绿。有借助MySQL自增的，有借助Redis自增的退渗，有基于[雪花算法]自增的移稳。具體使用哪種方式，那就看公司的技術棧了会油，一般使用Redis和基于「雪花算法」實現(xiàn)用得比較多个粱。

小奧：至于為什么強調(diào)[自增]（還是跟索引是有序有關，前面已經(jīng)講過了翻翩，你應該還記得）

image.png

小茵：嗯都许，那如果我要分庫分表了稻薇，遷移的過程是怎么樣的呢

小奧：我們一般采取[雙寫]的方式來進行遷移，大致步驟就是：

小奧：一胶征、增量的消息各自往新表和舊表寫一份

小奧：二塞椎、將舊表的數(shù)據(jù)遷移至新庫

小奧：三、遲早新表的數(shù)據(jù)都會追得上舊表（在某個[節(jié)點]上數(shù)據(jù)是同步的）

小奧：四睛低、校驗新表和老表的數(shù)據(jù)是否正常（主要看能不能對得上）

小奧：五忱屑、開啟[雙讀]（一部分流量走新表，一部分流量走老表）暇昂，相當于灰度上線的過程

小奧：六、讀流量全部切新表伴嗡，停止老表的寫入

小奧：七急波、提前準備回滾機制，臨時切換失敗能恢復正常業(yè)務以及有修數(shù)據(jù)的相關程序瘪校。

image.png

小茵：嗯…今天就到這吧

本文總結：

• 數(shù)據(jù)庫表存在一定數(shù)據(jù)量澄暮，就需要有對應的索引
• 發(fā)現(xiàn)慢查詢時，檢查是否走對索引阱扬，是否能用更好的索引進行[優(yōu)化]查詢速度泣懊，查看使用索引的姿勢有沒有問題
• 當索引解決不了慢查詢時，一般由于業(yè)務表的數(shù)據(jù)量太大導致麻惶，利用空間換時間的思想
• 當讀寫性能均遇到瓶頸時馍刮，先考慮能否升級數(shù)據(jù)庫架構即可解決問題，若不能則需要考慮分庫分表
• 分庫分表雖然能解決掉讀寫瓶頸窃蹋，但同時會帶來各種問題卡啰，需要提前調(diào)研解決方案和踩坑