图片 8

mysql 开荒基础体系10 存款和储蓄引擎 InnoDB 介绍

一. 概述:

 
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)

图片 1

  能够通过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')

图片 2

  下边能够看见自增加是依据组合索引的后边几列实行排序后递增的。

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

图片 3

图片 4

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

图片 5

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

图片 6

图片 7

  在导入几个表数据时,要是急需忽视之前的导入顺序,能够有时关闭外键的自己批评,加快管理速度。

-- 关闭命令是
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'

图片 8

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

    

 

MySQL存款和储蓄引擎笔记

 

仓库储存引擎概述

插件式存款和储蓄引擎是MySQL数据库的重大特征之一,客商能够依据使用的急需选拔如何存款和储蓄和目录数据、是不是使用事物等
 www.2cto.com  

特点

MyISAM

InnoDB

Memory

东埃德蒙顿全

 

支持

 

锁机制

表锁

行锁

表锁

索引类型

B树索引

全文索引

B树索引

集群索引

B树索引

哈希索引

数码缓存

 

支持

支持

目录缓存

支持

支持

支持

帮助外键

   www.2cto.com  

支持

 

仓库储存引擎性情

MyISAM(ISAM → Indexed Sequential Access Method )引擎

a)         MyISAM

劣点:不匡助事物、不扶持外键

优势:访谈速度快

适用:对事物完整性无必要、并发性不高或以select和insert操作为主的利用(web、数据货仓等)

b)        MyISAMy表存款和储蓄格式

静态表:存款和储蓄神速、轻便缓存、故障复苏轻松(注:数据最后的空格会被电动删除)

动态表:空间占领小,删除和翻新易产生碎片化(注:optimize
table或myisamchk -r碎片整理)

压缩表:每条记下单独压缩、访谈费用小

InnoDB引擎

a)       InnoDB

劣点:写操作效能差、占用愈来愈多磁盘(保留数据和目录)

优势:事物安全、协理外键

适用:对事物完整性和产出下得一致性要求高并且有相当多更新、删除操作(InnoDB有效裁减了删减更新导致的表锁定)的运用(计费、财务系统等)

b)      自动增进体系

  1.       自动拉长列必需是索引。

  2.      
    如若是组成索引,则自动拉长列必需是率先列(MyISAMy可为别的列,MyISAMy是比照前几列排序后递增的)

例:

create table autoincrease_demo_myisam

(t1 smallint not null auto_increment,

 t2 smallint not null,  www.2cto.com  

 name varchar(20),

 index(t2,t1)

)engine =myisam;

逐一插入以下数据

t1

2

2