Mysql

SQL的分类

多表查询

多表查询也称为关联查询

前提条件：这些表之间是有关系的（一对一，一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键

为什么需要多表查询呢，因为如果合成为一张表的话，粒度太大了，当一个线程操作数据库时，可能导致其他线程不能操作；且合成为一张表时可能会造成空间的浪费

实现

例如：

练习：

#查询员工id为...的人在哪个部门工作
select employee_id , department_name
from emp,dep #连接两张表操作
where emp.departemt_id = dep.department_id

注意：如果查询语句中出现多个表中都存在的字段，则必须指明此字段所在的表；从sql优化的角度来看，建议多表查询时，每个查询字段都指明其所在表

#查询员工的employee_id,department_name,city
select employee_id,department_name,city
from emp e ,dep d ,location l #连接三张表
where e.department_id = d.department_id and d.location_id = l.location_id

自连接

自己连接自己

#查询员工id，员工姓名，及其管理者的id和姓名
select emp1.employee_id,emp1.last_name,emp2.employee_id,emp2.last_name
from emp emp1,emp emp2
where emp1.manager_id = emp2.employee_id

JOIN语法

内连接

合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行；只查询出能完全匹配的数据

#查询员工的employee_id,department_name信息
select employee_id,department_name
from emp e 
join dep d
on e.department_id = d.department_id

外连接

合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到左表或右表中不匹配的行

左外连接

#查询所有员工的employee_id,department_name信息
select employee_id,department_name
from emp e 
left join dep d
on e.department_id = d.department_id
#结果集中包含不匹配的结果，匹配不到的结果以null表示

右外连接

#查询所有员工的employee_id,department_name信息
select employee_id,department_name
from emp e 
right join dep d
on e.department_id = d.department_id
#结果集中包含不匹配的结果，匹配不到的结果以null表示

满外连接

使用 full 关键字（Mysql不支持）

#查询所有员工的employee_id,department_name信息
select employee_id,department_name
from emp e 
full join dep d
on e.department_id = d.department_id
#结果集中包含不匹配的结果，匹配不到的结果以null表示

使用 union all 关键字

#利用 union 关键字，可以给出多条select语句，并将它们的结果集合成为单个结果集，并且去除重复的部分，合并时，两个表对应的列数和数据类型必须相同，且互相对应；union all 关键字不会去重，效率相对高一些

#左外连接去除重复部分
select employee_id,department_name
from emp e 
left join dep d
on e.department_id = d.department_id
where d.department_id is NULL
union all
#右外连接
select employee_id,department_name
from emp e 
right join dep d
on e.department_id = d.department_id

外连接的效率要高于子查询，因为子查询走的是笛卡尔积

自然连接

不够灵活，了解即可

#我们可以把自然连接理解为等值连接，它会自动查询两种链接表中所有相同的字段，然后进行等值连接
select employee_id,last_name,department_name
from emp e 
natural join dep d

我们在使用SQL语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS。DBMS之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被DBMS同时支持的。比如，大多数DBMS使用（||）或者（+）来做拼接符，而在MYSQL 中的字符串拼接函数为concat()。大部分DBMS会有自己特定的函数，这就意味着采用SQL函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

MySQL的内置函数

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MysQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类:单行函数、聚合函数（或分组函数）

单行函数：接收一条数据，产生一条数据

聚合函数：接收多条数据，产生一条数据

单行函数

数值基本函数

函数	用法
ABS(X)	返回x的绝对值
SIGN(X)	返回x的符号；正数1，负数-1
PI()	返回圆周率的值
FLOOR(X)	返回小于或等于某个值的最大整数
LEAST(E1,E2,E3)	返回列表中的最小值
GREATEST(E1,E2,E3)	返回列表中的最大值
MOD(X,Y)	返回x除以y后的余数
RAND()	返回0~1的随机数
RAND(X)	返回0~1的随机数，并用x的值作为种子
ROUND(X)	返回对x进行四舍五入后的结果

字符串类型函数

日期时间类型函数

流程控制函数

加密解密函数

聚合函数

对一组数据进行汇总的函数，输入的事一组数据的集合，输出的是单个值

常见的聚合函数

函数	用法
AVG	求平均值
SUM	求总和
MAX	求最大值
MIN	求最小值
COUNT	求个数

例如：

#avg sum
select avg(salary),sum(salary)
from emp

#max min
select max(salary),min(salary)
from emp

#count 不计算null值
select count(employee_id)
from emp

group by

group by声明在from之后、where之后、order by之前、limit之前

对一个字段进行分组

例：

#查询各个部门的平均工资，最高工资
select department_id, avg(salary), sum(salary)
from emp
group by department_id

对多个字段进行分组

例：

#查询各个部门、job_id的平均工资
select department_id, job_id, avg(salary)
from emp
group by department_id, job_id

having

用于过滤数据，与group by结合使用
having中可带有聚集函数
声明于gruop by之后
当过滤条件中没有聚合函数时，建议将过滤条件放在where中，where执行效率更高

例：

#查询各个部门中最高工资比10000高的部门信息
select department_id, max(salary)
from emp
gruop by department_id
having max(salary) > 10000

总结

sql99语法

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入。

SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合)进行比较。

例：

#谁的工资比jjw高
select last_name, salary
from emp
where salary > (
	select last_name, salary
	from emp
	where last_name = 'jjw'
)

子查询在主查询之前完成
子查询结果被主查询使用
子查询要包含在括号内

不相关子查询

上面的例子就是不相关子查询，子查询中每次查询都是同一个数据，与主查询不相关

#谁的工资比jjw高
select last_name, salary
from emp
where salary > (
	select last_name, salary
	from emp
	where last_name = 'jjw'
)

#查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id, min(salary)
from emp
group by department_id
having min (salary) > (
	select min(salary)
	from emp
	where department_id = 50
)

#多行子查询需要使用in关键字
select employee_id, last_name 
from emp
where salary in (
    select min(salary)
    from emp
    group by department_id
)

DDL

数据定义语言，对数据库中的某些对象(例如database、table)进行管理，如create、alter和drop。DDL操作是隐性提交的，不能rollback

创建和管理表

创建数据库

#创建数据库
create database 数据库名

#创建并指定字符集编码
create database 数据库名 character set '字符集编码'

#不存在才创建
create database if not exists 数据库名

展示数据库

1	show database

切换数据库

use 数据库名

查看当前数据库中保存的数据表

1	show tables

查看当前使用的数据库

1	select database() from dual

修改表

#添加一个字段
alter table 数据库名
add 字段名 数据类型 约束

#修改一个字段：数据类型 、长度、默认值
alter table 数据库名
modify 字段名 数据类型（长度） default '默认值'

#重命名一个字段
alter table 数据库名
change 旧字段名 新字段名 数据类型

#删除一个字段
alter table 数据库名
drop column 字段名

#重命名表
rename table 旧数据库名
to 新数据库名

#删除表
drop table [if exists] 数据库名

修改数据库

修改数据库字符集编码

1	alter database 数据库名 character set '字符集编码'

删除数据库

1
2
3

drop database 数据库名

drop database if exists 数据库名

基于现有的表创建新表，同时导入数据

create table 数据库名
as
select ...
from ...

DML

数据操纵语言，对数据库中的数据进行一些简单操作，如insert、delete、update、select等。DML操作是可以手动控制事务的开启、提交和回滚的

DML操作默认是自动提交的，提交之后不能回滚，需要手动关闭自动提交才能回滚

1	set autocommit = false

查询数据

插入数据

//将一个新学生元组
insert into student
values ('201215128','陈东','男',18,'IS')

//插入一条选课记录
insert into sc(Sno,Cno) VALUES('201215128','1')

//insert语句与查询语句结合使用
//计算数据，存放到表中
insert into Deptage(Sdept,avg_age)
select Sdept,avg(Sage)
from student
group by Sdept

修改数据

//update语句与where语句结合使用
//将学生201215121的年龄改为22岁
update student
set Sage=22
where Sno='201215121'

//将所有学生的年龄增加1岁
update student
set Sage=Sage+1

删除数据

//delete语句与where语句结合使用
delete 
from student
where Sno='201215128'

//删除计算机系所有学生的选课记录
delete 
from sc
where Sno in(
  select Sno
  from student
  where Sdept='CS'
)

Mysql数据类型

浮点类型

浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例，因此，浮点数和定点数的使用场景比整数大多了

float 单精度浮点数
double 双精度浮点数

float 占用字节数少，取值范围也小；double占用字节数多，取值范围大

Mysql允许使用非标准语法(其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用)：FLOAT(M,D)或DOUBLE(M,D)；这里,M称为精度，D称为标度。(M,D)中 M=整数位+小数位，D=小数位。D<=M<=255，0<=D<=30。

例如，定义为FLOAT(5,2)的一个列可以显示为 -999.99~999.99 如果超过这个范围会报错。

不管是否显式设置了精度(M,D)，这里MySQL的处理方案如下:

如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值。

如果存储时，小数点部分若超出范围，就分以下情况:

若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。

若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。如FLOAT(5,2)列内插入999.995和-999.995都会报错。

定点数类型

在编程中，浮点数是不精准的，对于一些精度要求较高的项目，我们不能用浮点数，而是使用定点数；Mysql 中的定点数类型只有 decimal 一种类型

使用DECIMAL(M,D)的方式表示高精度小数。其中，M被称为精度，D被称为标度。O<=M<=65，0<=D<=30,D<M。例如，定义DECIMAL (5,2）的类型，表示该列取值范围是-999.99~999.99。当DECIMAL类型不指定精度和标度时，默认为DECIMAL(M,D)

DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。

位类型

BIT类型中存储的时二进制值，类似于010110

BIT类型如果没有指定精度，默认是1位；最大能表示64位

使用SELECT命令查询位字段时，可以用BIN()或 HEX()函数进行读取。

日期类型

YEAR类型通常用来表示年
DATE类型通常用来表示年、月、日
TIME 类型通常用来表示时、分、秒
DATETIME类型通常用来表示年、月、日、时、分、秒
TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒

文本字符串类型

MysQL中，文本字符串总体上分为CHAR、VARCHAR 、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET等类型。

char / varchar

char是固定长度的，默认为1个字符
当存储数据的长度比char类型声明的长度小的时候，则会在右侧填充空格
当检索char类型时会将字符尾部的空格去除
定义varchar类型时必须指定长度
当检索varchar类型时会保留尾部的空格

区别：

对于InnoDB存储引擎而言，建议使用varchar，因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列，主要影响性能的因素是数据行使用的存储量

text

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型。

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。

由于实际存储的长度不确定，Mysql不允许text类型作为主键

如果频繁的使用表，不建议包含text类型，建议单独分出，因为text类型如果删除后容易导致空洞，造成文件碎片

enum

1
2
3

create table test(
	season enum('春','夏','秋','冬')
)

添加数据时候只能从中选取一个添加，且不区分大小写

set

set表示一个字符串对象，可以包含0个或多个成员，上限为64

create table test(
	s set('a','b','c','d')
)

insert into test(s) values ('a'),('a,b')

二进制类型

binary / varbinary

BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。

BINARY(M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M),表示只能存储1个字节。例如BINARY(8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充’\o’以补齐指定长度。

VARBINARY(M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型必须指定(M)，否则报错。

blob

blob是一个二进制大对象，可以容纳可变数量的数据

MysQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片、音频和视频等。

需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。

//java中存储blob类型到数据库
InputStream in = new FileInputStream(new File(path));
int length=in.available();
pre.setBinaryStream(index,in,length);

约束

为什么需要约束

数据完整性是指数据的精确性和可靠性。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑:


实体完整性	例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性	例如:年龄范围0-120，性别范围“男/女”
引用完整性	例如:员工所在部门，在部门表中要能找到这个部门
用户自定义完整性	例如:用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍

如何添加、修改、删除约束

#create table 时添加约束
create table 表名(
	字段名 数据类型 约束
	...
)

#alter table 时修改约束
alter table 表名
add constraint 约束

#删除约束
alter table 表名
drop 约束

约束的分类

表级约束：定义完所有字段再添加约束

列级约束：定义字段的同时定义约束

约束的作用

非空约束（NOT NULL）：限定某个字段 / 某列的值不允许为空
唯一性约束（UNIQUE）：限制某个字段 / 某列的值不允许重复
主键约束（PRIMARY KEY）：主键约束相当于唯一性约束+非空约束，主键不允许重复也不允许出现空值；当创建主键约束时，系统会默认在所在的字段上建立对于的主键索引，一个表中只能有一个主键
1
2
3
#删除主键约束
alter table 表名
drop primary key
- auto_increment
  
  作用：某个字段的值自增
  - 自增列必须是主键列或唯一性列
  - 自增列的数据类型必须是整数类型
外键约束（FOREIGN KEY）：限定某个表的某个字段的引用完整性；创建表时要先创建主表再创建从表，删除时反之；创建外键约束时会自动加上普通索引
1
2
#表级约束
CONSTRAINT fk_empl_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
- 约束等级
  
  1、cascade：父表上更新、删除记录时，同步到子表
  
  2、set null：父表上更新、删除记录时，将子表中的相应字段设为null
  
  3、no action：如果子表中有匹配的记录时，不允许父表对候选键进行更新、删除
  
  4、set default：父表上更新、删除记录时，将子表中的相应字段设为默认值
- 设置约束等级
  1
  FOREIGN KEY(deptid) REFERENCES dept(did)ON UPDATE CASCADE ON DELETE SET NULL
在MysQL里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能会不适合。增、删、更新都会进行对数据进行约束性检查，从而耗费资源。

比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
检查约束（CHECK）：检查数据是否在某个范围内
默认值约束（DEFAULT）：

视图

为什么要使用视图

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

概述

视图建立在已有表的基础之上
视图是一种虚拟表，本身时不具有数据的，占用很少的内存空间，操作时就是对基表进行操作
视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。
向视图提供数据内容的语句为SELECT语句,可以将视图理解为存储起来的 SELECT语句

创建视图

#精简版
create view 视图名
as 查询语句

#自定义字段名匹配
create view 视图名(字段名...)
as
select 字段名...
...

#也可以存储多表查询
create view 视图名
as 多表查询语句

查看视图

#查看视图对象
show tables

#查看视图结构
describe 视图名

#查看视图的属性信息
show table status like '字段名'

#查看视图的详细定义信息
show create view 视图名

视图中增删改操作都跟之前介绍的一样，这里就不再赘述

修改视图

#使用 create or replace view 语句修改视图
create or replace view 视图名
as 查询语句

#使用alter
alter view 视图名
as 查询语句

删除视图

1	drop view if exists 视图名

视图的优点

减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

数据安全

MysQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。

视图的缺点

维护成本高

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图)，维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的sQL查询可能会对字段重命名，也可能包含复杂的逻辑。这些都会增加维护的成本。

存储过程

含义

就是一组经过预先编译的SQL语句的封装；执行过程：存储过程预先存储在Mysql服务器上，需要执行的时候，客户端只需要想服务器发出调用存储过程的命令，服务端就可以把预先存储好的这一系列SQL语句全部执行

与视图和函数相比

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的sQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

创建存储过程

create procedure (存储过程名 in（有参）| out（有返回值）| inout（有参有返回值）)
begin
	存储过程体（比如查询）
end

存储过程的调用

1	call 存储过程名()

例：

#查出员工中工资最少的
create procedure test(out output int（数据类型）)
begin
	select min(salary) into output
	from emp
end

call test(@output（直接定义一个用户变量）)

select @output

#查出jjw的工资
create procedure test(in input varchar(20)（数据类型）)
begin
	select salary
	from emp
	where last_name = input
end

##直接传参数
call test('jjw'（直接定义一个变量）)
##传递一个变量
set @name = 'jjw'
call test(@name)

变量

在Mysql数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据；在Mysql数据库中，变量分为系统变量以及用户自定义变量

系统变量

变量由系统定义，不是用户定义，属于服务器层面。启动MysQL服务，生成MySQL服务实例期间，MySQL将为MysQL服务器内存中的系统变量赋值，这些系统变量定义了当前MysQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值。

系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字)，有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量(在MySQL服务实例运行期间它们的值不能使用set动态修改）属于特殊的全局系统变量。

查看系统变量

#查看所有全局变量
show global variables

#查看所有会话变量
show session variables

#查看满足条件的部分系统变量
show global variables like '%标识符%'

#查看指定系统变量
select @@global.变量名

#查看会话变量
select @@session.变量名
select @@变量名

修改系统变量

方法一：修改Mysql配置文件，继而修改Mysql系统变量的值，修改完后需要重启Mysql服务

方法二：在Mysql服务运行期间，使用set命令重新设置系统变量的值

set @@global.变量名 = 变量值
set global 变量名 = 变量值

set @@session.变量名 = 变量值
set session 变量名 = 变量值

用户变量

用户变量是用户自己定义的，作为MySQL编码规范，MySQL中的用户变量以一个“@”开头。根据作用范围不同又分为会话用户变量和局部变量。

会话用户变量:作用域和会话变量一样，只对当前连接会话有效。
局部变量:只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用。

创建用户变量

#定义会话用户变量
set @用户变量名 = 值


#定义局部变量：使用declare声明；使用在begin...end中
declare 变量名 数据类型 默认值
set 变量名 = 值
#例：
DELIMITER &
CREATE PROCEDURE test_pro()
BEGIN
    #声明
    DECLARE emp_name VARCHAR(25);
    DECLARE sal DOUBLE(10,2) DEFAULT 0.0;#赋值
    SELECT last_name,salary INTO emp_name,sal
    FROM employees
    WHERE employee_id = 102;#使用
    SELECT emp _name,sal;
END &
DELIMITER ;


#使用用户变量
select @变量名

触发器

触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现

创建触发器

create trigger 触发器名
before|after  insert|update|delete  on 表名
for each row
触发器执行语句块

例：

create trigger before_insert
before insert on test_trigger_log
for each row
begin
	...
end

逻辑架构

Mysql是经典的 C/S 架构，服务端程序使用的是 mysqld

不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是:客户端进程向服务器进程发送一段文本(SQL语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果)。

第一层：连接层

系统（客户端）访问MySQL服务器前，做的第一件事就是建立TCP连接；经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

多个系统都可以和MysQL服务器建立连接，每个系统建立的连接肯定不止一个。所以，为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。Mysql 服务器里有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接，来解决上述问题。

TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

两个池：TCP连接池、线程池

第二层：服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化∶如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。

如果是SELECT语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

服务层包含了：解析器、查询优化器、查询缓存组件

第三层：引擎层

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的MysQL还允许开发人员设置自己的存储引擎。

插件式存储引擎层，真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

第四层：存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

SQL执行流程

查询缓存：server如果在查询缓存中发现了这条SQL语句，就直接将结果返回给客户端，如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存的命中率太低（因为sql语句要完全一样，且Mysql的缓存系统会对涉及到的每张表进行监测，如果该表的结构或数据被修改了，该缓存就会失效），所以在 Mysql8.0 之后就抛弃了这个功能

查询优化器：查询优化器分为逻辑查询优化和物理查询优化

物理查询优化则是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
逻辑查询优化就是通过SOL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

存储引擎

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为Mysql server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在Mysql server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。Mysql5.5之后默认存储引擎为innoDB

可以设置默认的存储引擎，也可以针对表修改存储引擎

InnoDB存储引擎

具备外键功能的事务存储引擎

InnoDB是Mysql的默认事务型引擎，它被设计用来处理大量的短期事务，可以确保事务的完整提交和回滚
除了增加和查询之外，还需要更新、删除操作，那么应优先使用InnoDB存储引擎
InnoDB是为处理巨大数据量的最大性能设计的
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
相比MyISAM，InnoDB使用的是行级锁，读效率更高；但会占用更多的磁盘空间以保存数据和索引，所以写效率更低
应用场景：并发量高、需要读写事务的场景

MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键。有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
5.5之前默认的存储引擎
优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
应用场景：只读或以读为主的业务

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据，对内存要求较高
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源

InnoDB数据存储结构

磁盘与内存交互的基本单位：页

InnoDB将数据划分为若干个页，InnoDB中页的默认大小为16KB

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中。不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page)，数据库 I/O 操作的最小单位是页。一个页中可以存储多个行记录。

页结构概述

页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里面的记录生成一个页目录

了解页的结构有助于我们理解B+树

页的上层结构

另外在数据库中，还存在着区、段和表空间的概念。关系如下

区：是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中一页的大小默认是16KB，所以一个区的大小是64*16KB=1MB

段：由一个或多个区组成，区在文件系统是一个连续分配的空间，不过段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段

表空间：是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

页的内部结构

页如果按类型划分的话，常见的有数据页（保存B+树节点)、系统页、Undo页和事务数据页等。数据页是我们最常使用的页。

数据页的16KB大小的存储空间被划分为七个部分，分别是

文件头：描述页的信息
页头：页的状态信息
最大最小记录：最大和最小记录，这是两个虚拟的行记录
用户记录：存储行记录内容
空闲空间：页中还没有被使用的空间
页目录：存储用户记录的相对位置
和文件尾：校验页是否完整

文件头包含

FIL_PAGE_OFFSET：相当于页号
FIL_PAGE_TYPE：页的类型；可能是数据页、系统页、…
FIL_PAGE_PREV 和 FIL_PAGE_NEXT：上一页、下一页内存地址
FIL_PAGE_SPACE_OR_CHKSUM：校验和；用于校验两个页是否一样
FIL_PAGE_LSN：页面被最后修改时对应的日志序列位置

校验和的作用

InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。

为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况〉，这时可以通过文件尾的校验和（checksum值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输己已经完成。

文件尾部包含

FIL_PAGE_SPACE_OR_CHKSUM：校验和；用于校验两个页是否一样
FIL_PAGE_LSN：页面被最后修改时对应的日志序列位置

FIL_PAGE_SPACE_OR_CHKSUM，FIL_PAGE_LSN这两个字段其实就是用来校验一个页是否完整

索引

为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MysQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录；索引的目的就是为了减少磁盘 I/O 的次数，加快查询速率

概述

索引是帮助Mysql高效获取数据的数据结构
索引的本质：索引是数据结构；可以理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法
索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，所以说索引是B+树实现的是不准确的，应该说在InnoDB存储引擎中，索引是B+树实现的

索引的推演

单页查找

一个数据表中可能有庞大的数据量，InnoDB中一个表中会分成多页，一个页的容量为16KB。在一个页中的每一条数据按照主键顺序排放，排放好的数据可以按照二分法进行查找，使得时间复杂度从 O(n) 变为 O(ln n)

多页查找

在庞大的数据量情况下，一页肯定是不够的，所以我们需要多页来存放数据，当然每一页并不是按照页号来排放，页号仅仅是一个具体的内存地址；当我们需要向某一页插入一条，而这一页已满的时候，最后一条数据向后移动到下一页

这样在多页中进行查找的情况下就是拆分成单页进行查找，一个页中没找到，换到下一个页

建立目录项

多页查询的弊端就是数据之前的每一页都需要进行查询，这样的效率会降低。那么我们就给每一页创建一个目录项，目录项记录页号和每一页的最小值，这样的话每次查找先在目录项中使用二分法查找到数据应该在哪一页，之后再进入这一页查找数据

目录页

目录项之间肯定也需要采用单向链表的方式存储，所以我们不妨也给目录项制成一张目录表

如此反复套娃下去就形成了B+树的结构

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

虽然只有几个节点但是能存放的数据量是非常庞大的，假设一个节点能存1000条数据，那么如上图的B+树就能够存放1000*1000*1000 = 1000000000 条数据，而且因为树的高度仅为3，所以我们仅需要经过3次 I/O 就能找到数据

常见的索引

索引按照物理实现方式可分为2种

聚簇索引
非聚簇索引（二级索引）

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在叶子节点），也就是所谓的索引即数据，数据即索引；一张表中只能由一个聚簇索引

特点

页内的记录是按主键大小排成一个单向链表
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
B+树的叶子节点存储的是完整的用户记录

优点

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树种，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
按照聚簇索引排列顺序，查询显式一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 io 操作

缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则会出现页分裂，严重影响性能
更新主键的代价很高
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键找到数据

非聚簇索引

如果我们想以别的列作为搜索条件怎么办？我们可以多建几可B+树

这个B+树与上面介绍的聚簇索引有几处不同

叶子节点存储的并不是完整数据，而只是某一列和主键两个列的值
目录项不再是主键+页号，而是某一列+页号

例：如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为例，查找过程如下:

确定目录项记录页
根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42(因为2 < 4 < 9)。
通过目录项记录页确定用户记录真实所在的页。
在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为 2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
在真实存储用户记录的页中定位到具体的记录。
到页34和页35中定位到具体的记录。
但是这个B+树的叶子节点中的记录只存储了c2和c1(也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找—遍完整的用户记录。

回表

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树!

聚簇索引与非聚簇索引区别

聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。

一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。

使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

联合索引

我们也可以同时以多个列作为排序规则，也就是同时为多个列建立索引，比如说我们想让B+树按照c2和c3列的大小进行排序，这包含两层含义：

先把各个记录和页按照c2列进行排序
在记录的c2列相同的情况下，采用c3列进行排序

InnoDB的B+树索引注意事项

根页面位置万年不动

上述推演为了理解上的方便，是从叶子节点向上推演的，然而索引底层实现并不是这样的

而是先生成一个叶子节点后，如果存放的数据个数超过这个叶子节点的存储大小，则再生成两个叶子节点，然后将一开始的叶子节点上的数据复制到这两个新的叶子节点上，一开始的叶子节点变成根节点，由此往复

非叶子节点中目录项记录的唯一性

非叶子节点中存放的是目录项，我们必须确保目录项是唯一的，否则插入一条数据的时候不能确定再哪张表中插入，所以可以加入主键作为联合索引

一个页面最少存储两条记录

MyISAM中的索引(了解即可)

MyISAM的索引方案虽然也是采用B+树的结构，但却将索引和数据分开存储，叶子节点存放的是数据记录的地址

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。

为什么使用B+树而不使用B树呢

B树图示：

可以看出B树的非叶子节点同样也存放数据

B树非叶子节点不存放数据有什么好处呢？

首先，B+树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。

其次，B+树的查询效率更高。这是因为通常B+树比B树更矮胖（阶数更大，深度更低)，查询所需要的磁盘I/o也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。

不仅是对单个关键字的查询上，在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

索引的创建与设计原则

索引的分类

Mysql的索引包括了普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等

从功能逻辑上说，索引分为4种：分别是普通索引、唯一索引、主键索引、全文索引
按照物理实现方式来说，索引可分为2种：聚簇索引和非聚簇索引
按照作用的字段进行划分，可分为单列索引和联合索引

普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name上建立一个普通索引，查询记录时就可以根据该索引进行查询。

唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的但允许有空值。在一张数据表里可以有多个唯一索引。例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOTNULL+UNIQUE，一张表里最多只有一个主键索引。这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储

全文索引

全文索引(也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

创建索引

隐式：给字段添加上唯一性约束和主键约束后会自动、隐式的为字段创建唯一性索引和主键索引

显示：

语法

1 2	create table 表名索引类型 index 索引名(作用的字段（多个字段逗号隔开）) [asc\|desc]

创建主键索引

create table 表名
... PRIMARY KEY
...

#ALTER
ALTER TABLE table_name ADD PRIMARY KEY (`column`)

创建唯一索引

create table 表名
...
unique index 索引名(作用的字段) [asc|desc]

#ALTER
ALTER TABLE table_name ADD UNIQUE (`column`)

#CREATE ON
create unique index index_name on table_name(`column`)

创建单列索引和联合索引（普通索引）

create table 表名
...
index 索引名(作用的字段) [asc|desc]

create table 表名
...
index 索引名(字段1,字段2...) [asc|desc]

#ALTER
ALTER TABLE table_name ADD INDEX index_name (`column`)

#CREATE ON
create index index_name on table_name(`column`)

注：当创建联合索引后，比如
1
index  test_index(id,name)
当查询 name 字段时是不会走索引的，因为在索引上查找是按创建时字段的先后顺序的，上面创建的这个索引会先走 id 字段，如果 id 一样再走 name 字段，所以查询一个 name 字段不会走这个索引

这就是最左匹配原则，以最左边的为起点任何连续的索引都能匹配上

删除索引

alter table

1 2	alter table table_name drop index index_name

drop index
1
2
drop index index_name
on table_name

索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。

适合索引的情况

字段的数值具有唯一性的特点
频繁作为where查询条件的字段

某个字段在SELECT语句的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

经常 group by 和 order by 的字段

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引，因为索引本身就是有序的。

如果 group by 和 order by 两个字段一起出现，则创建联合索引，其顺序应为先 group by 字段再 order by 字段

distinct字段需要创建索引

因为B+树上都是有序的，相同的字段都是紧挨在一起，去重更容易

多表 join 连接操作时，对用于连接的字段创建索引
使用列的类型较小的创建索引

列的类型较小的意思是类型的数据范围较少，比如varchar（10）和 varchar（20）

数据类型越小，在查询时候进行的比较操作越快，且索引占用的存储空间越少

使用字符串前缀创建索引
散列性高的列适合作为索引

比如100w条数据中，性别就不适合作为索引，因为性别只有两种，散列性太低了

使用最频繁的字段放在联合索引的左侧

where id = … and name = … 如果创建的索引顺序是先name再id那么这个查询就用不到这个联合索引了（但是优化器可能会优化select语句，将其调换，使其能使用索引）

多个字段都要创建索引的情况下，联合索引优于单列索引

不适合索引的情况

where中使用不到的字段不设置索引

如果起不到定位的字段通常是不需要创建索引的

数据量小的表最好不要使用索引

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

有大量重复数据的列上不要建立索引
避免对经常更新的表创建过多的索引

因为更新数据时，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率

不建议用无序的值作为索引

例如身份证、uuid这些虽然是唯一的、非空的，但是作为主键索引的话插入时很有可能经常造成页分裂

不要定义冗余或重复的索引

限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
索引回影响insert、delect、update等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，造成负担
优化器再选择如何优化查询时，会根据同一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有多个索引都可以用于查询，会增加Mysql优化器生成执行计划的时间，降低查询性能

索引优化与查询优化

都有哪些维度可以进行数据库调优？

索引失效、没有充分利用到索引――索引建立
关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化
务器调优及各个参数设置（缓冲、线程数等）――调整my.cnf
数据过多—―分库分表

索引失效情况

外连接与内连接的查询优化

子查询优化

排序优化

GROUP BY优化

分页查询优化

事务

事务概述

事务是数据库区别于文件系统的重要特征之一，当我们有了事务就会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit)，那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。

事务的ACID特性

原子性

原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样?就会出现数据不一致的情形，A账户减去100元，而B账户增加100元操作失败，系统将无故丢失100元。

一致性

根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。

隔离性

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的状态

活动的

事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态

部分提交的

当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态

失败的

当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

中止的

如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

提交的

当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

事务基础知识

显式事务

开启事务

#方式一
start transaction [read only / read write（默认） / with consistent snapshot]

#方式二
begin

隐式事务

默认情况下，如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交

关闭自动提交

#方式一
set autocommit = false

#方式二
#显式的开启事务

提交事务

1 2	#提交事务 commit

回滚事务

1 2	#回到上一次提交后的情形 rollback

设置保存点

#设置保存点，相当于快照
savepoint 保存点名

#回滚到保存点
rollback to 保存点名

事务的隔离级别

数据并发问题

数据在并发情况下可能会产生如下问题

脏写

对于两个事务A、B，如果事务A修改了另一个未提交的事务B修改过的数据就会产生脏写

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为’李四，然后Session A中的事务接着又把这条studentno列为1的记录的name列更新为’张三’。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。这时session A中的事务就没有效果了，明明把数据更新了，最后也提交事务了，最后看到的数据什么变化也没有。这里大家对事务的隔离级比较了解的话，会发现默认隔离级别下，上面SessionA中的更新语句会处于等待状态，这里只是跟大家说明一下会出现这样现象。

脏读

对于两个事务A、B，事务A读取了已经被事务B更新但还没有被提交的字段。之后若事务B回滚，事务A读取的内容就是临时且无效的

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为’张三’，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为’张三’，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

不可重复度

对于两个事务A、B，事务A读取了一个字段，然后事务B更新了该字段。之后事务A再次读取同一个字段，值就不同了。那就意味着发生了不可重复读

我们在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了)，这些事务都修改了studentno列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

幻读

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

SQL中的四种隔离级别

根据上面几种并发问题严重性排序后

脏写 > 脏读 > 不可重复读 > 幻读

设立一些隔离级别，隔离级别越低，并发问题发生的越多，SQL中设立了4种隔离级别

读未提交：所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读
读已提交：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是Mysql默认的）。可以避免脏读，但不可避免不可重复读、幻读
可重复读：在事务存在期间，不允许其他事务执行会影响这个事务正在访问的任何行的插入、更新或删除操作。可以避免脏读、不可重复读，但不可避免幻读，这是Mysql默认的隔离级别
可串行化：确保事务可以从一个表中读取相同的慌。在这个事务持续期间，禁止其他事务对该表执行增删改。所有并发问题都可避免，但性能十分低下

查看修改隔离级别

#查看隔离级别
show variables like 'transaction_isolation'

#修改隔离级别
set [global|session] transaction isolation level 隔离级别
#global 对全局范围进行设置，对当前会话无效
#session 对当前会话的所有后续事务有效

Mysql事务日志

事务的四种特性是基于什么机制实现的呢？

事务的隔离性由锁机制实现
事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证

redo和undo都可以被视为一种恢复操作，但是：

redo log 是存储引擎层生成的日志，记录的是“物理级别”上的页修改操作，比如页号、偏移量写入的数据。主要是为了保证数据的可靠性
undo log 是存储引擎层生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了insert语句操作，那么undo log就记录一条与之相反的delete操作。主要用于事务的回滚和一致性非锁定读

redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存种的缓冲池之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘，通过缓冲池来优化CPU和磁盘之间鸿沟，这样就可以保证整体的性能不会下降太快

为什么需要redo日志

由于刷盘操作并不是每次变更就触发，所以可能存在事务提交后，刚写完缓冲池，数据库就宕机了，那么这段数据就丢失了，如果没有redo日志将无法恢复

解决的思路就是将每一次操作记录下来，存放到一个文件中，下次恢复的时候根据文件中的操作重新操作一遍，并刷新到磁盘中

InnoDB引擎的事务采用了WAL技术，这种技术的思想就是先写日志，再写磁盘；只有当日志写入成功，才算事务提交成功。当发生宕机且数据未刷新到磁盘的时候，可以通过 redo log 来恢复

redo的整体流程

先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
当事务commitl时，将redo log buffer中的内容刷新到 redo log file，对redo log file采用追加
写的方式定期将内存中修改的数据刷新到磁盘中

redo log 的写入并不是直接写入磁盘的，InnoDB引擎会在写 redo log 的时候先写 redo log buffer ，之后以一定的频率刷入到真正的redo log file 中。

但是 redo log buffer 刷盘到 redo log file 的过程并不是真正的刷到磁盘中，只是刷入到文件系统缓存中，真正的写入会交给系统自己来决定；所以存在问题，如果交给系统来同步，如果系统宕机，那么数据也会丢失

针对这种情况，InnoDB可以通过修改innodb_flush_log_at_trx_commit参数来控制刷盘的策略

0：表示每次事务提交时不进行刷盘，每隔1秒进行刷盘

1：表示每次事务都将进行刷盘（默认）

2：表示每提交只是把内容写入文件系统缓存中，由系统决定什么时候同步到磁盘文件

undo日志

redo log 是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个 undo log

事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如

事务执行过程中可能会遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误
程序员手动进行回滚结束当前事务的执行

以上情况发生后，我们需要把数据改回原先的样子，这个过程称之为回滚，这样就可以保证原子性

undo日志的作用

回滚数据：undo是逻辑日志，因此只是将数据库逻辑恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同
MVCC：undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来实现。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此来实现非锁定读取

undo的存储结构

InnoDB对undo log的管理采用段的方式，也就是回滚段（rollback segment），每个回滚段记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等)，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。

Mysql并发事务访问同一条记录可能产生的问题

写-写问题：这种情况下会发生脏写问题，所以在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队过程其实是通过锁来实现的

当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。比如，事务T1要对这条记录做改动，就需要生成一个如下锁结构与之关联

当事务T1改动了这条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false，我们把这个场景就称之为获取锁成功，或者加锁成功，然后就可以继续执行操作了

在事务T1提交之前，另一个事务T2也想对该记录做改动，那么先看看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过锁结构的is_waiting属性值为true，表示当前事务需要等待，我们把这个场景就称之为获取锁失败，或者加锁失败
读-写问题：这种情况下可能会发生脏读、不可重复读、幻读；解决读-写问题有两种方案
- 读使用MVCC，写使用加锁
  
  所谓MVCC就是生成一个ReadView，通过ReadView找到合适的记录版本（历史版本有undo日志构建）。查询语句只能读到在生成ReadView之前已提交事务所做的更改。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突
普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
- 在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象
- 在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题
- 读写都加锁
  
  如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本，这就要对读写操作都加锁

以数据操作类型划分

对于上述读-写的问题，Mysql需要使用MVCC或加锁的方式来解决。在使用加锁方式解决问题时，由于既要允许读-读情况不受影响，又要写-写、读-写相互阻塞，所以Mysql实现一个由两种类型的锁组成的锁系统来解决，两种锁被称为读锁、写锁，也称为共享锁和排他锁

共享锁

读锁：针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互也是不阻塞的

排他锁

写锁：当前写操作没有完成前，它会阻断其他写锁和读锁，这样就能确保在给定时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源

	排他锁	共享锁
排他锁	不兼容	不兼容
共享锁	不兼容	兼容

锁定读

#给查询增加共享锁
select ... for share 

#给查询增加排他锁
select ... for update

这里需要注意的是 for update 是对于扫描到的数据都加锁，所以在使用条件查询时，需要保证当作条件的字段是有索引的，否则没有索引变成全文检索的话会对扫描到的数据都加上锁

以数据操作的粒度划分

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但管理多是很消耗资源的事情。因此数据库系统需要在高并发响应和系统性能两方面进行平衡

表级锁

该锁会锁定整张表，它是MysQL中最基本的锁策略，并不依赖于存储引擎（不管你是Myql的什么存储引擎，对于表锁的策略都是一样的)，并且表锁是开销最小的策略（因为粒度比较大)。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

表级共享锁

#添加表级读锁
lock tables 表名 read

#解锁
unlock tables

表级排他锁

#添加表级读锁
lock tables 表名 write

#解锁
unlock tables

总结：MyISAM在执行查询语句(SELECT)前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁或者写锁的

意向锁是一种隐式的表级锁，不需要手动添加，行锁可以通过意向锁协调与表级锁并存

添加意向锁的意义在于，添加之后如果还有其他十五要添加表级锁的话只需要找到这个表是否被添加意向锁了之后就能判断能不能添加表级锁，如果没有意向锁，就需要一条条数据判断是否添加过行级排他锁，这样可以提升性能

这里的共享锁 / 排他锁指的都是表级别的锁，意向锁不与行级锁互斥

页级锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页级锁的开销介于表锁和行锁之间。

行级锁

行锁也称为记录锁。需要注意的是，Mysql服务器层并没有实现行锁机制，行级锁只在存储引擎层实现；行级锁也分成行级读锁、行级写锁

优点：锁定粒度小，发生锁冲突概率低，可以实现的并发度高

缺点：对于锁的开销比较大，加锁比较慢，容易出现死锁情况

行级共享锁 / 排他锁使用情景：

1、查询某一条数据且这条数据被另一个事务加了排他锁（更新会自动加上排他锁）:

select加锁：等待正在更新的事务执行完毕，查询到的是最新的值

select不加锁：查询的是最近一次提交后数据的值，也就是历史值，这跟隔离级别有关，因为Mysql默认隔离级别为可重复读，所以查询出来的是最近一次提交后数据的值

2、一个事务更新一条数据，另一个事务也更新则后来的等待

间隙锁

在可重复读的隔离级别下也是能解决幻读问题的，其中加锁的解决方案存在一个问题，就是事务在第一次执行读取操作时，那些幻影记录并不存在，我们无法给这些幻影记录加上记录锁。

InnoDB提出了一种间隙锁，简称gap锁；若给某一条数据加上了间隙锁，则意味着不允许别的事务在这条数据前面的间隙插入新记录，比如（1、2、3、6），如果给6加了间隙锁，那么不允许别的事务在（3，6）之间插入数据

间隙锁示例：

#事务一
select * from test where id = 5 for update（由于数据不存在则添加间隙锁）
#事务二
insert into test values(4,'jjw') #插入不成功

#gap锁的提出仅仅是为了防止插入幻影记录而提出的。虽然有共享gap锁和排他gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁（不论是共享gap锁还是独占gap锁)，并不会限制其他事务对这条记录加记录锁或者继续加gap锁。

由于幻读的定义是：幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。

所以当有一个业务是想要插入一条数据，先查询并加间隙锁，然后再插入。这期间由于加了间隙锁，所以其他事务不能在这个间隙进行插入，这样就解决了幻读问题

临键锁

有时候我们既想锁住某条记录，又想阻止其他事物在该记录前面的间隙插入新记录，所以InnoDB就提出了一种临键锁。临键锁是在事务级别为可重复读的情况下使用的数据库锁，InnoDB默认的锁就是临键锁

1	select * from test where id <= 10 and id > 8 for update（对[8，10]之间为间隙锁，10为行锁）

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

以对待锁的态度划分

从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，从名字中也可以看出这两种锁是两种看待数据并发的思维方式。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的设计思想

悲观锁

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程)。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized和ReentrantLock 等独占锁就是悲观锁思想的实现。例如 select .... for update 就是悲观锁的思想。

悲观锁不适用的场景较多，它存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制来实现，以保证程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

乐观锁

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者CAS机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

MVCC

什么是MVCC

MVCC，多版本并发控制，MVCC就是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

快照读

快照读读取都是快照数据据，不加锁的简单的select都属于快照读

之所以出现快照读的情况是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。快照读读取到的并不一定是数据的最新版本，可能是之前的历史版本

当前读

当前读读取的是记录的最新版本，读取时候还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的select，或者对数据进行增删改都会进行当前读

隐藏字段、undo log版本链

对于InnoDB存储引擎来说，它的聚簇索引记录中都包含两个必要的隐藏列

trx_id：每次一个事务对某条聚簇索引记录进行改动是，都会把该事务的事务id赋值给trx_id隐藏列
roll_pointer：每次对某条聚簇索引记录进行改动是，都会把旧版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

通过 roll_pointer串成一个版本链

ReadView

什么是ReadView

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。

ReadView就是某一个事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID(“活跃”指的就是，启动了但还没提交)。

ReadView中包含4个比较重要的内容：

creator_trx_id：创建这个ReadView的事务ID
trx_ids：生成ReadView时当前系统中活跃的读写事务的事务id列表（注意：仅仅是生成ReadView时活跃的事务id，之后开启的事务是不会算入的）
up_limit_id：活跃事务中最小的事务ID
low_limit_id：表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id是系统最大的事务id值，这里要注意的是系统中的事务id，需要区别于正在活跃的事务ID

ReadView的规则

以下面两图为参考

如果被访问版本的trx_id属性值与ReadView中的creator.trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_ids列表中。
- 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
- 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC整体操作流程

查询一条记录时，系统如何通过MVCC来找到

获取事务自己的版本号，也就是事务ID
获取ReadView
查询得到的数据，然后与ReadView中的事务版本号进行比较
如果不符合ReadView规则，就需要从undo log中获取历史快照
最后返回符合规则的数据

如果某个版本的数据仍然对当前事务不可见的话，那就顺着版本链往下找下一个版本的数据，继续按照上面的步骤判断可见性，以此类推

在隔离级别为读已提交时，一个事务中的每一次select查询都会重新获取一次ReadView，而可重复读不会