首页  思维导图  详情

MySQL知识全知道

2021-11-04 18:10:44   0  举报





AI智能生成

MySQL所有的知识点都整理到这里了，非常的详细，花了我一周时间整理出来的，很不容易，请多多点赞支持

MySQL

学习笔记

作者其他创作

大纲/内容

MySQL基础

MySQL 安装

MySQL 管理

MySQL 连接

MySQL 创建数据库

MySQL 删除数据库

MySQL 选择数据库

MySQL 数据类型

MySQL 创建数据表

MySQL 删除数据表

MySQL 插入数据

MySQL 查询数据

MySQL WHERE 子句

MySQL UPDATE 更新

MySQL DELETE 语句

MySQL LIKE 子句

MySQL UNION

MySQL 排序

MySQL 分组

MySQL 连接的使用

MySQL NULL 值处理

MySQL 正则表达式

MySQL 事务

MySQL ALTER命令

MySQL 索引

MySQL 临时表

MySQL 复制表

MySQL 元数据

MySQL 序列使用

MySQL 处理重复数据

MySQL 及 SQL 注入

MySQL 导出数据

MySQL 导入数据

MySQL 函数

MySQL 运算符

MySQL前进一步

数据库设计

数据库三范式

为了建立冗余较小、结构合理的表，设计数据库时必须遵循一定的规则

在关系型数据库中这种规则就称为范式

范式是符合某一种设计要求的总结

要想设计一个结构合理的关系型数据库，必须满足一定的范式。比较常见的是三范式，第一范式、第二范式和第三范式

第一范式

表中必须要有主键，不能出现重复记录，每个字段都是原子性的不能再分

不符合第一范式的实例

联系方式列不是原子性的，可以再次拆分成邮箱和手机号

解决方案

关于列不可再分，应该根据具体的业务情况来决定。但是一个表必须要有主键，且数据不能出现重复

第二范式

第二范式是建立在第一范式基础上的，要求所有非主键字段完全依赖主键，不能产生部分依赖，一张表只描述一件事

不符合第二范式的案例：其中学生编号和课程编号为联合主键

这张表描述了三件事：学生信息、课程信息、学生的成绩信息

虽然存在着主键（学生编号 + 课程编号），但是表中出现了大量冗余数据，例如课程名称中java、mysql、html多次出现

出现冗余的原因在于，学生信息部分依赖了主键的一个字段学生编号，和课程编号没有关系。同时课程的信息只是依赖课程id，和学生id没有关系。只有成绩一个字段完全依赖主键的两个部分，这就是第二范式部分依赖

上面的表其实是多对多的关系，多对多一般存在联合主键

解决方案：将表进行拆分：学生表、课程表、成绩表

学生表：学生编号为主键

课程表：课程编号为主键

成绩表：中间表，学生编号和课程编号为联合主键

第三范式

建立在第二范式基础上的，所有非主键字段和主键字段之间不能产生传递依赖

不满足第三范式的例子：其中学生编号是主键

何为传递依赖：专业编号依赖学生编号，应为该学生学的就是这个专业啊。但是专业名称和学生其实没多大关系，专业名称依赖于专业编号。某一个字段不直接依赖主键，而是另一个字段（外键），外键依赖于主键，形成传递依赖

解决方法

学生表，学生编号为主键

专业表，专业编号为主键

以上设计是典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方

总结

一张表必须要有主键，最好是和业务无关的字段，例如自增主键

一般而言所有的表都会满足第一范式，不满足第一范式的表一眼都能看出来

关于列是否必须为原子性，可以根据具体的业务需求来定

如果感觉表很奇怪，一般都是不满足第二范式，没有做到一张表只描述一件事

在设计表的时候一般满足第一和第二范式即可，如果需要冗余部分字段来减少关联查询可能不满足第三范式。由于进行了字段冗余，就需要必须要做到多张表数据的一致性

常见表关系

一对一

一个表和另一张表存在的关系是一对一

如何设计表

两张表共享主键，其实就是纵向拆分，将一张表拆成多张表，将部分字段拆分出去形成多张表

两张表，各自有主键，外键唯一

使用这种方式，一般存在主表和次表。主表的数据不会发生弃用，例如不会删除或者是status设置为0

次表的数据可能发生弃用

外键字段添加到哪里？

外键字段添加到主表中，引用次表的主键。可以清晰地看到使用的次表的数据

外键字段添加到次表中，关联主表的主键。可以看到主表使用的历史次表数据。需要保证次表数据只能引用一次主表数据（例如主表 A JOIN 次表B ON 主表A.bid = 次表B.id AND 次表B.status = 1）

一对多

第三范式的例子

如何设计表

两张表，外键建在多的一方，引用主表的主键

三张表，中间表分别引用另外两张表的主键形成联合主键，或者使用自增主键

这种可以记录两张表之间的引用关系

优点：如果表之间关系发生变更，从一对多变成多对多，这样不用修改表结构和数据

缺点：增加额外的表，查询起来较为复杂

多对多

第二范式的例子

如何设计表

三张表，外键设置在中间表，分别引用主表的主键

使用联合主键（引用另外两张表的外键）或者是自增主键

SQL的分类

DCL（数据库控制语言）

MySQL的ALL PRIVILEGES的权限列表

创建用户

给用户授权

撤销授权

查看权限

删除用户

DDL（Data Definition Language）

数据库操作

创建数据库

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

查询数据库

查询所有数据库

SHOW DATABASES

查询数据库建表时的sql

SHOW CREATE DATABASE db_name;

选择数据库

USE db_name;

删除数据库

DROP DATABASE db_name;

修改数据库的字符编码和排序方式

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

命令行设置之后操作的编码格式

SET NAMES UTF8

数据库表操作

创建表

SQL : CREATE TABLE tb_name (字段名、类型、长度、约束、默认、注释)

约束

主键

PRIMARY KEY

自增

AUTO_INCREMENT

唯一

UNIQUE

唯一约束，取值不允许重复，可以为NULL

并不是指只有一行字段为NULL，可以有多个为NULL

非空

NOT NULL

非负

UNSIGNED

只能用于数值

对于库存或者是余额意义的字段，建议加上该约束，如果程序出现错误新增或者修改后的值为负数，直接报错

外键

FOREIGN KEY

现在很少使用物理外键，一般使用逻辑外键

可以在开发环境或者测试环境加上外键，在正式环境去除外键

默认

DEFAULT

注释

COMMENT

表字段索引

唯一索引

添加

创建索引

CREATE UNIQUE INDEX index_name(field_name);

表字段修改

ALTER TABLE table_name ADD UNIQUE INDEX index_name(field_name);

删除

DROP INDEX index_name ON table_name

普通索引

添加

表字段修改

ALTER TABLE table_name ADD INDEX index_name(field_name);

创建索引

CREATE INDEX index_name ON table_name(field_name);

删除

DROP INDEX index_name ON table_name

主键

添加

ALTER TABLE table_name ADD PRIMARY KEY (field_name)

删除

ALTER TABLE table_name DROP PRIMARY KEY;

联合索引

添加

ALTER TABLE table_name ADDindex_name (field_name1, field_name2);

删除

DROP INDEXindex_name ONtable_name

修改表

表字段的增删改查

字段添加

ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT COMMENT 地址;

ALERT TABLE tb_name ADD 添加字段字段类型非空约束默认注释

字段类型修改

ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT COMMENT 地址;

ALERT TABLE tb_name MODIFY 字段名称新的字段类型非负非空默认注释

字段名称类型修改

ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT COMMENT 地址;

ALTER TABLE tb_name CHANGE 旧的字段名新的字段名新的类型约束默认注释

字段类型查询

DESC tb_name;

字段删除

ALTER TABLE tb_name DROP addr;

ALTER TABLE 表名 DROP 删除的字段名

表修改

表名修改

ALTER TABLE tb_name RENAME TO new_tb_name;

ALTER TABLE 旧表名 RENAME TO 新表名

引擎修改

ALTER TABLE tb_name ENGINE = InnoDB;

ALTER TABLE 表名 ENGINE = 新的引擎名称

删除表

DROP TABLE tb_name;

查询表

查询所有表

SHOW TABLES;

查询建表时的sql

SHOW CREATE TABLE tb_name;

DML（Data Manipulation Language）（重点）

增

添加单条

INSERT INTO tb_name(`field1`,`field2`,....) VALUES(value1,value2,.....);

添加多条

INSERT INTO tb_name(`field1`,`field2`,....) VALUES(value1,value2,.....), (value1,value2,.....),(value1,value2,.....),....;

删

sql

DELETE FROM tb_name WHERE ...

注意

删除时必须加WHERE条件

改

sql

UPDATE tb_name SET field1 = value1, field2 = value2, ..... WHERE ....

注意

修改时必须加WHERE条件

DQL（Data Query Language）（重点）

基础的查询

SELECT * FROM tb_name

WHERE子句

比较运算符

大于、小于、等于、不等于、大于等于、小于等于

SELECT * FROM tb_name WHERE user_id >10;

逻辑运算符

逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号

SELECT * FROM tb_name WHERE user_id > 10 AND sex = 男

LIKE模糊查询

通配符

%（百分号）匹配零个或者多个任意字符

_（下划线）匹配一个任意字符

sql

SELECT * FROM tb_name WHERE username LIKE 张%;查找username开头是张的数据

SELECT * FROM tb_name WHERE username LIKE %张%;查询username中含有张的数据

SELECT * FROM tb_name WHERE username LIKE %张;查询username字段的数据以张结尾的

SELECT * FROM tb_name WHERE username LIKE 张_;查询username以张开头后边有一个字符的数据

IN字段指定多个值查询

IN (value1, value2, value3, ....)

SELECT * FROM tb_name WHERE user_id IN (1, 3, 5, 7, 9, 11);查询user_id是1, 3, 5, 7, 9, 11的所有数据

BETWEEN AND 区间查询

field BETWEEN value1 AND value2

字段的值大于等于value1同时小于等于value2

SELECT * FROM user WHERE user_id BETWEEN 2 AND 9;查询user表中user_id大于等于2小于等于9的所有值

GROUP BY分组查询

聚合函数

COUNT(field)：获取符合条件出现的非NULL值的次数

COUNT(*)：统计所有行

SUM(field)：获取所有符合条件的数据的总和

AVG(field)，取平均值

MAX(field)，取最大值

MIN(field)，取最小值

GROUP_CONCAT(field)，可以将分组的字符串以", "连接起来

SELECT sex, COUNT(*) count FROM class GROUP BY sex;获取class表中男生和女生的数量

HAVING对聚合值进行过滤

对聚合值或者是字段进行过滤

WHERE不能对聚合值进行过滤

ORDER BY查询排序

查询顺序

ORDER BY field DESC;降序查询

ORDER BY field ASC;升序查询

SELECT * FROM tb_name ORDER BY id DESC; 查询tb_name表中所有数据，按id的降序来查找

ORDER BY是否使用索引的严格要求

通过有序索引顺序扫描直接返回有序数据，通过explain分析显示Using Index，不需要额外的排序，操作效率比较高

索引的顺序和ORDER BY子句的顺序完全一致

索引中所有列的方向（升序、降序）和ORDER BY子句完全一致

当多表连接查询时ORDER BY中的字段必须在关联表中的第一张表中

通过对返回数据进行排序，也就是FileSort排序，所有不是通过索引直接返回排序结果的都叫FileSort排序

FileSort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序中进行排序

如果内存装载不下，就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集

可以理解为归并排序

LIMIT查询结果截取

参数

LIMIT后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形

SELECT * FROM tb_name LIMIT 5;查询tb_name表中的所有数据，只要前边的5条数据

SELECT * FROM tb_name LIMIT 5, 5;查询tb_name中所有的数据，返回的结果是从第五条开始截取五条数据

分页查询一般会全表扫描，优化的目的应尽可能减少扫描；第一种思路：在索引上完成排序分页的操作，最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id，覆盖索引效率高些第二中思路：limit m,n 转换为 n之前分页查询是传pageNo页码, pageSize分页数量，当前页的最后一行对应的id即last_row_id，以及pageSize，这样先根据条件过滤掉last_row_id之前的数据，然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列，如果用于重复的列，那么分页数据将不准确

JOIN连接查询

JOIN连接查询总共有7种（内连接、左连接（全A）、右连接（全B）、左连接（只A）、右连接（只B）、全外连接、交叉外连接）

内连接：SELECT select...list FROM TableA A INNER JOIN TableB B ON A.Key=B.Key;

左连接（全A）：SELECT select...list FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;

右连接（全B）：SELECT select...list FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

左连接（只A）：SELECT select…list FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL;

右连接（只B）：SELECT select…list FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;

全外连接：SELECT select…list FROM TableA a FULL OUTER JOIN TableB b ON A.Key=B.Key;

由于MySQL不支持FULL JOIN这种语法（在orcale可行），所以使用UNION关键字拼接左连接（全A）和右连接（全B）结果并去重来达到效果

SELECT select...list FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;UNIONSELECT select...list FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

交叉外连接

同理，使用UNION关键字拼接左连接（只A）和右连接（只B）达到效果

SELECT select…list FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL;UNIONSELECT select…list FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;

JOIN时使用ON和WHERE过滤的区别

INNER JOIN

如果是内连接将过滤条件写在ON和WHERE的效果一样

LEFT JOIN

RIGHT JOIN

FULL JOIN

事务

关键词

BEGIN：开启事务

ROLLBACK：事务回滚

COMMIT：事务提交

必备条件

表的存储引擎为InnoDB

MySQL存储引擎（重点）

InnoDB存储引擎

从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了

具有如下特点

支持事务。默认的事务隔离级别为可重复度，通过MVCC（多并发版本控制）来实现读写不冲突

使用的锁粒度为行级锁，可以支持更高的并发，但是容易发生死锁

支持外键

在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度

对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，在.ibd文件中

MyISAM存储引擎

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少

具有如下特点

不支持事务

不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用

对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存

默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁

支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch、Solr、Sphinx等

InnoDB和MyISAM两种存储引擎的区别（重点）

事务

InnoDB支持事务

MyISAM不支持事务

外键

InnoDB支持外键

MyISAM不支持外键

锁

InnoDB有表锁和行锁，默认锁的粒度为行级锁，并发度更高，但是更容易发生死锁

MyISAM只有表锁，默认锁的粒度为表锁，并发度较低，但是发生死锁的概率较低

索引

InnoDB的索引是聚簇索引，索引和数据放在一起，B+树叶子节点放置主键和实际数据

MyISAM是非聚簇索引，索引文件和数据文件分开放置，B+树的叶子结点放置数据地址指针

文件

InnoDB的索引为聚簇索引，分为.frm（表空间）和.idb（数据和索引）两个文件

MyISAM的索引为非聚簇索引，分为.frm（表空间）、.myi（索引）和.myd（数据）三个文件

缓存

InnoDB查询会同时缓存索引和数据

MyISAM查询只有缓存索引

如何选择

使用场景是否需要事务支持

是否需要支持高并发，InnoDB的并发度远高于MyISAM

是否需要支持外键

高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引

索引，不同存储引擎的索引并不太一样

如果不知道如何选择，默认选择InnoDB即可

引擎类型

InnoDB

支持事务，具有提交、回滚和崩溃恢复能力，事务安全

MyISAM

不支持事务和外键，访问速度快

Memory

利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引

一旦关闭，数据就会丢失

Archive

归档类型引擎，仅能支持insert和select语句

csv

以csv文件进行数据存储，由于文件限制，所有列必须强制指定not null

不支持索引和分区，适合做数据交换的中间表

BlackHole

黑洞，只进不出，进来就是消失，所有插入数据都不会保存

Federated

可以访问远端MySQL数据库中的表，一个本地表，不保存数据，访问远程表内容

Merge MyISAM

一组MyISAM表的组合，这些表必须结构相同，Merge表本身没有数据，对Merge操作可以对一组MyISAM表进行

EXPLAIN（重点）

EXPLAIN关键字查看MySQL对SELECT类型的SQL的执行计划。从而知道MySQL是如何处理查询SQL的

当前测试的MySQL服务器版本为5.7.28，操作系统为Window10

语法

在原来的SELECT的SQL上增加EXPLAIN关键字即可

作用

查看表的读取顺序

查询类型

哪些索引被使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

测试使用的表

subject（学科表）

建表语句

create table subject( id int(10) auto_increment, name varchar(20), teacher_id int(10), primary key (id), index idx_teacher_id (teacher_id));

INSERT语句

insert into subject(name,teacher_id) values('math',1),('Chinese',2), ('English',3),('history',4);

teacher（教师表）

建表语句

create table teacher( id int(10) auto_increment, name varchar(20), teacher_no varchar(20), primary key (id), unique index unx_teacher_no (teacher_no(20)));alter table teacher add index idx_name(name(20));

INSERT语句

insert into teacher(name,teacher_no) values('wangsi','T2010001'), ('sunsi','T2010002'),('jiangsi','T2010003'),('zhousi','T2010004');

student（学生表）

建表语句

create table student( id int(10) auto_increment, name varchar(20), student_no varchar(20), primary key (id), unique index unx_student_no (student_no(20)));

INSERT语句

insert into student(name,student_no) values ('zhangsan','20200001'), ('lisi','20200002'),('yan','20200003'),('dede','20200004');

student_score（学生成绩表）

建表语句

create table student_score( id int(10) auto_increment, student_id int(10), subject_id int(10), score int(10), primary key (id), index idx_student_id (student_id), index idx_subject_id (subject_id));

INSERT语句

insert into student_score(student_id,subject_id,score) values(1,1,90),(1,2,60), (1,3,80),(1,4,100),(2,4,60),(2,3,50),(2,2,80),(2,1,90),(3,1,90),(3,4,100), (4,1,40),(4,2,80),(4,3,80),(4,5,100);

EXPLAIN EXTENDED

会在EXPLAIN的基础上额外提供一些查询优化信息

紧随其后SHOW WARNINGS命令可以查看优化后的查询语句，从而看出优化器优化了什么

EXPLAIN EXTENDED SELECT * FROM film WHERE id = 1;SHOW WARNINGS;

结果

在未来版本的MySQL可能会删除这个关键字

EXPLAIN PARTITIONS

相比EXPLAIN多了个PARTITIONS字段，如果查询是基于分区表的话，会显示查询将访问的分区

高版本的MySQL已经默认带上了PARTITIONS字段

EXPLAIN中的列

id：表示查询中执行SELECT子句或操作表的顺序

有几个SELECT就有几个id

执行顺序

id相同

执行顺序从上到下

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

执行计划

执行的顺序为teacher -> subject -> student_score，并不是按照书写的顺序查询的

id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

执行的顺序为teacher -> subject -> score

id相同又不同

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

执行的顺序为2.teacher -> 2.subject -> 1.subject -> 1.teacher

id的值为NULL

id为NULL的最后执行

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL

select_type：表示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询

SIMPLE

简单的SELECT查询，查询中不包括子查询或者UNION

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

执行计划

PRIMARY

查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

SUBQUERY

在select或where列表中包含了子查询

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

DERIVED

在FROM列表中，包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里

MySQL5.7+进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

当派生子查询存在以下操作时该特性无法生效：UNION 、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作

SQL语句

EXPLAIN SELECT t1.* FROM (SELECT * FROM subject WHERE id = 1 GROUP BY id) t1

执行计划

UNION

若第二个SELECT出现在UNION之后，则被标记为UNION

若UNION包含在FROM子句的子查询中，外侧SELECT将被标记为DERIVE

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

UNION RESULT

从UNION表获取结果的SELECT

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

table

显示数据来自于哪个表，有时不是真实的表的名字（对表取了别名，显示别名），虚拟表最后一位是数字，代表id为多少的查询

type（重点）

在表中找到所需行的方式NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > in dex_subquery > range > index > ALL。效率从高到低

掌握常见8种即可：NULL > system > const > eq_ref > ref > range > index > ALL

一般来说达到range级别就可以，最好达到ref级别

NULL

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

SQL语句

explain select min(id) from subject;

执行计划

const、system

当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问

如将主键或者是唯一键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

SQL语句

explain select * from teacher where teacher_no = 'T2010001';

执行计划

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;

执行计划

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id

执行计划

range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现between、<>、in等的查询

SQL语句

explain select * from subject where id between 1 and 3;

执行计划

index

Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的

通常见于索引覆盖或者是JOIN连接查询中（外键设置了索引）

SQL语句

explain select id from subject;

执行计划

ALL

Full Table Scan，将遍历全表以找到匹配行

SQL语句

explain select * from subject;

执行计划

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示NULL）

可能出现possible_keys有列，而显示NULL的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询

key（重点）

实际使用到的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

key_len计算规则如下

按照生成索引字段的数据类型来计算

MySQL常用数据类型（日期|时间、数值、字符串）

日期和时间类型

date：3字节，日期，格式：2014-09-18

time：3字节，时间，格式：08:42:30

datetime：8字节，日期时间，格式：2014-09-18 08:42:30，范围为1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp：4字节，自动存储记录修改的时间，时间戳，范围为1970-01-01 00:00:00/2038

year：1字节，年份

整型（有符号）

tinyint：1字节，范围（-128 ~ 127）

smallint：2字节，范围（-32768 ~ 32767）

mediumint：3字节，范围（-8388608 ~ 8388607）

int或integer：4字节，范围（-2147483648 ~ 2147483647）

bigint：8字节，范围（+|-9.22 * 10的18次方）

注意：上面定义的都是有符号的

加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了。例如tinyint unsigned的取值范围为0 ~ 255

表示数量的字段建议加上unsigned关键字，例如库存、余额等，如果程序计算库存小于0了，在UPDATE或者是INSERT的时候直接报错，保证库存不超卖

浮点型

float(m, d)：4字节，单精度浮点型，m总个数，d小数位

double(m, d)：8字节，双精度浮点型，m总个数，d小数位

decimal(m, d)：m + 2字节，decimal是存储为字符串的浮点数

字符串数据类型

char(n)：固定长度，最多255个字符

varchar(n)：可变长度，最多65535个字符

tinytext：可变长度，最多255个字符

text：可变长度，最多65535个字符

mediumtext：可变长度，最多2的24次方-1个字符

longtext：可变长度，最多2的32次方-1个字符

注意

char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候（UTF8）意味着可以插入n个中文，但是实际会占用n * 3个字节

同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n

超过char和varchar的n设置后，直接报错

对于char类型的字段不要设置的太大，如果不确定字段是否有值，建议设置成varchar(255)

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

生成索引的字段，建议设置非空约束。如果存在非空，建议使用其他值代替

不损失精确性的情况下，长度越短越好

对于字符类型的字段，如果没有必要使用全部数据，可以只是用字符前几位。例如INDEX(name(21))

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%

Extra（重要）

Using filesort

当查询中包含order by操作，而且无法利用索引完成的排序操作称为"文件排序"，这里的文件排序是指先在内存中进行排序，当内存排序无法完成时，使用临时文件帮助排序

出现了Using filesort就需要对SQL语句进行优化

SQL语句

explain select * from subject order by name;

执行计划

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见group by、order by、distinct、union等关键字

出现Using temporary，说明使用了临时表，可能需要对SQL语句进行优化

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

关于Using where、Using index、Using index & Using where、Using index condition详细解释说明：https://www.cnblogs.com/kerrycode/p/9909093.html

Using join buffer

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

Impossible where

这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used

Query语句中使用from dual 或不含任何from子句

缺点

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

索引（重点）

索引是什么

索引的基本操作

索引命名

普通索引：idx_字段1_字段2_...字段N

唯一索引：ux_字段1_字段2_...字段N

新增

建表时

INDEX `索引名`(字段1, 字段2(使用的长度)) USING BTREE

建表后

ALTER TABLE db_name.tbl_name ADD INDEX idx_name(filed_name(20));

删除

ALTER TABLE db_name.tbl_name DROP INDEX idx_name;

使用索引进行查询

一般将索引列在WHERE或者是ON的后面，同时注意索引不要失效

使用索引的优点和缺点

优点

缺点

索引的分类（重点）

按照使用字段的个数

单列索引

主键索引、辅助索引

使用主键字段的索引就是主键索引，其他都是辅助索引

辅助索引以非主键字段生成的称为辅助索引，又称为次级索引，二级索引

聚簇索引、非聚簇索引

聚簇索引只有Innodb存储引擎支持，使用主键生成的索引

如果没有主键，使用第一个非空唯一键；如果也没有，默认生成row_id（6字节）作为主键

除了聚簇索引之外的索引，其他都是非聚簇索引

唯一索引（添加了唯一约束）

一个或者多个字段添加了唯一约束，形成唯一索引

全文索引

较少使用。一般使用ElasticSearch、Solr等搜索引擎代替

复合索引（联合索引、组合索引）

多个字段组成形成复合索引

按照使用字段是否为主键

主键索引

使用的字段为主键

辅助索引（次级索引、二级索引）

使用非主键的字段

按照索引和数据是否存储在一起（重点）

聚簇索引（聚集索引）

表的存储引擎为InnoDB，且使用主键或者非空唯一键或者默认row_id作为索引

非聚簇索引（非聚集索引）

表的存储引擎为InnoDB，除了主键或者非空唯一键或者row_id，之外的其他字段，作为索引列都是非聚簇索引

表的存储引擎为MyISAM，使用到的索引都为非聚簇索引

按照底层数据结构

B树索引（B树和B+树）

Hash索引

R-索引（空间索引）

全文索引（倒排索引）

各种数据结构

数组和链表

数组数据查询时，需要将数据全部加载到内存，如果数据量较大，占据大量内存。查询时需要进行全表扫描，获取数据

链表不使用大内存，查询时仍然需要进行全表扫描

hash

类似于Java中的HashMap，字段的值通过hash函数进行散列，然后使用链地址法解决hash冲突

可以快速进行等值查询，但是涉及到范围查找，只能进行全表扫描

在特定使用常见下合适，不适合大规模使用

二叉搜索树

简单定义

首先二叉搜索树也是一棵二叉树

二叉搜索树的任意结点A，其左子树的所有结点的值都小于结点A的值，其右子树的所有结点都大于结点A的值；前提是任意结点A的左右子树不为空

二叉搜索树的左右子树也是一棵二叉搜索树

二叉搜索树没有值相等的结点

特点

二叉搜索树所存储的元素必须具有可比较性，也就是说字段的值必须存在，不能为NULL

二叉搜索树搜索元素的时间复杂度为O(logN) ~ O(N)，N为元素的个数

图示

缺点

在顺序（递增或者递减）写入的情况下，会退化成链表。查询数据时需要全表扫描，时间复杂度为O(N)

图示

AVL树

由于二叉搜索树在最坏的情况下（顺序写入）会退化成链表，搜索时的时间复杂度高

这里AVL树在节点进行插入、删除、修改的时候进行了自平衡，让整棵树不至于过于倾斜

简单定义

树的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

某结点的左子树与右子树的高度（深度）差即为该结点的平衡因子（BF、Balance Factor）

特点

AVL树由于自平衡的存在，使得树的高度不会太高，平均搜索的时间复杂度为O(logN)

图示

右边二叉树中节点值为10的左右子树高度差为2

自平衡手段

如果插入新节点时发现左右子树的平衡因子的绝对值大于2，通过LL、LR、RR、RL的操作保证平衡因子的绝对值小于等于1

缺点

树的高度较高，需要多次IO操作

红黑树

红黑树存在的问题和AVL树类似，在于当数据量很大的时候，树的高度太高，IO的次数太多，效率较低

B树

前言

二叉树查找的时间复杂度为O(log N)，AVL树和红黑树等通过自平衡手段让二叉树不那么倾斜，但是二叉树的高度还是太高了，需要进行多次磁盘IO操作

为了减少磁盘IO的次数，必须降低树的深度，将瘦高的树变得矮胖;

基本的思路就是每个节点存储多个元素，摒弃二叉树结构使用多叉树

B树，这里的B表示balance（平衡的意思），B树是一种多路自平衡的搜索树。它类似普通的平衡二叉树，不同的一点是B树允许每个节点有更多的子节点

m阶B树（m为子树个数）基本定义

图示

特点

叶子结点和非叶子均存储数据

任何一个关键字只存在于一个结点中，也就是说关键字在B树内不会重复

按照索引查询，并不需要在叶子结点结束，可能在非叶子节点结束。B树的查询效率，最好为O(1)，最差为树的高度，查询效率不稳定

查询时，在关键字内全集内做一次查找，性能逼近二分查找

新增、修改、删除数据需要重新维护B树

B+树

B+树是B-树的变体，也是一种多路搜索树, 它与B树的不同之处在于

非叶子节点只存储关键字，叶子节点存储关键字和实际数据

关键字在整颗B+树内可以重复

非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字

数据库具体实现的时候，一般都会为所有叶子结点增加了双向指针

图示

特点

非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层

不可能非叶子节点命中返回，必须命中叶子结点返回（索引覆盖除外）。查询效率较为稳定，需要IO的次数就为B+树的高度

总结各种数据结构

前面讲述了大量的数据结构，最后发现B树和B+树是较为合理的，可以作为索引底层的数据结构

评价数据结构是否适合作为索引的标准就是查询数据时磁盘IO的次数，因为磁盘IO的速度比起内存IO要慢上几个数量级

由于B树在非叶子节点同时存储数据和关键字，造成一个节点能够存储的数据个数不会太多，那么B树的高度就会比较高，磁盘IO的次数就会较多

B+树非叶子节点只存储关键字，因此能够存储的关键字更多，B+树的高度就不会太高，一般为3 ~ 4层。因此B+树的高度比B树低，磁盘IO次数更少

综上所述B+树更适合作为索引底层的数据结构

扇区、磁盘块、内存页、局部性原理、磁盘预读、InnoDB数据页

扇区

硬盘的最小读写单元，一般是4KB

磁盘块

操作系统对硬盘读取的最小单元，一般是扇区的2的N次方

内存页

操作系统对内存操作的最小单元，一般是4KB

局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用

磁盘预读

程序运行期间所需要的数据通常比较集中

通常程序读取数据，并不是只读取需要的，而是将附近的数据都读取出来

通常是读取一整个磁盘块

InnoDB数据页

和操作系统读取磁盘类似，InnoDB读取数据是以页为单位进行读取的

页（Page）是Innodb存储引擎用于管理数据的最小磁盘单位

页大小默认为16KB

可以通过SQL：SHOW GLOBAL STATUS LIKE ;innodb_page_size ;;进行查看

综上所述，将B+树一个节点就设置成16KB，就是一个数据页大小。读取节点数据时将整个数据页一次性加载到内存，减少IO操作的次数

B+树根节点常驻内存，搜索时，遍历整个B+树需要进行磁盘IO的次数为h - 1（h为B+树的高度），一般B+树的高度一般为3 ~ 4层，那么只需要2 ~ 3次磁盘IO就可以获取数据。通常存储引擎会缓存索引，因此查询速度会更快

InnoDB和MyISAM存储引擎如何使用B+树索引

MyISAM

MyISAM所有的索引都是非聚簇索引

数据的存储不是按主键顺序存放的，按写入的顺序存放

主键索引和辅助索引

主键索引

图示

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意

可以看出MyISAM的索引文件非叶子节点存放主键，叶子结点存放主键和数据地址

辅助索引

图示

主键索引和辅助索引的区别在于主键不能重复，辅助索引的值可以重复

MyISAM存储引擎的索引文件和数据文件分开存放，因此也叫作非聚簇索引

如果表使用的存储引擎为MyISAM，存在三个文件.frm（表定义文件）、.MYD（数据文件）、.MYI（索引文件）

图示

InnoDB

只有InnoDB支持聚簇索引，只有InnoDB的主键索引是聚簇索引，除此之外的所有索引都是非聚簇索引

数据写入的顺序是按照主键的大小升序写入

主键索引和辅助索引

主键索引

图示

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有主键会去寻找第一个唯一非空键，如果也没有默认生成6字节的row_id作为主键。索引数据和真实数据放置在一起，在同一个文件中

非叶子节点存放关键字，叶子节点存放关键字以及实际数据

同时叶子节点键增加指向前后节点的顺序指针，对于范围查找非常方便

对于查找如果找到主键，那么就直接找到了整行数据，不需要进行额外IO操作

如果使用InnoDB存储引擎，存在两个文件.frm（表定义文件）和.idb（数据和索引文件）

图示

辅助索引

图示

关键字可以重复，非叶子节点存放关键字，叶子节点存放关键字和主键索引的值

辅助索引查找整行数据，需要先根据辅助索引的值进行定位到主键索引的值，然后去主键索引树查找整行数据，这里需要进行两次索引树的查找，因此整个过程叫回表

MySQL单表能够存储的数据量计算

这里以InnoDB的主键索引为例

B+树的一个节点大小为一个数据页，默认为16KB

B+树非叶子节点存储的数据为关键字和指针，主键字段的数据类型为bigint（8字节），同时存在着指向下一个主键的指针（6字节）

一个节点中主键的个数为16 * 1024 / (8 + 6) ≈ 1170个

叶子节点中存储主键和实际数据，一般而言实际数据大小远远大于主键大小，主键大小可以忽略不计。实际数据大小假定为1KB，那么叶子节点可以存储16KB / 1KB = 16条数据

通常B+树的高度为3层，整颗B+树能够存储的数据就是1170 * 1170 * 16 ≈ 2200 0000，大约等于2000万

也就是说MySQL单表能够存储的数据量大概在2000万左右

为什么MySQL推荐使用自增主键，而不推荐使用UUID或者雪花ID

这里主要都是InnoDB的主键索引，也就是聚簇索引

InnoDB存储引擎在写入数据时，按照主键的值进行顺序写入，也就是说如果主键的值如果不是自增（例如UUID杂乱无序），可能会将后面的数据，写入到前面的数据页中，可能造成数据页重建以及分裂

雪花ID的插入顺序略低于自增主键

使用自增ID的缺点

由于主键自增，容易被猜出业务增长量

在高并发写入情况下，可能造成对锁的争抢严重，造成写入性能下降

联合索引（复合索引）（重要）

联合索引指多个字段共同建立索引

对于经常查询的字段例如A、B、C，通常建立(A, B, C)的联合索引，而不是为每一个字段都建立单独的索引

为什么使用联合索引

减少索引开销

例如建立联合索引(A, B, C)实际上会建立索引(A)、(A, B)、(A, B, C)三个索引

最左匹配原则（重要）

如果使用了联合索引在进行WHERE过滤时，需要注意MySQL会一直向右进行匹配，遇到范围查找就停止（>、<、BETWEEN、LIKE）

比如WHERE a = 1 AND b = 2 c > 3 AND d = 4如果建立(a, b, c, d)顺序的索引，d是用不到索引的，如果建立(a, b, d, c)的索引则都可以用到，a、b、d的顺序可以任意调整

比如WHERE a = 1 AND b = 2 AND c = 3建立(a, b, c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式

索引覆盖（重要）

又称为覆盖索引，本质上是一种现象，并不是一种实际存在的索引

查找数据时，只查询索引的值，不查询其他数据，过滤时只通过索引列进行过滤，命中索引就会直接返回索引数据，不需要查询实际数据，大大提高了查询效率

A、B、C三个字段建立了联合索引，进行如下SQL的查询：SELECT A, B, C FROM tbl WHERE A = XXX AND B = XXX;

因此在写SQL时，不需要的字段没有必要查询出来，尤其要避免SELECT *的写法

然而理想很丰满，现实很骨感。这样的SQL业务场景非常有限，几乎没有

索引失效

联合索引，没有遵守最左前缀原则

联合索引，范围（>、<、BETWEEN AND）之后的字段索引失效

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转而向全表扫描

在高版本的MySQL似乎进行了优化

使用不等于（!=或者<>），索引失效

使用LIKE ;%XXX% ;进行模糊查询

可以使用LIKE ;abc% ;，避免全表扫描，前面必须要有字段

字符串不使用单引号索引失效

在高版本的MySQL似乎进行了优化

使用IS NULL或者IS NOT NULL进行WHERE过滤

InnoDB存储引擎允许字段的值为NULL，也可以添加索引

在MySQL不同版本，不同数据量的情况下，优化器会根据情况进行判断是否使用索引，因此存在着4不同的情况

一般而言MySQL的版本越高，基本上都会走索引

IN关键字

在不同版本的MySQL，情况不相同，可能会走，也可能不会走索引

OR关键字

如果想要OR也是用索引，过滤的每一列都需要添加索引

MySQL判断全表扫描比索引查找更快，索引失效

总结：上述的几种情况，在高版本的MySQL中都进行了优化，同时和数据量也有一定的关系，因此走不走索引并不是绝对的

索引使用事项

应该

对于经常WHERE、ORDER BY、GROUP BY、聚合函数的字段建立索引

外键字段建立索引

字段具有唯一性，建议生成唯一索引。在数据库的层面，保证数据正确性

对于经常一起出现的字段，推荐建立联合索引，需要注意最左前缀原则，将经常过滤的字段放在前面

能够使用覆盖索引，就不要查询出无用字段，减少回表操作

不应该

数据量不大，没有必要建索引，全表扫描可能更快

对于数据区分度不高的字段，不要建立索引

例如性别，一般而言只有男、女，建立索引的意义不大

可以使用SELECT COUNT(DISTINCT col) / COUNT(*)进行判断区分度

对于频繁发生修改的字段，不要建立索引

参与计算的列，不要建立索引

没有必要为每个字段建立索引，索引存储会消耗磁盘空间

常见面试题

B树和B+树有什么区别，为什么MySQL使用B+树作为索引底层的数据结构

MyISAM和InnoDB是如何使用B+树索引的

聚簇索引和非聚簇索引有什么区别

索引覆盖是什么？什么是回表？

事务（重点）

简介

在MySQL数据库中只有InnoDB存储引擎支持事务，MyISAM存储引擎不支持事务。默认的存储引擎是InnoDB

事务用来保证数据的完整性、正确性，操作的原子性以及并发访问时数据的隔离性

事务用来管理INSERT、UPDATE、DELETE等DML语句

事务必须手动开启、提交、回滚，也可以进行自动提交事务

事务的作用范围是一个SESSION中，一个SESSION中可以有多个事务。常见的SESSION：JDBC中的一个Connection对象，也就是一个线程；命令行窗口也是一个SESSION

事务有中有安全点，可以理解为将大事务拆分成小事务，回滚时只回滚到对应的安全点，并不是回滚全部回滚数据

事务四大特性（ACID）

原子性（Atomicity）

事务是一个操作最基本单元，其对数据的新增、修改、删除，要么全都执行，要么全都不执行，不会结束在中间某个环节

事务一旦被提交提交，在事务期间对数据的新增、修改、删除，必须全部执行

事务一旦被回滚，在事务期间对数据的新增、修改、删除，必须全部回退。新增数据，必须删除；修改数据必须恢复；删除数据必须重新回归，就好像这个事务从来没有被执行过一样

在一个SESSION中，也就是一个会话期间，事务既没有提交也没有回滚，当会话结束时，数据并不会发生修改

隔离性（Isolation）

数据库允许多个事务同时对相同的数据进行读取和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

持久性（Durable）

事务完成之后，它对于数据的修改是永久性的，即使系统出现故障也不会丢失

一致性（Consistent）

在事务开始之前和事务结束以后，数据库数据的完整性没有被破坏

这表示写入的数据必须完全符合所有的预设规则，这包含数据的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到）

原子性、隔离性、持久性三个特性实现一致性

更新丢失（Lost Update）

两个或者多个事务，同时对一个数据进行修改，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新

更新丢失问题有两类

第一类更新丢失

事务A的回滚覆盖了事务B已提交的数据

第二类更新丢失

事务A的提交覆盖了事务B提交的数据

第一类更新丢失是错误，必须要避免。MySQL已经解决，不用担心

两个事务同时根据主键id更新数据时，一个事务会获取意向排它锁（IX）、排它锁（X锁）和记录锁（Record Lock），X锁是排他性的，当前事务没有提交或者回滚，其他事务不允许操作当前数据

第二类更新丢失并不是错误，需要根据具体业务情况来定。MySQL在默认隔离级别（RR）没有解决第二类更新丢失

如果业务允许，那就无所谓了

但是在有些业务系统中是必须要避免的。例如余额表中的金额。多个事务并发修改同一个用户的余额，如果扣款前进行判断余额是否满足扣款，结果由于并发扣款，可能导致最后余额存在负数，这是绝对不允许的

常见的方式有乐观锁、悲观锁或者设置当前事务的隔离级别为串行化

乐观锁

乐观锁本质上是无锁的方式

增加版本号或者时间戳字段（记录的修改时间），进行失败重试的方式保证更新操作的正确性

对表记录修改前先进行查询，然后更新数据时，带上WHERE判断条件版本号或者是时间戳。同时判断是否执行成功，如果没有成功说明其他事务对当前数据进行了修改，进行失败重试，当达到最大重试次数直接报错，结束运行

案例

建表语句

CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `amount` int(10) unsigned DEFAULT NULL, `version` int(11) DEFAULT 1, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT语句

INSERT INTO `account`(`id`, `name`, `amount`, `version`, `create_time`, `update_time`) VALUES (1, 张三, 100, 1, 2021-03-18 11:31:33, 2021-03-18 11:31:33);

开启事务，START;

先查询数据，SELECT * FROM account WHERE id = 1;

业务处理后准备更新余额

UPDATE account SET amount = 200, version = version + 1 WHERE id = 1 AND amount = SELECT字段的值 AND version = SELECT字段的值 AND update_time = SELECT字段的值;

UPDATE语句会返回0或者1。0表示失败，1表示成功

如果返回0，表示没有更新成功，有其他事务进行了修改，可以直接抛出异常，进行事务回滚。或者进行失败重试，直到达到最大重试次数后抛出异常，结束程序

如果返回1，表示更新成功，程序结束

悲观锁

显式加排它锁SELECT ... FOR UPDATE，锁住当前数据

这样在当前事务还没有提交前，其他事务不能读取该数据

串行化

直接修改当前会话的事务隔离级别为串行化，让并发的事务，串行执行

事务串行执行肯定能保证更新操作成功执行

如果并发激烈，建议使用悲观锁，减少锁的冲突；如果并发不激烈，建议使用乐观锁；对于串行化，不建议使用，效率太低

事务控制语句

SELECT @@global.tx_isolation, @@tx_isolation;

查询全局、以及当前会话的事务隔离级别

SET AUTOCOMMIT= 0;

0表示禁止自动提交，1表示自动提交事务

BEGIN;

显式地开启一个事务

COMMITT;

提交事务，并使已对数据库进行的所有修改成为永久性的

ROLLBACK;

回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

SAVEPOINT identifier;

SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT

这对于复杂的大事务非常有帮助，例如批量新增1000万的数据，如果中间某一条失败，需要将1000万数据全部回滚吗

如果业务需求，只能全部回滚

如果不需要，可以设置一些保存点，将发生错误时，回滚到最近的保存点上，而不是回滚所有数据

RELEASE SAVEPOINT identifier;

删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常

ROLLBACK TO identifier;

把事务回滚到标记的保存点

SET TRANSACTION ISOLATION LEVEL 隔离级别;

修改事务隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ |SERIALIZABLE];

GLOBAL，设置全局事务隔离级别

SESSION，设置当前会话的隔离级别

如果GLOBAL和SESSION都不设置，表示修改的事务隔离级别将应用于当前session内的下一个还未开始的事务

事务隔离级别

分类

读未提交（Read uncommitted）

脏读/不可重复读/幻读都可能

读已提交（Read committed简称RC）

脏读不可能，不可重复读/幻读都可能

可重复读（Repeatable read简称RR）

不可重复读/脏读不可能，幻读都可能

可重复读的隔离级别下使用了MVCC机制，让读写不冲突，快照读的读视图相同，读取到的数据永远都是一样的

要避免幻读可以用间隙锁

串行化（Serializable）

脏读/不可重复读/幻读都不可能

MySQL中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到

脏读（Dirty Reads）

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求

不可重读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做不可重复读

一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为"幻读"

查看事务隔离级别

show variables like transaction_isolation;

select @@transaction_isolation;

InnoDB存储引擎默认的事务隔离级别是Repeatable read（可重复读），同时通过MVCC + Next-Key Lock可以解决幻读问题

演示事务隔离级别

测试数据

建表SQL

-- UNSIGNED代表无符号数，不能是负数

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, balance decimal(10,2) unsigned DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据SQL

INSERT INTO `user` VALUES (1, 张三, 200), (2, 李四, 200);

读未提交，出现脏读

SQL执行流程

脏读违反了一致性，是必须要避免的

读已提交，避免脏读和出现不可重复读

只能够读到其他事务已经提交的事务，但是通过相同的SQL语句，多次读取可能读到的数据不相同。可能是其他事务执行了UPDATE和DELETE语句

SQL执行流程

出现不可重复读并不是错误，这需要在业务进行判定，业务上是否允许

Oracle数据库默认的隔离级别就是读已提交

可重复读，避免脏读、避免不可重复读和出现幻读

可重复读SQL执行流程

出现幻读SQL执行流程

事务A先查询所有数据，发现只有2条数据，准备插入id为3的数据

事务B插入id为3的数据，并且提交事务

事务A插入id为3的数据，报主键重复错误

这里出现了幻读，事务A查询的时候明明没有id为3的数据，但是插入数据的时候却报主键冲突

再次查询仍然没有id = 3的数据，这是可重复读机制（MVCC实现）

MySQL默认事务隔离级别为可重复读

想要解决幻读可以显示加锁SELECT ... LOCK IN SHARE MODE或者是SELECT ... FOR UPDATE

串行化，避免脏读、避免不可重复读和避免幻读

将写操作进行串行化处理，多个读操作可以同时进行，读写冲突，写写冲突，直到其中一个事务提交前

SQL执行流程

InnoDB存储引擎如何实现四大特性

原子性

undo log

隔离性

MVCC（依赖于undo log、read view、数据行的隐式字段（trx_id、回滚指针））、锁

读未提交

事务A还没有提交时，释放排它锁。事务B能够读取该数据

读已提交

事务A提交后，释放排它锁。事务B只能够读取到已提交的数据。同时read view在每次快照读重新生成一份，每次读取已提交的数据

可重复读

每次快照读复用第一次快照读生成的read view，因此每次读到的数据相同

串行化

对于每个写操作串行处理

持久性

redo log

一致性

通过原子性、隔离性、持久性实现一致性

关系型数据中的ACID与分布式理论中的CAP中，这两个C有什么区别

ACID中的C是一致性，在一个事务中，多次对数据进行操作后，数据仍然保证正确性，并不会凭空增加、减少或者消失

CAP中C也是一致性，主要指在分布式系统中，一份数据要在多个服务间进行同步。数据的同步是需要时间，复制数据可能失败，需要保证多个服务间数据的一致性。常见的有强一致性、弱一致性和最终一致性

二者目的不同，因而达到的效果也不同。ACID更强调的是单个数据的正确性、完整性，CAP更强调的是多个服务间数据的一致性

MVCC（重点）

MVCC英文全称Multi-Version Concurrency Control，中文翻译多版本并发控制，是一种用来解决读 - 写冲突的无锁并发控制技术，同时也是解决事务中隔离性的关键

MVCC并不是为每个事务将数据全量复制一份，每个事务只读自己的数据，这样的效率太低，性能太差

InnoDB存储引擎为每个事务都分配一个自增的事务id。在事务中修改数据时，并不是覆盖修改原有数据，而是保持原有数据不变，生成一个新的数据。每次修改或者删除都产生一个新的版本（这就是多版本的含义）指向旧版本的数据，形成一个版本链。修改或者删除的数据版本与当时操作的事务id关联。Read View（读视图）决定当前事务能够读取的数据版本，它包含了多个事务id。根据读视图从数据版本链中选择合适的数据版本让当前事务读，这样即使其他事务在写，但是不会阻塞读请求，可以读取历史版本的数据，让读写不冲突

注意：begin/start transaction命令并不是一个事务的起点，执行UPDATE、DELETE、INSERT语句，事务才真正启动，才会向MySQL申请事务id。SELECT语句是不会分配事务id的。MySQL内部是严格按照事务的启动顺序来分配事务id的

数据库并发场景分为三种

读 - 读

不存在任何问题，也不需要任何并发控制

读 - 写

可能存在数据安全问题，因为一个事务在读，另外一个事务在写。从事务的隔离级别这个角度而言，可能产生脏读、不可重复读和幻读

写 - 写

可能存在数据安全问题，多个事务同时对同一个数据进行修改可能产生更新丢失问题，也就是第一类更新丢失和第二类更新丢失

MySQL数据库本身已经解决了第一类更新丢失

第二类更新丢失可以通过悲观锁或者乐观锁（版本号或者时间戳）进行解决

MVCC的作用

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写能力

同时还可以解决脏读、不可重复读、幻读事务隔离问题，但是不能解决第二类更新丢失问题。第二类更新问题需要使用悲观锁或者是乐观锁解决

小结一下：MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读 - 写冲突问题，而提出解决方案。所以在数据库中有了MVCC，可以形成如下两种组合

MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突

MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

当前读和快照读

在学习MVCC之前需要先了解一下MySQL中InnoDB存储引擎下的当前读和快照读

当前读

像SELECT LOCK IN SHARE MODE（共享锁）、SELECT FOR UPDATE、UPDATE、INSERT、DELETE（排它锁）这些操作都是当前读

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

快照读

快照读就是普通的SELECT，即不加锁的非阻塞读

快照读的前提是事务隔离级别不是串行，在串行级别下快照读会退化成当前读

快照读是为了提高并发度，让读写不冲突

快照读是基于MVCC实现，读到的是某一个版本的数据，既然是某一个版本的数据，就并不一定是最新的数据，可能是历史数据

案例

案例1

SQL执行顺序

在上表的顺序下，事务B的在事务A提交修改后的快照读是旧版本数据，而当前读是实时新数据400

案例2

SQL执行顺序

在这里的顺序中，事务B在事务A提交后的快照读和当前读都是实时的新数据400

快照读非常依赖于读操作首次出现的时机，它有决定该事务后续快照读结果的能力

MVCC实现原理

MVCC主要通过表数据中的3个隐式字段、undo log、Read View来实现

表数据3个隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

DB_TRX_ID

6字节，最近修改（修改/插入）事务id，记录创建这条记录/最后一次修改该记录的事务id

DB_ROLL_PTR

7字节，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

DB_ROW_ID

6字节，隐含的自增id（隐藏主键），如果表没有主键，InnoDB会自动以DB_ROW_ID生成一个聚簇索引

删除flag隐藏字段

实际上还有一个删除flag隐藏字段，既记录被删除并不代表真的删除，而是删除flag变了

记录图示

undo log

undo log主要分为两种insert undo log、update undo log

insert undo log

代表事务在insert新数据时产生的undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log

事务在进行update或者delete时产生的undo log

不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除

只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge线程

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的delete_bit，并不真正将过时的记录删除

为了节省磁盘空间，InnoDB有专门的purge线程来清理delete_bit为true的记录

为了不影响MVCC的正常工作，purge线程自己维护了一个read view（这个read view相当于系统中最老活跃事务的read view）

如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的

对MVCC有帮助的实质是update undo log，undo log实际上就是存在rollback segment中旧记录链

update undo log执行流程

比如persion表有一条记录，记录如下，name为Jerry，age为24岁，隐式主键是1，事务id和回滚指针，我们假设为NULL

现在来了一个事务1对该记录的name做出了修改，改为Tom

在事务1修改该行（记录）数据时，数据库会先对该行加排他锁

然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本

拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务id为当前事务1的id，我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，即表示我的上一个版本就是它

事务提交后，释放锁

图示

又来了个事务2修改person表的同一个记录，将age修改为30岁

在事务2修改该行数据时，数据库也先为该行加锁

然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面

修改该行age为30岁，并且修改隐藏字段的事务id为当前事务2的id，那就是2 ，回滚指针指向刚刚拷贝到undo log的副本记录

事务提交，释放锁

图示

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表

undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

Read View（读视图）

事务在进行快照读操作的时候产生的读视图，在该事务执行快照读的那一刻，会生成数据库系统当前对于事务的一个快照，记录并维护系统当前活跃事务的id

读视图并不是事务开始后就生成了，而是在进行快照读的时候才会产生，当前读（加排它锁）不会产生读视图

读视图的作用就是通过记录的事务id，在数据版本链中寻找一个合适的版本让当前事务读

在不同的隔离级别下，读视图也不相同

在可重复读隔离级别下，不管有多少个快照读，使用的都是第一个快照读进行时生成的读视图，读视图从始至终都不会发生变化

在读已提交隔离级别下，每进行一次快照读，都会产生一个读视图，也就是说每次快照读，读视图都不相同，读视图随着执行快照读而发生变化

综上所述，在读已提交隔离级别下，即使是相同的SELECT语句，由于读视图可能不同（每次产生新的读视图），读取到的数据可能不同；在可重复读隔离级别下，相同的SELECT语句，读视图一定相同（沿用第一次快照读产生的读视图），读取到的数据一定相同。这也是可重复读的含义，相同的SELECT语句，查询的数据一定相同

读视图由查询时所有未提交事务id数组（数组里最小的id为min_id）和已提交的最大事务id（max_id）组成。查询的数据结果需要和读视图做对比从而得到快照结果

事务id和事务是否提交的关系图

版本链的比对规则

trx_id并不是指当前session的事务id，而是版本链中数据行对应生成该数据的事务id

creator_trx_id：表示生成该ReadView的事务的事务id

如果是只读事务，那么该事务的事务id就是0

如果被访问版本的trx_id与creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问

当前事务自己修改的数据，对自己一定是可见的

如果落在绿色部分（trx_id < min_id），表示这个版本是已提交的事务生成的，这个数据是可见的

如果落在红色部分（trx_id > max_id），表示这个版本是由将来启动的事务生成的，是肯定不可见的

如果落在黄色部分（min_id <= trx_id <= max_id）那就包含两种情况

若trx_id在数组中，表示这个版本是由还没有提交的事务生成的，对当前事务不可见

若trx_id不在数组中，表示这个版本是已经提交的事务生成的，对当前事务可见

如果某个版本的数据对当前事务不可见，根据回滚指针，需要顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，直到找到版本链中的最后一条数据。如果最后一个版本也不可见，那意味着该条记录对该事务完全不可见，查询结果不包含该记录

对于删除的情况可以认为是update的特殊情况。会将版本链上最新的数据复制一份，然后将trx_id修改成操作的trx_id，同时在该记录的头信息（record header）里的（deleted flag）标记位写上true，意味着记录已被删除，不返回数据

整体流程

前面理解了隐式字段、undo log、Read View、当前读和快照读的概念后，整体来看MVCC的执行流程

假定存在5个session，其中三个写事务，两个读事务（默认事务隔离级别是可重复读）

三个写事务和其中一个读事务（select1）

SQL语句执行顺序

第一次快照读

select 1第一次进行快照读生成读视图readview[100, 200], 300。当前活跃事务id为100和200，已提交的最大事务id为300。min_id为100，max_id为300

此时undo log版本链。第三列是生成改行数据的事务id

版本链的比对规则

第一条数据的row的trx_id为300

min_id <= trx_id <= max_id同时trx_id不在未提交事务id的数组[100, 200]中，因此该条数据对当前事务可见，停止查找直接返回数据

查询到的数据就是lilei300

第二次快照读

由于是可重复读，读视图沿用第一次快照读的读视图readview[100, 200], 300

此时undo log版本链

版本链的比对规则

第一条也是最新的一条数据，trx_id为100。100在事务活跃数组[100, 200]中，属于事务未提交的修改，对当前事务不可见

根据回滚指针，找到第二条数据，trx_id为100。100在事务活跃数组[100, 200]中，属于事务未提交的修改，对当前事务不可见

根据回滚指针，找到第三条数据，trx_id为100。min_id <= 100 <= max_id同时trx_id不在未提交事务id的数组[100, 200]中，因此该条数据对当前事务可见，停止查找直接返回数据

第三次快照读

读视图仍然沿用第一次快照读生成的读视图，由于读视图不变，根据版本比对规则，到最后也会找到lilei300这条数据

在可重复读隔离级别下，三次快照读，无论其他事务是否提交事务，读到的数据均相同，满足了可重复读的语义，根本的原因是在RR隔离级别下，读视图均沿用第一次快照读生成的读视图

三个写事务和其中一个读事务（select2）

SQL语句执行顺序

undo log日志数据版本链

进行快照读生成读视图readview[200], 300。此时活跃事务id为200，已提交的最大事务id为300。min_id为200，max_id为300

版本比对规则

第一条数据trx_id为200，在未提交事务id数组[200]中，对当前事务不可见

顺着回滚指针找到第二条数据，trx_id为200，对当前事务同样不可见

顺着回滚指针找到第三条数据，trx_id为100，小于min_id200，属于已提交的事务，对当前事务可见，停止查找，返回lilei2

RC、RR级别下的InnoDB快照读有什么不同

在RC隔离级别下，是每个快照读都会生成并获取最新的Read View

在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是第一次快照读生成的Read View

正是由于在不同事务隔离级别下，Read View可能不同（活跃事务提交），造成读取的数据可能不同

总结

多个事务对同一条数据进行修改或者删除时，通过undo log形成数据版本链的链表，版本数据中有额外的隐式字段事务id，回滚指针。回滚指针指向老数据，形成链表，链表的头部就是最新的数据，尾部就是最老的数据

读视图就是对当前系统中所有事务进行一个快照，包含已提交和未提交事务，用来判断数据的可见性。读视图在RC和RR隔离级别生成时机不同

查找数据时，到undo log日志中进行查找，根据读视图，进行版本比对，判断可见性，找到合适的数据，进行返回

MVCC是避免读写冲突的关键技术，同时也是实现事务隔离性的关键技术

锁（重点）

数据库中的锁是为了保证并发访问时数据的一致性，使各种共享资源在被访问时变得有序而设计的一种规则

MySQL中关于锁的知识和事务隔离级别、索引、MVCC杂合在一起，显得非常乱。且锁的各种名词让人眼花缭乱

锁的分类

加锁机制

乐观锁

本质上是无锁的方式，总是乐观地认为不会发生锁冲突，如果发现更新失败，则进行失败重试，直到达到最大重试次数，回滚事务

根据版本号或者是时间戳控制

悲观锁

只要是加了锁都是悲观锁

锁定表或者行，让其他数据操作等待

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响

不能进行写操作

写锁（排他锁）

当前写操作没有完成前，它会阻断其他写锁和读锁

锁粒度

表锁

表锁是指对一整张表加锁。表锁由MySQL Server层实现

行锁

行锁是锁定某行、某几行或者行之间的间隙，由存储引擎实现，不同存储引擎实现不同。目前只有InnoDB存储引擎支持行锁，如没有特殊说明，行锁就是指InnoDB存储引擎的行锁

存储引擎

InnoDB存储引擎

行锁

兼容性

共享锁（Share Lock，简称S锁）

加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁

排它锁（Exclusive Lock，简称X锁）

允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

表锁

意向锁

读意向锁（IS锁）

写意向锁（IX锁）

获取共享锁、排它锁之前需要先获取意向共享锁、意向排它锁

MyISAM存储引擎

表共享读锁（Table Read Lock）

表独享写锁（Table Write Lock）

InnoDB锁详细解释

行锁

兼容性

共享锁（Share Lock，简称S锁）

普通的SELECT语句不会加共享锁

想要显式加共享锁，可以加SELECT ... LOCK IN SHARE MODE子句

为了确保自己查询的数据一定是最新的数据，不会被其他事务进行修改

被读取的行记录或者行范围其他SESSION可以读，也可以加共享锁，但是其他事务无法获取排它锁，也就是说S锁不冲突，S锁和X锁冲突

其他事务可以进行普通的SELECT、SELECT ... LOCK IN SHARE MODE，但是不能进行UPDATE、DELETE、INSERT操作

排它锁（Exclusive Lock，简称X锁）

普通的UPDATE、INSERT、DELETE语句都会加排它锁

想要对SELECT语句显式加排它锁，可以加SELECT ... FOR UPDATE子句，相当于UPDATE语句

保证读取到的数据一定是最新的，不允许其他事务进行修改

其他事务可以进行普通的SELECT，但是不能进行SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT操作

LOCK IN SHARE MODE和FOR UPDATE的相同和不同

相同

二者都可以读取到最新的数据，保证不让其他事务进行修改

可以让普通的SELECT读，UPDATE、DELETE和INSERT语句阻塞

不同

共享锁和共享锁不是互斥的，排它锁和排它锁、共享锁和排它锁是互斥的

两个事务同时进行LOCK IN SHARE MODE，且对锁住的数据执行UPDATE语句，会造成死锁

排它锁只有一个事务能进行FOR UPDATE，不会发生死锁

LOCK IN SHARE MODE适用于并发度低，且不会执行UPDATE锁住数据的场景，FOR UPDATE适用于并发度高，且执行UPDATE锁住数据的场景

锁模式

记录锁（Record Lock）

更新数据时根据索引进行更新。记录锁最简单的一种行锁形式，记录锁是加在索引上的

如果更新语句中WHERE过滤条件不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意

间隙锁（Gap Lock）

当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁

而对于键值在条件范围内但并不存在的记录，就叫做间隙，InnoDB在此时也会对间隙加锁，这种记录锁+间隙锁的机制叫Next-Key Lock

间隙锁是一个索引值的左开右开的区间

临键锁（Next-key Lock）

临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开右闭的区间

记录锁、间隙锁、临建锁之间的关系示意图

插入意向锁（Insert Intention Lock）

插入意图锁是一种间隙锁，在行执行INSERT之前的插入操作设置

如果多个事务INSERT到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突

锁模式下各种锁之间的兼容矩阵

第一行表示已有的锁，第一列表示要加的锁

插入意向锁不影响其他任何锁

间隙锁和Next-Key与插入意向锁冲突

间隙锁和除了插入意向锁之外的锁都不冲突

记录锁和记录锁冲突，记录锁和Next-key冲突，Next-key和Next-key冲突

表锁

读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，必须要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）

当事务要在记录上加上行锁时，要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率

意向锁是InnoDB自动加的，不需要用户干预

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的

读意向锁、写意向锁、自增锁、共享锁、排它锁之间的兼容性

第一行是已存在的锁，第一列是想要获取的锁

总结

当不存在冲突时，InnoDB存储引擎并不会默认生成锁，而是当多个事务冲突后才会生成锁

表锁为意向锁，意向锁主要是为了简化表锁和行锁之间的逻辑，表锁是InooDB存储引擎自己加上的，一般不用关注

较为复杂的是行锁，行锁有两种模式，一种是S锁，一种是X锁。行锁的类型又可以细分成记录锁、间隙锁、临建锁等

如何描述一个行锁呢？现有锁的模式（共享、排他），然后有锁的类型。例如共享记录锁、排他记录锁

可以在information_schema.INNODB_LOCKS系统表中查看当前InnoDB存储引擎中存在的锁

lock_mode，表示锁模式，主要有S、X、IS、IX、GAP、AUTO_INC

lock_type，表示锁类型，主要有Record Lock、Next-key Lock、Insert Intention Lock

常见加锁场景分析

一般而言表使用的存储引擎都是InnoDB，下面所有案例都是InnoDB存储引擎，这里只描述行锁中的X锁

执行SQL后究竟加什么锁和事务隔离级别、索引、是否命中数据均存在关系

事务隔离剂级别取读已提交和可重复读

索引取主键索引、二级唯一索引、二级非唯一索引

具体场景分析

建表语句

CREATE TABLE `book` ( `id` int(11) NOT NULL, `isbn` varchar(255) DEFAULT NULL, `author` varchar(255) DEFAULT NULL, `score` double(2,1) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `isbn_unique_index` (`isbn`) USING BTREE, KEY `author_index` (`author`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据SQL

INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (10, N0001, Bob, 3.4);INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (18, N0002, Alice, 7.7);INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (25, N0003, Jim, 5.0);INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (30, N0004, Eric, 9.1);INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (41, N0005, Tom, 2.2);INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (49, N0006, Tom, 8.3);INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (60, N0007, , 8.1);

案例1：聚簇索引，查询命中

SQL执行流程图

UPDATE语句会获取X锁，同时根据主键id，命中会加记录锁

记录锁和记录锁之间是冲突的

加锁记录

由于Record Lock之间冲突，所以在information_schema.INNODB_LOCKS才能看到

在RC和RR隔离等级下的加锁，两种隔离等级下没有任何区别，都是对id = 10这个索引加排他记录锁

案例2：聚簇索引，查询未命中

SQL执行流程图

间隙锁和插入意向锁，如果插入的数据在间隙锁的区间内就冲突，否则不冲突

在RC隔离等级下，不需要加锁。而在RR隔离级别会在id = 16前后两个索引（(10, 18)）之间加上间隙锁

加锁记录

案例3：二级唯一索引，查询命中

SQL执行流程

在InnoDB存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁，无论是RC还是RR隔离级别

加锁记录

案例4：二级唯一索引，查询未命中

SQL执行流程

在RR隔离等级下未命中时的加锁情况，RC隔离等级下该语句未命中不会加锁。在 N0007 和 Suprenum Record 之间加了间隙锁

加锁记录

在SHOW EGINE INNODB STATUS的日志中出现了插入意向锁等待间隙锁

案例5：二级非唯一索引，查询命中

案例6：二级非唯一索引，查询未命中

案例7：无索引

案例8：聚簇索引，范围查询

案例9：二级索引，范围查询

案例10：修改索引值

案例11：DELETE语句加锁分析

案例12：INSERT语句加锁分析

行锁分析

show status likeinnodb_row_lock%;

Innodb_row_ lock_current_wait

当前正在等待锁定的数量

Innodb_row_ lock_time

从系统启动到现在锁定总时间长度

Innodb_row_ lock_time_avg

每次等待所花平均时间

Innodb_row_ lock_time_max

从系统启动到现在等待最长的一次所花时间

Innodb_row_ lock_waits

系统启动后到现在总共等待的次数

死锁

Session _1执行：select *from account where i d= 1 for update;Session _2执行：select *from account where i d= 2 for update;Session _1执行：select *from account where i d= 2 for update;Session _2执行：select *from account where i d= 1 for update;查看近期死锁日志信息：show engine innodb status;

undo log、redo log和binlog（重点）

binlog

简介

binlog用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中

binlog是MySQL的逻辑日志，并且由Server层进行记录，使用任何存储引擎的MySQL数据库都会记录binlog日志

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志

使用场景

在实际应用中， binlog的主要使用场景有两个，分别是主从复制和数据恢复

主从复制

在Master端开启 binlog ，然后将binlog发送到各个Slave端， Slave端重放binlog从而达到主从数据一致

数据恢复

通过使用mysqlbinlog工具来恢复数据

数据同步

例如canal监听mysql的binlog，然后将数据同步数据源中，例如将mysql数据导入到hive中

binlog刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢

mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N

0：不去强制要求，由系统自行判断何时写入磁盘

1：每次commit的时候都要将binlog写入磁盘

N：每N个事务，才会将binlog写入磁盘

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值

但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能

binlog日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定

STATMENT

基于SQL语句的复制（statement-based replication, SBR），每一条会修改数据的SQL语句会记录到binlog中

优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO , 从而提高了性能

缺点

在某些情况下会导致主从数据不一致，比如执行sysdate() 、slepp() 等

ROW

基于行的复制（row-based replication, RBR），不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

优点

不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题

缺点

会产生大量的日志，尤其是`alter table` 的时候会让日志暴涨

MIXED

基于STATMENT和ROW两种模式的混合复制（mixed-based replication, MBR），一般的复制使用STATEMENT模式保存binlog ，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

redo log

为什么需要redo log

事务四大特性之一为持久性，只要事务提交成功，那么对数据库的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态

最简单的做法就是每次事务提交时，将事务涉及到修改的数据页全部刷新到磁盘中

上述做法存在着严重的性能问题

InnoDB是以页为单位与磁盘进行交互，一个数据页大小为16kb，一个事务可能只修改一个数据页里面几个字节，如果将完整的数据页刷新到磁盘，太浪费资源

一个事务可能涉及到多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差

常见做法是修改数据时，先将数据读取到内存的缓冲池中，然后进行修改。数据在内存中被修改，与磁盘中相比就存在了差异，这种有差异的数据成为脏页

因此MySQL设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了（相对而言文件更小并且是顺序IO）

redo log基本概念

redo log包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo logfile）

MySQL每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file

这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL（Write-Ahead Logging）技术

redo log刷写时机

用户空间（user space）下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过内核空间（kernel space）缓冲区（OS Buffer）

redo log buffer写入redo logfile实际上是先写入OS Buffer，然后通过系统调用fsync()将其刷到redo log file中

MySQL支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下

0（延迟写）

1（实时写，实时刷）

2（实时写，延迟刷）

redo log记录形式

redo log和binlog的区别

文件大小

redo log的大小固定

binlog可通过参数max_binlog_size设置每个binlog文件的大小

实现方式

redo log是InnoDB引擎层实现的，并不是所有引擎都有

binlog是Server层实现，所有引擎都可以使用binlog日志

记录方式

redo log采用循环写的方式，当写到结尾时，会回到开头循环写日志

binlog通过追加的方式记录，当文件大小大于设定值后，后续日志会记录到新的文件上

使用场景

redo log适用于崩溃恢复（crash-safe）

binlog适用于主从复制和数据同步

undo log

定义

Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。

作用：

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

MySQL常见优化手段（重点）

步骤： 1.开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来; 2.EXPLAIN+慢SQL分析; 3.SHOW profile,查询SQL在MySQL服务器里面的执行细节和生命周期情况 4.具体优化

SHOW WARNINGS 在explain执行后执行，查看翻译后的sql

使用EXPLAIN关键字去查看SQL的执行计划

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

索引优化规则

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 使用索引 EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 未使用索引

存储引擎不能使用索引中范围条件右边的列 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 使用索引 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 未使用索引

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句 EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 只查询索引不用查询具体的数据，效率更高 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

is null,is not null 也无法使用索引

like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

字符串不加单引号索引失效 EXPLAIN SELECT * FROM employees WHERE name = '1000'; EXPLAIN SELECT * FROM employees WHERE name = 1000;

or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的

in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内

union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表

order by如果根据多个值进行排序，那么排序方式必须保持一致，要么同时升续，要么同时降续，排序方式不一致不走索引

优化方式

优化数据库表结构的设计

字段的数据类型

不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer

数据类型的长度

数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度

表的存储引擎

分库分表

数据库参数配置优化

主从复制，读写分离

数据库编码: 采用utf8mb4而不使用utf8

字段名

MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。 一般所有表都要有id, id必为主键，类型为bigint unsigned,单表时自增、步长为1; 有些特殊场景下(如在高并发的情况下该字段的自增可能对效率有比价大的影响)id是通过程序计算出来的一个唯一值而不是通过数据库自增长来实现的。 一般情况下主键id和业务没关系的，例如订单号不是主键id，一般是订单表中的其他字段，一般订单号order_code为字符类型 一般情况下每张表都有着四个字段create_id,create_time,update_id,update_time, 其中create_id表示创建者id，create_time表示创建时间，update_id表示更新者id，update_time表示更是时间，这四个字段的作用是为了能够追踪数据的来源和修改 最好不要使用备用字段(个人观点), 禁用保留字，如 desc、range、match、delayed 等 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1 表示是，0 表示否), 任何字段如果为非负数，必须是unsigned。表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除 如果某个值能通过其他字段能计算出来就不需要用个字段来存储，减少存储的数据 为了提高查询效率，可以适当的数据冗余，注意是适当 强烈建议不使用外键, 数据的完整性靠程序来保证 单条记录大小禁止超过8k，一方面字段不要太多，有的都能上百，甚至几百个，另一方面字段的内容不易过大像文章内容等这种超长内容的需要单独存到另一张表

字段类型

字符类型

不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。 innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高

数值类型

金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度 如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal 如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围 不建议使用ENUM、SET类型，使用TINYINT来代替

日期类型

根据实际需要选择能够满足应用的最小存储日期类型。 如果应用只需要记录年份，那么仅用一个字节的year类型。 如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31 如果记录时间时分秒使用它time类型 如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多 如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应 datetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6) timestamp与datetime 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。 都可以使用自动更新CURRENT_TIMESTAMP 对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。 timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’ datetime占用8个字节：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’ 总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大 注意： 禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算 也尽量不要使用int来存储时间戳

是否为null

MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。 在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”，而空值”“是不占用空间的。 含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。 联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义 注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。 1) NULL<>NULL的返回结果是NULL，而不是false。 2) NULL=NULL的返回结果是NULL，而不是true。 3) NULL<>1的返回结果是NULL，而不是true。

实例

EXPLAIN SELECT * FROM tbl_user LIMIT 100000,2; EXPLAIN SELECT * FROM tbl_user u INNER JOIN (SELECT id FROM tbl_user ORDER BY id ASC LIMIT 10000,2) temp ON u.id = temp.id; id为主键，性能高于第一条全表扫描

where中如果有多个过滤条件，在没有索引的情况下将过滤多的写在前面，过滤少的写在后面

禁止使用select *，需要什么字段就去取哪些字段

不要使用count(列名)或 count(常量)来替代 count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和非NULL无关。说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。避免使用存储过程、触发器

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

MySQL再进一步

MySQL基架

（1）MySQL向外提供的交互接口（Connectors）

Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。

（2）管理服务组件和工具组件(Management Service & Utilities)

提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等

（3）连接池组件(Connection Pool)

负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。

（4）SQL接口组件(SQL Interface)

接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。

（5）查询分析器组件(Parser)

首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。

主要功能：

a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的

b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

（6）优化器组件（Optimizer）

查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化

（7）缓存主件（Caches & Buffers）

查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

（8）插件式存储引擎（Pluggable Storage Engines）

（9）物理文件（File System）

MySQL内存结构

全局内存（Global buffer）

数据缓存

索引缓存

锁信息

插入缓存

重做日志缓存

额外的内存池

线程内存（Thread buffer）

Master Thread

IO Thread

Purage Thread

Page Cleaner Thread

MySQL文件结构

(1) 参数文件

启动MySQL实例的时候，指定一些初始化参数，比如：缓冲池大小、数据库文件路径、用户名密码等。

(2) 日志文件

比如：错误日志、二进制日志、慢查询日志、查询日志等等。

(3) socket文件

当用UNIX域套接字方式进行连接的时候需要的文件。

(4) pid文件

MySQL实例的进程ID文件。

(5) 表结构文件

表结构定义文件：

*.frm 文件是所有mysql数据库都有的文件，记录了该表的表结构定义。

表空间文件

一是系统表空间文件，包括 ibdata1、 ibdata2 等文件

储了 InnoDB 系统信息和用户数据库表数据和索引，是所有表公用的

另一个是.idb文件，是每张表独有的

(6) InnoDB存储引擎文件

表空间文件

nnoDB采用将存储的数据按表空间（tablespace）进行存放和设计, 在默认配置下会有一个10MB大小的文件，名为ibdata1文件，该文件就是默认的表空间文件(tablespace file)，用户可以通过参数innodb_data_file_path进行设置。

innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

（1）共享表空间：

享表空间文件以.ibdata*来命名；共享表空间下，innodb所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

（2）独立表空间

每个表拥有自己独立的表空间用来存储数据和索引。

重做日志文件(Redo log file)

参数

innodb_log_file_size

innodb_log_file_in_group

innodb_mirrored_log_groups

innodb_log_group_home_dir

每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组至少有2个重做日志文件，如默认的id_logfile0和ib_logfile1。

用户可以设置多个的镜像日志组（mirrored log groups)，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性

在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。

InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，再当重做日志文件2也被写满时，会再切换到重做日志文件1。

InnoDB逻辑存储结构

表空间由segment、extend、page组成

常见的segment有数据段、索引段、回滚段等，数据段为B+树的叶子节点(Leaf node segment)、索引段为B+树的非叶子节点(Non-leaf node segment

Extend (区) 每个区大小固定为1MB，为保证区中page的连续性通常InnoDB会一次从磁盘中申请4-5个区。在默认page的大小为16KB的情况下，一个区则由64个连续的page。

Page (页)参数innodb_page_size参数指定page的大小，默认大小为16

Innodb行记录格式

Innodb存储引擎以行的形式存储。这意味着页中保存着表中一行行的数据。

Compact行记录格式

Redundant行记录格式

行溢出数据

Compressed

CHAR的行结构存储

Dynamic行记录格式

Innodb数据页结构

页是Innodb存储管理数据引擎的最小单位。

File Header用来记录页的一些头信息，由8部分组成，共占38字节。

Page Header, 该部分用来记录数据页的状态信息

Infimum 和 Supremum Record分别记录比该页中任何主键都要小和大的值

分支主题

User Record 实际存储行记录的内容

Free Space很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中

File Trailer

检测页是否完整的写入磁盘

分支主题

InnoDb引擎

InnoDB Architecture

InnoDB In-Memory Structures

Buffer Pool

简介

缓冲池是主内存中的一个区域，用于在 InnoDB访问时缓存表和索引数据。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，多达 80% 的物理内存通常分配给缓冲池。

为了提高大量读取操作的效率，缓冲池被划分为可能包含多行的页面。为了缓存管理的效率，缓冲池被实现为页面的链表；很少使用的数据使用最近最少使用 (LRU) 算法的变体从缓存中老化。

Buffer Pool LRU 算法

缓冲池使用 LRU 算法的变体作为列表进行管理。当需要空间向缓冲池添加新页面时，最近最少使用的页面会被逐出，并将新页面添加到.

列表中间在此中点插入策略将列表视为两个子列表：

在头部，最近访问的新（“年轻”）页面的子列表

在尾部，最近访问过的旧页面的子列表

该算法将经常使用的页面保留在新的子列表中。旧的子列表包含不太常用的页面；这些页面是驱逐的候选页面。

默认情况下，算法操作如下：

缓冲池的 3/8 专用于旧子列表。

列表的中点是新子列表尾部与旧子列表头部相交的边界。

当InnoDB将页面读入缓冲池时，它最初将它插入到中点（旧子列表的头部）。可以读取页面，因为它是用户启动的操作（例如 SQL 查询）所必需的，或者是由自动执行的预读操作的一部分 InnoDB。

访问旧子列表中的页面使其 “年轻”，将其移动到新子列表的头部。如果页面是因为用户启动的操作需要它而被读取，则第一次访问会立即移动，并且页面会变年轻。如果页面是由于预读操作而读取的，则第一次访问不会立即移动，并且在页面被逐出之前可能根本不需要移动。

随着数据库的运行，缓冲池中未被访问的页面会通过向列表尾部移动来“老化”。新旧子列表中的页面随着其他页面的更新而老化。旧子列表中的页面也会随着页面插入中点而老化。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。

Buffer Pool 配置

将缓冲池的大小设置为尽可能大的值，从而为服务器上的其他进程留出足够的内存来运行而不会产生过多的分页。缓冲池越大，就越InnoDB像内存数据库，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。

配置 InnoDB 缓冲池大小

配置 InnoDB 缓冲池块大小：innodb_buffer_pool_chunk_size

缓冲池大小必须始终等于或倍数 innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances

当增加缓冲池的大小时，调整大小操作：

添加页面chunks（块大小由定义 innodb_buffer_pool_chunk_size）

转换哈希表、列表和指针以使用内存中的新地址

将新页面添加到空闲列表

当这些操作正在进行时，其他线程被阻止访问缓冲池。

当减小缓冲池的大小时，调整大小操作：

对缓冲池进行碎片整理并撤回（释放）页面

删除页面chunks（块大小由定义 innodb_buffer_pool_chunk_size）

转换哈希表、列表和指针以使用内存中的新地址

在具有足够内存的 64 位系统上，您可以将缓冲池拆分为多个部分，以最大程度地减少并发操作之间对内存结构的争用。

配置多个缓冲池实例

使用innodb_buffer_pool_instances 配置选项配置多个缓冲池实例

作用：

当多个线程访问buffer pool时，单个缓冲池实例就会限制访问性能，因此，使用多个缓冲池实例可以减少线程之间的争用。

用Hash函数将存储在随机分配给其中一个缓冲池中或从缓冲池中读取

每个缓冲池实例管理自己的空闲列表、刷新列表、LRU 和所有其他连接到缓冲池的数据结构，并由自己的缓冲池互斥锁保护。

Free List

Buffer Pool初始化的时候每个数据页都是空闲的，随着后续对数据库的增删查改等操作，空闲的页就会被填充或者没有价值的页会被释放。此时Buffer Pool不知道那些数据页是空闲，所以需要Free列表进行管理，需要空闲页只需要从Free列表查找即可。

Free List是双向链表，链表的节点存储是空闲数据页的描述信息块。当需要从磁盘加载数据页到内存时会先从Free列表中找到空闲页，把数据页的表空间号和数据页号写入描述信息块，加载数据页写入空闲页后，该空闲页的描述信息块会从Free列表中移除。

Flush List

Flush列表和Free列表一样都是双向链表，只是Flush列表存放着脏页。在Buffer Pool里被修改的数据页称为脏页，需要Flush列进行管理。当需要将脏页刷到磁盘时从Flush列表查找。脏页被刷新到磁盘后描述信息块会从Flush列表移除变成空闲页，添加到Free列表中。

LRU List

LRU列表是用来管理从磁盘读取的数据页，在讲LRU列表之前我们先理解LRU算法（Latest Recent Used）。内存区域的数据页就是通过该算法来管理，通常频繁使用的数据页放在LRU列表头部，最少使用的页放在尾部，当内存区域不能存放新读取页时就会淘汰尾端的数据页。

为了获得最佳效率，使得每个缓冲池实例是至少为1GB。

可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常访问的数据带入缓冲池。

使缓冲池扫描抗性

配置参数 innodb_old_blocks_pct控制LRU 列表中“旧”块的百分比。

默认值 innodb_old_blocks_pct是 37，对应于3/8原固定比率,则new为5/8

innodb_old_blocks_time 指定第一次访问页面后的时间窗口（以毫秒为单位），在此期间它可以被访问而不会被移到 LRU 列表的前面（最近使用的末尾）。

默认值 innodb_old_blocks_time是 1000。

增加此值会使越来越多的块可能会从缓冲池中更快地老化。

防止缓冲池被预读搅动的优化可以避免由于表或索引扫描引起的类似问题。

可以控制如何以及何时执行预读请求以异步地将页面预取到缓冲池中，以预期很快就会需要这些页面。

配置 InnoDB 缓冲池预取（预读）

InnoDB使用两种预读算法来提高 I/O 性能：

线性预读

根据缓冲池中按顺序访问的页面来预测可能很快需要哪些页面

随机预读

根据缓冲池中已有的页面来预测何时可能很快需要页面

将配置变量设置 innodb_random_read_ahead为 ON。

可以控制何时发生后台刷新以及是否根据工作负载动态调整刷新速率。

配置缓冲池刷新

InnoDB在后台执行某些任务，包括从缓冲池中刷新脏页。脏页是那些已被修改但尚未写入磁盘上数据文件的页。

缓冲池刷新由页面清理线程执行。页面清理线程的数量由innodb_page_cleaners变量控制

该变量的默认值为 4。

当脏页的百分比达到innodb_max_dirty_pages_pct_lwm 变量定义的低水位线值时，将启动缓冲池刷新。

默认的低水位标记为 0，这将禁用此早期刷新行为。

innodb_max_dirty_pages_pct_lwm 阈值的目的是控制缓冲池中脏页的百分比，并防止脏页数量达到innodb_max_dirty_pages_pct 变量定义的阈值，

该变量的默认值为 75。

配置时 innodb_max_dirty_pages_pct_lwm，该值应始终小于该 innodb_max_dirty_pages_pct 值。

可以配置如何InnoDB保留当前缓冲池状态以避免服务器重新启动后的长时间预热。

保存和恢复缓冲池状态

为了减少重新启动服务器后的预热时间，InnoDB在服务器关闭时为每个缓冲池保存最近使用的页面的百分比，并在服务器启动时恢复这些页面。存储的最近使用页面的百分比由innodb_buffer_pool_dump_pct 配置选项定义。

Change Buffer

Change Buffer是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存对二级索引页的更改。可能由INSERT、 UPDATE或 DELETE操作 (DML)导致的缓冲更改稍后在其他读取操作将页面加载到缓冲池时合并。

Change Buffer 由innodb_change_buffering变量控制，配置的值：

all

默认值：缓冲区插入、删除标记操作和清除。

none

不要缓冲任何操作。

inserts

缓冲区插入操作。

deletes

缓冲区删除标记操作。

changes

缓冲插入和删除标记操作。

purges

在后台发生的缓冲区物理删除操作。

配置更改缓冲区大小

innodb_change_buffer_max_size

默认设置为 25。最大设置为 50。

变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。

Adaptive Hash Index

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。

InnoDB会监控对表上各索引页的查询，如果观察该数据被访问的频次符合规则，那么就建立哈希索引来加快数据访问的速度，这个哈希索引称之为"Adaptive Hash Index,AHI",AHI是通过缓冲池的B+树页构建的，建立的速度很快，而且不对整颗树都建立哈希索引。(可以理解成热点的数据才会进入这个哈希表)

当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree所有之上再创建一个哈希索引

innodb_adaptive_hash_index 变量控制

特点

仅使用于 =这种逻辑的搜索条件，因为自适应哈希索引是用key value的形式对数据进行存储的。

自适应哈希索引无法对order by进行优化

不支持模糊查询

Hash 索引限制和弊端

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

（2）Hash 索引无法被用来避免数据的排序操作。

（3）Hash 索引不能利用部分索引键查询。

（4）Hash 索引在任何时候都不能避免表扫描。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

Log Buffer

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。

日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。

innodb_flush_log_at_trx_commit 变量控制日志缓冲区的内容如何写入和刷新到磁盘。

完全符合 ACID 需要默认设置 1。日志在每次事务提交时写入并刷新到磁盘。

设置为 0 时，提交事务时并不将log buffer写入磁盘，而是等待主线程每秒的刷新。

设置为 2 时，事务提交时将事务日志写入redo log file，但仅写入文件系统的缓存，不进行fsync操作。在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机，并不会导致事务的丢失。

innodb_flush_log_at_timeout 变量控制日志刷新频率。

允许你集日志冲洗频率 N秒（其中 N是1 ... 2700，为1的默认值）

任何意外的mysqld进程退出都可以擦除长达N数秒的事务

日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为 16MB。

innodb_log_file_size

该参数决定着mysql事务日志文件（ib_logfile0）的大小；

innodb_file_per_table：该参数设置为on时，每张表都建一个ibd文件，否则合用ibdata1

innodb_log_files_in_group：该参数控制日志文件数。默认值为2。mysql 事务日志文件是循环覆写的。

InnoDB On-Disk Structures

Tables

创建 InnoDB 表

InnoDB默认情况下，表是在 file-per-table 表空间中创建的。

行格式

InnoDB表的行格式决定了其行在磁盘上的物理存储方式。

InnoDB支持四种行格式，每种格式具有不同的存储特性

支持行格式包括 REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。DYNAMIC行格式是默认的。

.frm 文件

MySQL 将表的数据字典信息存储在数据库目录中的 .frm 文件中。

在外部创建表

在数据目录之外创建表

原因可能包括空间管理、I/O 优化或将表放置在具有特定性能或容量特征的存储设备上。

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

导入 InnoDB 表

导入表的原因

在非生产 MySQL 服务器实例上运行报告以避免在生产服务器上放置额外负载。

将数据复制到新的副本服务器。

从备份的表空间文件恢复表。

这是一种比导入转储文件更快的数据移动方式，后者需要重新插入数据和重建索引。

将数据移动到具有更适合您的存储要求的存储介质的服务器。例如，可以将繁忙的表移至 SSD 设备，或将大表移至高容量 HDD 设备。

移动或复制 InnoDB 表

将表从 MyISAM 转换为 InnoDB

InnoDB 中的 AUTO_INCREMENT 处理

InnoDB AUTO_INCREMENT 锁定模式

“简单的插入”

“ INSERT-like ” 语句

“批量插入”

“混合模式插入”

innodb_autoinc_lock_mode 变量有三种可能的设置。设置为 0、1 或 2，分别表示 “传统”、“连续”或 “交错”锁定模式。

InnoDB AUTO_INCREMENT 锁模式使用含义

在复制中使用自动增量

“丢失”自动递增值和序列间隙

为AUTO_INCREMENT列指定 NULL 或 0

为AUTO_INCREMENT列分配负值

如果该AUTO_INCREMENT值大于指定整数类型的最大整数

“批量插入”的自动增量值中的差距

由“混合模式插入”分配的自动递增值

InnoDB AUTO_INCREMENT 计数器初始化

Indexes

聚集索引和二级索引

InnoDB 索引的物理结构

排序索引构建

InnoDB 全文索引

Tablespaces

The System Tablespace

系统表空间(共享表空间)是InnoDB数据字典、双写缓冲区、更改缓冲区和撤消日志的存储区域。如果表是在系统表空间中创建的，而不是在每个表文件或通用表空间中创建，则它还可能包含表和索引数据。系统表空间（在操作系统上体现就是ibdata文件）

调整系统表空间的大小

增加系统表空间的大小

增加系统表空间大小的最简单方法是将其配置为自动扩展

innodb_data_file_path=ibdata1:1G:autoextend

File-Per-Table Tablespaces

独立表空间（file-per-table tablespaces）默认是开启的（也就是innodb_file_per_table参数不设置时，它默认等于1）

独立表空间

表名.frm # 表的表结构文件(里面存放的是表的创建语句)

表名.ibd # 表的数据文件(当有数据往表中插入时,数据就保存之个文件中的)

General Tablespaces

General tablespace 是一种共享的 innodb 表空间，有点类似 ibdata1 。可以在一个表空间数据文件下存储多张表，即使这些表来自不同的 schame 。

常规表空间功能提供以下功能：

类似于系统表空间，常规表空间是共享表空间，可以存储多个表的数据。

常规表空间比每表文件表空间具有潜在的内存优势。服务器在表空间的生存期内将表空间元数据保留在内存中。与单独的每表文件表空间中的相同数量的表相比，较少的常规表空间中的多个表为表空间元数据消耗的内存更少。

常规表空间数据文件可以放置在相对于MySQL数据目录或独立于MySQL数据目录的目录中，该目录为您提供了许多数据文件和每表文件表空间的存储管理功能。与每表文件表空间一样，将数据文件放置在MySQL数据目录之外的功能使您可以分别管理关键表的性能，为特定表设置RAID或DRBD或将表绑定到特定磁盘。

常规表空间支持Antelope和Barracuda文件格式，因此支持所有表行格式和相关功能。支持两种文件格式，通用表空间不依赖 innodb_file_format或 innodb_file_per_table 设置，这些变量也不影响通用表空间。

该TABLESPACE选项可用于 CREATE TABLE在常规表空间，每表文件表空间或系统表空间中创建表。

该TABLESPACE选项可用于 ALTER TABLE在常规表空间，每表文件表空间和系统表空间之间移动表。以前，不可能将表从每个表文件表空间移动到系统表空间。

创建通用表空间

CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]

通用表空间可以在数据目录中或在其外部创建。为避免与隐式创建的每表文件表空间冲突，不支持在数据目录下的子目录中创建常规表空间。在数据目录之外创建常规表空间时，该目录必须在创建表空间之前存在。

Redo Log TablesSpaces

Undo Tablespaces

撤消表空间包含撤消日志，这是包含有关如何撤消事务对聚集索引记录的最新更改的信息的记录集合。

配置撤销表空间：

innodb_undo_directory

指定单独存放 undo 表空间的目录，默认为.（即 datadir），可以设置相对路径或者绝对路径

innodb_undo_tablespaces

指定单独存放的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003

innodb_max_undo_log_size

undo 表空间文件超过此值即标记为可收缩，默认 1G

innodb_undo_logs

指定回滚段的个数（早期版本该参数名字是innodb_rollback_segments），默认 128 个。每个回滚段可同时支持 1024 个在线事务。这些回滚段会平均分布到各个 undo 表空间中

innodb_undo_logs>=35（默认 128）。因为在 MySQL 5.7 中，第一个 undo log 永远在系统表空间中，另外 32 个 undo log 分配给了临时表空间，即 ibtmp1，至少还有 2 个undo log 才能保证 2 个 undo 表空间中每个里面至少有 1 个 undo log；

分支主题

innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可收缩，默认 1G，可在线修改；

The Temporary Tablespace

innodb_temp_data_file_path = ibtmp1:12M:autoextend

命名ibtmp1文件，初始化12M，且默认无上限。

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G, 临时表空间设置最大值，避免无限增大

说明

临时表空间不像普通InnoDB表空间那样，不支持裸设备（raw device）。

临时表空间使用动态的表空间ID，因此每次重启时都会变化（每次重启时，都会重新初始化临时表空间文件）。

当选项设置错误或其他原因（权限不足等原因）无法创建临时表空间时，mysqld实例也无法启动。

临时表空间中存储这非压缩的InnoDB临时表，如果是压缩的InnoDB临时表，则需要单独存储在各自的表空间文件中，文件存放在 tmpdir（/tmp）目录下。

临时表元数据存储在 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 视图中。

建议

设置 innodb_temp_data_file_path 选项，设定文件最大上限，超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。

检查 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO，找到最大的临时表对应的线程，kill之即可释放，但 ibtmp1 文件则不能释放（除非重启）。

择机重启实例，释放 ibtmp1 文件，和 ibdata1 不同，ibtmp1 重启时会被重新初始化而 ibdata1 则不可以。

定期检查运行时长超过N秒（比如N=300）的SQL，考虑干掉，避免垃圾SQL长时间运行影响业务。

InnoDB Data Dictionary

InnoDB数据字典由包含元数据的用于跟踪对象的如表，索引，和表中的列的内部系统表。数据字典元数据与存储在InnoDB表元数据文件（.frm文件）中的信息有一定程度的重叠

Data Dictironary(DD, 数据字典)是有关数据库对象的合集, 例如表、视图、索引等, 可以看做是数据库的元信息。换句话说, 数据字典存储了有关表结构的信息, 每个表具有的列, 表的索引等。

系统表是什么? 跟自己创建的表有何不同?

系统表有很多, 常见的有mysql.schemata,mysql.tables, mysql.indexes

我们创建的表的元信息是放到系统表中的

在内存中, 这些元信息以对象的方式提供给外部使用, 比如说, 创建一个表, 内存中会创建这个表的数据字典对象, 系统表以及我们创建的表都会有自己的数据字典对象

可以认为系统表的元信息就存储在自己的数据字典对象中, 这些信息会被序列化到磁盘的mysql.ibd文件中

data dictionary (简称DD)中的数据结构是完全按照多态、接口/实现的形式来组织的，接口通过纯虚类来实现（比如表示一个表的 Table），其实现类（Table_impl）为接口类的名字加 _impl 后缀。

Doublewrite Buffer

Doubel write保证了页的可靠性，Redo log是记录对页(16K)的物理操作，若innodb将页写回表时写了一部分(如4K)出现宕机，则物理页将会损坏无法通过redolog恢复。所以在apply重做日志前，将缓冲池中的脏页通过memcpy到doublewrite buffer中，再将doublewrite buffer页分两次每次1MB刷入共享表空间的磁盘文件中（磁盘连续，开销较小），完成doublewrite buffer的页写入后再写入各个表空间的表中, 当写入页时发生系统崩溃，恢复过程中，innodb从共享表空间的doublewrite找到该页的副本，并将其恢复到表空间文件中，再apply重做日志。

A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复

B. 如果写物理页时宕机了，则可以使用临时页来恢复物理页,每次写物理页时，先写到double write buffer中，然后从double write buffer写到double write上去。最后再从double write buffer写到物理页上去。

Redo Log

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程将缓存池刷新到磁盘。

当在执行刷新时，宕机或者断电，可能会丢失部分数据。所以引入了redo log来记录已成功提交事务的修改信息，并且在事务提交时会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

参数配置：

innodb_log_file_size : 每个redo log文件大小。innodb_log_files_in_group : 文件组中的文件数量，默认为2.

innodb_log_files_in_group : 文件组中的文件数量，默认为2.

innodb_log_group_home_dir: 日志文件路径，默认在数据文件路径下。

参数innodb_flush_log_at_trx_commit影响重做日志的刷写动作

【0】事务提交时并不写，而是等待主线程每秒刷写一次。

【1】默认值，表示执行事务commit时同步写到磁盘，提供最大的安全性，也是最慢的方式。

【2】异步写磁盘，先写到系统缓存，交给系统写到磁盘。

redo:ib_logfileN文件

Undo Logs

undo log 叫做回滚日志，用于记录数据被修改前的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

undo log默认存放在共享表空间中。

如果开启了 innodb_file_per_table ，将放在每个表的.ibd文件中。

undo的相关变量

innodb_undo_directory .

innodb_undo_logs 128

innodb_undo_tablespaces 0

undo:share tablespace或.ibd

备份恢复

MySQL备份数据的方式

热备份

热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响

温备份

温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作

冷备份

冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

我们要备份什么?

数据

二进制日志, InnoDB事务日志

代码(存储过程、存储函数、触发器、事件调度器)

服务器配置文件

备份工具

mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备

cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份

lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份

mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎

xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

mysql主从

mysql主从形式

（一）一主一从

分支主题

（二）主主复制

分支主题

（三）一主多从

分支主题

（四）多主一从

分支主题

（五）联级复制

分支主题

mysql复制原理

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

具体步骤：

1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave

2、从库的IO线程和主库的dump线程建立连接。

3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。

4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。

5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到http://master.info中

6、从库SQL线程应用relay-log，并且把应用过的记录到http://relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

MySQL锁

锁的类型

行锁（Record Locks）

行锁是对索引记录的锁。

行锁总是锁定索引记录，即使一个表没有定义索引。对于这种情况， InnoDB创建一个隐藏的聚集索引并使用该索引进行行锁定。

在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

间隙锁（Gap Locks）

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

间隙锁定InnoDB是“纯粹的抑制性”，这意味着它们的唯一目的是防止其他事务插入间隙。

间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

临键锁（Next-key Locks）

是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。

InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

共享锁/排他锁（Shared and Exclusive Locks）

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

共享锁用于读取操作，而排他锁是用于更新或删除操作。

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

意向锁是InnoDB自动加的，不需用户干预。

意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

插入意向锁（Insert Intention Locks）

管插入意向锁是一种特殊的间隙锁

自增锁（Auto-inc Locks）