1. 事務(wù)
ACID 特性
- 原子性(Atomicity)：事務(wù)是一個不可分割的單位，因此在一個事務(wù)里的所有操作要么全部生效，要么全部不生效。
- 一致性(Consistency)：也可以理解為是預(yù)期狀態(tài)的正確性，即從一個正確的狀態(tài)到另一個正確的狀態(tài)，這里的狀態(tài)往往是由業(yè)務(wù)來定義的。比如轉(zhuǎn)賬中的一個扣錢一個加錢，是我們規(guī)定的一個數(shù)據(jù)流轉(zhuǎn)，那么執(zhí)行前的賬戶余額和轉(zhuǎn)賬后的賬戶余額就得滿足加減特性，這就是所謂的業(yè)務(wù)正確。
- 隔離性(Isolation)：事務(wù)并發(fā)執(zhí)行時，各個事務(wù)之間相互影響的程度。
- 持久化(Durability)：通過日志等手段，只要我們的事務(wù)提交成功了，那么就意味著這次的數(shù)據(jù)操作是成功的。即使下次重啟了程序，也不會丟失此處的操作結(jié)果。
隔離級別
- 未提交讀： 即所謂的臟讀，事務(wù)讀取的數(shù)據(jù)可能是另一個事務(wù)已修改但還沒提交的，這部分?jǐn)?shù)據(jù)有可能產(chǎn)生回滾。導(dǎo)致后續(xù)的操作依賴了無效的數(shù)據(jù)。
- 已提交讀： 如果想防止臟讀，就需要等待其他事務(wù)提交后再進(jìn)行讀取操作。
- 可重復(fù)讀： 已提交讀的隔離級別考慮到了數(shù)據(jù)回滾的無效性，卻無法阻止事務(wù)的多次提交。比如事務(wù) A 不斷的對表進(jìn)行修改提交，那么事務(wù) B 就會在不同的時間點讀取到不同的數(shù)據(jù)。為了讓事務(wù) B 在執(zhí)行期間讀取的數(shù)據(jù)都是一致的，就有了可重復(fù)讀的隔離級別，即事務(wù) B 在執(zhí)行期間，其他事務(wù)不得進(jìn)行修改操作。
- 可串行化： 上面的可重復(fù)讀隔離級別保證了事務(wù)執(zhí)行期間讀取的一致性。然而這里并不包括插入、刪除操作。即會出現(xiàn)讀多讀少數(shù)據(jù)的情況，這種現(xiàn)象叫做幻讀。為了解決幻讀，只得進(jìn)行串行化執(zhí)行事務(wù)，才能互不影響。而此時的事務(wù)并發(fā)性是最低的
2. 索引
Mysql 的索引分類
- 從數(shù)據(jù)結(jié)構(gòu)劃分： B+ 樹、hash 索引、全文索引
- 從物理結(jié)構(gòu)劃分： 聚集索引、非聚集索引
- 從邏輯用戶劃分： 主鍵、唯一索引、復(fù)合索引、普通單列索引
聚集索引、非聚集索引、主建的區(qū)別
- 聚集索引：在索引的葉子節(jié)點直接存 data 數(shù)據(jù), 使用的是物理排序，一個表只能有一個字段設(shè)置了聚集索引（因為物理排序規(guī)則只能有一個），允許有 null 的數(shù)據(jù)存在，數(shù)據(jù)也不一定是唯一的。
- 主建：唯一標(biāo)識某行記錄，不允許有 null 的數(shù)據(jù)，要求數(shù)據(jù)必須唯一。在設(shè)置某個字段為主建時，數(shù)據(jù)庫一般會自動在這個主建上建立一個唯一索引，并且如果之前表沒有創(chuàng)建過聚集索引，還會在這個主建上建立一個聚集索引。
- 非聚集索引：索引的葉子節(jié)點存的是數(shù)據(jù)域的記錄指針，需要跳轉(zhuǎn)查找。排序規(guī)則是邏輯排序，因此可以有多個非聚集索引存在。
有哪些情況會讓索引失效？
- 在 where 字段 上使用了函數(shù)或其他隱式轉(zhuǎn)換
- Like 模糊查詢，開頭使用了 "%"，例如 like '%hello%'
- where 條件里使用了 or
- 建立了復(fù)合索引，但 where 條件里使用的是第二個字段的搜索
最左匹配原則是指？
mysql 建立聯(lián)合索引后，是按最左匹配原則來篩選記錄的，即檢索數(shù)據(jù)是從聯(lián)合索引的第一個字段來篩選的。如果 where 里的條件只有第二個字段，那么將無法應(yīng)用到索引。
索引的底層數(shù)據(jù)結(jié)構(gòu) B+ 樹是怎么樣的？
B+ 樹是二叉搜索樹的一個擴(kuò)充，是多路搜索樹。它只在葉子節(jié)點存儲具體的數(shù)據(jù)或者數(shù)據(jù)的指向指針，而非葉子節(jié)點存放索引數(shù)據(jù)。這樣可以降低磁盤 IO，還能充分利用磁盤的預(yù)讀功能，批量的加載索引數(shù)據(jù)。
b 樹 b+樹 b-樹的區(qū)別
- b 樹就是 b-樹， 國外叫 b-tree, 也就是 b 樹。
- b-tree 是在非葉子節(jié)點存放了數(shù)據(jù)，在查詢索引時，只要找到索引值也就可以找到數(shù)據(jù)了，這樣可以提前終止搜索。但每個節(jié)點就得存儲索引值+數(shù)據(jù)值，占用的頁空間會比較大，需要的磁盤 io 次數(shù)也會變多，即使是不需要關(guān)心的數(shù)據(jù)也會被預(yù)加載出來，浪費(fèi)性能。
- b+樹是將索引值存在非葉子節(jié)點，數(shù)據(jù)值存在葉子節(jié)點，這樣可以壓縮樹的高度，減少磁盤 io。
為什么不能在重復(fù)率高，例如性別字段上建立索引？
對于性別這種索引， 由于重復(fù)率高，對于 B+樹(多路搜索樹)來講，得遍歷多條路徑，搜索代價大。還不如全表掃描，這樣不需要維護(hù)索引，降低開銷。
Mysql 的 hash 索引是怎么樣，有什么優(yōu)缺點？
hash 索引將列通過 hash 運(yùn)算得到 hash code，然后將 hash code 跟數(shù)據(jù)行的指針地址關(guān)聯(lián)在一起，下次查找時只需查找對應(yīng) hash code 的數(shù)據(jù)行地址即可。
hash 索引非常的緊湊，查找速度很快，適用于內(nèi)存存儲引擎的應(yīng)用。不過它只能精確查詢，不支持范圍查找，也不能直接進(jìn)行排序。限制還是挺多的。
Mysql 的全文索引
全文索引主要是用于文檔查找，像我們可能會從多篇文章中查找包含某些詞語的文章，這時就可以使用全文索引了。雖然 like 也可以使用，但是效率太低了。全文索引在接收到文檔時，會對它進(jìn)行分詞處理，以獲取到關(guān)鍵詞。然后會將關(guān)鍵詞和屬于這個文檔的 id 關(guān)聯(lián)起來。下次查找，就會先到關(guān)鍵詞列表里找到關(guān)聯(lián)的文檔 id ，最后利用文檔 id 去查找到文檔數(shù)據(jù)。
3. 日志
日志類別
- binlog： 二進(jìn)制日志，記錄了數(shù)據(jù)庫對數(shù)據(jù)的修改記錄，包括了 DDL：例如表的創(chuàng)建，數(shù)據(jù)更新等。但并不包括 select 這些查詢語句。binlog 日志是屬于邏輯語句的記錄，可用于主從數(shù)據(jù)庫的同步。
- relay log： 中繼日志，用于主從備份恢復(fù)使用的。有主服務(wù)器的 binlog 邏輯操作語句，以及當(dāng)前的恢復(fù)位置。
- 慢查詢?nèi)罩荆?/strong> 記錄在 mysql 里執(zhí)行時間超過預(yù)期值的耗時語句
- redo log： redo log 是對加載到內(nèi)存數(shù)據(jù)頁的修改結(jié)果的記錄，和 binlog 不同的是，binlog 記錄的是邏輯操作語句，偏向于過程記錄。而 redo log 是一個數(shù)據(jù)頁的修改日志，偏向于結(jié)果的記錄。
- undo log： 回滾日志主要用于回滾數(shù)據(jù)，和 redo log 不一樣的是，undo log 是邏輯日志，是一種相反操作的記錄，比如在回滾時，如果是 insert 操作時，則會逆向為 delete，delete 操作時，逆向為 insert 操作，更新則恢復(fù)到當(dāng)時的版本數(shù)據(jù)。
redo log 相關(guān)概念：writepos、checkpoint、prepare、commit
redo log 是用來記錄當(dāng)前數(shù)據(jù)頁的修改情況，由于性能問題，每次修改并不會實時同步到硬盤。而是先在內(nèi)存中修改，然后將修改情況記錄到 redo 里，再定時的去將 redo 刷新到硬盤里。因此，redo log 有 2 個位置，一個是 writepos，自己寫日志的位置；另一個是 checkpoint，是定時的將數(shù)據(jù)頁同步到硬盤的位置。
redo log 在寫 binlog 日志前會先記錄 redo log，記錄完后標(biāo)記為 prepare 狀態(tài)。當(dāng) binlog 也寫入完成后，才將 redo log 標(biāo)記為 commit 狀態(tài)。只有當(dāng) redo log 是 commit 狀態(tài)時，事務(wù)才能真正的 commit。這樣能防止主從節(jié)點根據(jù) binlog 同步有可能事務(wù)不一致的情況。
4. Mysql 里的鎖
Mysql 里的鎖有哪些？
樂觀/悲觀鎖
- 樂觀鎖：在讀取數(shù)據(jù)時會假設(shè)各個事務(wù)互不影響，它們會處理好屬于自己的那部分?jǐn)?shù)據(jù)。如果在更新數(shù)據(jù)時，發(fā)現(xiàn)有其他事務(wù)修改了屬于自己的數(shù)據(jù)，則會回滾之前的一切操作。
- 悲觀鎖：采取了先獲取鎖再訪問的保守策略，如果已經(jīng)有其他事務(wù)獲取了鎖，則必須等待鎖釋放才能繼續(xù)。
共享/排它鎖
- 共享鎖：又稱讀鎖，當(dāng)前事務(wù)在讀取時，允許其他事務(wù)并發(fā)讀取，但不允許其他事務(wù)上排它鎖，必須等自己釋放了才能繼續(xù)。
- 排它鎖：又稱寫鎖，在寫鎖占有時，如果其他事務(wù)想上讀寫鎖，則得排隊等待。
表鎖/行鎖
- 表鎖：在操作數(shù)據(jù)時，直接將整張表鎖住，操作粒度很大，很容易讓其他事務(wù)在等待，但不會產(chǎn)生死鎖。
- 行鎖：針對的是行記錄的并發(fā)控制，鎖粒度很細(xì)，能支持高并發(fā)，但是不排除會有死鎖情況產(chǎn)生。在 mysql 里行鎖依賴索引實現(xiàn)，如果沒有索引存在，則會直接進(jìn)行表鎖！
行鎖
- 記錄鎖：只鎖住某一條記錄。當(dāng)對唯一索引(包括主鍵)進(jìn)行精確查詢時，會使用記錄鎖。
- 間隙鎖：當(dāng)使用范圍查詢時，會對符合條件的區(qū)間數(shù)據(jù)上鎖。在涉及到普通索引（即不是唯一索引）的查詢時，都會使用間隙鎖。
- Next-key 鎖：臨建鎖，可以理解為 記錄鎖 + 間隙鎖。當(dāng)對唯一索引進(jìn)行范圍查找或?qū)ξㄒ凰饕M(jìn)行查找但結(jié)果不存在時（可以理解為鎖住不存在的記錄），會使用臨建鎖。
上面的間隙鎖、臨建鎖有效的防止了事務(wù)幻讀情況產(chǎn)生，避免了在查找期間有數(shù)據(jù)新增或刪除。
意向鎖
表鎖的一種，它僅僅表示一種操作意向。當(dāng)我們使用粒度比較小的行鎖時，在檢測是否有鎖時，需要一行一行的檢查，效率較低。有了意向鎖之后，則不需一行一行的排查，只需檢測對應(yīng)的意向鎖即可。
事務(wù)里鎖的應(yīng)用是怎么樣的？
可重復(fù)讀
可重復(fù)讀使用的是 MVCC 快照，所以在讀取數(shù)據(jù)時大多數(shù)時候不需要使用鎖。
但使用了 UPDATE, DELETE，或 SELECT with FOR UPDATE（排它鎖） 或 FOR SHARE（共享鎖），則會根據(jù)下面的情況來使用鎖：
- 在唯一索引上精確查找某條記錄時，使用記錄鎖
- 對于其他的搜索，InnoDB 將會鎖定掃描到的索引范圍，使用間隙鎖或臨建鎖來防止幻讀的產(chǎn)生
讀提交
也是使用 MVCC 機(jī)制來讀取數(shù)據(jù)，不過在使用 UPDATE, DELETE，或 SELECT with FOR UPDATE（排它鎖） 或 FOR SHARE（共享鎖）時和上面的機(jī)制不一樣，當(dāng)存儲引擎將篩選到的記錄交給 mysql server 層后，會對不相干的數(shù)據(jù)進(jìn)行解鎖，所以不會涉及間隙鎖或臨建鎖。它們只會在做外鍵約束檢查和重復(fù)鍵檢查時使用到。由于間隙鎖的禁用，可能會出現(xiàn)幻讀現(xiàn)象。
未提交讀
在 mysql 的 innodb 存儲引擎里做 SELECT操作不會做任何鎖動作，如果是 myisam 存儲引擎，則會上共享鎖。
如果使用UPDATE, DELETE，或 SELECT with FOR UPDATE（排它鎖） 或 FOR SHARE（共享鎖）則和讀提交一樣的原則。
可序列化讀
可序列化讀在使用 select 時，一般會自動的轉(zhuǎn)化為 SELECT ... FOR SHARE（共享鎖），以保證讀寫序列化。
lock in share mode 和 for update 里間隙鎖什么時候會應(yīng)用？
- lock in share mode, for update 如果 where 條件是非索引類的，則不會加間隙鎖；
- lock in share mode, for update 如果 where 條件是主鍵類的，并且找不到記錄時會加間隙鎖；如果找到記錄了則會將間隙鎖給釋放了。比如 where 主鍵 = 3 能找到記錄時則不會加間隙鎖，找不到時會在該數(shù)據(jù)的前后葉子節(jié)點間加間隙鎖；此時假如記錄里只有 1，8，9，則會在 1, 8 之間加間隙鎖
- lock in share mode, for update 如果 where 條件是非聚集索引類的，會加間隙鎖，即使找不到記錄。
鎖超時的配置
當(dāng) mysql 獲取鎖超時時候，如果系統(tǒng)變量 innodb_rollback_on_timeout 為 off ,則當(dāng)前事務(wù)只會回滾最后一條 sql, 所以建議設(shè)置 innodb_rollback_on_timeout 為 on, 這樣在獲取鎖超時時可以回滾全部 sql。
5. MVCC 是指什么？
MVCC 即多版本并發(fā)控制，它利用了 undo log 會在數(shù)據(jù)修改時保留上一個修改記錄指針的特點，使得每個事務(wù)對數(shù)據(jù)的修改能有自己的歷史版本追溯，就像鏡像備份一樣。當(dāng)進(jìn)行讀操作時，如果有其他寫操作的事務(wù)并發(fā)進(jìn)行，那么此時可以根據(jù)事務(wù)的隔離級別選擇讀取最新版本亦或自己之前版本的數(shù)據(jù)。MVCC 不需要加鎖的，它能提高事務(wù)的并發(fā)處理能力。
6. mysql 的復(fù)制技術(shù)
- 全同步復(fù)制：只有等所有的 slave 節(jié)點將同步的日志寫入 relay log，并且響應(yīng) ack 確認(rèn)后，此次的事務(wù)才會提交。數(shù)據(jù)完整性高，但性能低
- 半同步復(fù)制：只要有一個 salve 節(jié)點響應(yīng) ack 后就可以認(rèn)為同步成功，但細(xì)分為了兩種，一種是
AFTER_COMMIT：先在主庫提交事務(wù), 然后同步從庫, 等待從庫的 ack 確認(rèn). 才告訴客戶端是否 Ok。另一種是AFTER_SYNC：主庫先不提交事務(wù), 只有從庫 有 replay log ,回復(fù)了 ack 后才進(jìn)行提交事務(wù)。后面一種數(shù)據(jù)一致性較高 - 異步復(fù)制：一旦有需要復(fù)制的就通知 slave, 但不會等待確認(rèn)成功才進(jìn)行后續(xù)操作。
7. 存儲引擎
Mysql 存儲引擎有哪些以及特點？
- InnoDB： 它是 mysql 的默認(rèn)存儲引擎，能夠?qū)崿F(xiàn) ACID 特性的事務(wù)，并且能提交、回滾、恢復(fù)數(shù)據(jù)，能很好的保障用戶數(shù)據(jù)。同時支持了行級鎖、聚集索引以及外鍵約束，是一個完善的存儲引擎。
- MyISAM： 是 mysql 最開始的存儲引擎，占用空間小，能快速存儲，但不支持事務(wù)，提供了基于表級別的鎖粒度，適用于配置或只讀功能的應(yīng)用程序。
- Memory： 數(shù)據(jù)都是存在內(nèi)存里的，能提供快速訪問，不過應(yīng)該較少人使用，畢竟一旦斷電數(shù)據(jù)也就丟失了。
- CSV： 帶有逗號分隔值的文本文件，沒有索引存在。但是兼容性很好，可以跟其他的程序交換數(shù)據(jù)。
myisam 存儲引擎和 innodb 的區(qū)別
- innoDB 支持事務(wù)，myisam 不支持
- innoDB 支持行鎖，myisam 不支持，只能到表鎖
- innoDB 支持外鍵，myisam 不支持
- innoDB 不支持全文索引，myisam 支持
- innoDB 支持聚集索引 和 非聚集索引；myISAM 只支持非聚集索引，該索引存的是數(shù)據(jù)域的記錄指針，還得跳轉(zhuǎn)查找。
8. Mysql 的三層架構(gòu)
- 連接層： 主要負(fù)責(zé)連接池、通信協(xié)議、認(rèn)證授權(quán)等；
- SQL 層： 這一層是 mysql 的大腦，通過一系列組件得到數(shù)據(jù)操作的最優(yōu)解。
-
存儲層： 負(fù)責(zé)數(shù)據(jù)的存儲、檢索。
mysql 的 3 層架構(gòu)
9. 執(zhí)行計劃是什么？怎么看？
執(zhí)行計劃是 mysql 根據(jù)我們的查詢語句進(jìn)行一系列的分析后得到的優(yōu)化方案。我們可以通過執(zhí)行計劃來獲取執(zhí)行過程。
執(zhí)行計劃的獲?。?/p>
explain select 語句
[圖片上傳失敗...(image-abc1fb-1644421977238)]
涉及的字段含義如下：
- id： 該 SELECT 標(biāo)識符
- select_type： 該 SELECT 類型
- table： 輸出行的表
- partitions： 匹配的分區(qū)
- type： 聯(lián)接類型
- possible_keys： 可供選擇的可能索引
- key： 實際選擇的索引
- key_len： 所選密鑰的長度
- ref： 與索引比較的列
- rows： 估計要檢查的行數(shù)
- filtered： 按表條件過濾的行百分比
- Extra： 附加信息
其中，有個 type 字段，它的含義大概如下：
- eq_ref： 使用到了 UNIQUE 或 PRIMARY KEY 索引
- ref： 顯示索引的哪一列被使用了
- ref_or_null： 對 Null 進(jìn)行了索引優(yōu)化
- range： 索引范圍檢索
- index： 索引掃描
- unique_subquery： 使用了 in 子查詢，里面涉及了主鍵字段
- index_subquery： 使用了 in 子查詢，里面涉及了非唯一索引
- fulltext： 全文索引
- all： 全表掃描數(shù)據(jù)
從上面大概就能分析出索引的使用情況了，如果是 all，那就是沒有用到索引了。
10. SQL 注入的現(xiàn)象是？
在拼接 SQL 語句時，直接使用客戶端傳遞過來的值拼接，如果客戶端傳來包含 or 1=1 類似的語句，那么就會篩選到非預(yù)期的結(jié)果，進(jìn)而達(dá)到欺騙服務(wù)器的效果。
解決方案是使用現(xiàn)在數(shù)據(jù)庫提供的預(yù)編譯（prepare）和查詢參數(shù)綁定功能，例如使用占位符 ?，然后將帶有占位符的 SQL 語句交給數(shù)據(jù)庫編譯，這樣數(shù)據(jù)庫就能知道要執(zhí)行的是哪些語句，條件值又是哪些，而不會混雜在一起。
11. UNION 和 UNION ALL 的區(qū)別？
- UNION ALL：將所有的數(shù)據(jù)聯(lián)合起來，即使有重復(fù)數(shù)據(jù)
- UNION：會合并重復(fù)數(shù)據(jù)
12. 為什么盡量使用自增 ID，而不是 UUID？
自增 ID 是由有序的，而 UUID 是無序的，如果該字段作為索引，那么就會很容易打破 B+ 樹的平衡，進(jìn)而不斷的在進(jìn)行磁盤數(shù)據(jù)頁的調(diào)整，導(dǎo)致性能下降
13. 分庫分表有哪些？有什么優(yōu)缺點？
- 分庫：從業(yè)務(wù)角度進(jìn)行切分
- 分表：將數(shù)據(jù)根據(jù)一定的規(guī)則落在多張表上。比如按時間范圍來切分，或者通過對 ID 進(jìn)行 Hash 來路由到對應(yīng)的表上。
分庫分表后使得數(shù)據(jù)不再集中到一張表上，但也帶來了維護(hù)以及其他處理問題。比如原來的事務(wù)變?yōu)榉植际绞聞?wù)；原來的 join 操作將要變?yōu)樵趹?yīng)用層序做過濾；還有數(shù)據(jù)的后續(xù)遷移、擴(kuò)容規(guī)劃等。
14. 內(nèi)連接、外連接區(qū)別
- 內(nèi)連接：只有符合條件的記錄才會出現(xiàn)在結(jié)果集里
- 外連接：其結(jié)果集中不僅包含符合連接條件的行，還會包括左表、右表或兩個表中的所有數(shù)據(jù)行，這三種情況依次稱之為左外連接，右外連接，和全外連接。
15. 常見的數(shù)據(jù)庫優(yōu)化
- 對經(jīng)常出現(xiàn)在 where 條件里，并且數(shù)據(jù)重復(fù)率不高的字段建立索引
- 使用 JOIN 來代替子查詢；
- 能使用 in 就不使用 or，前者能命中索引，后者會讓索引失效
- 避免在 where 字段上計算，例如 where a / 3 = 1，這樣會讓索引失效；避免在 where 字段上使用 NULL 值的判斷
- 打開慢查詢?nèi)罩九渲茫嗅槍π缘姆治鲰憫?yīng)緩慢的語句。
感興趣的朋友可以搜一搜公眾號「 閱新技術(shù) 」，關(guān)注更多的推送文章。
可以的話，就順便點個贊、留個言、分享下，感謝各位支持！
閱新技術(shù)，閱讀更多的新知識。
