mysql一约束和索引的区别

阳光穿透心脏的1/2处 2024-03-26 15:05 27阅读 0赞

#### 文章目录 ####

*  一、约束（Constraint）
 *   *  1、主键约束（primary key）
     *  2、唯一性约束（unique）
     *  3、默认值约束（default）
     *  4、外键约束（foreign key）
 *  二、索引（index）
 *   *  索引类型
 *  三、区别与联系

> 本文内容仅在 MariaDB-10.2.15 版本下验证，其它环境下可能略有差异。

简单来说，约束是为了实现业务规则、保证数据的完整性，索引是为了查询高效，二者原本是两个不同的东西，只是在 mysql 中实现方法有类似之处，所以经常会让人感到迷惑。

## 一、约束（Constraint） ##

约束，是为了实现非空、非重等常见业务规则，在定义数据时对表或某些字段增加的特定的约束规则。

常见的约束类型有主键约束、唯一约束、非空约束、默认值约束、[外键约束][Link 1]等。

### 1、主键约束（primary key） ###

*  primary key，用于定义表的主键，是唯一确定表中每一条记录的标识符
 *  主键不能为空，也不能重复
 *  一张表中只能有一个主键

**设置主键：**

*  创建表的同时设置主键

create table student1 (
    	sid int(10) primary key,  -- 对 sid 字段设置主键约束
    	sname varchar(20) not null  -- 对 sname 字段设置非空约束
    )

*  创建表以后，追加主键

create table student2(
    	sid int(10) not null,
    	sname varchar(10) not null
    )
    
    # 追加主键，并设置主键名称
    ALTER TABLE student2 ADD CONSTRAINT pks2 PRIMARY KEY(sid);

*  设置复合主键
    
     *  即把多个列同时设置为一个主键

create table student3 (
    	sid int(10) not null,
    	nid int(10) not null,
    	sname varchar(10) not null,
        primary key (sid, nid)  -- 定义复合主键
    )

*  删除主键：

ALTER TABLE student3 DROP PRIMARY KEY;

### 2、唯一性约束（[unique][]） ###

*  unique，设置某列数据不能重复，但可以有空值
 *  一张表中可以对多个列设置 unique 约束，也可以把多个字段定义成一个 unique 约束
 *  主键所在的列，不能使用唯一约束

**区分唯一约束和主键约束：**

*  一张表只能有一个主键，但可以出现多个唯一约束
 *  主键不能为null，唯一可以为null

**设置唯一约束：**

*  在创建表的同时，设置唯一约束：

create table student4(
    	sid int(10) primary key ,
    	sname varchar(10) unique
    )

*  在创建表以后，追加约束：

create table student5(
    	sid int(10) primary key,
    	sname varchar(10)
    )
    ALTER TABLE student5 ADD CONSTRAINT uns5
    UNIQUE(sname);

*  把多个字段都设置成同一个唯一约束：

create table student6 (
    	sid int(10) primary key,
    	nid int(10) not null,
    	sname varchar(10) not null,
        UNIQUE KEY uk6(sid, nid)  -- 定义复合 unique 约束
    )

**删除约束：**

ALTER TABLE student6 DROP INDEX uk6;

### 3、默认值约束（default） ###

在插入操作时，当某一列没有值时系统就自动把之前设置的默认值赋值过去。

**设置默认值约束：**

create table student7(
    	sid int(10) primary key,
    	sname varchar(10) not null,
    	age int(10) default 12  -- 设置默认值为12
    )

### 4、外键约束（foreign key） ###

**定义：**

*  如果在一张表中有一个非主键的字段，指向了另一张表中的主键。
    
     *  每张表中可以有多个外键

*  通常将外键所在的表称为子表或被约束表，指向的另一个表称为父表或约束表或外表
    
     *  子表中外键字段的取值范围由父表决定

*  子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败

*  对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败

**外键的三种约束模式：**

*  **`district`：** 严格模式， 父表不能删除或更新一个被子表引用的记录。

*  **`cascade`：** 级联模式，父表操作后，子表关联的数据也跟着一起操作。

*  **`set null`：** 置空模式，父表操作后，子表对应的字段被置空（前提是外键字段允许为NULL）。

**使用外键的前提：**

*  表储存引擎必须是 innodb，否则创建的外键无约束效果。
 *  外键的列类型必须与父表的主键类型完全一致。
 *  外键的名字不能重复。
 *  已经存在数据的字段被设为外键时，必须保证字段中的数据与父表的主键数据对应起来。
 *  外键必须建立索引（可以为普通、主键、唯一，事先不建立的话会自动创建一个普通索引）

**使用外键的优缺点：**

*  通过数据库自身机制（而非程序员）来保证数据一致性和完整性
 *  可靠性较高，但在一定程度上降低了数据库的速度

*  当系统数据越来越多时，尤其是现在的互联网高并发业务场景较多，若外键较多，会导致系统响应很慢，要尽量少用外键，改成用中间层控制业务规则！

"外键使用示例："
    --dept表
    create table dept(
    	did int primary key,
    	dname varchar(10) not null unique, 
    )
    --emp表
    create table emp(
    	eid int primary key,
    	ename varchar(10) not null ,
    	salary float not null,
    	dept_id int,
        foreign key(dept_id) references dept(did)  --定义外键 dept_id 与 dept 表的 did 主键字段关联
    )

其实在实际工作中，用的比较多的是主键、非空、默认值约束，其他的很少用，复杂的业务规则尽量通过程序来控制，从而提高数据库性能。

## 二、索引（index） ##

**定义：**

*  索引是一种高效获取数据的数据结构，可以快速提高查询速度
    
     *  在 innoDB 引擎中使用的是 `B-Tree` 索引算法。

*  当查询海量数据时，索引的效果尤其明显。

*  索引是定义在列上的，有单列索引、组合索引。

### 索引类型 ###

*  **普通索引(normal)：**
    
     *  `create index 索引名字 on 表名(列名)`

*  **主键索引：**
    
     *  很多时候也把 `PRIMARY KEY` 称为主键索引，但是主键和索引还是两个不同的概念。
     *  可以理解为，创建主键的同时，mysql 会自动对它创建索引.
 *  **唯一索引(`unique`)：**
    
     *  索引字段必须是唯一的，该值不能重复
     *  `create unique index 索引名 on 表(列名)`
 *  **复合索引：**
    
     *  也叫组合索引，在多个列创建一个索引
     *  `create index myIndex on teacher2(sname, sex)`

**空间索引(spatial)**:这个有点复杂,可以去单独研究一下.

**全文索引(fullText)**:全文索引是为了解决需要基于相似度的查询，而不是精确数值比较。

虽然使用 `like + %` 也可以实现模糊匹配，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 `like` 快 N 倍，速度不是一个数量级。

但是全文索引缺点也很大,如:导致磁盘资源的大量占用,全文索引可能存在精度问题.

*  **删除索引：**
    
     *  `drop index 表.索引名字`

## 三、区别与联系 ##

这里主要说明主键约束、唯一性约束和索引之间的区别与联系。

*  **概念上不同：**
    
     *  约束是为了保证数据的完整性，索引是为了提高查询速度。

*  **创建主键约束时，mysql 默认会自动创建一个索引**
    
     *  若要实现主键的值不重复，在每次插入新记录时都需要检索数据，所以为了提高检索速度，同时对主键创建索引。

*  **创建唯一约束时，mysql 默认会自动创建一个唯一索引**
    
     *  跟上面的道理一样，通过唯一索引实现唯一约束

[Link 1]: https://so.csdn.net/so/search?q=%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F&spm=1001.2101.3001.7020
[unique]: https://so.csdn.net/so/search?q=unique&spm=1001.2101.3001.7020