Mysql面試總結(jié)-背誦版 V0.1

1. mysql基礎(chǔ)

1.MySQL的默認(rèn)端?號(hào)是3306。

1.1 InnoDB和MyISAM存儲(chǔ)引擎

MyISAM和InnoDB區(qū)別
主要有5點(diǎn)，關(guān)鍵字為：1.事務(wù) 2.外鍵 3.聚簇索引 4.行級(jí)鎖 5. MVCC(多版本并發(fā)控制)

  1. InnoDB 支持事務(wù)，MyISAM 不支持事務(wù)。
  2. InnoDB 支持外鍵，而 MyISAM 不支持。
  3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主鍵索引的葉子節(jié)點(diǎn)上，因此 InnoDB 必須要有主鍵，通過主鍵索引效率很高。而 MyISAM 是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨(dú)立的。
  4. InnoDB ?持?級(jí)鎖(row?level locking)和表級(jí)鎖,默認(rèn)為?級(jí)鎖，MyISAM 只有表級(jí)鎖(table-level locking).
  5. InnoDB 支持MVCC（多版本并發(fā)控制）：僅InnoDB 支持。應(yīng)對(duì)高并發(fā)事務(wù)，MVCC 比單純的加鎖更加高效；MVCC 只在 EAD COMMITTED(讀已提交) 和 REPEATABLE READ(可重復(fù)讀) 兩個(gè)級(jí)別下工作；MVCC 可以使用樂觀鎖（optimistic）和 悲觀鎖（pessimistic）來實(shí)現(xiàn)；各數(shù)據(jù)庫(kù)中實(shí)現(xiàn)MVCC的方式并不統(tǒng)一。

兩者如何選擇？

?多數(shù)時(shí)候我們使?的都是 InnoDB 存儲(chǔ)引擎，但是在某些情況下使? MyISAM 也是合適的?如讀密集的情況下。（如果你不介意 MyISAM 崩潰恢復(fù)問題的話）。

1.2 索引

主要有兩種索引，B+Tree索引和哈希索引，對(duì)于哈希索引來說，底層的數(shù)據(jù)結(jié)構(gòu)就是哈希表，因此在絕?多數(shù)需求為單條記錄查詢的時(shí)候，可以選擇哈希索引，查詢性能最快；其余?部分場(chǎng)景，建議選擇BTree索引。

在 MyISAM引擎下，B+Tree索引為非聚簇索引，節(jié)點(diǎn)保存了數(shù)據(jù)。而在InnoDB引擎，索引?件和數(shù)據(jù)?件是分離的，在根據(jù)主索引搜索時(shí)，直接找到key所在的節(jié)點(diǎn)即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時(shí)，則需要先取出主鍵的值，再??遍主索引。 因此，在設(shè)計(jì)表的時(shí)候，不建議使?過?的字段作為主鍵，也不建議使??單調(diào)的字段作為主鍵，這樣會(huì)造成主索引頻繁分裂

1.3 數(shù)據(jù)庫(kù)緩存

開啟查詢緩存后在同樣的查詢條件以及數(shù)據(jù)情況下，會(huì)直接在緩存中返回結(jié)果。緩存雖然能夠提升數(shù)據(jù)庫(kù)的查詢性能，但是緩存同時(shí)也帶來了額外的開銷，每次查詢后都要做?次緩存操作，失效后還要銷毀。

緩存雖然能夠提升數(shù)據(jù)庫(kù)的查詢性能，但是緩存同時(shí)也帶來了額外的開銷，每次查詢后都要做?次緩存操作，失效后還要銷毀。

還可以通過sql_cache和sql_no_cache來控制某個(gè)查詢語(yǔ)句是否需要緩存。

1.4 事務(wù)

事務(wù)是邏輯上的?組操作，要么都執(zhí)行，要么都不執(zhí)行。

事務(wù)的4個(gè)特性：

  1. 原?性（Atomicity）： 事務(wù)是最?的執(zhí)?單位，不允許分割。事務(wù)的原?性確保動(dòng)作要么全部完成，要么完全不起作?；
  2. ?致性（Consistency）： 執(zhí)?事務(wù)前后，數(shù)據(jù)保持?致，多個(gè)事務(wù)對(duì)同?個(gè)數(shù)據(jù)讀取的結(jié)果是相同的；
  3. 隔離性（Isolation）： 并發(fā)訪問數(shù)據(jù)庫(kù)時(shí)，?個(gè)?戶的事務(wù)不被其他事務(wù)所?擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫(kù)是獨(dú)?的；
  4. 持久性（Durability）： ?個(gè)事務(wù)被提交之后。它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫(kù)發(fā)?故障也不應(yīng)該對(duì)其有任何影響。

1.5 并發(fā)事務(wù)帶來哪些問題

1.5.1 并發(fā)事務(wù)帶來哪些問題

  1. 臟讀（Dirty read）：B事務(wù)讀到了A事務(wù)已修改但未提交的數(shù)據(jù)。
  2. 丟失修改（Lost to modify）：這樣第?個(gè)事務(wù)內(nèi)的修改結(jié)果就被丟失，因此稱為丟失修改。
  3. 不可重復(fù)讀（Unrepeatableread）: 指在?個(gè)事務(wù)內(nèi)多次讀同?數(shù)據(jù)，由于第?個(gè)事務(wù)的修改導(dǎo)致第?個(gè)事務(wù)兩次讀取的數(shù)據(jù)可能不太?樣。
  4. 幻讀（Phantom read）:幻讀與不可重復(fù)讀類似。它發(fā)?在?個(gè)事務(wù)（T1）讀取了??數(shù)據(jù)，接著另?個(gè)并發(fā)事務(wù)（T2）插?了?些數(shù)據(jù)時(shí)。在隨后的查詢中，第?個(gè)事務(wù)（T1）就會(huì)發(fā)現(xiàn)多了?些原本不存在的記錄，就好像發(fā)?了幻覺?樣，所以稱為幻讀。

1.5.2 事務(wù)隔離級(jí)別有哪些?MySQL的默認(rèn)隔離級(jí)別是?

SQL 標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別：
READ-UNCOMMITTED(讀取未提交)： 最低的隔離級(jí)別，允許讀取尚未提交的數(shù)據(jù)變更，可能會(huì)導(dǎo)致臟讀、幻讀或不可重復(fù)讀。
READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻?臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)?。
REPEATABLE-READ(可重復(fù)讀)： 對(duì)同?字段的多次讀取結(jié)果都是?致的，除?數(shù)據(jù)是被本身事務(wù)??所修改，可以阻?臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)?。
SERIALIZABLE(可串?化)： 最?的隔離級(jí)別，完全服從ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)?，這樣事務(wù)之間就完全不可能產(chǎn)??擾，也就是說，該級(jí)別可以防?臟讀、不可重復(fù)讀以及幻讀。

數(shù)據(jù)庫(kù)隔離級(jí)別.png

1.5.3 鎖機(jī)制與InnoDB鎖算法

MyISAM和InnoDB存儲(chǔ)引擎使?的鎖：

  1. MyISAM采?表級(jí)鎖(table-level locking)。
  2. InnoDB?持?級(jí)鎖(row-level locking)和表級(jí)鎖,默認(rèn)為?級(jí)鎖

表級(jí)鎖和行級(jí)鎖可以進(jìn)一步劃分為共享鎖（s）（讀鎖）和排他鎖（X）（寫鎖）

InnoDB存儲(chǔ)引擎的鎖的算法有三種：
Record lock：?jiǎn)蝹€(gè)?記錄上的鎖
Gap lock：間隙鎖，鎖定?個(gè)范圍，不包括記錄本身
Next-key lock：record+gap 鎖定?個(gè)范圍，包含記錄本身

1.6 大表優(yōu)化

  1. 限定數(shù)據(jù)的范圍。務(wù)必禁?不帶任何限制數(shù)據(jù)范圍條件的查詢語(yǔ)句。
  2. 讀/寫分離。經(jīng)典的數(shù)據(jù)庫(kù)拆分?案，主庫(kù)負(fù)責(zé)寫，從庫(kù)負(fù)責(zé)讀；
  3. 垂直分區(qū)。根據(jù)數(shù)據(jù)庫(kù)??數(shù)據(jù)表的相關(guān)性進(jìn)?拆分。簡(jiǎn)單來說垂直拆分是指數(shù)據(jù)表列的拆分，把?張列?較多的表拆分為多張表。

垂直拆分的優(yōu)點(diǎn)： 可以使得列數(shù)據(jù)變?，在查詢時(shí)減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡(jiǎn)化表的結(jié)構(gòu)，易于維護(hù)。
垂直拆分的缺點(diǎn)*： 主鍵會(huì)出現(xiàn)冗余，需要管理冗余列，并會(huì)引起Join操作，可以通過在應(yīng)?層進(jìn)?Join來解決。此外，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜；

  1. 水平拆分。保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過某種策略存儲(chǔ)數(shù)據(jù)分?。這樣每??數(shù)據(jù)分散到不同的表或者庫(kù)中，達(dá)到了分布式的?的。 ?平拆分可以?撐?常?的數(shù)據(jù)量。
    水平拆分最好分庫(kù) 。 ?平拆分能夠?持?常?的數(shù)據(jù)量存儲(chǔ)，應(yīng)?端改造也少，但 分?事務(wù)難以解決 ，跨節(jié)點(diǎn)Join性能差，邏輯復(fù)雜。《Java?程師修煉之道》的作者推薦 盡量不要對(duì)數(shù)據(jù)進(jìn)?分?，因?yàn)椴鸱謺?huì)帶來邏輯、部署、運(yùn)維的各種復(fù)雜度 ，?般的數(shù)據(jù)表在優(yōu)化得當(dāng)?shù)那闆r下?撐千萬以下的數(shù)據(jù)量是沒有太?問題的。如果實(shí)在要分?，盡量選擇客戶端分?架構(gòu)，這樣可以減少?次和中間件的?絡(luò)I/O。

1.6.1 分庫(kù)分表之后,id 主鍵如何處理？

因?yàn)橐欠殖啥鄠€(gè)表之后，每個(gè)表都是從 1 開始累加，這樣是不對(duì)的，我們需要?個(gè)全局唯?的id 來?持。
?成全局 id 有下?這?種?式：

  1. UUID：不適合作為主鍵，因?yàn)樘?了，并且?序不可讀，查詢效率低。??適合?于?成唯?的名字的標(biāo)示?如?件的名字。
  2. **數(shù)據(jù)庫(kù)?增 id **: 兩臺(tái)數(shù)據(jù)庫(kù)分別設(shè)置不同步?，?成不重復(fù)ID的策略來實(shí)現(xiàn)?可?。這種?式?成的 id 有序，但是需要獨(dú)?部署數(shù)據(jù)庫(kù)實(shí)例，成本?，還會(huì)有性能瓶頸。
  3. 利? redis ?成 id : 性能??好，靈活?便，不依賴于數(shù)據(jù)庫(kù)。但是，引?了新的組件造成系統(tǒng)更加復(fù)雜，可?性降低，編碼更加復(fù)雜，增加了系統(tǒng)成本。
  4. Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
  5. 美團(tuán)的Leaf分布式ID?成系統(tǒng) ：Leaf 是美團(tuán)開源的分布式ID?成器，能保證全局唯?性、趨勢(shì)遞增、單調(diào)遞增、信息安全，??也提到了?種分布式?案的對(duì)?，但也需要依賴關(guān)系數(shù)據(jù)庫(kù)、Zookeeper等中間件。感覺還不錯(cuò)。美團(tuán)技術(shù)團(tuán)隊(duì)的?篇?章：https://tech.meituan.com/2017/04/21/mt-leaf.html
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容