25.有哪些方式優(yōu)化慢SQL扔仓？

慢SQL的優(yōu)化褐奥，主要從兩個(gè)方面考慮，SQL語(yǔ)句本身的優(yōu)化翘簇，以及數(shù)據(jù)庫(kù)設(shè)計(jì)的優(yōu)化撬码。

image.png

SQL優(yōu)化

避免不必要的列

這個(gè)是老生常談，但還是經(jīng)常會(huì)出的情況版保，SQL查詢的時(shí)候呜笑，應(yīng)該只查詢需要的列，而不要包含額外的列彻犁，像slect * 這種寫(xiě)法應(yīng)該盡量避免叫胁。

分頁(yè)優(yōu)化

在數(shù)據(jù)量比較大，分頁(yè)比較深的情況下汞幢，需要考慮分頁(yè)的優(yōu)化驼鹅。

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

優(yōu)化方案：

• 延遲關(guān)聯(lián)先通過(guò)where條件提取出主鍵，在將該表與原數(shù)據(jù)表關(guān)聯(lián)，通過(guò)主鍵id提取數(shù)據(jù)行谤民，而不是通過(guò)原來(lái)的二級(jí)索引提取數(shù)據(jù)行例如：select a.* from table a,
  (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
  where a.id = b.id
• 書(shū)簽方式書(shū)簽方式就是找到limit第一個(gè)參數(shù)對(duì)應(yīng)的主鍵值堰酿，根據(jù)這個(gè)主鍵值再去過(guò)濾并limit例如：

  select * from table where id >
  (select * from table where type = 2 and level = 9 order by id asc limit 190

索引優(yōu)化

合理地設(shè)計(jì)和使用索引，是優(yōu)化慢SQL的利器张足。

利用覆蓋索引

InnoDB使用非主鍵索引查詢數(shù)據(jù)時(shí)會(huì)回表触创，但是如果索引的葉節(jié)點(diǎn)中已經(jīng)包含要查詢的字段，那它沒(méi)有必要再回表查詢了为牍，這就叫覆蓋索引

例如對(duì)于如下查詢：

select name from test where city='上海'

我們將被查詢的字段建立到聯(lián)合索引中哼绑，這樣查詢結(jié)果就可以直接從索引中獲取

alter table test add index idx_city_name (city, name);

低版本避免使用or查詢

在 MySQL 5.0 之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來(lái)替代碉咆，因?yàn)樵缙诘?MySQL 版本使用 or 查詢可能會(huì)導(dǎo)致索引失效抖韩，高版本引入了索引合并，解決了這個(gè)問(wèn)題疫铜。

避免使用 != 或者 <> 操作符

SQL中茂浮，不等于操作符會(huì)導(dǎo)致查詢引擎放棄查詢索引，引起全表掃描壳咕，即使比較的字段上有索引

解決方法：通過(guò)把不等于操作符改成or席揽，可以使用索引，避免全表掃描

例如谓厘，把column<>’aaa’幌羞，改成column>’aaa’ or column<’aaa’，就可以使用索引了

適當(dāng)使用前綴索引

適當(dāng)?shù)厥褂们熬Y所云竟稳，可以降低索引的空間占用属桦，提高索引的查詢效率。

比如他爸，郵箱的后綴都是固定的“@xxx.com”聂宾，那么類(lèi)似這種后面幾位為固定值的字段就非常適合定義為前綴索引

alter table test add index index2(email(6));

PS:需要注意的是，前綴索引也存在缺點(diǎn)诊笤，MySQL無(wú)法利用前綴索引做order by和group by 操作亏吝，也無(wú)法作為覆蓋索引

避免列上函數(shù)運(yùn)算

要避免在列字段上進(jìn)行算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則可能會(huì)導(dǎo)致存儲(chǔ)引擎無(wú)法正確使用索引盏混，從而影響了查詢的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正確使用聯(lián)合索引

使用聯(lián)合索引的時(shí)候，注意最左匹配原則惜论。

JOIN優(yōu)化

優(yōu)化子查詢

盡量使用 Join 語(yǔ)句來(lái)替代子查詢许赃，因?yàn)樽硬樵兪乔短撞樵儯短撞樵儠?huì)新創(chuàng)建一張臨時(shí)表馆类，而臨時(shí)表的創(chuàng)建與銷(xiāo)毀會(huì)占用一定的系統(tǒng)資源以及花費(fèi)一定的時(shí)間混聊，同時(shí)對(duì)于返回結(jié)果集比較大的子查詢，其對(duì)查詢性能的影響更大

小表驅(qū)動(dòng)大表

關(guān)聯(lián)查詢的時(shí)候要拿小表去驅(qū)動(dòng)大表乾巧，因?yàn)殛P(guān)聯(lián)的時(shí)候句喜，MySQL內(nèi)部會(huì)遍歷驅(qū)動(dòng)表预愤，再去連接被驅(qū)動(dòng)表。

比如left join咳胃，左表就是驅(qū)動(dòng)表植康，A表小于B表，建立連接的次數(shù)就少展懈，查詢速度就被加快了销睁。

 select name from A left join B ;

適當(dāng)增加冗余字段

增加冗余字段可以減少大量的連表查詢，因?yàn)槎鄰埍淼倪B表查詢性能很低存崖，所有可以適當(dāng)?shù)脑黾尤哂嘧侄味臣牵詼p少多張表的關(guān)聯(lián)查詢，這是以空間換時(shí)間的優(yōu)化策略

避免使用JOIN關(guān)聯(lián)太多的表

《阿里巴巴Java開(kāi)發(fā)手冊(cè)》規(guī)定不要join超過(guò)三張表来惧，第一join太多降低查詢的速度冗栗，第二join的buffer會(huì)占用更多的內(nèi)存。

如果不可避免要join多張表供搀，可以考慮使用數(shù)據(jù)異構(gòu)的方式異構(gòu)到ES中查詢隅居。

排序優(yōu)化

利用索引掃描做排序

MySQL有兩種方式生成有序結(jié)果：其一是對(duì)結(jié)果集進(jìn)行排序的操作，其二是按照索引順序掃描得出的結(jié)果自然是有序的

但是如果索引不能覆蓋查詢所需列趁曼，就不得不每掃描一條記錄回表查詢一次军浆，這個(gè)讀操作是隨機(jī)IO，通常會(huì)比順序全表掃描還慢

因此挡闰，在設(shè)計(jì)索引時(shí)乒融，盡可能使用同一個(gè)索引既滿足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣時(shí)摄悯，才能夠使用索引來(lái)對(duì)結(jié)果做排序

UNION優(yōu)化

條件下推

MySQL處理union的策略是先創(chuàng)建臨時(shí)表赞季，然后將各個(gè)查詢結(jié)果填充到臨時(shí)表中最后再來(lái)做查詢，很多優(yōu)化策略在union查詢中都會(huì)失效奢驯，因?yàn)樗鼰o(wú)法利用索引

最好手工將where申钩、limit等子句下推到union的各個(gè)子查詢中，以便優(yōu)化器可以充分利用這些條件進(jìn)行優(yōu)化

此外瘪阁，除非確實(shí)需要服務(wù)器去重撒遣，一定要使用union all，如果不加all關(guān)鍵字管跺，MySQL會(huì)給臨時(shí)表加上distinct選項(xiàng)义黎，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表做唯一性檢查，代價(jià)很高豁跑。

26.怎么看執(zhí)行計(jì)劃（explain）廉涕，如何理解其中各個(gè)字段的含義？

explain是sql優(yōu)化的利器，除了優(yōu)化慢sql狐蜕，平時(shí)的sql編寫(xiě)南蓬，也應(yīng)該先explain白对，查看一下執(zhí)行計(jì)劃衬浑，看看是否還有優(yōu)化的空間笑陈。

直接在 select 語(yǔ)句之前增加explain 關(guān)鍵字，就會(huì)返回執(zhí)行計(jì)劃的信息湃累。

image.png

explain

image.png

explain

1. id 列：MySQL會(huì)為每個(gè)select語(yǔ)句分配一個(gè)唯一的id值
2. select_type 列勃救，查詢的類(lèi)型，根據(jù)關(guān)聯(lián)治力、union蒙秒、子查詢等等分類(lèi)，常見(jiàn)的查詢類(lèi)型有SIMPLE宵统、PRIMARY晕讲。
3. table 列：表示 explain 的一行正在訪問(wèn)哪個(gè)表。
4. type 列：最重要的列之一马澈。表示關(guān)聯(lián)類(lèi)型或訪問(wèn)類(lèi)型瓢省，即 MySQL 決定如何查找表中的行。性能從最優(yōu)到最差分別為：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
5. systemsystem：當(dāng)表僅有一行記錄時(shí)(系統(tǒng)表)痊班，數(shù)據(jù)量很少勤婚，往往不需要進(jìn)行磁盤(pán)IO，速度非车臃ィ快
6. constconst：表示查詢時(shí)命中 primary key 主鍵或者 unique 唯一索引馒胆，或者被連接的部分是一個(gè)常量(const)值。這類(lèi)掃描效率極高凝果，返回?cái)?shù)據(jù)量少祝迂，速度非常快器净。
7. eq_refeq_ref：查詢時(shí)命中主鍵primary key 或者 unique key索引型雳， type 就是 eq_ref。
8. ref_or_nullref_or_null：這種連接類(lèi)型類(lèi)似于 ref山害，區(qū)別在于 MySQL會(huì)額外搜索包含NULL值的行纠俭。
9. index_mergeindex_merge：使用了索引合并優(yōu)化方法，查詢使用了兩個(gè)以上的索引浪慌。
10. unique_subqueryunique_subquery：替換下面的 IN子查詢柑晒，子查詢返回不重復(fù)的集合。
11. index_subqueryindex_subquery：區(qū)別于unique_subquery眷射，用于非唯一索引，可以返回重復(fù)值。
12. rangerange：使用索引選擇行妖碉，僅檢索給定范圍內(nèi)的行涌庭。簡(jiǎn)單點(diǎn)說(shuō)就是針對(duì)一個(gè)有索引的字段，給定范圍檢索數(shù)據(jù)欧宜。在where語(yǔ)句中使用 bettween...and坐榆、<、>冗茸、<=席镀、in 等條件查詢 type 都是 range。
13. indexindex：Index 與ALL 其實(shí)都是讀全表夏漱，區(qū)別在于index是遍歷索引樹(shù)讀取豪诲，而ALL是從硬盤(pán)中讀取。
14. ALL就不用多說(shuō)了挂绰，全表掃描屎篱。
15. possible_keys 列：顯示查詢可能使用哪些索引來(lái)查找，使用索引優(yōu)化sql的時(shí)候比較重要葵蒂。
16. key 列：這一列顯示 mysql 實(shí)際采用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的訪問(wèn)交播，判斷索引是否失效的時(shí)候常用。
17. key_len 列：顯示了 MySQL使用
18. ref 列：ref 列展示的就是與索引列作等值匹配的值践付，常見(jiàn)的有：const（常量）秦士，func，NULL永高，字段名隧土。
19. rows 列：這也是一個(gè)重要的字段，MySQL查詢優(yōu)化器根據(jù)統(tǒng)計(jì)信息乏梁，估算SQL要查到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù)次洼，這個(gè)值非常直觀顯示SQL的效率好壞，原則上rows越少越好遇骑。
20. Extra 列：顯示不適合在其它列的額外信息卖毁，雖然叫額外，但是也有一些重要的信息：

• Using index：表示MySQL將使用覆蓋索引落萎，以避免回表
• Using where：表示會(huì)在存儲(chǔ)引擎檢索之后再進(jìn)行過(guò)濾
• Using temporary ：表示對(duì)查詢結(jié)果排序時(shí)會(huì)使用一個(gè)臨時(shí)表亥啦。

索引

索引可以說(shuō)是MySQL面試中的重中之重，一定要徹底拿下练链。

27.能簡(jiǎn)單說(shuō)一下索引的分類(lèi)嗎翔脱？

從三個(gè)不同維度對(duì)索引分類(lèi)：

image.png

索引分類(lèi)

例如從基本使用使用的角度來(lái)講：

• 主鍵索引: InnoDB主鍵是默認(rèn)的索引，數(shù)據(jù)列不允許重復(fù)媒鼓，不允許為NULL届吁，一個(gè)表只能有一個(gè)主鍵错妖。
• 唯一索引: 數(shù)據(jù)列不允許重復(fù)，允許為NULL值疚沐，一個(gè)表允許多個(gè)列創(chuàng)建唯一索引暂氯。
• 普通索引: 基本的索引類(lèi)型，沒(méi)有唯一性的限制亮蛔，允許為NULL值痴施。
• 組合索引：多列值組成一個(gè)索引，用于組合搜索究流，效率大于索引合并

28.為什么使用索引會(huì)加快查詢辣吃？

傳統(tǒng)的查詢方法，是按照表的順序遍歷的芬探，不論查詢幾條數(shù)據(jù)神得，MySQL需要將表的數(shù)據(jù)從頭到尾遍歷一遍。

在我們添加完索引之后灯节，MySQL一般通過(guò)BTREE算法生成一個(gè)索引文件循头，在查詢數(shù)據(jù)庫(kù)時(shí)，找到索引文件進(jìn)行遍歷炎疆，在比較小的索引數(shù)據(jù)里查找卡骂，然后映射到對(duì)應(yīng)的數(shù)據(jù)，能大幅提升查找的效率形入。

和我們通過(guò)書(shū)的目錄全跨，去查找對(duì)應(yīng)的內(nèi)容，一樣的道理亿遂。

image.png

索引加快查詢遠(yuǎn)離

29.創(chuàng)建索引有哪些注意點(diǎn)浓若？

索引雖然是sql性能優(yōu)化的利器，但是索引的維護(hù)也是需要成本的蛇数，所以創(chuàng)建索引挪钓，也要注意：

1. 索引應(yīng)該建在查詢應(yīng)用頻繁的字段在用于 where 判斷、 order 排序和 join 的(on)字段上創(chuàng)建索引耳舅。
2. 索引的個(gè)數(shù)應(yīng)該適量索引需要占用空間碌上；更新時(shí)候也需要維護(hù)。
3. 區(qū)分度低的字段浦徊，例如性別馏予，不要建索引。離散度太低的字段盔性，掃描的行數(shù)降低的有限霞丧。
4. 頻繁更新的值，不要作為主鍵或者索引維護(hù)索引文件需要成本冕香；還會(huì)導(dǎo)致頁(yè)分裂蛹尝，IO次數(shù)增多后豫。
5. 組合索引把散列性高(區(qū)分度高)的值放在前面為了滿足最左前綴匹配原則
6. 創(chuàng)建組合索引，而不是修改單列索引箩言。組合索引代替多個(gè)單列索引（對(duì)于單列索引硬贯，MySQL基本只能使用一個(gè)索引，所以經(jīng)常使用多個(gè)條件查詢時(shí)更適合使用組合索引）
7. 過(guò)長(zhǎng)的字段陨收，使用前綴索引。當(dāng)字段值比較長(zhǎng)的時(shí)候鸵赖，建立索引會(huì)消耗很多的空間务漩，搜索起來(lái)也會(huì)很慢。我們可以通過(guò)截取字段的前面一部分內(nèi)容建立索引它褪，這個(gè)就叫前綴索引饵骨。
8. 不建議用無(wú)序的值(例如身份證、UUID )作為索引當(dāng)主鍵具有不確定性茫打，會(huì)造成葉子節(jié)點(diǎn)頻繁分裂居触，出現(xiàn)磁盤(pán)存儲(chǔ)的碎片化

30.索引哪些情況下會(huì)失效呢？

• 查詢條件包含or老赤，可能導(dǎo)致索引失效
• 如果字段類(lèi)型是字符串轮洋，where時(shí)一定用引號(hào)括起來(lái)，否則會(huì)因?yàn)殡[式類(lèi)型轉(zhuǎn)換抬旺，索引失效
• like通配符可能導(dǎo)致索引失效弊予。
• 聯(lián)合索引，查詢時(shí)的條件列不是聯(lián)合索引中的第一個(gè)列开财，索引失效汉柒。
• 在索引列上使用mysql的內(nèi)置函數(shù)，索引失效责鳍。
• 對(duì)索引列運(yùn)算（如碾褂，+、-历葛、*正塌、/），索引失效啃洋。
• 索引字段上使用（传货！= 或者 < >，not in）時(shí)宏娄，可能會(huì)導(dǎo)致索引失效问裕。
• 索引字段上使用is null， is not null孵坚，可能導(dǎo)致索引失效粮宛。
• 左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣窥淆，可能導(dǎo)致索引失效。
• MySQL優(yōu)化器估計(jì)使用全表掃描要比使用索引快,則不使用索引巍杈。

31.索引不適合哪些場(chǎng)景呢忧饭？

• 數(shù)據(jù)量比較少的表不適合加索引
• 更新比較頻繁的字段也不適合加索引
• 離散低的字段不適合加索引（如性別）

32.索引是不是建的越多越好呢？

當(dāng)然不是筷畦。

• 索引會(huì)占據(jù)磁盤(pán)空間
• 索引雖然會(huì)提高查詢效率词裤，但是會(huì)降低更新表的效率。比如每次對(duì)表進(jìn)行增刪改操作鳖宾，MySQL不僅要保存數(shù)據(jù)吼砂，還有保存或者更新對(duì)應(yīng)的索引文件。

33.MySQL索引用的什么數(shù)據(jù)結(jié)構(gòu)了解嗎鼎文？

MySQL的默認(rèn)存儲(chǔ)引擎是InnoDB渔肩，它采用的是B+樹(shù)結(jié)構(gòu)的索引。

• B+樹(shù)：只有葉子節(jié)點(diǎn)才會(huì)存儲(chǔ)數(shù)據(jù)拇惋，非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值周偎。葉子節(jié)點(diǎn)之間使用雙向指針連接，最底層的葉子節(jié)點(diǎn)形成了一個(gè)雙向有序鏈表撑帖。

image.png

B+樹(shù)索引

在這張圖里蓉坎，有兩個(gè)重點(diǎn)：

• 最外面的方塊，的塊我們稱之為一個(gè)磁盤(pán)塊磷仰，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（粉色所示）和指針（黃色/灰色所示）袍嬉，如根節(jié)點(diǎn)磁盤(pán)包含數(shù)據(jù)項(xiàng)17和35，包含指針P1灶平、P2伺通、P3，P1表示小于17的磁盤(pán)塊逢享，P2表示在17和35之間的磁盤(pán)塊罐监，P3表示大于35的磁盤(pán)塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3瞒爬、4弓柱、5……、65侧但。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)矢空，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17禀横、35并不真實(shí)存在于數(shù)據(jù)表中屁药。
• 葉子節(jié)點(diǎn)之間使用雙向指針連接，最底層的葉子節(jié)點(diǎn)形成了一個(gè)雙向有序鏈表柏锄，可以進(jìn)行范圍查詢酿箭。

34.那一棵B+樹(shù)能存儲(chǔ)多少條數(shù)據(jù)呢复亏？

image.png

B+樹(shù)存儲(chǔ)數(shù)據(jù)條數(shù)

假設(shè)索引字段是 bigint 類(lèi)型，長(zhǎng)度為 8 字節(jié)缭嫡。指針大小在 InnoDB 源碼中設(shè)置為 6 字節(jié)缔御，這樣一共 14 字節(jié)。非葉子節(jié)點(diǎn)(一頁(yè))可以存儲(chǔ) 16384/14=1170 個(gè)這樣的 單元(鍵值+指針)妇蛀，代表有 1170 個(gè)指針耕突。

樹(shù)深度為 2 的時(shí)候，有 1170^2 個(gè)葉子節(jié)點(diǎn)评架，可以存儲(chǔ)的數(shù)據(jù)為 1170117016=21902400有勾。

在查找數(shù)據(jù)時(shí)一次頁(yè)的查找代表一次 IO，也就是說(shuō)古程，一張 2000 萬(wàn)左右的表，查詢數(shù)據(jù)最多需要訪問(wèn) 3 次磁盤(pán)喊崖。

所以在 InnoDB 中 B+ 樹(shù)深度一般為 1-3 層挣磨，它就能滿足千萬(wàn)級(jí)的數(shù)據(jù)存儲(chǔ)。

35.為什么要用 B+ 樹(shù)荤懂，而不用普通二叉樹(shù)茁裙？

可以從幾個(gè)維度去看這個(gè)問(wèn)題，查詢是否夠快节仿，效率是否穩(wěn)定晤锥，存儲(chǔ)數(shù)據(jù)多少，以及查找磁盤(pán)次數(shù)廊宪。

為什么不用普通二叉樹(shù)矾瘾？

普通二叉樹(shù)存在退化的情況，如果它退化成鏈表箭启，相當(dāng)于全表掃描壕翩。平衡二叉樹(shù)相比于二叉查找樹(shù)來(lái)說(shuō)，查找效率更穩(wěn)定傅寡，總體的查找速度也更快放妈。

為什么不用平衡二叉樹(shù)呢？

讀取數(shù)據(jù)的時(shí)候荐操，是從磁盤(pán)讀到內(nèi)存芜抒。如果樹(shù)這種數(shù)據(jù)結(jié)構(gòu)作為索引，那每查找一次數(shù)據(jù)就需要從磁盤(pán)中讀取一個(gè)節(jié)點(diǎn)托启，也就是一個(gè)磁盤(pán)塊宅倒，但是平衡二叉樹(shù)可是每個(gè)節(jié)點(diǎn)只存儲(chǔ)一個(gè)鍵值和數(shù)據(jù)的，如果是 B+ 樹(shù)驾中，可以存儲(chǔ)更多的節(jié)點(diǎn)數(shù)據(jù)唉堪，樹(shù)的高度也會(huì)降低模聋，因此讀取磁盤(pán)的次數(shù)就降下來(lái)啦，查詢效率就快唠亚。

36.為什么用 B+ 樹(shù)而不用 B 樹(shù)呢链方？

B+相比較B樹(shù)，有這些優(yōu)勢(shì)：

• 它是 B Tree 的變種灶搜，B Tree 能解決的問(wèn)題祟蚀，它都能解決。B Tree 解決的兩大問(wèn)題：每個(gè)節(jié)點(diǎn)存儲(chǔ)更多關(guān)鍵字割卖；路數(shù)更多
• 掃庫(kù)前酿、掃表能力更強(qiáng)如果我們要對(duì)表進(jìn)行全表掃描，只需要遍歷葉子節(jié)點(diǎn)就可以 了鹏溯，不需要遍歷整棵 B+Tree 拿到所有的數(shù)據(jù)罢维。
• B+Tree 的磁盤(pán)讀寫(xiě)能力相對(duì)于 B Tree 來(lái)說(shuō)更強(qiáng)，IO次數(shù)更少根節(jié)點(diǎn)和枝節(jié)點(diǎn)不保存數(shù)據(jù)區(qū)丙挽， 所以一個(gè)節(jié)點(diǎn)可以保存更多的關(guān)鍵字肺孵，一次磁盤(pán)加載的關(guān)鍵字更多，IO次數(shù)更少。
• 排序能力更強(qiáng)因?yàn)槿~子節(jié)點(diǎn)上有下一個(gè)數(shù)據(jù)區(qū)的指針，數(shù)據(jù)形成了鏈表楷兽。
• 效率更加穩(wěn)定B+Tree 永遠(yuǎn)是在葉子節(jié)點(diǎn)拿到數(shù)據(jù)，所以 IO 次數(shù)是穩(wěn)定的瑰艘。

37.Hash 索引和 B+ 樹(shù)索引區(qū)別是什么？

• B+ 樹(shù)可以進(jìn)行范圍查詢肤舞，Hash 索引不能紫新。
• B+ 樹(shù)支持聯(lián)合索引的最左側(cè)原則，Hash 索引不支持萨赁。
• B+ 樹(shù)支持 order by 排序弊琴，Hash 索引不支持。
• Hash 索引在等值查詢上比 B+ 樹(shù)效率更高杖爽。
• B+ 樹(shù)使用 like 進(jìn)行模糊查詢的時(shí)候敲董，like 后面（比如 % 開(kāi)頭）的話可以起到優(yōu)化的作用，Hash 索引根本無(wú)法進(jìn)行模糊查詢慰安。

38.聚簇索引與非聚簇索引的區(qū)別腋寨？

首先理解聚簇索引不是一種新的索引，而是而是一種數(shù)據(jù)存儲(chǔ)方式化焕。聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲(chǔ)在一起萄窜。我們熟悉的兩種存儲(chǔ)引擎——MyISAM采用的是非聚簇索引，InnoDB采用的是聚簇索引。

可以這么說(shuō)：

• 索引的數(shù)據(jù)結(jié)構(gòu)是樹(shù)查刻，聚簇索引的索引和數(shù)據(jù)存儲(chǔ)在一棵樹(shù)上键兜，樹(shù)的葉子節(jié)點(diǎn)就是數(shù)據(jù)，非聚簇索引索引和數(shù)據(jù)不在一棵樹(shù)上穗泵。

image.png

聚簇索引和非聚簇索引

• 一個(gè)表中只能擁有一個(gè)聚簇索引普气，而非聚簇索引一個(gè)表可以存在多個(gè)。
• 聚簇索引佃延，索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序现诀；索引，索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同履肃。
• 聚簇索引：物理存儲(chǔ)按照索引排序仔沿；非聚集索引：物理存儲(chǔ)不按照索引排序；

39.回表了解嗎尺棋？

在InnoDB存儲(chǔ)引擎里封锉，利用輔助索引查詢，先通過(guò)輔助索引找到主鍵索引的鍵值膘螟，再通過(guò)主鍵值查出主鍵索引里面沒(méi)有符合要求的數(shù)據(jù)烘浦，它比基于主鍵索引的查詢多掃描了一棵索引樹(shù)，這個(gè)過(guò)程就叫回表萍鲸。

例如:select * from user where name = ‘張三’;

image.png

InnoDB回表

40.覆蓋索引了解嗎？

在輔助索引里面擦俐，不管是單列索引還是聯(lián)合索引脊阴，如果 select 的數(shù)據(jù)列只用輔助索引中就能夠取得，不用去查主鍵索引蚯瞧，這時(shí)候使用的索引就叫做覆蓋索引嘿期，避免了回表。

比如埋合，select name from user where name = ‘張三’;

image.png

覆蓋索引

41.什么是最左前綴原則/最左匹配原則备徐？

注意：最左前綴原則、最左匹配原則甚颂、最左前綴匹配原則這三個(gè)都是一個(gè)概念蜜猾。

最左匹配原則：在InnoDB的聯(lián)合索引中，查詢的時(shí)候只有匹配了前一個(gè)/左邊的值之后振诬，才能匹配下一個(gè)蹭睡。

根據(jù)最左匹配原則，我們創(chuàng)建了一個(gè)組合索引赶么，如 (a1,a2,a3)肩豁，相當(dāng)于創(chuàng)建了（a1）、(a1,a2)和 (a1,a2,a3) 三個(gè)索引。

為什么不從最左開(kāi)始查清钥，就無(wú)法匹配呢琼锋？

比如有一個(gè)user表，我們給 name 和 age 建立了一個(gè)組合索引祟昭。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

組合索引在 B+Tree 中是復(fù)合的數(shù)據(jù)結(jié)構(gòu)缕坎，它是按照從左到右的順序來(lái)建立搜索樹(shù)的 (name 在左邊，age 在右邊)从橘。

image.png

組合索引

從這張圖可以看出來(lái)念赶，name 是有序的，age 是無(wú)序的恰力。當(dāng) name 相等的時(shí)候叉谜， age 才是有序的。

這個(gè)時(shí)候我們使用 where name= ‘張三‘ and age = ‘20 ‘去查詢數(shù)據(jù)的時(shí)候踩萎， B+Tree 會(huì)優(yōu)先比較 name 來(lái)確定下一步應(yīng)該搜索的方向停局，往左還是往右。如果 name 相同的時(shí)候再比較age香府。但是如果查詢條件沒(méi)有 name董栽，就不知道下一步應(yīng)該查哪個(gè) 節(jié)點(diǎn)，因?yàn)榻⑺阉鳂?shù)的時(shí)候 name 是第一個(gè)比較因子企孩，所以就沒(méi)用上索引锭碳。

42.什么是索引下推優(yōu)化？

索引條件下推優(yōu)化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的勿璃，用于優(yōu)化數(shù)據(jù)查詢擒抛。

• 不使用索引條件下推優(yōu)化時(shí)存儲(chǔ)引擎通過(guò)索引檢索到數(shù)據(jù)，然后返回給MySQL Server补疑，MySQL Server進(jìn)行過(guò)濾條件的判斷歧沪。
• 當(dāng)使用索引條件下推優(yōu)化時(shí)，如果存在某些被索引的列的判斷條件時(shí)莲组，MySQL Server將這一部分判斷條件下推給存儲(chǔ)引擎诊胞，然后由存儲(chǔ)引擎通過(guò)判斷索引是否符合MySQL Server傳遞的條件，只有當(dāng)索引符合條件時(shí)才會(huì)將數(shù)據(jù)檢索出來(lái)返回給MySQL服務(wù)器锹杈。

例如一張表撵孤，建了一個(gè)聯(lián)合索引（name, age），查詢語(yǔ)句：select * from t_user where name like '張%' and age=10;竭望，由于name使用了范圍查詢早直，根據(jù)最左匹配原則：

不使用ICP，引擎層查找到name like '張%'的數(shù)據(jù)市框，再由Server層去過(guò)濾age=10這個(gè)條件霞扬，這樣一來(lái)，就回表了兩次，浪費(fèi)了聯(lián)合索引的另外一個(gè)字段age喻圃。

image.png

沒(méi)有使用ICP

但是萤彩，使用了索引下推優(yōu)化，把where的條件放到了引擎層執(zhí)行斧拍，直接根據(jù)name like '張%' and age=10的條件進(jìn)行過(guò)濾雀扶，減少了回表的次數(shù)。

image.png

使用ICP

索引條件下推優(yōu)化可以減少存儲(chǔ)引擎查詢基礎(chǔ)表的次數(shù)肆汹，也可以減少M(fèi)ySQL服務(wù)器從存儲(chǔ)引擎接收數(shù)據(jù)的次數(shù)愚墓。

鎖

43.MySQL中有哪幾種鎖，列舉一下昂勉？

image.png

MySQL中的鎖

如果按鎖粒度劃分浪册，有以下3種：

• 表鎖：開(kāi)銷(xiāo)小，加鎖快岗照；鎖定力度大村象，發(fā)生鎖沖突概率高，并發(fā)度最低;不會(huì)出現(xiàn)死鎖攒至。
• 行鎖：開(kāi)銷(xiāo)大厚者，加鎖慢；會(huì)出現(xiàn)死鎖迫吐；鎖定粒度小库菲，發(fā)生鎖沖突的概率低，并發(fā)度高志膀。
• 頁(yè)鎖：開(kāi)銷(xiāo)和加鎖速度介于表鎖和行鎖之間蝙昙；會(huì)出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間梧却，并發(fā)度一般

如果按照兼容性，有兩種败去，

• 共享鎖（S Lock）,也叫讀鎖（read lock）放航，相互不阻塞。
• 排他鎖（X Lock）圆裕，也叫寫(xiě)鎖（write lock）广鳍，排它鎖是阻塞的，在一定時(shí)間內(nèi)吓妆，只有一個(gè)請(qǐng)求能執(zhí)行寫(xiě)入赊时，并阻止其它鎖讀取正在寫(xiě)入的數(shù)據(jù)。

44.說(shuō)說(shuō)InnoDB里的行鎖實(shí)現(xiàn)?

我們拿這么一個(gè)用戶表來(lái)表示行級(jí)鎖行拢，其中插入了4行數(shù)據(jù)祖秒，主鍵值分別是1,6,8,12，現(xiàn)在簡(jiǎn)化它的聚簇索引結(jié)構(gòu)，只保留數(shù)據(jù)記錄竭缝。

image.png

簡(jiǎn)化的主鍵索引

InnoDB的行鎖的主要實(shí)現(xiàn)如下：

• Record Lock 記錄鎖

記錄鎖就是直接鎖定某行記錄房维。當(dāng)我們使用唯一性的索引(包括唯一索引和聚簇索引)進(jìn)行等值查詢且精準(zhǔn)匹配到一條記錄時(shí)，此時(shí)就會(huì)直接將這條記錄鎖定抬纸。例如select * from t where id =6 for update;就會(huì)將id=6的記錄鎖定咙俩。

image.png

記錄鎖

• Gap Lock 間隙鎖

間隙鎖(Gap Locks) 的間隙指的是兩個(gè)記錄之間邏輯上尚未填入數(shù)據(jù)的部分,是一個(gè)左開(kāi)右開(kāi)空間。

image.png

間隙鎖

間隙鎖就是鎖定某些間隙區(qū)間的湿故。當(dāng)我們使用用等值查詢或者范圍查詢阿趁，并且沒(méi)有命中任何一個(gè)record，此時(shí)就會(huì)將對(duì)應(yīng)的間隙區(qū)間鎖定坛猪。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就會(huì)將(1,6)區(qū)間鎖定脖阵。

• Next-key Lock 臨鍵鎖

臨鍵指的是間隙加上它右邊的記錄組成的左開(kāi)右閉區(qū)間。比如上述的(1,6]砚哆、(6,8]等独撇。

image.png

臨鍵鎖

臨鍵鎖就是記錄鎖(Record Locks)和間隙鎖(Gap Locks)的結(jié)合，即除了鎖住記錄本身躁锁，還要再鎖住索引之間的間隙纷铣。當(dāng)我們使用范圍查詢，并且命中了部分record記錄战转，此時(shí)鎖住的就是臨鍵區(qū)間搜立。注意，臨鍵鎖鎖住的區(qū)間會(huì)包含最后一個(gè)record的右邊的臨鍵區(qū)間槐秧。例如select * from t where id > 5 and id <= 7 for update;會(huì)鎖住(4,7]啄踊、(7,+∞)。mysql默認(rèn)行鎖類(lèi)型就是臨鍵鎖(Next-Key Locks)刁标。當(dāng)使用唯一性索引颠通，等值查詢匹配到一條記錄的時(shí)候，臨鍵鎖(Next-Key Locks)會(huì)退化成記錄鎖膀懈；沒(méi)有匹配到任何記錄的時(shí)候顿锰，退化成間隙鎖。

間隙鎖(Gap Locks)和臨鍵鎖(Next-Key Locks)都是用來(lái)解決幻讀問(wèn)題的启搂，在已提交讀（READ COMMITTED）隔離級(jí)別下硼控，間隙鎖(Gap Locks)和臨鍵鎖(Next-Key Locks)都會(huì)失效！

上面是行鎖的三種實(shí)現(xiàn)算法胳赌，除此之外牢撼，在行上還存在插入意向鎖。

• Insert Intention Lock 插入意向鎖

一個(gè)事務(wù)在插入一條記錄時(shí)需要判斷一下插入位置是不是被別的事務(wù)加了意向鎖 疑苫，如果有的話熏版，插入操作需要等待纷责，直到擁有 gap鎖 的那個(gè)事務(wù)提交。但是事務(wù)在等待的時(shí)候也需要在內(nèi)存中生成一個(gè) 鎖結(jié)構(gòu) 纳决，表明有事務(wù)想在某個(gè) 間隙 中插入新記錄碰逸，但是現(xiàn)在在等待。這種類(lèi)型的鎖命名為 Insert Intention Locks 阔加，也就是插入意向鎖 饵史。

假如我們有個(gè)T1事務(wù)，給(1,6)區(qū)間加上了意向鎖胜榔，現(xiàn)在有個(gè)T2事務(wù)胳喷，要插入一個(gè)數(shù)據(jù)，id為4夭织，它會(huì)獲取一個(gè)（1,6）區(qū)間的插入意向鎖吭露，又有有個(gè)T3事務(wù)，想要插入一個(gè)數(shù)據(jù)尊惰，id為3讲竿，它也會(huì)獲取一個(gè)（1,6）區(qū)間的插入意向鎖，但是弄屡，這兩個(gè)插入意向鎖鎖不會(huì)互斥题禀。

image.png

插入意向鎖

45.意向鎖是什么知道嗎？

意向鎖是一個(gè)表級(jí)鎖膀捷，不要和插入意向鎖搞混迈嘹。

意向鎖的出現(xiàn)是為了支持InnoDB的多粒度鎖，它解決的是表鎖和行鎖共存的問(wèn)題全庸。

當(dāng)我們需要給一個(gè)表加表鎖的時(shí)候秀仲，我們需要根據(jù)去判斷表中有沒(méi)有數(shù)據(jù)行被鎖定，以確定是否能加成功壶笼。

假如沒(méi)有意向鎖神僵，那么我們就得遍歷表中所有數(shù)據(jù)行來(lái)判斷有沒(méi)有行鎖；

有了意向鎖這個(gè)表級(jí)鎖之后覆劈，則我們直接判斷一次就知道表中是否有數(shù)據(jù)行被鎖定了保礼。

有了意向鎖之后，要執(zhí)行的事務(wù)A在申請(qǐng)行鎖（寫(xiě)鎖）之前墩崩，數(shù)據(jù)庫(kù)會(huì)自動(dòng)先給事務(wù)A申請(qǐng)表的意向排他鎖。當(dāng)事務(wù)B去申請(qǐng)表的互斥鎖時(shí)就會(huì)失敗侯勉，因?yàn)楸砩嫌幸庀蚺潘i之后事務(wù)B申請(qǐng)表的互斥鎖時(shí)會(huì)被阻塞鹦筹。

image.png

意向鎖

46.MySQL的樂(lè)觀鎖和悲觀鎖了解嗎？

• 悲觀鎖（Pessimistic Concurrency Control）：

悲觀鎖認(rèn)為被它保護(hù)的數(shù)據(jù)是極其不安全的址貌，每時(shí)每刻都有可能被改動(dòng)铐拐，一個(gè)事務(wù)拿到悲觀鎖后徘键，其他任何事務(wù)都不能對(duì)該數(shù)據(jù)進(jìn)行修改，只能等待鎖被釋放才可以執(zhí)行遍蟋。

數(shù)據(jù)庫(kù)中的行鎖吹害，表鎖，讀鎖虚青，寫(xiě)鎖均為悲觀鎖它呀。

• 樂(lè)觀鎖（Optimistic Concurrency Control）

樂(lè)觀鎖認(rèn)為數(shù)據(jù)的變動(dòng)不會(huì)太頻繁。

樂(lè)觀鎖通常是通過(guò)在表中增加一個(gè)版本(version)或時(shí)間戳(timestamp)來(lái)實(shí)現(xiàn)抢野，其中，版本最為常用昧互。

事務(wù)在從數(shù)據(jù)庫(kù)中取數(shù)據(jù)時(shí)玖雁，會(huì)將該數(shù)據(jù)的版本也取出來(lái)(v1)，當(dāng)事務(wù)對(duì)數(shù)據(jù)變動(dòng)完畢想要將其更新到表中時(shí)辽幌，會(huì)將之前取出的版本v1與數(shù)據(jù)中最新的版本v2相對(duì)比猪腕，如果v1=v2，那么說(shuō)明在數(shù)據(jù)變動(dòng)期間，沒(méi)有其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改，此時(shí)剪侮，就允許事務(wù)對(duì)表中的數(shù)據(jù)進(jìn)行修改绪爸，并且修改時(shí)version會(huì)加1廊遍，以此來(lái)表明數(shù)據(jù)已被變動(dòng)。

如果挂疆，v1不等于v2，那么說(shuō)明數(shù)據(jù)變動(dòng)期間锈拨，數(shù)據(jù)被其他事務(wù)改動(dòng)了砌庄，此時(shí)不允許數(shù)據(jù)更新到表中，一般的處理辦法是通知用戶讓其重新操作奕枢。不同于悲觀鎖鹤耍，樂(lè)觀鎖通常是由開(kāi)發(fā)者實(shí)現(xiàn)的。

47.MySQL 遇到過(guò)死鎖問(wèn)題嗎验辞，你是如何解決的稿黄？

排查死鎖的一般步驟是這樣的：

（1）查看死鎖日志 show engine innodb status;

（2）找出死鎖 sql

（3）分析 sql 加鎖情況

（4）模擬死鎖案發(fā)

（5）分析死鎖日志

（6）分析死鎖結(jié)果

當(dāng)然，這只是一個(gè)簡(jiǎn)單的流程說(shuō)明跌造，實(shí)際上生產(chǎn)中的死鎖千奇百怪杆怕，排查和解決起來(lái)沒(méi)那么簡(jiǎn)單。

事務(wù)

48.MySQL 事務(wù)的四大特性說(shuō)一下壳贪？

image.png

事務(wù)四大特性

• 原子性：事務(wù)作為一個(gè)整體被執(zhí)行陵珍，包含在其中的對(duì)數(shù)據(jù)庫(kù)的操作要么全部被執(zhí)行，要么都不執(zhí)行违施。
• 一致性：指在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后互纯，數(shù)據(jù)不會(huì)被破壞，假如 A 賬戶給 B 賬戶轉(zhuǎn) 10 塊錢(qián)磕蒲，不管成功與否留潦，A 和 B 的總金額是不變的。
• 隔離性：多個(gè)事務(wù)并發(fā)訪問(wèn)時(shí)辣往，事務(wù)之間是相互隔離的兔院，即一個(gè)事務(wù)不影響其它事務(wù)運(yùn)行效果。簡(jiǎn)言之站削，就是事務(wù)之間是進(jìn)水不犯河水的坊萝。
• 持久性：表示事務(wù)完成以后，該事務(wù)對(duì)數(shù)據(jù)庫(kù)所作的操作更改，將持久地保存在數(shù)據(jù)庫(kù)之中十偶。

49.那ACID靠什么保證的呢菩鲜？

• 事務(wù)的隔離性是通過(guò)數(shù)據(jù)庫(kù)鎖的機(jī)制實(shí)現(xiàn)的。
• 事務(wù)的一致性由undo log來(lái)保證：undo log是邏輯日志惦积，記錄了事務(wù)的insert睦袖、update、deltete操作丧鸯，回滾的時(shí)候做相反的delete躁锡、update、insert操作來(lái)恢復(fù)數(shù)據(jù)。
• 事務(wù)的原子性和持久性由redo log來(lái)保證：redolog被稱作重做日志京髓，是物理日志，事務(wù)提交的時(shí)候让蕾，必須先將事務(wù)的所有日志寫(xiě)入redo log持久化铡俐，到事務(wù)的提交操作才算完成。

image.png

ACID靠什么保證

50.事務(wù)的隔離級(jí)別有哪些招刹？MySQL 的默認(rèn)隔離級(jí)別是什么恬试？

image.png

事務(wù)的四個(gè)隔離級(jí)別

• 讀未提交（Read Uncommitted）
• 讀已提交（Read Committed）
• 可重復(fù)讀（Repeatable Read）
• 串行化（Serializable）

MySQL默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀 (Repeatable Read)。

51.什么是幻讀疯暑，臟讀训柴，不可重復(fù)讀呢？

• 事務(wù) A妇拯、B 交替執(zhí)行幻馁，事務(wù) A 讀取到事務(wù) B 未提交的數(shù)據(jù)，這就是臟讀越锈。
• 在一個(gè)事務(wù)范圍內(nèi)仗嗦，兩個(gè)相同的查詢，讀取同一條記錄甘凭，卻返回了不同的數(shù)據(jù)稀拐，這就是不可重復(fù)讀。
• 事務(wù) A 查詢一個(gè)范圍的結(jié)果集丹弱，另一個(gè)并發(fā)事務(wù) B 往這個(gè)范圍中插入 / 刪除了數(shù)據(jù)德撬，并靜悄悄地提交，然后事務(wù) A 再次查詢相同的范圍躲胳，兩次讀取得到的結(jié)果集不一樣了砰逻，這就是幻讀。

不同的隔離級(jí)別泛鸟，在并發(fā)事務(wù)下可能會(huì)發(fā)生的問(wèn)題：

隔離級(jí)別臟讀不可重復(fù)讀幻讀Read Uncommited 讀取未提交是是是Read Commited 讀取已提交否是否Repeatable Read 可重復(fù)讀否否是Serialzable 可串行化否否否

52.事務(wù)的各個(gè)隔離級(jí)別都是如何實(shí)現(xiàn)的蝠咆？

讀未提交

讀未提交，就不用多說(shuō)了，采取的是讀不加鎖原理刚操。

• 事務(wù)讀不加鎖闸翅，不阻塞其他事務(wù)的讀和寫(xiě)
• 事務(wù)寫(xiě)阻塞其他事務(wù)寫(xiě)，但不阻塞其他事務(wù)讀菊霜；

讀取已提交&可重復(fù)讀

讀取已提交和可重復(fù)讀級(jí)別利用了ReadView和MVCC坚冀，也就是每個(gè)事務(wù)只能讀取它能看到的版本（ReadView）。

• READ COMMITTED：每次讀取數(shù)據(jù)前都生成一個(gè)ReadView
• REPEATABLE READ ：在第一次讀取數(shù)據(jù)時(shí)生成一個(gè)ReadView

串行化

串行化的實(shí)現(xiàn)采用的是讀寫(xiě)都加鎖的原理鉴逞。

串行化的情況下记某，對(duì)于同一行事務(wù)，寫(xiě)會(huì)加寫(xiě)鎖构捡，讀會(huì)加讀鎖液南。當(dāng)出現(xiàn)讀寫(xiě)鎖沖突的時(shí)候，后訪問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成勾徽，才能繼續(xù)執(zhí)行滑凉。

53.MVCC了解嗎？怎么實(shí)現(xiàn)的喘帚？

MVCC(Multi Version Concurrency Control)畅姊，中文名是多版本并發(fā)控制，簡(jiǎn)單來(lái)說(shuō)就是通過(guò)維護(hù)數(shù)據(jù)歷史版本吹由，從而解決并發(fā)訪問(wèn)情況下的讀一致性問(wèn)題若未。關(guān)于它的實(shí)現(xiàn)，要抓住幾個(gè)關(guān)鍵點(diǎn)倾鲫，隱式字段陨瘩、undo日志、版本鏈级乍、快照讀&當(dāng)前讀舌劳、Read View。

版本鏈

對(duì)于InnoDB存儲(chǔ)引擎玫荣，每一行記錄都有兩個(gè)隱藏列DB_TRX_ID甚淡、DB_ROLL_PTR

• DB_TRX_ID，事務(wù)ID捅厂，每次修改時(shí)贯卦，都會(huì)把該事務(wù)ID復(fù)制給DB_TRX_ID；
• DB_ROLL_PTR焙贷，回滾指針撵割，指向回滾段的undo日志。

image.png

表隱藏列

假如有一張user表辙芍，表中只有一行記錄啡彬，當(dāng)時(shí)插入的事務(wù)id為80羹与。此時(shí)，該條記錄的示例圖如下：

image.png

在這里插入圖片描述

接下來(lái)有兩個(gè)DB_TRX_ID分別為100庶灿、200的事務(wù)對(duì)這條記錄進(jìn)行update操作纵搁，整個(gè)過(guò)程如下：

image.png

update操作

由于每次變動(dòng)都會(huì)先把undo日志記錄下來(lái)，并用DB_ROLL_PTR指向undo日志地址往踢。因此可以認(rèn)為腾誉，對(duì)該條記錄的修改日志串聯(lián)起來(lái)就形成了一個(gè)版本鏈，版本鏈的頭節(jié)點(diǎn)就是當(dāng)前記錄最新的值峻呕。如下：

image.png

MVCC

ReadView

對(duì)于Read Committed和Repeatable Read隔離級(jí)別來(lái)說(shuō)利职，都需要讀取已經(jīng)提交的事務(wù)所修改的記錄，也就是說(shuō)如果版本鏈中某個(gè)版本的修改沒(méi)有提交瘦癌，那么該版本的記錄時(shí)不能被讀取的猪贪。所以需要確定在Read Committed和Repeatable Read隔離級(jí)別下，版本鏈中哪個(gè)版本是能被當(dāng)前事務(wù)讀取的佩憾。于是就引入了ReadView這個(gè)概念來(lái)解決這個(gè)問(wèn)題。

Read View就是事務(wù)執(zhí)行快照讀時(shí)干花，產(chǎn)生的讀視圖妄帘，相當(dāng)于某時(shí)刻表記錄的一個(gè)快照，通過(guò)這個(gè)快照池凄，我們可以獲嚷胀铡：

image.png

事務(wù)和ReadView

• m_ids ：表示在生成 ReadView 時(shí)當(dāng)前系統(tǒng)中活躍的讀寫(xiě)事務(wù)的事務(wù)id 列表。
• min_trx_id ：表示在生成 ReadView 時(shí)當(dāng)前系統(tǒng)中活躍的讀寫(xiě)事務(wù)中最小的 事務(wù)id 肿仑，也就是 m_ids 中的最小值致盟。
• max_trx_id ：表示生成 ReadView 時(shí)系統(tǒng)中應(yīng)該分配給下一個(gè)事務(wù)的 id 值。
• creator_trx_id ：表示生成該 ReadView 的事務(wù)的 事務(wù)id

有了這個(gè) ReadView 尤慰，這樣在訪問(wèn)某條記錄時(shí)馏锡，只需要按照下邊的步驟判斷記錄的某個(gè)版本是否可見(jiàn)：

• 如果被訪問(wèn)版本的 DB_TRX_ID 屬性值與 ReadView 中的 creator_trx_id 值相同，意味著當(dāng)前事務(wù)在訪問(wèn)它自己修改過(guò)的記錄伟端，所以該版本可以被當(dāng)前事務(wù)訪問(wèn)杯道。
• 如果被訪問(wèn)版本的 DB_TRX_ID 屬性值小于 ReadView 中的 min_trx_id 值，表明生成該版本的事務(wù)在當(dāng)前事務(wù)生成 ReadView 前已經(jīng)提交责蝠，所以該版本可以被當(dāng)前事務(wù)訪問(wèn)党巾。
• 如果被訪問(wèn)版本的 DB_TRX_ID 屬性值大于 ReadView 中的 max_trx_id 值，表明生成該版本的事務(wù)在當(dāng)前事務(wù)生成 ReadView 后才開(kāi)啟霜医，所以該版本不可以被當(dāng)前事務(wù)訪問(wèn)齿拂。
• 如果被訪問(wèn)版本的 DB_TRX_ID 屬性值在 ReadView 的 min_trx_id 和 max_trx_id 之間，那就需要判斷一下trx_id 屬性值是不是在 m_ids 列表中肴敛，如果在署海，說(shuō)明創(chuàng)建 ReadView 時(shí)生成該版本的事務(wù)還是活躍的吗购，該版本不可以被訪問(wèn)；如果不在叹侄，說(shuō)明創(chuàng)建 ReadView 時(shí)生成該版本的事務(wù)已經(jīng)被提交巩搏，該版本可以被訪問(wèn)。

如果某個(gè)版本的數(shù)據(jù)對(duì)當(dāng)前事務(wù)不可見(jiàn)的話趾代，那就順著版本鏈找到下一個(gè)版本的數(shù)據(jù)贯底，繼續(xù)按照上邊的步驟判斷可見(jiàn)性，依此類(lèi)推撒强，直到版本鏈中的最后一個(gè)版本禽捆。如果最后一個(gè)版本也不可見(jiàn)的話，那么就意味著該條記錄對(duì)該事務(wù)完全不可見(jiàn)飘哨，查詢結(jié)果就不包含該記錄胚想。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔離級(jí)別的的一個(gè)非常大的區(qū)別就是它們生成ReadView的時(shí)機(jī)不同芽隆。

READ COMMITTED 是每次讀取數(shù)據(jù)前都生成一個(gè)ReadView浊服，這樣就能保證自己每次都能讀到其它事務(wù)提交的數(shù)據(jù)；REPEATABLE READ 是在第一次讀取數(shù)據(jù)時(shí)生成一個(gè)ReadView胚吁，這樣就能保證后續(xù)讀取的結(jié)果完全一致牙躺。

高可用/性能

54.數(shù)據(jù)庫(kù)讀寫(xiě)分離了解嗎？

讀寫(xiě)分離的基本原理是將數(shù)據(jù)庫(kù)讀寫(xiě)操作分散到不同的節(jié)點(diǎn)上腕扶，下面是基本架構(gòu)圖：

image.png

讀寫(xiě)分離

讀寫(xiě)分離的基本實(shí)現(xiàn)是:

• 數(shù)據(jù)庫(kù)服務(wù)器搭建主從集群孽拷，一主一從、一主多從都可以半抱。
• 數(shù)據(jù)庫(kù)主機(jī)負(fù)責(zé)讀寫(xiě)操作脓恕，從機(jī)只負(fù)責(zé)讀操作。
• 數(shù)據(jù)庫(kù)主機(jī)通過(guò)復(fù)制將數(shù)據(jù)同步到從機(jī)窿侈，每臺(tái)數(shù)據(jù)庫(kù)服務(wù)器都存儲(chǔ)了所有的業(yè)務(wù)數(shù)據(jù)炼幔。
• 業(yè)務(wù)服務(wù)器將寫(xiě)操作發(fā)給數(shù)據(jù)庫(kù)主機(jī)，將讀操作發(fā)給數(shù)據(jù)庫(kù)從機(jī)史简。

55.那讀寫(xiě)分離的分配怎么實(shí)現(xiàn)呢江掩？

將讀寫(xiě)操作區(qū)分開(kāi)來(lái)，然后訪問(wèn)不同的數(shù)據(jù)庫(kù)服務(wù)器乘瓤，一般有兩種方式：程序代碼封裝和中間件封裝环形。

1. 程序代碼封裝

程序代碼封裝指在代碼中抽象一個(gè)數(shù)據(jù)訪問(wèn)層（所以有的文章也稱這種方式為 "中間層封裝" ） ，實(shí)現(xiàn)讀寫(xiě)操作分離和數(shù)據(jù)庫(kù)服務(wù)器連接的管理衙傀。例如抬吟，基于 Hibernate 進(jìn)行簡(jiǎn)單封裝，就可以實(shí)現(xiàn)讀寫(xiě)分離：

image.png

業(yè)務(wù)代碼封裝

目前開(kāi)源的實(shí)現(xiàn)方案中统抬，淘寶的 TDDL (Taobao Distributed Data Layer, 外號(hào)：頭都大了）是比較有名的火本。

2. 中間件封裝

中間件封裝指的是獨(dú)立一套系統(tǒng)出來(lái)危队，實(shí)現(xiàn)讀寫(xiě)操作分離和數(shù)據(jù)庫(kù)服務(wù)器連接的管理。中間件對(duì)業(yè)務(wù)服務(wù)器提供 SQL 兼容的協(xié)議钙畔，業(yè)務(wù)服務(wù)器無(wú)須自己進(jìn)行讀寫(xiě)分離茫陆。

對(duì)于業(yè)務(wù)服務(wù)器來(lái)說(shuō)，訪問(wèn)中間件和訪問(wèn)數(shù)據(jù)庫(kù)沒(méi)有區(qū)別擎析，事實(shí)上在業(yè)務(wù)服務(wù)器看來(lái)簿盅，中間件就是一個(gè)數(shù)據(jù)庫(kù)服務(wù)器。

其基本架構(gòu)是：

image.png

數(shù)據(jù)庫(kù)中間件

56.主從復(fù)制原理了解嗎揍魂？

• master數(shù)據(jù)寫(xiě)入桨醋，更新binlog
• master創(chuàng)建一個(gè)dump線程向slave推送binlog
• slave連接到master的時(shí)候，會(huì)創(chuàng)建一個(gè)IO線程接收binlog现斋，并記錄到relay log中繼日志中
• slave再開(kāi)啟一個(gè)sql線程讀取relay log事件并在slave執(zhí)行喜最，完成同步
• slave記錄自己的binglog

image.png

主從復(fù)制

57.主從同步延遲怎么處理？

主從同步延遲的原因

一個(gè)服務(wù)器開(kāi)放Ｎ個(gè)鏈接給客戶端來(lái)連接的庄蹋，這樣有會(huì)有大并發(fā)的更新操作, 但是從服務(wù)器的里面讀取 binlog 的線程僅有一個(gè)瞬内，當(dāng)某個(gè) SQL 在從服務(wù)器上執(zhí)行的時(shí)間稍長(zhǎng) 或者由于某個(gè) SQL 要進(jìn)行鎖表就會(huì)導(dǎo)致，主服務(wù)器的 SQL 大量積壓限书，未被同步到從服務(wù)器里虫蝶。這就導(dǎo)致了主從不一致， 也就是主從延遲蔗包。

主從同步延遲的解決辦法

解決主從復(fù)制延遲有幾種常見(jiàn)的方法:

1. 寫(xiě)操作后的讀操作指定發(fā)給數(shù)據(jù)庫(kù)主服務(wù)器

例如秉扑，注冊(cè)賬號(hào)完成后慧邮，登錄時(shí)讀取賬號(hào)的讀操作也發(fā)給數(shù)據(jù)庫(kù)主服務(wù)器调限。這種方式和業(yè)務(wù)強(qiáng)綁定，對(duì)業(yè)務(wù)的侵入和影響較大误澳，如果哪個(gè)新來(lái)的程序員不知道這樣寫(xiě)代碼耻矮，就會(huì)導(dǎo)致一個(gè)bug。

2. 讀從機(jī)失敗后再讀一次主機(jī)

這就是通常所說(shuō)的 "二次讀取" 忆谓，二次讀取和業(yè)務(wù)無(wú)綁定裆装，只需要對(duì)底層數(shù)據(jù)庫(kù)訪問(wèn)的 API 進(jìn)行封裝即可，實(shí)現(xiàn)代價(jià)較小倡缠，不足之處在于如果有很多二次讀取哨免，將大大增加主機(jī)的讀操作壓力。例如昙沦，黑客暴力破解賬號(hào)琢唾，會(huì)導(dǎo)致大量的二次讀取操作，主機(jī)可能頂不住讀操作的壓力從而崩潰盾饮。

3. 關(guān)鍵業(yè)務(wù)讀寫(xiě)操作全部指向主機(jī)采桃，非關(guān)鍵業(yè)務(wù)采用讀寫(xiě)分離

例如懒熙，對(duì)于一個(gè)用戶管理系統(tǒng)來(lái)說(shuō)，注冊(cè) + 登錄的業(yè)務(wù)讀寫(xiě)操作全部訪問(wèn)主機(jī)普办，用戶的介紹工扎、爰好、等級(jí)等業(yè)務(wù)衔蹲，可以采用讀寫(xiě)分離肢娘，因?yàn)榧词褂脩舾牧俗约旱淖晕医榻B，在查詢時(shí)卻看到了自我介紹還是舊的踪危，業(yè)務(wù)影響與不能登錄相比就小很多蔬浙，還可以忍受。

58.你們一般是怎么分庫(kù)的呢贞远？

• 垂直分庫(kù)：以表為依據(jù)畴博，按照業(yè)務(wù)歸屬不同，將不同的表拆分到不同的庫(kù)中蓝仲。

image.png

垂直分庫(kù)

• 水平分庫(kù)：以字段為依據(jù)俱病，按照一定策略（hash、range 等）袱结，將一個(gè)庫(kù)中的數(shù)據(jù)拆分到多個(gè)庫(kù)中亮隙。

image.png

水平分庫(kù)

59.那你們是怎么分表的？

• 水平分表：以字段為依據(jù)垢夹，按照一定策略（hash溢吻、range 等），將一個(gè)表中的數(shù)據(jù)拆分到多個(gè)表中果元。
• 垂直分表：以字段為依據(jù)促王，按照字段的活躍性，將表中字段拆到不同的表（主表和擴(kuò)展表）中而晒。

image.png

表拆分

60.水平分表有哪幾種路由方式蝇狼？

什么是路由呢？就是數(shù)據(jù)應(yīng)該分到哪一張表倡怎。

水平分表主要有三種路由方式：

• 范圍路由：選取有序的數(shù)據(jù)列 （例如迅耘，整形、時(shí)間戳等） 作為路由的條件监署，不同分段分散到不同的數(shù)據(jù)庫(kù)表中颤专。

我們可以觀察一些支付系統(tǒng)，發(fā)現(xiàn)只能查一年范圍內(nèi)的支付記錄钠乏，這個(gè)可能就是支付公司按照時(shí)間進(jìn)行了分表栖秕。

image.png

范圍路由

范圍路由設(shè)計(jì)的復(fù)雜點(diǎn)主要體現(xiàn)在分段大小的選取上，分段太小會(huì)導(dǎo)致切分后子表數(shù)量過(guò)多缓熟，增加維護(hù)復(fù)雜度累魔；分段太大可能會(huì)導(dǎo)致單表依然存在性能問(wèn)題摔笤，一般建議分段大小在 100 萬(wàn)至2000 萬(wàn)之間，具體需要根據(jù)業(yè)務(wù)選取合適的分段大小垦写。

范圍路由的優(yōu)點(diǎn)是可以隨著數(shù)據(jù)的增加平滑地?cái)U(kuò)充新的表吕世。例如，現(xiàn)在的用戶是 100 萬(wàn)梯投，如果增加到 1000 萬(wàn)命辖，只需要增加新的表就可以了，原有的數(shù)據(jù)不需要?jiǎng)臃直汀７秶酚傻囊粋€(gè)比較隱含的缺點(diǎn)是分布不均勻尔艇，假如按照 1000 萬(wàn)來(lái)進(jìn)行分表，有可能某個(gè)分段實(shí)際存儲(chǔ)的數(shù)據(jù)量只有 1000 條么鹤，而另外一個(gè)分段實(shí)際存儲(chǔ)的數(shù)據(jù)量有 900 萬(wàn)條终娃。

• Hash 路由：選取某個(gè)列 （或者某幾個(gè)列組合也可以） 的值進(jìn)行 Hash 運(yùn)算，然后根據(jù) Hash 結(jié)果分散到不同的數(shù)據(jù)庫(kù)表中蒸甜。

同樣以訂單 id 為例棠耕，假如我們一開(kāi)始就規(guī)劃了 4個(gè)數(shù)據(jù)庫(kù)表，路由算法可以簡(jiǎn)單地用 id % 4 的值來(lái)表示數(shù)據(jù)所屬的數(shù)據(jù)庫(kù)表編號(hào)柠新，id 為 12的訂單放到編號(hào)為 50的子表中窍荧，id為 13的訂單放到編號(hào)為 61的字表中。

image.png

Hash路由

Hash 路由設(shè)計(jì)的復(fù)雜點(diǎn)主要體現(xiàn)在初始表數(shù)量的選取上恨憎，表數(shù)量太多維護(hù)比較麻煩蕊退，表數(shù)量太少又可能導(dǎo)致單表性能存在問(wèn)題。而用了 Hash 路由后憔恳，增加子表數(shù)量是非常麻煩的瓤荔，所有數(shù)據(jù)都要重分布。Hash 路由的優(yōu)缺點(diǎn)和范圍路由基本相反喇嘱，Hash 路由的優(yōu)點(diǎn)是表分布比較均勻茉贡，缺點(diǎn)是擴(kuò)充新的表很麻煩塞栅，所有數(shù)據(jù)都要重分布者铜。

• 配置路由：配置路由就是路由表，用一張獨(dú)立的表來(lái)記錄路由信息放椰。同樣以訂單id 為例作烟，我們新增一張 order_router 表，這個(gè)表包含 orderjd 和 tablejd 兩列 , 根據(jù) orderjd 就可以查詢對(duì)應(yīng)的 table_id砾医。

配置路由設(shè)計(jì)簡(jiǎn)單拿撩，使用起來(lái)非常靈活，尤其是在擴(kuò)充表的時(shí)候如蚜，只需要遷移指定的數(shù)據(jù)压恒，然后修改路由表就可以了影暴。

image.png

配置路由

配置路由的缺點(diǎn)就是必須多查詢一次，會(huì)影響整體性能探赫；而且路由表本身如果太大（例如型宙，幾億條數(shù)據(jù)） ，性能同樣可能成為瓶頸伦吠，如果我們?cè)俅螌⒙酚杀矸謳?kù)分表妆兑，則又面臨一個(gè)死循環(huán)式的路由算法選擇問(wèn)題。

61.不停機(jī)擴(kuò)容怎么實(shí)現(xiàn)毛仪？

實(shí)際上搁嗓，不停機(jī)擴(kuò)容，實(shí)操起來(lái)是個(gè)非常麻煩而且很有風(fēng)險(xiǎn)的操作箱靴，當(dāng)然腺逛，面試回答起來(lái)就簡(jiǎn)單很多。

• 第一階段：在線雙寫(xiě)衡怀，查詢走老庫(kù)
• 建立好新的庫(kù)表結(jié)構(gòu)屉来，數(shù)據(jù)寫(xiě)入久庫(kù)的同時(shí)，也寫(xiě)入拆分的新庫(kù)
• 數(shù)據(jù)遷移狈癞，使用數(shù)據(jù)遷移程序茄靠，將舊庫(kù)中的歷史數(shù)據(jù)遷移到新庫(kù)
• 使用定時(shí)任務(wù)，新舊庫(kù)的數(shù)據(jù)對(duì)比蝶桶，把差異補(bǔ)齊第一階段
• 第二階段：在線雙寫(xiě)慨绳，查詢走新庫(kù)
• 完成了歷史數(shù)據(jù)的同步和校驗(yàn)
• 把對(duì)數(shù)據(jù)的讀切換到新庫(kù)第二階段
• 第三階段：舊庫(kù)下線
• 舊庫(kù)不再寫(xiě)入新的數(shù)據(jù)
• 經(jīng)過(guò)一段時(shí)間，確定舊庫(kù)沒(méi)有請(qǐng)求之后真竖，就可以下線老庫(kù)

image.png

第三階段

62.常用的分庫(kù)分表中間件有哪些脐雪？

• sharding-jdbc
• Mycat

63.那你覺(jué)得分庫(kù)分表會(huì)帶來(lái)什么問(wèn)題呢？

從分庫(kù)的角度來(lái)講：

• 事務(wù)的問(wèn)題

使用關(guān)系型數(shù)據(jù)庫(kù)恢共，有很大一點(diǎn)在于它保證事務(wù)完整性战秋。

而分庫(kù)之后單機(jī)事務(wù)就用不上了，必須使用分布式事務(wù)來(lái)解決讨韭。

• 跨庫(kù) JOIN 問(wèn)題

在一個(gè)庫(kù)中的時(shí)候我們還可以利用 JOIN 來(lái)連表查詢脂信，而跨庫(kù)了之后就無(wú)法使用 JOIN 了。

此時(shí)的解決方案就是在業(yè)務(wù)代碼中進(jìn)行關(guān)聯(lián)透硝，也就是先把一個(gè)表的數(shù)據(jù)查出來(lái)剪决，然后通過(guò)得到的結(jié)果再去查另一張表乍惊，然后利用代碼來(lái)關(guān)聯(lián)得到最終的結(jié)果崎场。

這種方式實(shí)現(xiàn)起來(lái)稍微比較復(fù)雜控乾，不過(guò)也是可以接受的。

還有可以適當(dāng)?shù)娜哂嘁恍┳侄?/strong>。比如以前的表就存儲(chǔ)一個(gè)關(guān)聯(lián) ID丽声，但是業(yè)務(wù)時(shí)常要求返回對(duì)應(yīng)的 Name 或者其他字段礁蔗。這時(shí)候就可以把這些字段冗余到當(dāng)前表中，來(lái)去除需要關(guān)聯(lián)的操作雁社。