mysql引擎fa_Mysql 存儲引擎學習筆記

由於各存儲引擎功能特性差異較大,選擇合適的存儲引擎來存儲數據對業務的優化也是非常重要的.今天對各類存儲引擎學習學習.

MYSQL存儲引擎類型:MYISAM,INNODB,MEMORY,ARCHIVE,CSV,KERKELEY DB,MERGE,Cluster/NDB等.

MyISAM 存儲引擎:

特性

不支持事務：MyISAM存儲引擎不支持事務，所以對事務有要求的業務場景不能使用

表級鎖定：其鎖定機制是表級索引，這雖然可以讓鎖定的實現成本很小但是也同時大大降低了其並發性能

讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身並不會阻塞另外的讀

只會緩存索引：MyISAM可以通過key_buffer緩存以大大提高訪問性能減少磁盤IO，但是這個緩存區只會緩存索引，而不會緩存數據

適用場景

不需要事務支持(不支持)

並發相對較低(鎖定機制問題)

數據修改相對較少(阻塞問題)

以讀為主

數據一致性要求不是非常高

最佳實踐

盡量索引(緩存機制)

調整讀寫優先級，根據實際需求確保重要操作更優先

啟用延遲插入改善大批量寫入性能

盡量順序操作讓insert數據都寫入到尾部，減少阻塞

分解大的操作，降低單個操作的阻塞時間

降低並發數，某些高並發場景通過應用來進行排隊機制

對於相對靜態的數據，充分利用Query Cache可以極大的提高訪問效率

MyISAM的Count只有在全表掃描的時候特別高效，帶有其他條件的count都需要進行實際的數據訪問

總結: MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。如果應用中需要執行大量的SELECT查詢，那么MyISAM是更好的選擇。

InnoDB 存儲引擎:

特性

具有較好的事務支持：支持4個事務隔離級別，支持多版本讀

行級鎖定：通過索引實現，全表掃描仍然會是表鎖，注意間隙鎖的影響

讀寫阻塞與事務隔離級別相關

具有非常高效的緩存特性：能緩存索引，也能緩存數據

整個表和主鍵以Cluster方式存儲，組成一顆平衡樹

所有Secondary Index都會保存主鍵信息

適用場景

需要事務支持(具有較好的事務特性)

行級鎖定對高並發有很好的適應能力，但需要確保查詢是通過索引完成

數據更新較為頻繁的場景

數據一致性要求較高

硬件設備內存較大，可以利用InnoDB較好的緩存能力來提高內存利用率，盡可能減少磁盤 IO

最佳實踐

主鍵盡可能小，避免給Secondary index帶來過大的空間負擔

避免全表掃描，因為會使用表鎖

盡可能緩存所有的索引和數據，提高響應速度

在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動提交

合理設置innodb_flush_log_at_trx_commit參數值，不要過度追求安全性

避免主鍵更新，因為這會帶來大量的數據移動

總結:InnoDB用於事務處理應用程序，具有眾多特性，包括ACID事務支持。如果應用中需要執行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多用戶並發操作的性能。

NDBCluster  存儲引擎:

特性

分布式：分布式存儲引擎，可以由多個NDBCluster存儲引擎組成集群分別存放整體數據的一部分

支持事務：和Innodb一樣，支持事務

可與mysqld不在一台主機：可以和mysqld分開存在於獨立的主機上，然后通過網絡和mysqld通信交互

內存需求量巨大：新版本索引以及被索引的數據必須存放在內存中，老版本所有數據和索引必須存在與內存中

適用場景

具有非常高的並發需求

對單個請求的響應並不是非常的critical

查詢簡單，過濾條件較為固定，每次請求數據量較少，又不希望自己進行水平Sharding

最佳實踐

盡可能讓查詢簡單，避免數據的跨節點傳輸

盡可能滿足SQL節點的計算性能，大一點的集群SQL節點會明顯多余Data節點

在各節點之間盡可能使用萬兆網絡環境互聯，以減少數據在網絡層傳輸過程中的延時

日常使用當中主要是以上存儲引擎了.

下面是關於存儲引擎的一些基本操作

查看系統存儲引擎:

mysql> show engines;

默認的存儲引擎:

mysql> show variables like '%storage_engine%';

你要看某個表使用什么引擎(在顯示結果里參數engine后面的就表示該表當前用的存儲引擎):

mysql> show create table 表名;

其中DEFAULT表明系統的默認存儲引擎，可以通過修改配置參數來變更：

default-storage-engine=MyISAM

查看某個存儲引擎的使用狀態信息:

mysql> show engine InnoDB status\G;

指定系統默認存儲引擎為INNODB

default-storage-engine=INNODB

1.可以在啟動數據庫服務器時在命令行后面加上--default-storage-engine或--default-table-type選項。

2.更靈活的方式是在隨MySQL服務器發布同時提供的MySQL客戶端時指定使用的存儲引擎。最直接的方式是在創建表時指定存儲引擎的類型，向下面這樣:

CREATE TABLE mytable (id int, titlechar(20)) ENGINE = INNODB

修改表的存儲引擎方法：

1> 直接修改的方式: ALTER TABLE engineTest ENGINE = INNODB；

2> 間接修改的方法: 修改tablename 表的存儲引擎為 INNODB;

例: 1.use databasename;show create table tablename; 查看建表SQL;

2.通過建表SQL,創建新的表名:  tablename_bak; //對查詢出的建表SQL 做簡單修改,表名與存儲引擎.

3.insert into tablename_bak select * from tablename;  //將數據插入新表,如果在線寫數據表比較大,可以指定id;

4.alter table tablename rename tablename_date;alter table tablename_bak rename tablename;  //備份表與在線表進行切換;

注意: 3,4步驟需要連貫操作,以免出現數據丟失.

由於修改表的存儲引擎會進行表鎖,對大表的存儲引擎切換使用 "間接修改"的方法,可以減少鎖表時間.

修改數據庫默認存儲引擎：

在mysql配置文件(linux下為/etc/my.cnf)，在mysqld后面增加default-storage-engine=INNODB即可。

但是如果表建立的時候是MyISAM，要更改整個數據庫表的存儲引擎，一般要一個表一個表的修改，比較繁瑣，可以采用先把數據庫導出，得到SQL，把MyISAM修改成INNODB，再導入的方式

批量修改導出的數據存儲引擎: sed -i s/=MyISAM/=INNODB/g  mysqldump_database;

將進行大量表的引擎轉換時,注意需要提前對轉換后的存儲引擎進行優化.

Innodb 存儲引擎使用可以選擇使用獨立表空間與 共享表空間存儲數據.

設置獨立表空間存儲數據:

innodb_file_per_table  = 1   //這是一個動態參數,可以進行全局設置,不需要重啟數據庫.

查看當獨立表空間設置情況:

mysql> show variables like '%per_table';

+-----------------------+-------+

| innodb_file_per_table | OFF   |

設置獨立表空間存儲:

mysql> set global innodb_file_per_table = 1;

檢查操作設置結果:

mysql> show variables like '%per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

注意: 只有新創建的表才會使用獨立表空間存儲,生成 database_name/tablename.ibd 的數據與索引 共同存儲文件.

經測試,獨立表空間下面創建的表,在非獨立表空間下面可以正常讀寫操作.

獨立表空間與共享表空間兩者之間的優缺點

共享表空間：

優點:可以放表空間分成多個文件存放到各個磁盤上(表空間文件大小不受表大小的限制，如一個表可以分布在不同步的文件上)。數據和文件放在一起方便管理。

缺點:所有的數據和索引存放到一個文件中意味着將有一個很常大的文件，雖然可以把一個大文件分成多個小文件，但是多個表及索引在表空間中混合存儲，這樣對於一個表做了大量刪除操作后表空間中將會有大量的空隙.

特別是對於統計分析，日值系統這類應用最不適合用共享表空間。

獨立表空間：

優點:

1．  每個表都有自已獨立的表空間,表的數據和索引都會存在自已的表空間中。

2.   可以實現單表在不同的數據庫中移動。

3．  空間數據回收情況:(除drop table操作處，表空不能自已回收)

Drop table操作自動回收表空間，如果對於統計分析或是日值表，刪除大量數據后可以通過:alter table TableName engine=innodb;回縮不用的空間。

對於使innodb-plugin的Innodb使用turncate table也會使空間收縮。

使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。

缺點:

單表增加過大，如超過100個G。

相比較之下，使用獨占表空間的效率以及性能會更高一點

學習參考:http://isky000.com/database/mysql-performance-tuning-storage-engine