mysql的约束、索引、范式
### 约束
-
表或列的primary key,unique,not null等修饰符常常被称作约束(constraint)
-
主要作用:是数据库用来提高数据质量和保证数据完整性的一套机制,是表定义(DDL语句)的一部分
-
约束主要包括:
- 非空约束 (not null)
- 唯一性约束 (unique)
- 主键约束 (primary key)
- 外键约束 (foreign key)
-
约束的定义方式
-
创建表时定义
-
列级别定义
-
表级别定义
-
-
修改表时进行追加定义
-
-
约束的查看
-
使用desc查看列属性查看约束
-
使用show create 命令查看
-
使用show index命令查看(无法查看not null 约束)
-
-
非空约束(not null)
-
用于确保其所在列的值不能为空值null,允许有多个列设置非空约束
-
只有列级定义和追加定义
-
列级定义
-
追加定义
-
非空约束的删除(将其改回为null)
或
-
-
惟一约束(unique)
-
列字段中不允许有重复值,但是可以有空值,允许有多个列设置惟一约束
-
也被称为惟一索引(其他数据库管理系统略有不同)
-
列级定义
-
表级定义
-
追加定义
-
删除惟一约束
-
-
主键约束(primary key)
-
主键列中不允许有重复值,也不可以有空值
-
一个表中只能有一个主键约束,但一个主键约束可以包含多个列
-
定义主键时会同时为主键创建唯一性索引
-
列级定义
-
表级定义
-
追加定义
-
删除主键约束
-
-
外键约束(foreign key)
-
外键约束引用主键构成完整性约束,外键允许有空值,不允许存在对应主键约束的列所有数值以外的其它值
-
外键可以同时创建多个
-
创建外键约束时,MySQL自动创建非唯一性索引
-
表级别定义
先定义作为关联的主键表
或使用constraint为外键指定名字
定义外键表指定外键为主键表中的主键
-
追加定义
-
删除外键
-
索引
-
索引是一种可以让SELECT语句提高效率的一种数据结构,类似书籍的目录、图书分类号等
-
索引要占用额外磁盘存储空间
-
索引的优点
- 某些情况下使SELECT语句大幅提高效率,合适的索引可以优化MySQL服务器的查询性能,从而起到优化MySQL的作用
-
索引的缺点
- 表行数据的变化(insert, update, delete),建立在表列上的索引也会自动维护,一定程度上会使DML操作变慢;索引会占用磁盘额外的存储空间
-
索引的类型
- FULLTEXT
- 即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。
- 全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
- HASH
- 由于HASH的唯一性(几乎100%的唯一)及类似键值对的形式,很适合作为索引
- HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。
- BTREE
- BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。
- RTREE
- RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
- 相对于BTREE,RTREE的优势在于范围查找
- FULLTEXT
-
索引分类
- 普通索引:加速查询
- 唯一索引:加速查询 、列值唯一、列值允许为空
- 主键索引:加速查询 、列值唯一、列值不允许为空且表中只允许出现一个主键索引
- 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
- 全文索引:对文本的内容进行分词,进行搜索
-
按照存储方式分类
- 聚集索引 clustered index:索引与表数据存放在一起(主键索引为聚集索引)
- 非聚集索引 non-clustered index :索引页与表页分离,索引占用额外磁盘空间
-
创建索引
-
语法结构如下:
CREATE INDEX index_name ON table_name(col_name); #创建普通索引 CREATE UNIQUE INDEX index_name ON table_name(col_name); #创建唯一索引 CREATE INDEX index_name ON table_name(col_name1,col_name2); #创建普通组合索引 CREATE UNIQUE INDEX index_name ON table_name(col_name1,col_name2); #创建唯一组合索引 #注:index_name必须唯一
-
建表时添加索引
-
追加索引
或追加组合索引
-
-
查看索引
或
或
-
删除索引
或
注:第一种删除方法为mysql特有的,第二种为通用方法
-
注意事项:
- 主键约束会自动创建索引,主键索引是聚集索引,索引中不能有重复值
- 对于外键约束MySQL也会自动创建索引,外键约束的索引是非聚集索引,默认是非唯一索引(因为外键列可以有重复值)
- 对于唯一性约束MySQL也会自动创建索引,唯一性约束的索引是唯一性索引
- 一个表只能有一个主键约束(因为主键索引是聚集索引,索引值和对应表行数据存放在一起而且按照索引值的顺序存放;同一时刻只能有一种排序方式),但是可以给主键列之外的列添加多个非空+唯一性约束(效果等同于主键约束)
数据库范式
- 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小
- 范式的分类
- 第一范式(1NF)
- 第二范式(2NF)
- 第三范式(3NF)
- 巴斯-科德范式(BCNF)
- 第四范式(4NF)
- 第五范式(5NF,又称完美范式)
- 一般来说,数据库只需满足第三范式(3NF)
- 第一范式
- 数据库表的每一列都是不可分割的基本数据项(原子性)
- 即:表中不能有性质相同的重复列
- 第二范式
- 必须满足第一范式
- 表中非主键列都完全依赖于主键列,表中的每一列都和主键相关,而不能只与主键的某一部分相关
- 消除非主键列对主键列的部分依赖
- 第三范式
- 必须满足第二范式
- 所有非主键列对非主键列不存在依赖,即表中的每一列数据都和主键直接相关,而不能间接相关
- 消除传递依赖
- 表之间的关系
- 一对一:如学生与学号即为一对一的关系,一个学生只有一个学号
- 一对多:如班级与学生的关系就是一对多,一个班级对应多个学生
- 多对多:课程与学生的关系即为多对多的关系,每门课程有多名学生选择,每个学生需要选择多门课程