摘要：所以我們如果使用系統表空間進行表數據的存儲，所面臨的問題是無法很容易的收縮系統文件，造成大量的空間浪費，并且會產生大量的磁盤碎片，從而降低了系統性能。

??為不了解存儲引擎的數據庫使用者，提供了很大的便利，因為innodb適應大部分應用場景。

??和myisam不同的是，innodb是一種事務型存儲引擎。也就是說，innodb是支持事務的acid特性的。innodb的設計，更適合大量的小事務，而小事務大部分情況下， 都可以正常提交，很少會被回滾。

??innodb和mysiam存儲數據方式不同，innodb有自己的表空間的概念。表中的數據是存儲在表空間之中的，具體存儲在什么樣的表空間中了，則由 innodb_file_per_table 這個參數來決定。

ON： 為每個innodb表建立一個空間，tablename.ibd（.ibd后綴）

OFF：則會把數據存儲到系統的共享表空間，ibdatax(x代表一個數字,從1開始)

??在mysql5.6之前的innodb參數-innodb_file_per_table = OFF （默認）,也就是數據默認會存儲到系統表空間中。

??默認的設置會遇到下面的問題：在一個繁忙的系統中，系統表空間不斷的增長。沒超過我們磁盤限制是可以接受的，但是一旦我們磁盤空間出現不足，我們為了釋放磁盤空間，不得不在系統中刪除大量無效的數據（如：長期不使用的日志類數據等）。我們在刪除數據后，系統表空間并不會縮小。這種情況下，我們想通過復制文件的方式，對數據庫進行備份。由于雖然刪除了數據，但表空間大小并不會改變，這就意味著，我們每次刪除時，都要浪費很大的空間。

??你不要以為我們不會遇到備份的問題，實際上，目前我們最常用的熱備方式，就是這樣處理的。這時我們就會遇到使用系統表空間進行存儲的一些問題了。

??想要收縮系統表空間的唯一方法就是把系統表空間的表導出后，刪除innodb的文件后，在重啟mysql服務器，進行表空間的重建，然后在導入數據。這個過程其實是很復雜的，并且十分耗時，這在業務繁忙的生產環境中了，顯然是不可能做到的。

??所以我們如果使用系統表空間進行表數據的存儲，所面臨的問題是無法很容易的收縮系統文件，造成大量的空間浪費，并且會產生大量的磁盤碎片，從而降低了系統性能。

??如果我們使用獨立表空間， 對一個大表的數據進行清理之后，可以方便的對這個表進行收縮，使用optimize table命令 ，會對表進行重建。但是對比整個系統進行重建要快的多，而且不需要重啟數據庫服務器，甚至不會影響這個表的正常訪問。所以從這點來看，使用獨立的表空間顯然要比使用系統表空間要好得多。

??對于系統個表空來說，由于只有一個文件。如果同時對多個表進行數據刷新時，實際上在文件系統層面上來說是順序進行的，所以會產生一定的io瓶勁。

??對于獨立表空間來說，由于每個表都有自己的表空間文件。在進行數據寫入時，可以利用多個文件增加io處理的性能。所以對于平凡寫入操作的系統來說，不太適合使用系統表空間統一存放數據，而是要使用獨立表空間的方式。

??對使用innodb存儲引擎的表使用獨立表空間進行管理，在mysql5.6后，使用innodb存儲引擎的默認表配置就是獨立存儲空間。

把原來存在于系統表空間中的表轉移到獨立表空間中的方法

??如果數據庫中使用存儲過程，觸發器，計劃事件等，一定要記得一起導出。

??停止mysql服務，如果是主從架構的話，我們可以先從-從服務器上進行這些操作。停止mysql服務器之后，我們需要修改my.cnf文件，并加入innodb_file_per_table這個參數，然后手動刪除原來innodb系統中的相關數據文件，這時后系統表空間就不含有任何數據了。

??實際上，由于我們之前已經備份了所有數據表，所以這里可以重建一個data目錄，并使用mysql install db腳本來重建數據庫目錄

??重新導入之前備份的數據，就可以完成數據的恢復，并且把原來存在于系統表空間的數據遷移到獨立表空間中了。

??我們也可不按照上面操作，在修改完系統參數后，重啟服務后將需要修改的所有innodb表都執行一遍：alter table table_name engine=innodb;.這樣也可以把系統表空間的表遷移到獨立表空間中，但是卻無法對系統表空間所占用的空間進行回收，所以還是按照上面正規步驟吧。

我們把系統表空間數據遷移到獨立表空間后，那么現在系統表空間還有什么內容了？

??答案是雖然我們已經把系統表數據遷移了出來，但是對于系統表空間來說，還是有一些很重要的東西要存儲的。這其中之一就是 innodb數據字典信息

??數據字典是數據對象結構的元數據信息，存放與數據庫對象相關的信息。如：表，列，索引，內鍵等內容。

mysql是使用 .frm文件存儲表結構定義的，那么.frm文件和系統空間中的數據字典又有什么區別了？

??首先，.frm文件是mysql服務器層產生的文件，可以理解為mysql數據庫服務器層的數據字典，這對mysql所有存儲引擎都是一樣的。mysql數據層所保留的東西是與存儲引擎無關的，而innodb內部的數據字典是存儲引擎內部產生的，并可以保證事務的一些安全性。另外，innodb存儲引擎沒有直接使用mysql數據上的一些類型，而是自己封裝了定義，因此其數據字典存儲的都是引擎相關的內容。最后，.frm文件只是簡單的二進制文件，而innodb數據字典是通過b樹來進行數據管理的

除了innodb數據字典外，系統表空間還存在undo回滾段和innodb臨時表

這兩種在mysql5.7都是可以從系統表移除的 了，但還會有很多人默認的把他們存儲在系統表空間中。這里要聲明的是，undo回滾段的存儲在mysql5.6就已經支持了。

??完全支持事物的acid特性

redo log 和 undo log

redo log實現事物的持久性，其有兩部分組成

innodb_log_buffer_size：配置redo log緩沖區的大小，以字節為單位，程序每隔秒就會把緩存區數據刷新到磁盤里，所以這個緩存區不用配置太大。

innodb_log_files_in_group：決定了數據庫目錄下的ib_**的文件數量

undo log 幫助對未提交事物進行回滾和實現多版本并發控制

聯系

redo log存儲的是已提交的事物，undo log存儲的是未提交的事物。

對innodb的表進行修改時，不僅會產生redo log 還會產生 undo log

用戶事物失敗或使用回滾語句，就需要利用undo log中的信息了。

redo log順序寫入，在數據庫允許時，不需要對redo log進行讀取操作。undo log 需要進行隨機讀寫的，mysql5.6中的undo log可以獨立于系統的空間而存在。如果條件允許，我們就可以把undo log存儲到固態存儲設備上，這樣可以獲得更好的性能。

??同myisam支持的表級鎖是不一樣的，行級鎖的特點是在進行寫操作時，我們所需要鎖定的資源更少，這樣可以支持的并發就更多。需要注意的是，innodb的行級鎖是由存儲引擎層實現的，mysql服務器完全不了解存儲引擎中鎖的實現方式。

??說到鎖可能大家還不太了解，數據庫中鎖的作用是什么了？因為鎖對我們數據庫的性能有非常大的影響，所以這里介紹下什么是鎖。

??可能很多開發人員會問，經常聽到dba說的各種各樣的鎖，比如說行級鎖，表級鎖，共享鎖，獨斷鎖等等。這些鎖的作用是啥了？

??鎖是數據庫系統區別于文件系統的重要特性鎖的主要作用是管理共享資源的并發訪問。并發訪問是一個很讓人頭疼的事情啊，對于任何一個在竄行環境下工作良好的系統，一旦涉及到并發的情況就有可能出現各種各樣的問題。
??比如說郵件系統，在一個郵箱中，所有的郵件都是竄行的疊在一起的，彼此首尾相連。這種結構對于讀取和投遞郵件都是有很大的好處的。當有新的郵件到來時，只要插入文件末尾就可以了。當有兩個用戶，同時對郵箱進行郵件投遞，那會出現什么樣的情況了？很有可能郵箱的數據會被破壞，兩封性的內容會交叉在一起。當然了，這可能和我們平常生活的感受有很大的不同。那是因為現在的郵箱系統都是利用了鎖進行了控制。鎖保證了一個用戶向郵箱投遞郵件時，另一個用戶會阻塞，無法向相同的文件末尾寫入文件。
??那么鎖的另一個作用就是實現事務的隔離性。前面提到過，我們通過redo log 和 undo log 實現了事務的原子性，一致性和持久性，而隔離性就需要鎖來實現。對于未提交的事務鎖定的數據是無法被其他事務查詢到的。

了解了什么是鎖，及鎖的作用后，我們來看看鎖的常見分類。

共享鎖（也稱讀鎖）：可以看出讀鎖是共享的，不會被阻塞的， 多個線程可以在同一時間讀取同一資源，而不相互干擾。
獨占鎖（也稱寫鎖）：寫鎖是獨占的，是排它的，會阻塞其他的寫鎖和讀鎖。這是對數據完整性的考慮，只有這樣才能保證在給定的時間里，只有一個線程可以執行寫入，并防止其他用戶正在讀取寫入的資源，也就是我們前面說的事務的隔離性。
??對于innodb來說，讀鎖和寫鎖都是行鎖。兼容性指的是對同一行記錄可見兼容的情況

??按照上面介紹，對于同一資源的請求應該是互斥的，但是實際體驗并不和以上相同。（鎖比上面提到的復雜多了）

初始化數據庫中數據

測試，兩個連接啟動一個事務。一個連接給表的第一行記錄加個獨占鎖，不提交事務

另一個連接獲取加了獨占鎖的數據，仍然可以獲取到

這和我們介紹的兼容性情況完全不同，這是為什么了？

??這就是innodb在實現鎖上的獨到之處，這里innodb用到了我們上面提到的undo log中的記錄，所以我們在第二個連接中查看的數據實際上是存儲在undo log中的版本。也就是說，并不是我們在第一個連接中進行更新的。

??鎖的粒度就是鎖的策略，指被加鎖資源的最小單位。比如在行上加鎖，那么最小單位就是行，這鎖就稱為行級鎖。如果說的最小單位是列，那就稱為鎖的列級鎖。同理，如果鎖的最小單位是表的話，那么鎖的最小單位就是表級鎖。

表級鎖：mysql中最基本的表策略，開銷最小的策略，并發性低。表鎖會在加鎖時鎖定整張表，一個用戶在對表進行寫操作前，需要先獲得寫鎖。這會阻塞其他用戶的讀寫操作，只有沒有寫鎖時，其他用戶才可以獲取讀鎖，讀鎖之前說了是相互間不會阻塞的。表級鎖通常是在mysql服務器層實現的，所以雖然innodb實現了行級鎖，但是在一些時候，mysql數據服務層還是會對innodb表加上表級鎖。

實驗使用兩個連接，一個連接加表級獨占鎖

第二個連接從表獲取數據

第一個連接執行解鎖操作，第二個連接就會被執行

行級鎖：可以最大程度的支持并發處理，同時鎖的開銷比表級鎖要大。目前innodb和其他的存儲引擎中實現了行級鎖，行級鎖只在存儲引擎中進行實現，而mysql服務層并沒有實現。

什么是阻塞：阻塞是不同鎖之間兼容性的關系。在有些時刻，一個事務中的鎖需要等待另一事務中的鎖釋放它所占用的資源，這就形成了阻塞。阻塞是為了確保事務可以并發且正常的運行， 但是，當一個系統中如果出現了大量的阻塞，往往就意味著系統中存在著問題。也許是在一個被頻繁更新的表上出現了慢查詢或是由于其他的管理操作，如表備份等。對一個頻繁訪問的資源加上了排他鎖，過多的阻塞不是個好的現象，使數據庫中的連接大量的堆積，占用大量的系統資源，使得系統的總體性能下降。

什么是死鎖：死鎖是指兩個或兩個以上的事務，在執行過程中相互占用了對方等待的資源 ，而產生的一種異常。處在阻塞中的事務占有被阻塞事務的多個資源，而死鎖是產生事務的多個事務之間相互占用對方等待的資源，這就是阻塞和死鎖最大的不同之處。另外一點不同是，死鎖數據庫系統會自動發現，并且在多個資源占用的事務中，選擇一個資源占用最少的事務來進行回滾操作。這樣就可以使其他事務正常的運行了，所以說死鎖是可以由系統自動處理的。如果只是有少量的死鎖，并不會對系統造成什么影響。只要在應用程序中，發現死鎖，并進行重新處理就可以了。但是，如果一個系統中頻繁的出現大量的死鎖，這時就需要留意了。通常情況下，死鎖可以通過在多個事務中按相同的順序訪問所需要的資源來解決，也可通過增加相關的索引來解決。

show engine innodb status 這命令的用法百度

??innodb適合于大多數OLTP應用。無論是否需要事務支持，只要是不使用innodb所不支持的特殊的功能的話，我們都應該在新的系統中使用innodb存儲引擎。其中所說的特殊功能可能就包括之前介紹myisam功能中對于空間應用和全文索引這些應用。由于在mysql5.7之前,只支持myisam存儲引擎，所以如果我們要使用mysql來存儲這類應用數據的話，可能就只能選擇myisam存儲引擎了。可是這種情況在mysql5.7之后有了改變，innodb支持全文索引和空間函數了。 所以對于這類應用，我們也可以完全使用innodb存儲引擎來進行存儲了。innodb和myisam是mysql最常用的兩種存儲引擎，還有一些引擎會使用到，請聽下回分解。

InnoDB 引擎獨立表空間 innodb_file_per_table
MySQL Server參數優化 - innodb_file_per_table（獨立表空間）
show engine innodb status解讀
死鎖和阻塞的關系