一、Mysql中有哪幾種鎖？

1）表級鎖

開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

2）行級鎖

開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

3）頁面鎖

開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

二、MySQL索引類型

1）存儲方式區(qū)分

根據(jù)存儲方式的不同，MySQL 中常用的索引在物理上分為 B-樹索引和 HASH 索引兩類，兩種不同類型的索引各有其不同的適用范圍。

1、B-Tree索引

它使用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲數(shù)據(jù)，實際上很多存儲引擎使用的是B+Tree。B+Tree和B-Tree的不同點在于：

• 非葉子節(jié)點只存儲鍵值信息
• 所有葉子節(jié)點之間都有鏈指針
• 數(shù)據(jù)記錄都存放在葉子節(jié)點中
• B-Tree是為磁盤等外存儲設(shè)備設(shè)計的一種平衡多路查找樹。

B-Tree模型（InnoDB）：

B+Tree模型（InnoDB）：

B-Tree索引與B+Tree索引的區(qū)別

B-樹索引的特點：

• 所有鍵值分布在整個樹中
• 任何關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個節(jié)點中
• 搜索有可能在非葉子節(jié)點結(jié)束
• 在關(guān)鍵字全集內(nèi)做一次查找，性能逼近二分查找算法

B+樹索引與B-樹索引的不同在于：

• 非葉子節(jié)點只存儲鍵值信息。
• 所有葉子節(jié)點之間都有一個鏈指針。
• 數(shù)據(jù)記錄都存放在葉子節(jié)點中。

B+Tree對比BTree的優(yōu)點：

• 磁盤讀寫代價更低

那么提升查找速度的關(guān)鍵就在于盡可能少的磁盤I/O，那么可以知道，每個節(jié)點中的key個數(shù)越多，那么樹的高度越小，需要I/O的次數(shù)越少，因此一般來說B+Tree比BTree更快，因為B+Tree的非葉節(jié)點中不存儲data，就可以存儲更多的key。

• 查詢速度更穩(wěn)定

由于B+Tree非葉子節(jié)點不存儲數(shù)據(jù)（data），因此所有的數(shù)據(jù)都要查詢至葉子節(jié)點，而葉子節(jié)點的高度都是相同的，因此所有數(shù)據(jù)的查詢速度都是一樣的。

2、HASH 索引

• 哈希（Hash）一般翻譯為“散列”，也有直接音譯成“哈?！钡?，就是把任意長度的輸入（又叫作預映射，pre-image）通過散列算法變換成固定長度的輸出，該輸出就是散列值。
• 哈希索引也稱為散列索引或 HASH 索引。MySQL 目前僅有 MEMORY 存儲引擎和 HEAP 存儲引擎支持這類索引。其中，MEMORY 存儲引擎可以支持 B-樹索引和 HASH 索引，且將 HASH 當成默認索引。
• HASH 索引不是基于樹形的數(shù)據(jù)結(jié)構(gòu)查找數(shù)據(jù)，而是根據(jù)索引列對應(yīng)的哈希值的方法獲取表的記錄行。哈希索引的最大特點是訪問速度快，但也存在下面的一些缺點：

1. MySQL 需要讀取表中索引列的值來參與散列計算，散列計算是一個比較耗時的操作。也就是說，相對于 B-樹索引來說，建立哈希索引會耗費更多的時間。
2. 不能使用 HASH 索引排序。
3. HASH 索引只支持等值比較，如“=”“IN()”或“<=>”。
4. HASH 索引不支持鍵的部分匹配，因為在計算 HASH 值的時候是通過整個索引值來計算的。

2）邏輯區(qū)分

根據(jù)索引的具體用途，MySQL 中的索引在邏輯上分為以下 5 類：

1、普通索引

• 普通索引是 MySQL 中最基本的索引類型，它沒有任何限制，唯一任務(wù)就是加快系統(tǒng)對數(shù)據(jù)的訪問速度。
• 普通索引允許在定義索引的列中插入重復值和空值。
• 創(chuàng)建普通索引時，使用的關(guān)鍵字是 INDEX。

【示例】

CREATE INDEX index_id ON tb_student(id);

2、唯一索引

• 唯一索引與普通索引類似，不同的是創(chuàng)建唯一性索引的目的不是為了提高訪問速度，而是為了避免數(shù)據(jù)出現(xiàn)重復。
• 唯一索引列的值必須唯一，允許有空值。如果是組合索引，則列值的組合必須唯一。
• 創(chuàng)建唯一索引使用 UNIQUE INDEX 關(guān)鍵字。

【示例】

CREATE UNIQUE INDEX index_id ON tb_student(id);

3、主鍵索引

• 顧名思義，主鍵索引就是專門為主鍵字段創(chuàng)建的索引，也屬于索引的一種。
• 主鍵索引是一種特殊的唯一索引，不允許值重復或者值為空。
• 創(chuàng)建主鍵索引通常使用 PRIMARY KEY 關(guān)鍵字。不能使用 CREATE INDEX 語句創(chuàng)建主鍵索引。

4、全文索引

• 全文索引主要用來查找文本中的關(guān)鍵字，只能在 CHAR、VARCHAR 或 TEXT 類型的列上創(chuàng)建。在 MySQL 中只有 MyISAM 存儲引擎支持全文索引。
• 全文索引允許在索引列中插入重復值和空值。
• 不過對于大容量的數(shù)據(jù)表，生成全文索引非常消耗時間和硬盤空間。
• 創(chuàng)建全文索引使用 FULLTEXT INDEX 關(guān)鍵字。

【示例】

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的存儲引擎必須是 MyISAM，info 字段必須是 CHAR、VARCHAR 和 TEXT 等類型。

5、空間索引

• 空間索引是對空間數(shù)據(jù)類型的字段建立的索引，使用 SPATIAL 關(guān)鍵字進行擴展。
• 創(chuàng)建空間索引的列必須將其聲明為 NOT NULL，空間索引只能在存儲引擎為 MyISAM 的表中創(chuàng)建。
• 空間索引主要用于地理空間數(shù)據(jù)類型 GEOMETRY。對于初學者來說，這類索引很少會用到。
• 創(chuàng)建普通索引時，使用的關(guān)鍵字是 SPATIAL INDEX。

【示例】

CREATE SPATIAL INDEX index_line ON tb_student(line);

3）實際使用區(qū)分

1、單列索引

• 單列索引就是索引只包含原表的一個列。在表中的單個字段上創(chuàng)建索引，單列索引只根據(jù)該字段進行索引。
• 單列索引可以是普通索引，也可以是唯一性索引，還可以是全文索引。只要保證該索引只對應(yīng)一個字段即可。

示例

CREATE INDEX index_addr ON tb_student(address(4));

2、多列索引

• 組合索引也稱為復合索引或多列索引。相對于單列索引來說，組合索引是將原表的多個列共同組成一個索引。多列索引是在表的多個字段上創(chuàng)建一個索引。該索引指向創(chuàng)建時對應(yīng)的多個字段，可以通過這幾個字段進行查詢。但是，只有查詢條件中使用了這些字段中第一個字段時，索引才會被使用。

示例

CREATE INDEX index_na ON tb_student(name,address);

三、MySQL數(shù)據(jù)庫中MyISAM和InnoDB的區(qū)別

1）MyISAM

• 不支持事務(wù)，但是每次查詢都是原子的；
• 支持表級鎖，即每次操作是對整個表加鎖；
• 存儲表的總行數(shù)，查詢總數(shù)很快；
• 一個MYISAM表有三個文件：索引文件、表結(jié)構(gòu)文件、數(shù)據(jù)文件；
• 可被壓縮，存儲空間較??；支持三種不同的存儲格式：靜態(tài)表(默認，但是注意數(shù)據(jù)末尾不能有空格，會被去掉)、動態(tài)表、壓縮表。
• 數(shù)據(jù)是以文件的形式存儲，所以在跨平臺的數(shù)據(jù)轉(zhuǎn)移中會很方便。在備份和恢復時可單獨針對某個表進行操作。
• 不支持外鍵；
• 支持 FULLTEXT類型的全文索引；
• 采用非聚集索引，索引文件的數(shù)據(jù)域存儲指向數(shù)據(jù)文件的指針。MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點的data域存放的是數(shù)據(jù)記錄的地址。

2）InnoDB

• 支持ACID的事務(wù)，支持事務(wù)的四種隔離級別；
• 支持行級鎖及外鍵約束：因此可以支持寫并發(fā)；
• 所有的表都保存在同一個數(shù)據(jù)文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB。
• 備份不方便，免費的方案可以是拷貝數(shù)據(jù)文件、備份 binlog，或者用 mysqldump，在數(shù)據(jù)量達到幾十 G 的時候就相對痛苦了。
• 不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
• 然InnoDB也使用B+Tree作為索引結(jié)構(gòu)，但具體實現(xiàn)方式卻與MyISAM截然不同。葉節(jié)點data域保存了完整的數(shù)據(jù)記錄。

四、事務(wù)的四大特性（ACID）

1）原子性（Atomicity）

原子性指整個數(shù)據(jù)庫事務(wù)是不可分割的工作單位。只有使事務(wù)中所有的數(shù)據(jù)庫操作都執(zhí)行成功，才算整個事務(wù)成功。事務(wù)中任何一個 SQL 語句執(zhí)行失敗，已經(jīng)執(zhí)行成功的 SQL 語句也必須撤銷，數(shù)據(jù)庫狀態(tài)應(yīng)該退回到執(zhí)行事務(wù)前的狀態(tài)。

2）一致性（consistency）

一致性指事務(wù)將數(shù)據(jù)庫從一種狀態(tài)轉(zhuǎn)變?yōu)橄乱环N一致的狀態(tài)。在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。

3）隔離性（isolation）

一個事務(wù)的影響在該事務(wù)提交前對其他事務(wù)都不可見——這通過鎖來實現(xiàn)。

四種隔離級別

Read Uncommitted（讀取未提交內(nèi)容）

在該隔離級別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本隔離級別很少用于實際應(yīng)用，因為它的性能也不比其他級別好多少。讀取未提交的數(shù)據(jù)，也被稱之為臟讀（Dirty Read）。

Read Committed（讀取提交內(nèi)容，臟讀，不可重復讀）

一個事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變。這種隔離級別 也支持所謂的不可重復讀（Nonrepeatable Read），因為同一事務(wù)的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結(jié)果。

Repeatable Read（可重讀）

這是MySQL的默認事務(wù)隔離級別，它確保同一事務(wù)的多個實例在并發(fā)讀取數(shù)據(jù)時，會看到同樣的數(shù)據(jù)行。不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。簡單的說，幻讀指當用戶讀取某一范圍的數(shù)據(jù)行時，另一個事務(wù)又在該范圍內(nèi)插入了新行，當用戶再讀取該范圍的數(shù)據(jù)行時，會發(fā)現(xiàn)有新的“幻影” 行。InnoDB和Falcon存儲引擎通過多版本并發(fā)控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。

Serializable（可串行化）

這是最高的隔離級別，它通過強制事務(wù)排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個讀的數(shù)據(jù)行上加上共享鎖。在這個級別，可能導致大量的超時現(xiàn)象和鎖競爭。

4）持久性（durability）

事務(wù)一旦提交，其結(jié)果就是永久性的。即使發(fā)生宕機等故障，數(shù)據(jù)庫也能將數(shù)據(jù)恢復。

五、主從同步機制

1）主從同步過程

主從同步過程中主服務(wù)器有一個工作線程I/O dump thread，從服務(wù)器有兩個工作線程I/O thread和SQL thread。

• 主庫把外界接收的SQL請求記錄到自己的binlog日志中（查詢操作不記錄）；
• 從庫的I/O thread去請求主庫的binlog日志，并將binlog日志寫到中繼日志中；
• 最后從庫SQL thread重做中繼日志的SQL語句。

2）復制原理

1、異步復制

異步復制是MySQL默認方式，主庫寫入binlog日志后即可成功返回客戶端，無須等待binlog日志傳遞給從庫的過程，但是一旦主庫宕機，就有可能出現(xiàn)丟失數(shù)據(jù)的情況。

2、半同步復制

• MySQL默認的復制方式是異步復制，但是當主庫宕機，在高可用架構(gòu)做準備切換，就會造成新的主庫丟失數(shù)據(jù)的現(xiàn)象。
• MySQL5.5版本之后引入了半同步復制，但是主從服務(wù)器必須同時安裝半同步復制插件。在該功能下，確保從庫接收完成主庫傳遞過來的binlog內(nèi)容已經(jīng)寫入到自己的relay log后才會通知主庫上面的等待線程。如果等待超時(超時參數(shù)：rpl_semi_sync_master_timeout)，則關(guān)閉半同步復制，并自動轉(zhuǎn)換為異步復制模式，直到至少有一臺從庫通知主庫已經(jīng)接收到binlog信息為止。
• 半同步復制提升了主從之間數(shù)據(jù)的一致性，讓復制更加安全可靠，在5.7 版本中又增加了rpl_semi_sync_master_wait_point參數(shù)，用來控制半同步模式下主庫返回給session事務(wù)成功之前的事務(wù)提交方式。

六、數(shù)據(jù)備份

1）備份方式

• 物理備份：一般就是通過tar,cp等命令直接打包復制數(shù)據(jù)庫的數(shù)據(jù)文件達到備份的效果

1. 冷備份：冷備份指的是當數(shù)據(jù)庫進行備份時, 數(shù)據(jù)庫不能進行讀寫操作, 即數(shù)據(jù)庫要下線

• 邏輯備份：邏輯備份是備份sql語句，在恢復的時候執(zhí)行備份的sql語句實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的重現(xiàn)。

熱備份：熱備份指的是當數(shù)據(jù)庫進行備份時, 數(shù)據(jù)庫的讀寫操作均不受影響

溫備份：溫備份指的是當數(shù)據(jù)庫進行備份時, 數(shù)據(jù)庫的讀操作可以執(zhí)行, 但是不能執(zhí)行寫操作

2）備份工具

• mysqldump：邏輯備份工具, 適用于所有的存儲引擎, 支持溫備、完全備份、部分備份、對于InnoDB存儲引擎支持熱備
• cp, tar 等歸檔復制工具 ：物理備份工具, 適用于所有的存儲引擎, 冷備、完全備份、部分備份
• lvm2 snapshot：幾乎熱備, 借助文件系統(tǒng)管理工具進行備份
• mysqlhotcopy：名不副實的的一個工具, 幾乎冷備, 僅支持MyISAM存儲引擎
• xtrabackup：一款非常強大的InnoDB/XtraDB熱備工具, 支持完全備份、增量備份, 由percona提供

3）備份策略

針對不同的場景下, 我們應(yīng)該制定不同的備份策略對數(shù)據(jù)庫進行備份, 一般情況下, 備份策略一般為以下幾種：

• 直接cp,tar復制數(shù)據(jù)庫文件（物理備份，冷備）：適合數(shù)據(jù)量小。
• lvm2快照+復制BIN LOGS（邏輯備份，熱備）：適合數(shù)據(jù)量一般，使用lvm2的快照對數(shù)據(jù)文件進行備份, 而后定期備份BINARY LOG達到增量備份的效果。
• mysqldump+復制BIN LOGS（邏輯備份，熱備）：適合數(shù)據(jù)量中等，先使用mysqldump對數(shù)據(jù)庫進行完全備份, 然后定期備份BINARY LOG達到增量備份的效果。
• xtrabackup（邏輯備份，熱備）：適合數(shù)據(jù)量很大，使用xtrabackup進行完全備份后, 定期使用xtrabackup進行增量備份或差異備份。

七、MySQL死鎖及解決方案

MySQL死鎖產(chǎn)生原因

所謂死鎖：是指兩個或兩個以上的進程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用，它們都將無法推進下去.此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖，這些永遠在互相等待的進程稱為死鎖進程。表級鎖不會產(chǎn)生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB。

產(chǎn)生死鎖的四個必要條件：

1. 互斥條件：一個資源每次只能被一個進程使用。
2. 請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放。
3. 不剝奪條件:進程已獲得的資源，在末使用完之前，不能強行剝奪。
4. 循環(huán)等待條件:若干進程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系。

這四個條件是死鎖的必要條件，只要系統(tǒng)發(fā)生死鎖，這些條件必然成立，而只要上述條件之一不滿足，就不會發(fā)生死鎖。

死鎖解決方案

【原因】

死鎖的關(guān)鍵在于：兩個(或以上)的Session加鎖的順序不一致。

【解決】

那么對應(yīng)的解決死鎖問題的關(guān)鍵就是：讓不同的session加鎖有次序。

最大限度的降低死鎖方法：

1. 按同一順序訪問對象。
2. 避免事務(wù)中的用戶交互。
3. 保持事務(wù)簡短并在一個批處理中。
4. 使用低隔離級別。
5. 使用綁定連接。