mysql的约束，引擎，索引

最新推荐文章于 2023-03-14 19:13:00 发布

栗子鱿鱼

最新推荐文章于 2023-03-14 19:13:00 发布

阅读量133

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/qq_45083168/article/details/112320306

版权

Mysql 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

文章目录

1. 约束(Constraint)

什么是约束？常见的约束有哪些？

在创建表的时候，可以给表字段添加相应的约束，其目的是：为了保证表中数据的**合法性**、**有效性**、**完整性**
常见的约束有：
	非空约束(not null)：约束的字段不能为NULL
	唯一约束(unique)：约束的字段不能重复
	主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
	外键约束(foreign key)：...（简称FK）
	检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

1.1 非空约束(not null)

create table 表名(
	字段名1 数据类型 primary key not null，
	字段名2 数据类型 default 1，
	字段名3 数据类型 not null，
	...
);

mysql> insert into 表名(字段名1,字段名2) values(值1,值1);
ERROR 1364 (HY000): Field '值3' doesn't have a default value

1.2 唯一约束(unique)

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

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255) unique  // 列级约束
);

mysql> insert into t_user values(1,'zhangsan');
Query OK, 1 row affected (0.08 sec)

mysql> insert into t_user values(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_user.username'

// 给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
	id int, 
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);

not null约束只有列级约束。没有表级约束

1.3 主键约束(primary key)

主键约束，主键字段中的数据不能为NULL，也不能重复。

create table 表名(
	字段名1 数据类型 primary key not null，
	字段名2 数据类型 default 1，
	字段名3 数据类型 not null，
	...
);

create table 表名(
	字段名1 数据类型，
	字段名2 数据类型
	primary key(字段名1,字段名2)
);

主键有什么作用？

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

主键的分类？

根据主键字段的字段数量来划分：
- 单一主键（推荐的，常用的。）
- 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
- 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
- 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要
随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

一张表的主键约束只能有1个

mysql提供主键值自增：id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象

1.4 外键约束(foreign key)

外键约束的相关术语

外键约束: foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值

1.4.1 栗子

请设计数据库表，用来维护学生和班级的信息

第一种方案：一张表存储所有数据

no(pk)	name	classno	      classname
----------------------------------------------
	1	zs1		 101	       高三1班
	2	zs2		 101	       高三1班
	3	zs3		 102	       高三2班
	4	zs4		 102	       高三2班
	5	zs5		 102	       高三2班

缺点：冗余。【不推荐】

第二种方案：两张表（班级表和学生表）
班级表：t_class

   cno(pk)		cname
--------------------------------------------------------
	101		   高三1班
	102		   高三2班

学生表：t_student

  sno(pk)	sname		 classno(该字段添加外键约束fk)
------------------------------------------------------------
	1		 zs1			101
	2		 zs2			101
	3		 zs3			102
	4		 zs4			102
	5		 zs5			102

将以上表的建表语句写出来：
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表；t_class表叫做父表

删除的顺序：

删除数据的时候，先删除子表，再删除父表
添加数据的时候，先添加父表，在添加子表
创建表的时候，先创建父表，再创建子表
删除表的时候，先删除子表，在删除父表

外键值可以为NULL？

可以

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

被引用的字段不一定是主键，但至少具有unique约束

2. 存储引擎

存储引擎这个名字只有在mysql中存在。
Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”

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

show engines \G

mysql> show engines \G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

2.2 常见的存储引擎

MyISAM：不支持事务

采用三个文件组织一张表：

xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）

优点： 可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点： 不支持事务

InnoDB：

优点： 支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
用commit、savepoint、rollback支持事务处理
InnoDB支持级联删除和级联更新

MEMORY：

缺点： 不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点： 查询速度最快。
以前叫做HEPA引擎。

3. 事务

什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分
比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”
在这里插入图片描述
事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

和事务相关的语句

只有DML语句：insert、delete、update
因为它们这三个语句都是和数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性，安全性

假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务。
但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成

事务的特性

原子性：Atomicity 整个事务中的所有操作，必须作为一个单元全部完成(或全部取消)，事务是最小的工作单元，不可再分
一致性：Consistency 在事务开始之前与结束之后，数据库都保持一致状态，事务必须保证多条DML语句同时成功或者同时失败
隔离性：Isolation 一个事务不会影响其他事务的运行，事务A与事务B之间具有隔离
持久性：Durability 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚，持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束

关于事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读取未提交(read uncommitted)，对方事务还没有提交，我们当前事务可以读取到对方未提交的数据，读未提交存在脏读(Dirty Read)现象：表示读到了脏的数据(极其不稳定的数据)
第二级别：读取已提交(read committed)，对方事务提交之后的数据我方可以读取到，这种隔离级别解决了：脏读现象没有了。读已提交存在的问题是：不可重复读(当我读取数据的时候，对方在不断修改提交数据，我下次读取的时候数据是不一样的，既不可重复)【Oracle默认级别】
第三级别：可重复读(repeatable read)，这种隔离级别解决了：不可重复读问题。这种隔离级别存在的问题是：读取到的数据是幻象【Mysql默认级别】
第四级别：序列化读取/串行化读取(serializable)，解决了所有问题。效率低。需要事务排队，两个事务不能并发了

在这里插入图片描述

演示事务

mysql事务默认情况下是自动提交的。
什么是自动提交？只要执行任意一条DML语句则提交一次
怎么关闭自动提交？start transaction;
演示

自动提交：

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment not null,
	username varchar(20)
);

insert into t_user(username) values('张三');

select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
+----+----------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
+----+----------+
1 row in set (0.00 sec)

回滚，发现数据已经提交了，回不去了 ⇒ mysql中的事务自动提交了

关闭自动提交：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('李四');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  2 | 李四     |
+----+----------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
+----+----------+
1 row in set (0.00 sec)

rollback和commit后事务已经结束，要重新开始]

提交：commit

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('南疆');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
+----+----------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
+----+----------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
+----+----------+
2 rows in set (0.00 sec)

演示隔离级别

演示两个事务，假如隔离级别【打开两个窗口】

演示第1级别：读未提交：

mysql> set global transaction isolation level read uncommitted; // 设置全局的事务隔离级别为读取未提交
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation; // mysql8：查看全局隔离级别  mysql5:select @@global.tx_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)

mysql> exit // 设置后全部退出，重新进去

窗口1：

mysql> use test;
Database changed
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
+----+----------+
2 rows in set (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
|  4 | Lisa     |
+----+----------+
3 rows in set (0.00 sec)

窗口2：

mysql> use test;
Database changed
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('Lisa');
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

读到了数据，脏读。

演示第2级别：读已提交：

窗口1：

mysql> use test;
Database changed
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> exit 

// 登录
mysql> use test;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
+----+----------+
2 rows in set (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
+----+----------+

窗口2：

// 登录
mysql> use test;
Database changed
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user(username) values('Tom');
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

读取已经提交：脏读现象没有了，不commit就读取不到数据

演示第3级别：可重复读：

窗口1：

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

mysql> exit
// 登录
mysql> use test
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  3 | 南疆     |
|  5 | Tom      |
+----+----------+
3 rows in set (0.00 sec)

窗口2：

mysql> use test
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t_user;
Query OK, 3 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

在这里插入图片描述

可以重复读。实际上，窗口1读的是备份数据，不是原表数据。在窗口2中其实已经修改了表。所以这种就幻像读

4. 索引

什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源
在数据库方面，查询一张表的时候有两种检索方式：
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）

其实最根本的原理是缩小了扫描的范围

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护

添加索引是给某一个字段，或者说某些字段添加索引

select ename,sal from emp where ename='SMITH';当ename字段上没有添加索引的时候，这句会进行全表扫描，扫描ename字段中所有的值；当ename字段上添加索引的时候，会根据索引扫描，快速定位。

怎么创建索引对象？怎么删除索引对象？

创建索引对象：

create index 索引名 
on 表名(字段名);

删除索引对象：

drop index 索引名 
on 表名;

什么时候考虑给字段添加索引？（满足什么条件）

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中。（经常根据哪个字段查询）

注意：主键和具有unique约束的字段自动回添加索引。根据主键查询效率最高，尽量根据主键检索。

示例

mysql> select ename,sal from emp where sal=5000;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.12 sec)

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

⇒ type：all 全表扫描 rows：14 扫描了14条记录

给薪资sal字段添加索引：

mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (1.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

mysql> drop index emp_sal_index on emp; // mysql可以，oracle可以不加on
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.1 索引的实现原理

索引底层数据结构：B+Tree

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH';
//通过索引转换为：
select ename from emp where 物理地址 = 0x3;

在这里插入图片描述

4.2 索引的分类

单一索引：给单个字典添加索引
复合索引：给多个字段联合添加一个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段会自欧东添加索引
…

索引什么时候失效？

select ename from emp where ename like '%A%';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

栗子鱿鱼

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录