MySQL存储引擎 InnoDB 介绍

共计 2910 个字符，预计需要花费 8 分钟才能阅读完成。

一. 概述:

InnoDB 存储引擎提供了具有提交，回滚，和崩溃恢复能力的事务安全，对比 MYISAM 的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。它的特点有如下：

1. 自动增长列特点（AUTO_INCREMENT）

InnoDB 表的自动增长列可以手工插入，但插入的值如果是空或者是 0，则实际插入的将是自动增长的值，下面演示下

CREATE TABLE autoincre_demo
(
  i  SMALLINT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(10),
  PRIMARY KEY(i)
)ENGINE=INNODB

INSERT INTO autoincre_demo VALUES(1,’1′),(0,’2′),(NULL,3)

可以通过 alter table autoincre_demo auto_increment=n 来设置自增长的初使值, 该值是保留在内存中，如重启数据库这个强制的默认值就会丢失。

2. 自动增长与索引

对于 InnoDB 表，自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列。但对于 MYISAM 表，自增长列可以是组合索引的其他列。
这里简单来说下 MySQL 的索引，索引的关键词包括: key（普通索引）, primary key（主键索引）,unique key（唯一索引）,index（没有约束的索引）。

下面演示下 Myisam 类型的表 autoincre_demo, 自动增长列 d1 作为组合索引第二列。

 CREATE TABLE autoincre_demo
(
  d1  SMALLINT NOT NULL AUTO_INCREMENT,
  d2  SMALLINT NOT NULL,
  NAME VARCHAR(10),
  INDEX(d2,d1)
)ENGINE=MYISAM

INSERT INTO autoincre_demo(d2,NAME) VALUES(2,’2′),(3,’3′),(4,’4′),(2,’2′),(3,’3′)

上面可以看出自增长是按照组合索引的前面几列进行排序后递增的。

3. 外键约束

mysql 支持外键的存储引擎只有 innodb, 在创建外键的时候，要求父表必须有对应的索引，子表创建外键的时候也会自动创建对应的索引

 下面演示两个表 country 父表 country_id 列为 主键索引，city 子表其中 country_id 列为外键

  — 创建父表
CREATE TABLE country
(
  country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country VARCHAR(50) NOT NULL,
  PRIMARY KEY(country_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

— 创建子表  关联 country_id
CREATE TABLE city
(
  city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country_id SMALLINT 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 DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=utf8

下面先说下里面的关键词的含义：
unsigned：表示无符号的意思, 也就是非负数, 只用于整型。
key：为 country_id 建个索引，名叫 dx_fk_country_id。
CONSTRAINT：关键词是约束，外键约束名叫 fk_city_country，FOREIGN KEY 是对应的外键字段。
references：是引用 country 表的 country_id 字段。
在删除更新父表时，对子表相应的操作包括 restrict，cascade,set null 和 no action。
其中 restrict 与 no action 相同 是指限制在：子表有关联记录的情况下父表不能更新；cascade 表示父表在更新或者删除时，同时更新或删除子表相应记录。set null 则表示在更新或者删除时，子表对应字段被设置为 null。
了解后在看 on delete ,restrict 是指：主表删除记录时，如果子表有对应记录，则不允许删除。
on update cascade 是指：主表更新记录时，如果子表有对应记录，则子表对应更新；

– 先维护下数据
INSERT INTO country(country) VALUES(‘ 中国 ’);
INSERT INTO city (country_id) VALUES(1);

— 先试下 on delete ,restrict 的作用，主表删除记录时，如果子表有对应记录，则不允许删除
DELETE FROM country WHERE country_id=1

— 再试下 on update cascade 是指主表更新记录时，如果子表有对应记录，则子表对应更新；
UPDATE country SET country_id=2 WHERE country_id=1;

在导入多个表数据时，如果需要忽略之前的导入顺序，可以暂时关闭外键的检查，加快处理速度。

— 关闭命令是
set foreign_key_checks=0;
— 开启
set foreign_key_checks=1;

对于 INNODB 类型表，外键的信息通过使用 INFORMATION_SCHEMA 查看

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA =’test’

4. 存储方式（简单了解）

        innodb 存储表和索引有两种方式。

        一是使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。

        二是使用多表空间存储，这种方式创建的表的表结构保存在.frm 文件中，每个表的数据和索引单独保存在.ibd 中，如果是分区表，则每个分区对应单独的.ibd 文件，文件名是 ” 表名 + 分区名 ”

  需要使用多表空间存储，需要设置参数 innodb_file_per_table, 并重新启动服务后生效。新建的表就按多表空间来存储，已有表仍然使用共享表空间存储。

多表空间存储的优势是方便进行单表备份和恢复操作，命令如下：

ALTER TABLE tab_name DISCARD TABLESPACE
TABLE TABLE tab_name IMPORT TABLESPACE

：