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的优势在于范围查找

• 索引分类

  • 普通索引：加速查询
  • 唯一索引：加速查询 、列值唯一、列值允许为空
  • 主键索引：加速查询 、列值唯一、列值不允许为空且表中只允许出现一个主键索引
  • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
  • 全文索引：对文本的内容进行分词，进行搜索

• 按照存储方式分类

  • 聚集索引 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）
• 第一范式
  • 数据库表的每一列都是不可分割的基本数据项（原子性）
  • 即：表中不能有性质相同的重复列
• 第二范式
  • 必须满足第一范式
  • 表中非主键列都完全依赖于主键列，表中的每一列都和主键相关，而不能只与主键的某一部分相关
  • 消除非主键列对主键列的部分依赖
• 第三范式
  • 必须满足第二范式
  • 所有非主键列对非主键列不存在依赖，即表中的每一列数据都和主键直接相关，而不能间接相关
  • 消除传递依赖
• 表之间的关系
  • 一对一：如学生与学号即为一对一的关系，一个学生只有一个学号
  • 一对多：如班级与学生的关系就是一对多，一个班级对应多个学生
  • 多对多：课程与学生的关系即为多对多的关系，每门课程有多名学生选择，每个学生需要选择多门课程