存储引擎 - InnoDB引擎 - 《MySQL》

特点
- 自动增长列
- 外键约束
存储方式
场景

特点

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，InnoDB写的效率会差一些，会占用更多的磁盘空间来保留数据和索引。
不同于其他引擎的地方：

自动增长列

InnoDB自动增长列可以手动插入，如果插入为空0或者为空，实际插入将是自动增长后的值。 ```sql mysql> show create table auto_incre \G * 1. row *
```
Table: auto_incre
```
Create Table: CREATE TABLE auto_incre ( i smallint(6) NOT NULL AUTO_INCREMENT, name char(5) DEFAULT NULL, PRIMARY KEY (i) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

*下面插入了0和Null* mysql> insert into auto_incre values (1, ‘1’), (0, ‘2’), (null, ‘3’); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from auto_incre; +—-+———+ | i | name | +—-+———+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +—-+———+


2. 可以通过`alter table XXX auto_increment = n`来设置自动增长id的初始值，默认值为1。这个值保存在内存中，重启后需重新设置。
2. 对于innodb表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。但是对于MyISAM，自动增长列可以是组合索引的其他列，自动增长列是按照组合索引前面几列进行排序后递增的。eg:创建一个MyISAM类型的表，自动增长列`d1`作为组合索引的第二列，可以发现自动增长列是按照组合索引第一列`d2`排序后递增的。
```sql
mysql> show create table autoincre_demo \G
*************************** 1. row ***************************
       Table: autoincre_demo
Create Table: CREATE TABLE `autoincre_demo` (
  `d1` smallint(6) NOT NULL AUTO_INCREMENT,
  `d2` smallint(6) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  KEY `d2` (`d2`,`d1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
mysql> insert into autoincre_demo (d2, name) values (2, '2'), (3, '3'), (4, '4'), (2, '2'), (3, '3'), (4, '4');
mysql> select * from autoincre_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+

外键约束

MySQL支持外键的只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
eg:创建两个表，contry为父表，counry_id为主键索引，city为子表，country_id为外键，对应于country表的主键。

mysql> show create table country \G
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(50) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> show create table city \G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

在创建索引时，可以指定在删除、更新父表时，对子表进行相应的操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。

CASCADE：表示父表在进行更新和删除时，更新和删除子表相对应的记录
RESTRICT和NO ACTION：限制在子表有关联记录的情况下，父表不能单独进行删除和更新操作

SET NULL：表示父表进行更新和删除的时候，子表的对应字段被设为NULL

***************************插入测试数据***************************
mysql> insert into country values(1, 'China');
mysql> insert into city value(1, 'shanghai', 1);
mysql> select * from country;
+------------+---------+
| country_id | country |
+------------+---------+
|          1 | China   |
+------------+---------+
mysql> select * from city;
+---------+----------+------------+
| city_id | city     | country_id |
+---------+----------+------------+
|       1 | shanghai |          1 |
+---------+----------+------------+
***************************删除父表记录***************************
mysql> delete from country where country='China';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
***************************修改父表记录***************************
mysql> update country set country_id=2  where country_id=1;
mysql> select * from city;
+---------+----------+------------+
| city_id | city     | country_id |
+---------+----------+------------+
|       1 | shanghai |          2 |
+---------+----------+------------+

当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时，如果需要忽略导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA和ALTER TABLE操作的时候，可以通过暂时关闭外键约束来加快处理速度，关闭命令为SET FOREIGN_KEY_CHECKS = 0；执行完成之后，通过执行SET FOREIGN_KEY_CHECKS=1恢复。
可以通过以下命令查看InnoDB类型表的外键信息。

show create table
show table status

存储方式
InnoDB存储表和索引有以下两种方式。
使用共享表空间存储：这种方式创建的表的表结构保存在.frm中，数据和索引保存在innodb_data_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
多表空间存储：表结构保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。
- 要使用多表空间存储方式，需要设置参数innodb_file_per_table，并且重新启动服务后才能生效。对于新建表使用多表空间存储方式，已创建表仍然使用共享表空间存储。若改回共享表空间存储，则已创建的使用多表空间存储的表仍然不变，新创建表使用共享表空间存储。
- 多表空间的数据文件没有大小限制。
- 可以比较方便进行单表备份和恢复操作，直接复制.ibd问价你是不行的，没有共享表空间和数据字典信息；直接复制.ibd和.frm文件恢复时是不能被正确识别的，但是以通过以下命令：
  - ALTER TABLE tbl_name DISCARD TABLESPACE
  - ALTER TABLE tbl_name IMPORT TABLESPACE

将备份恢复到数据库中，但是这样的单表备份，只能恢复到原来的数据库中。恢复到其他数据库中需要通过mysqldump和mysqlimport命令。
即便是在多表空间存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和在线重做日志放在这个文件中。

场景

用于事务处理的应用程序，支持外键。
对事务完整性要求比较高。
在并发条件下要求数据一致性。
数据除了插入和查询外，还包括很多插入和删除操作。