面試不得不知的Mysql殺手鐧

程序員讀書俱樂部

教育達人

主要分2層:

server層：

連接器：用戶連接認證

分析器：

優化器

執行器

引擎層：具體執行操作由引擎執行。

事務隔離機制

串行化：它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之,它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。

擴展：

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

不可重複讀：事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新並提交，導致事務A多次讀取同一數據時，結果不一致。

幻讀：指的是一個事務在前後兩次查詢同一個範圍的時候,後一次查詢看到了前一次查詢沒有看到的數據行。MVCC可避免幻讀。

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

四種事務隔離級別 ACID

1. 原子性（Atomicity）：事務開始後所有操作，要麼全部做完，要麼全部不做，不可能停滯在中間環節。靠undolog保證，rollback時執行。

2. 一致性（Consistency）：事務開始前和結束後，數據庫的完整性約束沒有被破壞 。（a、i、d）實現了c。

3. 隔離性（Isolation）：同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。（用鎖實現）

4. 持久性（Durability）：事務完成後，事務對數據庫的所有更新將被保存到數據庫，不能回滾。（redolog保證）

理解MVCC、多版本並發控制

innodb的MVCC，是通過在每行記錄後面保存兩個隱藏的列來實現的。這兩個列，一個是行的創建時間，一個保存行的過期時間。存儲的是系統版本號，不是真實的時間。每開始一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。就是樂觀鎖的實現。

闡述對數據的理解

一切皆數據，無數據不編程。

存儲引擎的區別

InnoDB mysql默認引擎，支持事務，行鎖、表鎖（沒有索引就會表鎖）、外鍵。數據文件frm 表結構、idb數據，是聚集索引，索引和數據在一起。

MyISAM 不支持事務和行鎖。支持表鎖。數據文件：frm表結構、myd表數據、myi表索引，是非聚集索引，索引和數據文件是分開的。

MEMORY 內存數據庫， 不能持久化，索引是hash。

索引原理

Mysql索引原理簡單概況就是InnoDB用的是B+數做索引，非葉子節點不存data，減少io次數，提高效率。

InnoDB為什麼推薦使用自增ID作為主鍵？

自增ID可以保證每次插入時B+索引是從右邊擴展的，可以避免B+樹和頻繁合並和分裂（對比使用UUID）。如果使用字符串主鍵和隨機主鍵，會使得數據隨機插入，效率比較差。

分庫分表

可參考mycat的思路：

事務：盡量避免事務。

跨庫Join的幾種解決思路

全局表

字段冗餘

數據同步，讀寫分離

系統層組裝 說起來容易，做起來難。

讀寫分離，查詢的放在只讀庫

limit：如果是limit 0，n。給db1、db2都發送一條。取結果級的最小值。

如果是limit m，n。需要先執行limit 0，n。再取結果級的m，n。非常耗時。

hash索引和btree索引

hash

Hash索引只支持等值比較，例如使用=，IN( )和<=>。對於WHERE price>100並不能加速查詢。

Hash 索引無法排序,因為hash是取模。

Hash 索引不支持多列聯合索引的最左匹配規則

Hash索引在任何時候都不能避免表掃描

Hash索引每次查詢要加載所有的索引數據到內存當中，而B+樹只需要根據匹配規則選擇對應的葉子數據加載即可。

sql查詢大部分都是範圍查找，而hash擅長的是等值判斷。

B+樹與B樹的不同在於：

所有關鍵字存儲在葉子節點，非葉子節點不存儲真正的data

為所有葉子節點（左右相鄰的節點之間）增加了一個鏈指針

explain

typetype顯示的是訪問類型，訪問類型表示我是以何種方式去訪問我們的數據，最容易想的是全表掃描，直接暴力的遍曆一張表去尋找需要的數據，效率非常低下，訪問的類型有很多，效率從最好到最壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般情況下，得保證查詢至少達到range級別，最好能達到ref

key實際使用的索引，如果為null，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。

ref顯示索引的哪一列被使用了，如果可能的話，是一個常數。

rows根據表的統計信息及索引使用情況，大致估算出找出所需記錄需要讀取的行數，此參數很重要，直接反應的sql找了多少數據，在完成目的的情況下越少越好，絕大部分rows小的語句執行一定很快。所以優化語句基本上都是在優化rows。

mysql鎖機制

InnoDB行級鎖都是基於索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。行級鎖的缺點是：由於需要請求大量的鎖資源，所以速度慢，內存消耗大。

InnoDB引擎默認的修改數據語句：update,delete,insert都會自動給涉及到的數據加上排他鎖。

共享鎖又稱讀鎖，是讀取操作創建的鎖。其他用戶可以並發讀取數據，但任何事務都不能對數據進行修改（獲取數據上的排他鎖），直到已釋放所有共享鎖。

排他鎖又稱寫鎖，如果事務T對數據A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲准排他鎖的事務既能讀數據，又能修改數據。

樂觀鎖：不是數據庫提供的鎖機制，通過版本號或者時間戳來實現樂觀鎖。1.查詢出商品信息

select (status,status,version) from t_goods where id=#{id}

2.根據商品信息生成訂單

3.修改商品status為2

update t_goods

set status=2,version=version+1

where id=#{id} and version=#{version};

悲觀鎖：在整個數據處理過程中，將數據處於鎖定狀態。悲觀鎖的實現，往往依靠數據庫提供的鎖機制。要使用悲觀鎖，必須關閉mysql數據庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果進行提交。set autocommit=0;

mysql日志

undolog：1.為了實現數據的原子性，操作數據時，先將數據備份到undolog。然後執行操作，如果需要rolback，就把unlog數據恢複。比如執行一條delete語句，就在undolog中執行一條insert語句。可以理解為回滾日志。日志總是和執行的操作相反。

redolog：

數據產生修改的時候，innodb先將數據寫到redolog中，並更新內存，此時更新完成，innodb會在合適的時候將數據同步到磁盤中。

超過固定的大小後，會覆蓋最先寫入的日志。

redolog，又稱重做日志文件，用於記錄事務操作的變化，記錄的是數據修改之後的值，不管事務是否提交都會記錄下來。在實例和介質失敗（media failure）時，redo log文件就能派上用場，如數據庫掉電，InnoDB存儲引擎會使用redo log恢複到掉電前的時刻，以此來保證數據的完整性。，crash-safe。

binlog：記錄了對MySQL數據庫執行更改的所有操作，不包括SELECT和SHOW這類操作。之前的操作都記錄binlog。寫的時候直接順序追加到binlog，效率高。先寫內存，再寫磁盤。主從複制依賴binlog。

binlog屬於mysql server層，是二進制文件。redolog、undolog屬於InnoDB，是sql語句。

16、Mysql的主從同步原理

原理：

數據操作寫入master的binlog，master啟動dump線程，為每個slave發送biblog的二進制。

slave啟動io線程和sql線程，I/O將master中的binlog寫入relay-log。sql線程將relay-log寫入數據庫。

mysql高可用

主從複制、MMM、Keepalived、MHA等。參考mysql各個集群方案的優劣