摘要：串行最高的隔離級(jí)別，完全服從的隔離級(jí)別。但是這將嚴(yán)重影響程序的性能。此外，垂直分區(qū)可以簡(jiǎn)化表的結(jié)構(gòu)，易于維護(hù)。

我自己總結(jié)的Java學(xué)習(xí)的一些知識(shí)點(diǎn)以及面試問題，目前已經(jīng)開源，會(huì)一直完善下去，歡迎建議和指導(dǎo)歡迎Star： https://github.com/Snailclimb/Java_Guide

書籍推薦

《高性能MySQL : 第3版》

文字教程推薦

MySQL 教程（菜鳥教程）

MySQL教程（易百教程）

視頻教程推薦

基礎(chǔ)入門： 與MySQL的零距離接觸-慕課網(wǎng)

Mysql開發(fā)技巧： MySQL開發(fā)技巧（一）　　MySQL開發(fā)技巧（二）　　MySQL開發(fā)技巧（三）

Mysql5.7新特性及相關(guān)優(yōu)化技巧： MySQL5.7版本新特性　　性能優(yōu)化之MySQL優(yōu)化

MySQL集群（PXC）入門　　MyCAT入門及應(yīng)用

常見問題總結(jié)

①存儲(chǔ)引擎

MySQL常見的兩種存儲(chǔ)引擎：MyISAM與InnoDB的愛恨情仇

②字符集及校對(duì)規(guī)則

字符集指的是一種從二進(jìn)制編碼到某類字符符號(hào)的映射。校對(duì)規(guī)則則是指某種字符集下的排序規(guī)則。Mysql中每一種字符集都會(huì)對(duì)應(yīng)一系列的校對(duì)規(guī)則。

Mysql采用的是類似繼承的方式指定字符集的默認(rèn)值，每個(gè)數(shù)據(jù)庫(kù)以及每張數(shù)據(jù)表都有自己的默認(rèn)值，他們逐層繼承。比如：某個(gè)庫(kù)中所有表的默認(rèn)字符集將是該數(shù)據(jù)庫(kù)所指定的字符集（這些表在沒有指定字符集的情況下，才會(huì)采用默認(rèn)字符集） PS：整理自《Java工程師修煉之道》

詳細(xì)內(nèi)容可以參考： MySQL字符集及校對(duì)規(guī)則的理解

③索引相關(guān)的內(nèi)容（數(shù)據(jù)庫(kù)使用中非常關(guān)鍵的技術(shù)，合理正確的使用索引可以大大提高數(shù)據(jù)庫(kù)的查詢性能）

　　Mysql索引使用的數(shù)據(jù)結(jié)構(gòu)主要有BTree索引 和 哈希索引 。對(duì)于哈希索引來(lái)說(shuō)，底層的數(shù)據(jù)結(jié)構(gòu)就是哈希表，因此在絕大多數(shù)需求為單條記錄查詢的時(shí)候，可以選擇哈希索引，查詢性能最快；其余大部分場(chǎng)景，建議選擇BTree索引。

　　Mysql的BTree索引使用的是B數(shù)中的B+Tree，但對(duì)于主要的兩種存儲(chǔ)引擎的實(shí)現(xiàn)方式是不同的。

　　MyISAM: B+Tree葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。在索引檢索的時(shí)候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其data域的值，然后以data域的值為地址讀取相應(yīng)的數(shù)據(jù)記錄。這被稱為“非聚簇索引”。

　　InnoDB: 其數(shù)據(jù)文件本身就是索引文件。相比MyISAM，索引文件和數(shù)據(jù)文件是分離的，其表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。這被稱為“聚簇索引（或聚集索引）”。而其余的索引都作為輔助索引，輔助索引的data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址，這也是和MyISAM不同的地方。在根據(jù)主索引搜索時(shí)，直接找到key所在的節(jié)點(diǎn)即可取出數(shù)據(jù)；在根據(jù)輔助索引查找時(shí)，則需要先取出主鍵的值，在走一遍主索引。 因此，在設(shè)計(jì)表的時(shí)候，不建議使用過(guò)長(zhǎng)的字段作為主鍵，也不建議使用非單調(diào)的字段作為主鍵，這樣會(huì)造成主索引頻繁分裂。 PS：整理自《Java工程師修煉之道》

詳細(xì)內(nèi)容可以參考：

干貨：mysql索引的數(shù)據(jù)結(jié)構(gòu)

MySQL優(yōu)化系列（三）--索引的使用、原理和設(shè)計(jì)優(yōu)化

④查詢緩存的使用

my.cnf加入以下配置，重啟Mysql開啟查詢緩存

query_cache_type=1
query_cache_size=600000

Mysql執(zhí)行以下命令也可以開啟查詢緩存

set global  query_cache_type=1;
set global  query_cache_size=600000;

如上，開啟查詢緩存后在同樣的查詢條件以及數(shù)據(jù)情況下，會(huì)直接在緩存中返回結(jié)果。這里的查詢條件包括查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫(kù)、客戶端協(xié)議版本號(hào)等一些可能影響結(jié)果的信息。因此任何兩個(gè)查詢?cè)谌魏巫址系牟煌紩?huì)導(dǎo)致緩存不命中。此外，如果查詢中包含任何用戶自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、Mysql庫(kù)中的系統(tǒng)表，其查詢結(jié)果也不會(huì)被緩存。

緩存建立之后，Mysql的查詢緩存系統(tǒng)會(huì)跟蹤查詢中涉及的每張表，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。

緩存雖然能夠提升數(shù)據(jù)庫(kù)的查詢性能，但是緩存同時(shí)也帶來(lái)了額外的開銷，每次查詢后都要做一次緩存操作，失效后還要銷毀。 因此，開啟緩存查詢要謹(jǐn)慎，尤其對(duì)于寫密集的應(yīng)用來(lái)說(shuō)更是如此。如果開啟，要注意合理控制緩存空間大小，一般來(lái)說(shuō)其大小設(shè)置為幾十MB比較合適。此外，還可以通過(guò)sql_cache和sql_no_cache來(lái)控制某個(gè)查詢語(yǔ)句是否需要緩存：

select sql_no_cache count(*) from usr;

⑤事務(wù)機(jī)制

關(guān)系性數(shù)據(jù)庫(kù)需要遵循ACID規(guī)則，具體內(nèi)容如下：

原子性： 事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性確保動(dòng)作要么全部完成，要么完全不起作用；

一致性： 執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致；

隔離性： 并發(fā)訪問數(shù)據(jù)庫(kù)時(shí)，一個(gè)用戶的事物不被其他事物所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫(kù)是獨(dú)立的；

持久性: 一個(gè)事務(wù)被提交之后。它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫(kù) 發(fā)生故障也不應(yīng)該對(duì)其有任何影響。

為了達(dá)到上述事務(wù)特性，數(shù)據(jù)庫(kù)定義了幾種不同的事務(wù)隔離級(jí)別：

READ_UNCOMMITTED（未授權(quán)讀?。? 最低的隔離級(jí)別，允許讀取尚未提交的數(shù)據(jù)變更，可能會(huì)導(dǎo)致臟讀、幻讀或不可重復(fù)讀

READ_COMMITTED（授權(quán)讀取）: 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生

REPEATABLE_READ（可重復(fù)讀）: 對(duì)同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。

SERIALIZABLE（串行）: 最高的隔離級(jí)別，完全服從ACID的隔離級(jí)別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說(shuō)，該級(jí)別可以防止臟讀、不可重復(fù)讀以及幻讀。但是這將嚴(yán)重影響程序的性能。通常情況下也不會(huì)用到該級(jí)別。

這里需要注意的是：Mysql 默認(rèn)采用的 REPEATABLE_READ隔離級(jí)別 Oracle 默認(rèn)采用的 READ_COMMITTED隔離級(jí)別.

事務(wù)隔離機(jī)制的實(shí)現(xiàn)基于鎖機(jī)制和并發(fā)調(diào)度。其中并發(fā)調(diào)度使用的是MVVC（多版本并發(fā)控制），通過(guò)保存修改的舊版本信息來(lái)支持并發(fā)一致性讀和回滾等特性。

詳細(xì)內(nèi)容可以參考： 可能是最漂亮的Spring事務(wù)管理詳解

⑥鎖機(jī)制與InnoDB鎖算法

MyISAM和InnoDB存儲(chǔ)引擎使用的鎖：

MyISAM采用表級(jí)鎖(table-level locking)。

InnoDB支持行級(jí)鎖(row-level locking)和表級(jí)鎖,默認(rèn)為行級(jí)鎖

表級(jí)鎖和行級(jí)鎖對(duì)比：

表級(jí)鎖： Mysql中鎖定 粒度最大 的一種鎖，對(duì)當(dāng)前操作的整張表加鎖，實(shí)現(xiàn)簡(jiǎn)單，資源消耗也比較少，加鎖快，不會(huì)出現(xiàn)死鎖。其鎖定粒度最大，觸發(fā)鎖沖突的概率最高，并發(fā)度最低，MyISAM和 InnoDB引擎都支持表級(jí)鎖。

行級(jí)鎖： Mysql中鎖定 粒度最小 的一種鎖，只針對(duì)當(dāng)前操作的行進(jìn)行加鎖。 行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖突。其加鎖粒度最小，并發(fā)度高，但加鎖的開銷也最大，加鎖慢，會(huì)出現(xiàn)死鎖。

詳細(xì)內(nèi)容可以參考：
Mysql鎖機(jī)制簡(jiǎn)單了解一下

InnoDB存儲(chǔ)引擎的鎖的算法有三種：

Record lock：?jiǎn)蝹€(gè)行記錄上的鎖

Gap lock：間隙鎖，鎖定一個(gè)范圍，不包括記錄本身

Next-key lock：record+gap 鎖定一個(gè)范圍，包含記錄本身

相關(guān)知識(shí)點(diǎn)：

1. innodb對(duì)于行的查詢使用next-key lock
2. Next-locking keying為了解決Phantom Problem幻讀問題
3. 當(dāng)查詢的索引含有唯一屬性時(shí)，將next-key lock降級(jí)為record key
4. Gap鎖設(shè)計(jì)的目的是為了阻止多個(gè)事務(wù)將記錄插入到同一范圍內(nèi)，而這會(huì)導(dǎo)致幻讀問題的產(chǎn)生
5. 有兩種方式顯式關(guān)閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock） A. 將事務(wù)隔離級(jí)別設(shè)置為RC B. 將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1

⑦大表優(yōu)化

當(dāng)MySQL單表記錄數(shù)過(guò)大時(shí)，數(shù)據(jù)庫(kù)的CRUD性能會(huì)明顯下降，一些常見的優(yōu)化措施如下：

限定數(shù)據(jù)的范圍： 務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語(yǔ)句。比如：我們當(dāng)用戶在查詢訂單歷史的時(shí)候，我們可以控制在一個(gè)月的范圍內(nèi)。；

讀/寫分離： 經(jīng)典的數(shù)據(jù)庫(kù)拆分方案，主庫(kù)負(fù)責(zé)寫，從庫(kù)負(fù)責(zé)讀；

緩存： 使用MySQL的緩存，另外對(duì)重量級(jí)、更新少的數(shù)據(jù)可以考慮使用應(yīng)用級(jí)別的緩存；

垂直分區(qū)：

根據(jù)數(shù)據(jù)庫(kù)里面數(shù)據(jù)表的相關(guān)性進(jìn)行拆分。 例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個(gè)多帶帶的表，甚至放到多帶帶的庫(kù)做分庫(kù)。

簡(jiǎn)單來(lái)說(shuō)垂直拆分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。 如下圖所示，這樣來(lái)說(shuō)大家應(yīng)該就更容易理解了。

垂直拆分的優(yōu)點(diǎn)： 可以使得行數(shù)據(jù)變小，在查詢時(shí)減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡(jiǎn)化表的結(jié)構(gòu)，易于維護(hù)。

垂直拆分的缺點(diǎn)： 主鍵會(huì)出現(xiàn)冗余，需要管理冗余列，并會(huì)引起Join操作，可以通過(guò)在應(yīng)用層進(jìn)行Join來(lái)解決。此外，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜；

水平分區(qū)：

保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過(guò)某種策略存儲(chǔ)數(shù)據(jù)分片。這樣每一片數(shù)據(jù)分散到不同的表或者庫(kù)中，達(dá)到了分布式的目的。 水平拆分可以支撐非常大的數(shù)據(jù)量。

水平拆分是指數(shù)據(jù)表行的拆分，表的行數(shù)超過(guò)200萬(wàn)行時(shí)，就會(huì)變慢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來(lái)存放。舉個(gè)例子：我們可以將用戶信息表拆分成多個(gè)用戶信息表，這樣就可以避免單一表數(shù)據(jù)量過(guò)大對(duì)性能造成影響。

水品拆分可以支持非常大的數(shù)據(jù)量。需要注意的一點(diǎn)是:分表僅僅是解決了單一表數(shù)據(jù)過(guò)大的問題，但由于表的數(shù)據(jù)還是在同一臺(tái)機(jī)器上，其實(shí)對(duì)于提升MySQL并發(fā)能力沒有什么意義，所以 水品拆分最好分庫(kù) 。

水平拆分能夠 支持非常大的數(shù)據(jù)量存儲(chǔ)，應(yīng)用端改造也少，但 分片事務(wù)難以解決 ，跨界點(diǎn)Join性能較差，邏輯復(fù)雜?！禞ava工程師修煉之道》的作者推薦 盡量不要對(duì)數(shù)據(jù)進(jìn)行分片，因?yàn)椴鸱謺?huì)帶來(lái)邏輯、部署、運(yùn)維的各種復(fù)雜度 ，一般的數(shù)據(jù)表在優(yōu)化得當(dāng)?shù)那闆r下支撐千萬(wàn)以下的數(shù)據(jù)量是沒有太大問題的。如果實(shí)在要分片，盡量選擇客戶端分片架構(gòu)，這樣可以減少一次和中間件的網(wǎng)絡(luò)I/O。

下面補(bǔ)充一下數(shù)據(jù)庫(kù)分片的兩種常見方案：

客戶端代理： 分片邏輯在應(yīng)用端，封裝在jar包中，通過(guò)修改或者封裝JDBC層來(lái)實(shí)現(xiàn)。 當(dāng)當(dāng)網(wǎng)的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實(shí)現(xiàn)。

中間件代理： 在應(yīng)用和數(shù)據(jù)中間加了一個(gè)代理層。分片邏輯統(tǒng)一維護(hù)在中間件服務(wù)中。 我們現(xiàn)在談的 Mycat 、360的Atlas、網(wǎng)易的DDB等等都是這種架構(gòu)的實(shí)現(xiàn)。

詳細(xì)內(nèi)容可以參考：
MySQL大表優(yōu)化方案

歡迎關(guān)注我的微信公眾號(hào):"Java面試通關(guān)手冊(cè)"（一個(gè)有溫度的微信公眾號(hào)，無(wú)廣告，單純技術(shù)分享，期待與你共同進(jìn)步~~~堅(jiān)持原創(chuàng)，分享美文，分享各種Java學(xué)習(xí)資源。你想關(guān)注便關(guān)注，公眾號(hào)只是我記錄文字和生活的地方，無(wú)所謂利益，請(qǐng)不要一棒子打死一群做“自媒體”的人。)