MySQL資料庫存儲引擎概述

7.表類型 (存儲引擎)的選擇

7.1 MySQL 存儲引擎概述

默認的存儲引擎

創建新表不指定表的存儲引擎,則新表是默認存儲引擎的

修改默認存儲引擎:

可修改參數文件中設置 default_table_type

查看當前的默認存儲引擎,可以使用以下命令:

mysql> show variables like 'table_type';

+—————+——–+

| Variable_name | Value |

+—————+——–+

| table_type | MyISAM |

查看當前資料庫支持的引擎

方法一:

mysql> SHOW ENGINES \G

*************************** 1. row ***************************

Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

Transactions: NO

XA: NO

Savepoints: NO

*************************** 2. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

…….

第二種方法:

mysql> SHOW VARIABLES LIKE 'have%';

+—————————-+——-+

| Variable_name | Value |

Linux公社 www.linuxidc.com

109

+—————————-+——-+

| have_archive | NO |

| have_bdb | NO |

| have_blackhole_engine | NO |

| have_compress | YES |

| have_crypt | YES |

| have_csv | YES |

| have_dlopen | YES |

| have_example_engine | NO |

……….

,其中 Value 顯示為“DISABLED”的記錄表示支持該存儲引擎,但是資料庫啟動的時候被禁用。

創建表時設置引擎

在創建新表的時候,可以通過增加 ENGINE 關鍵字設置新建表的存儲引擎

CREATE TABLE ai (

i bigint(20) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (i)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

修改已有表引擎

使用 ALTER TABLE 語句,將一個已經存在的表修改成其他的存儲引擎

mysql> alter table ai engine = innodb;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table ai \G

*************************** 1. row ***************************

Table: ai

Create Table: CREATE TABLE `ai` (

`i` bigint(20) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`i`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

7.2 各種存儲引擎的特性

MyISAM

存儲限制:有

事務安全:不支持

鎖機制:表鎖

B樹索引:支持

哈希索引:不支持

全文索引:支持

集群索引:不支持

數據緩存:不支持

索引緩存:支持

數據可壓縮:支持

空間使用:低

內存使用:低

批量插入數據的速度:高

支持外鍵:不支持

特點:

MyISAM 不支持事務,不支持外鍵。

訪問速度快,對事務完整性沒有要求,或者以INSERT 或者SELECT 為主的應用可以使用該引擎。

每個MyISAM 存儲三個文件:

.frm(存儲表設定)

.MYD(MYDate, 存儲數據)

.MYI(MYIndex,存儲索引)

數據文件和索引文件可以放置在不同的目錄,平均分佈IO,獲得更快的速度。

指定數據文件和索引文件的目錄,需要在創建表時,可以通過DATA_DIRECTORY和INDEX_DIRECTORY來設定。文件路徑需要時絕對路徑,並有訪問權限。

MyISAM表可能損壞,可以通過 CHECK TABLE檢查表健康狀態,REPIRE TATBLE 修復,詳細步驟31章

MyISAM 支持的三種不同存儲格式

靜態(固定長度)表

動態表

壓縮表

靜態表是默認存儲格式,靜態表中的字段都是非變長度字段,這樣每個記錄都是固定長度。

優點: 存儲迅速,容易緩存,出現故障容易恢復。

缺點:占用空間比動態表多。

靜態表存儲時會按照列的寬度補足空格,應用訪問是並不會得到這些空格,這些空格在返回應用前已經去掉。當存儲時尾部真的需要空格時 也會被去掉。

動態表中包含變長字段,記錄不是固定長度的。

占用空間相對較少,但是頻繁的更新個刪除記錄會產生碎片,需要定期執行OPTIMIZE TABLE 或者 myisamchk -r命令來改善性能。並且在出現故障時恢復相對較困難。

壓縮表由myisampack 工具創建,占據非常小的空間,因為每條記錄是單獨壓縮的,所以有非常小的訪問開支。

InnoDB

存儲限制:64TB

事務安全:支持

鎖機制:行鎖

B樹索引:支持

哈希索引:NO

全文索引:NO

集群索引:支持

數據緩存:支持

索引緩存:支持

數據可壓縮:NO

空間使用:高

內存使用:高

批量插入數據的速度:低

支持外鍵:支持

特點 :

具有提交、回滾、和崩潰恢復能力的事務安全。

但是相對於MyISAM ,InnoDB寫的效率差一些,並且會占用更多的磁盤空間以保存數據和索引。

自動增長列:

InnoDB的自動增長列可以手工插入,但是插入的值如果是0或者空時 將自動增長。可以通過ALTER TABLE tablename AUTO_INCREMENT = n 來設置自動增長的初始值。但是該值是保留在內存中的。如果重新啟動 資料庫,該值將丟失。

可以使用LAST_INSERT_ID(),來查詢當前線程最後插入使用的值。如果插入多條,則返回第一條記錄使用的值。

對於InnoDB的自增列必須是索引,如果是組合索引,必須是組合索引的第一列。但是對於MyISAM表,自動增長列可以是組合索引的其他列,這樣插入記錄後,自動增長列是按照前幾列進行排序後自增的。

外鍵約束

隻有InnoDB支持外鍵約束。創建外鍵時,父表必須有對應的索引,字表在創建外建時也會創建對應的索引

修改:

ALTER TABLE tablename ADD CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 關聯表名(關聯字段名);

創建:

CREATE TABEL table_name (

…..,

CONSTRAINT foreign_keyname FORENTIN KEY(col_name) REFERENCES 關聯表名(關聯字段名)

) ENGINE = InnoDB;

創建外鍵時,可以指定在刪除 更新 父表時對子表的操作。

RESTRICT

NO ACTION

CASCADE

SET NULL

例子:

CREATE TABLE city (

city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

city VARCHAR(50) NOT NULL,

country_id SMALLINT UNSIGNED NOT NULL,

last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (city_id),

KEY idx_fk_country_id (country_id),

CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON

DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

在導入多表數據時,可以暫時關閉外鍵檢查 SET FOREIGN_KEY_CHECK = 0 :導入完成再開啟 SET FOREIGN_KEY_CHECK = 1;

存儲方式

使用共享表空間存儲

使用多表空間存儲

MEMEORY

存儲限制:有

事務安全:NO

鎖機制:表鎖

B樹索引:支持

哈希索引:支持

全文索引:NO

集群索引:NO

數據緩存:支持

索引緩存:支持

數據可壓縮:NO

空間使用:N/A

內存使用:中等

批量插入數據的速度:高

支持外鍵:NO

特點:memory 存儲引擎使用存在於內存中的內容來創建表,每個memory實際隻對應一個磁盤文件,格式是.frm。 memory 表訪問速度非常快,因為他數據存儲在內存中的,並默認使用HASH索引。 但是服務一旦關閉 表中的數據就會丟失。

創建索引的時候可以指定使用HASH還是BTREE:

CREATE INDEX mem_hash USING HASH ON tablename(col_name)

持久數據:

在啟動mysql時使用 –init-file選項,把INSERT INTO ….SELECT 或者LOAD DATA INFILE 這樣的語句放入文件中,這樣就可以將持久的數據載入表。

數據表的大小:

每個memory 表可以放置的數據大小,收到max_heap_table_size限制,系統默認是16Mb,可以根據需要加大。此外設定表的時候可以設置MAX_ROWS子句指定表的最大長度。

適用環境:

memory主要適用那些內容變化不頻繁,或者作為那麼統計操作的中間結果表。

要註意數據並沒有寫入硬盤,重啟時數據會丟失。

MERGE [m?:rd?]

存儲限制:沒有

事務安全:NO

鎖機制:表鎖

B樹索引:支持

哈希索引:NO

全文索引:NO

集群索引:NO

數據緩存:NO

索引緩存:支持

數據可壓縮:NO

空間使用:低

內存使用:低

批量插入數據的速度:高

支持外鍵:NO

是什麼

MERGE 存儲引擎是一組MyISAM表的組合,這些MyISAM表的結構必須完全相同,MERGE表本身並沒有數據,對MERGE標的可以進行查詢、更新、刪除操作,這些操作實際是對MyISAM標的操作。

設定MERGE表

語法

CRATE TABLE tablename (

….

)ENGINE = MERGE UNION(tablename1,tablename2, ,,) INSERT_METHOD=LAST

范例

(1)創建 3 個測試表 payment_2006、payment_2007 和 payment_all,其中 payment_all是前兩個表的 MERGE 表:

mysql> create table payment_2006(

-> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> KEY idx_fk_country_id (country_id)

-> )engine=myisam;

Query OK, 0 rows affected (0.03 sec)

mysql> create table payment_2007(

-> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> KEY idx_fk_country_id (country_id)

-> )engine=myisam;

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE payment_all(

-> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> INDEX(country_id)

-> )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

Query OK, 0 rows affected (0.04 sec)

插入操作:

對MERGE表進行插入操作,是通過INSERT_METHOD 設定插入的表,有三個值:LAST 插入到最後一張表,FIRST 插入到第一張表,NO 不允許插入

NDB

存儲限制:有

事務安全:NO

鎖機制:表鎖

B樹索引:支持

哈希索引:支持

全文索引:NO

集群索引:NO

數據緩存:支持

索引緩存:支持

數據可壓縮:NO

空間使用:N/A

內存使用:中等

批量插入數據的速度:高

支持外鍵:NO

第三方存儲引擎: TokuDB

特點:高寫 高壓縮性能,

7.3 如何選擇合適的存儲引擎

MyISAM:

如果應用是讀和插入操作為主,隻有很少的更新和刪除操作。對事務的完整性和並發要求不高,MyISAM 非常合適

InnoDB

用於事務處理程式,支持外鍵。如果應用對事務完整性有較高的要求,在並發的條件下要保證數據的一致性,數據除瞭查詢和插入操作外還有很多的更新個刪除操作,那麼InnoDB比較合適,

MEMORY

將所有的數據保存在RAM中,在需要快速定位數據和其他類似的數據環境下,可提供快速訪問,

缺陷是對表的大小有限制,太大的表無法緩存在內存中,其次要確保表的可恢復。

用戶更新不頻繁的小表,用戶快速得到訪問結果。

MERGE:

用於將一系列等同的表邏輯的組合在一起,

MERGE表的優點在於可以突破單個MyISAM的大小限制,並通過將不同的表分佈在不同的磁盤上,可以有效的改善MERGE表的訪問效率。

對於數據倉庫VLDB非常合適。

You May Also Like