MySQL约束与索引解析：你应该知道的一切

MySQL约束与索引概念是什么

一、关系型数据库设计规则

遵循ER模型和三范式

• E entity 代表实体的意思 对应到数据库当中的一张表

• R relationship 代表关系的意思

三范式：

1、列不能拆分

2、唯一标识

3、关系引用主键

具体体现

• 将数据放到表中，表再放到库中。

• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。

• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python 中 “类”的设计。

• 表由列组成，我们也称为字段。数据表的设计实际上是对各个字段的含义进行设计和描述。在创建数据表时，需要为每个字段指定数据类型，定义它们的数据长度和命名。每个字段类似java 或者python中的“实例属性”。

• 表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。

二、数据完整性和约束与索引的概念

1、数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability。这个要点旨在防止数据库中存在不符合语义规定的数据，同时避免因错误信息的输入输出而导致无效操作或错误信息。

数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录

• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”

• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门

• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

2、约束（CONSTRAINTS）

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限在当前数据库，约束可以被当做数据库对象来处理，它们具有名称和关联模式，是逻辑约束，不会因为设置约束而额外占用空间。

3、索引（INDEX）

索引是一个单独、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单（类似于新华字典的目录索引页）。索引的存在会增加数据库的存储空间，也会使插入、修改数据的时间开销变多(因为插入和修改数据时，索引也要随之变动），但是可以大大提高查询速度。因此应该在键列、或其他经常要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列，值很少的列（例如性别只有男和女）等列上不应该创建索引。

①Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。

②主键删除，对应的索引也会删除

③删除唯一键的方式是通过删除对应的索引来实现的

④删除外键，外键列上的索引还在，如果需要删除，需要单独删除索引

三、约束的应用

1、查看某个表的约束和索引

#查看某个表的约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '
表名称'
;

或
SHOW CREATE TABLE 表名;

#查看某个表的索引
SHOW INDEX FROM 表名称;

2、主键约束：primary key

（1）主键分为单列主键和复合主键（复合主键不建议使用，因为复合主键违背三范式。）：

#单个字段设置主键
create table t_user(
id int primary key,
username varchar(20),
password varchar(20)
);

create table t_user(
id int,
username varchar(20),
password varchar(20),
primary key(id)
);

#多个字段设置联合主键
drop table t_user;

create table t_user(
id int,
username varchar(20),
password varchar(20),
primary key(id,username)
);

#了解
#在建表后指定主键约束
alter table 表名称 add primary key (主键字段列表);

#删除主键约束
alter table 表名称 drop primary key;

主键的特点：

• 1、一张表中只能有一个主键

• 2、设置为主键的字段的值唯一且非空

• 3、若主键有多个字段组成，此时不能在字段后面设置主键，应该在所有字段后面使用"
  primary key(字段,字段)"
  

• 4、联合主键中，组成主键的每个字段都非空，可以单独重复，但是不能同时重复

• 5、创建主键会自动创建对应的索引，同样删除主键对应的索引也会删除。

3、自增约束：auto_increment

create table t_user(
id int primary key auto_increment,
username varchar(20),
password varchar(20)
);

#建表后指定自增长列
alter table [数据库.]表名 modify 自增字段名 数据类型 auto_increment;

#删除自增约束
alter table 表名 modify 自增字段名 数据类型;

自增约束的特点：

要求一个表中只有一个主键为自增长类型的字段，该字段必须为整型且不能为空。通常只在主键上设置键约束，如主键约束、唯一键约束、外键约束

2、设置为自增的字段，从1开始自增；每次添加数据，都会在该字段最大值的基础上+1

3、使字段自增的方式：

• 如果是空或者0，则实际插入的将是自动增长后的值。

• a>
  insert into t_user(username,password) values(&
  lsquo;
  admin&
  rsquo;
  ,&
  lsquo;
  123456&
  rsquo;
  );
  

• b>
  insert into t_user values(null,&
  lsquo;
  root&
  rsquo;
  ,&
  lsquo;
  123456&
  rsquo;
  );
  (推荐使用)

• c>
  insert into t_user values(0,&
  lsquo;
  root&
  rsquo;
  ,&
  lsquo;
  123456&
  rsquo;
  );
  

4、唯一键约束：unique key

create table t_user(
id int primary key auto_increment,
username varchar(20) unique key,
password varchar(20) unique key
);

create table t_user(
id int primary key auto_increment,
username varchar(20),
password varchar(20),
unique key(username,password)
);

#在建表后增加唯一键约束
alter table 表名称 add 【constraint 约束名】 unique key (字段名列表);

#如果没有指定约束名，(字段名列表)中只有一个字段的，默认是该字段名，如果是多个字段的默认是字段名列表的第1个字段名。也可以通过show index from 表名;
来查看
#删除唯一键约束
ALTER TABLE 表名称 DROP INDEX 唯一性约束名;

#注意：如果忘记名称，可以通过“show index from 表名称;
”查看

唯一键约束的特点：

• 1、设置唯一键约束的字段值唯一，但是可以为null

• 2、一张表可以设置多个唯一键约束，也可以设置联合唯一键，即多个字段设置一个唯一约束，但是不能使用"
  unique key"
  写在字段后设置，必须写在所有字段后，使用"
  unique key(字段,字段)"
  

• 3、联合唯一键要求组成唯一约束的字段可以单独重复，不能同时重复

• 4、 MySQL会给唯一约束的列上默认创建一个唯一索引。

• 5、删除唯一键只能通过删除对应索引的方式删除，删除时需要指定唯一键索引名

5、非空约束：not null

create table t_user(
id int primary key auto_increment,
username varchar(20) unique key not null,
password varchar(20)
);

#在建表后指定某个字段非空
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL 【default 默认值】;

#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失
#取消某个字段非空
ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;

#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

非空约束的特点：

设置为非空约束的字段的值不能为null

6、默认值约束：default

create table t_user(
id int primary key auto_increment,
username varchar(20) unique key not null,
password varchar(20),
gender char not null default '
男'

);

添加数据时使用默认值的方式：

不为该字段赋值或使用关键字default

• insert into t_user(username,password) values(&
  lsquo;
  root&
  rsquo;
  ,&
  lsquo;
  123&
  rsquo;
  );
  

• insert into t_user values(null,&
  lsquo;
  admin123&
  rsquo;
  ,&
  lsquo;
  123&
  rsquo;
  ,default);
  

• insert into t_user values(null,&
  lsquo;
  admin&
  rsquo;
  ,&
  lsquo;
  123&
  rsquo;
  ,null);
  //此方式不可以，会为该字段赋值为null

7、外键约束：foreign key

表关系：

1、一对一

2、多对一，在多的一方引用一的主键

• student(sid,sname,age,sex,cid)&
  ndash;
  clazz(cid,cname,location)

3、一对多，在多的一方引用一的主键

• clazz(cid,cname,location)&
  ndash;
  student(sid,sname,age,sex,cid)

4、多对多

• user(uid,username,password)

• order(oid,create_time,total_count,total_amount,status,user_id)

• order_goods(id,oid,gid)

• goods(gid,gname,price,sales,stock)

create table t_dept(
id int primary key auto_increment,
name varchar(20)
);

create table t_emp(
id int primary key auto_increment,
name varchar(20),
age int,
gender char,
dept_id int,
foreign key(dept_id) references t_dept(id)
#外键只能在所有字段列表后面单独指定
);

#在建表后指定外键约束
alter table 从表名称 add 【constraint 外键约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名) 【on update xx】[on delete xx];

#删除外键约束
ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;

#查看某个表的约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '
表名称'
;

或
SHOW CREATE TABLE 表名;

#删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;

#查看索引名 show index from 表名称;

（1）外键特点

• 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;
  ），也可以指定外键约束名。

• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。

• 删除外键时，关于外键列上的普通索引需要单独删除。

（2）要求

• 在从表上建立外键，而且主表要先存在。

• 一个表可以建立多个外键约束

• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键），推荐引用主表的主键。

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

（3）约束关系：约束是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束

• 添加了外键约束后，从表的添加和修改数据受约束

• 在从表上建立外键，要求主表必须存在

• 删除主表时，要求从表先删除，或将从表中外键引用该主表的关系先删除

（4）5个约束等级

• Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

• Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

• No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

• Restrict方式：同no action, 都是立即检查外键约束

• Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式

8、检查约束：check

检查约束，mysql暂不支持

create table stu(
sid int primary key,
sname varchar(20),
gender char check ('
男'
or'
女'
)
);

insert into stu values(1,'
张三'
,'
男'
);

insert into stu values(2,'
李四'
,'
妖'
);

使用枚举类型解决如上问题：
create table stu(
sid int primary key,
sname varchar(20),
gender enum ('
男'
,'
女'
)
);


要让MySQL数据库正常运行，需要对表进行一定的约束和设计优化。这就是MySQL约束与索引的作用，并且不管是数据处理、查询、排序还是更新操作，这些都会影响MySQL的性能。
一、MySQL约束是什么？
MySQL约束是用于保证表中的数据安全完整性的一种方式。在创建表格的时候，可以加入约束规则来限制对表格的操作，以防止出现不合法的数据。
1. Not Null约束
这种约束可用于确保某一列的值不能为空。
2. Unique约束
这种约束可用于确保某一列的值没有重复出现。
3. Primary Key约束
这种约束可用于确保某一列的值唯一并且不为空。在一个表中只能有一个主键。
4. Foreign Key约束
这种约束可用于确保两个表之间的关系正确。指定的外键必须是另一张表的主键或唯一键。
二、索引是什么？
索引是用来优化数据查询的重要手段。在创建表格的时候，可以为某些列（或者是组合列）创建索引来提高查询效率。
索引分为主键索引和非主键索引，其通过B+Tree的算法实现。主键索引在创建表格时就已经被创建，而非主键索引则可以在需要的时候创建。
1. 主键索引
主键索引是在创建表格时自动生成的，并且是表格中最重要的索引。它用于维护表格的一些特殊属性，如唯一性和允许Null值等。
2. 非主键索引
非主键索引是针对数据表中的其他列来创建的索引。这种索引常被用于快速查询大量数据，但并不会增强表的数据完整性。
三、MySQL约束与索引的使用技巧
MySQL约束与索引的使用需要根据不同的情况和需求，有选择地进行设计与应用。下面是几个MySQL约束与索引的使用技巧：
1. 使用最少的约束
尽管约束是保证数据完整性的一个好办法，但是过多的约束会对性能产生影响。因此，为了保证优秀的查询性能，应该尽量少使用约束。
2. 尽量使用整形
对于需要创建索引的列，最好使用整型。整型存储空间比字符类型小，并且MySQL支持多种整型类型，在性能上都比字符类型要优秀。
3. 单列索引优于多列索引
在针对单个列进行查询时，使用单列索引会比多列索引更加高效。因为单列索引可以更好地利用MySQL的B+Tree算法，从而提高查询效率。
MySQL约束与索引的使用泛指本文的MySQL约束与索引概念介绍及其使用技巧。只有深入了解MySQL约束与索引的概念及其优化对数据库性能的影响，才能能够充分利用MySQL提供的约束和索引功能，使查询尽可能地高效率，达到开发应用实现高效的数据库管理。