點贊再看萨咳，養(yǎng)成習慣懊缺，微信搜索【三太子敖丙】關注這個互聯(lián)網(wǎng)茍且偷生的工具人。
本文 GitHub https://github.com/JavaFamily 已收錄培他，有一線大廠面試完整考點鹃两、資料以及我的系列文章。
前言
面試官：敖丙你簡歷上寫了你會數(shù)據(jù)庫調優(yōu)舀凛，你都是怎么調優(yōu)的俊扳？
敖丙：加索引。
面試官：還有么猛遍？
敖丙：沒了馋记。
面試官：我們公司的門你知道在哪里吧，自己走還是我送你懊烤？
哈哈開頭這個場景是我臆想的一個面試場景梯醒，但是大家是不是覺得很真實，每個人的簡歷上但凡寫到了數(shù)據(jù)庫腌紧，都會在后面順便寫一句茸习，會數(shù)據(jù)庫調優(yōu)。
但是問題就來了壁肋，面試官一問到數(shù)據(jù)庫調優(yōu)的逮光，大家就說加索引，除了加索引大家還知道別的么墩划？
或者索引相關的點你全部都知道么涕刚？聚簇索引，非聚簇索引乙帮，普通索引杜漠，唯一索引，change buffer，表鎖驾茴、行鎖盼樟、間隙鎖以及行鎖并發(fā)情況下的最大TPS是多少？還有索引為啥會選擇錯誤锈至？這些大家知道嘛晨缴？
我覺得調優(yōu)能回答的點還是很多很多的，我自己看了《MySQL實戰(zhàn)》、《高性能MySQL》、《丁奇MySQL47講》之后總結了自己去面試回答的一套邏輯贯莺，個人覺得是比較不錯的一套combo绿鸣，這套連招下來曲稼，一般面試官都會暗自對你豎起大拇指，反正我面試的時候基本上就是這一套。
內容就是個人理解的總結，還有書中內容的復述械拍，需要一定的數(shù)據(jù)庫知識，不過我想大家都點進來了装盯，肯定都會了坷虑。
正文
數(shù)據(jù)庫調優(yōu)其實一般情況都是我們的SQL調優(yōu)，SQL的調優(yōu)就可以解決大部分問題了埂奈，當然也不排除SQL執(zhí)行環(huán)節(jié)的調優(yōu)迄损。
我之前在索引和數(shù)據(jù)庫基礎環(huán)節(jié)有介紹過相關的基礎知識，這里就不過多的贅述了挥转，但是數(shù)據(jù)庫的組成可能很多小伙伴都忘記了，那我們再看一遍結構圖吧共屈。
我們所謂的調優(yōu)也就是在绑谣，執(zhí)行器執(zhí)行之前的分析器，優(yōu)化器階段完成的拗引，那我們開發(fā)工作中怎么去調優(yōu)的呢借宵？
帥丙一般在開發(fā)涉及SQL的業(yè)務都會去本地環(huán)境跑一遍SQL，用explain去看一下執(zhí)行計劃矾削，看看分析的結果是否符合自己的預期壤玫，用沒用到相關的索引，然后再去線上環(huán)境跑一下看看執(zhí)行時間（這里只有查詢語句哼凯，修改語句也無法在線上執(zhí)行）欲间。
遇SQL不決explain，但是這里就要說到第一個坑了断部。
排除緩存干擾
因為在MySQL8.0之前我們的數(shù)據(jù)庫是存在緩存這樣的情況的猎贴，我之前就被坑過，因為存在緩存，我發(fā)現(xiàn)我sql怎么執(zhí)行都是很快她渴，當然第一次其實不快但是我沒注意到达址，以至于上線后因為緩存經(jīng)常失效，導致rt（Response time）時高時低趁耗。
后面就發(fā)現(xiàn)了是緩存的問題沉唠，我們在執(zhí)行SQL的時候，記得加上SQL NoCache去跑SQL苛败，這樣跑出來的時間就是真實的查詢時間了满葛。
我說一下為什么緩存會失效，而且是經(jīng)常失效著拭。
如果我們當前的MySQL版本支持緩存而且我們又開啟了緩存纱扭，那每次請求的查詢語句和結果都會以key-value的形式緩存在內存中的，大家也看到我們的結構圖了儡遮，一個請求會先去看緩存是否存在乳蛾，不存在才會走解析器。
緩存失效比較頻繁的原因就是鄙币，只要我們一對表進行更新肃叶，那這個表所有的緩存都會被清空，其實我們很少存在不更新的表十嘿，特別是我之前的電商場景因惭，可能靜態(tài)表可以用到緩存，但是我們都走大數(shù)據(jù)離線分析绩衷，緩存也就沒用了蹦魔。
大家如果是8.0以上的版本就不用擔心這個問題，如果是8.0之下的版本咳燕，記得排除緩存的干擾勿决。
Explain
最開始提到了用執(zhí)行計劃去分析，我想explain是大家SQL調優(yōu)都會回答到的吧招盲。
因為這基本上是寫SQL的必備操作低缩，那我現(xiàn)在問大家一個我去阿里面試被問過的一個問題：explain你記得哪些字段，分別有什么含義曹货？
當時我就回答上來三個咆繁，我默認大家都是有數(shù)據(jù)庫基礎的，所以每個我這里不具體討論每個字段顶籽，怕大家忘記我貼一遍圖大家自己回憶一下玩般。
那我再問大家一下，你們認為統(tǒng)計這個統(tǒng)計的行數(shù)就是完全對的么礼饱？索引一定會走到最優(yōu)索引么壤短？
當然我都這么問了设拟，你們肯定也知道結果了，行數(shù)只是一個接近的數(shù)字久脯，不是完全正確的纳胧，索引也不一定就是走最優(yōu)的，是可能走錯的帘撰。
我的總行數(shù)大概有10W行跑慕，但是我去用explain去分析sql的時候，就會發(fā)現(xiàn)只得到了9.4W摧找，為啥行數(shù)只是個近視值呢核行？
看過基礎章節(jié)的小伙伴都知道，MySQL中數(shù)據(jù)的單位都是頁蹬耘，MySQL又采用了采樣統(tǒng)計的方法芝雪，采樣統(tǒng)計的時候，InnoDB默認會選擇N個數(shù)據(jù)頁综苔，統(tǒng)計這些頁面上的不同值惩系，得到一個平均值，然后乘以這個索引的頁面數(shù)如筛，就得到了這個索引的基數(shù)堡牡。
我們數(shù)據(jù)是一直在變的，所以索引的統(tǒng)計信息也是會變的杨刨，會根據(jù)一個閾值晤柄，重新做統(tǒng)計。
至于MySQL索引可能走錯也很好理解妖胀，如果走A索引要掃描100行芥颈，B所有只要20行，但是他可能選擇走A索引赚抡，你可能會想MySQL是不是有病啊爬坑，其實不是的。
一般走錯都是因為優(yōu)化器在選擇的時候發(fā)現(xiàn)怕品，走A索引沒有額外的代價妇垢，比如走B索引并不能直接拿到我們的值巾遭，還需要回到主鍵索引才可以拿到肉康，多了一次回表的過程，這個也是會被優(yōu)化器考慮進去的灼舍。
他發(fā)現(xiàn)走A索引不需要回表吼和，沒有額外的開銷，所有他選錯了骑素。
如果是上面的統(tǒng)計信息錯了炫乓，那簡單，我們用analyze table tablename 就可以重新統(tǒng)計索引信息了，所以在實踐中末捣，如果你發(fā)現(xiàn)explain的結果預估的rows值跟實際情況差距比較大侠姑，可以采用這個方法來處理。
還有一個方法就是force index強制走正確的索引箩做，或者優(yōu)化SQL莽红，最后實在不行，可以新建索引邦邦，或者刪掉錯誤的索引安吁。
覆蓋索引
上面我提到了，可能需要回表這樣的操作燃辖，那我們怎么能做到不回表呢鬼店？在自己的索引上就查到自己想要的，不要去主鍵索引查了黔龟。
覆蓋索引
如果在我們建立的索引上就已經(jīng)有我們需要的字段妇智，就不需要回表了，在電商里面也是很常見的捌锭，我們需要去商品表通過各種信息查詢到商品id俘陷，id一般都是主鍵，可能sql類似這樣：
select itemId from itemCenter where size between 1 and 6
因為商品id itemId一般都是主鍵观谦，在size索引上肯定會有我們這個值拉盾，這個時候就不需要回主鍵表去查詢id信息了。
由于覆蓋索引可以減少樹的搜索次數(shù)豁状，顯著提升查詢性能捉偏，所以使用覆蓋索引是一個常用的性能優(yōu)化手段。
聯(lián)合索引
還是商品表舉例泻红，我們需要根據(jù)他的名稱夭禽，去查他的庫存，假設這是一個很高頻的查詢請求谊路，你會怎么建立索引呢讹躯？
大家可以思考上面的回表的消耗對SQL進行優(yōu)化。
是的建立一個缠劝，名稱和庫存的聯(lián)合索引潮梯，這樣名稱查出來就可以看到庫存了，不需要查出id之后去回表再查詢庫存了惨恭，聯(lián)合索引在我們開發(fā)過程中也是常見的秉馏，但是并不是可以一直建立的，大家要思考索引占據(jù)的空間脱羡。
剛才我舉的例子其實有點生硬萝究，正常通過商品名稱去查詢庫存的請求是不多的免都，但是也不代表沒有哈，真來了帆竹，難道我們去全表掃描绕娘？
最左匹配原則
大家在寫sql的時候，最好能利用到現(xiàn)有的SQL最大化利用栽连，像上面的場景业舍，如果利用一個模糊查詢 itemname like ’敖丙%‘，這樣還是能利用到這個索引的升酣，而且如果有這樣的聯(lián)合索引舷暮，大家也沒必要去新建一個商品名稱單獨的索引了。
很多時候我們索引可能沒建對噩茄，那你調整一下順序下面，可能就可以優(yōu)化到整個SQL了。
索引下推
你已經(jīng)知道了前綴索引規(guī)則绩聘，那我就說一個官方幫我們優(yōu)化的東西沥割，索引下推。
select * from itemcenter where name like '敖%' and size=22 and age = 20;
所以這個語句在搜索索引樹的時候凿菩，只能用 “敖”机杜，找到第一個滿足條件的記錄ID1，當然衅谷，這還不錯椒拗，總比全表掃描要好。
然后呢获黔？
當然是判斷其他條件是否滿足蚀苛，比如size。
在MySQL 5.6之前玷氏，只能從ID1開始一個個回表堵未，到主鍵索引上找出數(shù)據(jù)行，再對比字段值盏触。
而MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)渗蟹， 可以在索引遍歷過程中，對索引中包含的字段先做判斷赞辩，直接過濾掉不滿足條件的記錄雌芽，減少回表次數(shù)。
唯一索引普通索引選擇難題
這個在我的面試視頻里面其實問了好幾次了诗宣，核心是需要回答到change buffer膘怕，那change buffer又是個什么東西呢想诅？
當需要更新一個數(shù)據(jù)頁時召庞，如果數(shù)據(jù)頁在內存中就直接更新岛心，而如果這個數(shù)據(jù)頁還沒有在內存中的話，在不影響數(shù)據(jù)一致性的前提下篮灼，InooDB會將這些更新操作緩存在change buffer中忘古，這樣就不需要從磁盤中讀入這個數(shù)據(jù)頁了。
在下次查詢需要訪問這個數(shù)據(jù)頁的時候诅诱，將數(shù)據(jù)頁讀入內存髓堪，然后執(zhí)行change buffer中與這個頁有關的操作，通過這種方式就能保證這個數(shù)據(jù)邏輯的正確性娘荡。
需要說明的是干旁，雖然名字叫作change buffer，實際上它是可以持久化的數(shù)據(jù)炮沐。也就是說争群，change buffer在內存中有拷貝，也會被寫入到磁盤上大年。
將change buffer中的操作應用到原數(shù)據(jù)頁换薄，得到最新結果的過程稱為merge。
除了訪問這個數(shù)據(jù)頁會觸發(fā)merge外翔试，系統(tǒng)有后臺線程會定期merge轻要。在數(shù)據(jù)庫正常關閉（shutdown）的過程中，也會執(zhí)行merge操作垦缅。
顯然冲泥，如果能夠將更新操作先記錄在change buffer，減少讀磁盤壁涎，語句的執(zhí)行速度會得到明顯的提升柏蘑。而且，數(shù)據(jù)讀入內存是需要占用buffer pool的粹庞，所以這種方式還能夠避免占用內存咳焚，提高內存利用率
那么，什么條件下可以使用change buffer呢庞溜？
對于唯一索引來說革半，所有的更新操作都要先判斷這個操作是否違反唯一性約束。
要判斷表中是否存在這個數(shù)據(jù)流码，而這必須要將數(shù)據(jù)頁讀入內存才能判斷又官，如果都已經(jīng)讀入到內存了，那直接更新內存會更快漫试，就沒必要使用change buffer了六敬。
因此，唯一索引的更新就不能使用change buffer驾荣，實際上也只有普通索引可以使用外构。
change buffer用的是buffer pool里的內存普泡，因此不能無限增大，change buffer的大小审编，可以通過參數(shù)innodb_change_buffer_max_size來動態(tài)設置撼班，這個參數(shù)設置為50的時候，表示change buffer的大小最多只能占用buffer pool的50%垒酬。
將數(shù)據(jù)從磁盤讀入內存涉及隨機IO的訪問砰嘁，是數(shù)據(jù)庫里面成本最高的操作之一，change buffer因為減少了隨機磁盤訪問勘究，所以對更新性能的提升是會很明顯的矮湘。
change buffer的使用場景
因為merge的時候是真正進行數(shù)據(jù)更新的時刻，而change buffer的主要目的就是將記錄的變更動作緩存下來口糕，所以在一個數(shù)據(jù)頁做merge之前板祝，change buffer記錄的變更越多（也就是這個頁面上要更新的次數(shù)越多），收益就越大走净。
因此券时，對于寫多讀少的業(yè)務來說，頁面在寫完以后馬上被訪問到的概率比較小伏伯，此時change buffer的使用效果最好橘洞，這種業(yè)務模型常見的就是賬單類、日志類的系統(tǒng)说搅。
反過來炸枣，假設一個業(yè)務的更新模式是寫入之后馬上會做查詢，那么即使?jié)M足了條件弄唧，將更新先記錄在change buffer适肠，但之后由于馬上要訪問這個數(shù)據(jù)頁，會立即觸發(fā)merge過程候引。這樣隨機訪問IO的次數(shù)不會減少侯养，反而增加了change buffer的維護代價，所以澄干，對于這種業(yè)務模式來說逛揩，change buffer反而起到了副作用。
前綴索引
我們存在郵箱作為用戶名的情況麸俘，每個人的郵箱都是不一樣的辩稽，那我們是不是可以在郵箱上建立索引，但是郵箱這么長从媚，我們怎么去建立索引呢逞泄？
MySQL是支持前綴索引的，也就是說，你可以定義字符串的一部分作為索引喷众。默認地各谚，如果你創(chuàng)建索引的語句不指定前綴長度，那么索引就會包含整個字符串侮腹。
我們是否可以建立一個區(qū)分度很高的前綴索引，達到優(yōu)化和節(jié)約空間的目的呢稻励？
使用前綴索引父阻，定義好長度，就可以做到既節(jié)省空間望抽，又不用額外增加太多的查詢成本加矛。
上面說過覆蓋索引了，覆蓋索引是不需要回表的煤篙，但是前綴索引斟览，即使你的聯(lián)合索引已經(jīng)包涵了相關信息，他還是會回表辑奈，因為他不確定你到底是不是一個完整的信息苛茂，就算你是www.aobing@mogu.com一個完整的郵箱去查詢，他還是不知道你是否是完整的鸠窗，所以他需要回表去判斷一下妓羊。
下面這個也是我在阿里面試面試官問過我的，很長的字段稍计，想做索引我們怎么去優(yōu)化他呢躁绸？
因為存在一個磁盤占用的問題，索引選取的越長臣嚣，占用的磁盤空間就越大净刮，相同的數(shù)據(jù)頁能放下的索引值就越少，搜索的效率也就會越低硅则。
我當時就回答了一個hash淹父，把字段hash為另外一個字段存起來，每次校驗hash就好了怎虫，hash的索引也不大弹灭。
我們都知道只要區(qū)分度過高，都可以揪垄，那我們可以采用倒序穷吮，或者刪減字符串這樣的情況去建立我們自己的區(qū)分度，不過大家需要注意的是饥努，調用函數(shù)也是一次開銷喲捡鱼，這點當時沒注意。
就比如本來是www.aobing@qq,com 其實前面的www.基本上是沒任何區(qū)分度的酷愧，所有人的郵箱都是這么開頭的驾诈，你一搜一大堆出來缠诅，放在索引還浪費內存，你可以substring()函數(shù)截取掉前面的乍迄，然后建立索引管引。
我們所有人的身份證都是區(qū)域開頭的，同區(qū)域的人很多闯两，那怎么做良好的區(qū)分呢褥伴？REVERSE（）函數(shù)翻轉一下，區(qū)分度可能就高了漾狼。
這些操作都用到了函數(shù)重慢，我就說一下函數(shù)的坑。
條件字段函數(shù)操作
日常開發(fā)過程中逊躁，大家經(jīng)常對很多字段進行函數(shù)操作似踱，如果對日期字段操作，浮點字符操作等等稽煤，大家需要注意的是核芽，如果對字段做了函數(shù)計算，就用不上索引了酵熙，這是MySQL的規(guī)定狞洋。
對索引字段做函數(shù)操作，可能會破壞索引值的有序性绿店，因此優(yōu)化器就決定放棄走樹搜索功能吉懊。
需要注意的是，優(yōu)化器并不是要放棄使用這個索引假勿。
這個時候大家可以用一些取巧的方法借嗽，比如 select * from tradelog where id + 1 = 10000 就走不上索引，select * from tradelog where id = 9999就可以转培。
隱式類型轉換
select * from t where id = 1
如果id是字符類型的恶导，1是數(shù)字類型的，你用explain會發(fā)現(xiàn)走了全表掃描浸须，根本用不上索引惨寿，為啥呢？
因為MySQL底層會對你的比較進行轉換删窒，相當于加了 CAST( id AS signed int) 這樣的一個函數(shù)裂垦，上面說過函數(shù)會導致走不上索引。
隱式字符編碼轉換
還是一樣的問題肌索，如果兩個表的字符集不一樣蕉拢，一個是utf8mb4，一個是utf8，因為utf8mb4是utf8的超集晕换，所以一旦兩個字符比較午乓，就會轉換為utf8mb4再比較。
轉換的過程相當于加了CONVERT(id USING utf8mb4)函數(shù)闸准，那又回到上面的問題了益愈，用到函數(shù)就用不上索引了。
還有大家一會可能會遇到mysql突然卡頓的情況夷家，那可能是MySQLflush了蒸其。
flush
redo log大家都知道，也就是我們對數(shù)據(jù)庫操作的日志瘾英，他是在內存中的枣接，每次操作一旦寫了redo log就會立馬返回結果颂暇，但是這個redo log總會找個時間去更新到磁盤缺谴，這個操作就是flush。
在更新之前耳鸯，當內存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內容不一致的時候湿蛔，我們稱這個內存頁為“臟頁”。
內存數(shù)據(jù)寫入到磁盤后县爬，內存和磁盤上的數(shù)據(jù)頁的內容就一致了阳啥，稱為“干凈頁“。
那什么時候會flush呢财喳？
InnoDB的redo log寫滿了察迟，這時候系統(tǒng)會停止所有更新操作，把checkpoint往前推進耳高，redo log留出空間可以繼續(xù)寫扎瓶。
系統(tǒng)內存不足，當需要新的內存頁泌枪，而內存不夠用的時候概荷，就要淘汰一些數(shù)據(jù)頁，空出內存給別的數(shù)據(jù)頁使用碌燕。如果淘汰的是“臟頁”误证，就要先將臟頁寫到磁盤。
你一定會說修壕，這時候難道不能直接把內存淘汰掉愈捅，下次需要請求的時候，從磁盤讀入數(shù)據(jù)頁慈鸠，然后拿redo log出來應用不就行了改鲫？
這里其實是從性能考慮的，如果刷臟頁一定會寫盤，就保證了每個數(shù)據(jù)頁有兩種狀態(tài)：
- 一種是內存里存在像棘，內存里就肯定是正確的結果稽亏，直接返回；
- 另一種是內存里沒有數(shù)據(jù)缕题，就可以肯定數(shù)據(jù)文件上是正確的結果截歉，讀入內存后返回。
這樣的效率最高烟零。
MySQL認為系統(tǒng)“空閑”的時候瘪松，只要有機會就刷一點“臟頁”。
MySQL正常關閉锨阿，這時候宵睦，MySQL會把內存的臟頁都flush到磁盤上，這樣下次MySQL啟動的時候墅诡，就可以直接從磁盤上讀數(shù)據(jù)壳嚎，啟動速度會很快。
那我們怎么做才能把握flush的時機呢末早？
Innodb刷臟頁控制策略烟馅，我們每個電腦主機的io能力是不一樣的，你要正確地告訴InnoDB所在主機的IO能力然磷，這樣InnoDB才能知道需要全力刷臟頁的時候郑趁，可以刷多快。
這就要用到innodb_io_capacity這個參數(shù)了姿搜，它會告訴InnoDB你的磁盤能力寡润，這個值建議設置成磁盤的IOPS，磁盤的IOPS可以通過fio這個工具來測試舅柜。
正確地設置innodb_io_capacity參數(shù)梭纹，可以有效的解決這個問題。
這中間有個有意思的點业踢，刷臟頁的時候栗柒，旁邊如果也是臟頁，會一起刷掉的知举，并且如果周圍還有臟頁瞬沦，這個連帶責任制會一直蔓延，這種情況其實在機械硬盤時代比較好雇锡，一次IO就解決了所有問題逛钻，
但是現(xiàn)在都是固態(tài)硬盤了，innodb_flush_neighbors=0這個參數(shù)可以不產(chǎn)生連帶制锰提，在MySQL 8.0中曙痘，innodb_flush_neighbors參數(shù)的默認值已經(jīng)是0了芳悲。
資料參考：《MySQL實戰(zhàn)》、《高性能MySQL》边坤、《丁奇MySQL47講》
總結
在本文中我提到了以下知識點：
應該還不算全名扛，行鎖、表鎖茧痒、間隙鎖肮韧、同步場景等等都沒怎么提到，因為他們的場景比較復雜旺订，每種都可以單獨開一篇了弄企，丁奇的MySQL里面算是很全了，還有就是高性能MySQL大家可以展開看看区拳，要是懶也可以等我總結拘领。
每個點我也沒多仔細的講解，主要是篇幅原因樱调，其實每個點在MySQL相關書籍都是很多篇幅才介紹完的约素，我就做個總結，對具體的概念不了解可以用搜索引擎查詢相關概念本涕，不過我想我說得還算通俗易懂业汰。
本文敖丙也就肝了一個多星期吧伙窃，主要是知識點的梳理菩颖，因為我也忘記得差不多了，我又回頭看了一遍为障，然后總結了一下晦闰，還有之前的筆記還在，本文我還是不開贊賞鳍怨，大家覺得可以點個在看就好了呻右，么么。
我是敖丙鞋喇，一個在互聯(lián)網(wǎng)茍且偷生的程序猿声滥。
你知道的越多窒升，你不知道的越多宅楞，人才們的 【三連】 就是丙丙創(chuàng)作的最大動力，我們下期見榨惠！
注：如果本篇博客有任何錯誤和建議罐韩，歡迎人才們留言憾赁！
文章持續(xù)更新，可以微信搜索「 三太子敖丙 」第一時間閱讀散吵，回復【資料】有我準備的一線大廠面試資料和簡歷模板龙考，本文 GitHub https://github.com/JavaFamily 已經(jīng)收錄蟆肆，有大廠面試完整考點，歡迎Star晦款。
