# 存儲引擎特性 ## 1. 常見存儲引擎 面重點介紹幾種常用的存儲引擎， 并對比各個存儲引擎之間的區別， 如下表所示 ： | 特點 | InnoDB | MyISAM | MEMORY | MERGE | NDB | | ------------ | -------------------- | -------- | ------ | ----- | ---- | | 存儲限制 | 64TB | 有 | 有 | 沒有 | 有 | | 事務安全 | ==支持== | | | | | | 鎖機制 | ==行鎖(適合高并發)== | ==表鎖== | 表鎖 | 表鎖 | 行鎖 | | B樹索引 | 支持 | 支持 | 支持 | 支持 | 支持 | | 哈希索引 | | | 支持 | | | | 全文索引 | 支持(5.6版本之后) | 支持 | | | | | 集群索引 | 支持 | | | | | | 數據索引 | 支持 | | 支持 | | 支持 | | 索引緩存 | 支持 | 支持 | 支持 | 支持 | 支持 | | 數據可壓縮 | | 支持 | | | | | 空間使用 | 高 | 低 | N/A | 低 | 低 | | 內存使用 | 高 | 低 | 中等 | 低 | 高 | | 批量插入速度 | 低 | 高 | 高 | 高 | 高 | | 支持外鍵 | ==支持== | | | | | 下面我們將重點介紹最長使用的兩種存儲引擎： InnoDB、MyISAM ， 另外兩種 MEMORY、MERGE ， 了解即可。 ## 2. InnoDB InnoDB存儲引擎是Mysql的默認存儲引擎。InnoDB存儲引擎提供了具有提交、回滾、崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數據和索引。 InnoDB存儲引擎不同于其他存儲引擎的特點 ： ### 2.1事務控制 ~~~ mysql> create table goods_innodb( -> id int NOT NULL AUTO_INCREMENT, -> name varchar(20) NOT NULL, -> primary key(id) -> )ENGINE=innodb DEFAULT CHARSET=utf8; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into goods_innodb(id,name)values(null,'iphone 20'); Query OK, 1 row affected (0.00 sec) mysql> select * from goods_innodb; +----+-----------+ | id | name | +----+-----------+ | 1 | iphone 20 | +----+-----------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) ~~~ 測試發現在InnoDB中是存在事務的 ### 2.2 外鍵約束 MySQL支持外鍵的存儲引擎只有InnoDB ， 在創建外鍵的時候， 要求父表必須有對應的索引 ， 子表在創建外鍵的時候， 也會自動的創建對應的索引。 ? 下面兩張表中 ， country_innodb是父表 ， country_id為主鍵索引，city_innodb表是子表，country_id字段為外鍵，對應于country_innodb表的主鍵country_id 。 ```sql create table country_innodb( country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table city_innodb( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan'); insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1); ``` 在創建索引時， 可以指定在刪除、更新父表時，對子表進行的相應操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。 RESTRICT和NO ACTION相同， 是指限制在子表有關聯記錄的情況下， 父表不能更新； CASCADE表示父表在更新或者刪除時，更新或者刪除子表對應的記錄； SET NULL 則表示父表在更新或者刪除的時候，子表的對應字段被SET NULL 。 針對上面創建的兩個表， 子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表刪除記錄的時候， 如果子表有對應記錄， 則不允許刪除， 主表在更新記錄的時候， 如果子表有對應記錄， 則子表對應更新 。 表中數據如下圖所示 ： ~~~ mysql> select * from country_innodb; +------------+--------------+ | country_id | country_name | +------------+--------------+ | 1 | China | | 2 | America | | 3 | Japan | +------------+--------------+ 3 rows in set (0.00 sec) mysql> select * from city_innodb; +---------+-----------+------------+ | city_id | city_name | country_id | +---------+-----------+------------+ | 1 | Xian | 1 | | 2 | NewYork | 2 | | 3 | BeiJing | 1 | +---------+-----------+------------+ 3 rows in set (0.00 sec) #刪除主表數據時，如果關聯表中有關聯數據，則不能被刪除 mysql> delete from country_innodb where country_id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo_01`.`city_innodb`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE) #更新主表數據時，如果關聯表中有關聯數據，則數據也會被更新 mysql> update country_innodb set country_id =10 where country_id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from city_innodb; +---------+-----------+------------+ | city_id | city_name | country_id | +---------+-----------+------------+ | 1 | Xian | 10 | | 2 | NewYork | 2 | | 3 | BeiJing | 10 | +---------+-----------+------------+ 3 rows in set (0.00 sec) ~~~ ### 2.3 存儲位置 InnoDB 存儲表和索引有以下兩種方式 ： ①. 使用共享表空間存儲， 這種方式創建的表的表結構保存在.frm文件中， 數據和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個文件。 ②. 使用多表空間存儲， 這種方式創建的表的表結構仍然存在 .frm 文件中，但是每個表的數據和索引單獨保存在 .ibd 中。 默認存儲位置在Mysql/data/’數據庫‘ 下 ~~~ ? mysql-8.0.14-macos10.14-x86_64 cd data ? data ls #innodb_temp ca-key.pem ib_logfile0 mysqld.local.err server-key.pem auto.cnf ca.pem ib_logfile1 mysqld.local.pid sys binlog.000001 client-cert.pem ibdata1 performance_schema undo_001 binlog.000002 client-key.pem ibtmp1 private_key.pem undo_002 binlog.000003 demo_01 mysql public_key.pem binlog.index ib_buffer_pool mysql.ibd server-cert.pem ? data cd demo_01 ? demo_01 ls city.ibd city_innodb.ibd country.ibd country_innodb.ibd goods_innodb.ibd ~~~ ## 3. MyISAM ? MyISAM 不支持事務、也不支持外鍵，其優勢是訪問的速度快，對事務的完整性沒有要求或者以SELECT、INSERT為主的應用基本上都可以使用這個引擎來創建表 。有以下兩個比較重要的特點： ### 3.1 不支持事務 ~~~ create table goods_myisam( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=myisam DEFAULT CHARSET=utf8; mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into goods_myisam values(null,'diannao'); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select * from goods_myisam; +----+---------+ | id | name | +----+---------+ | 1 | diannao | +----+---------+ 1 row in set (0.00 sec) ~~~ 通過測試，我們發現，在MyISAM存儲引擎中，是沒有事務控制的 ； ### 3.2 文件存儲方式 每個MyISAM在磁盤上存儲成3個文件，其文件名都和表名相同，但拓展名分別是 ： .frm (存儲表定義)； .MYD(MYData , 存儲數據)； .MYI(MYIndex , 存儲索引)； ~~~ ? demo_01 ls city.ibd country.ibd goods_innodb.ibd goods_myisam.MYI city_innodb.ibd country_innodb.ibd goods_myisam.MYD goods_myisam_343.sdi ~~~ ## 4. MEMORY Memory存儲引擎將表的數據存放在內存中。每個MEMORY表實際對應一個磁盤文件，格式是.frm ，該文件中只存儲表的結構，而其數據文件，都是存儲在內存中，這樣有利于數據的快速處理，提高整個表的效率。MEMORY 類型的表訪問非常地快，因為他的數據是存放在內存中的，并且默認使用HASH索引 ， 但是服務一旦關閉，表中的數據就會丟失。 ## 5. c ? MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結構完全相同，MERGE表本身并沒有存儲數據，對MERGE類型的表可以進行查詢、更新、刪除操作，這些操作實際上是對內部的MyISAM表進行的。 ? 對于MERGE類型表的插入操作，是通過INSERT_METHOD子句定義插入的表，可以有3個不同的值，使用FIRST 或 LAST 值使得插入操作被相應地作用在第一或者最后一個表上，不定義這個子句或者定義為NO，表示不能對這個MERGE表執行插入操作。 ? 可以對MERGE表進行DROP操作，但是這個操作只是刪除MERGE表的定義，對內部的表是沒有任何影響的。