一、Mysql中有哪幾種鎖？

1）表級(jí)鎖

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

2）行級(jí)鎖

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

3）頁面鎖

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

二、MySQL索引類型

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

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

1、B-Tree索引

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

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

B-Tree模型（InnoDB）：

B+Tree模型（InnoDB）：

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

B-樹索引的特點(diǎn)：

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

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

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

B+Tree對(duì)比BTree的優(yōu)點(diǎn)：

• 磁盤讀寫代價(jià)更低

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

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

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

2、HASH 索引

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

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

2）邏輯區(qū)分

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

1、普通索引

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

【示例】

CREATE INDEX index_id ON tb_student(id);

2、唯一索引

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

【示例】

CREATE UNIQUE INDEX index_id ON tb_student(id);

3、主鍵索引

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

4、全文索引

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

【示例】

CREATE FULLTEXT INDEX index_info ON tb_student(info);

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

5、空間索引

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

【示例】

CREATE SPATIAL INDEX index_line ON tb_student(line);

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

1、單列索引

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

示例

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

2、多列索引

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

示例

CREATE INDEX index_na ON tb_student(name,address);

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

1）MyISAM

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

2）InnoDB

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

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

1）原子性（Atomicity）

原子性指整個(gè)數(shù)據(jù)庫事務(wù)是不可分割的工作單位。只有使事務(wù)中所有的數(shù)據(jù)庫操作都執(zhí)行成功，才算整個(gè)事務(wù)成功。事務(wù)中任何一個(gè) 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）

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

四種隔離級(jí)別

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

在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少。讀取未提交的數(shù)據(jù)，也被稱之為臟讀（Dirty Read）。

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

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

Repeatable Read（可重讀）

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

Serializable（可串行化）

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

4）持久性（durability）

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

五、主從同步機(jī)制

1）主從同步過程

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

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

2）復(fù)制原理

1、異步復(fù)制

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

2、半同步復(fù)制

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

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

1）備份方式

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

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

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

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

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

2）備份工具

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

3）備份策略

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

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

七、MySQL死鎖及解決方案

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

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

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

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

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

死鎖解決方案

【原因】

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

【解決】

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

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

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