首页  思维导图  详情

MySQL知识全知道

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





AI智能生成

为你推荐

查看更多



全

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）

外键字段添加到哪里？

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

如何设计表

一对一

第三范式的例子

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

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

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

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

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

一对多

第二范式的例子

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

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

多对多

常见表关系

数据库设计

MySQL的ALL PRIVILEGES的权限列表

创建用户

给用户授权

撤销授权

查看权限

删除用户

DCL（数据库控制语言）

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

创建数据库

SHOW DATABASES

查询所有数据库

SHOW CREATE DATABASE db_name;

查询数据库建表时的sql

查询数据库

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);

普通索引

ALTER TABLE table_name ADD PRIMARY KEY (field_name)

ALTER TABLE table_name DROP PRIMARY KEY;

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;

查询所有表

SHOW CREATE TABLE tb_name;

查询建表时的sql

查询表

数据库表操作

DDL（Data Definition Language）

添加单条

添加多条

增

DELETE FROM tb_name WHERE ...

sql

删除时必须加WHERE条件

注意

删

修改时必须加WHERE条件

改

DML（Data Manipulation Language）（重点）

SELECT * FROM tb_name

基础的查询

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

SELECT * FROM tb_name WHERE user_id >10;

比较运算符

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

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

逻辑运算符

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

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

通配符

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以张开头后边有一个字符的数据

LIKE模糊查询

IN字段指定多个值查询

field BETWEEN value1 AND value2

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

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

BETWEEN AND 区间查询

WHERE子句

COUNT(*)：统计所有行

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

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

AVG(field)，取平均值

MAX(field)，取最大值

MIN(field)，取最小值

聚合函数

GROUP BY分组查询

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

WHERE不能对聚合值进行过滤

HAVING对聚合值进行过滤

ORDER BY field DESC;降序查询

ORDER BY field ASC;升序查询

查询顺序

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

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

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

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

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

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

可以理解为归并排序

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

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

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

ORDER BY查询排序

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

参数

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

LIMIT查询结果截取

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;

由于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;

全外连接：SELECT select…list FROM TableA a FULL OUTER 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

LEFT JOIN

RIGHT JOIN

FULL JOIN

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

BEGIN：开启事务

ROLLBACK：事务回滚

COMMIT：事务提交

关键词

表的存储引擎为InnoDB

必备条件

事务

DQL（Data Query Language）（重点）

SQL的分类

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

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

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

支持外键

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

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

具有如下特点

InnoDB存储引擎

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

不支持事务

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

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

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

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

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对数据和索引都做了缓冲，而MyISAM只缓冲了索引

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

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

如何选择

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

InnoDB

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

MyISAM

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

一旦关闭，数据就会丢失

Memory

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

Archive

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

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

csv

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

BlackHole

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

Federated

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

Merge MyISAM

引擎类型

MySQL存储引擎（重点）

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

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

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

语法

查看表的读取顺序

查询类型

哪些索引被使用

哪些索引被实际使用

表之间的引用

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

作用

建表语句

INSERT语句

subject（学科表）

teacher（教师表）

student（学生表）

student_score（学生成绩表）

测试使用的表

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

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

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

结果

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

EXPLAIN EXTENDED

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

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

EXPLAIN PARTITIONS

有几个SELECT就有几个id

执行顺序从上到下

SQL语句

执行计划

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

id相同

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

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值越大，优先级越高，越先执行

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相同又不同

id为NULL的最后执行

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

id的值为NULL

执行顺序

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

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

SIMPLE

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

PRIMARY

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

SUBQUERY

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

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

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

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

DERIVED

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

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

UNION

从UNION表获取结果的SELECT

UNION RESULT

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

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

table

在表中找到所需行的方式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级别

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

explain select min(id) from subject;

NULL

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

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

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

const、system

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

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

eq_ref

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

ref

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

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

range

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

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

explain select id from subject;

index

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

explain select * from subject;

ALL

type（重点）

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

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

possible_keys

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

key（重点）

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

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

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的时候直接报错，保证库存不超卖

整型（有符号）

浮点型

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)

字符串数据类型

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

key_len计算规则如下

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

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

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

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

key_len

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

ref

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

rows

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

filtered

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

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

explain select * from subject order by name;

Using filesort

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

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

Using temporary

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

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

Using join buffer

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

Impossible where

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

Select tables optimized away

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

No tables used

Extra（重要）

EXPLAIN中的列

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

EXPLAIN不考虑各种Cache

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

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

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

缺点

EXPLAIN（重点）

索引是什么

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

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

索引命名

建表时

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的后面，同时注意索引不要失效

使用索引进行查询

索引的基本操作

优点

缺点

使用索引的优点和缺点

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

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

主键索引、辅助索引

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

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

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

聚簇索引、非聚簇索引

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

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

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

全文索引

单列索引

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

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

按照使用字段的个数

使用的字段为主键

主键索引

使用非主键的字段

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

按照使用字段是否为主键

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

聚簇索引（聚集索引）

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

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

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

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

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

Hash索引

R-索引（空间索引）

全文索引（倒排索引）

按照底层数据结构

索引的分类（重点）

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

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

数组和链表

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

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

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

hash

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

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

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

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

简单定义

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

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

特点

图示

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

二叉搜索树

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

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

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

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

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

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

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

自平衡手段

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

AVL树

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

红黑树

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

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

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

前言

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

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

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

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

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

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

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

B树

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

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

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

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

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

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

B+树

各种数据结构

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

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

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

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

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

总结各种数据结构

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

扇区

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

磁盘块

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

内存页

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

局部性原理

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

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

通常是读取一整个磁盘块

磁盘预读

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

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

页大小默认为16KB

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

InnoDB数据页

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

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

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

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

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

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

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

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

辅助索引

主键索引和辅助索引

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

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

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

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

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

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

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

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

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

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

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

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

这里以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单表能够存储的数据量计算

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

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

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

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

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

使用自增ID的缺点

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

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

减少索引开销

为什么使用联合索引

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

最左匹配原则（重要）

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

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

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

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

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

索引覆盖（重要）

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

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

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

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

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

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

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

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

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

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

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

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

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

IN关键字

如果想要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

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

简介

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

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

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

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

原子性（Atomicity）

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

隔离性（Isolation）

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

持久性（Durable）

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

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

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

一致性（Consistent）

事务四大特性（ACID）

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

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

第一类更新丢失

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

第二类更新丢失

更新丢失问题有两类

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

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

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

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

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

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

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

开启事务，START;

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

业务处理后准备更新余额

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

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

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

案例

乐观锁

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

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

悲观锁

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

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

串行化

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

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

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

更新丢失（Lost Update）

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

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

SET AUTOCOMMIT= 0;

显式地开启一个事务

BEGIN;

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

COMMITT;

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

ROLLBACK;

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

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

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

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

SAVEPOINT identifier;

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

RELEASE SAVEPOINT identifier;

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

ROLLBACK TO identifier;

修改事务隔离级别

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

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

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

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

SET TRANSACTION ISOLATION LEVEL 隔离级别;

事务控制语句

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

读未提交（Read uncommitted）

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

读已提交（Read committed简称RC）

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

要避免幻读可以用间隙锁

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

可重复读（Repeatable read简称RR）

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

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

串行化（Serializable）

分类

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

脏读（Dirty Reads）

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

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

不可重读（Non-Repeatable Reads）

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

幻读（Phantom Reads）

show variables like transaction_isolation;

select @@transaction_isolation;

查看事务隔离级别

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

事务隔离级别

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

建表SQL

数据SQL

测试数据

SQL执行流程

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

读未提交，出现脏读

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

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

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

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

可重复读SQL执行流程

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

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

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

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

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

出现幻读SQL执行流程

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

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

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

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

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

演示事务隔离级别

undo log

原子性

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

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

读未提交

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

读已提交

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

可重复读

对于每个写操作串行处理

隔离性

redo log

持久性

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

一致性

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

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

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

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

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

事务（重点）

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实现，读到的是某一个版本的数据，既然是某一个版本的数据，就并不一定是最新的数据，可能是历史数据

快照读

SQL执行顺序

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

案例1

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

案例2

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

当前读和快照读

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

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

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

DB_TRX_ID

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

DB_ROLL_PTR

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

DB_ROW_ID

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

删除flag隐藏字段

记录图示

表数据3个隐式字段

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

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

insert undo log

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

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

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

update undo log

从前面的分析可以看出，为了实现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可见，那么这条记录一定是可以被安全清除的

purge线程

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

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

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

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

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

事务提交后，释放锁

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

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

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

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

事务提交，释放锁

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

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

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

update undo log执行流程

事务在进行快照读操作的时候产生的读视图，在该事务执行快照读的那一刻，会生成数据库系统当前对于事务的一个快照，记录并维护系统当前活跃事务的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），表示这个版本是由将来启动的事务生成的，是肯定不可见的

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

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

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

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

版本链的比对规则

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

Read View（读视图）

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

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

SQL语句执行顺序

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

第一条数据的row的trx_id为300

查询到的数据就是lilei300

第一次快照读

此时undo log版本链

第二次快照读

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

第三次快照读

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

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

undo log日志数据版本链

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

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

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

版本比对规则

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

整体流程

MVCC实现原理

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

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

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

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

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

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

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

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

总结

MVCC（重点）

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

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

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

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

只要是加了锁都是悲观锁

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

不能进行写操作

读锁（共享锁）

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

写锁（排他锁）

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

加锁机制

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

表锁

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

行锁

锁粒度

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

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

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

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

兼容性

读意向锁（IS锁）

写意向锁（IX锁）

意向锁

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

表共享读锁（Table Read Lock）

表独享写锁（Table Write Lock）

存储引擎

锁的分类

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

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

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

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

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

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

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

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

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

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

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

相同

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

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

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

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

不同

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

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

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

记录锁（Record Lock）

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

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

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

间隙锁（Gap Lock）

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

临键锁（Next-key Lock）

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

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

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

插入意向锁（Insert Intention Lock）

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

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

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

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

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

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

锁模式

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

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

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

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

读写意向锁

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

自增锁

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

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

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

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

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

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

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

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

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

InnoDB锁详细解释

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

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

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

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

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

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

SQL执行流程图

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

加锁记录

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

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

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

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

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

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

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

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

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

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

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

案例7：无索引

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

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

案例10：修改索引值

案例11：DELETE语句加锁分析

案例12：INSERT语句加锁分析

具体场景分析

常见加锁场景分析

当前正在等待锁定的数量

Innodb_row_ lock_current_wait

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

Innodb_row_ lock_time

每次等待所花平均时间

Innodb_row_ lock_time_avg

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

Innodb_row_ lock_time_max

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

Innodb_row_ lock_waits

show status likeinnodb_row_lock%;

行锁分析

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;

死锁

锁（重点）

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

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

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

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

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

主从复制

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

数据恢复

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

数据同步

使用场景

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

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

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

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

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

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

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

binlog刷盘时机

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

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

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

STATMENT

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

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

ROW

MIXED

binlog日志格式

binlog

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

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

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中

0（延迟写）

1（实时写，实时刷）

2（实时写，延迟刷）

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

redo log刷写时机

redo log记录形式

redo log的大小固定

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

文件大小

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

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

实现方式

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

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

记录方式

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

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

redo log和binlog的区别

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

定义

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

作用：

undo log、redo log和binlog（重点）

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

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

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

最佳左前缀法则

存储引擎不能使用索引中范围条件右边的列 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'; 未使用索引

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

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

字段的数据类型

数据类型的长度

表的存储引擎

优化数据库表结构的设计

分库分表

数据库参数配置优化

主从复制，读写分离

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

字段名

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

字符类型

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

数值类型

日期类型

是否为null

字段类型

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

实例

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

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

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

优化方式

MySQL常见优化手段（重点）

MySQL前进一步

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

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

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

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

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

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

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

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

主要功能：

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

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

（6）优化器组件（Optimizer）

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

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

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

（9）物理文件（File System）

MySQL基架

数据缓存

索引缓存

锁信息

插入缓存

重做日志缓存

额外的内存池

全局内存（Global buffer）

Master Thread

IO Thread

Purage Thread

Page Cleaner Thread

线程内存（Thread buffer）

MySQL内存结构

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

(1) 参数文件

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

(2) 日志文件

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

(3) socket文件

MySQL实例的进程ID文件。

(4) pid文件

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

表结构定义文件：

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

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

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

表空间文件

(5) 表结构文件

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

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

（1）共享表空间：

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

（2）独立表空间

innodb_log_file_size

innodb_log_file_in_group

innodb_mirrored_log_groups

innodb_log_group_home_dir

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

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

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

重做日志文件(Redo log file)

(6) InnoDB存储引擎文件

常见的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

表空间由segment、extend、page组成

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

Compact行记录格式

Redundant行记录格式

行溢出数据

Compressed

CHAR的行结构存储

Dynamic行记录格式

Innodb行记录格式

InnoDB逻辑存储结构

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

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

分支主题

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

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

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

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

File Trailer

Innodb数据页结构

MySQL文件结构

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

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

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

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

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

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

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

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

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

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

访问旧子列表中的页面使其 “年轻”，将其移动到新子列表的头部。如果页面是因为用户启动的操作需要它而被读取，则第一次访问会立即移动，并且页面会变年轻。如果页面是由于预读操作而读取的，则第一次访问不会立即移动，并且在页面被逐出之前可能根本不需要移动。

随着数据库的运行，缓冲池中未被访问的页面会通过向列表尾部移动来“老化”。新旧子列表中的页面随着其他页面的更新而老化。旧子列表中的页面也会随着页面插入中点而老化。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。

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

Buffer Pool LRU 算法

配置 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）

当减小缓冲池的大小时，调整大小操作：

配置 InnoDB 缓冲池大小

将缓冲池的大小设置为尽可能大的值，从而为服务器上的其他进程留出足够的内存来运行而不会产生过多的分页。缓冲池越大，就越InnoDB像内存数据库，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。

使用innodb_buffer_pool_instances 配置选项配置多个缓冲池实例

当多个线程访问buffer pool时，单个缓冲池实例就会限制访问性能，因此，使用多个缓冲池实例可以减少线程之间的争用。

用Hash函数将存储在随机分配给其中一个缓冲池中或从缓冲池中读取

Buffer Pool初始化的时候每个数据页都是空闲的，随着后续对数据库的增删查改等操作，空闲的页就会被填充或者没有价值的页会被释放。此时Buffer Pool不知道那些数据页是空闲，所以需要Free列表进行管理，需要空闲页只需要从Free列表查找即可。

Free List是双向链表，链表的节点存储是空闲数据页的描述信息块。当需要从磁盘加载数据页到内存时会先从Free列表中找到空闲页，把数据页的表空间号和数据页号写入描述信息块，加载数据页写入空闲页后，该空闲页的描述信息块会从Free列表中移除。

Free List

Flush列表和Free列表一样都是双向链表，只是Flush列表存放着脏页。在Buffer Pool里被修改的数据页称为脏页，需要Flush列进行管理。当需要将脏页刷到磁盘时从Flush列表查找。脏页被刷新到磁盘后描述信息块会从Flush列表移除变成空闲页，添加到Free列表中。

Flush List

LRU列表是用来管理从磁盘读取的数据页，在讲LRU列表之前我们先理解LRU算法（Latest Recent Used）。内存区域的数据页就是通过该算法来管理，通常频繁使用的数据页放在LRU列表头部，最少使用的页放在尾部，当内存区域不能存放新读取页时就会淘汰尾端的数据页。

LRU List

每个缓冲池实例管理自己的空闲列表、刷新列表、LRU 和所有其他连接到缓冲池的数据结构，并由自己的缓冲池互斥锁保护。

为了获得最佳效率，使得每个缓冲池实例是至少为1GB。

配置多个缓冲池实例

在具有足够内存的 64 位系统上，您可以将缓冲池拆分为多个部分，以最大程度地减少并发操作之间对内存结构的争用。

配置参数 innodb_old_blocks_pct控制LRU 列表中“旧”块的百分比。

默认值 innodb_old_blocks_time是 1000。

增加此值会使越来越多的块可能会从缓冲池中更快地老化。

防止缓冲池被预读搅动的优化可以避免由于表或索引扫描引起的类似问题。

innodb_old_blocks_time 指定第一次访问页面后的时间窗口（以毫秒为单位），在此期间它可以被访问而不会被移到 LRU 列表的前面（最近使用的末尾）。

使缓冲池扫描抗性

可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常访问的数据带入缓冲池。

根据缓冲池中按顺序访问的页面来预测可能很快需要哪些页面

线性预读

根据缓冲池中已有的页面来预测何时可能很快需要页面

随机预读

InnoDB使用两种预读算法来提高 I/O 性能：

将配置变量设置 innodb_random_read_ahead为 ON。

配置 InnoDB 缓冲池预取（预读）

可以控制如何以及何时执行预读请求以异步地将页面预取到缓冲池中，以预期很快就会需要这些页面。

InnoDB在后台执行某些任务，包括从缓冲池中刷新脏页。脏页是那些已被修改但尚未写入磁盘上数据文件的页。

该变量的默认值为 4。

缓冲池刷新由页面清理线程执行。页面清理线程的数量由innodb_page_cleaners变量控制

默认的低水位标记为 0，这将禁用此早期刷新行为。

当脏页的百分比达到innodb_max_dirty_pages_pct_lwm 变量定义的低水位线值时，将启动缓冲池刷新。

该变量的默认值为 75。

innodb_max_dirty_pages_pct_lwm 阈值的目的是控制缓冲池中脏页的百分比，并防止脏页数量达到innodb_max_dirty_pages_pct 变量定义的阈值，

配置时 innodb_max_dirty_pages_pct_lwm，该值应始终小于该 innodb_max_dirty_pages_pct 值。

配置缓冲池刷新

可以控制何时发生后台刷新以及是否根据工作负载动态调整刷新速率。

为了减少重新启动服务器后的预热时间，InnoDB在服务器关闭时为每个缓冲池保存最近使用的页面的百分比，并在服务器启动时恢复这些页面。存储的最近使用页面的百分比由innodb_buffer_pool_dump_pct 配置选项定义。

保存和恢复缓冲池状态

可以配置如何InnoDB保留当前缓冲池状态以避免服务器重新启动后的长时间预热。

Buffer Pool 配置

Buffer Pool

Change Buffer是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存对二级索引页的更改。可能由INSERT、 UPDATE或 DELETE操作 (DML)导致的缓冲更改稍后在其他读取操作将页面加载到缓冲池时合并。

默认值：缓冲区插入、删除标记操作和清除。

all

不要缓冲任何操作。

none

缓冲区插入操作。

inserts

缓冲区删除标记操作。

deletes

缓冲插入和删除标记操作。

changes

在后台发生的缓冲区物理删除操作。

purges

Change Buffer 由innodb_change_buffering变量控制，配置的值：

默认设置为 25。最大设置为 50。

变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。

innodb_change_buffer_max_size

配置更改缓冲区大小

Change Buffer

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。

InnoDB会监控对表上各索引页的查询，如果观察该数据被访问的频次符合规则，那么就建立哈希索引来加快数据访问的速度，这个哈希索引称之为\

当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree所有之上再创建一个哈希索引

innodb_adaptive_hash_index 变量控制

仅使用于 =这种逻辑的搜索条件，因为自适应哈希索引是用key value的形式对数据进行存储的。

自适应哈希索引无法对order by进行优化

不支持模糊查询

（1）Hash 索引仅仅能满足\"=\

（2）Hash 索引无法被用来避免数据的排序操作。

（3）Hash 索引不能利用部分索引键查询。

（4）Hash 索引在任何时候都不能避免表扫描。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

Hash 索引限制和弊端

Adaptive Hash Index

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。

日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。

完全符合 ACID 需要默认设置 1。日志在每次事务提交时写入并刷新到磁盘。

设置为 0 时，提交事务时并不将log buffer写入磁盘，而是等待主线程每秒的刷新。

设置为 2 时，事务提交时将事务日志写入redo log file，但仅写入文件系统的缓存，不进行fsync操作。在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机，并不会导致事务的丢失。

innodb_flush_log_at_trx_commit 变量控制日志缓冲区的内容如何写入和刷新到磁盘。

允许你集日志冲洗频率 N秒（其中 N是1 ... 2700，为1的默认值）

任何意外的mysqld进程退出都可以擦除长达N数秒的事务

innodb_flush_log_at_timeout 变量控制日志刷新频率。

日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为 16MB。

该参数决定着mysql事务日志文件（ib_logfile0）的大小；

innodb_file_per_table：该参数设置为on时，每张表都建一个ibd文件，否则合用ibdata1

innodb_log_files_in_group：该参数控制日志文件数。默认值为2。mysql 事务日志文件是循环覆写的。

Log Buffer

InnoDB In-Memory Structures

InnoDB默认情况下，表是在 file-per-table 表空间中创建的。

InnoDB表的行格式决定了其行在磁盘上的物理存储方式。

InnoDB支持四种行格式，每种格式具有不同的存储特性

支持行格式包括 REDUNDANT，COMPACT， DYNAMIC，和COMPRESSED。DYNAMIC行格式是默认的。

行格式

MySQL 将表的数据字典信息存储在数据库目录中的 .frm 文件中。

.frm 文件

创建 InnoDB 表

原因可能包括空间管理、I/O 优化或将表放置在具有特定性能或容量特征的存储设备上。

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

在数据目录之外创建表

在外部创建表

在非生产 MySQL 服务器实例上运行报告以避免在生产服务器上放置额外负载。

将数据复制到新的副本服务器。

从备份的表空间文件恢复表。

这是一种比导入转储文件更快的数据移动方式，后者需要重新插入数据和重建索引。

将数据移动到具有更适合您的存储要求的存储介质的服务器。例如，可以将繁忙的表移至 SSD 设备，或将大表移至高容量 HDD 设备。

导入表的原因

导入 InnoDB 表

移动或复制 InnoDB 表

将表从 MyISAM 转换为 InnoDB

“简单的插入”

“ INSERT-like ” 语句

“批量插入”

“混合模式插入”

innodb_autoinc_lock_mode 变量有三种可能的设置。设置为 0、1 或 2，分别表示 “传统”、“连续”或 “交错”锁定模式。

InnoDB AUTO_INCREMENT 锁定模式

在复制中使用自动增量

“丢失”自动递增值和序列间隙

为AUTO_INCREMENT列指定 NULL 或 0

为AUTO_INCREMENT列分配负值

如果该AUTO_INCREMENT值大于指定整数类型的最大整数

“批量插入”的自动增量值中的差距

由“混合模式插入”分配的自动递增值

InnoDB AUTO_INCREMENT 锁模式使用含义

InnoDB AUTO_INCREMENT 计数器初始化

InnoDB 中的 AUTO_INCREMENT 处理

Tables

聚集索引和二级索引

InnoDB 索引的物理结构

排序索引构建

InnoDB 全文索引

Indexes

系统表空间(共享表空间)是InnoDB数据字典、双写缓冲区、更改缓冲区和撤消日志的存储区域。如果表是在系统表空间中创建的，而不是在每个表文件或通用表空间中创建，则它还可能包含表和索引数据。系统表空间（在操作系统上体现就是ibdata文件）

增加系统表空间大小的最简单方法是将其配置为自动扩展

innodb_data_file_path=ibdata1:1G:autoextend

增加系统表空间的大小

调整系统表空间的大小

The System Tablespace

独立表空间（file-per-table tablespaces）默认是开启的（也就是innodb_file_per_table参数不设置时，它默认等于1）

表名.frm # 表的表结构文件(里面存放的是表的创建语句)

独立表空间

File-Per-Table 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]

通用表空间可以在数据目录中或在其外部创建。为避免与隐式创建的每表文件表空间冲突，不支持在数据目录下的子目录中创建常规表空间。在数据目录之外创建常规表空间时，该目录必须在创建表空间之前存在。

创建通用表空间

General Tablespaces

Redo Log TablesSpaces

撤消表空间包含撤消日志，这是包含有关如何撤消事务对聚集索引记录的最新更改的信息的记录集合。

指定单独存放 undo 表空间的目录，默认为.（即 datadir），可以设置相对路径或者绝对路径

innodb_undo_directory

指定单独存放的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003

innodb_undo_tablespaces

undo 表空间文件超过此值即标记为可收缩，默认 1G

innodb_max_undo_log_size

指定回滚段的个数（早期版本该参数名字是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_undo_logs

innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可收缩，默认 1G，可在线修改；

配置撤销表空间：

Undo Tablespaces

命名ibtmp1文件，初始化12M，且默认无上限。

innodb_temp_data_file_path = ibtmp1:12M:autoextend

临时表空间不像普通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长时间运行影响业务。

建议

The Temporary Tablespace

Tablespaces

InnoDB数据字典由包含元数据的用于跟踪对象的如表，索引，和表中的列的内部系统表。数据字典元数据与存储在InnoDB表元数据文件（.frm文件）中的信息有一定程度的重叠

我们创建的表的元信息是放到系统表中的

系统表是什么? 跟自己创建的表有何不同?

data dictionary (简称DD)中的数据结构是完全按照多态、接口/实现的形式来组织的，接口通过纯虚类来实现（比如表示一个表的 Table），其实现类（Table_impl）为接口类的名字加 _impl 后缀。

InnoDB Data Dictionary

A. 如果写临时页时宕机了，物理页还是完全未写之前的状态，可以用重做日志恢复

Doublewrite Buffer

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: 日志文件路径，默认在数据文件路径下。

参数配置：

【0】事务提交时并不写，而是等待主线程每秒刷写一次。

【1】默认值，表示执行事务commit时同步写到磁盘，提供最大的安全性，也是最慢的方式。

【2】异步写磁盘，先写到系统缓存，交给系统写到磁盘。

参数innodb_flush_log_at_trx_commit影响重做日志的刷写动作

redo:ib_logfileN文件

Redo Log

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文件中。

innodb_undo_directory .

innodb_undo_logs 128

innodb_undo_tablespaces 0

undo的相关变量

undo:share tablespace或.ibd

Undo Logs

InnoDB On-Disk Structures

InnoDB Architecture

InnoDb引擎

热备份

温备份

冷备份

MySQL备份数据的方式

数据

代码(存储过程、存储函数、触发器、事件调度器)

服务器配置文件

我们要备份什么?

备份工具

备份恢复

（一）一主一从

（二）主主复制

（三）一主多从

（四）多主一从

（五）联级复制

mysql主从形式

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

mysql复制原理

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主从

行锁是对索引记录的锁。

行锁总是锁定索引记录，即使一个表没有定义索引。对于这种情况， InnoDB创建一个隐藏的聚集索引并使用该索引进行行锁定。

在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

行锁（Record Locks）

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

间隙锁定InnoDB是“纯粹的抑制性”，这意味着它们的唯一目的是防止其他事务插入间隙。

间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

间隙锁（Gap Locks）

是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。

InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

临键锁（Next-key Locks）

共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

共享锁用于读取操作，而排他锁是用于更新或删除操作。

共享锁/排他锁（Shared and Exclusive Locks）

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

意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

管插入意向锁是一种特殊的间隙锁

插入意向锁（Insert Intention Locks）