一. 概述:
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
来源: http://www.linuxidc.com/Linux/2018-09/154350.htm