[TOC]

數(shù)據(jù)庫(kù)MySQL

數(shù)據(jù)庫(kù)的三范式是什么？什么是反模式？

作用：數(shù)據(jù)庫(kù)范式可以避免數(shù)據(jù)冗余拴还，減少數(shù)據(jù)庫(kù)的空間，并且減輕維護(hù)數(shù)據(jù)完整性的麻煩欧聘。

第一范式（1NF）

第一范式片林，強(qiáng)調(diào)屬性的原子性約束，要求屬性具有原子性怀骤，不可再分解费封。

舉個(gè)例子，活動(dòng)表（活動(dòng)編碼晒喷，活動(dòng)名稱(chēng)孝偎，活動(dòng)地址）访敌，假設(shè)這個(gè)場(chǎng)景中凉敲，活動(dòng)地址可以細(xì)分為國(guó)家、省份、城市爷抓、市區(qū)势决、位置，那么就沒(méi)有達(dá)到第一范式蓝撇。

第二范式（2NF）

第二范式果复，強(qiáng)調(diào)記錄的唯一性約束，表必須有一個(gè)主鍵渤昌，并且沒(méi)有包含在主鍵中的列必須完全依賴(lài)于主鍵虽抄，而不能只依賴(lài)于主鍵的一部分。

舉個(gè)例子独柑，版本表（版本編碼迈窟，版本名稱(chēng)，產(chǎn)品編碼忌栅，產(chǎn)品名稱(chēng)）车酣，其中主鍵是（版本編碼，產(chǎn)品編碼）索绪，這個(gè)場(chǎng)景中湖员，數(shù)據(jù)庫(kù)設(shè)計(jì)并不符合第二范式，因?yàn)楫a(chǎn)品名稱(chēng)只依賴(lài)于產(chǎn)品編碼瑞驱。存在部分依賴(lài)娘摔。所以，為了使其滿(mǎn)足第二范式钱烟，可以改造成兩個(gè)表：版本表（版本編碼晰筛，產(chǎn)品編碼）和產(chǎn)品表（產(chǎn)品編碼，產(chǎn)品名稱(chēng)）拴袭。

第三范式（3NF）

第三范式读第，強(qiáng)調(diào)屬性冗余性的約束，即非主鍵列必須直接依賴(lài)于主鍵拥刻。

舉個(gè)例子怜瞒，訂單表（訂單編碼，顧客編碼般哼，顧客名稱(chēng)）吴汪，其中主鍵是（訂單編碼），這個(gè)場(chǎng)景中蒸眠，顧客編碼漾橙、顧客名稱(chēng)都完全依賴(lài)于主鍵，因此符合第二范式楞卡，但是顧客名稱(chēng)依賴(lài)于顧客編碼霜运，從而間接依賴(lài)于主鍵脾歇，所以不能滿(mǎn)足第三范式。為了使其滿(mǎn)足第三范式淘捡，可以拆分兩個(gè)表：訂單表（訂單編碼藕各，顧客編碼）和顧客表（顧客編碼，顧客名稱(chēng)）焦除，拆分后的數(shù)據(jù)庫(kù)設(shè)計(jì)激况，就可以完全滿(mǎn)足第三范式的要求了。

值得注意的是膘魄，第二范式的側(cè)重點(diǎn)是非主鍵列是否完全依賴(lài)于主鍵乌逐，還是依賴(lài)于主鍵的一部分。第三范式的側(cè)重點(diǎn)是非主鍵列是直接依賴(lài)于主鍵创葡，還是直接依賴(lài)于非主鍵列黔帕。

反模式

范式可以避免數(shù)據(jù)冗余，減少數(shù)據(jù)庫(kù)的空間蹈丸，減輕維護(hù)數(shù)據(jù)完整性的麻煩成黄。
然而，通過(guò)數(shù)據(jù)庫(kù)范式化設(shè)計(jì)逻杖，將導(dǎo)致數(shù)據(jù)庫(kù)業(yè)務(wù)涉及的表變多奋岁，并且可能需要將涉及的業(yè)務(wù)表進(jìn)行多表連接查詢(xún)，這樣將導(dǎo)致性能變差荸百，且不利于分庫(kù)分表闻伶。因此，出于性能優(yōu)先的考量够话，可能在數(shù)據(jù)庫(kù)的結(jié)構(gòu)中需要使用反模式的設(shè)計(jì)蓝翰，即空間換取時(shí)間，采取數(shù)據(jù)冗余的方式避免表之間的關(guān)聯(lián)查詢(xún)女嘲。至于數(shù)據(jù)一致性問(wèn)題畜份，因?yàn)殡y以滿(mǎn)足數(shù)據(jù)強(qiáng)一致性，一般情況下欣尼，使存儲(chǔ)數(shù)據(jù)盡可能達(dá)到用戶(hù)一致爆雹，保證系統(tǒng)經(jīng)過(guò)一段較短的時(shí)間的自我恢復(fù)和修正，數(shù)據(jù)最終達(dá)到一致愕鼓。

MySQL 有哪些數(shù)據(jù)類(lèi)型钙态？

MySQL支持多種類(lèi)型，大致可以分為三類(lèi)：數(shù)值菇晃、日期/時(shí)間和字符串(字符)類(lèi)型册倒。

數(shù)值類(lèi)型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類(lèi)型。包括嚴(yán)格數(shù)值數(shù)據(jù)類(lèi)型(INTEGER磺送、SMALLINT驻子、DECIMAL和NUMERIC)屈尼，以及近似數(shù)值數(shù)據(jù)類(lèi)型(FLOAT、REAL和DOUBLE PRECISION)拴孤。
MySQL也支持整數(shù)類(lèi)型TINYINT、MEDIUMINT和BIGINT甲捏。

日期和時(shí)間類(lèi)型

表示時(shí)間值的日期和時(shí)間類(lèi)型為DATETIME演熟、DATE、TIMESTAMP司顿、TIME和YEAR芒粹。
每個(gè)時(shí)間類(lèi)型有一個(gè)有效值范圍和一個(gè)"零"值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值大溜。

字符串類(lèi)型

字符串類(lèi)型指CHAR（定長(zhǎng)字符串）化漆、VARCHAR（變長(zhǎng)字符串）、BINARY钦奋、VARBINARY座云、BLOB（二進(jìn)制大對(duì)象）、TEXT付材、ENUM和SET朦拖。
4 種 TEXT 類(lèi)型：TINYTEXT、TEXT厌衔、MEDIUMTEXT 和 LONGTEXT

MySQL數(shù)據(jù)類(lèi)型常見(jiàn)問(wèn)題

1璧帝、MySQL 中 varchar 與 char 的區(qū)別？varchar(50) 中的 50 代表的涵義富寿？

• 1睬隶、varchar 與 char 的區(qū)別，char 是一種固定長(zhǎng)度的類(lèi)型页徐，varchar 則是一種可變長(zhǎng)度的類(lèi)型苏潜。
• 2、varchar(50) 中 50 的涵義最多存放 50 個(gè)字符变勇。varchar(50) 和 (200) 存儲(chǔ) hello 所占空間一樣窖贤，但后者在排序時(shí)會(huì)消耗更多內(nèi)存。實(shí)際場(chǎng)景下贰锁，選擇合適的 varchar 長(zhǎng)度還是有必要的赃梧。

2、int(11) 中的 11 代表什么涵義豌熄？

int(11) 中的 11 授嘀，不影響字段存儲(chǔ)的范圍，只影響展示效果锣险，當(dāng)數(shù)字不足11位時(shí)蹄皱，前面會(huì)用0補(bǔ)齊.

3览闰、金額(金錢(qián))相關(guān)的數(shù)據(jù)，選擇什么數(shù)據(jù)類(lèi)型巷折？

• 1压鉴、使用 int 或者 bigint 類(lèi)型。如果需要存儲(chǔ)到分的維度锻拘，需要 *100 進(jìn)行放大油吭。
• 2、使用 decimal 類(lèi)型署拟，避免精度丟失婉宰。如果使用 Java 語(yǔ)言時(shí)，需要使用 BigDecimal 進(jìn)行對(duì)應(yīng)

4推穷、一張表心包，里面有 ID 自增主鍵，當(dāng) insert 了 17 條記錄之后馒铃，刪除了第 15,16,17 條記錄蟹腾，再把 MySQL 重啟，再 insert 一條記錄区宇，這條記錄的 ID 是 18 還是 15岭佳？

一般情況下，我們創(chuàng)建的表的類(lèi)型是 InnoDB 萧锉，如果新增一條記錄（不重啟 MySQL 的情況下）珊随，這條記錄的 ID 是18 ；但是如果重啟 MySQL 的話(huà)柿隙，這條記錄的 ID 是 15 叶洞。因?yàn)?InnoDB 表只把自增主鍵的最大 ID 記錄到內(nèi)存中，所以重啟數(shù)據(jù)庫(kù)或者對(duì)表 OPTIMIZE 操作禀崖，都會(huì)使最大 ID 丟失衩辟。
但是，如果我們使用表的類(lèi)型是 MyISAM 波附，那么這條記錄的 ID 就是 18 艺晴。因?yàn)?MyISAM 表會(huì)把自增主鍵的最大 ID 記錄到數(shù)據(jù)文件里面，重啟 MYSQL 后掸屡，自增主鍵的最大 ID 也不會(huì)丟失封寞。
最后，還可以跟面試官裝個(gè) x 仅财，生產(chǎn)數(shù)據(jù)狈究，不建議進(jìn)行物理刪除記錄。

5盏求、表中有大字段 X(例如：text 類(lèi)型)抖锥，且字段 X 不會(huì)經(jīng)常更新亿眠，以讀為為主，請(qǐng)問(wèn)您是選擇拆成子表磅废，還是繼續(xù)放一起?寫(xiě)出您這樣選擇的理由

• 1.拆帶來(lái)的問(wèn)題：連接消耗 + 存儲(chǔ)拆分空間纳像。
  如果能容忍拆分帶來(lái)的空間問(wèn)題，拆的話(huà)最好和經(jīng)常要查詢(xún)的表的主鍵在物理結(jié)構(gòu)上放置在一起(分區(qū)) 順序 IO 拯勉，減少連接消耗竟趾，最后這是一個(gè)文本列再加上一個(gè)全文索引來(lái)盡量抵消連接消耗。

• 不拆可能帶來(lái)的問(wèn)題：查詢(xún)性能谜喊。
  如果能容忍不拆分帶來(lái)的查詢(xún)性能損失的話(huà)，上面的方案在某個(gè)極致條件下肯定會(huì)出現(xiàn)問(wèn)題倦始，那么不拆就是最好的選擇斗遏。

實(shí)際場(chǎng)景下，例如說(shuō)商品表數(shù)據(jù)量比較大的情況下鞋邑，會(huì)將商品描述單獨(dú)存儲(chǔ)到一個(gè)表中诵次。即，使用拆的方案枚碗。

MySQL 有哪些存儲(chǔ)引擎逾一？

InnoDB
MyISAM
MRG_MYISAM
MEMORY
CSV
ARCHIVE
BLACKHOLE
PERFORMANCE_SCHEMA
FEDERATED
....................

如何選擇合適的存儲(chǔ)引擎？

提供幾個(gè)選擇標(biāo)準(zhǔn)肮雨，然后按照標(biāo)準(zhǔn)遵堵，選擇對(duì)應(yīng)的存儲(chǔ)引擎即可。一個(gè)數(shù)據(jù)庫(kù)中多個(gè)表可以使用不同的引擎以滿(mǎn)足各種性能和實(shí)際需求怨规。
1.是否需要支持事務(wù)陌宿。
2.對(duì)索引和緩存的支持。
3.是否需要使用熱備波丰。
4.崩潰恢復(fù)壳坪，能否接受崩潰。
5.存儲(chǔ)的限制掰烟。

• MySQL 默認(rèn)的存儲(chǔ)引擎是 InnoDB 爽蝴，并且也是最主流的選擇。主要原因如下：
  1.支持事務(wù)纫骑。
  2.支持行級(jí)鎖和表級(jí)鎖蝎亚，能支持更多的并發(fā)量。
  3.查詢(xún)不加鎖先馆，完全不影響查詢(xún)颖对。
  4.支持崩潰后恢復(fù)。

• 在 MySQL5.1 以及之前的版本磨隘，默認(rèn)的存儲(chǔ)引擎是 MyISAM 缤底，但是目前已經(jīng)不再更新顾患，且它有幾個(gè)比較關(guān)鍵的缺點(diǎn)：
  1.不支持事務(wù)。
  2.使用表級(jí)鎖个唧，如果數(shù)據(jù)量大江解，一個(gè)插入操作鎖定表后，其他請(qǐng)求都將阻塞徙歼。

為什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢犁河？

對(duì)于 SELECT COUNT(*) FROM table 語(yǔ)句，在沒(méi)有 WHERE 條件的情況下魄梯，InnoDB 比 MyISAM 可能會(huì)慢很多桨螺，尤其在大表的情況下。因?yàn)槟鸾眨琁nnoDB 是去實(shí)時(shí)統(tǒng)計(jì)結(jié)果灭翔，會(huì)全表掃描；而 MyISAM 內(nèi)部維持了一個(gè)計(jì)數(shù)器辣苏，預(yù)存了結(jié)果肝箱，所以直接返回即可。

什么是索引稀蟋？

索引煌张，類(lèi)似于書(shū)籍目錄，想找到一本書(shū)的某個(gè)特定的主題退客，需要先找到書(shū)的目錄骏融，定位對(duì)應(yīng)的頁(yè)碼。
MySQL 中存儲(chǔ)引擎使用類(lèi)似的方式進(jìn)行查詢(xún)萌狂，先去索引中查找對(duì)應(yīng)的值绎谦，然后根據(jù)匹配的索引找到對(duì)應(yīng)的數(shù)據(jù)行。

1.索引有什么好處粥脚？

1.提高數(shù)據(jù)的檢索速度窃肠，降低數(shù)據(jù)庫(kù)IO成本：使用索引的意義就是通過(guò)縮小表中需要查詢(xún)的記錄的數(shù)目從而加快搜索的速度。
2.降低數(shù)據(jù)排序的成本刷允，降低CPU消耗：索引之所以查的快冤留，是因?yàn)橄葘?shù)據(jù)排好序，若該字段正好需要排序树灶，則正好降低了排序的成本纤怒。

2.索引有什么壞處？

1.占用存儲(chǔ)空間：索引實(shí)際上也是一張表天通，記錄了主鍵與索引字段泊窘，一般以索引文件的形式存儲(chǔ)在磁盤(pán)上。
2.降低更新表的速度：表的數(shù)據(jù)發(fā)生了變化，對(duì)應(yīng)的索引也需要一起變更烘豹，從而減低的更新速度瓜贾。否則索引指向的物理數(shù)據(jù)可能不對(duì)，這也是索引失效的原因之一携悯。

3.索引的使用場(chǎng)景祭芦？

1、對(duì)非常小的表憔鬼，大部分情況下全表掃描效率更高龟劲。
2、對(duì)中大型表轴或，索引非常有效昌跌。
3、特大型的表照雁，建立和使用索引的代價(jià)隨著增長(zhǎng)蚕愤，可以使用分區(qū)技術(shù)來(lái)解決。

4.索引的類(lèi)型囊榜？

索引审胸，都是實(shí)現(xiàn)在存儲(chǔ)引擎層的亥宿。主要有六種類(lèi)型：
1卸勺、普通索引：最基本的索引，沒(méi)有任何約束烫扼。
2曙求、唯一索引：與普通索引類(lèi)似，但具有唯一性約束映企。
3悟狱、主鍵索引：特殊的唯一索引，不允許有空值堰氓。
4挤渐、復(fù)合索引：將多個(gè)列組合在一起創(chuàng)建索引，可以覆蓋多個(gè)列双絮。
5浴麻、外鍵索引：只有InnoDB類(lèi)型的表才可以使用外鍵索引，保證數(shù)據(jù)的一致性囤攀、完整性和實(shí)現(xiàn)級(jí)聯(lián)操作软免。
6、全文索引：MySQL 自帶的全文索引只能用于 InnoDB焚挠、MyISAM 膏萧，并且只能對(duì)英文進(jìn)行全文檢索，一般使用全文索引引擎。

5.MySQL 索引的“創(chuàng)建”原則榛泛？

1蝌蹂、最適合索引的列是出現(xiàn)在 WHERE 子句中的列，或連接子句中的列挟鸠，而不是出現(xiàn)在 SELECT 關(guān)鍵字后的列叉信。
2、索引列的基數(shù)（不重復(fù)的索引值的數(shù)量）越大艘希，索引效果越好硼身。
3、根據(jù)情況創(chuàng)建復(fù)合索引覆享，復(fù)合索引可以提高查詢(xún)效率佳遂。因?yàn)閺?fù)合索引的基數(shù)會(huì)更大。
4撒顿、避免創(chuàng)建過(guò)多的索引丑罪，索引會(huì)額外占用磁盤(pán)空間，降低寫(xiě)操作效率凤壁。
5吩屹、主鍵盡可能選擇較短的數(shù)據(jù)類(lèi)型，可以有效減少索引的磁盤(pán)占用提高查詢(xún)效率拧抖。
6煤搜、對(duì)字符串進(jìn)行索引，應(yīng)該定制一個(gè)前綴長(zhǎng)度唧席，可以節(jié)省大量的索引空間擦盾。

6.MySQL 索引的“使用”注意事項(xiàng)？

1淌哟、應(yīng)盡量避免在 WHERE 子句中使用 != 或 <> 或 not in操作符迹卢，否則將引擎放棄使用索引而進(jìn)行全表掃描。優(yōu)化器將無(wú)法通過(guò)索引來(lái)確定將要命中的行數(shù),因此需要搜索該表的所有行徒仓。column IS NULL 也是不可以使用索引的腐碱。
2、應(yīng)盡量避免在 WHERE 子句中使用 OR 來(lái)連接條件掉弛，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描症见，如：SELECT id FROM t WHERE num = 10 OR num = 20 。
3狰晚、應(yīng)盡量避免在 WHERE 子句中對(duì)字段進(jìn)行表達(dá)式操作筒饰，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
4壁晒、應(yīng)盡量避免在 WHERE 子句中對(duì)字段進(jìn)行函數(shù)操作瓷们，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
5、不要在 WHERE 子句中的 = 左邊進(jìn)行函數(shù)谬晕、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算碘裕，否則系統(tǒng)將可能無(wú)法正確使用索引。
6攒钳、復(fù)合索引遵循前綴原則帮孔。
7、如果 MySQL 評(píng)估使用索引比全表掃描更慢不撑，會(huì)放棄使用索引文兢。如果此時(shí)想要索引，可以在語(yǔ)句中添加強(qiáng)制索引焕檬。
8姆坚、列類(lèi)型是字符串類(lèi)型，查詢(xún)時(shí)一定要給值加引號(hào)实愚，否則索引失效兼呵。
9瓮钥、LIKE 查詢(xún)良狈，% 不能在前氛改，因?yàn)闊o(wú)法使用索引篙悯。如果需要模糊匹配，可以使用全文索引车摄。

7.以下三條 SQL 如何建索引米同，只建一條怎么建翅楼？

WHERE a = 1 AND b = 1
WHERE b = 1
WHERE b = 1 ORDER BY time DESC

以順序 b , a, time 建立復(fù)合索引乎赴，CREATE INDEX table1_b_a_time ON index_test01(b, a, time)忍法。
對(duì)于第一條 SQL 潮尝，因?yàn)樽钚?MySQL 版本會(huì)優(yōu)化 WHERE 子句后面的列順序榕吼，以匹配復(fù)合索引順序。

8.想知道一個(gè)查詢(xún)用到了哪個(gè)索引勉失，如何查看?

EXPLAIN 顯示了 MYSQL 如何使用索引來(lái)處理 SELECT 語(yǔ)句以及連接表,可以幫助選擇更好的索引和寫(xiě)出更優(yōu)化的查詢(xún)語(yǔ)句羹蚣。在 SELECT 語(yǔ)句前加上 EXPLAIN 就可以了。

MySQL 索引的原理乱凿？

什么是 B-Tree 索引

B-Tree 是為磁盤(pán)等外存儲(chǔ)設(shè)備設(shè)計(jì)的一種平衡查找樹(shù)顽素。B-Tree 結(jié)構(gòu)的數(shù)據(jù)可以讓系統(tǒng)高效的找到數(shù)據(jù)所在的磁盤(pán)塊。為了描述B-Tree徒蟆，首先定義一條記錄為一個(gè)二元組 [key, data] 胁出，key 為記錄的鍵值，對(duì)應(yīng)表中的主鍵值段审，data 為一行記錄中除主鍵外的數(shù)據(jù)全蝶。對(duì)于不同的記錄，key值互不相同。

一棵 m 階的 B-Tree 有如下特性：

1. 每個(gè)節(jié)點(diǎn)最多有 m 個(gè)孩子抑淫。
   • 除了根節(jié)點(diǎn)和葉子節(jié)點(diǎn)外绷落，其它每個(gè)節(jié)點(diǎn)至少有 Ceil(m/2) 個(gè)孩子。
   • 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)始苇，則至少有 2 個(gè)孩子砌烁。
2. 所有葉子節(jié)點(diǎn)都在同一層，且不包含其它關(guān)鍵字信息催式。
3. 每個(gè)非葉子節(jié)點(diǎn)包含 n 個(gè)關(guān)鍵字信息（P0,P1,…Pn, k1,…kn）
   • 關(guān)鍵字的個(gè)數(shù) n 滿(mǎn)足：ceil(m/2)-1 <= n <= m-1
   • ki(i=1,…n) 為關(guān)鍵字函喉，且關(guān)鍵字升序排序。
   • Pi(i=0,…n) 為指向子樹(shù)根節(jié)點(diǎn)的指針荣月。P(i-1) 指向的子樹(shù)的所有節(jié)點(diǎn)關(guān)鍵字均小于 ki 函似，但都大于 k(i-1) 。

B-Tree 中的每個(gè)節(jié)點(diǎn)根據(jù)實(shí)際情況可以包含大量的關(guān)鍵字信息和分支喉童，如下圖所示為一個(gè) 3 階的 B-Tree：

B-Tree 的結(jié)構(gòu)

模擬查找 key 為 29 的過(guò)程：

• 1撇寞、根據(jù)根節(jié)點(diǎn)找到磁盤(pán)塊 1 ，讀入內(nèi)存堂氯∶锏＃【磁盤(pán)I/O操作第1次】
• 2、比較 key 29 在區(qū)間（17,35）咽白，找到磁盤(pán)塊 1 的指針 P2 啤握。
• 3、根據(jù) P2 指針找到磁盤(pán)塊 3 晶框，讀入內(nèi)存排抬。【磁盤(pán)I/O操作第2次】
• 4授段、比較 key 29 在區(qū)間（26,30）蹲蒲，找到磁盤(pán)塊3的指針P2。
• 5侵贵、根據(jù) P2 指針找到磁盤(pán)塊 8 届搁，讀入內(nèi)存∏嫌【磁盤(pán)I/O操作第3次】
• 6卡睦、在磁盤(pán)塊 8 中的 key 列表中找到 eky 29 。

分析上面過(guò)程漱抓，發(fā)現(xiàn)需要 3 次磁盤(pán) I/O 操作表锻，和 3 次內(nèi)存查找操作。由于內(nèi)存中的 key 是一個(gè)有序表結(jié)構(gòu)乞娄，可以利用二分法查找提高效率瞬逊。而 3 次磁盤(pán) I/O 操作是影響整個(gè) B-Tree 查找效率的決定因素檐迟。B-Tree 相對(duì)于 AVLTree 縮減了節(jié)點(diǎn)個(gè)數(shù)，使每次磁盤(pán) I/O 取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用码耐，從而提高了查詢(xún)效率追迟。

什么是 B+Tree 索引

B+Tree 是在 B-Tree 基礎(chǔ)上的一種優(yōu)化，使其更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)骚腥，InnoDB存儲(chǔ)引擎就是用 B+Tree 實(shí)現(xiàn)其索引結(jié)構(gòu)敦间。

B+Tree 相對(duì)于 B-Tree 有幾點(diǎn)不同：

• 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息。
• 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針束铭。
• 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中廓块。

將 B-Tree 優(yōu)化，由于 B+Tree 的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息契沫，假設(shè)每個(gè)磁盤(pán)塊能存儲(chǔ) 4 個(gè)鍵值及指針信息带猴，則變成 B+Tree 后其結(jié)構(gòu)如下圖所示：

B+Tree 的結(jié)構(gòu)

• 通常在 B+Tree 上有兩個(gè)頭指針，一個(gè)指向根節(jié)點(diǎn)懈万，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)拴清，而且所有葉子節(jié)點(diǎn)（即數(shù)據(jù)節(jié)點(diǎn)）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)。因此可以對(duì) B+Tree 進(jìn)行兩種查找運(yùn)算：一種是對(duì)于主鍵的范圍查找和分頁(yè)查找会通，另一種是從根節(jié)點(diǎn)開(kāi)始口予，進(jìn)行隨機(jī)查找。

可能上面例子中只有 22 條數(shù)據(jù)記錄涕侈，看不出 B+Tree 的優(yōu)點(diǎn)沪停，下面做一個(gè)推算：

• InnoDB 存儲(chǔ)引擎中頁(yè)的大小為 16KB，一般表的主鍵類(lèi)型為 INT（占用4個(gè)字節(jié)） 或 BIGINT（占用8個(gè)字節(jié)）裳涛，指針類(lèi)型也一般為 4 或 8 個(gè)字節(jié)木张，也就是說(shuō)一個(gè)頁(yè)（B+Tree 中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ) 16KB/(8B+8B)=1K 個(gè)鍵值。也就是說(shuō)一個(gè)深度為 3 的 B+Tree 索引可以維護(hù)10^3 *10^3 *10^3 = 10億 條記錄端三。
• 實(shí)際情況中每個(gè)節(jié)點(diǎn)可能不能填充滿(mǎn)舷礼，因此在數(shù)據(jù)庫(kù)中，B+Tree 的高度一般都在 2~4 層技肩。MySQL 的 InnoDB 存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的且轨，也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要 1~3 次磁盤(pán) I/O 操作浮声。

B+Tree 有哪些索引類(lèi)型

在 B+Tree 中虚婿，根據(jù)葉子節(jié)點(diǎn)的內(nèi)容，索引類(lèi)型分為主鍵索引和非主鍵索引泳挥。

• 主鍵索引的葉子節(jié)點(diǎn)存的數(shù)據(jù)是整行數(shù)據(jù)( 即具體數(shù)據(jù) )然痊。在 InnoDB 里，主鍵索引也被稱(chēng)為聚集索引屉符。
• 非主鍵索引的葉子節(jié)點(diǎn)存的數(shù)據(jù)是整行數(shù)據(jù)的主鍵剧浸，鍵值是索引锹引。在 InnoDB 里，非主鍵索引也被稱(chēng)為輔助索引唆香。

輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)嫌变，而是存儲(chǔ)相應(yīng)行數(shù)據(jù)的聚集索引鍵，即主鍵躬它。當(dāng)通過(guò)輔助索引來(lái)查詢(xún)數(shù)據(jù)時(shí)腾啥，需要進(jìn)過(guò)兩步：

• 首先，InnoDB 存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵冯吓。
• 然后倘待，再通過(guò)主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

什么是索引的最左匹配特性组贺？

當(dāng) B+Tree 的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)凸舵，比如索引 (name, age, sex) 的時(shí)候，B+Tree 是按照從左到右的順序來(lái)建立搜索樹(shù)的失尖。

• 比如當(dāng) (張三, 20, F) 這樣的數(shù)據(jù)來(lái)檢索的時(shí)候啊奄，B+Tree 會(huì)優(yōu)先比較 name 來(lái)確定下一步的所搜方向，如果 name 相同再依次比較 age 和 sex 掀潮，最后得到檢索的數(shù)據(jù)增热。
• 但當(dāng) (20, F) 這樣的沒(méi)有 name 的數(shù)據(jù)來(lái)的時(shí)候，B+Tree 就不知道下一步該查哪個(gè)節(jié)點(diǎn)胧辽，因?yàn)榻⑺阉鳂?shù)的時(shí)候 name 就是第一個(gè)比較因子峻仇，必須要先根據(jù) name 來(lái)搜索才能知道下一步去哪里查詢(xún)。
• 比如當(dāng) (張三, F) 這樣的數(shù)據(jù)來(lái)檢索時(shí)邑商，B+Tree 可以用 name 來(lái)指定搜索方向摄咆，但下一個(gè)字段 age 的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到人断，然后再匹配性別是 F 的數(shù)據(jù)了吭从。

這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性恶迈。

MyISAM 索引實(shí)現(xiàn)

MyISAM 索引的實(shí)現(xiàn)涩金，和 InnoDB 索引的實(shí)現(xiàn)是一樣使用 B+Tree ，差別在于 MyISAM 索引文件和數(shù)據(jù)文件是分離的暇仲，索引文件僅保存數(shù)據(jù)記錄的地址步做。

1）主鍵索引：

MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址奈附。下圖是MyISAM主鍵索引的原理圖：

主鍵索引

• 這里設(shè)表一共有三列全度，假設(shè)我們以 Col1 為主鍵，上圖是一個(gè) MyISAM 表的主索引（Primary key）示意斥滤〗遥可以看出 MyISAM 的索引文件僅僅保存數(shù)據(jù)記錄的地址勉盅。

2）輔助索引：

在 MyISAM 中，主索引和輔助索引在結(jié)構(gòu)上沒(méi)有任何區(qū)別顶掉，只是主索引要求 key 是唯一的草娜，而輔助索引的 key 可以重復(fù)。如果我們?cè)?Col2 上建立一個(gè)輔助索引痒筒，則此索引的結(jié)構(gòu)如下圖所示：

輔助索引

• 同樣也是一顆 B+Tree 驱还，data 域保存數(shù)據(jù)記錄的地址。因此凸克，MyISAM 中索引檢索的算法為首先按照 B+Tree 搜索算法搜索索引议蟆，如果指定的 Key 存在，則取出其 data 域的值萎战，然后以 data 域的值為地址咐容，讀取相應(yīng)數(shù)據(jù)記錄。

MyISAM 的索引方式也叫做“非聚集”的蚂维，之所以這么稱(chēng)呼是為了與InnoDB 的聚集索引區(qū)分戳粒。

MyISAM 索引與 InnoDB 索引的區(qū)別

• InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引虫啥。

• InnoDB 的主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)著行數(shù)據(jù)蔚约，因此主鍵索引非常高效。

• MyISAM 索引的葉子節(jié)點(diǎn)存儲(chǔ)的是行數(shù)據(jù)地址涂籽，需要再尋址一次才能得到數(shù)據(jù)苹祟。

• InnoDB 非主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和其他帶索引的列數(shù)據(jù)，因此查詢(xún)時(shí)做到覆蓋索引會(huì)非常高效评雌。

  覆蓋索引树枫，指的是基于非主鍵索引查詢(xún)，但是查詢(xún)字段只有主鍵 ID 景东，那么在二級(jí)索引中就可以查找到砂轻。

什么是事務(wù)

事務(wù)就是對(duì)一系列的數(shù)據(jù)庫(kù)操作（比如插入多條數(shù)據(jù)）進(jìn)行統(tǒng)一的提交或回滾操作，如果插入成功斤吐，那么一起成功搔涝，如果中間有一條出現(xiàn)異常，那么回滾之前的所有操作和措。
這樣可以防止出現(xiàn)臟數(shù)據(jù)庄呈，防止數(shù)據(jù)庫(kù)數(shù)據(jù)出現(xiàn)問(wèn)題。

事務(wù)的特性臼婆？

事務(wù)的特性是ACID

image.png

1. 原子性 Atomicity ：一個(gè)事務(wù)（transaction）中的所有操作抒痒，或者全部完成，或者全部不完成颁褂，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)故响。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，會(huì)被恢復(fù)（Rollback）到事務(wù)開(kāi)始前的狀態(tài)颁独，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣彩届。即，事務(wù)不可分割誓酒、不可約簡(jiǎn)樟蠕。
2. 一致性 Consistency ：在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞靠柑。拿轉(zhuǎn)賬來(lái)說(shuō)寨辩，假設(shè)用戶(hù)A和用戶(hù)B兩者的錢(qián)加起來(lái)一共是5000，那么不管A和B之間如何轉(zhuǎn)賬歼冰，轉(zhuǎn)幾次賬靡狞，事務(wù)結(jié)束后兩個(gè)用戶(hù)的錢(qián)相加起來(lái)應(yīng)該還得是5000，這就是事務(wù)的一致性隔嫡。
3. 隔離性 Isolation ：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫(xiě)和修改的能力甸怕，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別腮恩，包括讀未提交（Read uncommitted）梢杭、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）秸滴。
4. 持久性 Durability ：事務(wù)處理結(jié)束后武契，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失荡含。

事務(wù)的并發(fā)問(wèn)題吝羞？

實(shí)際場(chǎng)景下，事務(wù)并不是串行的内颗，所以會(huì)帶來(lái)如下三個(gè)問(wèn)題：
1钧排、臟讀：事務(wù) A 讀取了事務(wù) B 更新的數(shù)據(jù)，然后 B 回滾操作均澳，那么 A 讀取到的數(shù)據(jù)是臟數(shù)據(jù)恨溜。
2、不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)找前，事務(wù) B 在事務(wù) A 多次讀取的過(guò)程中糟袁，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù) A 多次讀取同一數(shù)據(jù)時(shí)躺盛，結(jié)果不一致项戴。
3、幻讀：系統(tǒng)管理員 A 將數(shù)據(jù)庫(kù)中所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為 ABCDE 等級(jí)槽惫，但是系統(tǒng)管理員 B 就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄周叮，當(dāng)系統(tǒng)管理員 A 改結(jié)束后發(fā)現(xiàn)還有一條記錄沒(méi)有改過(guò)來(lái)辩撑，就好像發(fā)生了幻覺(jué)一樣，這就叫幻讀仿耽。
小結(jié)：不可重復(fù)讀的和幻讀很容易混淆合冀，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除项贺。解決不可重復(fù)讀的問(wèn)題只需鎖住滿(mǎn)足條件的行君躺，解決幻讀需要鎖表。

請(qǐng)說(shuō)說(shuō) MySQL 的四種事務(wù)隔離級(jí)別开缎？

事務(wù)定義了四種事務(wù)隔離級(jí)別棕叫，不同數(shù)據(jù)庫(kù)在實(shí)現(xiàn)時(shí)，產(chǎn)生的并發(fā)問(wèn)題是不同的奕删。
不同的隔離級(jí)別有不同的現(xiàn)象俺泣，并有不同的鎖定/并發(fā)機(jī)制，隔離級(jí)別越高急侥，數(shù)據(jù)庫(kù)的并發(fā)性就越差砌滞。

• READ UNCOMMITTED（未提交讀）：事務(wù)中的修改，即使沒(méi)有提交坏怪，對(duì)其他事務(wù)也都是可見(jiàn)的贝润。會(huì)導(dǎo)致臟讀。
• READ COMMITTED（提交讀）：事務(wù)從開(kāi)始直到提交之前铝宵，所做的任何修改對(duì)其他事務(wù)都是不可見(jiàn)的打掘。會(huì)導(dǎo)致不可重復(fù)讀。這個(gè)隔離級(jí)別鹏秋，也可以叫做“不可重復(fù)讀”尊蚁。
• REPEATABLE READ（可重復(fù)讀）：一個(gè)事務(wù)按相同的查詢(xún)條件讀取以前檢索過(guò)的數(shù)據(jù)，其他事務(wù)插入了滿(mǎn)足其查詢(xún)條件的新數(shù)據(jù)侣夷。產(chǎn)生幻行横朋。會(huì)導(dǎo)致幻讀。

• SERIALIZABLE（可串行化）：強(qiáng)制事務(wù)串行執(zhí)行百拓。

  
  
  image.png
  
  

  MySQL 默認(rèn)的事務(wù)隔離級(jí)別為可重復(fù)讀（repeatable-read） 琴锭。
  有些資料說(shuō)可重復(fù)讀解決了幻讀，實(shí)際是存在的衙传，可以通過(guò) SELECT xxx FROM t WHERE id = ? FOR UPDATE 的方式决帖，獲得到悲觀鎖，禁止其它事務(wù)操作對(duì)應(yīng)的數(shù)據(jù)蓖捶，從而解決幻讀問(wèn)題地回。同時(shí)還需要知道，即便當(dāng)前記錄不存在，比如 id = 1 是不存在的刻像，當(dāng)前事務(wù)也會(huì)獲得一把記錄鎖（因?yàn)镮nnoDB的行鎖鎖定的是索引畅买，故記錄實(shí)體存在與否沒(méi)關(guān)系，存在就加 行X鎖绎速，不存在就加 next-key lock間隙X鎖）皮获，其他事務(wù)則無(wú)法插入此索引的記錄焙蚓，故杜絕了幻讀纹冤。

Innodb 的事務(wù)與日志的實(shí)現(xiàn)方式

• redo日志（重做日志）
  Innodb是支持事務(wù)的存儲(chǔ)引擎，在事務(wù)提交時(shí)购公，必須先將該事務(wù)的所有日志寫(xiě)入到redo日志文件中萌京，待事務(wù)的commit操作完成才算整個(gè)事務(wù)操作完成。在每次將redo log buffer寫(xiě)入redo log file后宏浩，都需要調(diào)用一次fsync操作知残，因?yàn)橹刈鋈罩揪彌_只是把內(nèi)容先寫(xiě)入操作系統(tǒng)的緩沖系統(tǒng)中，并沒(méi)有確保直接寫(xiě)入到磁盤(pán)上比庄，所以必須進(jìn)行一次fsync操作求妹。因此，磁盤(pán)的性能在一定程度上也決定了事務(wù)提交的性能佳窑。
• undo日志
  undo log和redo log記錄物理日志不一樣制恍，它是邏輯日志∩翊眨可以認(rèn)為當(dāng)delete一條記錄時(shí)净神，undo log中會(huì)記錄一條對(duì)應(yīng)的insert記錄，反之亦然溉委，當(dāng)update一條記錄時(shí)鹃唯，它記錄一條對(duì)應(yīng)相反的update記錄。當(dāng)執(zhí)行回滾時(shí)瓣喊，就可以從undo log中的邏輯記錄讀取到相應(yīng)的內(nèi)容并進(jìn)行回滾坡慌。有時(shí)候應(yīng)用到行版本控制的時(shí)候，也是通過(guò)undo log來(lái)實(shí)現(xiàn)的：當(dāng)讀取的某一行被其他事務(wù)鎖定時(shí)藻三，它可以從undo log中分析出該行記錄以前的數(shù)據(jù)是什么洪橘，從而提供該行版本信息，幫助用戶(hù)實(shí)現(xiàn)一致性非鎖定讀取趴酣。

請(qǐng)說(shuō)說(shuō) MySQL 的鎖機(jī)制梨树？

當(dāng)多個(gè)查詢(xún)同一時(shí)刻進(jìn)行數(shù)據(jù)修改時(shí)，就會(huì)產(chǎn)生并發(fā)控制的問(wèn)題岖寞。MySQL 的共享鎖和排他鎖抡四，就是讀鎖和寫(xiě)鎖。

• 共享鎖：不堵塞，多個(gè)用戶(hù)可以同時(shí)讀一個(gè)資源指巡，互不干擾淑履。
• 排他鎖：一個(gè)寫(xiě)鎖會(huì)阻塞其他的讀鎖和寫(xiě)鎖，這樣可以只允許一個(gè)用戶(hù)進(jìn)行寫(xiě)入藻雪，防止其他用戶(hù)讀取正在寫(xiě)入的資源秘噪。

鎖的粒度？

表鎖：系統(tǒng)開(kāi)銷(xiāo)最小勉耀，會(huì)鎖定整張表指煎，MyIsam 使用表鎖。
行鎖：最大程度的支持并發(fā)處理便斥，但是也帶來(lái)了最大的鎖開(kāi)銷(xiāo)至壤，InnoDB 使用行鎖。

什么是悲觀鎖枢纠？什么是樂(lè)觀鎖像街？

1.悲觀鎖

指的是對(duì)數(shù)據(jù)被外界修改持保守態(tài)度，因此晋渺，在整個(gè)數(shù)據(jù)處理過(guò)程中镰绎，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實(shí)現(xiàn)木西，往往依靠數(shù)據(jù)庫(kù)提供的鎖機(jī)制（也只有數(shù)據(jù)庫(kù)層提供的鎖機(jī)制才能真正保證數(shù)據(jù)訪(fǎng)問(wèn)的排他性畴栖，否則，即使在本系統(tǒng)中實(shí)現(xiàn)了加鎖機(jī)制户魏，也無(wú)法保證外部系統(tǒng)不會(huì)修改數(shù)據(jù)）驶臊。
在悲觀鎖的情況下，為了保證事務(wù)的隔離性叼丑，就需要一致性鎖定讀关翎。讀取數(shù)據(jù)時(shí)給加鎖，其它事務(wù)無(wú)法修改這些數(shù)據(jù)鸠信。修改刪除數(shù)據(jù)時(shí)也要加鎖纵寝，其它事務(wù)無(wú)法讀取這些數(shù)據(jù)。
悲觀鎖星立，就是我們上面看到的共享鎖和排他鎖爽茴。

2.樂(lè)觀鎖

相對(duì)悲觀鎖而言，樂(lè)觀鎖機(jī)制采取了更加寬松的加鎖機(jī)制绰垂。悲觀鎖大多數(shù)情況下依靠數(shù)據(jù)庫(kù)的鎖機(jī)制實(shí)現(xiàn)室奏，以保證操作最大程度的獨(dú)占性。但隨之而來(lái)的就是數(shù)據(jù)庫(kù)性能的大量開(kāi)銷(xiāo)劲装，特別是對(duì)長(zhǎng)事務(wù)而言胧沫，這樣的開(kāi)銷(xiāo)往往無(wú)法承受昌简。
而樂(lè)觀鎖機(jī)制在一定程度上解決了這個(gè)問(wèn)題。樂(lè)觀鎖绒怨，大多是基于數(shù)據(jù)版本Version記錄機(jī)制實(shí)現(xiàn)纯赎。何謂數(shù)據(jù)版本？即為數(shù)據(jù)增加一個(gè)版本標(biāo)識(shí)南蹂，在基于數(shù)據(jù)庫(kù)表的版本解決方案中犬金，一般是通過(guò)為數(shù)據(jù)庫(kù)表增加一個(gè) “version” 字段來(lái)實(shí)現(xiàn)。讀取出數(shù)據(jù)時(shí)六剥，將此版本號(hào)一同讀出晚顷，之后更新時(shí)，對(duì)此版本號(hào)加一仗考。此時(shí)音同，將提交數(shù)據(jù)的版本數(shù)據(jù)與數(shù)據(jù)庫(kù)表對(duì)應(yīng)記錄的當(dāng)前版本信息進(jìn)行比對(duì)词爬，如果提交的數(shù)據(jù)版本號(hào)大于數(shù)據(jù)庫(kù)表當(dāng)前版本號(hào)秃嗜，則予以更新，否則認(rèn)為是過(guò)期數(shù)據(jù)顿膨。
樂(lè)觀鎖實(shí)際就是通過(guò)版本號(hào)锅锨，從而實(shí)現(xiàn) CAS 原子性更新。

什么是死鎖恋沃？

如果一個(gè)資源被鎖定必搞，它總會(huì)在以后某個(gè)時(shí)間被釋放。而死鎖發(fā)生在當(dāng)多個(gè)進(jìn)程訪(fǎng)問(wèn)同一數(shù)據(jù)庫(kù)時(shí)囊咏，其中每個(gè)進(jìn)程擁有的鎖都是其他進(jìn)程所需的恕洲，由此造成每個(gè)進(jìn)程都無(wú)法繼續(xù)下去。簡(jiǎn)單的說(shuō)梅割，進(jìn)程 A 等待進(jìn)程 B 釋放他的資源霜第，B 又等待 A 釋放他的資源，這樣就互相等待就形成死鎖户辞。

雖然進(jìn)程在運(yùn)行過(guò)程中泌类，可能發(fā)生死鎖，但死鎖的發(fā)生也必須具備一定的條件底燎，死鎖的發(fā)生必須具備以下四個(gè)必要條件：

• 互斥條件：指進(jìn)程對(duì)所分配到的資源進(jìn)行排它性使用刃榨，即在一段時(shí)間內(nèi)某資源只由一個(gè)進(jìn)程占用。如果此時(shí)還有其它進(jìn)程請(qǐng)求資源双仍，則請(qǐng)求者只能等待枢希，直至占有資源的進(jìn)程用畢釋放。
• 請(qǐng)求和保持條件：指進(jìn)程已經(jīng)保持至少一個(gè)資源朱沃，但又提出了新的資源請(qǐng)求苞轿，而該資源已被其它進(jìn)程占有，此時(shí)請(qǐng)求進(jìn)程阻塞，但又對(duì)自己已獲得的其它資源保持不放呕屎。
• 不剝奪條件：指進(jìn)程已獲得的資源让簿，在未使用完之前，不能被剝奪秀睛，只能在使用完時(shí)由自己釋放尔当。
• 環(huán)路等待條件：指在發(fā)生死鎖時(shí)，必然存在一個(gè)進(jìn)程——資源的環(huán)形鏈蹂安，即進(jìn)程集合 {P0椭迎，P1，P2田盈，???畜号，Pn} 中的 P0 正在等待一個(gè) P1 占用的資源；P1 正在等待 P2 占用的資源允瞧，……简软，Pn 正在等待已被 P0 占用的資源。

如何最大限度地降低死鎖

• 設(shè)置獲得鎖的超時(shí)時(shí)間述暂。通過(guò)超時(shí)痹升，至少保證最差最差最差情況下，可以有退出的口子畦韭。
• 按同一順序訪(fǎng)問(wèn)對(duì)象疼蛾。
• 避免事務(wù)中的用戶(hù)交互。
• 保持事務(wù)簡(jiǎn)短并在一個(gè)批處理中艺配。
• 使用低隔離級(jí)別察郁。
• 使用綁定連接。

MySQL 中 InnoDB 引擎的行鎖是通過(guò)加在什么上實(shí)現(xiàn)的转唉？為什么是這樣子的皮钠？？

InnoDB 是基于索引來(lái)完成行鎖酝掩。例如：SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE 鳞芙。
FOR UPDATE 可以根據(jù)條件來(lái)完成行鎖鎖定，并且 id 是有索引鍵的列,如果 id 不是索引鍵那么 InnoDB 將完成表鎖期虾，并發(fā)將無(wú)從談起原朝。

Innodb 的行鎖是怎么實(shí)現(xiàn)的？

Innodb 的鎖的策略為 next-key 鎖镶苞，即 record lock + gap lock 喳坠，是通過(guò)在 index 上加 lock 實(shí)現(xiàn)的。
如果 index 為 unique index 茂蚓，則降級(jí)為 record lock 行鎖壕鹉。
如果是普通 index 剃幌，則為 next-key lock 。
如果沒(méi)有 index 晾浴，則直接鎖住全表负乡，即表鎖。

MySQL 查詢(xún)執(zhí)行順序脊凰？

(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
在SQL語(yǔ)句的執(zhí)行過(guò)程中抖棘，每一步都會(huì)產(chǎn)生一個(gè)虛擬表（Virtual Table，簡(jiǎn)稱(chēng)VT）狸涌，用來(lái)保存SQL語(yǔ)句的執(zhí)行結(jié)果

當(dāng)向MySQL發(fā)送一個(gè)請(qǐng)求的時(shí)候切省，MySQL到底做了些什么呢

image.png

• 1.客戶(hù)端向MySQL服務(wù)器發(fā)送一條查詢(xún)請(qǐng)求
• 2.服務(wù)器首先檢查查詢(xún)緩存，如果命中緩存帕胆，則立刻返回存儲(chǔ)在緩存中的結(jié)果朝捆。否則進(jìn)入下一階段
• 3.服務(wù)器進(jìn)行SQL解析、預(yù)處理懒豹、再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
• 4.MySQL根據(jù)執(zhí)行計(jì)劃芙盘，調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢(xún)
• 5.將結(jié)果返回給客戶(hù)端，同時(shí)緩存查詢(xún)結(jié)果

什么是 MVCC 歼捐？

多版本并發(fā)控制（MVCC）何陆，是一種用來(lái)解決讀（快照讀）-寫(xiě)沖突的無(wú)鎖并發(fā)控制，也就是為事務(wù)分配單向增長(zhǎng)的時(shí)間戳豹储，為每個(gè)修改保存一個(gè)版本，版本與事務(wù)時(shí)間戳關(guān)聯(lián)淘这，讀操作只讀該事務(wù)開(kāi)始前的數(shù)據(jù)庫(kù)的快照剥扣。 這樣在讀操作不用阻塞寫(xiě)操作，寫(xiě)操作不用阻塞讀操作的同時(shí)铝穷，避免了臟讀和不可重復(fù)讀钠怯。快照讀就是MVCC思想在MySQL的具體非阻塞讀功能實(shí)現(xiàn)曙聂。

MVCC主要是為Repeatable-Read事務(wù)隔離級(jí)別做的晦炊。在此隔離級(jí)別下，A宁脊、B事務(wù)所示的數(shù)據(jù)相互隔離断国，互相更新不可見(jiàn)。

什么是MySQL InnoDB下的當(dāng)前讀和快照讀?

• 當(dāng)前讀
  當(dāng)前讀實(shí)際上是一種加鎖的操作榆苞，是悲觀鎖的實(shí)現(xiàn).
  select * from table where ? lock in share mode; （加S鎖）
  select * from table where ? for update; （加X(jué)鎖）
  update, insert ,delete(排他鎖)這些操作都是一種當(dāng)前讀稳衬，為什么叫當(dāng)前讀？就是它讀取的是記錄的最新版本坐漏，讀取時(shí)還要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄薄疚，會(huì)對(duì)讀取的記錄進(jìn)行加鎖
• 快照讀
  像不加鎖的select操作就是快照讀碧信，即不加鎖的非阻塞讀；快照讀的前提是隔離級(jí)別不是串行級(jí)別街夭，串行級(jí)別下的快照讀會(huì)退化成當(dāng)前讀砰碴；之所以出現(xiàn)快照讀的情況，是基于提高并發(fā)性能的考慮板丽，快照讀的實(shí)現(xiàn)是基于多版本并發(fā)控制衣式，即MVCC,可以認(rèn)為MVCC是行鎖的一個(gè)變種，但它在很多情況下檐什，避免了加鎖操作碴卧，降低了開(kāi)銷(xiāo)；既然是基于多版本乃正，即快照讀可能讀到的并不一定是數(shù)據(jù)的最新版本住册，而有可能是之前的歷史版本.

MySQL 數(shù)據(jù)庫(kù) CPU 飆升到 500% 的話(huà)，怎么處理瓮具？

• 當(dāng) CPU 飆升到 500% 時(shí)荧飞，先用操作系統(tǒng)命令 top 命令觀察是不是 mysqld 占用導(dǎo)致的，如果不是名党，找出占用高的進(jìn)程叹阔，并進(jìn)行相關(guān)處理。
• 如果此時(shí)是 IO 壓力比較大传睹，可以使用 iostat 命令耳幢，定位是哪個(gè)進(jìn)程占用了磁盤(pán) IO 。如果是 mysqld 造成的欧啤，使用 show processlist 命令睛藻，看看里面跑的 Session 情況，是不是有消耗資源的 SQL 在運(yùn)行邢隧。找出消耗高的 SQL 旺罢，看看執(zhí)行計(jì)劃是否準(zhǔn)確陨舱， index 是否缺失涌矢，或者實(shí)在是數(shù)據(jù)量太大造成枚驻。一般來(lái)說(shuō)，肯定要 kill 掉這些線(xiàn)程(同時(shí)觀察 CPU 使用率是否下降)纫谅，等進(jìn)行相應(yīng)的調(diào)整(比如說(shuō)加索引炫贤、改 SQL 、改內(nèi)存參數(shù))之后系宜，再重新跑這些 SQL照激。
• 也可以查看 MySQL 慢查詢(xún)?nèi)罩荆词欠裼新?SQL 盹牧。
• 也有可能是每個(gè) SQL 消耗資源并不多俩垃，但是突然之間励幼，有大量的 Session 連進(jìn)來(lái)導(dǎo)致 CPU 飆升，這種情況就需要跟應(yīng)用一起來(lái)分析為何連接數(shù)會(huì)激增口柳，再做出相應(yīng)的調(diào)整苹粟，比如說(shuō)限制連接數(shù)等。

編寫(xiě) SQL 查詢(xún)語(yǔ)句的考題合集

《10 道 MySQL 查詢(xún)語(yǔ)句面試題》

聊聊 MySQL SQL 優(yōu)化跃闹？

1.對(duì)查詢(xún)進(jìn)行優(yōu)化嵌削，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引望艺。
2.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷苛秕，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：select id from t where num is null可以在num上設(shè)置默認(rèn)值0找默，確保表中num列沒(méi)有null值艇劫，然后這樣查詢(xún)：select id from t where num=0
3.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描惩激。
4.應(yīng)盡量避免在 where 子句中使用or 來(lái)連接條件店煞，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢(xún)：select id from t where num=10 union all select id from t where num=20
5.in 和 not in 也要慎用风钻，否則會(huì)導(dǎo)致全表掃描顷蟀，如：select id from t where num in(1,2,3) 對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
6.避免使用通配符骡技。下面的查詢(xún)也將導(dǎo)致全表掃描：select id from t where name like ‘李%’若要提高效率鸣个，可以考慮全文檢索。
7.如果在 where 子句中使用參數(shù)哮兰，也會(huì)導(dǎo)致全表掃描毛萌。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪(fǎng)問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)喝滞；它必須在編譯時(shí)進(jìn)行選擇。然而膏秫，如果在編譯時(shí)建立訪(fǎng)問(wèn)計(jì)劃右遭，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項(xiàng)缤削。如下面語(yǔ)句將進(jìn)行全表掃描：select id from t where num=@num可以改為強(qiáng)制查詢(xún)使用索引：select id from t with(index(索引名)) where num=@num
8.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作窘哈，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：select id from t where num/2=100應(yīng)改為:select id from t where num=1002
9.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作亭敢，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描滚婉。如：select id from t where substring(name,1,3)=’abc’ ，name以abc開(kāi)頭的id應(yīng)改為:select id from t where name like ‘a(chǎn)bc%’
10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)帅刀、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算让腹，否則系統(tǒng)將可能無(wú)法正確使用索引远剩。
11.在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引骇窍，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引瓜晤，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致腹纳。
12.不要寫(xiě)一些沒(méi)有意義的查詢(xún)痢掠，如需要生成一個(gè)空表結(jié)構(gòu)：select col1,col2 into #t from t where 1=0 這類(lèi)代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的嘲恍，應(yīng)改成這樣：create table #t(…)
13.很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：select num from a where num in(select num from b)用下面的語(yǔ)句替換：select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引對(duì)查詢(xún)都有效足画，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢(xún)優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)佃牛，SQL查詢(xún)可能不會(huì)去利用索引淹辞，如一表中有字段sex，male吁脱、female幾乎各一半桑涎，那么即使在sex上建了索引也對(duì)查詢(xún)效率起不了作用。
15.索引并不是越多越好兼贡，索引固然可以提高相應(yīng)的 select 的效率攻冷，但同時(shí)也降低了insert 及 update 的 效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引遍希，所以怎樣建索引需要慎重考慮等曼，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)凿蒜，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要禁谦。
16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ) 順序废封，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整州泊，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列漂洋，那么需要考慮是否應(yīng)將該索引建為 clustered 索引遥皂。
17.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型刽漂，這會(huì)降低查詢(xún)和連接的性能演训，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。這是因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符贝咙，而對(duì)于數(shù)字型而言只需要比較一次就夠了样悟。
18.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間窟她，其次對(duì)于查詢(xún)來(lái)說(shuō)陈症，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。
19.任何地方都不要使用 select * from t 礁苗，用具體的字段列表代替“”爬凑，不要返回用不到的任何字段。
20.盡量使用表變量來(lái)代替臨時(shí)表试伙。如果表變量包含大量數(shù)據(jù)嘁信，請(qǐng)注意索引非常有限（只有主鍵索引）。
21.避免頻繁創(chuàng)建和刪除臨時(shí)表疏叨，以減少系統(tǒng)表資源的消耗潘靖。
22.臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行г槁缲砸纾?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是秀又，對(duì)于一次性事件单寂，最好使用導(dǎo)出表。
23.在新建臨時(shí)表時(shí)吐辙，如果一次性插入數(shù)據(jù)量很大宣决，那么可以使用 select into 代替 create table，避免造成大量 log 昏苏，以提高速度尊沸；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源贤惯，應(yīng)先create table洼专，然后insert。
24.如果使用到了臨時(shí)表孵构，在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除屁商，先 truncate table ，然后 drop table 颈墅，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定棒假。
25.盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差精盅，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫(xiě)谜酒。
26.使用基于游標(biāo)的方法或臨時(shí)表方法之前叹俏，應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效僻族。
27.與臨時(shí)表一樣粘驰，游標(biāo)并不是不可使用屡谐。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)蝌数。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快愕掏。如果開(kāi)發(fā)時(shí)間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下顶伞，看哪一種方法的效果更好饵撑。
28.在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開(kāi)始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF唆貌。無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶(hù)端發(fā)送DONE_IN_PROC 消息滑潘。
29.盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力锨咙。
30.盡量避免向客戶(hù)端返回大數(shù)據(jù)量语卤，若數(shù)據(jù)量過(guò)大，應(yīng)該考慮相應(yīng)需求是否合理酪刀。