首页 > 编程笔记 > MySQL笔记 阅读:10

MySQL InnoDB存储引擎简介(新手必看)

InnoDB 是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。MySQL 5.5.5 之后,InnoDB 作为默认的存储引擎。

InnoDB 写处理相对于 MyISAM 效率低一些,InnoDB 牺牲了存储和查询的效率,支持事务安全,支持自动增长列。对事务安全的支持,这是 InnoDB 成为 MySQL 最为流行的存储引擎之一的重要原因。下面重点介绍 InnoDB 存储引擎的特点。

MySQL InnoDB支持事务

MySQL 支持对 InnoDB 存储事务控制,实现了 SQL92 标准所定义的四个级别(read uncommitted、repeatable read、read committed 和 serializable)。

MySQL 通过 commit﹑rollback﹑set autocommit﹑start transaction 等语法支持本地事务,具体语法如下所示:
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN ][[NO] RELEASE]
SET AUTOCOMMIT = {0|1}

MySQL InnoDB自动增长列

InnoDB 表的自动增长需要在列的后面添加 auto_increment 属性,对表的添加数据过程中,可以插入空值,该列都可以自动增加数据。

以下示例可以实现自动增长列。首先创建具有增长列属性的表,命令如下:
mysql> create table authors(
     id int primary key auto_increment,
     name varchar(10));
插入 id 列为空值的数据,命令如下:
mysql> insert into authors(name) values('ivan'),('susan'),('shark');

查询插入的结果,命令如下:
mysql> select * from authors;
+----+-------+
| id | name  |
+----+-------+
|  1 | ivan  |
|  2 | susan |
|  3 | shark |
+----+-------+
从结果可以看出,id 列自动插入了数值。

MySQL InnoDB外键约束

InnoDB 实现了外键这一数据库重要功能。从数据库性能上讲,数据库外键降低了数据库查询的效率,数据库表之间的耦合度更加紧密,但是对于不少用户来讲,采用外键约束可能是最低成本的选择方式。

MySQL 支持外键的存储引擎只有 InnoDB。在创建外键的时候,要求父表必须有对应的索引,字表在创建外键的时候会添加相对应的索引。

下面是一个外键的例子,sclass 是主表,id 作为主表主键索引,st 表示子表,其中 class_id 作为外键对应 sclass 表的 id 值。

首先创建数据表 sclass,命令如下:
mysql> create table sclass(
     id int primary key auto_increment,
     cname varchar(20) not null,
     last_update timestamp not null default current_timestamp on update current_timestamp);

创建数据表 st,并添加外键约束,命令如下:
mysql> create table st(
     id integer primary key auto_increment,
     sname varchar(20) not null,
     class_id integer not null,
     last_update timestamp not null default current_timestamp on update current_timestamp,
     foreign key(class_id) references sclass(id) on delete restrict on update cascade);
对于上面创建的两个表,在做删除操作时,如果是删除主表的数据,子表对应的记录不会被删除;如果是更新主表,子表对应的记录会更新。

在物理存储方面,InnoDB 有自己独特的存储方式,数据也是存放在 .frm 文件里面,但是表数据和索引数据是存放在一起的。

InnoDB的存储表和索引有以下两种方式:
如果要使用多表空间存储方式,需要设置 innodb_file_per_table 参数。该参数可以修改 InnoDB 为独立表空间模式,每个数据库的表都会生成一个数据空间。

可以使用如下方式查看多表存储空间模式是否已经开启:
mysql> SHOW VARIABLES LIKE '%per_table%';
+-----------------------+-------+
| Variable_name        | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
在设置 innodb_file_per_table 参数之前,需要先关闭数据库,然后在 my.cnf 文件中设置或者添加该参数 innodb_file_per_table=1,并重启数据库才能生效。这时候,再查看多表存储空间模式是否已经开启:
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
使用多表空间特性的表,可以比较方便地进行表备份和恢复操作,但是直接复制 .idb 文件是不行的,可以通过以下命令:
ALTER TABLE TABLE_NAME DISCARD TABLESPACE;
ALTER TABLE TABLE_NAME IMPORT TABLESPACE;
InnoDB 在功能上跟 MyISAM 存储引擎有很大的不同,在参数配置上,InnoDB 也是单独处理的,InnoDB 所有的参数基本上都加了前缀“innodb_”。

如果想屏蔽 InnoDB 存储引擎,在 my.ini 配置文件中,将 skip-innodb 参数前的 # 去除,这样就无法创建 InnoDB 类型的表了。

相关文章