1. 什麼是MVCC
MVCC，Multi-Version Concurrency Control，多版本併發控制。MVCC 是一種併發控制的方法，一般在資料庫管理系統中，實現對資料庫的併發訪問；在程式語言中實現事務記憶體。
簡單來說，多版本並發控制 的思想就是保存資料的歷史版本，通過對資料行的多個版本管理來實現資料庫的並發控制。這樣我們就可以通過比較版本號決定資料是否顯示出來，讀取資料的時候不需要加鎖也可以保證事務的隔離效果。
可以認為 多版本並發控制（MVCC）是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現機制有所不同，但大都實現了非阻塞的讀操作，寫操作也只鎖定必要的??。
MySQL的大多數事務型存儲引擎實現的都不是簡單的行級鎖。基於提升並發性能的考慮，它們一般都同時實現了多版本並發控制（MVCC）。不僅是MySQL，包括Oracle、PostgreSQL等其他資料庫系統也都實現了MVCC，但各自的實現機制不盡相同，因為MVCC沒有一個統一的實現標準，典型的有樂觀（optimistic）並發控制和悲觀（pessimistic）並發控制。
多版本並發控制（MVCC）在一定程度上實現了讀寫並發，它只在 可重複讀（REPEATABLE READ）和 提交讀（READ COMMITTED）兩個隔離級別下工作。其他兩個隔離級別都和MVCC 不兼容，因為 未提交讀（READ UNCOMMITTED），總是讀取最新的資料行，而不是符合當前事務版本的資料行。而 可串行化（SERIALIZABLE）則會對所有讀取的行都加鎖。
2. MySQL的邏輯架構
連接層
主要工作是：連接處理、授權認證、安全防護等。
服務層（server層）
服務層用於處理核心服務，如標準的SQL接口、查詢解析、SQL優化和統計、全局的和引擎依賴的緩存與緩衝器等等。所有的與存儲引擎無關的工作，如過程、函數等，都會在這一層來處理。在該層上，服務器會解析查詢並創建相應的內部解析樹，並對其完成優化，如確定查詢表的順序，是否利用索引等，最後生成相關的執行操作。如果是SELECT 語句，服務器還會查詢內部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。
分析器
如果沒有命中緩存，就開始做詞法分析和語法分析
詞法分析：MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字符串“T”識別成“表名T”，把字符串“ID”識別成“列ID”
語法分析：判斷你輸入的這個SQL 語句是否滿足MySQL 語法。如果語句不對，就會收到“You have an error in your SQL syntax”的錯誤提醒
優化器
優化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序
執行器
開始執行的時候，要先判斷一下對這個表T 有沒有執行查詢的權限，如果沒有，就會返回沒有權限的錯誤(在工程實現上，如果命中查詢緩存，會在查詢緩存返回結果的時候，做權限驗證。查詢也會在優化器之前調用precheck 驗證權限)。如果有權限，就打開表繼續執行。打開表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的接口。
3. MySQL的讀寫鎖?
表鎖：不會出現死鎖，發生鎖衝突機率高，併發低
行鎖：會出現死鎖，發生鎖衝突機率低，併發高
鎖衝突：例如說事務A將某幾行上鎖後，事務B又對其上鎖，鎖不能共存否則會出現鎖衝突。（但是共享鎖可以共存，共享鎖和排它鎖不能共存，排它鎖和排他鎖也不可以）
死鎖：例如說兩個事務，事務A鎖住了1~5行，同時事務B鎖住了6~10行，此時事務A請求鎖住6~10行，就會阻塞直到事務B施放6~10行的鎖，而隨後事務B又請求鎖住1~5行，事務B也阻塞直到事務A釋放1~5行的鎖。死鎖發生時，會產生Deadlock錯誤。鎖是對錶操作的，所以自然鎖住全表的表鎖就不會出現死鎖。
行鎖的型別
共享鎖又稱：讀鎖。當一個事務對某幾行上讀鎖時，允許其他事務對這幾行進行讀操作，但不允許其進行寫操作，也不允許其他事務給這幾行上排它鎖，但允許上讀鎖。
排它鎖又稱：寫鎖。當一個事務對某幾個上寫鎖時，不允許其他事務寫，但允許讀。更不允許其他事務給這幾行上任何鎖。包括寫鎖。
共享鎖的寫法：lock in share mode
select * from test where math>60 lock in share mode；
排它鎖的寫法：for update
select * from test where math >60 for update；
行鎖的實現 注意幾點
1.行鎖必須有索引才能實現，否則會自動鎖全表
2.兩個事務不能鎖同一個索引
客戶端A讀取操作不需要等待客戶端B讀取完成並釋放鎖。但客戶端A進行寫操作的時候，會阻塞其他客戶端的讀和寫操作，直到客戶端A寫操作完成並釋放鎖，其他客戶端才可以進行讀寫。
上面說了這麼多，但實際上當客戶端A更新某行記錄的同時，客戶端B任然可讀取到資料，不會被阻塞，這是為什麼呢？
先來了解兩個東西，Redo Log（重做日誌）和Undo Log（回滾日誌）
redo log通常是物理日誌，記錄的是資料頁的物理修改，它用來恢復提交後的物理資料頁(恢復資料頁，且只能恢復到最後一次提交的位置)。
undo用來回滾行記錄到某個版本。undo log一般是邏輯日誌，根據每行記錄進行記錄。
如果看不懂，就這樣理解：當發生寫操作時，Innodb會把舊資料存儲到Undo Log（回滾日誌）中，新資料寫到Redo Log（重做日誌）中。
當客戶端A更新某行記錄的同時，客戶端B會被寫鎖阻塞，這時，客戶端B會去Undo Log中讀取舊資料。
4. 怎麼解決MySQL死鎖問題？
從死鎖的定義來看，MySQL 出現死鎖的幾個要素為：
兩個或者兩個以上事務
每個事務都已經持有鎖並且申請新的鎖
鎖資源同時只能被同一個事務持有或者不兼容
事務之間因為持有鎖和申請鎖導致彼此循環等待
一個用戶A 訪問表A(鎖住了表A),然後又訪問表B；另一個用戶B 訪問表B(鎖住了表B)，然後企圖訪問表A；這時用戶A由於用戶B已經鎖住表B，它必須等待用戶B釋放表B才能繼續，同樣用戶B要等用戶A釋放表A才能繼續，這就死鎖就產生了。
解決方法
這種死鎖比較常見，是由於程序的BUG產生的，除了調整程序的邏輯沒有其它的辦法。仔細分析程序的邏輯，對於資料庫的多表操作時，盡量按照相同的順序進行處理，盡量避免同時鎖定兩個資源，如操作A和B兩張表時，總是按先A後B的順序處理， 必須同時鎖定兩個資源時，要保證在任何時刻都應該按照相同的順序來鎖定資源。
如果在事務中執行了一條不滿足條件的update語句，則執行全表掃描，把行級鎖上升為表級鎖，多個這樣的事務執行後，就很容易產生死鎖和阻塞。類似的情況還有當表中的資料量非常龐大而索引建的過少或不合適的時候，使得經常發生全表掃描，最終應用系統會越來越慢，最終發生阻塞或死鎖。
解決方法
SQL語句中不要使用太複雜的關聯多表的查詢；使用“執行計劃”對SQL語句進行分析，對於有全表掃描的SQL語句，建立相應的索引進行優化。
5. InnoDB四大特性
插入緩存（insert buffer）
對非聚集索引的插入或者update，purge等操作，並非每一次直接插入索引頁，而是把若干對於同一頁面的更新緩存起來合併為一次操作，隨機IO –> 順序IO，避免隨機IO帶的性能消耗，提高寫性能。
二次寫（double write）
帶給innodb存儲引擎資料的可靠性
起因：當資料庫宕機時，可能發生資料庫寫一個頁面，而這個頁只寫了一部分，這就是所謂的部分寫失效（partial page write），它會導致資料丟失，這時是無法通過重做日誌恢復的，因為重做日誌記錄的是對頁的物理修改，如果頁本身已經損壞，重做日誌也無能為力。
恢復原理：mysql在恢復的時候是通過檢查page的checksum來決定這個頁是否需要恢復，checksum就是當前這個頁最後一個事務的事務號，如果系統找不到checksum，mysql就無法對該行資料進行寫入操作
自適應哈希索引（adapter hash index）
innodb存儲引擎會監控對錶上索引的查找，如果觀察到建立哈希索引可以帶來速度上的提升，則建立哈希索引，所以稱為自適應的。
自適應哈希索引通過緩衝池的B+樹構造而來，因為建立的速度很快，而且不需要將整個表都建立哈希索引，innodb會自動根據訪問頻率和模式來為某些頁建立哈希索引。
啟用自適應哈希索引後，讀寫速度提高兩倍，輔助索引的鏈接操作，性能提高五倍。
可以通過show engine innodb status\G來查看自適應哈西索引的使用情況。可以使用innodb_adaptive_hash_index來禁用和啟用hash索引，默認開啟。
6. MySQL事務隔離級別和實現原理
事務具有原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）四個特性，簡稱ACID，缺一不可。
臟讀指的是讀到了其他事務未提交的資料，未提交意味著這些資料可能會回滾，也就是可能最終不會存到資料庫中，也就是不存在的資料。讀到了並一定最終存在的資料，這就是臟讀。
可重複讀指的是在一個事務內，最開始讀到的資料和事務結束前的任意時刻讀到的同一批資料都是一致的。通常針對資料更新（UPDATE）操作。
對比可重複讀，不可重複讀指的是在同一事務內，不同的時刻讀到的同一批資料可能是不一樣的，可能會受到其他事務的影響，比如其他事務改了這批資料並提交了。通常針對資料更新（UPDATE）操作。
幻讀是針對資料插入（INSERT）操作來說的。假設事務A對某些行的內容作了更改，但是還未提交，此時事務B插入了與事務A更改前的記錄相同的記錄行，並且在事務A提交之前先提交了，而這時，在事務A中查詢，會發現好像剛剛的更改對於某些資料未起作用，但其實是事務B剛插入進來的，讓用戶感覺很魔幻，感覺出現了幻覺，這就叫幻讀。
SQL 標准定義了四種隔離級別，MySQL 全都支持。這四種隔離級別分別是：
讀未提交（READ UNCOMMITTED）
讀提交（READ COMMITTED）
可重複讀（REPEATABLE READ）
串行化（SERIALIZABLE）
從上往下，隔離強度逐漸增強，性能逐漸變差。採用哪種隔離級別要根據系統需求權衡決定，其中，可重複讀是MySQL 的默認級別。
事務隔離其實就是為了解決上面提到的髒讀、不可重複讀、幻讀這幾個問題，下面展示了4 種隔離級別對這三個問題的解決程度。
7. 什麼是全文索引？
MySQL 從 5.7.6 版本開始，MySQL就內建了ngram全文解析器，用來支援中文、日文、韓文分詞。在 MySQL 5.7.6 版本之前，全文索引只支援英文全文索引，不支援中文全文索引，需要利用分詞器把中文段落預處理拆分成單詞，然後存入資料庫。
ngram就是一段文字裡面連續的n個字的序列。ngram全文解析器能夠對文字進行分詞，每個單詞是連續的n個字的序列。
MySQL 中使用全域性變數ngram_token_size來配置ngram中n的大小，它的取值範圍是1到10，預設值是2。通常ngram_token_size設定為要查詢的單詞的最小字數。如果需要搜尋單字，就要把ngram_token_size設定為1。在預設值是2的情況下，搜尋單字是得不到任何結果的。因為中文單詞最少是兩個漢字，推薦使用預設值2。
常用的全文檢索模式有兩種：
1、自然語言模式(NATURAL LANGUAGE MODE) ，
自然語言模式是MySQL 預設的全文檢索模式。自然語言模式不能使用操作符，不能指定關鍵詞必須出現或者必須不能出現等複雜查詢。
2、BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支援指定關鍵詞必須出現或者必須不能出現或者關鍵詞的權重高還是低等複雜查詢。
8. 什麼是覆蓋索引？
覆蓋索引的定義有如下三種：
解釋一：就是select的資料列只用從索引中就能夠取得，不必從資料表中讀取，換句話說查詢列要被所使用的索引覆蓋。
解釋二：索引是高效找到行的一個方法，當能通過檢索索引就可以讀取想要的資料，那就不需要再到資料表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中欄位與條件的資料就叫做覆蓋索引。
解釋三：是非聚集組合索引的一種形式，它包括在查詢里的Select、Join和Where子句用到的所有列（即建立索引的欄位正好是覆蓋查詢語句[select子句]與查詢條件[Where子句]中所涉及的欄位，也即，索引包含了查詢正在查找的所有資料）。
通俗些說，覆蓋索引就是能在某次查詢中能同時覆蓋到所有的查詢結果和查詢條件的索引。這裡強調兩點：覆蓋索引首先是一個索引；覆蓋索引覆蓋的是什麼呢？覆蓋的是所有查要讀取的所有的列，同時也覆蓋所有的查詢條件。
索引條目通常遠小於資料行大小，所以如果只需讀取索引，那mysql就會極大的減少資料訪問量。這對緩存的負載非常重要，因為這種情況下響應時間大部分花費在資料拷貝上。覆蓋索引對於I/O密集型的應用也很有幫助，因為索引比資料更小，更容易全部放入內存中。
由於InnoDB的聚簇索引，覆蓋索引對InnoDB特別有用。InnoDB的二級索引在葉子節點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。減少IO，提高效率。
9. 索引失效的情況有哪些？
一、單表查詢時索引失效
1、mysql查詢單表時，查詢得到的結果集佔資料總量很大比例，mysql會認為全表掃描會優於索引，則不走索引。
例：比如企業人員信息表 （userInfo），字段（user_id、user_name、user_type（vachar）)，假設企業裡有10w人，一千個管理層user_type為1，9萬9千人為普通員工user_type為2，
sql：select * from userInfo where user_type='2' 這時user_type字段索引可能會失效
2、查詢時where條件後的字段類型要與表結構中該字段類型一致，
例：select * from userInfo where user_type=2 ，user_type在表結構中時字符類型，查詢時沒用有單引號包含起來則不走索引。
3、在where條件後對索引字段加了函數轉換或者運算邏輯（+、-、*、/、！、<>、%、like'%_'（%放在前面）、or、in (疑問、可能存在成本問題)、exist等）的處理，比如對時間戳字段進行日期格式化函數都會引起索引失效。
二、多表關聯查詢時索引失效
1、在表結構設計階段主表與關聯表之間的關聯字段的資料類型、資料長度、字段的編碼格式以及字段的排序規則需要保持一致
三、組合索引
1、當一張表的查詢方式比較固定，這時候可以嘗試創建聚集索引，查詢時應當遵從組合索引的規則，最左原則，查詢時使用最頻繁的一列放在最左邊，
例：index(user_id,user_name,user_type)這是一個組合索引，當查詢時如果想走索引則
sql：select * from userInfo where user_id='001' and user_name='小張' and user_type='1'；這個時候是走了索引的，但是
select * from userInfo where user_name='小張' and user_type='1'；這時user_id沒有在where條件內將不走索引；
此例，user_id字段必須出現在where後面，不然索引將不會生效。
10. Trace 是做什麼用的？
# 1. 開啟optimizer trace功能 (預設情況下它是關閉的):
SET optimizer_trace="enabled=on";
SELECT ...; # 這裡輸入你自己的查詢語句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 當你停止檢視語句的優化過程時，把optimizer trace功能關閉
SET optimizer_trace="enabled=off";
QUERY：表示我們的查詢語句。
TRACE：表示優化過程的JSON格式文字。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由於優化過程可能會輸出很多，如果超過某個限制時，多餘的文字將不會被顯示，這個欄位展示了被忽略的文字位元組數。
INSUFFICIENT_PRIVILEGES：表示是否沒有許可權檢視優化過程，預設值是0，只有某些特殊情況下才會是1，我們暫時不關心這個欄位的值。
11. 什麼是mysql主從同步？
當master(主)庫的資料發生變化的時候，變化會實時的同步到slave(從)庫。
資料是一個應用至關重要的一部分。從目的出發，主從同步有那麼點備份的意思，主庫（Master）將自己庫中的寫入同時同步給自己的從庫（Slave），當主庫發生某些不可預知的狀況，導致整個伺服器無法使用時，由於從庫中也有一份資料，所以資料可以做到快速恢復，不造成或者減少造成資料的損失。
當我們在MySQL中設定了主從之後，只要我們對Master節點進行了寫操作，這個操作將會被儲存到MySQL的binary-log（bin-log）紀錄檔當中，當slave連線到master的時候，master機器會為slave開啟binlog dump執行緒。當master 的 binlog發生變化的時候，Master的dump執行緒會通知slave，並將相應的binlog內容傳送給Slave。而Slave節點在主從同步開啟的時候，會建立兩個執行緒，一個I/O執行緒，一個SQL執行緒，這在我們後面的搭建中可以親眼看到。
I/0執行緒：該執行緒連結到master機器，master機器的binlog傳送到slave的時候，IO執行緒會將該紀錄檔內容寫在原生的中繼紀錄檔（Relay log）中。
SQL執行緒：該執行緒讀取中繼紀錄檔中的內容，並且根據中繼紀錄檔中的內容對Slave資料庫做相應的操作。
可能造成的問題：在寫請求相當多的情況下，可能會造成Slave資料和Master資料不一致的情況，這是因為紀錄檔傳輸過程中的短暫延遲、或者寫命令較多，系統速度不匹配造成的。
12. Join的使用技巧和優化
join用於多表中欄位之間的聯繫，在資料庫的DML (資料操作語言，即各種增刪改查操作）中有著重要的作用。
合理使用Join語句優化SQL有利於：
增加資料庫的處理效率，減少響應時間；
減少資料庫伺服器負載，增加伺服器穩定性；
減少伺服器通訊的網絡流量；
1. Join的分類：
內連接 Inner Join
全外連接 FULL Outer Join
左外連接 Left Outer Join
右外連接 Right Outer Join
交叉連接 Cross Join
MySQL按如下方式實現A LEFT JOIN B：
表B被設置為依賴於表A和A所依賴的所有表。 表A被設置為依賴於在LEFT JOIN條件中使用的所有表（除了B）。 LEFT JOIN條件用於決定如何從表B中檢索行（換句話說，不使用WHERE子句中的任何條件）。 所有標準連接優化都執行，不同之處在於一個表總是在它所依賴的所有表之後被讀取。 如果有循環依賴，則會發生錯誤。 所有標準的WHERE優化都被執行。 如果A中存在與WHERE子句匹配的行，但B中沒有與ON條件相匹配的行，則會生成一個額外的B行，並將所有列設置為NULL。 如果使用LEFT JOIN查找某些表中不存在的行，並且您有以下測試：在WHERE部分中，col_name是NULL，其中col_name是一個聲明為NOT NULL的列，MySQL將停止搜尋更多行（為瞭找到一行符合LEFT JOIN條件的特定組合鍵）。
RIGHT JOIN實現類似於LEFT JOIN，其表格角色顛倒瞭。 右連接轉換為等效的左連接
連接優化器計算連接表的順序。 由LEFT JOIN或STRAIGHT_JOIN強制的表讀順序可以幫助聯接優化器更快地完成工作，因為檢查的表排列更少。 這意味著如果執行以下類型的查詢，MySQL會對b執行完整掃描，因為LEFT JOIN強制在d之前讀取它：
那麼如何優化LEFT JOIN：
1、條件中盡量能夠過濾一些行將驅動表變得小一點，用小表去驅動大表
2、右表的條件列一定要加上索引（主鍵、唯一索引、前綴索引等），最好能夠使type達到range及以上（ref,eq_ref,const,system）
13. 資料庫為什麼使用B+樹而不是B樹
1、B樹只適合隨機檢索，而B+樹同時支持隨機檢索和順序檢索；
2、B+樹空間利用率更高，可減少I/O次數，磁盤讀寫代價更低。一般來說，索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上。這樣的話，索引查找過程中就要產生磁盤I/O消耗。B+樹的內部結點並沒有指向關鍵字具體信息的指針，只是作為索引使用，其內部結點比B樹小，盤塊能容納的結點中關鍵字數量更多，一次性讀入內存中可以查找的關鍵字也就越多，相對的，IO讀寫次數也就降低了。而IO讀寫次數是影響索引檢索效率的最大因素；
3、B+樹的查詢效率更加穩定。B樹搜索有可能會在非葉子結點結束，越靠近根節點的記錄查找時間越短，只要找到關鍵字即可確定記錄的存在，其性能等價於在關鍵字全集內做一次二分查找。而在B+樹中，順序檢索比較明顯，隨機檢索時，任何關鍵字的查找都必須走一條從根節點到葉節點的路，所有關鍵字的查找路徑長度相同，導致每一個關鍵字的查詢效率相當。
4、B-樹在提高了磁盤IO性能的同時並沒有解決元素遍歷的效率低下的問題。B+樹的葉子節點使用指針順序連接在一起，只要遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹不支持這樣的操作。
5、增刪文件（節點）時，效率更高。因為B+樹的葉子節點包含所有關鍵字，並以有序的鍊錶結構存儲，這樣可很好提高增刪效率。
14. In與Exists的區別
EXISTS操作符用來判斷一個子查詢是否返回數據行。如果一個子查詢返回了至少一個數據行，則 EXISTS 的計算結果為TRUE，否則計算結果為FALSE。
not exists與exists相反，也就是當exists條件有結果集返回時，loop到的記錄將被丟棄，否則將loop到的記錄加入結果集
EXISTS運算的結果只與子查詢是否返回數據行有關，子查詢中的列的數量或者名稱不影響運算結果。
總的來說，in查詢就是先將子查詢條件的記錄全都查出來，假設結果集為B，共有m條記錄，然後在將子查詢條件的結果集分解成m個，再進行m次查詢
15 該如何防止sql注入？我們通過以下三種方法進行防治sql注入
1.開啟php的魔術模式，,magic_quotes_gpc = on即可，當一些特殊字符出現在網站前端的時候，就會自動進行轉化，轉化成一些其他符號導致sql語句無法執行。
2.網站代碼裡寫入過濾sql特殊字符的代碼，對一些特殊字符進行轉化，比如單引號，逗號，*，（括號）AND 1=1 、反斜槓，select union等查詢的sql語句都進行安全過濾，限制這些字符的輸入，禁止提交到後端中去。
3.開啟網站防火牆，IIS防火牆，apache防火牆，nginx防火牆，都有內置的過濾sql注入的參數，當用戶輸入參數get、post、cookies方式提交過來的都會提前檢測攔截。
沒有留言:
張貼留言