mysql引擎筆記整頓

2024-07-24 12:35:17

字體：大中小

來源：轉載

供稿：網友

　　查看當前的mysql數據庫都支持什么樣的存儲引擎：
　　mysql> show engines;
　　ERROR 2006 (HY000): MySQL server has gone away
　　No connection. Trying to reconnect...
　　Connection id:    170700
　　Current database: *** NONE ***

　　注：
　　1、support的狀態有default，yes，no，disabled。no表示該引擎不可用，disabled表示支持該引擎，但是當前被禁用。
　　2、Transactions表示是否支持事務。
　　3、XA表示是否支持分布式事務。
　　4、Savepoints表示是否支持保存點。

　　mysql中自帶的也比較常見的存儲引擎有：MyISAM，Innodb，NDB Cluster，Memory，Archive，Merge，Federated等。其中，最著名和廣泛的就是MyISAM，Innodb。

　　1) MEMORY引擎
　　MEMORY存儲引擎的表只擁有一個獨立的磁盤文件，擴展名為.frm，用來存儲表結構的定義，不包含數據。
　　MEMORY引擎表的數據保存在內存中，mysql服務端需要單獨為其分配內存區域。分配給它的內存在正常狀態下不會釋放，會一直被持有。除非整個MEMORY表被刪除或重建才會釋放相關內存。
　　如果僅刪除MEMORY引擎表中的數據，釋放的內存會被該表新插入的數據所使用，無法被其他對象或者線程使用。
　　當mysql服務關閉時，MEMORY引擎表中的數據都會丟失，表結構會予以保留。
　　MEMORY引擎表占用的最大內存由參數max_heap_table_size系統變量來決定。該值默認識16M。可以為不同的MEMORY引擎表設置不同的最大內存。但是mysql服務重啟后，所有的MEMORY引擎表的最大內存又會被系統變量的值覆蓋掉。
　　---------設置最大內存為1M；----------
　　set max_heap_table_size=1024*1024;
　　create tabele test1(user varchar(10)) engine=memory;
　　---------設置最大內存為2M；----------
　　set max_heap_table_size=1024*1024*2;
　　create tabele test1(user varchar(10)) engine=memory;
　　除此之外，還可以在創建MEMORY引擎表時指定max_rows項的值來限制MEMORY引擎表所使用的內存。

　　2）csv存儲引擎
　　csv存儲引擎是基于csv格式文件存儲數據的。
　　csv存儲引擎表的所有列必須強制指定NOT NULL，而且csv存儲引擎表不支持索引，不支持分區。
　　csv存儲引擎表包含一個表的結構定義文件，擴展名為.frm，以及一個擴展名為.CSV的數據文件，還有一個同名的元信息文件，擴展名為.CSM，用來保存表狀態和表中保存的數據量。
　　.CSV的數據文件就是CSV格式的平面文本文件，可以用excel打開。
　　如果.CSV文件中的內容被損壞，可以用CHECK TABLE或者REPAIR TABLE命令來進行檢查和恢復。
　　CHECK TABLE命令用于檢查文件中的每一行是否合法，如果又發現不合法的行，就拋出異常。
　　REPAIR TABLE命令用來修復文件，將文件中損壞的數據清除掉。注意它會將出現損壞行之后的所有記錄行都清掉，而不管后面的記錄行是否有合法的數據。

　　3）ARCHIVE存儲引擎
　　基于ARCHIVE存儲引擎的對象，能夠將大量數據壓縮存儲，它使用了zlib無損數據壓縮算法，并且還可以使用OPTIMIZE TABLE分析表使其打包成更小的格式。
　　ARCHIVE存儲引擎的特點就是插入效率高，數據保存時占空間小。
　　ARCHIVE存儲引擎目前僅能支持insert和select語句，不支持update，delete，replace語句。支持order by操作，BLOB列等常規列，可以使用行級鎖定，但不支持索引。
　　ARCHIVE存儲引擎擁有.frm結構定義文件，以及擴展名為.arz的數據文件。在執行優化操作時可能還會出現一個擴展名為.arn的文件

　　4）BLACKHOLE引擎
　　BLACKHOLE引擎雖然可以像其他引擎一樣接受數據，但是數據并不會保存。BLACKHOLE引擎表中永遠為空。但是，該引擎表支持各種索引。
　　盡管BLACKHOLE引擎表中不存放任何數據，但是如果啟用了binlog，那么執行的sql語句實際上是會被記錄的。
　　BLACKHOLE引擎表只有一個.frm格式的文件，用于保存表定義。

　　5）MRG_MYISAM存儲引擎
　　又稱為MERGE存儲引擎，它是將一組MyISAM表聚合在一起，使用時就像一張表一樣，以此來簡化查詢操作。
　　MRG_MYISAM存儲引擎表，要求基表擁有相同的列和索引信息，并且列的定義和順序，索引的順序都必須一模一樣。否則是無法聚合到一起的。
　　MRG_MYISAM存儲引擎表本身并不存儲數據，只是一個匯總作用，但是它除了支持查詢操作外，也支持插入，修改，刪除操作。操作方式和普通的MyISAM表一樣。
　　只是插入的時候，需要一些配置，以指明往哪張MyISAM表中插入數據。這個配置就是創建MRG_MYISAM存儲引擎表時的INSERT_METHOD選項。
　　INSERT_METHOD選項有三個值，分別是NO，FIRST，LAST。NO表示不允許插入，是默認值。FIRST是插入第一張表。LAST是插入最后一張表。

　　6）FEDERATED存儲引擎
　　類似于oracle中的database link。默認mysql安裝時不安裝FEDERATED存儲引擎的，如果需要使用該引擎，在編譯安裝時，添加參數-DWITH_FEDERATED_STORAGE_ENGINE。并且在啟動mysql服務時，
　　附加參數--FEDERATED。或者修改my.cnf配置文件，加上FEDERATED一行，重啟mysql服務。
　　FEDERATED存儲引擎表，只有表結構，其物理數據是來自于遠端的mysql服務器。每個FEDERATED存儲引擎表都包含了兩個元素：一個遠端的mysql數據庫表，一個本地的數據庫表。
　　FEDERATED存儲引擎表在創建時，需要通過CONNECTION選項指定連接信息。CONNECTION選項有兩種寫法：一種通過字符串，一種是通過CREATE SERVER語句。通過CREATE SERVER語句創建的server信息都會被保存在mysql.servers表中。

　　7）MyISAM存儲引擎
　　每個MyISAM表對象都是由3個獨立的文件組成，擴展名分別是.frm，.MYD，.MYI。.frm文件用于存儲表對象的結構，.MYD用于存儲表數據，.MYI用于存儲表的索引信息。
　　在MyISAM引擎表中，不管包含多少個CHAR/VARCHAR類型列，這些列的長度加起來也不能超過65535B，單表字符列最大長度不能超過65532B。
　　MyISAM引擎的主要優點是快，查詢快，寫入快。但是缺點也比較明顯，不支持事務，鎖粒度太粗（表級鎖），在OLTP場景下不合適。
　　在存儲數據時，MyISAM引擎表支持三種存儲格式：FIXED（定長或稱靜態），DYNAMIC（動態），COMPRESSED（壓縮）。
　　靜態格式表就是表中不含變長度的列，比如varchar/varbinary/blob/text等，所定義的每一列保存的均是固定的字節數。或者創建時指定ROW_FORMAT=FIXED。優點：簡單，查找數據最快，崩潰后最好恢復。缺點：費空間。
　　動態格式表就是和靜態格式表相反的，或者創建時指定了ROW_FORMAT=DYNAMIC。除了字符串長度小于4的列以外，其他字符列的長度都是動態的。優點：省空間。缺點：查詢相對較慢，存儲空間碎片會比較多。崩潰后恢復操作比較麻煩。
　　（myisamchk工具：OPTIMIZE TABLE或者myisamchk -r命令用來消除碎片；myisamchk -ei用來查詢表的統計信息；myisamchk -ed命令用來查詢表對象的鏈接數。）
　　壓縮格式表：創建只能用myisampack創建，解壓縮用myisamchk命令。壓縮表只能讀，不能添加或修改記錄。

　　8）InnoDB存儲引擎
　　InnoDB擁有自己獨立的緩存池，對應參數是innodb_buffer_pool_size系統變量，類似于oracle數據庫中的SGA_TARGET，常用數據包括索引都在緩存中。
　　鎖粒度為行級，提供一致性讀。支持事務。支持外鍵約束。支持數據加密。支持查詢、數據、索引緩存。支持備份/恢復到時間點。
　　InnoDB存儲引擎表必須創建主鍵，一般選擇將經常需要查詢的列作為主鍵，如果沒有合適的列，就將主鍵創造到自動增長的列。
　　InnoDB能夠處理多會話并發讀寫同一個對象。
　　InnoDB有兩種表空間方式：系統表空間和多重表空間。
　　InnoDB系統表空間對應哪些物理文件，是由系統變量innodb_data_file_path來設置的：
　　mysql> show variables like "%innodb_data%";
　　+-----------------------+------------------------+
　　| Variable_name         | Value                  |
　　+-----------------------+------------------------+
　　| innodb_data_file_path | ibdata1:12M:autoextend |
　　| innodb_data_home_dir |                        |
　　+-----------------------+------------------------+
　　2 rows in set (0.00 sec)
　　默認情況下，InnoDB數據文件時是存放在mysql的data目錄中，如果想變更目錄，可以通過系統變量innodb_data_home_dir來設置。
　　啟動innodb_file_per_table選項，以啟用多重表空間，使表中數據和索引保存在單獨的文件中，而不是保存在系統表空間中。
　　設置為多重表空間時，truncate存儲引擎為InnoDB的表速度很快，并且釋放的空間能夠被操作系統使用。
　　mysql5.6中，innodb_file_per_table選項默認是為啟動的。
　　mysql> show variables like "%innodb_file%";
　　+--------------------------+----------+
　　| Variable_name            | Value    |
　　+--------------------------+----------+
　　| innodb_file_format       | Antelope |
　　| innodb_file_format_check | ON       |
　　| innodb_file_format_max   | Antelope |
　　| innodb_file_per_table    | ON       |
　　+--------------------------+----------+
　　4 rows in set (0.00 sec)

　　InnoDB除了有表空間概念，還有自己專用的日志文件，即REDOLOG日志。
　　默認情況下，InnoDB引擎會創建兩組大小均為5M的日志文件，分別名為ib_logfile0和ib_logfile1。
　　關于InnoDB引擎日志的系統參數有以下幾個：innodb_log_group_home_dir redo log的保存路徑，默認是在datadir變量指定的路徑下
　　    innodb_log_file_size 每一個redo日志的大小，最大不能超過512G
　　    innodb_log_files_in_group 指定日志文件組的數量，默認是兩個，最多不超過100個
　　調整InnoDB引擎日志比較復雜，因為這幾個參數均是靜態參數，并且調整它們還要依賴參數innodb_fast_shutdown。
　　innodb_fast_shutdown參數用來控制InnoDB的關閉模式，有三個值：
　　    0 0模式類似于oracle的shutdown normal
　　    1 1模式類似于oracle的shutdown immediate，默認值是1
　　    2 2模式類似于oracle的shutdown abort
　　InnoDB引擎因為支持事務，所以一定會需要回滾段。關于InnoDB引擎的回滾段，有以下兩個參數：innodb_undo_directory 指定UNDO日志的物理文件位置
　　     innodb_undo_tablespaces 指定UNDO表空間的數量，每個undo表空間都是獨立的.idb文件
　　     innodb_undo_logs   指定undo表空間中回滾段的數量
　　undo表空間一旦創建，就無法刪除。所以一旦使用了undo表空間，mysql的版本就無法從5.6降下去。

　　默認情況下，連接到MYSQL服務的客戶端處于自動提交模式，即每條DML執行即提交。
　　如何啟動InnoDB引擎的事務支持？有兩種方式：
　　1、禁用事務的自動提交：
　　將autocommit系統變量置為0或者OFF。以后就由用戶來決定是否提交或者回滾事務。
　　autocommit是會話級變量，只針對當前會話，無法全局生效。
　　mysql> show variables like "%autocommit%";
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| autocommit    | ON    |
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　2、顯式聲明事務，通過語句start transaction語句。

　　mysql庫中的表默認均為MYISAM引擎表，不可以更改引擎。
　　對于InnoDB引擎表而言，用show table status返回表的記錄行數，行長度等狀態信息并不精確，只是一個估算值。最好用count函數來進行統計。

　　InnoDB引擎表的邏輯存儲結構從小到大分為頁(pages)/塊，擴展(extend)/區，段(segments)，表空間。
　　頁(pages)/塊：基本單位，默認大小為16K，可以通過參數innodb_page_size變量設置，可選值有4k，8k，16k
　　擴展(extend)/區：每個區固定1M大小，區由頁構成。
　　段(segments)：InnoDB中，段實際上指的是獨立表空間對應的數據文件。
　　表空間：
　　
　　InnoDB引擎內部在保存數據到數據庫時，會自動生成3個內部列：DB_TRX_IDG標記事務的標識符，DB_ROLL_PRT為回滾標記，DB_ROW_ID為行ID
　　回滾段中的UNDO日志分為insert UNDO日志和update UNDO日志。
　　insert UNDO日志：僅在事務回滾時需要，事務提交后即被廢棄
　　update UNDO日志：用于構造一致性讀。
　　回滾段中的UNDO日志記錄大小，一般會小于實際插入或者修改的記錄大小。

　　purge線程：delete記錄時，記錄并不會立即在物理上被刪除，只有當InnoDB廢棄update UNDO日志后，才會從物理上移除關聯的列和索引記錄，這種移除操作稱之為purge。
　　控制purge線程延遲可以用過系統變量innodb_max_purge_lag進行設置。

（編輯：武林網）

上一篇：MySQL的搭設和基本的增刪改運作

下一篇：mysql show 語言