在看數(shù)據(jù)庫 的過程中我做了一些小筆記，可能沒我之前系統(tǒng)文章那么有趣，但是絕對也是干貨十足，適合大家去回顧或者面試突擊的適合看看，也不多說先放圖。 InnoDB 是 MySQL 默認(rèn)的事務(wù)型存儲(chǔ)引擎，只要在需要它不支持的特性時(shí)，才考慮使用其他存儲(chǔ)引擎。InnoDB 采用 MVCC 來支持高并發(fā)，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)隔離級(jí)別(未提交讀、提交讀、可重復(fù)讀、可串行化)。其默認(rèn)級(jí)別時(shí)可重復(fù)讀（REPEATABLE READ），在可重復(fù)讀級(jí)別下，通過 MVCC Next-Key Locking 防止幻讀。主索引時(shí)聚簇索引，在索引中保存了數(shù)據(jù)，從而避免直接讀取磁盤，因此對主鍵查詢有很高的性能。InnoDB 內(nèi)部做了很多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時(shí)采用的可預(yù)測性讀，能夠自動(dòng)在內(nèi)存中創(chuàng)建 hash 索引以加速讀操作的自適應(yīng)哈希索引，以及能夠加速插入操作的插入緩沖區(qū)等。InnoDB 支持真正的在線熱備份，MySQL 其他的存儲(chǔ)引擎不支持在線熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合的場景中，停止寫入可能也意味著停止讀取。
設(shè)計(jì)簡單，數(shù)據(jù)以緊密格式存儲(chǔ)。對于只讀數(shù)據(jù)，或者表比較小、可以容忍修復(fù)操作，則依然可以使用它。提供了大量的特性，包括壓縮表、空間數(shù)據(jù)索引等。不支持事務(wù)。不支持行級(jí)鎖，只能對整張表加鎖，讀取時(shí)會(huì)對需要讀到的所有表加共享鎖，寫入時(shí)則對表加排它鎖。但在表有讀取操作的同時(shí)，也可以往表中插入新的記錄，這被稱為并發(fā)插入（CONCURRENT INSERT）。可以手工或者自動(dòng)執(zhí)行檢查和修復(fù)操作，但是和事務(wù)恢復(fù)以及崩潰恢復(fù)不同，可能導(dǎo)致一些數(shù)據(jù)丟失，而且修復(fù)操作是非常慢的。如果指定了 DELAY_KEY_WRITE 選項(xiàng)，在每次修改執(zhí)行完成時(shí)，不會(huì)立即將修改的索引數(shù)據(jù)寫入磁盤，而是會(huì)寫到內(nèi)存中的鍵緩沖區(qū)，只有在清理鍵緩沖區(qū)或者關(guān)閉表的時(shí)候才會(huì)將對應(yīng)的索引塊寫入磁盤。這種方式可以極大的提升寫入性能，但是在數(shù)據(jù)庫或者主機(jī)崩潰時(shí)會(huì)造成索引損壞，需要執(zhí)行修復(fù)操作。
事務(wù)：InnoDB 是事務(wù)型的，可以使用 Commit 和 Rollback 語句。 并發(fā)：MyISAM 只支持表級(jí)鎖，而 InnoDB 還支持行級(jí)鎖。 外鍵：InnoDB 支持外鍵。 備份：InnoDB 支持在線熱備份。 崩潰恢復(fù)：MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 高很多，而且恢復(fù)的速度也更慢。 其它特性：MyISAM 支持壓縮表和空間數(shù)據(jù)索引。 B Tree 指的是 Balance Tree，也就是平衡樹，平衡樹是一顆查找樹，并且所有葉子節(jié)點(diǎn)位于同一層。
B Tree 是 B 樹的一種變形，它是基于 B Tree 和葉子節(jié)點(diǎn)順序訪問指針進(jìn)行實(shí)現(xiàn)，通常用于數(shù)據(jù)庫和操作系統(tǒng)的文件系統(tǒng)中。 B 樹有兩種類型的節(jié)點(diǎn)：內(nèi)部節(jié)點(diǎn)（也稱索引節(jié)點(diǎn)）和葉子節(jié)點(diǎn)，內(nèi)部節(jié)點(diǎn)就是非葉子節(jié)點(diǎn)，內(nèi)部節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，只存儲(chǔ)索引，數(shù)據(jù)都存在葉子節(jié)點(diǎn)。內(nèi)部節(jié)點(diǎn)中的 key 都按照從小到大的順序排列，對于內(nèi)部節(jié)點(diǎn)中的一個(gè) key，左子樹中的所有 key 都小于它，右子樹中的 key 都大于等于它，葉子節(jié)點(diǎn)的記錄也是按照從小到大排列的。每個(gè)葉子節(jié)點(diǎn)都存有相鄰葉子節(jié)點(diǎn)的指針。
查找 查找以典型的方式進(jìn)行，類似于二叉查找樹。起始于根節(jié)點(diǎn)，自頂向下遍歷樹，選擇其分離值在要查找值的任意一邊的子指針。在節(jié)點(diǎn)內(nèi)部典型的使用是二分查找來確定這個(gè)位置。
插入 Perform a search to determine what bucket the new record should go into. If the bucket is not full(a most b - 1 entries after the insertion，b 是節(jié)點(diǎn)中的元素個(gè)數(shù)，一般是頁的整數(shù)倍),add tht record. Otherwise,before inserting the new record original node has 「(L 1)/2」items new node has 「(L 1)/2」items split the bucket. Move ?「(L 1)/2」-th key to the parent,and insert the new node to the parent. Repeat until a parent is found that need not split. If the root splits,treat it as if it has an empty parent ans split as outline above. B-trees grow as the root and not at the leaves.
刪除 和插入類似，只不過是自下而上的合并操作。
AVL 樹 平衡二叉樹，一般是用平衡因子差值決定并通過旋轉(zhuǎn)來實(shí)現(xiàn)，左右子樹樹高差不超過1，那么和紅黑樹比較它是嚴(yán)格的平衡二叉樹，平衡條件非常嚴(yán)格（樹高差只有1），只要插入或刪除不滿足上面的條件就要通過旋轉(zhuǎn)來保持平衡。由于旋轉(zhuǎn)是非常耗費(fèi)時(shí)間的。所以 AVL 樹適用于插入/刪除次數(shù)比較少，但查找多的場景。
紅黑樹 通過對從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)路徑上各個(gè)節(jié)點(diǎn)的顏色進(jìn)行約束，確保沒有一條路徑會(huì)比其他路徑長2倍，因而是近似平衡的。所以相對于嚴(yán)格要求平衡的AVL樹來說，它的旋轉(zhuǎn)保持平衡次數(shù)較少。適合，查找少，插入/刪除次數(shù)多的場景。（現(xiàn)在部分場景使用跳表來替換紅黑樹，可搜索“為啥 redis 使用跳表(skiplist)而不是使用 red-black？”）
B/B 樹 多路查找樹，出度高，磁盤IO低，一般用于數(shù)據(jù)庫系統(tǒng)中。
紅黑樹等平衡樹也可以用來實(shí)現(xiàn)索引，但是文件系統(tǒng)及數(shù)據(jù)庫系統(tǒng)普遍采用 B Tree 作為索引結(jié)構(gòu)，主要有以下兩個(gè)原因：（一）磁盤 IO 次數(shù)B 樹一個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)元素，相對于紅黑樹的樹高更低，磁盤 IO 次數(shù)更少。（二）磁盤預(yù)讀特性為了減少磁盤 I/O 操作，磁盤往往不是嚴(yán)格按需讀取，而是每次都會(huì)預(yù)讀。預(yù)讀過程中，磁盤進(jìn)行順序讀取，順序讀取不需要進(jìn)行磁盤尋道。每次會(huì)讀取頁的整數(shù)倍。操作系統(tǒng)一般將內(nèi)存和磁盤分割成固定大小的塊，每一塊稱為一頁，內(nèi)存與磁盤以頁為單位交換數(shù)據(jù)。數(shù)據(jù)庫系統(tǒng)將索引的一個(gè)節(jié)點(diǎn)的大小設(shè)置為頁的大小，使得一次 I/O 就能完全載入一個(gè)節(jié)點(diǎn)。
B 樹的磁盤 IO 更低 B 樹的內(nèi)部節(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針。因此其內(nèi)部節(jié)點(diǎn)相對 B 樹更小。如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對來說IO讀寫次數(shù)也就降低了。
B 樹的查詢效率更加穩(wěn)定 由于非葉子結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢的路徑長度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢效率相當(dāng)。
B 樹元素遍歷效率高 B 樹在提高了磁盤IO性能的同時(shí)并沒有解決元素遍歷的效率低下的問題。正是為了解決這個(gè)問題，B 樹應(yīng)運(yùn)而生。B 樹只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的，而 B 樹不支持這樣的操作（或者說效率太低）。
索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的，所以不同存儲(chǔ)引擎具有不同的索引類型和實(shí)現(xiàn)。
是大多數(shù) MySQL 存儲(chǔ)引擎的默認(rèn)索引類型。
因?yàn)椴辉傩枰M(jìn)行全表掃描，只需要對樹進(jìn)行搜索即可，所以查找速度快很多。 因?yàn)?B Tree 的有序性，所以除了用于查找，還可以用于排序和分組。 可以指定多個(gè)列作為索引列，多個(gè)索引列共同組成鍵。 適用于全鍵值、鍵值范圍和鍵前綴查找，其中鍵前綴查找只適用于最左前綴查找。如果不是按照索引列的順序進(jìn)行查找，則無法使用索引。 InnoDB 的 B Tree 索引分為主索引和輔助索引。主索引的葉子節(jié)點(diǎn) data 域記錄著完整的數(shù)據(jù)記錄，這種索引方式被稱為聚簇索引。因?yàn)闊o法把數(shù)據(jù)行存放在兩個(gè)不同的地方，所以一個(gè)表只能有一個(gè)聚簇索引。
輔助索引的葉子節(jié)點(diǎn)的 data 域記錄著主鍵的值，因此在使用輔助索引進(jìn)行查找時(shí)，需要先查找到主鍵值，然后再到主索引中進(jìn)行查找，這個(gè)過程也被稱作回表。
哈希索引能以 O(1) 時(shí)間進(jìn)行查找，但是失去了有序性：
無法用于排序與分組； 只支持精確查找，無法用于部分查找和范圍查找。 InnoDB 存儲(chǔ)引擎有一個(gè)特殊的功能叫“自適應(yīng)哈希索引”，當(dāng)某個(gè)索引值被使用的非常頻繁時(shí)，會(huì)在 B Tree 索引之上再創(chuàng)建一個(gè)哈希索引，這樣就讓 B Tree 索引具有哈希索引的一些優(yōu)點(diǎn)，比如快速的哈希查找。
MyISAM 存儲(chǔ)引擎支持全文索引，用于查找文本中的關(guān)鍵詞，而不是直接比較是否相等。查找條件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引實(shí)現(xiàn)，它記錄著關(guān)鍵詞到其所在文檔的映射。InnoDB 存儲(chǔ)引擎在 MySQL 5.6.4 版本中也開始支持全文索引。
MyISAM 存儲(chǔ)引擎支持空間數(shù)據(jù)索引（R-Tree），可以用于地理數(shù)據(jù)存儲(chǔ)?？臻g數(shù)據(jù)索引會(huì)從所有維度來索引數(shù)據(jù)，可以有效地使用任意維度來進(jìn)行組合查詢。必須使用 GIS 相關(guān)的函數(shù)來維護(hù)數(shù)據(jù)。
在進(jìn)行查詢時(shí)，索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)，否則無法使用索引。例如下面的查詢不能使用 actor_id 列的索引：
SELECT ?actor_id?FROM ?sakila.actor?WHERE ?actor_id? ?1 ?=?5 ;在需要使用多個(gè)列作為條件進(jìn)行查詢時(shí)，使用多列索引比使用多個(gè)單列索引性能更好。例如下面的語句中，最好把 actor_id 和 film_id 設(shè)置為多列索引。
SELECT ?film_id,?actor_?id ?FROM ?sakila.film_actorWHERE ?actor_id?=?1 ?AND ?film_id?=?1 ;讓選擇性最強(qiáng)的索引列放在前面。索引的選擇性是指：不重復(fù)的索引值和記錄總數(shù)的比值。最大值為 1，此時(shí)每個(gè)記錄都有唯一的索引與其對應(yīng)。選擇性越高，每個(gè)記錄的區(qū)分度越高，查詢效率也越高。例如下面顯示的結(jié)果中 customer_id 的選擇性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。
SELECT ?COUNT (DISTINCT ?staff_id)/COUNT (*)?AS ?staff_id_selectivity,COUNT (DISTINCT ?customer_id)/COUNT (*)?AS ?customer_id_selectivity,COUNT (*)FROM ?payment;???staff_id_selectivity:?0.0001對于 BLOB、TEXT 和 VARCHAR 類型的列，必須使用前綴索引，只索引開始的部分字符。前綴長度的選取需要根據(jù)索引選擇性來確定。
索引包含所有需要查詢的字段的值。具有以下優(yōu)點(diǎn)：
索引通常遠(yuǎn)小于數(shù)據(jù)行的大小，只讀取索引能大大減少數(shù)據(jù)訪問量。 一些存儲(chǔ)引擎（例如 MyISAM）在內(nèi)存中只緩存索引，而數(shù)據(jù)依賴于操作系統(tǒng)來緩存。因此，只訪問索引可以不使用系統(tǒng)調(diào)用（通常比較費(fèi)時(shí)）。 對于 InnoDB 引擎，若輔助索引能夠覆蓋查詢，則無需訪問主索引。 大大減少了服務(wù)器需要掃描的數(shù)據(jù)行數(shù)。 幫助服務(wù)器避免進(jìn)行排序和分組，以及避免創(chuàng)建臨時(shí)表（B Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。臨時(shí)表主要是在排序和分組過程中創(chuàng)建，不需要排序和分組，也就不需要?jiǎng)?chuàng)建臨時(shí)表）。 將隨機(jī) I/O 變?yōu)轫樞?I/O（B Tree 索引是有序的，會(huì)將相鄰的數(shù)據(jù)都存儲(chǔ)在一起）。 對于非常小的表、大部分情況下簡單的全表掃描比建立索引更高效； 對于中到大型的表，索引就非常有效； 但是對于特大型的表，建立和維護(hù)索引的代價(jià)將會(huì)隨之增長。這種情況下，需要用到一種技術(shù)可以直接區(qū)分出需要查詢的一組數(shù)據(jù)，而不是一條記錄一條記錄地匹配，例如可以使用分區(qū)技術(shù)。 為什么對于非常小的表，大部分情況下簡單的全表掃描比建立索引更高效？ 如果一個(gè)表比較小，那么顯然直接遍歷表比走索引要快（因?yàn)樾枰乇恚?/p>注：首先，要注意這個(gè)答案隱含的條件是查詢的數(shù)據(jù)不是索引的構(gòu)成部分，否也不需要回表操作。其次，查詢條件也不是主鍵，否則可以直接從聚簇索引中拿到數(shù)據(jù)。explain 用來分析 SELECT 查詢語句，開發(fā)人員可以通過分析 Explain 結(jié)果來優(yōu)化查詢語句。 常用的有 SIMPLE 簡單查詢，UNION 聯(lián)合查詢，SUBQUERY 子查詢等。
要查詢的表
The possible indexes to choose 可選擇的索引
The index actually chosen 實(shí)際使用的索引
Estimate of rows to be examined 掃描的行數(shù)
索引查詢類型，經(jīng)常用到的索引查詢類型：
const：使用主鍵或者唯一索引進(jìn)行查詢的時(shí)候只有一行匹配
ref：使用非唯一索引
range：使用主鍵、單個(gè)字段的輔助索引、多個(gè)字段的輔助索引的最后一個(gè)字段進(jìn)行范圍查詢
index：和all的區(qū)別是掃描的是索引樹
all：掃描全表： 觸發(fā)條件：表只有一行，這是一個(gè) const type 的特殊情況 觸發(fā)條件：在使用主鍵或者唯一索引進(jìn)行查詢的時(shí)候只有一行匹配。 SELECT ?*?FROM ?tbl_name?WHERE ?primary_key=1 ;SELECT ?*?FROM ?tbl_nameWHERE ?primary_key_part1=1 ?AND ?primary_key_part2=2 ;觸發(fā)條件：在進(jìn)行聯(lián)接查詢的，使用主鍵或者唯一索引并且只匹配到一行記錄的時(shí)候 SELECT ?*?FROM ?ref_table,other_tableWHERE ?ref_table.key_column=other_table.column;SELECT ?*?FROM ?ref_table,other_tableWHERE ?ref_table.key_column_part1=other_table.columnAND ?ref_table.key_column_part2=1 ;觸發(fā)條件：使用非唯一索引
SELECT ?*?FROM ?ref_table?WHERE ?key_column=expr;SELECT ?*?FROM ?ref_table,other_tableWHERE ?ref_table.key_column=other_table.column;SELECT ?*?FROM ?ref_table,other_tableWHERE ?ref_table.key_column_part1=other_table.columnAND ?ref_table.key_column_part2=1 ;觸發(fā)條件：只有在使用主鍵、單個(gè)字段的輔助索引、多個(gè)字段的輔助索引的最后一個(gè)字段進(jìn)行范圍查詢才是 range SELECT ?*?FROM ?tbl_nameWHERE ?key_column?=?10 ;SELECT ?*?FROM ?tbl_nameWHERE ?key_column?BETWEEN ?10 ?and ?20 ;SELECT ?*?FROM ?tbl_nameWHERE ?key_column?IN ?(10 ,20 ,30 );SELECT ?*?FROM ?tbl_nameWHERE ?key_part1?=?10 ?AND ?key_part2?IN ?(10 ,20 ,30 );The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways: 觸發(fā)條件：只掃描索引樹1）查詢的字段是索引的一部分，覆蓋索引。2）使用主鍵進(jìn)行排序
觸發(fā)條件：全表掃描，不走索引
只返回必要的列：最好不要使用 SELECT * 語句。 只返回必要的行：使用 LIMIT 語句來限制返回的數(shù)據(jù)。 緩存重復(fù)查詢的數(shù)據(jù)：使用緩存可以避免在數(shù)據(jù)庫中進(jìn)行查詢，特別在要查詢的數(shù)據(jù)經(jīng)常被重復(fù)查詢時(shí)，緩存帶來的查詢性能提升將會(huì)是非常明顯的。 最有效的方式是使用索引來覆蓋查詢。
一個(gè)大查詢?nèi)绻淮涡詧?zhí)行的話，可能一次鎖住很多數(shù)據(jù)、占滿整個(gè)事務(wù)日志、耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。
DELETE ?FROM ?messages?WHERE ?create ?DATE_SUB(NOW (),?INTERVAL ?3 ?MONTH );rows_affected?=?0do ?{"DELETE?FROM?messages?WHERE?create??)while ?rows_affected?>?0 將一個(gè)大連接查詢分解成對每一個(gè)表進(jìn)行一次單表查詢，然后在應(yīng)用程序中進(jìn)行關(guān)聯(lián)，這樣做的好處有：
讓緩存更高效。對于連接查詢，如果其中一個(gè)表發(fā)生變化，那么整個(gè)查詢緩存就無法使用。而分解后的多個(gè)查詢，即使其中一個(gè)表發(fā)生變化，對其它表的查詢緩存依然可以使用。 分解成多個(gè)單表查詢，這些單表查詢的緩存結(jié)果更可能被其它查詢使用到，從而減少冗余記錄的查詢。 減少鎖競爭； 在應(yīng)用層進(jìn)行連接，可以更容易對數(shù)據(jù)庫進(jìn)行拆分，從而更容易做到高性能和可伸縮。 查詢本身效率也可能會(huì)有所提升。例如下面的例子中，使用 IN() 代替連接查詢，可以讓 MySQL 按照 ID 順序進(jìn)行查詢，這可能比隨機(jī)的連接要更高效。 SELECT ?*?FROM ?tagJOIN ?tag_post?ON ?tag_post.tag_id=tag.idJOIN ?post?ON ?tag_post.post_id=post.idWHERE ?tag.tag='mysql' ;SELECT ?*?FROM ?tag?WHERE ?tag='mysql' ;SELECT ?*?FROM ?tag_post?WHERE ?tag_id=1234 ;SELECT ?*?FROM ?post?WHERE ?post.id?IN ?(123 ,456 ,567 ,9098 ,8904 );事務(wù)是指滿足 ACID 特性的一組操作，可以通過 Commit 提交一個(gè)事務(wù)，也可以使用 Rollback 進(jìn)行回滾。
事務(wù)最基本的莫過于 ACID 四個(gè)特性了，這四個(gè)特性分別是：
Atomicity：原子性 Consistency：一致性 Isolation：隔離性 Durability：持久性 原子性 事務(wù)被視為不可分割的最小單元，事務(wù)的所有操作要么全部成功，要么全部失敗回滾。
一致性 數(shù)據(jù)庫在事務(wù)執(zhí)行前后都保持一致性狀態(tài)，在一致性狀態(tài)下，所有事務(wù)對一個(gè)數(shù)據(jù)的讀取結(jié)果都是相同的。
隔離性 一個(gè)事務(wù)所做的修改在最終提交以前，對其他事務(wù)是不可見的。
持久性 一旦事務(wù)提交，則其所做的修改將會(huì)永遠(yuǎn)保存到數(shù)據(jù)庫中。即使系統(tǒng)發(fā)生崩潰，事務(wù)執(zhí)行的結(jié)果也不能丟。
事務(wù)的 ACID 特性概念很簡單，但不好理解，主要是因?yàn)檫@幾個(gè)特性不是一種平級(jí)關(guān)系：
只有滿足一致性，事務(wù)的結(jié)果才是正確的。 在無并發(fā)的情況下，事務(wù)串行執(zhí)行，隔離性一定能夠滿足。此時(shí)只要能滿足原子性，就一定能滿足一致性。在并發(fā)的情況下，多個(gè)事務(wù)并行執(zhí)行，事務(wù)不僅要滿足原子性，還需要滿足隔離性，才能滿足一致性。 事務(wù)滿足持久化是為了能應(yīng)對數(shù)據(jù)庫崩潰的情況。 未提交讀（READ UNCOMMITTED） 事務(wù)中的修改，即使沒有提交，對其他事務(wù)也是可見的。
提交讀（READ COMMITTED） 一個(gè)事務(wù)只能讀取已經(jīng)提交的事務(wù)所做的修改。換句話說，一個(gè)事務(wù)所做的修改在提交之前對其他事務(wù)是不可見的。
可重復(fù)讀（REPEATABLE READ） 保證在同一個(gè)事務(wù)中多次讀取同樣數(shù)據(jù)的結(jié)果是一樣的。
可串行化（SERIALIZABLE） 強(qiáng)制事務(wù)串行執(zhí)行。需要加鎖實(shí)現(xiàn)，而其它隔離級(jí)別通常不需要。
隔離級(jí)別 臟讀 不可重復(fù)讀 幻影讀 未提交讀 √ √ √ 提交讀 × √ √ 可重復(fù)讀 × × √ 可串行化 × × ×
鎖是數(shù)據(jù)庫系統(tǒng)區(qū)別于文件系統(tǒng)的一個(gè)關(guān)鍵特性。鎖機(jī)制用于管理對共享資源的并發(fā)訪問。
共享鎖（S Lock） 允許事務(wù)讀一行數(shù)據(jù)
排他鎖（X Lock） 允許事務(wù)刪除或者更新一行數(shù)據(jù)
意向共享鎖（IS Lock） 事務(wù)想要獲得一張表中某幾行的共享鎖
意向排他鎖 事務(wù)想要獲得一張表中某幾行的排他鎖
多版本并發(fā)控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存儲(chǔ)引擎實(shí)現(xiàn)隔離級(jí)別的一種具體方式，用于實(shí)現(xiàn)提交讀和可重復(fù)讀這兩種隔離級(jí)別。而未提交讀隔離級(jí)別總是讀取最新的數(shù)據(jù)行，無需使用 MVCC。可串行化隔離級(jí)別需要對所有讀取的行都加鎖，單純使用 MVCC 無法實(shí)現(xiàn)。
版本號(hào) 系統(tǒng)版本號(hào)：是一個(gè)遞增的數(shù)字，每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)就會(huì)自動(dòng)遞增。 事務(wù)版本號(hào)：事務(wù)開始時(shí)的系統(tǒng)版本號(hào)。 隱藏的列 MVCC 在每行記錄后面都保存著兩個(gè)隱藏的列，用來存儲(chǔ)兩個(gè)版本號(hào)：
創(chuàng)建版本號(hào)：指示創(chuàng)建一個(gè)數(shù)據(jù)行的快照時(shí)的系統(tǒng)版本號(hào)； 刪除版本號(hào)：如果該快照的刪除版本號(hào)大于當(dāng)前事務(wù)版本號(hào)表示該快照有效，否則表示該快照已經(jīng)被刪除了。 Undo 日志 MVCC 使用到的快照存儲(chǔ)在 Undo 日志中，該日志通過回滾指針把一個(gè)數(shù)據(jù)行（Record）的所有快照連接起來。
以下實(shí)現(xiàn)過程針對可重復(fù)讀隔離級(jí)別。當(dāng)開始一個(gè)事務(wù)時(shí)，該事務(wù)的版本號(hào)肯定大于當(dāng)前所有數(shù)據(jù)行快照的創(chuàng)建版本號(hào)，理解這一點(diǎn)很關(guān)鍵。數(shù)據(jù)行快照的創(chuàng)建版本號(hào)是創(chuàng)建數(shù)據(jù)行快照時(shí)的系統(tǒng)版本號(hào)，系統(tǒng)版本號(hào)隨著創(chuàng)建事務(wù)而遞增，因此新創(chuàng)建一個(gè)事務(wù)時(shí)，這個(gè)事務(wù)的系統(tǒng)版本號(hào)比之前的系統(tǒng)版本號(hào)都大，也就是比所有數(shù)據(jù)行快照的創(chuàng)建版本號(hào)都大。
SELECT 多個(gè)事務(wù)必須讀取到同一個(gè)數(shù)據(jù)行的快照，并且這個(gè)快照是距離現(xiàn)在最近的一個(gè)有效快照。但是也有例外，如果有一個(gè)事務(wù)正在修改該數(shù)據(jù)行，那么它可以讀取事務(wù)本身所做的修改，而不用和其它事務(wù)的讀取結(jié)果一致。把沒有對一個(gè)數(shù)據(jù)行做修改的事務(wù)稱為 T，T 所要讀取的數(shù)據(jù)行快照的創(chuàng)建版本號(hào)必須小于等于 T 的版本號(hào)，因?yàn)槿绻笥?T 的版本號(hào)，那么表示該數(shù)據(jù)行快照是其它事務(wù)的最新修改，因此不能去讀取它。除此之外，T 所要讀取的數(shù)據(jù)行快照的刪除版本號(hào)必須是未定義或者大于 T 的版本號(hào)，因?yàn)槿绻∮诘扔?T 的版本號(hào)，那么表示該數(shù)據(jù)行快照是已經(jīng)被刪除的，不應(yīng)該去讀取它。
INSERT 將當(dāng)前系統(tǒng)版本號(hào)作為數(shù)據(jù)行快照的創(chuàng)建版本號(hào)。
DELETE 將當(dāng)前系統(tǒng)版本號(hào)作為數(shù)據(jù)行快照的刪除版本號(hào)。
UPDATE 將當(dāng)前系統(tǒng)版本號(hào)作為更新前的數(shù)據(jù)行快照的刪除版本號(hào)，并將當(dāng)前系統(tǒng)版本號(hào)作為更新后的數(shù)據(jù)行快照的創(chuàng)建版本號(hào)?？梢岳斫鉃橄葓?zhí)行 DELETE 后執(zhí)行 INSERT。
在可重復(fù)讀級(jí)別中，通過MVCC機(jī)制，雖然讓數(shù)據(jù)變得可重復(fù)讀，但我們讀到的數(shù)據(jù)可能是歷史數(shù)據(jù)，是不及時(shí)的數(shù)據(jù)，不是數(shù)據(jù)庫當(dāng)前的數(shù)據(jù)！這在一些對于數(shù)據(jù)的時(shí)效特別敏感的業(yè)務(wù)中，就很可能出問題。對于這種讀取歷史數(shù)據(jù)的方式，我們叫它快照讀 (snapshot read)，而讀取數(shù)據(jù)庫當(dāng)前版本數(shù)據(jù)的方式，叫當(dāng)前讀 (current read)。很顯然，在MVCC中：
快照讀 MVCC 的 SELECT 操作是快照中的數(shù)據(jù)，不需要進(jìn)行加鎖操作。
select ?*?from ?table ?….;當(dāng)前讀 MVCC 其它會(huì)對數(shù)據(jù)庫進(jìn)行修改的操作（INSERT、UPDATE、DELETE）需要進(jìn)行加鎖操作，從而讀取最新的數(shù)據(jù)。可以看到 MVCC 并不是完全不用加鎖，而只是避免了 SELECT 的加鎖操作。
INSERT ;UPDATE ;DELETE ;在進(jìn)行 SELECT 操作時(shí)，可以強(qiáng)制指定進(jìn)行加鎖操作。以下第一個(gè)語句需要加 S 鎖，第二個(gè)需要加 X 鎖。
-?select ?*?from ?table ?where ???lock ?in ?share ?mode ;select ?*?from ?table ?where ???for ?update ;事務(wù)的隔離級(jí)別實(shí)際上都是定義的當(dāng)前讀的級(jí)別，MySQL為了減少鎖處理（包括等待其它鎖）的時(shí)間，提升并發(fā)能力，引入了快照讀的概念，使得select不用加鎖。而update、insert這些“當(dāng)前讀”的隔離性，就需要通過加鎖來實(shí)現(xiàn)了。
鎖定一個(gè)記錄上的索引，而不是記錄本身。如果表沒有設(shè)置索引，InnoDB 會(huì)自動(dòng)在主鍵上創(chuàng)建隱藏的聚簇索引，因此 Record Locks 依然可以使用。
鎖定索引之間的間隙，但是不包含索引本身。例如當(dāng)一個(gè)事務(wù)執(zhí)行以下語句，其它事務(wù)就不能在 t.c 中插入 15。
SELECT ?c?FROM ?t?WHERE ?c?BETWEEN ?10 ?and ?20 ?FOR ?UPDATE ;它是 Record Locks 和 Gap Locks 的結(jié)合，不僅鎖定一個(gè)記錄上的索引，也鎖定索引之間的間隙。例如一個(gè)索引包含以下值：10, 11, 13, and 20，那么就需要鎖定以下區(qū)間：
(-∞,?10]在 InnoDB 存儲(chǔ)引擎中，SELECT 操作的不可重復(fù)讀問題通過 MVCC 得到了解決，而 UPDATE、DELETE 的不可重復(fù)讀問題通過 Record Lock 解決，INSERT 的不可重復(fù)讀問題是通過 Next-Key Lock（Record Lock Gap Lock）解決的。 臟讀指的是不同事務(wù)下，當(dāng)前事務(wù)可以讀取到另外事務(wù)未提交的數(shù)據(jù)。例如：T1 修改一個(gè)數(shù)據(jù)，T2 隨后讀取這個(gè)數(shù)據(jù)。如果 T1 撤銷了這次修改，那么 T2 讀取的數(shù)據(jù)是臟數(shù)據(jù)。
不可重復(fù)讀指的是同一事務(wù)內(nèi)多次讀取同一數(shù)據(jù)集合，讀取到的數(shù)據(jù)是不一樣的情況。例如：T2 讀取一個(gè)數(shù)據(jù)，T1 對該數(shù)據(jù)做了修改。如果 T2 再次讀取這個(gè)數(shù)據(jù)，此時(shí)讀取的結(jié)果和第一次讀取的結(jié)果不同。
在 InnoDB 存儲(chǔ)引擎中，SELECT 操作的不可重復(fù)讀問題通過 MVCC 得到了解決，而 UPDATE、DELETE 的不可重復(fù)讀問題是通過 Record Lock 解決的，INSERT 的不可重復(fù)讀問題是通過 Next-Key Lock（Record Lock Gap Lock）解決的。 The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row. Phantom Proble 是指在同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的 sql 語句可能返回不同的結(jié)果，第二次的 sql 語句可能會(huì)返回之前不存在的行。幻影讀是一種特殊的不可重復(fù)讀問題。
一個(gè)事務(wù)的更新操作會(huì)被另一個(gè)事務(wù)的更新操作所覆蓋。例如：T1 和 T2 兩個(gè)事務(wù)都對一個(gè)數(shù)據(jù)進(jìn)行修改，T1 先修改，T2 隨后修改，T2 的修改覆蓋了 T1 的修改。
這類型問題可以通過給 SELECT 操作加上排他鎖來解決，不過這可能會(huì)引入性能問題，具體使用要視業(yè)務(wù)場景而定。
水平切分又稱為 Sharding，它是將同一個(gè)表中的記錄拆分到多個(gè)結(jié)構(gòu)相同的表中。當(dāng)一個(gè)表的數(shù)據(jù)不斷增多時(shí)，Sharding 是必然的選擇，它可以將數(shù)據(jù)分布到集群的不同節(jié)點(diǎn)上，從而緩存單個(gè)數(shù)據(jù)庫的壓力。
垂直切分是將一張表按列分成多個(gè)表，通常是按照列的關(guān)系密集程度進(jìn)行切分，也可以利用垂直氣氛將經(jīng)常被使用的列喝不經(jīng)常被使用的列切分到不同的表中。在數(shù)據(jù)庫的層面使用垂直切分將按數(shù)據(jù)庫中表的密集程度部署到不通的庫中，例如將原來電商數(shù)據(jù)部署庫垂直切分稱商品數(shù)據(jù)庫、用戶數(shù)據(jù)庫等。
哈希取模：hash(key)%N 范圍：可以是 ID 范圍也可以是時(shí)間范圍 映射表：使用單獨(dú)的一個(gè)數(shù)據(jù)庫來存儲(chǔ)映射關(guān)系 事務(wù)問題 使用分布式事務(wù)來解決，比如 XA 接口
連接 可以將原來的連接分解成多個(gè)單表查詢，然后在用戶程序中進(jìn)行連接。
唯一性 使用全局唯一 ID （GUID） 為每個(gè)分片指定一個(gè) ID 范圍 分布式 ID 生成器（如 Twitter 的 Snowflake 算法） 主要涉及三個(gè)線程：binlog 線程、I/O 線程和 SQL 線程。
binlog 線程?：負(fù)責(zé)將主服務(wù)器上的數(shù)據(jù)更改寫入二進(jìn)制日志（Binary log）中。 I/O 線程?：負(fù)責(zé)從主服務(wù)器上讀取- 二進(jìn)制日志，并寫入從服務(wù)器的中繼日志（Relay log）。 SQL 線程?：負(fù)責(zé)讀取中繼日志，解析出主服務(wù)器已經(jīng)執(zhí)行的數(shù)據(jù)更改并在從服務(wù)器中重放（Replay）。 主服務(wù)器處理寫操作以及實(shí)時(shí)性要求比較高的讀操作，而從服務(wù)器處理讀操作。讀寫分離能提高性能的原因在于：
主從服務(wù)器負(fù)責(zé)各自的讀和寫，極大程度緩解了鎖的爭用； 從服務(wù)器可以使用 MyISAM，提升查詢性能以及節(jié)約系統(tǒng)開銷； 增加冗余，提高可用性。 讀寫分離常用代理方式來實(shí)現(xiàn)，代理服務(wù)器接收應(yīng)用層傳來的讀寫請求，然后決定轉(zhuǎn)發(fā)到哪個(gè)服務(wù)器。
在實(shí)際業(yè)務(wù)中經(jīng)常會(huì)使用到 JSON 數(shù)據(jù)類型，在查詢過程中主要有兩種使用需求：在 where 條件中有通過 json 中的某個(gè)字段去過濾返回結(jié)果的需求 查詢 json 字段中的部分字段作為返回結(jié)果（減少內(nèi)存占用） JSON_CONTAINS(target, candidate[, path]) 如果在 json 字段 target 指定的位置 path，找到了目標(biāo)值 condidate，返回 1，否則返回 0 如果只是檢查在指定的路徑是否存在數(shù)據(jù)，使用JSON_CONTAINS_PATH() mysql>?SET ?@j?=?'{"a":?1,?"b":?2,?"c":?{"d":?4}}' ;SET ?@j2?=?'1' ;SELECT ?JSON_CONTAINS(@j,?@j2,?'$.a' );------------------------------- ------------------------------- ------------------------------- SELECT ?JSON_CONTAINS(@j,?@j2,?'$.b' );------------------------------- ------------------------------- ------------------------------- SET ?@j2?=?'{"d":?4}' ;SELECT ?JSON_CONTAINS(@j,?@j2,?'$.a' );------------------------------- ------------------------------- ------------------------------- SELECT ?JSON_CONTAINS(@j,?@j2,?'$.c' );------------------------------- ------------------------------- ------------------------------- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) 如果在指定的路徑存在數(shù)據(jù)返回 1，否則返回 0 mysql>?SET ?@j?=?'{"a":?1,?"b":?2,?"c":?{"d":?4}}' ;SELECT ?JSON_CONTAINS_PATH(@j,?'one' ,?'$.a' ,?'$.e' );--------------------------------------------- --------------------------------------------- --------------------------------------------- SELECT ?JSON_CONTAINS_PATH(@j,?'all' ,?'$.a' ,?'$.e' );--------------------------------------------- --------------------------------------------- --------------------------------------------- SELECT ?JSON_CONTAINS_PATH(@j,?'one' ,?'$.c.d' );---------------------------------------- ---------------------------------------- ---------------------------------------- SELECT ?JSON_CONTAINS_PATH(@j,?'one' ,?'$.a.d' );---------------------------------------- ---------------------------------------- ---------------------------------------- 實(shí)際使用：
????????$conds?=?new ?Criteria();'dept_code' ,?'in' ,?$deptCodes);if ?(!empty ($aoiAreaId))?{new ?Criteria();"JSON_CONTAINS_PATH(new_aoi_area_ids,'one',?'$.\"$aoiAreaId\"')" ,?'=' ,?1 );"JSON_CONTAINS_PATH(old_aoi_area_ids,'one',?'$.\"$aoiAreaId\"')" ,?'=' ,?1 );獲取指定路徑的值 -> vs ->> Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result.
mysql>?SELECT ?*?FROM ?jemp?WHERE ?g?>?2 ;------------------------------- ------ ------------------------------- ------ ------------------------------- ------ set ?(0.01 ?sec)SELECT ?c->'$.name' ?AS ?name FROM ?jemp?WHERE ?g?>?2 ;---------- ---------- ---------- set ?(0.00 ?sec)SELECT ?JSON_UNQUOTE(c->'$.name' )?AS ?name FROM ?jemp?WHERE ?g?>?2 ;-------- -------- -------- set ?(0.00 ?sec)SELECT ?c->>'$.name' ?AS ?name FROM ?jemp?WHERE ?g?>?2 ;-------- -------- -------- set ?(0.00 ?sec)實(shí)際使用：
$retTask?=?AoiAreaTaskOrm::findRows(['status' ,?'extra_info->>"$.new_aoi_area_infos"?as?new_aoi_area_infos' ,?'extra_info->>"$.old_aoi_area_infos"?as?old_aoi_area_infos' ],?$cond);記 A->B 表示 A 函數(shù)決定 B，也可以說 B 函數(shù)依賴于 A。如果 {A1，A2，... ，An} 是關(guān)系的一個(gè)或多個(gè)屬性的集合，該集合函數(shù)決定了關(guān)系的其它所有屬性并且是最小的，那么該集合就稱為鍵碼。對于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函數(shù)依賴，否則就是完全函數(shù)依賴。對于 A->B，B->C，則 A->C 是一個(gè)傳遞函數(shù)依賴
以下的學(xué)生課程關(guān)系的函數(shù)依賴為 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，鍵碼為 {Sno, Cname}。也就是說，確定學(xué)生和課程之后，就能確定其它信息。
Sno Sname Sdept Mname Cname Grade 1 學(xué)生-1 學(xué)院-1 院長-1 課程-1 90 2 學(xué)生-2 學(xué)院-2 院長-2 課程-2 80 2 學(xué)生-2 學(xué)院-2 院長-2 課程-1 100 3 學(xué)生-3 學(xué)院-2 院長-2 課程-2 95
不符合范式的關(guān)系，會(huì)產(chǎn)生很多異常，主要有以下四種異常：
冗余數(shù)據(jù)：例如 學(xué)生-2 出現(xiàn)了兩次。 修改異常：修改了一個(gè)記錄中的信息，但是另一個(gè)記錄中相同的信息卻沒有被修改。 刪除異常：刪除一個(gè)信息，那么也會(huì)丟失其它信息。例如刪除了 課程-1 需要?jiǎng)h除第一行和第三行，那么 學(xué)生-1 的信息就會(huì)丟失。 插入異常：例如想要插入一個(gè)學(xué)生的信息，如果這個(gè)學(xué)生還沒選課，那么就無法插入。 范式理論是為了解決以上提到四種異常。高級(jí)別范式的依賴于低級(jí)別的范式，1NF 是最低級(jí)別的范式。
屬性不可分。
每個(gè)非主屬性完全函數(shù)依賴于鍵碼。可以通過分解來滿足。
分解前 Sno Sname Sdept Mname Cname Grade 1 學(xué)生-1 學(xué)院-1 院長-1 課程-1 90 2 學(xué)生-2 學(xué)院-2 院長-2 課程-2 80 2 學(xué)生-2 學(xué)院-2 院長-2 課程-1 100 3 學(xué)生-3 學(xué)院-2 院長-2 課程-2 95
以上學(xué)生課程關(guān)系中，{Sno, Cname} 為鍵碼，有如下函數(shù)依賴：
Sno -> Sname, Sdept Sdept -> Mname Sno, Cname-> Grade Grade 完全函數(shù)依賴于鍵碼，它沒有任何冗余數(shù)據(jù)，每個(gè)學(xué)生的每門課都有特定的成績。Sname, Sdept 和 Mname 都部分依賴于鍵碼，當(dāng)一個(gè)學(xué)生選修了多門課時(shí)，這些數(shù)據(jù)就會(huì)出現(xiàn)多次，造成大量冗余數(shù)據(jù)。
分解后 關(guān)系-1
Sno Sname Sdept Mname 1 學(xué)生-1 學(xué)院-1 院長-1 2 學(xué)生-2 學(xué)院-2 院長-2 3 學(xué)生-3 學(xué)院-2 院長-2
有以下函數(shù)依賴：
Sno -> Sname, Sdept Sdept -> Mname 關(guān)系-2
Sno Cname Grade 1 課程-1 90 2 課程-2 80 2 課程-1 100 3 課程-2 95
有以下函數(shù)依賴：
非主屬性不傳遞函數(shù)依賴于鍵碼。上面的 關(guān)系-1 中存在以下傳遞函數(shù)依賴：
可以進(jìn)行以下分解：關(guān)系-11
Sno Sname Sdept 1 學(xué)生-1 學(xué)院-1 2 學(xué)生-2 學(xué)院-2 3 學(xué)生-3 學(xué)院-2
關(guān)系-12
Sdept Mname 學(xué)院-1 院長-1 學(xué)院-2 院長-2
Entity-Relationship，有三個(gè)組成部分：實(shí)體、屬性、聯(lián)系。用來進(jìn)行關(guān)系型
數(shù)據(jù)庫 系統(tǒng)的概念設(shè)計(jì)。
包含一對一，一對多，多對多三種。
如果 A 到 B 是一對多關(guān)系，那么畫個(gè)帶箭頭的線段指向 B； 如果是一對一，畫兩個(gè)帶箭頭的線段； 如果是多對多，畫兩個(gè)不帶箭頭的線段。 下圖的 Course 和 Student 是一對多的關(guān)系。
一個(gè)實(shí)體在聯(lián)系出現(xiàn)幾次，就要用幾條線連接。下圖表示一個(gè)課程的先修關(guān)系，先修關(guān)系出現(xiàn)兩個(gè) Course 實(shí)體，第一個(gè)是先修課程，后一個(gè)是后修課程，因此需要用兩條線來表示這種關(guān)系。
雖然老師可以開設(shè)多門課，并且可以教授多名學(xué)生，但是對于特定的學(xué)生和課程，只有一個(gè)老師教授，這就構(gòu)成了一個(gè)三元聯(lián)系。
用一個(gè)三角形和兩條線來連接類和子類，與子類有關(guān)的屬性和聯(lián)系都連到子類上，而與父類和子類都有關(guān)的連到父類上。
姜承堯. MySQL 技術(shù)內(nèi)幕: InnoDB 存儲(chǔ)引擎 [M]. 機(jī)械工業(yè)出版社, 2011. CS-Notes-MySQL B tree 紅黑樹、B( )樹、跳表、AVL等數(shù)據(jù)結(jié)構(gòu)，應(yīng)用場景及分析，以及一些英文縮寫 B樹、B 樹、紅黑樹、AVL樹比較 8.8.2 EXPLAIN Output Format 最官方的 mysql explain type 字段解讀 12.18.3 Functions That Search JSON Values 這都是些基礎(chǔ)知識(shí)，也是面試常問的知識(shí)點(diǎn)，大家別光顧著收藏了呀，耐心學(xué)完它！
下載文檔
