MySQL自身對(duì)性能的影響

MySQL體系結(jié)構(gòu)

想要了解MySQL自身對(duì)性能的影響，就需要先熟悉MySQL的體系結(jié)構(gòu)和常用的存儲(chǔ)引擎。MySQL并不完美，卻足夠靈活，能夠適應(yīng)高要求的環(huán)境，例如Web類應(yīng)用。同時(shí)，MySQL既可以嵌入到應(yīng)用程序中，也可以支持?jǐn)?shù)據(jù)倉庫、內(nèi)容索引和部署軟件、高可用的冗余系統(tǒng)、在線事務(wù)處理系統(tǒng)（OLTP）等各種應(yīng)用類型。

MySQL最重要、最與眾不同的特性是它的存儲(chǔ)引擎架構(gòu)，這種架構(gòu)的設(shè)計(jì)將查詢處理（Query Processing）及其他系統(tǒng)任務(wù)（Server Task）和數(shù)據(jù)的存儲(chǔ)/提取相分離。這種處理和存儲(chǔ)分離的設(shè)計(jì)可以在使用時(shí)根據(jù)性能、特性，以及其他需求來選擇數(shù)據(jù)存儲(chǔ)的方式。

MySQL的體系結(jié)構(gòu)，第一層：

客戶端層，這一層代表了各種可以通過MySQL連接協(xié)議連接到MySQL服務(wù)器的客戶端，例如 PHP、JAVA、C API、ODBC、JDBC等 ?？梢钥吹贸鰜磉@些并不是MySQL所獨(dú)有的，大多數(shù)基于網(wǎng)絡(luò)的C/S (客戶端/服務(wù)器) 的工具或者服務(wù)都有類似的架構(gòu)。這一層主要是：連接處理，授權(quán)認(rèn)證和安全相關(guān)的一些功能。每個(gè)連接到MySQL客戶端都會(huì)在服務(wù)器進(jìn)程中擁有一個(gè)線程，這個(gè)連接的查詢，只會(huì)在這個(gè)單獨(dú)的線程中執(zhí)行，也就是說的每個(gè)連接的查詢只能用到一個(gè)CPU的核心

第二層：

第二層架構(gòu)是MySQL比較有意思的部分。大多數(shù)MySQL的核心服務(wù)功能都在這一層，這一層包括了：查詢緩存器、查詢解析器、查詢優(yōu)化器等。以及MySQL所有的內(nèi)置函數(shù)（例如，日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等）。總之，所有跨存儲(chǔ)引擎的功能都是在這一層來實(shí)現(xiàn)的。因?yàn)檫@一層稱之為MySQL服務(wù)層，這一層中實(shí)現(xiàn)了與存儲(chǔ)引擎無關(guān)的特性。那什么是與存儲(chǔ)引擎無關(guān)的特性呢？比如說select語句，這個(gè)語句對(duì)所有的存儲(chǔ)引擎來說，所要實(shí)現(xiàn)的功能都是一樣的。（獲取存儲(chǔ)在文件中的數(shù)據(jù)，并根據(jù)我們的過濾條件進(jìn)行過濾，然后把數(shù)據(jù)顯示出來）所以這個(gè)select語句的功能是可以在MySQL服務(wù)層實(shí)現(xiàn)的，但具體如何從文件中獲得我們所要查詢的數(shù)據(jù)，則是由下一層存儲(chǔ)引擎層來實(shí)現(xiàn)的。

第三層：

存儲(chǔ)引擎層，這是區(qū)別其他數(shù)據(jù)庫最大的地方，MySQL定義了一系列存儲(chǔ)引擎的接口，所以我們也可以根據(jù)這些接口開發(fā)、定制出符合自己需求的存儲(chǔ)引擎，例如Innodb一開始就是第三方公司所開發(fā)的存儲(chǔ)引擎。存儲(chǔ)引擎主要負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)和提取，每個(gè)存儲(chǔ)引擎都有它的優(yōu)勢(shì)和劣勢(shì)。存儲(chǔ)引擎API包含了幾十個(gè)底層函數(shù)，但存儲(chǔ)引擎不會(huì)去解析SQL（注：InnoDB是一個(gè)例外，它會(huì)解析外鍵定義，因?yàn)镸ySQL服務(wù)層本身沒有實(shí)現(xiàn)該功能），不同存儲(chǔ)引擎之間也不會(huì)相互通信，而只是簡(jiǎn)單地響應(yīng)上層服務(wù)器的請(qǐng)求。目前常用的存儲(chǔ)引擎有：innodb、myisam、XtraDB、CSV、Memory等。由于MySQL這種插件式的存儲(chǔ)引擎所帶來的靈活性，使得我們可以很方便的根據(jù)不同的特點(diǎn)選擇不同的存儲(chǔ)引擎。注意，存儲(chǔ)引擎是針對(duì)于表的而不是針對(duì)于庫的，（理論上一個(gè)庫里面不同的表可以使用不同的存儲(chǔ)引擎，但不建議這么干）

MySQL體系結(jié)構(gòu)圖如下：

MySQL常用存儲(chǔ)引擎之MyISAM

MyISAM是在mysql5.58之前的版本默認(rèn)的存儲(chǔ)引擎，MyISAM存儲(chǔ)引擎將表存儲(chǔ)在兩個(gè)系統(tǒng)文件中，一個(gè)是數(shù)據(jù)文件以.MYD結(jié)尾，另一個(gè)是索引文件以.MYI結(jié)尾的。對(duì)于mysql所有的存儲(chǔ)引擎都會(huì)有一個(gè)以.frm結(jié)尾的文件，這個(gè)文件是記錄表的結(jié)構(gòu)的文件，所以.MYD和.MYI文件是MyISAM存儲(chǔ)引擎特有的。

MyISAM的特性：

特性一：

首先是并發(fā)性及鎖級(jí)別，MyISAM使用的是表級(jí)鎖而不是行級(jí)鎖，這就意味著，對(duì)表中的數(shù)據(jù)修改時(shí)，要對(duì)整個(gè)表進(jìn)行加鎖。而對(duì)表中的數(shù)據(jù)進(jìn)行讀取的時(shí)候也需要對(duì)所有的表加共享鎖，從這里可以看出使用MyISAM引擎的表讀取和寫入這兩種操作是互斥的。MyISAM對(duì)讀寫混合的操作的并發(fā)性不是很好，如果是只讀的操作，并發(fā)性還算可以接受，因?yàn)楣蚕礞i并不會(huì)阻塞共享鎖。

特性二：

第二個(gè)特性需要從表損壞修復(fù)上來看，MyISAM支持由于意外關(guān)閉而損壞MyISAM表進(jìn)行檢查和修復(fù)操作，但這里指的并不是事務(wù)的恢復(fù)。因?yàn)閙yisam并不是支持事務(wù)的存儲(chǔ)引擎，所以它也不可能有事務(wù)恢復(fù)的相關(guān)日志。需要注意的是對(duì)MyISAM表進(jìn)行修復(fù)會(huì)有可能造成數(shù)據(jù)的丟失，使用 check table tablename 命令可以對(duì)表進(jìn)行檢查，使用 repair table tablename 命令則可以對(duì)有問題的表進(jìn)行修復(fù)。

這里使用一個(gè)簡(jiǎn)單的例子演示一下這個(gè)特性，在數(shù)據(jù)庫中新建一個(gè)myIsam表，存儲(chǔ)引擎為myIsam，建表的SQL語句如下：

create table myIsam(id int,c1 varchar(10))engine=myisam;

建表完成后，在相應(yīng)的MySQL數(shù)據(jù)存儲(chǔ)目錄中會(huì)出現(xiàn)myIsam.frm和myIsam.MYD以及myIsam.MYI這三個(gè)文件，其中myIsam.frm存放的是表的結(jié)構(gòu)信息，myIsam.MYD文件存儲(chǔ)的是myIsam表的數(shù)據(jù)信息，myIsam.MYI則存放的是索引的信息：

這時(shí)候我們使用check table myIsam;檢查這個(gè)表，然后使用repair table myIsam;修復(fù)表，Msg_text 顯示ok代表沒有問題：

如果發(fā)現(xiàn)有損壞的表，可以使用repair table myIsam;來進(jìn)行修復(fù)。除此之外，mysql還提供了一個(gè)命令行的工具：myisamchk 。這個(gè)工具也可以對(duì)myisam表進(jìn)行修復(fù)，但是需要注意的是：如果使用這個(gè)命令行工具來修復(fù)myisam表的話，要先把mysql服務(wù)先停掉。因?yàn)槿绻趍ysql運(yùn)行的時(shí)候使用這個(gè)工具對(duì)表進(jìn)行修復(fù)，有可能對(duì)表進(jìn)行一些損壞。

特性三：

MyISAM支持的索引，myisam支持全文索引，而且是在mysql5.7之前版本中，唯一原生就是支持全文索引的官方存儲(chǔ)引擎。另外myisam表還支持對(duì) test、belog等字段建立前500個(gè)字符的這種前綴索引。

特性四：

MyISAM是支持表壓縮的，如果myisam是一張很大的只讀表的時(shí)候，也就是說在表創(chuàng)建完并導(dǎo)入數(shù)據(jù)后，就不會(huì)在對(duì)表進(jìn)行任何操作了，那么我們就可以對(duì)這樣的表進(jìn)行壓縮操作，可以減少磁盤I/O。如果對(duì)一張表壓縮，可以使用myisampack命令來對(duì)表進(jìn)行壓縮表中數(shù)據(jù)。對(duì)表中的數(shù)據(jù)是獨(dú)立進(jìn)行壓縮的，在讀取單數(shù)據(jù)的時(shí)候呢，不必對(duì)整個(gè)表來進(jìn)行解壓。

使用如下命令，即可對(duì)一張表進(jìn)行壓縮：

myisampack -b myIsam.MYI

需要注意的是，對(duì)壓縮后的表只能進(jìn)行讀操作，不能進(jìn)行寫操作，如果此時(shí)向myIsam表插入數(shù)據(jù)就會(huì)報(bào)錯(cuò)：

MyISAM存儲(chǔ)引擎的限制：

在 mysql5.0 版本之前默認(rèn)單表的最大存儲(chǔ)大小是4G，如果想要存儲(chǔ)超過4G的數(shù)據(jù)的時(shí)候，需要在建表的是指定MAX_Rows 和AVG_ROW_LENGTH參數(shù)的值，這兩個(gè)參數(shù)的值相乘的大小就是表存儲(chǔ)的最大的大小。如果對(duì)已存在的大表修改這兩個(gè)參數(shù)，等于對(duì)表進(jìn)行重建，會(huì)花費(fèi)一些時(shí)間。在Mysql5.0之后單表最大支持256TB

MyISAM適用的場(chǎng)景：

• 非事務(wù)型應(yīng)用，myisam 本身是非事務(wù)存儲(chǔ)引擎，是不支持事務(wù)的
• myisam支持壓縮，所以適合只讀類的應(yīng)用
• 在mysql5.7之前myisam是唯一個(gè)支持空間函數(shù)的一個(gè)存儲(chǔ)引擎，所以也適合空間類應(yīng)用

MySQL常用存儲(chǔ)引擎之Innodb

在mysql5.58之后的版本默認(rèn)存儲(chǔ)引擎是Innodb，代替了之前的myisam。與myisam不同的是，Innodb是事務(wù)存儲(chǔ)引擎，Innodb是支持事務(wù)特性的，適合大部分的應(yīng)用場(chǎng)景，也更適合處理大量的小事務(wù)。

Innodb與myisam另一個(gè)區(qū)別是存儲(chǔ)方式的不同，Innodb有自己的表空間的概念，表空間數(shù)據(jù)是存儲(chǔ)在表空間之中的，具體存放到哪個(gè)表空間由 innodb_file_per_table 這個(gè)參數(shù)來決定。如果這個(gè)參數(shù)為ON，則會(huì)為每個(gè)Innodb表建立一個(gè)獨(dú)立的表空間，以tablename.ibd為擴(kuò)展名的文件。如果這個(gè)參數(shù)為OFF則會(huì)把數(shù)據(jù)存放到系統(tǒng)共享表空間，也就是ibdataX 這個(gè)空間里，X代表一個(gè)從 1 開始的整數(shù)。

我們可以使用如下SQL語句來查看該參數(shù)的值：

show variables like 'innodb_file_per_table';

執(zhí)行結(jié)果如下：

接下來新建一個(gè)myinnodb表，看看這個(gè)表是如何存儲(chǔ)的，建表的SQL語句如下：

create table myinnodb(id int, c1 varchar(10)) engine='innodb';

可以看到有兩個(gè)文件一個(gè)是myinnodb.frm、myinnodb.ibd，其中.frm文件是記錄表結(jié)構(gòu)的，而.idb文件就是表數(shù)據(jù)實(shí)際存儲(chǔ)的地方了：

注：MySQL8.x 版本有所改變，只會(huì)存在.ibd文件，不會(huì)有.frm文件，具體可以查閱官方文檔

我們可以使用如下命令更改表的存儲(chǔ)方式，例如改為存儲(chǔ)在系統(tǒng)表空間中：

set global innodb_file_per_table=off;

接下來新建一個(gè)myinnodb_g表，建表的SQL語句如下：

create table myinnodb_g(id int, c1 varchar(10)) engine='innodb';

此時(shí)可以看到只有一個(gè)myinnodb_g.frm文件，沒有.idb文件了：

關(guān)于系統(tǒng)表空間和獨(dú)立表空間如何選擇：

1.對(duì)表空間的管理方式比較：mysql5.6之前的Innodb的innodb_file_per_table參數(shù)的默認(rèn)值為off，所以所有的數(shù)據(jù)都會(huì)存儲(chǔ)在系統(tǒng)表空間中。在一個(gè)繁忙的系統(tǒng)中，會(huì)發(fā)現(xiàn)系統(tǒng)表空間會(huì)不斷的增長，一旦我們的磁盤空間出現(xiàn)不足，需要釋放一些磁盤空間時(shí)，就不得不刪除大量的無效數(shù)據(jù)及一些無關(guān)緊要的日志數(shù)據(jù)，但刪除完之后會(huì)發(fā)現(xiàn)系統(tǒng)表空間并不會(huì)縮小。而收縮系統(tǒng)表空間的唯一方法就是把整個(gè)數(shù)據(jù)庫中的所有Innodb表導(dǎo)出后，刪除Innodb相關(guān)的表空間文件，接著重啟MySQL進(jìn)行表空間的重建，然后再將之前導(dǎo)出的數(shù)據(jù)再次導(dǎo)入進(jìn)去。想也知道這個(gè)過程是麻煩又耗時(shí)的，我們不可能在生產(chǎn)環(huán)境中這么干。由于系統(tǒng)表空間無法簡(jiǎn)單的收縮文件大小，會(huì)造成大量空間的浪費(fèi)，并且產(chǎn)生大量的磁盤碎片，從而降低系統(tǒng)的性能。如果使用獨(dú)立表空間的話這個(gè)問題就很好解決了，我們對(duì)表數(shù)據(jù)進(jìn)行清理之后，可以直接通過optimize table命令來收縮系統(tǒng)文件，并且不需要重啟MySQL，也不會(huì)影響數(shù)據(jù)庫的訪問。從這一點(diǎn)來看，使用獨(dú)立表空間顯然要比系統(tǒng)表空間要好得多。

2.然后我們?cè)賮砜纯词褂孟到y(tǒng)表空間對(duì)I/O會(huì)有什么影響：對(duì)于系統(tǒng)表空間來說，因?yàn)橹挥幸粋€(gè)文件，所以多個(gè)表空間進(jìn)行數(shù)據(jù)刷新的時(shí)候，實(shí)際上在文件系統(tǒng)上是順序進(jìn)行的，這樣就會(huì)產(chǎn)生大量的I/O瓶頸。而獨(dú)立的表空間來說，每個(gè)表都有自己獨(dú)立的表空間文件。所以在數(shù)據(jù)寫入的時(shí)候，可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)。結(jié)論：對(duì)于頻繁寫入的操作，不適合使用系統(tǒng)表空間存儲(chǔ)數(shù)據(jù)，最好用獨(dú)立表空間進(jìn)行存儲(chǔ)。建議，在Innodb下使用獨(dú)立表空間進(jìn)行數(shù)據(jù)存儲(chǔ)。

由于版本原因，可能有些數(shù)據(jù)存儲(chǔ)在系統(tǒng)表空間中，這里簡(jiǎn)單說明把原來存在于系統(tǒng)表空間中的表轉(zhuǎn)移到獨(dú)立表空間中的方法及步驟：

1. 使用mysqldump導(dǎo)出所有數(shù)據(jù)庫表數(shù)據(jù)
2. 停止MySQL服務(wù)，修改innodb_file_per_table參數(shù)的值為on，并刪除Innodb相關(guān)的表空間文件
3. 重啟MySQL服務(wù)，重建Innodb系統(tǒng)表空間
4. 重新導(dǎo)入數(shù)據(jù)

Innodb存儲(chǔ)引擎的特性：

特性一：

Innodb是一種事務(wù)性存儲(chǔ)引擎，完全支持事務(wù)的ACID特性，也就是原子性、一致性、隔離性以及持久性。Innodb存儲(chǔ)引擎為了實(shí)現(xiàn)原子性、一致性以及持久性這三個(gè)特性，它使用了兩個(gè)特殊的日志類型：Redo Log（重做日志） 和 Undo Log（回滾日志）。

Redo Log主要實(shí)現(xiàn)事務(wù)的持久性，Redo Log主要由兩部分組成，一是存在于內(nèi)存中的重做日志緩沖區(qū)，該緩沖區(qū)大小由innodb_log_buffer_size參數(shù)來決定。二是重做日志文件，即ib_logfile開頭的文件。

Undo Log主要作用是用于幫助未提交事務(wù)進(jìn)行回滾和實(shí)現(xiàn)MVCC多版本并發(fā)控制，所以Redo Log中存儲(chǔ)的是已提交的事務(wù)，Undo Log存儲(chǔ)的則是未提交的事務(wù)。因此我們對(duì)Innodb表中的數(shù)據(jù)進(jìn)行修改時(shí)，不僅會(huì)產(chǎn)生Redo Log，還會(huì)產(chǎn)生一定數(shù)量的Undo Log。Redo Log基本上是順序?qū)懭氲?，因?yàn)樵跀?shù)據(jù)庫運(yùn)行時(shí)不需要對(duì)Redo Log進(jìn)行讀取操作，而Undo Log是需要進(jìn)行隨機(jī)讀寫的，所以我們有條件的話可以把Undo Log放在ssd這種隨機(jī)讀寫性能高的磁盤上以提高性能

查看重做日志緩沖區(qū)大小，單位為字節(jié)：

重做日志文件的數(shù)量由innodb_log_files_in_group參數(shù)決定：

特性二：

Innodb支持行級(jí)鎖，行級(jí)鎖是在寫操作時(shí)，我們所需要鎖定的資源更少，這樣我們支持的并發(fā)就會(huì)更多。需要注意的是Innodb的行級(jí)鎖是由存儲(chǔ)引擎層實(shí)現(xiàn)的，MySQL服務(wù)層是完全不了解存儲(chǔ)引擎中鎖的實(shí)現(xiàn)方式的。

什么是鎖：鎖是數(shù)據(jù)庫系統(tǒng)區(qū)別于文件系統(tǒng)的重要特性，鎖的作用主要是管理共享資源的并發(fā)訪問。例如鎖可以控制當(dāng)一個(gè)用戶向郵箱中投遞郵件的時(shí)候，另一個(gè)用戶會(huì)阻塞，無法向相同的郵件末尾寫入郵件，這樣就不會(huì)導(dǎo)致郵件的內(nèi)容出現(xiàn)混淆。鎖的另一個(gè)特性就是實(shí)現(xiàn)事務(wù)的隔離性，對(duì)于未提交的事物，鎖定的數(shù)據(jù)，是無法被其他事務(wù)所查詢到的。

鎖的常見分類：共享鎖（也稱讀鎖），獨(dú)占鎖（也稱寫鎖），從名字中可以看出讀鎖是共享的，也就是說相互不會(huì)被阻塞的，多個(gè)線程可以在同一時(shí)間讀取同一資源，而不相互干擾。寫鎖是獨(dú)占的，也就是排他的，一個(gè)寫鎖會(huì)阻塞其他的寫鎖或讀鎖。這是處于數(shù)據(jù)完整性的考慮，只有這樣才能保證，在給定的時(shí)間里，只有一個(gè)線程能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源，也就是前面所說的實(shí)現(xiàn)了事務(wù)的隔離性。

寫鎖不兼容寫鎖和讀鎖，而讀鎖兼容讀鎖。需要注意的是，對(duì)Innodb來說讀鎖和寫鎖都是行鎖，所謂兼容性是指同一行記錄的兼容性情況。另外一點(diǎn)，實(shí)際上在Innodb里鎖的兼容關(guān)系并不是如此，由于Innodb實(shí)現(xiàn)的鎖利用了Undo Log，所以實(shí)際使用時(shí)讀鎖和寫鎖是兼容的。

關(guān)于鎖的粒度含義：

鎖的粒度含義，就是如果加鎖資源的最小單位，比如在行上加鎖，最小單位就是行，這個(gè)鎖就稱為行級(jí)鎖。如果鎖的最小單位是數(shù)據(jù)頁，我們就稱為頁級(jí)鎖。同理如果鎖的最小單位是表的話，這個(gè)鎖就是表級(jí)鎖。通常提高共享資源并發(fā)性的方式就是讓鎖定義的對(duì)象盡可能的小，最理想的方式就是對(duì)修改的數(shù)據(jù)進(jìn)行精確的鎖定。任何時(shí)候在給定的資源上鎖定的數(shù)據(jù)越少，系統(tǒng)的并發(fā)性就會(huì)越高，只要相互之間不產(chǎn)生阻塞就可以

mysql所支持的兩種鎖的粒度，表級(jí)鎖和行級(jí)鎖：

表級(jí)鎖開銷小，并發(fā)性低，表鎖會(huì)在加鎖時(shí)候鎖定整張表，當(dāng)用戶對(duì)表進(jìn)行寫操作的時(shí)候，要先進(jìn)行解鎖，這時(shí)候就會(huì)阻塞其他用戶對(duì)表的讀寫操作，只有沒有寫鎖的時(shí)候，其他讀取的用戶才能獲得讀鎖，讀鎖之前說的是不會(huì)相互堵塞的，表級(jí)鎖通常是在mysql服務(wù)器層所實(shí)現(xiàn)的。

行級(jí)鎖可以最大程度的支持并發(fā)處理，同時(shí)鎖的開銷也比表級(jí)鎖開銷要大，所以它是并發(fā)性高，Innodb實(shí)現(xiàn)了行級(jí)鎖，行級(jí)鎖是在mysql存儲(chǔ)引擎中實(shí)現(xiàn)的，而不在mysql服務(wù)器中實(shí)現(xiàn)，

鎖的另外兩個(gè)概念，阻塞和死鎖：

阻塞是因?yàn)椴煌i之間兼容性的關(guān)系，在有些時(shí)刻，一個(gè)事務(wù)的鎖在需要等待另一個(gè)事務(wù)釋放它所占用的資源，這就形成了阻塞。比如前面所提到的表級(jí)鎖，如果當(dāng)?shù)谝粋€(gè)連接，在一張Innodb表上加了排他鎖，此時(shí)第二個(gè)連接想要在該表上加共享鎖的話，就要等第一個(gè)連接釋放排他鎖，這樣第一個(gè)連接就阻塞了第二個(gè)連接。阻塞是為了確保事務(wù)可以并發(fā)且可以正常的運(yùn)行。但是如果一個(gè)系統(tǒng)出現(xiàn)大量的阻塞就說明系統(tǒng)出現(xiàn)了問題，也許是在一個(gè)被頻繁更新的表上出現(xiàn)了慢查詢，或是一個(gè)頻繁訪問的資源加上了排他鎖。阻塞過多的時(shí)候可會(huì)令數(shù)據(jù)庫連接大量的堆積，從而占用大量的系統(tǒng)資源，使得系統(tǒng)性能整體下降。

死鎖是兩個(gè)或兩個(gè)以上事務(wù)在執(zhí)行過程中，相互占用了對(duì)方等待的資源而產(chǎn)生的異常。從定義中可以看到，處在阻塞中的多個(gè)事務(wù)，阻塞事務(wù)占用了被等待阻塞事務(wù)的資源。而死鎖呢，則是多個(gè)事務(wù)，相互之間互相占用對(duì)方等待的資源，這是阻塞和死鎖的最大不同之處。另外一個(gè)不同點(diǎn)就是死鎖數(shù)據(jù)庫會(huì)自動(dòng)發(fā)現(xiàn)，并且在多個(gè)死鎖的事務(wù)中，找到一個(gè)資源占用最少的事務(wù)來進(jìn)行回滾操作，這樣就可以是其他事務(wù)正常運(yùn)行了。所以說死鎖是可以由系統(tǒng)自動(dòng)處理的，如果只有少量的死鎖并不會(huì)對(duì)系統(tǒng)造成什么樣的影響，只要在應(yīng)用程序中發(fā)現(xiàn)死鎖并進(jìn)行處理就可以。但是如果一個(gè)系統(tǒng)頻繁的出現(xiàn)大量的死鎖就需要留意了，大多數(shù)情況下可以通過在多個(gè)事務(wù)中按相同順序訪問所需要資源來解決死鎖問題，也可以通過增加相關(guān)索引來解決。

關(guān)于鎖的一些實(shí)際操作，可以參考我早期寫的兩篇文章：

• MySQL-鎖
• MySQL-鎖02

Innodb引擎與其他大部分引擎不同的是，Innodb還提供了一個(gè)狀態(tài)監(jiān)查監(jiān)控工具，我們可以使用如下SQL語句查看監(jiān)控信息：

show engine innodb status;

注：兩次采樣之間至少間隔30s，這樣才能保證采集到足夠的數(shù)據(jù)

執(zhí)行該命令，輸出的內(nèi)容大致如下，不同的版本可能輸出的內(nèi)容不太一樣，我這里使用的是5.7.18版本：

=====================================
2018-10-10 11:59:55 0x7f755c9c5700 INNODB MONITOR OUTPUT # 當(dāng)前日期和時(shí)間
=====================================
Per second averages calculated from the last 57 seconds  # 這行顯示的是計(jì)算出這一平均值的時(shí)間間隔，即自上次輸出以來的時(shí)間，或者是距上次內(nèi)部復(fù)位的時(shí)長
-----------------
BACKGROUND THREAD  # backgroup 線程信息
-----------------
srv_master_thread loops: 61445 srv_active, 0 srv_shutdown, 5196496 srv_idle
srv_master_thread log flush and writes: 5257941
----------
SEMAPHORES  # 如果有高并發(fā)的工作負(fù)載，你就要關(guān)注下接下來的段（SEMAPHORES信號(hào)量）,它包含了兩種數(shù)據(jù)：事件計(jì)數(shù)器以及可選的當(dāng)前等待線程的列表，如果有性能上的瓶頸，可以使用這些信息來找出瓶頸，不幸的是，想知道怎么使用這些信息還是有一點(diǎn)復(fù)雜
----------
OS WAIT ARRAY INFO: reservation count 219819 # 這行給出了關(guān)于操作系統(tǒng)等待數(shù)組的信息，它是一個(gè)插槽數(shù)組，innodb在數(shù)組里為信號(hào)量保留了一些插槽，操作系統(tǒng)用這些信號(hào)量給線程發(fā)送信號(hào)，使線程可以繼續(xù)運(yùn)行，以完成它們等著做的事情，這一行還顯示出innodb使用了多少次操作系統(tǒng)的等待：保留統(tǒng)計(jì)（reservation count）顯示了innodb分配插槽的頻度，而信號(hào)計(jì)數(shù)（signal count）衡量的是線程通過數(shù)組得到信號(hào)的頻度，操作系統(tǒng)的等待相對(duì)于空轉(zhuǎn)等待（spin wait）要昂貴些。
OS WAIT ARRAY INFO: signal count 219805  # 進(jìn)行OS WAIT線程，接收到多少次信號(hào)(single)被喚醒，如果這個(gè)single數(shù)值越大，幾十萬或者幾百萬，可能是很多I/0等待或者是InnoDB爭(zhēng)用問題(關(guān)于爭(zhēng)用問題可能與OS調(diào)度有關(guān)，可以嘗試減少innodb_thread_concurrency參數(shù))
RW-shared spins 0, rounds 168443, OS waits 84271  # RW-shared 共享鎖，
RW-excl spins 0, rounds 180120, OS waits 1119  # RW-excl 排他鎖
RW-sx spins 60, rounds 6000, OS waits 50
Spin rounds per wait: 168443.00 RW-shared, 180120.00 RW-excl, 100.00 RW-sx
------------
TRANSACTIONS  # 包含了InnoDB事務(wù)(transaction)的統(tǒng)計(jì)信息
------------
Trx id counter 264168  # 當(dāng)前的transaction id ,這是個(gè)系統(tǒng)變量，隨著每次新的transaction產(chǎn)生而增加
Purge done for trx's n:o < 264167 undo n:o < 0 state: running but idle  # 正在進(jìn)行清空的操作操作的transaction ID，Purge的原則就是記錄沒有被其它事務(wù)繼續(xù)使用了
History list length 10  # 記錄了undo spaces 內(nèi)unpurged 的事務(wù)個(gè)數(shù)
LIST OF TRANSACTIONS FOR EACH SESSION:   # 當(dāng)前活躍事務(wù)列表
---TRANSACTION 421617178700976, not started  # 每個(gè)事務(wù)的第一行以事務(wù)的ID和狀態(tài)開始，not started表示這個(gè)事務(wù)已經(jīng)提交并且沒有再發(fā)起影響事務(wù)的語句，可能剛好空閑
0 lock struct(s), heap size 1136, 0 row lock(s) # 該事務(wù)鎖定結(jié)構(gòu)的數(shù)目和堆大小以及鎖定的行數(shù)，堆的大小指的是為了持有這些行鎖而占用的內(nèi)存大小
---TRANSACTION 421617178702800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178697328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178701888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178700064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178699152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178698240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178695504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178704624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178696416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O  # 顯示了I/O  Helper thread d的狀態(tài)，包含一些統(tǒng)計(jì)信息
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread) # insert buffer thread
I/O thread 1 state: waiting for i/o request (log thread) # log thread
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread) # 以上為默認(rèn)的4個(gè)read thread
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread) # 以上為默認(rèn)的4個(gè)write thread
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,  # 讀線程和寫線程掛起操作的數(shù)目等，aio的意思是異步I/O
 ibuf aio reads:, log i/o's:, sync i/o's:   # insert buffer thread掛起的fsync()操作數(shù)目等
Pending flushes (fsync) log: 0; buffer pool: 0  # log thread掛起的fsync()操作數(shù)目等
469 OS file reads, 1083592 OS file writes, 570823 OS fsyncs  # 這行顯示了讀，寫和fsync()調(diào)用執(zhí)行的數(shù)目，在你的機(jī)器環(huán)境負(fù)載下這些絕對(duì)值可能會(huì)有所不同，因此更重要的是監(jiān)控它們過去一段時(shí)間內(nèi)是如何改變的
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s  # 這行顯示了當(dāng)前被掛起的讀和寫操作數(shù)

注：三行掛起讀寫線程、緩沖池線程、日志線程的統(tǒng)計(jì)信息的值是檢測(cè)I/O受限的應(yīng)用的一個(gè)好方法，如果這些I/O大部分有掛起操作，那么負(fù)載可能I/O受限。在linux系統(tǒng)下使用參數(shù)：innodb_read_io_threads和innodb_write_io_threads兩個(gè)變量來配置讀寫線程的數(shù)量，默認(rèn)為各4個(gè)線程。
insert buffer thread：負(fù)責(zé)插入緩沖合并，如：記錄被從插入緩沖合并到表空間中
log thread：負(fù)責(zé)異步刷事務(wù)日志
read thread：執(zhí)行預(yù)讀操作以嘗試預(yù)先讀取innodb預(yù)感需要的數(shù)據(jù)
write thread：刷新臟頁緩沖

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX  # 這部分顯示了insert buffer和adaptive hash index兩個(gè)部分的結(jié)構(gòu)的狀態(tài)
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges  # 這行顯示了關(guān)于size（size 12代表了已經(jīng)合并記錄頁的數(shù)量）、free list（代表了插入緩沖中空閑列表長度）和seg size大小（seg size 27572顯示了當(dāng)前insert buffer的長度，大小為27572*16K=440M左右）的信息。18074934 merges代表合并插入的次數(shù)
merged operations:  # 這個(gè)標(biāo)簽下的一行信息insert，delete mark，delete分別表示merge操作合并了多少個(gè)insert buffer，delete buffer，purge buffer
 insert 0, delete mark 0, delete 0  # insert 插入的記錄數(shù)，delete mark 打上的標(biāo)記,delete 刪除的次數(shù)
discarded operations:   # 這個(gè)標(biāo)簽下的一行信息表示當(dāng)change buffer發(fā)生merge時(shí)表已經(jīng)被刪除了，就不需要再將記錄合并到輔助索引中了
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 13 buffer(s)  # 這行顯示了自使用哈希索引的狀態(tài)，其中，Hash table size 87709057表示AHI的大小，node heap has 10228 buffer(s)表示AHI的使用情況
Hash table size 34679, node heap has 8 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 13 buffer(s)
Hash table size 34679, node heap has 14 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
11.61 hash searches/s, 0.00 non-hash searches/s   # 這行顯示了在頭部第1部分提及的時(shí)間內(nèi)Innodb每秒完成了多少哈希索引操作，1741.05 hash searches/s表示每秒使用AHI搜索的情況，539.48 non-hash searches/s表示每秒沒有使用AHI搜索的情況(因?yàn)楣Ｋ饕荒苡糜诘戎挡樵?，而范圍查詢，模糊查詢是不能使用哈希索引的?，通過hash searches: non-hash searches的比例大概可以了解使用哈希索引后的效率，哈希索引查找與非哈希索引查找的比例僅供參考，自適應(yīng)哈希索引無法配置，但是可以通過innodb_adaptive_hash_index=ON|OFF參數(shù)來選擇是否需要這個(gè)特性。
---
LOG  # 這部分顯示了關(guān)于innodb事務(wù)日志（重做日志）子系統(tǒng)的統(tǒng)計(jì)
---
Log sequence number 113764958  # 這行顯示了當(dāng)前最新數(shù)據(jù)產(chǎn)生的日志序列號(hào)
Log flushed up to   113764958  # 這行顯示了日志已經(jīng)刷新到哪個(gè)位置了（已經(jīng)落盤到事務(wù)日志中的日志序列號(hào)）
Pages flushed up to 113764958  
Last checkpoint at  113764949  # 這行顯示了上一次檢查點(diǎn)的位置（一個(gè)檢查點(diǎn)表示一個(gè)數(shù)據(jù)和日志文件都處于一致狀態(tài)的時(shí)刻，并且能用于恢復(fù)數(shù)據(jù)），如果上一次檢查點(diǎn)落后與上一行太多，并且差異接近于事務(wù)日志文件的大小，Innodb會(huì)觸發(fā)“瘋狂刷”，這對(duì)性能而言非常糟糕。
0 pending log flushes, 0 pending chkp writes  # 這行顯示了當(dāng)前掛起的日志讀寫操作，可以將這行的值與第7部分FILE I/O對(duì)應(yīng)的值做比較，以了解你的I/O有多少是由于日志系統(tǒng)引起的。
350037 log i/o's done, 0.00 log i/o's/second  # 這行顯示了日志操作的統(tǒng)計(jì)和每秒日志I/O數(shù)，可以將這行的值與第7部分FILE I/O對(duì)應(yīng)的值做比較，以了解你的I/O有多少是由于日志系統(tǒng)引起的。
---------------------- 
BUFFER POOL AND MEMORY  # 這部分顯示了關(guān)于innodb緩沖池及其如何使用內(nèi)存的統(tǒng)計(jì)
----------------------
Total large memory allocated 137428992  # 這行顯示了由innodb分配的總內(nèi)存，以及其中多少是額外內(nèi)存池分配，額外內(nèi)存池僅分配了其中很小一部分內(nèi)存，由內(nèi)部?jī)?nèi)存分配器分配，現(xiàn)在的innodb版本一般使用操作系統(tǒng)的內(nèi)存分配器，但老版本使用自己的，這是由于在那個(gè)時(shí)代有些操作系統(tǒng)并未提供一個(gè)非常好的內(nèi)存分配實(shí)現(xiàn)。
Dictionary memory allocated 1401012
Buffer pool size   8192  # 從這行開始的下面4行顯示緩沖池度量值，以頁為單位，度量值有總的緩沖池大小，空閑頁數(shù)，分配用來存儲(chǔ)數(shù)據(jù)庫頁的頁數(shù)，以及臟數(shù)據(jù)庫頁數(shù)。這行顯示了緩沖池總共有多少個(gè)頁，即即2705015*16K，共有43G的緩沖池
Free buffers       6147  # 這行顯示了緩沖池空閑頁數(shù)
Database pages     1986  # 這行顯示了分配用來存儲(chǔ)數(shù)據(jù)庫頁的頁數(shù)，即，表示LRU列表中頁的數(shù)量,包含young sublist和old sublist
Old database pages 713   # 這行顯示了LRU中的old sublist部分頁的數(shù)量
Modified db pages  0     # 這行顯示臟數(shù)據(jù)庫頁數(shù)
Pending reads      0     # 這行顯示了掛起讀的數(shù)量
Pending writes: LRU 0, flush list 0, single page 0  # 這行顯示了掛起寫的數(shù)量
# 注意，這里掛起的讀和寫操作并不與FILE I/O部分的值匹配，因?yàn)镮nnodb可能合并許多的邏輯讀寫操作到一個(gè)物理I/O操作中，LRU代表最近使用到的被掛起數(shù)量，它是通過沖刷緩沖中不經(jīng)常使用的頁來釋放空間以供給經(jīng)常使用的頁的一種方法，沖刷列表flush list存放著檢查點(diǎn)處理需要沖刷的舊頁被掛起的數(shù)量，單頁single page被掛起的數(shù)量（single page寫是獨(dú)立的頁面寫，不會(huì)被合并)。
Pages made young 0, not young 0   # 這行顯示了LRU列表中頁移動(dòng)到LRU首部的次數(shù)，因?yàn)樵摲?wù)器在運(yùn)行階段改變沒有達(dá)到innodb_old_blocks_time閥值的值，因此not young為0
0.00 youngs/s, 0.00 non-youngs/s  # 表示每秒young和non-youngs這兩類操作的次數(shù)
Pages read 428, created 1558, written 680621  # 這行顯示了innodb被讀取，創(chuàng)建，寫入了多少頁，讀/寫頁的值是指的從磁盤讀到緩沖池的數(shù)據(jù)，或者從緩沖池寫到磁盤中的數(shù)據(jù)，創(chuàng)建頁指的是innodb在緩沖池中分配但沒有從數(shù)據(jù)文件中讀取內(nèi)容的頁，因?yàn)樗⒉魂P(guān)心內(nèi)容是什么（如，它們可能屬于一個(gè)已經(jīng)被刪除的表）
0.00 reads/s, 0.00 creates/s, 0.00 writes/s  # 這行顯示了對(duì)應(yīng)上面一行的每秒read，create，write的頁數(shù)
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000  # 這行顯示了緩沖池的命中率，它用來衡量innodb在緩沖池中查找到所需頁的比例，它度量自上次Innodb狀態(tài)輸出后到本次輸出這段時(shí)間內(nèi)的命中率，因此，如果服務(wù)器自那以后一直很安靜，你將會(huì)看到No buffer pool page gets since the last printout。它對(duì)于度量緩存池的大小并沒有用處。
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s   # 這行顯示了頁面預(yù)讀，隨機(jī)預(yù)讀的每秒頁數(shù)
LRU len: 1986, unzip_LRU len: 0   # innodb1.0.x開始支持壓縮頁的功能，將原來16K的頁壓縮為1K，2K，4K，8K，而由于頁的大小發(fā)生了變化，LRU列表也有了些改變，對(duì)于非16K的頁，是通過unzip_LRU列表進(jìn)行管理的，可以看到unzip_LRU len為0表示沒有使用壓縮頁.
I/O sum[0]:cur[0], unzip sum[0]:cur[0]  # 統(tǒng)計(jì)信息及解壓信息
--------------
ROW OPERATIONS  # 這部分顯示了其他各項(xiàng)的innodb統(tǒng)計(jì)
--------------
0 queries inside InnoDB, 0 queries in queue  # 這行顯示了innodb內(nèi)核內(nèi)有多少個(gè)線程，隊(duì)列中有多少個(gè)線程，隊(duì)列中的查詢是innodb為限制并發(fā)執(zhí)行的線程數(shù)量而不運(yùn)行進(jìn)入內(nèi)核的線程。查詢?cè)谶M(jìn)入隊(duì)列之前會(huì)休眠等待。
0 read views open inside InnoDB  # 這行顯示了有多少打開的innodb讀視圖，讀視圖是包含事務(wù)開始點(diǎn)的數(shù)據(jù)庫內(nèi)容的MVCC快照，你可以看看某特定事務(wù)在第6部分TRANSACTIONS是否有讀視圖
Process ID=32728, Main thread ID=140141416933120, state: sleeping  # 這行顯示了內(nèi)核的主線程狀態(tài)
Number of rows inserted 115492, updated 164886, deleted 1698, read 1351954675  # 這行顯示了多少行被插入，更新和刪除，讀取
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 34.46 reads/s  # 這行顯示了對(duì)應(yīng)上面一行的每秒平均值，如果想查看innodb有多少工作量在進(jìn)行，那么這兩行是很好的參考值
----------------------------
END OF INNODB MONITOR OUTPUT  # 信息結(jié)束標(biāo)記，要注意了，如果看不到這行輸出，可能是有大量事務(wù)或者是有一個(gè)大的死鎖截?cái)嗔溯敵鲂畔?============================

關(guān)于其他常用的存儲(chǔ)引擎可以參考我另一篇文章：

• 除Innodb和MyISAM外MySQL還有哪些存儲(chǔ)引擎