# 存储引擎特性 ## 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表的定义，对内部的表是没有任何影响的。