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

我自己總結(jié)的Java學(xué)習(xí)的一些知識點以及面試問題，目前已經(jīng)開源，會一直完善下去，歡迎建議和指導(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é)

①存儲引擎

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

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

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

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

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

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

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

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

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

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

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

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

MySQL優(yōu)化系列（三）--索引的使用、原理和設(shè)計優(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ù)情況下，會直接在緩存中返回結(jié)果。這里的查詢條件包括查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫、客戶端協(xié)議版本號等一些可能影響結(jié)果的信息。因此任何兩個查詢在任何字符上的不同都會導(dǎo)致緩存不命中。此外，如果查詢中包含任何用戶自定義函數(shù)、存儲函數(shù)、用戶變量、臨時表、Mysql庫中的系統(tǒng)表，其查詢結(jié)果也不會被緩存。

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

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

select sql_no_cache count(*) from usr;

⑤事務(wù)機制

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

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

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

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

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

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

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

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

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

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

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

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

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

⑥鎖機制與InnoDB鎖算法

MyISAM和InnoDB存儲引擎使用的鎖：

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

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

表級鎖和行級鎖對比：

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

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

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

InnoDB存儲引擎的鎖的算法有三種：

Record lock：單個行記錄上的鎖

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

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

相關(guān)知識點：

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

⑦大表優(yōu)化

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

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

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

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

垂直分區(qū)：

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

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

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

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

水平分區(qū)：

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

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

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

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

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

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

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

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

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