(四) BAT面試的20道高頻資料庫面試題

1.事務四大特性（ACID）原子性、一致性、隔離性、永續性？

原子性（Atomicity）

原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾，因此事務的操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有任何影響。

一致性（Consistency）

事務開始前和結束後，資料庫的完整性約束沒有被破壞。比如 A 向 B 轉賬，不可能 A 扣了錢，B 卻沒收到。

隔離性（Isolation）

隔離性是當多個使用者併發訪問資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所干擾，多個併發事務之間要相互隔離。
同一時間，只允許一個事務請求同一資料，不同的事務之間彼此沒有任何干擾。比如 A 正在從一張銀行卡中取錢，在 A 取錢的過程結束前，B 不能向這張卡轉賬。
關於事務的隔離性資料庫提供了多種隔離級別，稍後會介紹到。

永續性（Durability）

永續性是指一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性 的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

2.事務的併發？事務隔離級別，每個級別會引發 什麼問題，MySQL 預設是哪個級別？

從理論上來說, 事務應該彼此完全隔離, 以避免併發事務所導致的問題，然而, 那樣會對效能產生極大的影響, 因為事務必須按順序執行，在實際開發中, 為了提升效能, 事務會以較低的隔離級別執行， 事務的隔離級別可以通過隔離事務屬性指定。

事務的併發問題

1、髒讀：事務 A 讀取了事務 B 更新的資料，然後 B 回滾操作，那麼 A 讀取到的資料是髒資料。

2、不可重複讀：事務 A 多次讀取同一資料，事務 B 在事務 A 多次讀取的過程中，對資料作了更新並提交，導致事務 A 多次讀取同一資料時，結果因此本事務先後兩次讀到的資料結果會不一致。

3、幻讀：幻讀解決了不重複讀，保證了同一個事務裡，查詢的結果都是事務開始時的狀態（一致性）。

例如：事務 T1 對一個表中所有的行的某個資料項做了從“1”修改為“2”的操作 這時事務 T2 又對這個表中插入了一行資料項，而這個資料項的數值還是為“1”並且提交給資料庫。 而操作事務 T1 的使用者如果再檢視剛剛修改的資料，會發現還有跟沒有修改一樣，其實這行是從事務 T2 中新增的，就好像產生幻覺一樣，這就是發生了幻讀。

小結：不可重複讀的和幻讀很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。解決不可重複讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

事務的隔離級別

事務隔離級別	髒讀	不可重複讀	幻讀
讀未提交 read-uncommitted	是	是	是
不可重複讀 read-committed	否	是	是
可重複讀 repeatable-read	否	否	是
序列化 serializable	否	否	否

讀未提交：另一個事務修改了資料，但尚未提交，而本事務中的 SELECT 會讀到這些未被提交的資料髒讀。

不可重複讀：事務 A 多次讀取同一資料，事務 B 在事務 A 多次讀取的過程中， 對資料作了更新並提交，導致事務 A 多次讀取同一資料時，結果因此本事務先後兩次讀到的資料結果會不一致。

可重複讀：在同一個事務裡，SELECT 的結果是事務開始時時間點的狀態，因此， 同樣的 SELECT 操作讀到的結果會是一致的。但是，會有幻讀現象

序列化：最高的隔離級別，在這個隔離級別下，不會產生任何異常。併發的事務， 就像事務是在一個個按照順序執行一樣
MySQL 預設的事務隔離級別為 repeatable-read

MySQL 支援 4 中事務隔離級別.

事務的隔離級別要得到底層資料庫引擎的支援, 而不是應用程式或者框架的支援.

Oracle 支援的 2 種事務隔離級別：
READ_COMMITED ， SERIALIZABLE

補充：

1.SQL 規範所規定的標準，不同的資料庫具體的實現可能會有些差異
2.MySQL 中預設事務隔離級別是“可重複讀”時並不會鎖住讀取到的行

事務隔離級別：未提交讀時，寫資料只會鎖住相應的行。

事務隔離級別為：可重複讀時，寫資料會鎖住整張表。

事務隔離級別為：序列化時，讀寫資料都會鎖住整張表。

隔離級別越高，越能保證資料的完整性和一致性，但是對併發效能的影響也越大， 魚和熊掌不可兼得啊。
對於多數應用程式，可以優先考慮把資料庫系統的隔離級別設為 Read Committed，它能夠避免髒讀取，而且具有較好的併發效能。
儘管它會導致不可重複讀、幻讀這些併發問題，在可能出現這類問題的個別場合， 可以由應用程式採用悲觀鎖或樂觀鎖來控制。

3.MySQL 常見的三種儲存引擎（InnoDB、
MyISAM、MEMORY）的區別？

MySQL 儲存引擎 MyISAM 與 InnoDB 如何選擇

MySQL 有多種儲存引擎，每種儲存引擎有各自的優缺點，可以擇優選擇使用：
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、
CSV、BLACKHOLE。

雖然 MySQL 裡的儲存引擎不只是 MyISAM 與 InnoDB 這兩個，但常用的就是兩個。

兩種儲存引擎的大致區別表現在：

InnoDB 支援事務，MyISAM 不支援，這一點是非常之重要。事務是一種高階的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM 就不可以了。

MyISAM 適合查詢以及插入為主的應用。

InnoDB 適合頻繁修改以及涉及到安全性較高的應用。

InnoDB 支援外來鍵，MyISAM 不支援。

從 MySQL5.5.5 以後，InnoDB 是預設引擎。

InnoDB 不支援 FULLTEXT 型別的索引。

InnoDB 中不儲存表的行數，如 select count() from table 時，InnoDB 需要掃描一遍整個表來計算有多少行，但是 MyISAM 只要簡單的讀出儲存好的行數即可。
注意的是，當 count()語句包含 where 條件時 MyISAM 也需要掃描整個表。

對於自增長的欄位，InnoDB 中必須包含只有該欄位的索引，但是在MyISAM 表中可以和其他欄位一起建立聯合索引。

DELETE FROM table 時，InnoDB 不會重新建立表，而是一行一行的 刪除， 效率非常慢。MyISAM 則會重建表。

InnoDB 支援行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like ‘%lee%’。

關於 MySQL 資料庫提供的兩種儲存引擎，MyISAM 與
InnoDB 選擇使用：

INNODB 會支援一些關聯式資料庫的高階功能，如事務功能和行級鎖，MyISAM 不支援。

MyISAM 的效能更優，佔用的儲存空間少，所以，選擇何種儲存引擎， 視具體應用而定。

如果你的應用程式一定要使用事務，毫無疑問你要選擇 INNODB 引擎。但要注意，INNODB 的行級鎖是有條件的。在 where 條件沒有使用主鍵時， 照樣會鎖全表。
比如 DELETE FROM mytable 這樣的刪除語句。

如果你的應用程式對查詢效能要求較高，就要使用 MyISAM 了。MyISAM 索引和資料是分開的，而且其索引是壓縮的，可以更好地利用記憶體。所以它的查詢效能明顯優於 INNODB 。壓縮後的索引也能節約一些磁碟空間。
MyISAM 擁有全文索引的功能，這可以極大地優化 LIKE 查詢的效率。

有人說 MyISAM 只能用於小型應用，其實這只是一種偏見。如果資料量比較大， 這是需要通過升級架構來解決，比如分表分庫，而不是單純地依賴儲存引擎。

現在一般都是選用 Innodb 了，主要是 MyISAM 的全表鎖，讀寫序列問題，併發效率鎖表，效率低，MyISAM 對於讀寫密集型應用一般是不會去選用的。

MEMORY 儲存引擎：

MEMORY 是 MySQL 中一類特殊的儲存引擎。它使用儲存在記憶體中的內容來建立表，而且資料全部放在記憶體中。
這些特性與前面的兩個很不同。

每個基於 MEMORY 儲存引擎的表實際對應一個磁碟檔案。該檔案的檔名與表名相同，型別為 frm 型別。該檔案中只儲存表的結構。而其資料檔案，都是儲存在記憶體中，這樣有利於資料的快速處理，提高整個表的效率。值得注意的是，伺服器需要有足夠的記憶體來維持 MEMORY 儲存引擎的表的使用。如果不需要了， 可以釋放記憶體，甚至刪除不需要的表。

MEMORY 預設使用雜湊索引。速度比使用 B 型樹索引快。當然如果你想用 B型樹索引，可以在建立索引時指定。

注意，MEMORY 用到的很少，因為它是把資料存到記憶體中，如果記憶體出現異常就會影響資料。如果重啟或者關機，所有資料都會消失。因此，基於 MEMORY 的表的生命週期很短，一般是一次性的。

4.MySQL 的 MyISAM 與 InnoDB 兩種儲存引擎在，事務、鎖級別，各自的適用場景？

事務處理上方面

MyISAM：強調的是效能，每次查詢具有原子性,其執行數度比 InnoDB 型別更快，但是不提供事務支援。

InnoDB：提供事務支援事務，外部鍵等高階資料庫功能。 具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

鎖級別

MyISAM：只支援表級鎖，使用者在操作 MyISAM 表時，select，update，delete，insert 語句都會給表自動加鎖，如果加鎖以後的表滿足 insert 併發的情況下，可以在表的尾部插入新的資料。

InnoDB：支援事務和行級鎖，是 innodb 的最大特色。行鎖大幅度提高了多使用者併發操作的新能。但是 InnoDB 的行鎖，只是在 WHERE 的主鍵是有效的，非主鍵的 WHERE 都會鎖全表的。

5.查詢語句不同元素（where、jion、limit、group
by、having 等等）執行先後順序？、

1.查詢中用到的關鍵詞主要包含六個，並且他們的順序依次為 select–from–where–group by–having–order by

其中 select 和 from 是必須的，其他關鍵詞是可選的，這六個關鍵詞的執行順序與 sql 語句的書寫順序並不是一樣的，而是按照下面的順序來執行：

from:需要從哪個資料表檢索資料
where:過濾表中資料的條件
group by:如何將上面過濾出的資料分組
having:對上面已經分組的資料進行過濾的條件
select:檢視結果集中的哪個列，或列的計算結果
order by :按照什麼樣的順序來檢視返回的資料

2.from 後面的表關聯，是自右向左解析 而where 條件的解析順序是自下而上的。
也就是說，在寫 SQL 文的時候，儘量把資料量小的表放在最右邊來進行關聯（用小表去匹配大表），而把能篩選出小量資料的條件放在 where 語句的最左邊（用小表去匹配大表）。

6.什麼是臨時表，臨時表什麼時候刪除？

臨時表可以手動刪除：

DROP TEMPORARY TABLE IF EXISTS temp_tb;

臨時表只在當前連線可見，當關閉連線時，MySQL 會自動刪除表並釋放所有空間。因此在不同的連線中可以建立同名的臨時表，並且操作屬於本連線的臨時表。

建立臨時表的語法與建立表語法類似，不同之處是增加關鍵字 TEMPORARY， 如：

CREATE TEMPORARY TABLE tmp_table ( NAME VARCHAR (10) NOT NULL,
time date NOT NULL

);
select * from tmp_table;

7.MySQL B+Tree 索引和 Hash 索引的區別？

Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位;
B+樹索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的 IO訪問;

那為什麼大家不都用 Hash 索引而還要使用 B+樹索引呢？

Hash 索引

1. Hash 索引僅僅能滿足"=",“IN"和”<=>"查詢，不能使用範圍查詢,因為經過相應的 Hash 演算法處理之後的 Hash 值的大小關係，並不能保證和 Hash 運算前完全一樣；
2. Hash 索引無法被用來避免資料的排序操作，因為 Hash 值的大小關係並不一定和 Hash 運算前的鍵值完全一樣；
   3.Hash 索引不能利用部分索引鍵查詢，對於組合索引，Hash 索引在計算
   Hash 值的時候是組合索引鍵合併後再一起計算 Hash 值，而不是單獨計算
   Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用；
3. Hash 索引在任何時候都不能避免表掃描，由於不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要回表查詢資料；
4. Hash 索引遇到大量Hash 值相等的情況後效能並不一定就會比B+樹索引高。

B+Tree 索引

MySQL 中，只有 HEAP/MEMORY 引擎才顯示支援 Hash 索引。

常用的 InnoDB 引擎中預設使用的是 B+樹索引，它會實時監控表上索引的使用情況，如果認為建立雜湊索引可以提高查詢效率，則自動在記憶體中的“自適應雜湊索引緩衝區”建立雜湊索引（在 InnoDB 中預設開啟自適應雜湊索引），通過觀察搜尋模式，MySQL 會利用 index key 的字首建立雜湊索引，如果一個表幾乎大部分都在緩衝池中，那麼建立一個雜湊索引能夠加快等值查詢。

B+樹索引和雜湊索引的明顯區別是：

如果是等值查詢，那麼雜湊索引明顯有絕對優勢，因為只需要經過一次演算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據連結串列往後掃描，直到找到相應的資料；

如果是範圍查詢檢索，這時候雜湊索引就毫無用武之地了，因為原先是有序的鍵值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；

同理，雜湊索引沒辦法利用索引完成排序，以及 like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；

雜湊索引也不支援多列聯合索引的最左匹配規則；

B+樹索引的關鍵字檢索效率比較平均，不像 B 樹那樣波動幅度大，在有大量重複鍵值情況下，雜湊索引的效率也是極低的，因為存在所謂的雜湊碰撞問題。

在大多數場景下，都會有範圍查詢、排序、分組等查詢特徵，用 B+樹索引就可以了。

8.sql 查詢語句確定建立哪種型別的索引，如何優化查詢：

效能優化過程中，選擇在哪個列上建立索引是最重要的步驟之一，可以考慮使用索引的主要有兩種型別的列：在 where 子句中出現的列，在 join 子句中出現的列。

考慮列中值的分佈，索引的列的基數越大，索引的效果越好。

使用短索引，如果對字串列進行索引，應該指定一個字首長度，可節省大量索引空間，提升查詢速度。

利用最左字首,顧名思義，就是最左優先，在多列索引，有體現:(ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age); )，所謂最左字首原則就是先要看第一列，在第一列滿足的條件下再看左邊第二列，以此類推。

不要過度建索引，只保持所需的索引。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的效能。
在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。

MySQL 只對一下操作符才使用索引：<,<=,=,>,>=,between,in

以及某些時候的 like(不以萬用字元%或_開頭的情形)。

9.聚集索引和非聚集索引區別？

聚合索引(clustered index) / 非聚合索引(nonclustered index)

根本區別：

聚集索引和非聚集索引的根本區別是表記錄的排列順序和與索引的排列順序是否一致。

聚集索引：

聚集索引表記錄的排列順序和索引的排列順序一致，所以查詢效率快，只要找到第一個索引值記錄，其餘就連續性的記錄在物理也一樣連續存放。聚集索引對應的缺點就是修改慢，因為為了保證表中記錄的物理和索引順序一致，在記錄插入的時候，會對資料頁重新排序。

聚集索引類似於新華字典中用拼音去查詢漢字，拼音檢索表於書記順序都是按照a~z 排列的，就像相同的邏輯順序於物理順序一樣，當你需要查詢 a,ai 兩個讀音的字，或是想一次尋找多個傻(sha)的同音字時，也許向後翻幾頁，或緊接著下一行就得到結果了。

非聚集索引

非聚集索引制定了表中記錄的邏輯順序，但是記錄的物理和索引不一定一致， 兩種索引都採用 B+樹結構，非聚集索引的葉子層並不和實際資料頁相重疊，而採用葉子層包含一個指向表中的記錄在資料頁中的指標方式。
非聚集索引層次多，不會造成資料重排。

非聚集索引類似在新華字典上通過偏旁部首來查詢漢字，檢索表也許是按照橫、豎、撇來排列的，但是由於正文中是 a~z 的拼音順序，所以就類似於邏輯地址於實體地址的不對應。同時適用的情況就在於分組，大數目的不同值，頻繁更新的列中，這些情況即不適合聚集索引。

10.有哪些鎖（樂觀鎖悲觀鎖），select 時怎麼加排它鎖？

悲觀鎖（Pessimistic Lock）

悲觀鎖的特點是先獲取鎖，再進行業務操作，即“悲觀”的認為獲取鎖是非常有可能失敗的，因此要先確保獲取鎖成功再進行業務操作。
通常所說的“一鎖二查三更新”即指的是使用悲觀鎖。
通常來講在資料庫上的悲觀鎖需要資料庫本身提供支援，即通過常用的 select … for update 操作來實現悲觀鎖。
當資料庫執行select for update 時會獲取被 select 中的資料行的行鎖，因此其他併發執行的select for update 如果試圖選中同一行則會發生排斥（需要等待行鎖被釋放）， 因此達到鎖的效果。
select for update 獲取的行鎖會在當前事務結束時自動釋放， 因此必須在事務中使用。

這裡需要注意的一點是不同的資料庫對select for update 的實現和支援都是有所區別的，例如 oracle 支援 select for update no wait，表示如果拿不到鎖立刻報錯，而不是等待，MySQL 就沒有 no wait 這個選項。
另外 MySQL 還有個問題是 select for update 語句執行中所有掃描過的行都會被鎖上，這一點很容易造成問題。
因此如果在 MySQL 中用悲觀鎖務必要確定走了索引，而不是全表掃描。

樂觀鎖（Optimistic Lock）

樂觀鎖，也叫樂觀併發控制，它假設多使用者併發的事務在處理時不會彼此互相影響，各事務能夠在不產生鎖的情況下處理各自影響的那部分資料。在提交資料更新之前，每個事務會先檢查在該事務讀取資料後，有沒有其他事務又修改了該資料。
如果其他事務有更新的話，那麼當前正在提交的事務會進行回滾。

樂觀鎖的特點先進行業務操作，不到萬不得已不去拿鎖。即“樂觀”的認為拿鎖多半是會成功的，因此在進行完業務操作需要實際更新資料的最後一步再去拿一下鎖就好。

樂觀鎖在資料庫上的實現完全是邏輯的，不需要資料庫提供特殊的支援。一般的做法是在需要鎖的資料上增加一個版本號，或者時間戳，然後按照如下方式實現：

樂觀鎖（給表加一個版本號欄位） 這個並不是樂觀鎖的定義，給表加版本號， 是資料庫實現樂觀鎖的一種方式。

1\. SELECT data AS old_data, version AS old_version FROM …;2\. 根據獲取的資料進行業務操作，得到 new_data 和 new_version3\. UPDATE SET data = new_data, version = new_version WHERE version = old_versionif (updated row > 0) {

// 樂觀鎖獲取成功，操作完成

} else {

// 樂觀鎖獲取失敗，回滾並重試

}

樂觀鎖在不發生取鎖失敗的情況下開銷比悲觀鎖小，但是一旦發生失敗回滾開銷則比較大，因此適合用在取鎖失敗概率比較小的場景，可以提升系統併發效能。

樂觀鎖還適用於一些比較特殊的場景，例如在業務操作過程中無法和資料庫保持連線等悲觀鎖無法適用的地方。

總結：

悲觀鎖和樂觀鎖是資料庫用來保證資料併發安全防止更新丟失的兩種方法，例子在 select … for update 前加個事務就可以防止更新丟失。悲觀鎖和樂觀鎖大部分場景下差異不大，一些獨特場景下有一些差別，一般我們可以從如下幾個方面來判斷。

響應速度：如果需要非常高的響應速度，建議採用樂觀鎖方案，成功就執行， 不成功就失敗，不需要等待其他併發去釋放鎖。

衝突頻率：如果衝突頻率非常高，建議採用悲觀鎖，保證成功率，如果衝突頻率大，樂觀鎖會需要多次重試才能成功，代價比較大。

重試代價：如果重試代價大，建議採用悲觀鎖。

11.非關係型資料庫和關係型資料庫區別，優勢比 較？
非關係型資料庫的優勢：

1.效能

NOSQL 是基於鍵值對的，可以想象成表中的主鍵和值的對應關係，而且不需要經過 SQL 層的解析，所以效能非常高。

2.可擴充套件性

同樣也是因為基於鍵值對，資料之間沒有耦合性，所以非常容易水平擴充套件。

關係型資料庫的優勢：

1.複雜查詢

可以用 SQL 語句方便的在一個表以及多個表之間做非常複雜的資料查詢。

2.事務支援

使得對於安全效能很高的資料訪問要求得以實現。

總結：

對於這兩類資料庫，對方的優勢就是自己的弱勢，反之亦然。

NOSQL 資料庫慢慢開始具備SQL 資料庫的一些複雜查詢功能，比如MongoDB。

對於事務的支援也可以用一些系統級的原子操作來實現例如樂觀鎖之類的方法來曲線救國，比如 Redis set nx。

12.資料庫三正規化，根據某個場景設計資料表？

所有欄位值都是不可分解的原子值。
在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。
資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

第一正規化(確保每列保持原子性)

第一正規化是最基本的正規化。如果資料庫表中的所有欄位值都是不可分解的原子值，就說明該資料庫表滿足了第一正規化。

第一正規化的合理遵循需要根據系統的實際需求來定。比如某些資料庫系統中需要用到“地址”這個屬性，本來直接將“地址”屬性設計成一個資料庫表的欄位就行。但是如果系統經常會訪問“地址”屬性中的“城市”部分，那麼就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行儲存，這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了資料庫的第一正規化，如下表所示。

上表所示的使用者資訊遵循了第一正規化的要求，這樣在對使用者使用城市進行分類的時候就非常方便，也提高了資料庫的效能。

第二正規化(確保表中的每列都和主鍵相關)

第二正規化在第一正規化的基礎之上更進一層。第二正規化需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。

比如要設計一個訂單資訊表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為資料庫表的聯合主鍵。

第三正規化(確保每列都和主鍵列直接相關,而不是間接相關)

第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

比如在設計一個訂單資料表的時候，可以將客戶編號作為一個外來鍵和訂單表建立相應的關係。而不可以在訂單表中新增關於客戶其它資訊（比如姓名、所屬公司等）的欄位。

13.資料庫的讀寫分離、主從複製，主從複製分析 的 7 個問題？

主從複製的幾種方式：

同步複製

所 謂 的 同 步 復 制 ， 意 思 是 master 的 變 化 ， 必 須 等 待
slave-1,slave-2,…,slave-n 完成後才能返回。 這樣，顯然不可取，也不是MySQL 複製的預設設定。比如，在 WEB 前端頁面上，使用者增加了條記錄， 需要等待很長時間。

非同步複製

如同 AJAX 請求一樣。master 只需要完成自己的資料庫操作即可。至於slaves 是否收到二進位制日誌，是否完成操作，不用關心,MySQL 的預設設定。

半同步複製

master 只保證 slaves 中的一個操作成功，就返回，其他 slave 不管。 這個功能，是由 google 為 MySQL 引入的。

主從複製分析的 7 個問題：

問題 1：master 的寫操作，slaves 被動的進行一樣的操作，保持資料一致性， 那麼 slave 是否可以主動的進行寫操作？

假設 slave 可以主動的進行寫操作，slave 又無法通知 master，這樣就導致了master 和 slave 資料不一致了。因此 slave 不應該進行寫操作，至少是 slave 上涉及到複製的資料庫不可以寫。實際上，這裡已經揭示了讀寫分離的概念。

問題 2：主從複製中，可以有 N 個 slave,可是這些 slave 又不能進行寫操作， 要他們幹嘛？

以實現資料備份。

類似於高可用的功能，一旦 master 掛了，可以讓 slave 頂上去，同時 slave 提升為 master。

異地容災，比如 master 在北京，地震掛了，那麼在上海的 slave 還可以繼續。主要用於實現 scale out,分擔負載,可以將讀的任務分散到 slaves 上。
【很可能的情況是，一個系統的讀操作遠遠多於寫操作，因此寫操作發向master，讀操作發向 slaves 進行操作】

問題 3：主從複製中有 master,slave1,slave2,…等等這麼多 MySQL 資料庫， 那比如一個 JAVA WEB 應用到底應該連線哪個資料庫?

當 然，我們在應用程式中可以這樣，insert/delete/update 這些更新資料庫的操作，用 connection(for master)進行操作，select 用 connection(for slaves)進行操作。那我們的應用程式還要完成怎麼從 slaves 選擇一個來執行 select，例如使用簡單的輪循演算法。

這樣的話，相當於應用程式完成了 SQL 語句的路由，而且與 MySQL 的主從複製架構非常關聯，一旦 master 掛了，某些 slave 掛了，那麼應用程式就要修改了。能不能讓應用程式與 MySQL 的主從複製架構沒有什麼太多關係呢？

找一個元件，application program 只需要與它打交道，用它來完成 MySQL 的代理，實現 SQL 語句的路由。

MySQL proxy 並不負責，怎麼從眾多的 slaves 挑一個？可以交給另一個元件(比如 haproxy)來完成。

這就是所謂的 MySQL READ WRITE SPLITE，MySQL 的讀寫分離。

問題 4：如果 MySQL proxy , direct , master 他們中的某些掛了怎麼辦？

總統一般都會弄個副總統，以防不測。同樣的，可以給這些關鍵的節點來個備份。

問題 5：當 master 的二進位制日誌每產生一個事件，都需要發往 slave，如果我們有 N 個 slave,那是發 N 次，還是隻發一次？

如果只發一次，發給了 slave-1，那 slave-2,slave-3,…它們怎麼辦？

顯 然，應該發 N 次。實際上，在 MySQL master 內部，維護 N 個執行緒，每一個執行緒負責將二進位制日誌檔案發往對應的 slave。master 既要負責寫操作，還的維護 N 個執行緒，負擔會很重。可以這樣，slave-1 是 master 的從，slave-1 又是 slave-2,slave-3,…的主，同時 slave-1 不再負責 select。slave-1 將 master 的複製執行緒的負擔，轉移到自己的身上。這就是所謂的多級複製的概念。

問題 6：當一個 select 發往 MySQL proxy，可能這次由 slave-2 響應，下次由slave-3 響應，這樣的話，就無法利用查詢快取了？

應該找一個共享式的快取，比如 memcache 來解決。將 slave-2,slave-3,…這些查詢的結果都快取至 mamcache 中。

問題 7：隨著應用的日益增長，讀操作很多，我們可以擴充套件 slave，但是如果master 滿足不了寫操作了，怎麼辦呢？

scale on ?更好的伺服器？ 沒有最好的，只有更好的，太貴了！！！
scale out ? 主從複製架構已經滿足不了。
可以分庫【垂直拆分】，分表【水平拆分】

14.使用 explain 優化 sql 和索引？

對於複雜、效率低的 sql 語句，我們通常是使用 explain sql 來分析 sql 語句，這個語句可以列印出，語句的執行。這樣方便我們分析，進行優化

table：顯示這一行的資料是關於哪張表的

type：這是重要的列，顯示連線使用了何種型別。從最好到最差的連線型別為 const、eq_reg、ref、range、index 和 ALL

all: full table scan ;MySQL 將遍歷全表以找到匹配的行；
index ： index scan; index 和 all 的區別在於 index 型別只遍歷索引；
range：索引範圍掃描，對索引的掃描開始於某一點，返回匹配值的行， 常見與 between ，< ,>等查詢；
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，常見於使用非唯一索引即唯一索引的非唯一字首進行查詢；
eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配， 常用於主鍵或者唯一索引掃描；
const，system：當 MySQL 對某查詢某部分進行優化，並轉為一個常量時，使用這些訪問型別。如果將主鍵置於 where 列表中，MySQL 就能將該查詢轉化為一個常量。

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從 WHERE 語句中選擇一個合適的語句
key：實際使用的索引。如果為 NULL，則沒有使用索引。很少的情況下，
MySQL 會選擇優化不足的索引。這種情況下，可以在 SELECT 語句中使用
USE INDEX（indexname） 來強制使用一個索引或者用 IGNORE INDEX（indexname）來強制 MySQL 忽略索引
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數
rows：MySQL 認為必須檢查的用來返回請求資料的行數
Extra：關於 MySQL 如何解析查詢的額外資訊。將在表 4.3 中討論，但這裡可以看到的壞的例子是 Using temporary 和 Using filesort，意思 MySQL 根本不能使用索引，結果是檢索會很慢。

15.MySQL 慢查詢怎麼解決？

slow_query_log 慢查詢開啟狀態。
slow_query_log_file 慢查詢日誌存放的位置（這個目錄需要 MySQL 的執行帳號的可寫許可權，一般設定為 MySQL 的資料存放目錄）。
long_query_time 查詢超過多少秒才記錄。

16.什麼是 內連線、外連線、交叉連線、笛卡爾積等？

內連線

內連線查詢操作列出與連線條件匹配的資料行，它使用比較運算子比較被連線列的 列值。

內連線分三種：

1.等值連線：在連線條件中使用等於號(=)運算子比較被連線列的列值，其查詢結果中列出被連線表中的所有列，包括其中的重複列。
例，下面使用等值連線列出 authors 和 publishers 表中位於同一城市的作者和出版社：

SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city

2.不等連線： 在連線條件使用除等於運算子以外的其它比較運算子比較被連線的 列的列值。
這些運算子包括>、>=、<=、<、!>、!<和<>。

3.自然連線：在連線條件中使用等於(=)運算子比較被連線列的列值，但它使用選擇列表指出查詢結果集合中所包括的列，並刪除連線表中的重複列。

例，在選擇列表中刪除 authors 和 publishers 表中重複列(city 和 state)：

SELECT a.*,p.pub_id,p.pub_name,p.country FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city

外連線

外連線，返回到查詢結果集合中的不僅包含符合連線條件的行，而且還包括左表(左外連線或左連線)、右表(右外連線或右連線)或兩個邊接表(全外連線)中的所有資料行。

left join(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄。

right join(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄。

例如 1：

SELECT a.*,b.* FROM luntan as a LEFT JOIN usertable as b ON a.username=b.username

例如 2：

SELECT a.*,b.* FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

交叉連線

交叉連線不帶 WHERE 子句，它返回被連線的兩個表所有資料行的“笛卡爾積”，返回到結果集合中的資料行數等於第一個表中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。

例，titles 表中有 6 類圖書，而 publishers 表中有 8 家出版社，則下 列交叉連線檢索到的記錄數將等於 6*8=48 行。

例如：

SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

笛卡爾積

笛卡爾積是兩個表每一個欄位相互匹配，去掉 where 或者 inner join 的等值 得出的結果就是笛卡爾積。笛卡爾積也等同於交叉連線。

總結

內連線: 只連線匹配的行。

左外連線: 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行。

右外連線: 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行。

全外連線: 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。

交叉連線 生成笛卡爾積－它不使用任何匹配或者選取條件，而是直接將一個資料來源中的每個行與另一個資料來源的每個行都一一匹配。

17.MySQL 都有什麼鎖，死鎖判定原理和具體場景，死鎖怎麼解決？

MySQL 都有什麼鎖：

MySQL 有三種鎖的級別：頁級、表級、行級。

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高,併發度最低。

行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。

頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般

什麼情況下會造成死鎖：

所謂死鎖: 是指兩個或兩個以上的程式在執行過程中。

因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。

此時稱系統處於死鎖狀態或系統產生了死鎖,這些永遠在互相等竺的程式稱為死鎖程式。

表級鎖不會產生死鎖.所以解決死鎖主要還是針對於最常用的 InnoDB。死鎖的關鍵在於：兩個(或以上)的 Session 加鎖的順序不一致。

那麼對應的解決死鎖問題的關鍵就是：讓不同的 session 加鎖有次序。

死鎖的解決辦法

查出的執行緒殺死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

設定鎖的超時時間

Innodb 行鎖的等待時間，單位秒。可在會話級別設定，RDS 例項該引數的預設值為 50（秒）。

生產環境不推薦使用過大的 innodb_lock_wait_timeout 引數值

該引數支援在會話級別修改，方便應用在會話級別單獨設定某些特殊操作的行鎖等待超時時間，如下：

set innodb_lock_wait_timeout=1000; —設定當前會話 Innodb 行鎖等待超時時間，單位秒。

18.varchar 和 char 的使用場景？

char 的長度是不可變的，而 varchar 的長度是可變的。定義一個 char[10]和 varchar[10]。

如果存進去的是‘csdn’,那麼 char 所佔的長度依然為 10，除了字元‘csdn’外， 後面跟六個空格，varchar 就立馬把長度變為 8了(英文字元佔用 2 個位元組)，取資料的時候，char 型別的要用 trim()去掉多餘的空格，而 varchar 是不需要的。

char 的存取速度還是要比 varchar 要快得多，因為其長度固定，方便程式的儲存與查詢。

char 也為此付出的是空間的代價，因為其長度固定，所以難免會有多餘的空格佔位符佔據空間，可謂是以空間換取時間效率。

varchar 是以空間效率為首位。

char 的儲存方式是：對英文字元（ASCII）佔用 1 個位元組，對一個漢字佔用兩個位元組。

varchar 的儲存方式是：對每個英文字元佔用 2 個位元組，漢字也佔用 2 個位元組。兩者的儲存資料都非 unicode 的字元資料。

19.MySQL 高併發環境解決方案？

MySQL 高併發環境解決方案 分庫 分表 分散式 增加二級快取

需求分析：網際網路單位 每天大量資料讀取，寫入，併發性高。

現有解決方式：水平分庫分表，由單點分佈到多點資料庫中，從而降低單點資料庫壓力。

叢集方案：解決 DB 當機帶來的單點 DB 不能訪問問題。

讀寫分離策略：極大限度提高了應用中 Read 資料的速度和併發量。

無法解決高寫入壓力。

20.資料庫崩潰時事務的恢復機制（REDO 日誌和
UNDO 日誌）？

Undo Log

Undo Log 是為了實現事務的原子性，在 MySQL 資料庫 InnoDB 儲存引擎中， 還用了 Undo Log 來實現多版本併發控制(簡稱：MVCC)。

事務的原子性(Atomicity)事務中的所有操作，要麼全部完成，要麼不做任何操作， 不能只做部分操作。
如果在執行的過程中發生了錯誤，要回滾(Rollback)到事務開始前的狀態，就像這個事務從來沒有執行過。

原理 Undo Log 的原理很簡單，為了滿足事務的原子性，在操作任何資料之前， 首先將資料備份到一個地方（這個儲存資料備份的地方稱為 UndoLog）。
然後進行資料的修改。如果出現了錯誤或者使用者執行了 ROLLBACK 語句，系統可以利用 Undo Log 中的備份將資料恢復到事務開始之前的狀態。

之所以能同時保證原子性和持久化，是因為以下特點：

更新資料前記錄 Undo log。
為了保證永續性，必須將資料在事務提交前寫到磁碟。只要事務成功提交， 資料必然已經持久化。
Undo log 必須先於資料持久化到磁碟。如果在 G,H 之間系統崩潰，undolog 是完整的， 可以用來回滾事務。

如果在 A-F 之間系統崩潰,因為資料沒有持久化到磁碟。所以磁碟上的資料還是保持在事務開始前的狀態。

缺陷：每個事務提交前將資料和 Undo Log 寫入磁碟，這樣會導致大量的磁碟IO，因此效能很低。

如果能夠將資料快取一段時間，就能減少 IO 提高效能。但是這樣就會喪失事務的永續性。因此引入了另外一種機制來實現持久化，即 Redo Log。

Redo Log

原理和 Undo Log 相反，Redo Log 記錄的是新資料的備份。在事務提交前，只要將 Redo Log 持久化即可，不需要將資料持久化。當系統崩潰時， 雖然資料沒有持久化，但是 Redo Log 已經持久化。系統可以根據 Redo Log 的內容，將所有資料恢復到最新的狀態。