mysql innodb儲存引擎介紹

標籤：

innodb儲存引擎
1.儲存：資料目錄。可以通過配置修改
隱藏檔：frm，ibd結尾的檔案。frm儲存表結構，ibd儲存索引和資料
儲存日誌：ib_logfilen檔案

2.innodb儲存引擎開啟或關閉：
關閉innodb_fast_shutdown=
0 完成所有的full purge和merge insert buffer操作(如：做InnoDB plugin升級時)
1 預設，不需要完成上述操作，但會重新整理緩衝池中的髒頁
2 不完成上述兩個操作，而是將日誌寫入記錄檔，下次啟動時，會執行恢複操作recovery
沒有正常地關閉資料庫(如：kill命令)/innodb_fast_shutdown=2時，需要進行恢複操作。

恢複innodb_force_recovery=
0 預設，但需要恢複時執行所有恢複操作
1 忽略檢查到的corrupt頁
2 阻止主線程的運行，如主線程需要執行full purge操作，會導致crash
3 不執行交易回復操作
4 不執行插入緩衝的合併作業
5 不查看撤銷日誌undo log，InnoDB儲存引擎會將所有未提交的事務視為已提交
6 不執行前滾的操作

3.資料表空間：
資料表空間：單獨資料表空間和共用資料表空間。資料表空間的最大限制為64TB

查看當前資料表空間設定：
mysql> show variables like "innodb_file_per_table";
ON代表單獨資料表空間管理，OFF代表共用資料表空間管理；（查看單表的資料表空間管理方式，需要查看每個表是否有單獨的資料檔案）

修改資料庫的資料表空間管理方式：
修改innodb_file_per_table的參數值即可，但是修改不能影響之前已經使用過的共用資料表空間和單獨單獨資料表空間；
innodb_file_per_table=1 為使用單獨資料表空間
innodb_file_per_table=0 為使用共用資料表空間

mysql> show variables like ‘innodb_data%‘;

配置參數：
innodb_data_file_path=ibdata1:2G;ibdata2:3G:autoextend:max:4G //指定兩個共用空間。大小分別是2G和3G。當兩個都滿了後ibdata2自動成長最大4G
也可以指定儲存路徑：innodb_data_file_path=/tmp/ibdata1:2G;ibdata2:3G:autoextend:max:4G
需提前把innodb_data_home_dir= 設定為空白。
共用資料表空間的優點:
資料表空間可以分成多個檔案存放到各個磁碟，所以表也就可以分成多個檔案存放在磁碟上，表的大小不受磁碟大小的限制（很多文檔描述有點問題）。
資料和檔案放在一起方便管理

共用資料表空間的缺點:
所有的資料和索引存放到一個檔案，雖然可以把一個大檔案分成多個小檔案，但是多個表及索引在資料表空間中混合儲存，當資料量非常大的時候，
表做了大量刪除操作後資料表空間中將會有大量的空隙，特別是對於統計分析，對於經常刪除操作的這類應用最不適合用共用資料表空間。
共用資料表空間分配後不能回縮：當出現臨時建索引或是建立一個暫存資料表的動作表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了（
可以理解為mysql的資料表空間10G，但是才使用10M，但是作業系統顯示mysql的資料表空間為10G），進行資料庫的冷備很慢

單獨資料表空間的優點:
每個表都有自已單獨的資料表空間，每個表的資料和索引都會存在自已的資料表空間中，可以實現單表在不同的資料庫中移動。
空間可以回收（除drop table操作處，表空不能自已回收）
Drop table操作自動回收資料表空間，如果對於統計分析或是日值表，刪除大量資料後可以通過:alter table TableName engine=innodb;回縮不用的空間。
對於使innodb-plugin的Innodb使用turncate table也會使空間收縮。
對於使用單獨資料表空間的表，不管怎麼刪除，資料表空間的片段不會太嚴重的影響效能，而且還有機會處理。

單獨資料表空間的缺點:
單表增加過大，當單表佔用空間過大時，儲存空間不足，只能從作業系統層面思考解決方案；
4.事務：
事務的四個特性：原子性，一致性，隔離性，持久性。
事務的開啟： set autocommit=1 //開啟事務，0則是關閉事務

事務的提交：
start transaction //開啟
commit;//提交
隱士提交：
隱式提交的SQL語句
以下這些SQL語句會產生一個隱式的提交操作，即執行完這些語句後，會有一個隱式的COMMIT操作。
1、DDL語句：ALTER DATABASE...UPGRADE DATA DIRECTORY NAME、。。。。
2、用來隱式的修改mysql架構的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。
3、管理語句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE 、REPAIR TABLE。
事務的隔離級：
set {global|session} transaction=
1、READ UNCOMMITED
2、READ COMMITED
3、REPEATABLE READ
4、SERIALIZABLE
查看當前會話的交易隔離等級：
select @@tx_isolation;
插卡看全域交易隔離等級：
select @@global.tx_isolation;

在SERIALIZBLE的交易隔離等級，InnoDB儲存引擎會對每個SELECT語句後自動加上LOCK IN SHARE MODE，即給每個讀取操作加一個共用鎖定，
因此在這個交易隔離等級下，讀佔用鎖了，一致性的非鎖定讀不再予以支援，一般不再本地事務中使用SERIALIZBLE的隔離等級，
SERIALIZABLE的交易隔離等級主要用於InnoDB儲存引擎的分散式交易。
在READ COMMITED的交易隔離等級下，除了唯一性的約束檢查以及外鍵約束的檢查需要Gap Lock，InnoDB儲存引擎不會使用Gap Lock的鎖演算法。

分散式交易:
通過XA事務可以來支援分散式交易的實現，在使用分散式交易時，InnoDB儲存引擎必須使用SERIALIZABLE的隔離等級，
查看是否啟用了XA事務支援（預設開啟)
show variables like ‘innodb_support_xa‘
5.外鍵
外鍵的使用需要滿足下列的條件：
1. 兩張表必須都是InnoDB表，並且它們沒有暫存資料表。
2. 建立外鍵關係的對應列必須具有相似的InnoDB內部資料類型。
3. 建立外鍵關係的對應列必須建立了索引。
4. 假如顯式的給出了CONSTRAINT symbol，那symbol在資料庫中必須是唯一的。假如沒有顯式的給出，InnoDB會自動的建立。

如果子表試圖建立一個在父表中不存在的外索引值，InnoDB會拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外索引值，
最終動作取決於外鍵約束定義中的ON UPDATE和ON DELETE選項。
InnoDB支援5種不同的動作，如果沒有指定ON DELETE或者ON UPDATE，預設的動作為RESTRICT:
1. CASCADE: 從父表中刪除或更新對應的行，同時自動的刪除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支援。
2. SET NULL: 從父表中刪除或更新對應的行，同時將子表中的外鍵列設為空白。注意，這些在外鍵列沒有被設為NOT NULL時才有效。
ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支援。
3. NO ACTION: InnoDB拒絕刪除或者更新父表。
4. RESTRICT: 拒絕刪除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE選項的效果是一樣的。
5. SET DEFAULT: InnoDB目前不支援。

外鍵約束使用最多的兩種情況無外乎：
1）父表更新時子表也更新，父表刪除時如果子表有匹配的項，刪除失敗；在外鍵定義中，使用ON UPDATE CASCADE ON DELETE RESTRICT；
2）父表更新時子表也更新，父表刪除時子表匹配的項也刪除。在外鍵定義中，可以使用ON UPDATE CASCADE ON DELETE CASCADE；

InnoDB允許你使用ALTER TABLE在一個已經存在的表上增加一個新的外鍵:
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

InnoDB也支援使用ALTER TABLE來刪除外鍵：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
6.記憶體利用：
參數：innodb_buffer_pool_size
這個參數主要緩衝innodb表的索引，資料，插入資料時的緩衝。為Innodb加速最佳化首要參數。
　　該參數分配記憶體的原則：這個參數預設分配只有8M，如果是一個專用ＤＢ伺服器，那麼他可以佔到記憶體的70%-80%。這個參數不能動態更改，所以分配需多考慮。分配過大，
會使Swap佔用過多，致使Mysql的查詢特慢。如果你的資料比較小，那麼可分配是你的資料大小＋10％左右做為這個參數的值。
例如：資料大小為50MB,那麼給這個值分配innodb_buffer_pool_size＝64MB
查看參數配置：
show global variables like ‘%inndb_buffer%‘
設定方法：
set global innodb_buffer_pool_size=4G
這個參數分配值的使用方式可以根據show innodb status"G;中的
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4668764894;
去確認使用方式。

參數：innodb_additional_mem_pool
作用：用來存放Innodb的內部目錄
這個值不用分配太大，系統可以自動調。不用設定太高。
通常比較大資料設定16Ｍ夠用了，如果表比較多，可以適當的增大。如果這個值自動增加，會在error log有中顯示的。
查看參數配置：
show global variables like ‘%innodb_add%‘
分配原則：
用show innodb status"G;去查看運行中的DB是什麼狀態（參考BUFFER POOL AND MEMORY段中），然後可以調整到適當的值。
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4668764894; in additional pool allocated 16777216
參考：in additional pool allocated 16777216
根據你的參數情況，可以適當的調整。
設定方法：
innodb_additional_mem_pool=16M

參數：innodb_max_dirty_pages_pct
作用：控制Innodb的髒頁在緩衝中在那個百分比之下，值在範圍1-100,預設為90.
這個參數的另一個用處：當Innodb的記憶體配置過大，致使swap佔用嚴重時，可以適當的減小調整這個值，使達到swap空間釋放出來。
建義：這個值最大在90％，最小在15％。太大，緩衝中每次更新需要致換資料頁太多，太小，放的資料頁太小，更新操作太慢。
查看擦數設定大小： show global variables like ‘%pct%‘
設定方法：
innodb_max_dirty_pages_pct＝90
動態更改需要有super許可權：
set global innodb_max_dirty_pages_pct=50;
7.關於日誌：
參數：innodb_log_file_size
作用：指定日誌的大小
分配原則：幾個日值成員大小加起來差不多和你的innodb_buffer_pool_size相等。上限為每個日值上限大小為4G.一般控制在幾個LOG檔案相加大小在２Ｇ以內為佳。
具體情況還需要看你的事務大小，資料大小為依據。
說明：這個值分配的大小和資料庫的寫入速度，事務大小，異常重啟後的恢複有很大的關係。
設定方法：
innodb_log_file_size=256M

參數：innodb_log_files_in_group
作用：指定你有幾個日值組。
分配原則：　一般我們可以用２－３個日值組。預設為兩個。
設定方法：
innodb_log_files_in_group=3

參數：innodb_log_buffer_size：
作用：事務在記憶體中的緩衝。
分配原則：控制在2-8M.這個值不用太多的。他裡面的記憶體一般一秒鐘寫到磁碟一次。具體寫入方式和你的事務提交方式有關。一般最大指定為３Ｍ比較合適。
參考：Innodb_os_log_written(show global status 可以拿到，如果這個值增長過快，可以適當的增加innodb_log_buffer_size
另外如果需要處理大量的text，或是blob欄位，可以考慮增加這個參數的值。
設定方法：innodb_log_buffer_size=3M
參數：innodb_flush_logs_at_trx_commit
作用：控制事務的提交方式
分配原則：這個參數只有３個值，0，１，２請確認一下自已能接受的層級。預設為１，主庫請不要更改了。
效能更高的可以設定為０或是２，但會丟失一秒鐘的事務。
說明：
這個參數的設定對innodb的效能有很大的影響，所以在這裡給多說明一下。
當這個值為1時：innodb 的事務LOG在每次提交後寫入日值檔案，並對日值做重新整理到磁碟。這個可以做到不丟任何一個事務。
當這個值為2時：在每個提交，日誌緩衝被寫到檔案，但不對記錄檔做到磁碟操作的重新整理,在對記錄檔的重新整理在值為2的情況也每秒發生一次。
但需要注意的是，由於進程調用方面的問題，並不能保證每秒１００％的發生。從而在效能上是最快的。但作業系統崩潰或掉電才會刪除最後一秒的事務。
當這個值為0時：日誌緩衝每秒一次地被寫到記錄檔，並且對記錄檔做到磁碟操作的重新整理，但是在一個事務提交不做任何操作。
mysqld進程的崩潰會刪除崩潰前最後一秒的事務。

從以上分析，當這個值不為１時，可以取得較好的效能，但遇到異常會有損失，所以需要根據自已的情況去衡量。
設定方法：
innodb_flush_logs_at_trx_commit=1

 

mysql innodb儲存引擎介紹