[大数据]MySQL中的约束和存储引擎

约束（Constraint）

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有哪些呢？

• 非空约束（not null）：约束的字段不能为 NULL。
• 主键约束（primary key）：约束的字段不能重复
• 外键约束（foreign key）：简称FK
• 检查约束（check） ：注意oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束

非空约束not null

create table t_user(
id int ,
username varchar(255) not null,
password varchar(255)
)；

唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。

– 案例：
create table t_user(
id int ,
username varchar(255) unique
);
insert into t_user values(1,‘zhangsan’);
insert into t_user values(2,‘zhagnsan’);

– 案例：给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int ,
username varchar(255),
email varchar(255),
unique(usercode,username) //多个字段联合起来添加一个约束 [表级约束]
);

drop table if exists t_user;
create table t_user(
id int ,
username varchar(255) unique, //列级约束
email varchar(255) unique
);
注意：not null 约束只有列级约束，没有表级约束

主键约束

create table t_user(
id int primary key,
username varchar(255),
email varchar(255)
) ;

根据以上的测试得出：id是主键，因为添加了主键约束，主键约束中的数据不能为NULL，也不能重复。

主键有什么作用

• 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
• 主键的作用：主键值是记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）

主键的分类

根据主键字段的字段数量来划分：
单一主键 （推荐的，常用的）
复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式）
根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键（不推荐用）**最好不要拿着和业务挂钩的字段作为主键。**因为以后的业务一但发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。一张表的主键约束只能有一个。

使用表级约束方式定义主键：

drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);

mysql 提供主键值 自增：

drop table if exists t_user;
create table t_user (
id int primary key auto-increment, //id字段自动维护一个自增的数字，从1开始，以1递增。
username varchar(255)
);

提示：oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

外键约束

现在一般不建议使用，每次做DELETE 或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦,测试数据极为不方便。
阿里巴巴开发规范也不建议使用：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

存储引擎

CREATE TABLE dept (
DEPTNO int(2) NOT NULL,
DNAME varchar(14) DEFAULT NULL,
LOC varchar(13) DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

建表的时候可以指定存储引擎，也可以指定字符集。
MySql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF8。

什么是存储引擎呢？

存储引擎这名字只有在mysql中存在。（oracle中有相应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是”表的存储方式”）
Mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

查看当前mysql支持的存储引擎

show engines

Mysql 5.7.27版本支持的存储引擎有9个。

MyISAM存储引擎

MyISAM存储引擎是mysql最常用的引擎。
它管理的表具有以下特征：

• 使用三个文件表示每个表：
  • 格式文件-存储表结构的定义（mytable.frm）
  • 数据文件-存储表行的内容（mytable.MYD）
  • 索引文件-存储表上索引（mytable.MYI）
• 灵活的AUTO_INCREMENT字段处理
• 可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。缺点：不支持事务。

InnoDB存储引擎

InnoDB存储引擎是mysql的缺省引擎。
它管理的表具有下列主要特征：

• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tablespace被用于存储表的内容
• 提供一组用来记录事务性活动的日志文件
• 用commit（提交）、SQVAPOINT及ROLLBACK（回滚）支持事务处理
• 提供全ACID兼容
• 在mysql服务器崩溃后提供自动恢复
• 多版本（MVCC）和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎查询速度最快。
MEMORY存储引擎管理的表具有下列特征：

• 在数据库目录内，每个表均以.frm格式的文件表示。
• 表数据及索引被存储在内存中。
• 表级锁机制。
• 不能包含TEXT或BLOB字段。
• MEMORY存储引擎以前被称为HEAP引擎。
• 不支持事务数据容易丢失。

总结

• MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适合情形是使用压缩的只读表。
• 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
• 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于键盘的表中重新生成的数据。