萬字總結(jié) MySQL核心知識，贈送25連環(huán)炮

關(guān)注“Java后端技術(shù)全棧”

回復(fù)“000”獲取大量電子書

前言

大家好，我是老田，之前寫過 JVM、并發(fā)編程連環(huán)炮。然后有很多小伙伴私下找我就我繼續(xù)把MySQL的連環(huán)炮整理出來，但是由于本人比較懶，又加上最近有點忙，所以拖到了今天才發(fā)出來。

有志者，事竟成；破釜沉舟，百二秦關(guān)終歸楚；苦心人，天不負；臥薪嘗膽，三千越甲可吞吳。

本文總結(jié)了MySQL的核心知識點，然后結(jié)合20個連環(huán)炮，可以幫助大家學(xué)習(xí)MySQL、快速復(fù)習(xí)MySQL有所幫助。

MySQL的20個連環(huán)炮，如下：

1、數(shù)據(jù)庫的三范式是什么?

2、DDL、DML、DCL、TCL分表代碼什么含義？

3、熟悉MySQL的整體架構(gòu)嗎？

4、說說存儲引擎 Inno DB和MyISAM的區(qū)別

5、熟悉哪些日志文件？

6、熟悉慢查詢嗎？

7、MySQL優(yōu)化手段有哪些？

8、什么是事務(wù)？

9、事務(wù)的四大特性是什么？

10、說說Mysql的四種隔離級別

11、MySQL默認隔離級別是哪個？

12、知道MySQL中哪些鎖？

13、并發(fā)讀寫容易帶來什么問題？

14、說說你對MySQL中MVCC的認識

15、是如何解決幻讀的？

15、索引是什么？

17、MySQL索引數(shù)據(jù)結(jié)構(gòu)有哪幾種？

18、有哪些類型的索引呢？

19、Hash和BTree作為MySQL索引，說說你對此有什么想法？

20、索引優(yōu)化有哪些方式？

21、哪些場景建議創(chuàng)建索引？

22、哪些場景不建議使用索引？

23、用過explain嗎？怎么用的？

24、熟悉MySQL鎖優(yōu)化嗎？

25、熟悉哪些MySQL調(diào)優(yōu)策略？

MySQL知識點

需要思維導(dǎo)圖的，加微信tj20120622，免費獲取

基礎(chǔ)知識

范式

第一范式

數(shù)據(jù)庫中的所有字段（列）都是單一屬性，不可再分的。 這個單一屬性由基本的數(shù)據(jù)類型所構(gòu)成，如整型、浮點型、字符串等。第一范式是為了保證列的原子性。

第二范式

數(shù)據(jù)庫中的表不存在非關(guān)鍵字段對任一關(guān)鍵字字段的部分函數(shù)依賴  部分函數(shù)依賴是指存在著組合關(guān)鍵字中的某一關(guān)鍵字決定非關(guān)鍵字的情況。第二范式在滿足了第一范式的基礎(chǔ)上，消除非主鍵列對聯(lián)合主鍵的部分依賴

第三范式

所有非主鍵屬性都只和候選鍵有相關(guān)性，也就是說非主鍵屬性之間應(yīng)該是獨立無關(guān)的。第三范式是在滿足了第二范式的基礎(chǔ)上，消除列與列之間的傳遞依賴。

SQL語句匯總

DDL

DDL是數(shù)據(jù)定義語言（Data Definition Language）的簡稱，  它處理數(shù)據(jù)庫schemas和描述數(shù)據(jù)應(yīng)如何駐留在數(shù)據(jù)庫中。

CREATE：創(chuàng)建數(shù)據(jù)庫及其對象（如表，索引，視圖，存儲過程，函數(shù)和觸發(fā)器）  ALTER：改變現(xiàn)有數(shù)據(jù)庫的結(jié)構(gòu)  DROP：從數(shù)據(jù)庫中刪除對象  TRUNCATE：從表中刪除所有記錄，包括為記錄分配的所有空間都將被刪除  COMMENT：添加注釋  RENAME：重命名對象

DML

DML是數(shù)據(jù)操縱語言（Data Manipulation Language）的簡稱，  包括最常見的SQL語句

SELECT、INSERT、UPDATE、DELETE、GROUP BY、HAVING

DCL

DCL是數(shù)據(jù)控制語言（Data Control Language）的簡稱，  它包含諸如GRANT之類的命令，并且主要涉及數(shù)據(jù)庫系統(tǒng)的權(quán)限，權(quán)限和其他控件。

GRANT ：允許用戶訪問數(shù)據(jù)庫的權(quán)限  REVOKE：撤消用戶使用GRANT命令賦予的訪問權(quán)限

TCL

TCL是事務(wù)控制語言（Transaction Control Language）的簡稱，  用于處理數(shù)據(jù)庫中的事務(wù)

COMMIT：提交事務(wù)  ROLLBACK：在發(fā)生任何錯誤的情況下回滾事務(wù)

架構(gòu)介紹

連接層：負責(zé)處理客戶端的連接以及權(quán)限的認證。

服務(wù)層：定義有許多不同的模塊，包括權(quán)限判斷，SQL接口，SQL解析，SQL分析優(yōu)化， 緩存查詢的處理以及部分內(nèi)置函數(shù)執(zhí)行等。MySQL的查詢語句在服務(wù)層內(nèi)進行解析、優(yōu)化、緩存以及內(nèi)置函數(shù)的實現(xiàn)和存儲。

引擎層：負責(zé)MySQL中數(shù)據(jù)的存儲和提取。MySQL中的服務(wù)器層不管理事務(wù)，事務(wù)是由存儲引擎實現(xiàn)的。其中使用最為廣泛的存儲引擎為InnoDB，其它的引擎都不支持事務(wù)。

存儲層：負責(zé)將數(shù)據(jù)存儲于設(shè)備的文件系統(tǒng)中。

存儲引擎

查看命令

show engines; 
show variables like '%storage_engine%'

Inno DB和MyISAM對比

Mysql5.5 版本之前默認的存儲引擎就是 MyISAM 存儲引擎，MySQL 中比較多的系統(tǒng)表使用 MyISAM 存儲引擎，系統(tǒng)臨時表也會用到 MyISAM 存儲引擎，但是在 Mysql5.5 之后默認的存儲引擎就是 InnoDB 存儲引擎了。原因主要是 MyISAM 是表級鎖定，限制了數(shù)據(jù)庫讀/寫的性能；另外一個原因 MyISAM 不支持事務(wù)，基于以上兩點，InnoDB 引擎使用的非常廣泛。

常見存儲引擎介紹

Inno DB

MySQL的默認事務(wù)型引擎、它被用來處理大量短期事務(wù)。 除非有非常特別的原因需要使用其他的存儲引擎，否則建議優(yōu)先考慮Inno DB引擎。

MyISAM

提供了大量的特性，包含全文索引，壓縮，空間行數(shù)等，  但不支持事務(wù)和行級鎖，有個缺陷就是崩潰后無法恢復(fù)

Memory

Memory存儲引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。每個 Memory表實際只對應(yīng)一個磁盤文件，格式是 .frm。Memory類型的表訪問速度很快，因為其數(shù)據(jù)是存放在內(nèi)存中。默認使用 HASH 索引。

Merge

Merge存儲引擎是一組 MyISAM 表的組合，Merge表本身沒有數(shù)據(jù)，對 Merge類型的表進行查詢、更新、刪除的操作，  實際上是對內(nèi)部的 MyISAM 表進行的。Merge表在磁盤上保留兩個文件，一個是.frm文件存儲表定義、  一個是 .MRG文件存儲 Merge表的組成等。

如何選擇存儲引擎

MyISAM：如果應(yīng)用程序通常以檢索為主，只有少量的插入、更新和刪除操作，并且對事物的完整性、  并發(fā)程度不是很高的話，通常建議選擇 MyISAM 存儲引擎。

InnoDB：如果使用到外鍵、需要并發(fā)程度較高，數(shù)據(jù)一致性要求較高，那么通常選擇 InnoDB 引擎，  一般互聯(lián)網(wǎng)大廠對并發(fā)和數(shù)據(jù)完整性要求較高，所以一般都使用 InnoDB 存儲引擎。

MEMORY：MEMORY 存儲引擎將所有數(shù)據(jù)保存在內(nèi)存中，在需要快速定位下能夠提供及其迅速的訪問。MEMORY 通常用于更新不太頻繁的小表，用于快速訪問取得結(jié)果。

MERGE：MERGE 的內(nèi)部是使用 MyISAM 表，MERGE 表的優(yōu)點在于可以突破對單個 MyISAM 表大小的限制，  并且通過將不同的表分布在多個磁盤上， 可以有效地改善 MERGE 表的訪問效率。

日志文件

MySQL中有八種日志文件，分別是：

• 重做日志（redo log）
• 回滾日志（undo log）
• 二進制日志（binlog）
• 錯誤日志（errorlog）
• 慢查詢?nèi)罩荆╯low query log）
• 一般查詢?nèi)罩荆╣eneral log）
• 中繼日志（relay log）
• DDL日志 （metadata log）

他們分別都有各自的作用，而且默認情況下，服務(wù)器的日志文件都位于數(shù)據(jù)目錄（datadir）中。

常見日志介紹

重做日志（redo log）

確保事務(wù)的持久性。防止在發(fā)生故障的時間點，尚有臟頁未寫入磁盤，在重啟mysql服務(wù)的時候，根據(jù)redo log進行重做，從而達到事務(wù)的持久性這一特性。

事務(wù)開始之后就產(chǎn)生redo log，redo log的落盤并不是隨著事務(wù)的提交才寫入的，而是在事務(wù)的執(zhí)行過程中，便開始寫入redo log文件中。

對應(yīng)的物理文件：

默認情況下，對應(yīng)的物理文件位于數(shù)據(jù)庫的data目錄下的ib_logfile1&ib_logfile2

• innodb_log_group_home_dir 指定日志文件組所在的路徑，默認./ ，表示在數(shù)據(jù)庫的數(shù)據(jù)目錄下。
• innodb_log_files_in_group 指定重做日志文件組中文件的數(shù)量，默認2

關(guān)于文件的大小和數(shù)量，由以下兩個參數(shù)配置：

• innodb_log_file_size 重做日志文件的大小。
• innodb_mirrored_log_groups 指定了日志鏡像文件組的數(shù)量，默認1

回滾日志（undo log）

保存了事務(wù)發(fā)生之前的數(shù)據(jù)的一個版本，可以用于回滾，同時可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀 。undo日志是邏輯格式的日志，在執(zhí)行undo的時候，僅僅是將數(shù)據(jù)從邏輯上恢復(fù)至事務(wù)之前的狀態(tài)，而不是從物理頁面上操作實現(xiàn)的，這一點是不同于redo log的。

關(guān)于MySQL5.7之后的獨立undo 表空間配置參數(shù)如下：

• innodb_undo_directory = /data/undospace/ –undo獨立表空間的存放目錄
• innodb_undo_logs = 128 –回滾段為128KB
• innodb_undo_tablespaces = 4 –指定有4個undo log文件

二進制日志（binlog）

用于復(fù)制，在主從復(fù)制中，從庫利用主庫上的binlog進行重播，實現(xiàn)主從同步。用于數(shù)據(jù)庫的基于時間點的還原。

內(nèi)容是以邏輯格式的日志，可以簡單認為就是執(zhí)行過的事務(wù)中的sql語句。

但又不完全是sql語句這么簡單，而是包括了執(zhí)行的sql語句（增刪改）反向的信息，也就意味著delete對應(yīng)著delete本身和其反向的insert；update對應(yīng)著update執(zhí)行前后的版本的信息；insert對應(yīng)著delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都會真相大白。

因此可以基于binlog做到類似于oracle的閃回功能，其實都是依賴于binlog中的日志記錄。

對應(yīng)的物理文件：

配置文件的路徑為log_bin_basename，binlog日志文件按照指定大小，當(dāng)日志文件達到指定的最大的大小之后，進行滾動更新，生成新的日志文件。

錯誤日志（errorlog）

MySQL 錯誤日志（error log）記錄了 mysqld 啟動和停止的相關(guān)信息，同時還記錄了服務(wù)器在啟動、停止以及運行期間發(fā)生的診斷消息，例如錯誤、警告和通知等。例如，當(dāng) mysqld 發(fā)現(xiàn)某個表需要執(zhí)行自動檢查或修復(fù)時，它會向錯誤日志中寫入一條消息。

慢查詢?nèi)罩荆╯low query log）

見后面的SQL優(yōu)化部分。

性能優(yōu)化

硬件層

CPU相關(guān)

1、選擇Performance Per Watt Optimized(DAPC)模式，發(fā)揮CPU最大性能，跑DB這種通常需要高運算量的服務(wù)就不要考慮節(jié)電了；

2、關(guān)閉C1E和C States等選項，目的也是為了提升CPU效率；

3、Memory Frequency（內(nèi)存頻率）選擇Maximum Performance（最佳性能）；

4、內(nèi)存設(shè)置菜單中，啟用Node Interleaving，避免NUMA問題；

磁盤I/O相關(guān)

1、使用SSD或者PCIe SSD設(shè)備，至少獲得數(shù)百倍甚至萬倍的IOPS提升；

2、購置陣列卡同時配備CACHE及BBU模塊，可明顯提升IOPS（主要是指機械盤，SSD或PCIe SSD除外。 同時需要定期檢查CACHE及BBU模塊的健康狀況，確保意外時不至于丟失數(shù)據(jù)）；

3、有陣列卡時，設(shè)置陣列寫策略為WB，甚至FORCE WB（若有雙電保護，或?qū)?shù)據(jù)安全性要求不是特別高的話），  嚴禁使用WT策略。并且閉陣列預(yù)讀策略，基本上是雞肋，用處不大；

4、盡可能選用RAID-10，而非RAID-5；

5、使用機械盤的話，盡可能選擇高轉(zhuǎn)速的，例如選用15KRPM，而不是7.2KRPM的盤，不差幾個錢的；

系統(tǒng)層

文件系統(tǒng)層優(yōu)化

1、使用deadline/noop這兩種I/O調(diào)度器，千萬別用cfq（它不適合跑DB類服務(wù)）；

2、使用xfs文件系統(tǒng)，千萬別用ext3；ext4勉強可用，但業(yè)務(wù)量很大的話，則一定要用xfs；

3、文件系統(tǒng)mount參數(shù)中增加：noatime, nodiratime, nobarrier幾個選項（nobarrier是xfs文件系統(tǒng)特有的）；

其他內(nèi)核參數(shù)優(yōu)化

1、將vm.swappiness設(shè)置為5-10左右即可，甚至設(shè)置為0（RHEL 7以上則慎重設(shè)置為0，除非你允許OOM kill發(fā)生），以降低使用SWAP的機會；

2、將vm.dirty_background_ratio設(shè)置為5-10，將vm.dirty_ratio設(shè)置為它的兩倍左右，以確保能持續(xù)將臟數(shù)據(jù)刷新到磁盤，避免瞬間I/O寫，產(chǎn)生嚴重等待（和MySQL中的innodb_max_dirty_pages_pct類似）；

3、將net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設(shè)置為1，減少TIME_WAIT，提高TCP效率；

4、至于網(wǎng)傳的read_ahead_kb、nr_requests這兩個參數(shù)，我經(jīng)過測試后，發(fā)現(xiàn)對讀寫混合為主的OLTP環(huán)境影響并不大（應(yīng)該是對讀敏感的場景更有效果），不過沒準(zhǔn)是我測試方法有問題，可自行斟酌是否調(diào)整；

MySQL層

參數(shù)調(diào)整

1、選擇Percona或MariaDB版本的話，強烈建議啟用thread pool特性，可使得在高并發(fā)的情況下，性能不會發(fā)生大幅下降。此外，還有extra_port功能，非常實用， 關(guān)鍵時刻能救命的。還有另外一個重要特色是 QUERY_RESPONSE_TIME 功能，也能使我們對整體的SQL響應(yīng)時間分布有直觀感受；

2、設(shè)置default-storage-engine=InnoDB，也就是默認采用InnoDB引擎，強烈建議不要再使用MyISAM引擎了，InnoDB引擎絕對可以滿足99%以上的業(yè)務(wù)場景；

3、調(diào)整innodb_buffer_pool_size大小，如果是單實例且絕大多數(shù)是InnoDB引擎表的話，可考慮設(shè)置為物理內(nèi)存的50% ~ 70%左右；

4、根據(jù)實際需要設(shè)置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求數(shù)據(jù)不能丟失，那么兩個都設(shè)為1。如果允許丟失一點數(shù)據(jù)，則可分別設(shè)為2和10。而如果完全不用care數(shù)據(jù)是否丟失的話（例如在slave上，反正大不了重做一次），則可都設(shè)為0。這三種設(shè)置值導(dǎo)致數(shù)據(jù)庫的性能受到影響程度分別是：高、中、低；

5、設(shè)置innodb_file_per_table = 1，使用獨立表空間，我實在是想不出來用共享表空間有什么好處了；

6、設(shè)置innodb_data_file_path = ibdata1:1G:autoextend，千萬不要用默認的10M，否則在有高并發(fā)事務(wù)時，會受到不小的影響；

7、設(shè)置innodb_log_file_size=256M，設(shè)置innodb_log_files_in_group=2，基本可滿足90%以上的場景；

8、設(shè)置long_query_time = 1，而在5.5版本以上，已經(jīng)可以設(shè)置為小于1了，建議設(shè)置為0.05（50毫秒），記錄那些執(zhí)行較慢的SQL，用于后續(xù)的分析排查；

9、根據(jù)業(yè)務(wù)實際需要，適當(dāng)調(diào)整max_connection（最大連接數(shù)）、max_connection_error（最大錯誤數(shù)，建議設(shè)置為10萬以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache這幾個參數(shù)則可設(shè)為約10倍于max_connection的大??；

10、常見的誤區(qū)是把tmp_table_size和max_heap_table_size設(shè)置的比較大，曾經(jīng)見過設(shè)置為1G的，這2個選項是每個連接會話都會分配的，因此不要設(shè)置過大，否則容易導(dǎo)致OOM發(fā)生；其他的一些連接會話級選項例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等，也需要注意不能設(shè)置過大；

11、由于已經(jīng)建議不再使用MyISAM引擎了，因此可以把key_buffer_size設(shè)置為32M左右，并且強烈建議關(guān)閉query cache功能；

Schema設(shè)計規(guī)范及SQL使用建議

1、所有的InnoDB表都設(shè)計一個無業(yè)務(wù)用途的自增列做主鍵，對于絕大多數(shù)場景都是如此，真正純只讀用InnoDB表的并不多，真如此的話還不如用TokuDB來得劃算；

2、字段長度滿足需求前提下，盡可能選擇長度小的。此外，字段屬性盡量都加上NOT NULL約束，可一定程度提高性能；

3、盡可能不使用TEXT/BLOB類型，確實需要的話，建議拆分到子表中，不要和主表放在一起，避免SELECT * 的時候讀性能太差。

4、讀取數(shù)據(jù)時，只選取所需要的列，不要每次都SELECT *，避免產(chǎn)生嚴重的隨機讀問題，尤其是讀到一些TEXT/BLOB列；

5、對一個VARCHAR(N)列創(chuàng)建索引時，通常取其50%（甚至更?。┳笥议L度創(chuàng)建前綴索引就足以滿足80%以上的查詢需求了，沒必要創(chuàng)建整列的全長度索引；

6、通常情況下，子查詢的性能比較差，建議改造成JOIN寫法；

7、多表聯(lián)接查詢時，關(guān)聯(lián)字段類型盡量一致，并且都要有索引；

8、多表連接查詢時，把結(jié)果集小的表（注意，這里是指過濾后的結(jié)果集，不一定是全表數(shù)據(jù)量小的）作為驅(qū)動表；

9、多表聯(lián)接并且有排序時，排序字段必須是驅(qū)動表里的，否則排序列無法用到索引；

10、多用復(fù)合索引，少用多個獨立索引，尤其是一些基數(shù)（Cardinality）太?。ū热缯f，該列的唯一值總數(shù)少于255）的列就不要創(chuàng)建獨立索引了；

11、類似分頁功能的SQL，建議先用主鍵關(guān)聯(lián)，然后返回結(jié)果集，效率會高很多；

其他建議

1、通常地，單表物理大小不超過10GB，單表行數(shù)不超過1億條，行平均長度不超過8KB，如果機器性能足夠，這些數(shù)據(jù)量MySQL是完全能處理的過來的，不用擔(dān)心性能問題，這么建議主要是考慮ONLINE DDL的代價較高；

2、不用太擔(dān)心mysqld進程占用太多內(nèi)存，只要不發(fā)生OOM kill和用到大量的SWAP都還好；

3、在以往，單機上跑多實例的目的是能最大化利用計算資源，如果單實例已經(jīng)能耗盡大部分計算資源的話，就沒必要再跑多實例了；

4、定期使用pt-duplicate-key-checker檢查并刪除重復(fù)的索引。定期使用pt-index-usage工具檢查并刪除使用頻率很低的索引；

5、定期采集slow query log，用pt-query-digest工具進行分析，可結(jié)合Anemometer系統(tǒng)進行slow query管理以便分析slow query并進行后續(xù)優(yōu)化工作；

6、可使用pt-kill殺掉超長時間的SQL請求，Percona版本中有個選項 innodb_kill_idle_transaction 也可實現(xiàn)該功能；

7、使用pt-online-schema-change來完成大表的ONLINE DDL需求；

8、定期使用pt-table-checksum、pt-table-sync來檢查并修復(fù)mysql主從復(fù)制的數(shù)據(jù)差異；

事務(wù)

事務(wù)

數(shù)據(jù)庫事務(wù)(Database Transaction) ，是指作為單個邏輯工作單元執(zhí)行的一系列操作，要么完整地執(zhí)行，要么完全地不執(zhí)行。

經(jīng)典的銀行轉(zhuǎn)賬例子: A從銀行轉(zhuǎn)賬1w塊錢給B，此時數(shù)據(jù)庫會涉及2個最基本的操作:

• A銀行卡余額減少1w
• B銀行卡余額增加1w

那么這2個操作是一個整體，要么一起成功，要么一起失敗，不會存在只有部分成功。例如A銀行卡余額減少1w，但是B銀行卡余額沒有增加1w，這就會出大問題了。

四大特性

原子性（Atomicity）

原子性是指事務(wù)是一個不可分割的工作單位，事務(wù)中的操作要么全部成功，要么全部失敗

一致性（Consistency）

事務(wù)按照預(yù)期生效，數(shù)據(jù)的狀態(tài)是預(yù)期的狀態(tài)。

隔離性（Isolation）

事務(wù)的隔離性是多個用戶并發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務(wù)，  不能被其他事務(wù)的操作數(shù)據(jù)所干擾，多個并發(fā)事務(wù)之間要相互隔離。

持久性（Durability）

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

兩個操作

提交（commit）

將事務(wù)執(zhí)行結(jié)果寫入數(shù)據(jù)庫

回滾（rollback）

回滾所有已執(zhí)行的語句，返回修改之前的數(shù)據(jù)

鎖機制

按照粒度劃分

• 行鎖
• 頁鎖
• 表鎖

按照使用方式劃分

• 共享鎖
• 排它鎖

按照思想劃分

• 悲觀鎖
• 樂觀鎖

行鎖

• record lock ：單個行記錄刪搞的鎖。
• Grap lock ：間隙鎖鎖定一個范圍，但不包括記錄本身。
• Next-key lock：鎖定一個范圍，包括鎖記錄本身。

讀寫鎖

• 共享鎖Share lock、又叫讀鎖。
• 排它鎖（exclusive lock） 又叫寫鎖。

通過寫鎖，可以做到讀讀并行，但不能做到讀寫、寫寫并行。

減少死鎖的方式

• 自動死鎖檢測，優(yōu)先回滾小事務(wù)
• 超時參數(shù)設(shè)置（innodblockwait_timeout）
• 盡量提交事務(wù)，小事務(wù)不容易產(chǎn)生死鎖
• 加 for update、lock in share mode 讀鎖時，最好降低事務(wù)隔離級別，比如 rc 級別，降低死鎖發(fā)生概率
• 事務(wù)中涉及多個表，或者涉及多行記錄時，每個事務(wù)的操作順序都要保持一致，降低死鎖概率，最好用存儲過程
• 通過索引等方式優(yōu)化 sql 效率，降低死鎖概率（目的是減少掃描 / 鎖范圍，降低概率）

并發(fā)讀寫帶問題

• 臟讀
• 不可重復(fù)度
• 幻讀

臟讀(Drity Read)

某個事務(wù)已更新一份數(shù)據(jù)，另一個事務(wù)在此時讀取了同一份數(shù)據(jù)，由于某些原因，前一個RollBack了操作，則后一個事務(wù)所讀取的數(shù)據(jù)就會是不正確的。

不可重復(fù)讀(Non-repeatable read)

在一個事務(wù)的兩次查詢之中數(shù)據(jù)不一致，這可能是兩次查詢過程中間插入了一個事務(wù)更新的原有的數(shù)據(jù)。

幻讀(Phantom Read)

在一個事務(wù)的兩次查詢中數(shù)據(jù)筆數(shù)不一致，例如有一個事務(wù)查詢了幾列(Row)數(shù)據(jù)，而另一個事務(wù)卻在此時插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來的查詢中，就有幾列數(shù)據(jù)是未查詢出來的，如果此時插入和另外一個事務(wù)插入的數(shù)據(jù)，就會報錯。

Mysql的四種隔離級別

SQL標(biāo)準(zhǔn)定義了4類隔離級別，包括了一些具體規(guī)則，用來限定事務(wù)內(nèi)外的哪些改變是可見的，哪些是不可見的。低級別的隔離級一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開銷。

• 讀未提交
• 讀已提交
• 可重復(fù)度
• 可串行化

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

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

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

這是大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別（但不是MySQL默認的）。它滿足了隔離的簡單定義：一個事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變。這種隔離級別 也支持所謂的不可重復(fù)讀（Nonrepeatable Read），因為同一事務(wù)的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結(jié)果。

Repeatable Read（可重讀）

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

Serializable（可串行化）

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

在MySQL中，實現(xiàn)了這四種隔離級別，分別有可能產(chǎn)生問題如下所示：

MVCC

定義

MVCC (Multi- Version Concurrency Control) 是一種基于多版本的并發(fā)控制協(xié)議，只有在InnoDB引 擎下存在。另外，這里千萬別和Spring MVC聯(lián)想到一起了。

MVCC是為了實現(xiàn)事務(wù)的隔離性，通過版本號，避免同一數(shù)據(jù)在不同事務(wù)間的競爭，你 可以把它當(dāng)成基于多版本號的一種樂觀鎖。當(dāng)然，這種樂觀鎖只在事務(wù)級別未提交鎖和已提交鎖時 才會生效。MVCC最大的好處，相信也是耳熟能詳：讀不加鎖，讀寫不沖突。在讀多寫少的OLTP 應(yīng)用中，讀寫不沖突是非常重要的，極大的增加了系統(tǒng)的并發(fā)性能。

注意：MVCC只在 READ COMMITTED 和 REPEATABLE READ```兩個隔離級別下工作。其他兩個隔離級別不和MVCC不兼容, 因為READ UNCOMMITTED總是讀取最新的數(shù)據(jù)行, 而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行。而SERIALIZABLE` 則會對所有讀取的行都加鎖。

Mysql默認隔離級別是RR（可重復(fù)讀），是通過“行鎖+MVCC”來實現(xiàn)的，正常讀時不加鎖，寫時加鎖，MVCC的實現(xiàn)依賴于：三個隱藏字段，Read View、Undo log 來實現(xiàn)。

InnoDB MVCC 實現(xiàn)原理

InnoDB 中 MVCC 的實現(xiàn)方式為：每一行記錄都有兩個隱藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果沒有主鍵，則還會多一個隱藏的主鍵列）。

1、DB_TRX_ID：6個字節(jié)，記錄每一行最近修改他的事務(wù)ID

2、DB_ROLL_PTR：表示指向該行回滾段（rollback segment）的指針，大小為 7 個字節(jié)，InnoDB 便是通過這個指針找到之前版本的數(shù)據(jù)。該行記錄上所有舊版本，在 undo 中都通過鏈表的形式組織。

3、DB_ROW_ID：行標(biāo)識（隱藏單調(diào)自增 ID），大小為 6 字節(jié)，如果表沒有主鍵，InnoDB 會自動生成一個隱藏主鍵，因此會出現(xiàn)這個列。

快照讀和當(dāng)前讀

讀快照，可以讀取數(shù)據(jù)的所有版本信息，包括舊版本的信息。其實就是讀取MVCC中的read_view，同時結(jié)合MVCC進行相對應(yīng)的控制；

select * from table where ?;

讀當(dāng)前，讀取當(dāng)前數(shù)據(jù)的最新版本。而且讀取到這個數(shù)據(jù)之后會對這個數(shù)據(jù)加鎖，防止別的事務(wù)更改。（分析：在進行寫操作的時候就需要進行“當(dāng)前讀”，讀取數(shù)據(jù)記錄的最新版本）

select * from table where ? lock in share mode;  # 讀鎖
select * from table where ? for update;          # 寫鎖
insert into table values (…); 
update table set ? where ?; 
delete from table where ?;

RC和RR隔離級別下的快照讀和當(dāng)前讀：

• RC隔離級別下，快照讀和當(dāng)前讀結(jié)果一樣，都是讀取已提交的最新；

• RR隔離級別下，當(dāng)前讀結(jié)果是其他事務(wù)已經(jīng)提交的最新結(jié)果，快照讀是讀當(dāng)前事務(wù)之前讀到的結(jié)果。RR下創(chuàng)建快照讀的時機決定了讀到的版本。

解決幻讀

對于快照讀：通過MVCC來進行控制的，不用加鎖。按照MVCC中規(guī)定的“語法”進行增刪改查等操作，以避免幻讀。

對于當(dāng)前讀：通過next-key鎖（行鎖+gap鎖）來解決問題的。

索引詳情

索引概念

索引（在MySQL中也叫“鍵key”）是存儲引擎快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。

索引一般以文件形式存儲在磁盤上，索引檢索需要磁盤I/O操作

索引是數(shù)據(jù)結(jié)構(gòu)，而且是實現(xiàn)了高級查找算法的數(shù)據(jù)結(jié)構(gòu)

幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)

索引分類

• 普通索引
• 唯一所有
• 主鍵索引
• 復(fù)合索引
• 全文索引

MySQL索引結(jié)構(gòu)

• BTree索引
• Hash索引
• full-text全部問索引
• r-tree索引

在計算機數(shù)據(jù)結(jié)構(gòu)體系中，為了加速查找的速度，常見的數(shù)據(jù)結(jié)構(gòu)有兩種：

• Hash哈希結(jié)構(gòu)，例如Java中的HashMap，這種數(shù)據(jù)組織結(jié)構(gòu)可以讓查詢/插入/修改/刪除的平均時間復(fù)雜度都為O(1);
• Tree 樹 結(jié)構(gòu) ， 這種數(shù)據(jù)組織結(jié)構(gòu)可以讓查詢/插入/修改/刪除的平均時間復(fù)雜度都為O(log(n));

Hash這種類型比Tree樹這種類型都要更快一些，那為什么MySQL的開發(fā)者既使用Hash類型作為索引，又使用了BTREE呢？

確實用Hash索引更快，因為每次都只查詢一條信息（重名的雇員姓名也才幾條而已），但實際上業(yè)務(wù)對于SQL的應(yīng)用場景是：

order by 需要排個序、group by 還要分個組還要比較大小 大于或小于等等

這種情況下如果繼續(xù)用HASH類型做索引結(jié)構(gòu)，其時間復(fù)雜度會從O(1)直接退化為O(n)，相當(dāng)于全表掃描了，而Tree的特性保證了不管是哪種操作，依然能夠保持O(log(n))的高效率。

索引實操

創(chuàng)建索引

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));  
ALTER mytable ADD [UNIQUE] INDEX indexName

刪除索引

drop index 索引名 on 表名

查看索引

show index from 表名

索引維護

• 慢查詢
• 刪除不用的索引
• 查詢重復(fù)一句冗余索引

索引優(yōu)化

• 使用索引注意點
• 最左前綴法則
• 不要在索引列上做任何操作（計算、函數(shù)、（自動或手動）類型轉(zhuǎn)  換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
• 存儲引擎不能使用索引中范圍條件右邊的列
• 盡量使用覆蓋索引（只訪問索引的查詢（索引列包含查詢列）），  減少select *語句
• mysql在使用不等于（！=或者<>）的時候無法使用索引會導(dǎo)致全  表掃描
• 字符串不加單引號索引失效

哪些場景不要建索引

• 表記錄太少
• 經(jīng)常增刪改的字段
• where條件中用不到的字段
• 區(qū)分度不夠的（性別）

哪些場景下可建索引

• 主鍵自動建議唯一索引
• 頻繁作為查詢字段可以考慮建索引
• 查詢中與其他表關(guān)聯(lián)的字段、外鍵建立索引
• 查詢中統(tǒng)計或分組字段
• 查詢中排序的字段，排序后通過索引去訪問將大大提升排序性能

SQL優(yōu)化

explain查詢優(yōu)化器詳解

模擬優(yōu)化器執(zhí)行SQL語句，從而知道MySQL是 如何處理你的SQL語句的，分析  你的查詢語句或者表結(jié)構(gòu)的性能瓶頸。

使用方式

在 select 語句之前增加 explain 關(guān)鍵字，MySQL 會在查詢上設(shè)置一個標(biāo)記，  執(zhí)行查詢時，會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL。

explain select * from user where id = 1001;
--為了方便查看，可以在語句后面加一個\G，表示按字段順序展開
explain select * from user where id = 1001 \G

作用

• 表的讀取順序
• 數(shù)據(jù)讀取操作的操作類型
• 哪些索引可以使用
• 哪些索引被實際使用
• 表之間的引用
• 每張表有多少行被優(yōu)化器查詢

explain變種

explain extended

會在 explain 的基礎(chǔ)上額外提供一些查詢優(yōu)化的信息。 緊隨其后通過 show warnings 命令可以 得到優(yōu)  化后的查詢語句，從而看出優(yōu)化器優(yōu)化了什么

explain partitions

相比 explain 多了個 partitions 字段，  如果查詢是基于分區(qū)表的話，會顯示查詢將訪問的分區(qū)。

explain 結(jié)果列詳情

id

id列的編號是 select 的序列號，有幾個 select 就有幾個id，并且id的順序是按 select 出現(xiàn)的順序增長  的。

id列越大執(zhí)行優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行。

select_type

SIMPLE：簡單查詢。查詢不包含子查詢和union

PRIMARY：最外層SELECT

UNION：第二層，在SELECT之后使用了UNION。

DEPENDENT UNION：UNION語句中的第二個SELECT，依賴于外部子查詢。

UNION RESULT：UNION的結(jié)果

SUBQUERY：子查詢中的第一個SELECT。

DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢。

DERIVED ：導(dǎo)出表的SELECT（FROM子句的子查詢）

MATERIALIZED：物化子查詢

UNCACHEABLE SUBQUERY：無法緩存結(jié)果的子查詢，必須為外部查詢的每一行重新計算

UNCACHEABLE UNION  ：UNION 屬于不可緩存的子查詢的第二個或后一個選擇

table

union

<unionM,N,...>：該行指的是id值為M和id值為N的并集。

derived

<derivedN>：該行是指用于與該行的派生表結(jié)果id的值 N。 例如，派生表可以來自FROM子句

subquery

<subqueryN>：該行指的是id 值為的行的具體化子查詢的結(jié)果N

type

NULL

MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，  例如從一個索引列里選取最小值可以通過單獨索引查找完成。

system

該表只有一行（如：系統(tǒng)表）。這是const連接類型的特例

const

該表最多只有一個匹配行，在查詢開頭讀取。因為只有一行，  所以優(yōu)化器的其余部分可以將此行中列的值視為常量。  const表非?？欤驗樗鼈冎蛔x一次。SELECT * FROM tbl_name WHERE primary_key=1;

eq_ref

除了 system和 const類型之外，這是最好的連接類型。 當(dāng)連接使用索引的所有部分且索引是 索引PRIMARY KEY或UNIQUE NOT NULL索引時使用它。

ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

fulltext

使用FULLTEXT 索引執(zhí)行連接。

ref_or_null

該聯(lián)接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。SELECT * FROM ref_table WHERE key_column IS NULL;

index_merge

該指數(shù)合并訪問方法檢索與多行 range掃描和他們的結(jié)果合并到一個。 此訪問方法僅合并來自單個表的索引掃描，而不掃描多個表。

unique_subquery

該類型替換了下面形式的IN子查詢的 ref：value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數(shù)，  可以完全替換子查詢，效率更高?！静怀Ｓ谩?/p>

index_subquery

該聯(lián)接類型類似于unique_subquery?？梢蕴鎿QIN子查詢，但只適合下列形式的子查詢中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)?！静怀Ｓ谩?/p>

range

只檢索給定范圍的行，使用一個索引來選擇行。

index

該聯(lián)接類型與ALL相同，F(xiàn)ull Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。 這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。

ALL

Full Table Scan， MySQL將遍歷全表以找到匹配的行。

各個類型對比

結(jié)果值從好到壞依次是：Null > system > const > eq_ref > ref >   ref_or_null> index_merge > unique_subquery > index_subquery   > range > index > ALL

建議類型

一般來說，得保證查詢至少達到range級別，最好能達到ref。

possible_keys

表示查詢時，可能使用的索引。( MySQL能使用哪個索引在該表中找到行)

key

實際使用的索引(鍵)，必然包含在possible_keys中。如果沒有選擇索引，索引是NULL。 要想強制MySQL使用或忽視possible_keys列中的索引，  在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

索引的長度 ( 使用的字節(jié)數(shù) )。如果索引是NULL，則長度為NULL。不損失精確性的情況下，長度越短越好 。key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的。

ref

使用哪個列或常數(shù)，與索引一起被用于從表中查找索引列上的值。 ( 列與索引的比較，表示上述表的連接匹配條件。)  顯示索引的哪一列被使用了，如果可能得話，是一個常數(shù)。

rows

MySQL認為它執(zhí)行查詢時必須檢查的行數(shù)。( 掃描出的行數(shù) [估算的行數(shù) ]。)

filtered

通過表條件過濾出的行數(shù)的百分比估計值。

Extra

Mysql執(zhí)行情況的描述和詳細說明。信息 Extra 的常用值說明：

Using filesort：使用了文件進行排序，效率較低

Using index：使用了覆蓋索引，效率較高

Using temporary：使用了臨時表

Using where：使用了 where 條件

慢查詢

獲取慢查詢的方式

在工作中，我們發(fā)現(xiàn)慢查詢一般有 2 個途徑，一個是被動的，一個是主動的。估計大部分人都是被動的在處理慢查詢，被動的就是當(dāng)業(yè)務(wù)人員反饋某個查詢界面響應(yīng)的時間特別長，你才去處理。主動的就是通過通過分析慢查詢?nèi)罩緛碇鲃影l(fā)現(xiàn)執(zhí)行效率緩慢的 sql 語句，或者通過 information_schema.processlist 實時查詢執(zhí)行緩慢的 sql。

分析慢查詢?nèi)罩?span style="display: none;">

分析慢查詢?nèi)罩镜牟襟E：

• 通過命令查看數(shù)據(jù)庫是否開啟慢查詢?nèi)罩荆?code style="font-size: 14px;overflow-wrap: break-word;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">show variables like 'slow_query_log';
• 設(shè)置開啟慢查詢?nèi)罩荆?code style="font-size: 14px;overflow-wrap: break-word;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">set global slow_query_log=on;
• 沒有命中索引的查詢記入慢查詢?nèi)罩荆?code style="font-size: 14px;overflow-wrap: break-word;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">set global log_queries_not_using_indexes = on;
• sql 語句超過多少秒記入慢查詢?nèi)罩荆?code style="font-size: 14px;overflow-wrap: break-word;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">set global long_query_time=1;
• 查看慢查詢?nèi)罩颈４鏋闇?zhǔn)：show variables like 'slow_query_log_file';
• 直接打開日志進行查看：vi + file 路徑
• 慢查詢?nèi)罩竟ぞ撸?code style="font-size: 14px;overflow-wrap: break-word;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">mysqldumpslow -s at -t 15 file 路徑

慢查詢?nèi)罩緟?shù)介紹

Time：日志記錄時間

User@Host：執(zhí)行的用戶與主機

Query time：查詢耗費時間

Lock time：鎖表時間

Rows_sent：返回的結(jié)果行數(shù)

Rows_examined：掃描的記錄行數(shù)

Set timestamp：sql 語句執(zhí)行的時間

sql 語句表示執(zhí)行的具體語句。

分析 information_schema.processlist

SELECT id,user,host,DB,command,time,state,info
FROM information_schema.processlist
WHERE TIME>=30;

查詢當(dāng)前服務(wù)器執(zhí)行超過 30 秒的 sql，可以通過定時任務(wù)周期性的來執(zhí)行這個 sql，就能找到查詢緩慢的 sql 語句。

通過以上兩種方式找出查詢較慢的 sql，進行優(yōu)化即可。

MySQL鎖優(yōu)化

表級鎖

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

行級鎖

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

頁面鎖

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

MySQL 常用調(diào)優(yōu)策略

總結(jié)

這是連環(huán)炮的第四篇（JVM、并發(fā)編程基礎(chǔ)、線程池已發(fā)），計劃搞20篇連環(huán)炮。

歡迎關(guān)注微信公眾號：互聯(lián)網(wǎng)全棧架構(gòu)，收取更多有價值的信息。