MySQL存储引擎 InnoDB 介绍

栏目: 数据库 · 发布时间: 6年前

内容简介:一. 概述:InnoDB存储引擎提供了具有提交,回滚,和崩溃恢复能力的事务安全,对比MYISAM 的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。它的特点有如下:1. 自动增长列特点 (AUTO_INCREMENT)

一. 概述:

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)

MySQL存储引擎 InnoDB 介绍

可以通过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')

MySQL存储引擎 InnoDB 介绍

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

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);

MySQL存储引擎 InnoDB 介绍

MySQL存储引擎 InnoDB 介绍

-- 先试下on delete ,restrict的作用,主表删除记录时,如果子表有对应记录,则不允许删除

DELETE FROM country WHERE country_id=1

-- 再试下on update cascade 是指主表更新记录时,如果子表有对应记录,则子表对应更新;

UPDATE country SET country_id=2 WHERE country_id=1;

MySQL存储引擎 InnoDB 介绍

MySQL存储引擎 InnoDB 介绍

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

-- 关闭命令是

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'

MySQL存储引擎 InnoDB 介绍

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

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-09/154350.htm


以上所述就是小编给大家介绍的《MySQL存储引擎 InnoDB 介绍》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

马云如是说

马云如是说

朱甫 / 中国经济出版社 / 2008-1-1 / 39.80元

任何一个企业家的成功,都需要一种特立独行的精神。换尔言之,他一定是不断地否定别人的反对意见,坚持自己独特的观点,才能够真正走向大成功。在中国企业家群像里,马云就是这样一个特立独行的人。 目录 *永不放弃——马云论创业精神 *天下没有难做的生意——马云论经营理念 *B2B时代——马云论电子商务 *网络只是一个工具——马云论互联网与网络公司 *太多钱会坏事——马云论......一起来看看 《马云如是说》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

SHA 加密
SHA 加密

SHA 加密工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具