MySQL原理介紹，你學會了嗎？
一、Mysql中有哪幾種鎖？
1）表級鎖
開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
2）行級鎖
開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
3）頁面鎖
開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。
二、MySQL索引類型
1）存儲方式區分
根據存儲方式的不同，MySQL 中常用的索引在物理上分為 B-樹索引和 HASH 索引兩類，兩種不同類型的索引各有其不同的適用范圍。
1、B-Tree索引
它使用B-Tree數據結構來存儲數據，實際上很多存儲引擎使用的是B+Tree。B+Tree和B-Tree的不同點在于：
- 非葉子節點只存儲鍵值信息
- 所有葉子節點之間都有鏈指針
- 數據記錄都存放在葉子節點中
- B-Tree是為磁盤等外存儲設備設計的一種平衡多路查找樹。
B-Tree模型（InnoDB）：
B+Tree模型（InnoDB）：
B-Tree索引與B+Tree索引的區別
B-樹索引的特點：
- 所有鍵值分布在整個樹中
- 任何關鍵字出現且只出現在一個節點中
- 搜索有可能在非葉子節點結束
- 在關鍵字全集內做一次查找，性能逼近二分查找算法
B+樹索引與B-樹索引的不同在于：
- 非葉子節點只存儲鍵值信息。
- 所有葉子節點之間都有一個鏈指針。
- 數據記錄都存放在葉子節點中。
B+Tree對比BTree的優點：
- 磁盤讀寫代價更低
那么提升查找速度的關鍵就在于盡可能少的磁盤I/O，那么可以知道，每個節點中的key個數越多，那么樹的高度越小，需要I/O的次數越少，因此一般來說B+Tree比BTree更快，因為B+Tree的非葉節點中不存儲data，就可以存儲更多的key。
- 查詢速度更穩定
由于B+Tree非葉子節點不存儲數據（data），因此所有的數據都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有數據的查詢速度都是一樣的。
2、HASH 索引
- 哈希（Hash）一般翻譯為“散列”，也有直接音譯成“哈希”的，就是把任意長度的輸入（又叫作預映射，pre-image）通過散列算法變換成固定長度的輸出，該輸出就是散列值。
- 哈希索引也稱為散列索引或 HASH 索引。MySQL 目前僅有 MEMORY 存儲引擎和 HEAP 存儲引擎支持這類索引。其中，MEMORY 存儲引擎可以支持 B-樹索引和 HASH 索引，且將 HASH 當成默認索引。
- HASH 索引不是基于樹形的數據結構查找數據，而是根據索引列對應的哈希值的方法獲取表的記錄行。哈希索引的最大特點是訪問速度快，但也存在下面的一些缺點：
- MySQL 需要讀取表中索引列的值來參與散列計算，散列計算是一個比較耗時的操作。也就是說，相對于 B-樹索引來說，建立哈希索引會耗費更多的時間。
- 不能使用 HASH 索引排序。
- HASH 索引只支持等值比較，如“=”“IN()”或“<=>”。
- HASH 索引不支持鍵的部分匹配，因為在計算 HASH 值的時候是通過整個索引值來計算的。
2）邏輯區分
根據索引的具體用途，MySQL 中的索引在邏輯上分為以下 5 類：
1、普通索引
- 普通索引是 MySQL 中最基本的索引類型，它沒有任何限制，唯一任務就是加快系統對數據的訪問速度。
- 普通索引允許在定義索引的列中插入重復值和空值。
- 創建普通索引時，使用的關鍵字是 INDEX。
【示例】
CREATE INDEX index_id ON tb_student(id);2、唯一索引
- 唯一索引與普通索引類似，不同的是創建唯一性索引的目的不是為了提高訪問速度，而是為了避免數據出現重復。
- 唯一索引列的值必須唯一，允許有空值。如果是組合索引，則列值的組合必須唯一。
- 創建唯一索引使用 UNIQUE INDEX 關鍵字。
【示例】
CREATE UNIQUE INDEX index_id ON tb_student(id);3、主鍵索引
- 顧名思義，主鍵索引就是專門為主鍵字段創建的索引，也屬于索引的一種。
- 主鍵索引是一種特殊的唯一索引，不允許值重復或者值為空。
- 創建主鍵索引通常使用 PRIMARY KEY 關鍵字。不能使用 CREATE INDEX 語句創建主鍵索引。
4、全文索引
- 全文索引主要用來查找文本中的關鍵字，只能在 CHAR、VARCHAR 或 TEXT 類型的列上創建。在 MySQL 中只有 MyISAM 存儲引擎支持全文索引。
- 全文索引允許在索引列中插入重復值和空值。
- 不過對于大容量的數據表，生成全文索引非常消耗時間和硬盤空間。
- 創建全文索引使用 FULLTEXT INDEX 關鍵字。
【示例】
CREATE FULLTEXT INDEX index_info ON tb_student(info);其中，index_info 的存儲引擎必須是 MyISAM，info 字段必須是 CHAR、VARCHAR 和 TEXT 等類型。
5、空間索引
- 空間索引是對空間數據類型的字段建立的索引，使用 SPATIAL 關鍵字進行擴展。
- 創建空間索引的列必須將其聲明為 NOT NULL，空間索引只能在存儲引擎為 MyISAM 的表中創建。
- 空間索引主要用于地理空間數據類型 GEOMETRY。對于初學者來說，這類索引很少會用到。
- 創建普通索引時，使用的關鍵字是 SPATIAL INDEX。
【示例】
CREATE SPATIAL INDEX index_line ON tb_student(line);3）實際使用區分
1、單列索引
- 單列索引就是索引只包含原表的一個列。在表中的單個字段上創建索引，單列索引只根據該字段進行索引。
- 單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對應一個字段即可。
示例
CREATE INDEX index_addr ON tb_student(address(4));2、多列索引
- 組合索引也稱為復合索引或多列索引。相對于單列索引來說，組合索引是將原表的多個列共同組成一個索引。多列索引是在表的多個字段上創建一個索引。該索引指向創建時對應的多個字段，可以通過這幾個字段進行查詢。但是，只有查詢條件中使用了這些字段中第一個字段時，索引才會被使用。
示例
CREATE INDEX index_na ON tb_student(name,address);三、MySQL數據庫中MyISAM和InnoDB的區別
1）MyISAM
- 不支持事務，但是每次查詢都是原子的；
- 支持表級鎖，即每次操作是對整個表加鎖；
- 存儲表的總行數，查詢總數很快；
- 一個MYISAM表有三個文件：索引文件、表結構文件、數據文件；
- 可被壓縮，存儲空間較小；支持三種不同的存儲格式：靜態表(默認，但是注意數據末尾不能有空格，會被去掉)、動態表、壓縮表。
- 數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作。
- 不支持外鍵；
- 支持 FULLTEXT類型的全文索引；
- 采用非聚集索引，索引文件的數據域存儲指向數據文件的指針。MyISAM引擎使用B+Tree作為索引結構，葉節點的data域存放的是數據記錄的地址。
2）InnoDB
- 支持ACID的事務，支持事務的四種隔離級別；
- 支持行級鎖及外鍵約束：因此可以支持寫并發；
- 所有的表都保存在同一個數據文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統文件的大小，一般為2GB。
- 備份不方便，免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十 G 的時候就相對痛苦了。
- 不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
- 然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。葉節點data域保存了完整的數據記錄。
四、事務的四大特性（ACID）
1）原子性（Atomicity）
原子性指整個數據庫事務是不可分割的工作單位。只有使事務中所有的數據庫操作都執行成功，才算整個事務成功。事務中任何一個 SQL 語句執行失敗，已經執行成功的 SQL 語句也必須撤銷，數據庫狀態應該退回到執行事務前的狀態。
2）一致性（consistency）
一致性指事務將數據庫從一種狀態轉變為下一種一致的狀態。在事務開始之前和事務結束以后，數據庫的完整性約束沒有被破壞。
3）隔離性（isolation）
一個事務的影響在該事務提交前對其他事務都不可見——這通過鎖來實現。
四種隔離級別
Read Uncommitted（讀取未提交內容）
在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。
Read Committed（讀取提交內容，臟讀，不可重復讀）
一個事務只能看見已經提交事務所做的改變。這種隔離級別 也支持所謂的不可重復讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結果。
Repeatable Read（可重讀）
這是MySQL的默認事務隔離級別，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。簡單的說，幻讀指當用戶讀取某一范圍的數據行時，另一個事務又在該范圍內插入了新行，當用戶再讀取該范圍的數據行時，會發現有新的“幻影” 行。InnoDB和Falcon存儲引擎通過多版本并發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。
Serializable（可串行化）
這是最高的隔離級別，它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。
4）持久性（durability）
事務一旦提交，其結果就是永久性的。即使發生宕機等故障，數據庫也能將數據恢復。
五、主從同步機制
1）主從同步過程
主從同步過程中主服務器有一個工作線程I/O dump thread，從服務器有兩個工作線程I/O thread和SQL thread。
- 主庫把外界接收的SQL請求記錄到自己的binlog日志中（查詢操作不記錄）；
- 從庫的I/O thread去請求主庫的binlog日志，并將binlog日志寫到中繼日志中；
- 最后從庫SQL thread重做中繼日志的SQL語句。
2）復制原理
1、異步復制
異步復制是MySQL默認方式，主庫寫入binlog日志后即可成功返回客戶端，無須等待binlog日志傳遞給從庫的過程，但是一旦主庫宕機，就有可能出現丟失數據的情況。
2、半同步復制
- MySQL默認的復制方式是異步復制，但是當主庫宕機，在高可用架構做準備切換，就會造成新的主庫丟失數據的現象。
- MySQL5.5版本之后引入了半同步復制，但是主從服務器必須同時安裝半同步復制插件。在該功能下，確保從庫接收完成主庫傳遞過來的binlog內容已經寫入到自己的relay log后才會通知主庫上面的等待線程。如果等待超時(超時參數：rpl_semi_sync_master_timeout)，則關閉半同步復制，并自動轉換為異步復制模式，直到至少有一臺從庫通知主庫已經接收到binlog信息為止。
- 半同步復制提升了主從之間數據的一致性，讓復制更加安全可靠，在5.7 版本中又增加了rpl_semi_sync_master_wait_point參數，用來控制半同步模式下主庫返回給session事務成功之前的事務提交方式。
六、數據備份
1）備份方式
- 物理備份：一般就是通過tar,cp等命令直接打包復制數據庫的數據文件達到備份的效果
- 冷備份：冷備份指的是當數據庫進行備份時, 數據庫不能進行讀寫操作, 即數據庫要下線
- 邏輯備份：邏輯備份是備份sql語句，在恢復的時候執行備份的sql語句實現數據庫數據的重現。
熱備份：熱備份指的是當數據庫進行備份時, 數據庫的讀寫操作均不受影響
溫備份：溫備份指的是當數據庫進行備份時, 數據庫的讀操作可以執行, 但是不能執行寫操作
2）備份工具
- mysqldump：邏輯備份工具, 適用于所有的存儲引擎, 支持溫備、完全備份、部分備份、對于InnoDB存儲引擎支持熱備
- cp, tar 等歸檔復制工具 ：物理備份工具, 適用于所有的存儲引擎, 冷備、完全備份、部分備份
- lvm2 snapshot：幾乎熱備, 借助文件系統管理工具進行備份
- mysqlhotcopy：名不副實的的一個工具, 幾乎冷備, 僅支持MyISAM存儲引擎
- xtrabackup：一款非常強大的InnoDB/XtraDB熱備工具, 支持完全備份、增量備份, 由percona提供
3）備份策略
針對不同的場景下, 我們應該制定不同的備份策略對數據庫進行備份, 一般情況下, 備份策略一般為以下幾種：
- 直接cp,tar復制數據庫文件（物理備份，冷備）：適合數據量小。
- lvm2快照+復制BIN LOGS（邏輯備份，熱備）：適合數據量一般，使用lvm2的快照對數據文件進行備份, 而后定期備份BINARY LOG達到增量備份的效果。
- mysqldump+復制BIN LOGS（邏輯備份，熱備）：適合數據量中等，先使用mysqldump對數據庫進行完全備份, 然后定期備份BINARY LOG達到增量備份的效果。
- xtrabackup（邏輯備份，熱備）：適合數據量很大，使用xtrabackup進行完全備份后, 定期使用xtrabackup進行增量備份或差異備份。
七、MySQL死鎖及解決方案
MySQL死鎖產生原因
所謂死鎖：是指兩個或兩個以上的進程在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用，它們都將無法推進下去.此時稱系統處于死鎖狀態或系統產生了死鎖，這些永遠在互相等待的進程稱為死鎖進程。表級鎖不會產生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB。
產生死鎖的四個必要條件：
- 互斥條件：一個資源每次只能被一個進程使用。
- 請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放。
- 不剝奪條件:進程已獲得的資源，在末使用完之前，不能強行剝奪。
- 循環等待條件:若干進程之間形成一種頭尾相接的循環等待資源關系。
這四個條件是死鎖的必要條件，只要系統發生死鎖，這些條件必然成立，而只要上述條件之一不滿足，就不會發生死鎖。
死鎖解決方案
【原因】
死鎖的關鍵在于：兩個(或以上)的Session加鎖的順序不一致。
【解決】
那么對應的解決死鎖問題的關鍵就是：讓不同的session加鎖有次序。
最大限度的降低死鎖方法：
- 按同一順序訪問對象。
- 避免事務中的用戶交互。
- 保持事務簡短并在一個批處理中。
- 使用低隔離級別。
- 使用綁定連接。
