Linux營運學習筆記之MySQL儲存引擎介紹，學習筆記mysql

Linux營運學習筆記之MySQL儲存引擎介紹，學習筆記mysql

第三十五章 MySQL儲存引擎

一、MySQL常用引擎

MyISAM、InnoDB、HEAP(In-Memory)和NDB(clustered)

二、MySQL外掛程式式儲存引擎的體繫結構

三、MySQL最常用的兩種引擎：MyISAM和InnoDB

1、MyISAM：表級鎖，不支援事務。強調的是效能，其執行數度比InnoDB類型更快。儲存表的具體行數

2、InnoDB：行級鎖，InnoDB支援事務，以及外部鍵等進階資料庫功能。不儲存表的具體行數

3、MySQL5.5.1(含)之前為預設使用的是MyISAM引擎，MySQL5.1之後預設使用的是InnoDB引擎。

四、MyISAM引擎

1、簡介

MyISAM是MySQL5.1(含)之前預設儲存引擎。它基於更老的ISAM代碼，但有很多有用的擴充。

每個MyISAM在磁碟上儲存成三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案類型。MySQL的系統庫表基本上都是MyISAM引擎的檔案：

ls -l /data/3306/data/mysql

-rw-rw---- 1mysql mysql 8820 Mar 28 13:36columns_priv.frm

-rw-rw---- 1mysql mysql 0 Mar 28 13:36 columns_priv.MYD

-rw-rw---- 1mysql mysql 4096 Mar 28 13:36columns_priv.MYI

...

-rw-rw---- 1mysql mysql 10630 Mar 28 13:36 user.frm

-rw-rw---- 1mysql mysql 456 Apr 17 13:22 user.MYD

-rw-rw---- 1mysql mysql 2048 Apr 17 13:24 user.MYI

.frm檔案儲存體表定義。

資料檔案的副檔名為.MYD (MYData)。

索引檔案的副檔名是.MYI (MYIndex)。

要明確表示你想要用一個MyISAM表格，請用ENGINE表選項指出來：CREATE TABLE t (i INT) ENGINE =MYISAM;一般地，ENGINE選項是不必要的;除非預設已經被改變了，MyISAM是預設儲存引擎。

2、通過命令查看MyISAM引擎檔案類型

file user.frm

user.frm: MySQLtable definition file Version 9

file user.MYD

user.MYD:Hitachi SH big-endian COFF executable, not stripped

file user.MYI

user.MYI: MySQLMISAM compressed data file Version 1

3、MyISAM引擎特點

(1)不支援事務

(2)表級鎖定(更新時鎖整個表)

(3)讀寫互相阻塞，但讀讀不會阻塞

(4)只會緩衝索引，不會快取資料

(5)讀取速度較快，佔用資源相對少

(6)不支援外鍵約束，但支付全文索引

4、MyISAM引擎適用的生產情境

(1)不需要事務支援，對資料一致性不高的業務

(2)一般為讀多的應用，讀寫都頻繁的不適合，但讀多或寫多的都適合

(3)讀寫並發訪問相對較低的業務(純讀純寫高並發也可以)(鎖定機制問題)

(4)資料修改相對較少的業務(阻塞問題)

(5)中小型網站的部分業務會用

5、MyISAM引擎調優精要

(1)設定合適的索引(緩衝機制)

(2)調整讀寫優先順序，根據實際需求確保重要操作更優先執行

(3)啟用延遲插入，改善大批量寫入效能(降低寫入頻率，儘可能多條資料一次性寫入)

(4)盡量順序操作讓Insert資料都寫在尾部，減少阻塞

(5)分解大的，時間長的操作，降低單個操作的阻塞時間

(6)降低並發數(減少對MySQL的訪問)，某些高並發情境通過應用進行排隊的隊列機制

(7)對於相對靜態(更改不頻繁)的資料庫資料，充分利用Query Cache或Memcached快取服務可以極大的提高訪問效率。

grep query my.cnf

query_cache_size = 256M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

(8)MyISAM的Count只在在全表掃描的時候特別高效，帶有其它條件的Count都需要進行實際的資料訪問

(9)可以把主從同步的主庫使用InnoDB引擎，從庫使用MyISAM引擎(不推薦)

五、InnoDB引擎

1、簡介

InnoDB給MySQL提供了具有提交，復原和崩潰恢複能力的事務安全(ACID相容)儲存引擎。InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多使用者部署和效能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支援FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。

InnoDB是為處理巨大資料量時的最大效能設計。它的CPU效率可能是任何其它基於磁碟的關聯式資料庫引擎所不能匹敵的。

InnoDB儲存引擎被完全與MySQL伺服器整合，InnoDB儲存引擎為在主記憶體中快取資料和索引而維持它自己的緩衝池。被InnoDB儲存引擎管理的兩個重要的基於磁碟的資源是InnoDB資料表空間資料檔案和它的記錄檔。InnoDB儲存它的表&索引在一個資料表空間中，如果你指定無InnoDB配置選項，MySQL將在MySQL資料目錄下建立一個名為ibdata1的10MB大小的自動擴充資料檔案，以及兩個名為ib_logfile0和ib_logfile1的5MB大小的記錄檔。

資料表空間可以包含數個檔案(或原始磁碟分割)。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的檔案中。InnoDB 表可以是任何尺寸，即使在檔案尺寸被限制為2GB的作業系統上。

2、InnoDB引擎特點

(1)支援事務

(2)行級鎖定(更新時鎖當前行)，但全表掃描時，仍會是表鎖，要注意間隙鎖的影響

(3)讀寫阻塞與交易隔離等級相關

(4)緩衝索引和資料

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

(6)支援外鍵約束，5.5以前不支付全文索引，5.5以後就支援了

(7)所有Secondary Index都會儲存主鍵資訊

(8)支援分區、資料表空間，類似Oracle

(9)對硬體資源要求比較高。

3、InnoDB引擎適用的生產情境

(1)需要事務支援，對資料一致性較高的業務

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

(3)讀寫及更新較為頻繁的情境：BBS、SNS、微博、等

(4)硬體設定記憶體較大，可以利用InnoDB較好的緩衝能力來提高記憶體利用率，儘可能減少磁碟IO

grep innodb my.cnf

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size= 2048M #緩衝，官方建議為總記憶體的50-80%

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size= 16M

innodb_log_file_size= 128M

innodb_log_files_in_group= 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0 #為1時，每個表一個資料表空間檔案

4、InnoDB引擎調優精要

(1)主鍵儘可能小，避免給Secondary Index帶來過大的空間負擔

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

(3)儘可能緩衝所有索引和資料，提高響應速度，減少磁碟IO

(4)在大批量小插入時，盡量自已控制事務，不要使用autocommit來自動認可。

(5)合理設定innodb_flush_log_at_trx_commit參數值，不要過度追求安全性，推薦使用值為2。

如果innodb_flush_log_at_trx_commit = 0，則log buffer會每秒都刷寫記錄檔到磁碟，提交事務的時候不做任何操作，效能最好，安全性最差。當系統宕機時，會丟失一秒的資料。

innodb_flush_log_at_trx_commit= 0：每個事務提交時，每隔一秒，把交易記錄從緩衝區寫到記錄檔中，並把記錄檔的資料重新整理到磁碟上。即使伺服器沒有宕機，只是MySQL服務掛了，也可能會遺失資料。

innodb_flush_log_at_trx_commit= 1：每個事務提交時，把交易記錄從緩衝區寫到記錄檔中，並把記錄檔的資料重新整理到磁碟上。

innodb_flush_log_at_trx_commit= 2：每個事務提交時，把交易記錄從緩衝區寫到記錄檔中，每隔一秒，重新整理一次記錄檔，但不一定重新整理到磁碟上。只有當系統宕機時，才有可能丟失的資料。(推薦使用)

(6)避免主鍵更新，因為會帶來大量的資料移動

六、InnoDB和MyISAM的差別

1、InnoDB不支援FULLTEXT全文類型的索引。

2、InnoDB 中不儲存表的具體行數，也就是說，執行select count(*) fromtable時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出儲存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。

3、對於AUTO_INCREMENT類型的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他欄位一起建立聯合索引。

4、DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的刪除。

5、LOAD TABLE FROMMASTER操作對InnoDB是不起作用的，解決方案是首先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用的額外的InnoDB特性(例如外鍵)的表不適用。

6、另外，InnoDB表的行鎖也不是絕對的，假如在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如updatetable set num=1 where name like “%aaa%”

7、小結

兩種類型最主要的差別就是Innodb 支援交易處理與外鍵和行級鎖。而MyISAM不支援.所以MyISAM往往就容易被人認為只適合在小項目中使用。

七、MyISAM的優勢(網上文章，不一定)

作為使用MySQL的使用者角度出發，Innodb和MyISAM都是比較喜歡的，如果資料庫平台要達到需求：99.9%的穩定性，方便的擴充性和高可用性來說的話，MyISAM絕對是首選。原因如下：

1、平台上承載的大部分項目是讀多寫少的項目，而MyISAM的讀效能是比Innodb強不少的。

2、MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用量率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。

3、經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的範圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的檔案，隨便放到一個資料庫目錄下，然後dump成sql再導回到主庫，並把對應的binlog補上。如果是Innodb，恐怕不可能有這麼快速度，別和我說讓Innodb定期用匯出xxx.sql機製備份，因為最小的一個資料庫執行個體的資料量基本都是幾十G大小。

4、從接觸的應用邏輯來說，selectcount(*) 和order by 是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。

5、還有就是經常有很多應用部門需要我給他們定期某些表的資料，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的檔案，讓他們自己在對應版本的資料庫啟動就行，而Innodb就需要匯出xxx.sql了，因為光給別人檔案，受字典資料檔案的影響，對方是無法使用的。

6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫效能，如果是針對基於索引的update操作，雖然MyISAM可能會遜色Innodb,但是那麼高並發的寫，從庫能否追的上也是一個問題，還不如通過多執行個體分庫分表架構來解決。

7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些selectcount(*)操作，非常適合大項目總量約幾億的rows某一類型(如日誌，調查統計)的業務表。

當然Innodb也不是絕對不用，用事務的項目就用Innodb的。另外，可能有人會說你MyISAM無法抗太多寫操作，但是可以通過架構來彌補。

八、批量修改MySQL服務引擎

1、單個修改：SQL語句修改

(1)修改命令

alter table table_name Engine = InnoDB;

alter table table_name Engine = MyISAM;

(2)樣本

a、查看student表的儲存引擎

show create table student;

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

|Table |Create T able |

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

| student |CREATE TABLE `student` (

`id` int(2) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL

) ENGINE=InnoDBDEFAULT CHARSET=latin1 |

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

b、修改引擎

alter table student engine = MyISAM;

c、查看student表的儲存引擎

show create table student;

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

|Table |Create T able |

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

| student |CREATE TABLE `student` (

`id` int(2) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL

) ENGINE=MyISAMDEFAULT CHARSET=latin1 |

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

2、批量修改

(1)方法一：使用for迴圈

(2)方法二：使用sed對備份內容進行引擎轉換(資料量大的時候效果不好)

sed -e 's#InnoDB#MyISAM#g' bak_InnoDB.sql > bak_MyISAM.sql

mysql -uroot -p

(3)方法三：使用mysql_convert_table_format進行引擎轉換

a、查看命令

which mysql_convert_table_format

/application/mysql/bin/mysql_convert_table_format

b、改指定表

mysql_convert_table_format --user=root--password='123456' --socket=/data/3306/mysql.sock --engine=InnoDB test student

c、批量改庫中所有表

mysql_convert_table_format--user=root --password='123456' --socket=/data/3306/mysql.sock --engine=InnoDBtest