mysql高级-2

第11章_数据库的设计规范

范式

在关系型数据库中，关于数据表设计的基本原则就是，规则就是范式

可以理解为，一张数据表的设计结
构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

范式都包括哪些

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式
（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美
范式）。

键和相关属性的概念

这里有两个表：
球员表(player) ：球员编号 | 姓名 | 身份证号 | 年龄 | 球队编号
球队表(team) ：球队编号 | 主教练 | 球队所在地字段名称 字段类型 是否是主键 说明
id INT 是 主键id
username VARCHAR(30) 否 用户名
password VARCHAR(50) 否 密码
user_info VARCHAR(255) 否 用户信息 (包含真实姓名、电话、住址)
超键 ：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）
（球员编号，姓名）（身份证号，年龄）等。
候选键 ：就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
主键 ：我们自己选定，也就是从候选键中选择一个，比如（球员编号）。
外键 ：球员表中的球队编号。
主属性 、 非主属性 ：在球员表中，主属性是（球员编号）（身份证号），其他的属性（姓名）
（年龄）（球队编号）都是非主属性

第一范式(1st NF)

假设一家公司要存储员工的姓名和联系方式。它创建一个如下表：

user 表的设计不符合第一范式

其中，user_info字段为用户信息，可以进一步拆分成更小粒度的字段，不符合数据库设计对第一范式的
要求。将user_info拆分后如下：

举例3：
属性的原子性是主观的。例如，Employees关系中雇员姓名应当使用1个（fullname）、2个（firstname
和lastname）还是3个（firstname、middlename和lastname）属性表示呢？答案取决于应用程序。如果应
用程序需要分别处理雇员的姓名部分（如：用于搜索目的），则有必要把它们分开。否则，不需要。

第二范式(2nd NF)

举例1：
成绩表（学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课
程号也不能决定成绩，所以“（学号，课程号）→成绩”就是完全依赖关系。

举例2：
比赛表 player_game ，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这
里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键（或主键）来决定如下的关系：

(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

(球员编号) → (姓名，年龄)
(比赛编号) → (比赛时间, 比赛场地)

对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢

存在以下问题

数据冗余：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛
也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次

插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没
法插入。

删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删
除掉

更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调
整，否则就会出现一场比赛时间不同的情况。

为了避免出现上述情况，可以把球员比赛表设计如下

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。
1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只
表达一个意思。

定义了一个名为 Orders 的关系，表示订单和订单行的信息：

违反了第二范式，因为有非主键属性仅依赖于候选键（或主键）的一部分。例如，可以仅通过orderid找
到订单的 orderdate，以及 customerid 和 companyname，而没有必要再去使用productid。
修改：
Orders表和OrderDetails表如下，此时符合第二范式

第三范式(3rd NF)
举例1：
部门信息表：每个部门有部门编号（dept_id）、部门名称、部门简介等信息。
员工信息表：每个员工有员工编号、姓名、部门编号。列出部门编号后就不能再将部门名称、部门简介
等与部门有关的信息再加入员工信息表中。
如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

商品类别名称依赖于商品类别编号，不符合第三范式。
修改：
表1：符合第三范式的商品类别表的设计

商品表goods通过商品类别id字段（category_id）与商品类别表goods_category进行关联。

举例3：
球员player表：球员编号、姓名、球队名称和球队主教练。现在，我们把属性之间的依赖关系画出
来，如下图所示：

你能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依
赖于球员编号，因此不符合 3NF 的要求。
如果要达到 3NF 的要求，需要把数据表拆成下面这样：

举例4：
修改第二范式中的举例3。
此时的Orders关系包含 orderid、orderdate、customerid 和 companyname 属性，主键定义为 orderid。
customerid 和companyname均依赖于主键——orderid。例如，你需要通过orderid主键来查找代表订单中
客户的customerid，同样，你需要通过 orderid 主键查找订单中客户的公司名称（companyname）。然
而， customerid和companyname也是互相依靠的。为满足第三范式，可以改写如下：

符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：“每个非键属性依赖于键，依赖于
整个键，并且除了键别无他物”。

反范式化

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要
2. 在数据规范化的同时 , 要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询

应用举例

员工的信息存储在 employees 表中，部门信息存储在 departments 表中。通过 employees 表中的
department_id字段与 departments 表建立关联关系。如果要查询一个员工所在部门的名称：

select employee_id,department_name
from employees e join departments d
on e.department_id = d.department_id;

如果经常需要进行这个操作，连接查询就会浪费很多时间。可以在 employees 表中增加一个冗余字段
department_name，这样就不用每次都进行连接操作了。

反范式化的 goods商品信息表设计如下：

举例3：我们有 2 个表，分别是商品流水表（atguigu.trans ）和商品信息表
（atguigu.goodsinfo）。商品流水表里有 400 万条流水记录，商品信息表里有 2000 条商品记录。

在实际应用中，我们在显示课程评论的时候，通常会显示这个学生的昵称，而不是学生 ID，因此当我们
想要查询某个课程的前 1000 条评论时，需要关联 class_comment 和 student这两张表来进行查询。
实验数据：模拟两张百万量级的数据表
为了更好地进行 SQL 优化实验，我们需要给学生表和课程评论表随机模拟出百万量级的数据。我们可以
通过存储过程来实现模拟数据。
反范式优化实验对比
如果我们想要查询课程 ID 为 10001 的前 1000 条评论，需要写成下面这样：

SELECT p.comment_text, p.comment_time, stu.stu_name
FROM class_comment AS p LEFT JOIN student AS stu
ON p.stu_id = stu.stu_id
WHERE p.class_id = 10001
ORDER BY p.comment_id DESC
LIMIT 1000;

如果我们想要提升查询的效率，可以允许适当的数据冗余，也就是在商品评论表中增加用户昵称字段，
在 class_comment 数据表的基础上增加 stu_name 字段，就得到了 class_comment2 数据表。
这样一来，只需单表查询就可以得到数据集结果：

SELECT comment_text, comment_time, stu_name
FROM class_comment2
WHERE class_id = 10001
ORDER BY class_id DESC LIMIT 1000;

优化之后只需要扫描一次聚集索引即可，运行时间为 0.039 秒，查询时间是之前的 1/10。你能看到，
在数据量大的情况下，查询效率会有显著的提升。

3 反范式的新问题
存储空间变大了
一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

反范式的适用场景

当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采取反范式的优化

增加冗余字段的建议

历史快照、历史数据的需要

在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每
次发生的订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这
些冗余信息是非常有必要的。
反范式优化也常用在数据仓库的设计中，因为数据仓库通常存储历史数据，对增删改的实时性要求不
强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

BCNF(巴斯范式)

在这个表中，一个仓库只有一个管理员，同时一个管理员也只管理一个仓库。我们先来梳理下这些属性
之间的依赖关系。
仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个
属性。这样，我们就可以找到数据表的候选键。
候选键：是（管理员，物品名）和（仓库名，物品名），然后我们从候选键中选择一个作为主键，比
如（仓库名，物品名）。
主属性：包含在任一候选键中的属性，也就是仓库名，管理员和物品名。
非主属性：数量这个属性。

是否符合三范式

如何判断一张表的范式呢？我们需要根据范式的等级，从低到高来进行判断。
首先，数据表每个属性都是原子性的，符合 1NF 的要求；
其次，数据表中非主属性”数量“都与候选键全部依赖，（仓库名，物品名）决定数量，（管理员，物品
名）决定数量。因此，数据表符合 2NF 的要求；
最后，数据表中的非主属性，不传递依赖于候选键。因此符合 3NF 的要求。

存在的问题

既然数据表已经符合了 3NF 的要求，是不是就不存在问题了呢？我们来看下面的情况：

增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因
此会出现插入异常；
如果仓库更换了管理员，我们就可能会修改数据表中的多条记录；
如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。
你能看到，即便数据表符合 3NF 的要求，同样可能存在插入，更新和删除数据的异常情况。

问题解决

首先我们需要确认造成异常的原因：主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，
这样就有可能导致上面的异常情况。因此引入BCNF，它在 3NF 的基础上消除了主属性对候选键的部分依
赖或者传递依赖关系。

如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于
BCNF。
根据 BCNF 的要求，我们需要把仓库管理关系 warehouse_keeper 表拆分成下面这样：
仓库表：（仓库名，管理员）
库存表：（仓库名，物品名，数量）

这样就不存在主属性对于候选键的部分依赖或传递依赖，上面数据表的设计就符合 BCNF。
再举例：
有一个学生导师表，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主
键。

这个表的设计满足三范式，但是这里存在另一个依赖关系，“专业”依赖于“导师”，也就是说每个导师只
做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。
所以这个表的部分主键Major依赖于非主键属性Advisor，那么我们可以进行以下的调整，拆分成2个表：
学生导师表：

第四范式

举例1：职工表(职工编号，职工孩子姓名，职工选修课程)。
在这个表中，同一个职工可能会有多个职工孩子姓名。同样，同一个职工也可能会有多个职工选修课
程，即这里存在着多值事实，不符合第四范式。
如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如：职工表一 (职工编
号，职工孩子姓名)，职工表二 (职工编号，职工选修课程)，两个表都只有一个多值事实，所以符合第四
范式。

比如我们建立课程、教师、教材的模型。我们规定，每门课程有对应的一组教师，每门课程也有对应的
一组教材，一门课程使用的教材和教师没有关系。我们建立的关系表如下：
课程ID，教师ID，教材ID；这三列作为联合主键。
为了表述方便，我们用Name代替ID，这样更容易看懂：

这个表除了主键，就没有其他字段了，所以肯定满足BC范式，但是却存在多值依赖导致的异常。
假如我们下学期想采用一本新的英版高数教材，但是还没确定具体哪个老师来教，那么我们就无法在这
个表中维护Course高数和Book英版高数教材的的关系。
解决办法是我们把这个多值依赖的表拆解成2个表，分别建立关系。这是我们拆分后的表：

第五范式、域键范式

除了第四范式外，我们还有更高级的第五范式（又称完美范式）和域键范式（DKNF）。
在满足第四范式（4NF）的基础上，消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连
接依赖均由R的候选键所隐含，则称此关系模式符合第五范式。
函数依赖是多值依赖的一种特殊的情况，而多值依赖实际上是连接依赖的一种特殊情况。但连接依赖不
像函数依赖和多值依赖可以由语义直接导出，而是在关系连接运算时才反映出来。存在连接依赖的关系
模式仍可能遇到数据冗余及插入、修改、删除异常等问题。
第五范式处理的是无损连接问题，这个范式基本没有实际意义，因为无损连接很少出现，而且难以察
觉。而域键范式试图定义一个终极范式，该范式考虑所有的依赖和约束类型，但是实用价值也是最小
的，只存在理论研究中。

实战案例

ER模型

ER 模型中有三个要素，分别是实体、属性和关系。

实体，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用矩形来表
示。实体分为两类，分别是强实体和弱实体。强实体是指不依赖于其他实体的实体；弱实体是指对另
一个实体有很强的依赖关系的实体。
属性，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用椭圆形来表示。
关系，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模
型中用菱形来表示。
注意：实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在
的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。

关系的类型

ER3个要素，关系：：一对一，一对多，多对一

一对一：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是一对一的关系。一个
人只能有一个身份证信息，一个身份证信息也只属于一个人。
一对多：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关
系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级都有多个学生，每个学
生则对应一个班级，班级对学生就是一对多的关系。
多对多：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之
间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购
商品。再比如一个选课表，有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目，这
就是多对多的关系。

建模分析

ER 模型看起来比较麻烦，但是对我们把控项目整体非常重要。如果你只是开发一个小应用，或许简单设
计几个表够用了，一旦要设计有一定规模的应用，在项目的初始阶段，建立完整的 ER 模型就非常关键
了。开发应用项目的实质，其实就是建模。
我们设计的案例是电商业务，由于电商业务太过庞大且复杂，所以我们做了业务简化，比如针对
SKU（StockKeepingUnit，库存量单位）和SPU（Standard Product Unit，标准化产品单元）的含义上，我
们直接使用了SKU，并没有提及SPU的概念。本次电商业务设计总共有8个实体，如下所示。

地址实体
用户实体
购物车实体
评论实体
商品实体
商品分类实体
订单实体
订单详情实体

其中，用户和商品分类是强实体，因为它们不需要依赖其他任何实体。而其他属于弱实体，因为它们
虽然都可以独立存在，但是它们都依赖用户这个实体，因此都是弱实体。知道了这些要素，我们就可以
给电商业务创建 ER 模型了，如图

在这个图中，地址和用户之间的添加关系，是一对多的关系，而商品和商品详情示一对1的关系，商品和
订单是多对多的关系。这个 ER 模型，包括了 8个实体之间的 8种关系。
（1）用户可以在电商平台添加多个地址；
（2）用户只能拥有一个购物车；
（3）用户可以生成多个订单；
（4）用户可以发表多条评论；
（5）一件商品可以有多条评论；
（6）每一个商品分类包含多种商品；
（7）一个订单可以包含多个商品，一个商品可以在多个订单里。
（8）订单中又包含多个订单详情，因为一个订单中可能包含不同种类的商品

ER 模型的细化

有了这个 ER 模型，我们就可以从整体上理解电商的业务了。刚刚的 ER 模型展示了电商业务的框架，
但是只包括了订单，地址，用户，购物车，评论，商品，商品分类和订单详情这八个实体，以及它们之
间的关系，还不能对应到具体的表，以及表与表之间的关联。我们需要把属性加上，用椭圆来表示，
这样我们得到的 ER 模型就更加完整了。

因此，我们需要进一步去设计一下这个 ER 模型的各个局部，也就是细化下电商的具体业务流程，然后把
它们综合到一起，形成一个完整的 ER 模型。这样可以帮助我们理清数据库的设计思路。
接下来，我们再分析一下各个实体都有哪些属性，如下所示。

（1） 地址实体 包括用户编号、省、市、地区、收件人、联系电话、是否是默认地址。
（2） 用户实体 包括用户编号、用户名称、昵称、用户密码、手机号、邮箱、头像、用户级别。
（3） 购物车实体 包括购物车编号、用户编号、商品编号、商品数量、图片文件url。（4） 订单实体 包括订单编号、收货人、收件人电话、总金额、用户编号、付款方式、送货地址、下单
时间。
（5） 订单详情实体 包括订单详情编号、订单编号、商品名称、商品编号、商品数量。
（6） 商品实体 包括商品编号、价格、商品名称、分类编号、是否销售，规格、颜色。
（7） 评论实体 包括评论id、评论内容、评论时间、用户编号、商品编号
（8） 商品分类实体 包括类别编号、类别名称、父类别编号

ER 模型图转换成数据表

通过绘制 ER 模型，我们已经理清了业务逻辑，现在，我们就要进行非常重要的一步了：把绘制好的 ER
模型，转换成具体的数据表，下面介绍下转换的原则：
（1）一个实体通常转换成一个数据表；
（2）一个多对多的关系，通常也转换成一个数据表；
（3）一个 1 对 1 ，或者 1 对多的关系，往往通过表的外键来表达，而不是设计一个新的数据表；
（4）属性转换成表的字段。

下面结合前面的ER模型，具体讲解一下怎么运用这些转换的原则，把 ER 模型转换成具体的数据表，从
而把抽象出来的数据模型，落实到具体的数据库设计当中。

其实，任何一个基于数据库的应用项目，都可以通过这种先建立 ER 模型，再转换成数据表的方式，
完成数据库的设计工作。创建 ER 模型不是目的，目的是把业务逻辑梳理清楚，设计出优秀的数据库。我
建议你不是为了建模而建模，要利用创建 ER 模型的过程来整理思路，这样创建 ER 模型才有意义。

数据表的设计原则

数据表个数越少越好
数据表中字段个数越少越好
数据表中联合主键字段个数越少越好
使用主键和外键越多越好

注意：这个原则并不是绝对的，有时候我们需要牺牲数据的冗余度来换取数据处理的效率。

数据库对象编写建议

【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字
母开头。
2. 【强制】库名中英文 一律小写 ，不同单词采用 下划线 分割。须见名知意。
3. 【强制】库的名称格式：业务系统名称_子系统名。
4. 【强制】库名禁止使用关键字（如type,order等）。
5. 【强制】创建数据库时必须 显式指定字符集 ，并且字符集只能是utf8或者utf8mb4。
创建数据库SQL举例：CREATE DATABASE crm_fund DEFAULT CHARACTER SET 'utf8' ;
6. 【建议】对于程序连接数据库账号，遵循 权限最小原则
使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号 原则上不准有drop权限 。
7. 【建议】临时库以 tmp_ 为前缀，并以日期为后缀；
备份库以 bak_ 为前缀，并以日期为后缀

关于表、列

1. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议
以 英文字母开头 。
2. 【强制】 表名、列名一律小写 ，不同单词采用下划线分割。须见名知意。
3. 【强制】表名要求有模块名强相关，同一模块的表名尽量使用 统一前缀 。比如：crm_fund_item
4. 【强制】创建表时必须 显式指定字符集 为utf8或utf8mb4。
5. 【强制】表名、列名禁止使用关键字（如type,order等）。
6. 【强制】创建表时必须 显式指定表存储引擎 类型。如无特殊需求，一律为InnoDB。
7. 【强制】建表必须有comment。
8. 【强制】字段命名应尽可能使用表达实际含义的英文单词或 缩写 。如：公司 ID，不要使用
corporation_id, 而用corp_id 即可。
9. 【强制】布尔值类型的字段命名为 is_描述 。如member表上表示是否为enabled的会员的字段命
名为 is_enabled。
10. 【强制】禁止在数据库中存储图片、文件等大的二进制数据
通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随
机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
11. 【建议】建表时关于主键： 表必须有主键 (1)强制要求主键为id，类型为int或bigint，且为
auto_increment 建议使用unsigned无符号型。 (2)标识表里每一行主体的字段不要设为主键，建议
设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机
插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。
12. 【建议】核心表（如用户表）必须有行数据的 创建时间字段 （create_time）和 最后更新时间字段
（update_time），便于查问题。
13. 【建议】表中所有字段尽量都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT值 。 因为使用
NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问
题。
14. 【建议】所有存储相同数据的 列名和列类型必须一致 （一般作为关联列，如果查询时关联列类型
不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
15. 【建议】中间表（或临时表）用于保留中间结果集，名称以 tmp_ 开头。
备份表用于备份或抓取源表快照，名称以 bak_ 开头。中间表和备份表定期清理。
16. 【示范】一个较为规范的建表语句：

CREATE TABLE user_info (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_id` bigint(11) NOT NULL COMMENT '用户id',
`username` varchar(45) NOT NULL COMMENT '真实姓名',
`email` varchar(30) NOT NULL COMMENT '用户邮箱',
`nickname` varchar(45) NOT NULL COMMENT '昵称',
`birthday` date NOT NULL COMMENT '生日',
`sex` tinyint(4) DEFAULT '0' COMMENT '性别',
`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
`user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
`user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '修改时间',
`user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未
通过，4为还未提交审核',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user_id` (`user_id`),
KEY `idx_username`(`username`),
KEY `idx_create_time_status`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息'

【建议】创建表时，可以使用可视化工具。这样可以确保表、字段相关的约定都能设置上。
实际上，我们通常很少自己写 DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。
可视化工具除了方便，还能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构
的导出和导入

关于索引

【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新。
【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。
【建议】主键的名称以 pk_ 开头，唯一键以 uni_ 或 uk_ 开头，普通索引以 idx_ 开头，一律
使用小写格式，以字段的名称或缩写作为后缀。
【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:
sample 表 member_id 上的索引：idx_sample_mid。
【建议】单个表上的索引个数不能超过6个。
【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。
【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。
【建议】建表或加索引时，保证表里互相不存在冗余索引。比如：如果表里已经存在key(a,b)，
则key(a)为冗余索引，需要删除。

SQL编写

【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。
【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。
【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。值过多虽然上线很
快，但会引起主从同步延迟。
【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以
内。
【建议】线上环境，多表 JOIN 不要超过5个表。
【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER
BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果
集请保持在1000行以内，否则SQL会很慢。
【建议】对单表的多次alter操作必须合并为一次
对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整
合在一起。因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极
大影响。
【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
【建议】事务里包含SQL不超过5个。
因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX;
PRIMARY KEY (id),
UNIQUE KEY uniq_user_id (user_id),
KEY idx_username(username),
KEY idx_create_time_status(create_time,user_review_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息'否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

PowerDesigner的使用

第12章_数据库其它调优策略

数据库调优的措施

调优的目标

尽可能节省系统资源，以便系统可以提供更大负荷的服务。（吞吐量更大）
合理的结构设计和参数调整，以提高用户操作响应的速度。（响应速度更快）
减少系统的瓶颈，提高MySQL数据库整体的性能。

如何定位调优问题

用户的反馈（主要）
日志分析（主要）
服务器资源使用监控
数据库内部状况监控
其它

除了活动会话监控以外，我们也可以对事务、锁等待等进行监控，这些都可以帮助我们对数据库的运
行状态有更全面的认识。

调优的维度和步骤

我们需要调优的对象是整个数据库管理系统，它不仅包括 SQL 查询，还包括数据库的部署配置、架构
等。从这个角度来说，我们思考的维度就不仅仅局限在 SQL 优化上了。通过如下的步骤我们进行梳理：

第1步：选择适合的 DBMS

NoSQL 包括键值型数据库，文档型，搜索引G擎，列式存储，图形数据库

列式数据库可以大幅度降低系统IO，适合分布式文件系统，但是如果数据需要频繁的增删改，列式存储不太适用

选择开源mysql进行存储，很多存储引擎可以选，进行事务处理可以选择InnoDB，非事务处理选择MyISAM

第2步：优化表设计

1.表结构尽量遵循三范式原则，这样结构更加清晰规范，减少冗余字段，同时也减少了在更新插入删除数据等异常情况发生

如果查询应用较多，尤其是进行多表联查时候，可以采用反范式进行优化，反范式采用空间换时间方式，听过增加冗余字段进行提高查询效率

表字段数据类型，关系到查询效率高低以及存储空间大小一般来说如果哦字段可以采用数值类型就不要采用个字符类型。字符长度要尽可能设计段短一些，针对字符类型来说，当确定字符长度固定时，就可以采用char类型，当长度不固定时候，通常采用varchar类型

数据表结构设计很基础，也很关键，好的表结构可以在业务发展用户量增加情况下依然发挥作用，不好的表结构设计会让数据变的非常臃肿，查询效率也会降低

第3步：优化逻辑查询

当我们建立好数据表之后，就可以对数据表进行增删改查，sql查询优化，逻辑查询优化，物理查询优化，逻辑查询头花就是通过改变sql语句内容让sql执行效率更高效，采用方式是对sql语句进行等价交换，对查询进行重写

sql查询重写包括了子查询优化，等价谓词重写，视图重写，条件简化，连接消除，嵌套连接消除

第4步：优化物理查询

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对
各种可能的访问路径进行估算，从而找到执行方式中代价最小的作为执行计划。在这个部分中，我们需
要掌握的重点是对索引的创建和使用。

单表扫描：可以全局，也可以局部

两张表连接：循环连接，hash连接合并连接

多张表连接：多张数据表连接，顺序很重要，因为不同连接路径查询效率不同，搜索空间也不不同，进行多表连接时候，搜索空间可能会达到很高数据量级，巨大搜索空间显然会占用更多资源因此我们需要通过调整连接顺序，将搜索空间调整在一个可接受范围内

第5步：使用 Redis 或 Memcached 作为缓存
除了可以对 SQL 本身进行优化以外，我们还可以请外援提升查因为数据都是存放到数据库中，我们需要从数据库层中取出数据放到内存中进行业务逻辑的操作，当用
户量增大的时候，如果频繁地进行数据查询，会消耗数据库的很多资源。如果我们将常用的数据直接放
到内存中，就会大幅提升查询的效率。
键值存储数据库可以帮我们解决这个问题。
常用的键值存储数据库有 Redis 和 Memcached，它们都可以将数据存放到内存中。

第6步：库级优化

1、读写分离

2、数据分片

优化MySQL服务器

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈直接决定MySQL数据库的运行速度
和效率。针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。（1）配置较大的内
存（2）配置高速磁盘系统（3）合理分布磁盘I/O （4）配置多处理器

优化MySQL的参数

innodb_buffer_pool_size ：这个参数是Mysql数据库最重要的参数之一，表示InnoDB类型的 表
和索引的最大缓存 。它不仅仅缓存 索引数据 ，还会缓存 表的数据 。这个值越大，查询的速度就会越
快。但是这个值太大会影响操作系统的性能。
key_buffer_size ：表示 索引缓冲区的大小 。索引缓冲区是所有的 线程共享 。增加索引缓冲区可
以得到更好处理的索引（对所有读和多重写）。当然，这个值不是越大越好，它的大小取决于内存
的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存在 4GB 左右
的服务器该参数可设置为 256M 或 384M 。table_cache ：表示 同时打开的表的个数 。这个值越大，能够同时打开的表的个数越多。物理内
存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表
太多会影响操作系统的性能。
query_cache_size ：表示 查询缓冲区的大小 。可以通过在MySQL控制台观察，如果

Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，就要增加Query_cache_size
的值；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效
率，那么可以考虑不用查询缓存；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很
多。MySQL8.0之后失效。该参数需要和query_cache_type配合使用。
query_cache_type 的值是0时，所有的查询都不使用查询缓存区。但是query_cache_type=0并不
会导致MySQL释放query_cache_size所配置的缓存区内存。
当query_cache_type=1时，所有的查询都将使用查询缓存区，除非在查询语句中指定
SQL_NO_CACHE ，如SELECT SQL_NO_CACHE * FROM tbl_name。
当query_cache_type=2时，只有在查询语句中使用 SQL_CACHE 关键字，查询才会使用查询缓
存区。使用查询缓存区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的
查询操作的情况。

sort_buffer_size ：表示每个 需要进行排序的线程分配的缓冲区的大小 。增加这个参数的值可以
提高 ORDER BY 或 GROUP BY 操作的速度。默认数值是2 097 144字节（约2MB）。对于内存在4GB
左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6
＝ 600MB。

join_buffer_size = 8M ：表示 联合查询操作所能使用的缓冲区大小 ，和sort_buffer_size一样，
该参数对应的分配内存也是每个连接独享。

read_buffer_size ：表示 每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节） 。当线
程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参
数的值。默认为64K，可以设置为4M。

innodb_flush_log_at_trx_commit ：表示 何时将缓冲区的数据写入日志文件 ，并且将日志文件
写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。该参数的默认值
为1。
值为 0 时，表示 每秒1次 的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的
commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导
致上一秒钟所有事务数据的丢失。
值为 1 时，表示 每次提交事务时 将数据写入日志文件并将日志文件写入磁盘进行同步。该模
式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入
（flush）硬盘。
值为 2 时，表示 每次提交事务时 将数据写入日志文件， 每隔1秒 将日志文件写入磁盘。该模
式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数
据才可能丢失。

innodb_log_buffer_size ：这是 InnoDB 存储引擎的 事务日志所使用的缓冲区 。为了提高性能，
也是先将信息写入 Innodb Log Buffer 中，当满足 innodb_flush_log_trx_commit 参数所设置的相应条
件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。

max_connections ：表示 允许连接到MySQL数据库的最大数量 ，默认值是 151 。如果状态变量
connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接
数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。在Linux 平台下，性能好的
服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。这个连接数 不是越大
越好 ，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。back_log ：用于 控制MySQL监听TCP端口时设置的积压请求栈大小 。如果MySql的连接数达到
max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即
back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默
认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 对于Linux系统推荐设置为小于512
的整数，但最大不超过900。
如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。

thread_cache_size ： 线程池缓存线程数量的大小 ，当客户端断开连接后将当前线程缓存起来，
当在接到新的连接请求时快速响应无需创建新的线程 。这尤其对那些使用短连接的应用程序来说可
以极大的提高创建连接的效率。那么为了提高性能可以增大该参数的值。默认为60，可以设置为
120。
可以通过如下几个MySQL状态值来适当调整线程池的大小：
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+-------------------+-------+
4 rows in set (0.01 sec)

当 Threads_cached 越来越少，但 Threads_connected 始终不降，且 Threads_created 持续升高，可
适当增加 thread_cache_size 的大小。
wait_timeout ：指定 一个请求的最大连接时间 ，对于4GB左右内存的服务器可以设置为5-10。
interactive_timeout ：表示服务器在关闭连接前等待行动的秒数。
这里给出一份my.cnf的参考配置：

[mysqld]
port = 3306 serverid = 1 socket = /tmp/mysql.sock skip-locking #避免MySQL的外部锁定，减少
出错几率增强稳定性。 skip-name-resolve #禁止MySQL对外部连接进行DNS解析，使用这一选
项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权
都要使用IP地址方式，否则MySQL将无法正常处理连接请求！ back_log = 384
key_buffer_size = 256M max_allowed_packet = 4M thread_stack = 256K
table_cache = 128K sort_buffer_size = 6M read_buffer_size = 4M
read_rnd_buffer_size=16M join_buffer_size = 8M myisam_sort_buffer_size =
64M table_cache = 512 thread_cache_size = 64 query_cache_size = 64M
tmp_table_size = 256M max_connections = 768 max_connect_errors = 10000000
wait_timeout = 10 thread_concurrency = 8 #该参数取值为服务器逻辑CPU数量*2，在本
例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4*2=8 skipnetworking #开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接
的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！ table_cache=1024
innodb_additional_mem_pool_size=4M #默认为2M innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M #默认为1M innodb_thread_concurrency=8 #你的服务器CPU
有几个就设置为几。建议用默认一般为8 tmp_table_size=64M #默认为16M，调到64-256最挂
thread_cache_size=120 query_cache_size=32M

优化数据库结构

拆分表：冷热数据分离

举例1： 会员members表 存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电
话、个人描述字段。其中地址、电话、个人描述等字段并不常用，可以将这些不常用的字段分解出另一
个表。将这个表取名叫members_detail，表中有member_id、address、telephone、description等字段。
这样就把会员表分成了两个表，分别为 members表 和 members_detail表 。

创建这两个表的SQL语句如下：

CREATE TABLE members (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(50) DEFAULT NULL,
password varchar(50) DEFAULT NULL,
last_login_time datetime DEFAULT NULL,
last_login_ip varchar(100) DEFAULT NULL,
PRIMARY KEY(Id)
);
CREATE TABLE members_detail (
Member_id int(11) NOT NULL DEFAULT 0,
address varchar(255) DEFAULT NULL,
telephone varchar(255) DEFAULT NULL,
description text
);

如果需要查询会员的基本信息或详细信息，那么可以用会员的id来查询。如果需要将会员的基本信息和
详细信息同时显示，那么可以将members表和members_detail表进行联合查询，查询语句如下：

SELECT * FROM members LEFT JOIN members_detail on members.id =
members_detail.member_id;

通过这种分解可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表，可以通过这种分解的
方式来优化数据库的性能。

增加中间表

举例1：学生信息表和班级表的SQL语句如下：

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,

`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

现在有一个模块需要经常查询带有学生名称（name）、学生所在班级名称（className）、学生班级班
长（monitor）的学生信息。根据这种情况可以创建一个 temp_student 表。temp_student表中存储学生
名称（stu_name）、学生所在班级名称（className）和学生班级班长（monitor）信息。创建表的语句
如下：

CREATE TABLE `temp_student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stu_name` INT NOT NULL ,
`className` VARCHAR(20) DEFAULT NULL,
`monitor` INT(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

接下来，从学生信息表和班级表中查询相关信息存储到临时表中：

insert into temp_student(stu_name,className,monitor)
select s.name,c.className,c.monitor
from student as s,class as c
where s.classId = c.id

以后，可以直接从temp_student表中查询学生名称、班级名称和班级班长，而不用每次都进行联合查
询。这样可以提高数据库的查询速度。

增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。
但是，合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。尤其在数据量大，而
且需要频繁进行连接的时候，为了提升效率，我们也可以考虑增加冗余字段来减少连接。

优化数据类型

情况1：对整数类型数据进行优化。
遇到整数类型的字段可以用 INT 型。这样做的理由是，INT 型数据有足够大的取值范围，不用担心数
据超出取值范围的问题。刚开始做项目的时候，首先要保证系统的稳定性，这样设计字段类型是可以
的。但在数据量很大的时候，数据类型的定义，在很大程度上会影响到系统整体的执行效率。
对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型 UNSIGNED 来存储。因为无符号
相对于有符号，同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255，多
出一倍的存储空间。

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。
跟文本类型数据相比，大整数往往占用更少的存储空间，因此，在存取和比对的时候，可以占用更少的
内存空间。所以，在二者皆可用的情况下，尽量使用整数类型，这样可以提高查询的效率。如：将IP地
址转换成整型数据。

情况3：避免使用TEXT、BLOB数据类型

情况4：避免使用ENUM类型
情况5：使用TIMESTAMP存储时间
情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充
分发挥资源的效率，使系统达到最优。

优化插入记录的速度

MyISAM引擎的表：
① 禁用索引
② 禁用唯一性检查
③ 使用批量插入

insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',17,1);
insert into student values(3,'wangwu',17,1);
insert into student values(4,'zhaoliu',19,1);

使用一条INSERT语句插入多条记录的情形如下：

insert into student values
(1,'zhangsan',18,1),
(2,'lisi',17,1),
(3,'wangwu',17,1),
(4,'zhaoliu',19,1);

第2种情形的插入速度要比第1种情形快。
④ 使用LOAD DATA INFILE 批量导入

InnoDB引擎的表： ① 禁用唯一性检查
② 禁用外键检查
③ 禁止自动提交

使用非空约束

在设计字段的时候，如果业务允许，建议尽量使用非空约束

分析表、检查表与优化表

MySQL中提供了ANALYZE TABLE语句分析表，ANALYZE TABLE语句的基本语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

默认的，MySQL服务会将 ANALYZE TABLE语句写到binlog中，以便在主从架构中，从服务能够同步数据。
可以添加参数LOCAL 或者 NO_WRITE_TO_BINLOG取消将语句写到binlog中。
使用 ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取
表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表，但是不能
作用于视图。

ANALYZE TABLE分析后的统计结果会反应到 cardinality 的值，该值统计了表中某一键所在的列不重复
的值的个数。该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使
用。也就是索引列的cardinality的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查
询条件，存储引擎实际查询的时候使用的概率就越小。下面通过例子来验证下。cardinality可以通过
SHOW INDEX FROM 表名查看。

检查表

MySQL中可以使用 CHECK TABLE 语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表
是否存在错误。CHECK TABLE语句在执行过程中也会给表加上只读锁。
对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视
图是否有错误，比如在视图定义中被引用的表已不存在。该语句的基本语法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

其中，tbl_name是表名；option参数有5个取值，分别是QUICK、FAST、MEDIUM、EXTENDED和
CHANGED。各个选项的意义分别是：

QUICK ：不扫描行，不检查错误的连接。
FAST ：只检查没有被正确关闭的表。
CHANGED ：只检查上次检查后被更改的表和没有被正确关闭的表。
MEDIUM ：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算
出的校验和验证这一点。
EXTENDED ：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但
是花的时间较长。
option只对MyISAM类型的表有效，对InnoDB类型的表无效。比如

该语句对于检查的表可能会产生多行信息。最后一行有一个状态的 Msg_type 值，Msg_text 通常为 OK。
如果得到的不是 OK，通常要对其进行修复；是 OK 说明表已经是最新的了。表已经是最新的，意味着存
储引擎对这张表不必进行检查。

方式1：OPTIMIZE TABLE

MySQL中使用 OPTIMIZE TABLE 语句来优化表。但是，OPTILMIZE TABLE语句只能优化表中的
VARCHAR 、 BLOB 或 TEXT 类型的字段。一个表使用了这些字段的数据类型，若已经删除了表的一大部
分数据，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更新，则
应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
OPTIMIZE TABLE 语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上只读锁。
OPTILMIZE TABLE语句的基本语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

LOCAL | NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志。

执行完毕，Msg_text显示
‘numysql.SYS_APP_USER’, ‘optimize’, ‘note’, ‘Table does not support optimize, doing recreate +
analyze instead’
原因是我服务器上的MySQL是InnoDB存储引擎。

在MyISAM中，是先分析这张表，然后会整理相关的MySQL datafile，之后回收未使用的空间；在InnoDB
中，回收空间是简单通过Alter table进行整理空间。在优化期间，MySQL会创建一个临时表，优化完成之
后会删除原始表，然后会将临时表rename成为原始表。

说明：在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更
新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。

小结

修改数据类型,节省存储空间同时,你要考虑到数据不能超过取值范围

增加冗余字段时候,不要忘了确保数据一致性

把大表拆分,也也意味着你的查询会增加新的连接,从而增加额外的开销运维成本

大表优化

限定查询的范围

禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制
在一个月的范围内；

读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

一主一从模式：

垂直拆分

当数据量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，
减少对单一数据库服务器的访问压力。

垂直拆分优点:可以使得列数据变小,在查询减少读取的Block数,减少IO次数,此外,垂直分区可以简化表的结构,易于维护

垂直拆分缺点:主键会出现冗余,需要管理冗余列.并引起JOIN操作,此外,垂直拆分会让事务变的更加复杂

水平拆分

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的
Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在
谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

其它调优策略

服务器语句超时处理

在MySQL 8.0中可以设置服务器语句超时的限制，单位可以达到毫秒级别。当中断的执行语句超过设置的
毫秒数后，服务器将终止查询影响不大的事务或连接，然后将错误报给客户端。
设置服务器语句超时的限制，可以通过设置系统变量 MAX_EXECUTION_TIME 来实现。默认情况下，
MAX_EXECUTION_TIME的值为0，代表没有时间限制。例如：

SET GLOBAL MAX_EXECUTION_TIME=2000;
SET SESSION MAX_EXECUTION_TIME=2000; #指定该会话中SELECT语句的超时时间

创建全局通用表空间

MySQL 8.0新特性：隐藏索引对调优的帮助

数据库事务

数据库事务概述

存储引擎支持情况

SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些，以及这些存储引擎是否支持事务。

基本概念

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

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

事务的ACID特性

原子性:原子性是事务是一个不可分割的工作单位,要么全部提交,要么全部失败回滚

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

那么什么是合法数据状态,满足预定的约束,状态就叫做合法状态,通俗一点,这个状态是由你自己来定义的,数据就是一致的,不满足这个状态,数据就是不一致的,如果事务中某个操作失败了,系统就会自动撤销当前正在执行的事务,返回到事务做之前的状态,

隔离型

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

如果无法保证隔离性会怎么样？假设A账户有200元，B账户0元。A账户往B账户转账两次，每次金额为50
元，分别在两个事务中执行。如果无法保证隔离性，会出现下面的情形：

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';
UPDATE accounts SET money = money + 50 WHERE NAME = 'BB'

持久性（durability）：

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库
故障不应该对其有任何影响。
持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改
的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做
的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执
行，从而使事务具有持久性。

事务的状态

活动的

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

部分提交的

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

失败的

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

中止的（aborted）

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

提交的（committed）

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

如何使用事务

显式事务

步骤1： START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。

mysql> BEGIN;
#或者
mysql> START TRANSACTION;

START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个修饰符：

① READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不
能修改数据。
② READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，
也可以修改数据。
③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

步骤2：一系列事务中的操作（主要是DML，不含DDL）

步骤3：提交事务或中止事务（即回滚事务）

# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;
# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;
# 将事务回滚到某个保存点。
mysql> ROLLBACK TO [SAVEPOINT]

隐式事务

系统变量 autocommit

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)

当然，如果我们想关闭这种自动提交的功能，可以使用下边两种方法之一：
显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。这样在本次事务提交或者回
滚前会暂时关闭掉自动提交的功能。
把系统变量 autocommit 的值设置为 OFF ，就像这样：

SET autocommit = OFF;
#或
SET autocommit = 0;

隐式提交数据的情况

数据定义语言

隐式提交数据的情况

① 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了
另一个事务时，会隐式的提交上一个事务。即：
② 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会隐式的提交前边语
句所属的事务。

③ 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事
务。

加载数据的语句
关于MySQL复制的一些语句
其它的一些语句

使用举例1：提交与回滚

我们看下在 MySQL 的默认状态下，下面这个事务最后的处理结果是什么

CREATE TABLE user(name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;
BEGIN;
INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;
SELECT * FROM user;

mysql> select * from user;
+--------+
| name |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

CREATE TABLE user (name varchar(20), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;
INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;

mysql> SELECT * FROM user;
+--------+
| name |
+--------+
| 张三 |
| 李四 |
+--------+
2 行于数据集 (0.01 秒)

情况三

CREATE TABLE user(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;
INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;
SELECT * FROM user;

mysql> SELECT * FROM user;
+--------+
| name |
+--------+
| 张三 |
+--------+
1 行于数据集 (0.01 秒)

当我们设置 autocommit=0 时，不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事
务，都需要用 COMMIT 进行提交，让事务生效，使用 ROLLBACK 对事务进行回滚。
当我们设置 autocommit=1 时，每条 SQL 语句都会自动进行提交。 不过这时，如果你采用 START
TRANSACTION 或者 BEGIN 的方式来显式地开启事务，那么这个事务只有在 COMMIT 时才会生效，
在 ROLLBACK 时才会回滚。

事务隔离级别

MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每
个客户端与服务器连接上之后，就可以称为一个会话（ Session ）。每个客户端都可以在自己的会话中
向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理
多个事务。事务有隔离性的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排
队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样对性能影响太大，我们既想保持
事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，那就看二者如何权衡取
舍了。

数据准备

CREATE TABLE student (
studentno INT,
name VARCHAR(20),
class varchar(20),
PRIMARY KEY (studentno)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO student VALUES(1, '小谷', '1班');

mysql> select * from student;
+-----------+--------+-------+
| studentno | name | class |
+-----------+--------+-------+
| 1 | 小谷 | 1班 |
+-----------+--------+-------+
1 row in set (0.00 sec)

数据并发问题☆

脏写

对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数
据，那就意味着发生了 脏写

脏读

对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。
之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新
为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为'张三'，而
Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象
就称之为 脏读 。

不可重复读

对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后
Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。
我们在Session B中提交了几个 隐式事务 （注意是隐式事务，意味着语句结束事务就提交了），这些事务
都修改了studentno列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看
到最新的值，这种现象也被称之为 不可重复读 。

幻读

对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插
入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。
Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为'张三'的记录；
之后Session B中提交了一个 隐式事务 ，该事务向表student中插入了一条新记录；之后Session A中的事务
再根据相同的条件 studentno > 0查询表student，得到的结果集中包含Session B中的事务新插入的那条记
录，这种现象也被称之为 幻读 。我们把新插入的那些记录称之为 幻影记录 。

SQL中的四种隔离级别

上面介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题有轻重缓急之分，我们给这些问题
按照严重性来排一下序：

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

我们愿意舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，并
发问题发生的就越多。 SQL标准 中设立了4个 隔离级别 ：
READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结
果。不能避免脏读、不可重复读、幻读。
READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做
的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可
重复读、幻读问题仍然存在。
REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提
交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍
然存在。这是MySQL的默认隔离级别。
SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止
其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避
免脏读、不可重复读和幻读。

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

脏写怎么没涉及到？因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。
不同的隔离级别有不同的现象，并有不同的锁和并发机制，隔离级别越高，数据库的并发性能就越差，4
种事务隔离级别与并发性能的关系如下：

MySQL支持的四种隔离级别

MySQL的默认隔离级别为REPEATABLE READ，我们可以手动修改一下事务的隔离级别。

# 查看隔离级别，MySQL 5.7.20的版本之前：
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
# MySQL 5.7.20版本之后，引入transaction_isolation来替换tx_isolation
# 查看隔离级别，MySQL 5.7.20的版本及之后：
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)
#或者不同MySQL版本中都可以使用的：
SELECT @@transaction_isolation;

如何设置事务的隔离级别

通过下面的语句修改事务的隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#其中，隔离级别格式：
> READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
#其中，隔离级别格式：
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

关于设置时使用GLOBAL或SESSION的影响：

使用 GLOBAL 关键字（在全局范围影响）：

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
#或
SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';

当前已存在的会话无效

只对执行完该语句之后产生的会话起作用

使用 SESSION 关键字（在会话范围影响）：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
#或
SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';

对当前会话所有后续事务有效

如果在当前事务之间执行,则对后续事务有效

该语句可以在已经开启事务中中间执行,但不影响当前正在执行的事务

小结：
数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性
就越好，但并发性越弱。

不同隔离级别举例

事务的常见分类

扁平事务

带有保存点的扁平事务

链事务

嵌套事务

分布式事务

第14章_MySQL事务日志

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？
    事务的隔离性由 锁机制 实现。
    而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。
    REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持
    久性。
    UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。
有的DBA或许会认为 UNDO 是 REDO 的逆过程，其实不然。

redo日志

为什么需要REDO日志

一方面，缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟，checkpoint机制可以保证数据的最终落盘，然
而由于checkpoint 并不是每次变更的时候就触发的，而是master线程隔一段时间去处理的。所以最坏的情
况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。
另一方面，事务包含持久性的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩
溃，这个事务对数据库中所做的更改也不能丢失。
那么如何保证这个持久性呢？一个简单的做法：在事务提交完成之前把该事务所修改的所有页面都刷新
到磁盘，但是这个简单粗暴的做法有些问题
另一个解决的思路：我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系
统崩溃，在重启后也能把这种修改恢复出来。所以我们其实没有必要在每次事务提交时就把该事务在内
存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好。比如，某个事务将系统
表空间中第10号页面中偏移量为 100 处的那个字节的值 1 改成 2 。我们只需要记录一下：将第0号表
空间的10号页面的偏移量为100处的值更新为 2 。

REDO日志的好处、特点

redo日志占用空间非常小

特点

redo日志是顺序写入磁盘的

事务执行过程,redo log 不断记录

redo组成

redo log 由两部分组成

重做日志缓冲,保存在内存中,是是易失的。

参数设置：innodb_log_buffer_size：

redo的整体流程

以一个更新事务为例，redo log 流转过程，如下图所示：

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

体会：
Write-Ahead Log(预先日志持久化)：在持久化一个数据页之前，先将内存中相应的日志页持久化。

redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以一
定的频率刷入到真正的redo log file 中。这里的一定频率怎么看待呢？这就是我们要说的刷盘策略。

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存
（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系
统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同
步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。
针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务
时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：
设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日
志的同步）
设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自
己决定什么时候同步到磁盘文件。

不同刷盘策略演示

写入redo log buffer 过程

补充概念：Mini-Transaction

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条
redo日志，画个图表示它们的关系就是这样：

redo 日志写入log buffer

每个mtr都会产生一组redo日志，用示意图来描述一下这些mtr产生的日志情况：

不同的事务可能是并发执行的，所以 T1 、 T2 之间的 mtr 可能是交替执行的。

redo log block的结构图

redo log file

undo log的生命周期

简要生成过程

只有Buffer Pool的流程：

当我们执行INSERT时：

begin;
INSERT INTO user (name) VALUES ("tom");

当我们执行UPDATE时：

UPDATE user SET id=2 WHERE id=1;

undo log是如何回滚的

以上面的例子来说，假设执行rollback，那么对应的流程应该是这样：
1. 通过undo no=3的日志把id=2的数据删除
2. 通过undo no=2的日志把id=1的数据的deletemark还原成0
3. 通过undo no=1的日志把id=1的数据的name还原成Tom
4. 通过undo no=0的日志把id=1的数据删除

undo log的删除

针对于insert undo log
因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删
除，不需要进行purge操作。
针对于update undo log
该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等
待purge线程进行最后的删除。

小结

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。
redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程。

锁

概述

需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL
的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而
言显得尤其重要，也更加复杂。

MySQL并发事务访问相同记录

读

读读情况,即并发事务相继读取相同记录,读取操作本身不会对记录有任何影响,并不会引起什么问题所以允许

写

写写并发事务相继对相同记录做出改动

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

小结

不加锁,意思就是不需要在内存中生成对应的锁结构,可以直接执行操作

获取锁成功,或者枷锁成功

意思就是在内存中生成对应的锁结构而且锁结构is_waitting属性是false,也就是事务可以继续执行操作

获取锁失败,或者枷锁失败,或者没有获取到锁

读-写或写读情况

读-写或写-读即一个事务进行读取操作,另一个进行改动操作,这种情况可能会发生脏读,不可重复读,幻读.

怎么解决脏读,不可重复读,幻读

读操作利用多版本并发控制 MVCC,写操作进行加锁

普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一
个ReadView，ReadView的存在本身就保证了 事务不可以读取到未提交的事务所做的更改 ，也就
是避免了脏读现象；
在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会
生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读
和幻读的问题。

方案二：读、写操作都采用加锁的方式。
小结对比发现：
采用 MVCC 方式的话，读-写操作彼此并不冲突，性能更高。
采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。
一般情况下我们当然愿意采用 MVCC 来解决读-写操作并发执行的问题，但是业务在某些特殊情况
下，要求必须采用加锁的方式执行。下面就讲解下MySQL中不同类别的锁。

从数据操作的类型划分：读锁、写锁

读锁,共享锁

英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会
互相影响，相互不阻塞的。

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

需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。

从数据操作的粒度划分：表级锁、页级锁、行锁

表锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级
别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其
他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务
中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行 DDL 语句也会
发生阻塞。这个过程其实是通过在 server层 使用一种称之为 元数据锁 （英文名： Metadata Locks ，
简称 MDL ）结构来实现的。

般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁和 X锁。只会在一些特殊情况下，比方说崩
溃恢复过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时，手动获取
InnoDB存储引擎提供的表t 的 S锁或者 X锁可以这么写：

LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。
LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。

不过尽量避免在使用InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供
什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的行锁，关于
InnoDB表级别的 S锁和 X锁大家了解一下就可以了。
MySQL的表级锁有两种模式：（以MyISAM表进行操作的演示）

表共享读锁（Table Read Lock）
表独占写锁

意向锁

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向
锁就是其中的一种表锁。

意向锁分为两种：
意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁:事务有意向对表中某些行加排他锁(X锁)

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

事务要获取某些行锁X锁,必须先获得表的IX锁

意向锁是由存储引擎自己维护的,用户无法动手操作意向锁,在为数据行加共享/排他锁之前,innodb会先获取数据行所在的数据表对应的意向锁

即：意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，
InooDB 会先获取该数据行所在数据表的对应意向锁。
意向锁的并发性

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排
他锁时的并发性。（不然我们直接用普通的表锁就行了）
我们扩展一下上面 teacher表的例子来概括一下意向锁的作用（一条数据从被锁定到被释放的过程中，可
能存在多种不同锁，但是这里我们只着重表现意向锁）。

结论
innodb 支持多粒度锁,在特定场景下,行级锁可以与与表级锁共存
IX,IS是表级锁,不会和行级的X,S发生冲突,只会和表级的X,S发生冲突
意向锁在保证并发性前提下,实现和行锁和表锁共存,且满足事务隔离要求

自增锁

在使用MySQL过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性。举例

CREATE TABLE `teacher` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> select * from teacher;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
2 rows in set (0.00 sec)

现在我们看到的上面插入数据只是一种简单的插入模式，所有插入数据的方式总共分为三类，分别是
“ Simple inserts ”，“ Bulk inserts ”和“ Mixed-mode inserts ”。

“Simple inserts” （简单插入）
可以预先确定要插入的行数（当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行
INSERT...VALUES() 和 REPLACE 语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行
数。
“Bulk inserts” （批量插入）
事先不知道要插入的行数（和所需自动递增值的数量）的语句。比如 INSERT ... SELECT ， REPLACE
... SELECT 和 LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列
分配一个新值。
“Mixed-mode inserts” （混合模式插入）
这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name)
VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。另一种类型的“混
合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE 。

innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式：

（1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)

在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有
AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个
表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证
master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行insert的
时候，对于AUTO-INC锁的争夺会限制并发能力。

（2）innodb_autoinc_lock_mode = 1(“连续”锁定模式)

在 MySQL 8.0 之前，连续锁定模式是默认的。
在这个模式下，“bulk inserts”仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT ...
SELECT，REPLACE ... SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。
对于“Simple inserts”（要插入的行数事先已知），则通过在 mutex（轻量锁）的控制下获得所需数量的
自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。不使用
表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple
inserts”等待AUTO-INC锁，如同它是一个“bulk inserts”。
（3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)

在锁定模式下,自动递增值保证所有并发执行所有类型insert语句是唯一且单调递增,但是由于多个语句同时生成数字,为为任何语句插入行生成值可能不是连续的

元数据锁

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比
如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一
列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

当对一个表做增删改查操作时候,加MDL读锁,当要对表做出结构变更操作时候,加MDL写锁

InnoDB中的行锁

记录锁（Record Locks）

记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。比如我们把id值为8的
那条记录加一个记录锁的示意图如图所示。仅仅是锁住了id值为8的记录，对周围的数据没有影响。

S型记录锁和X型记录锁

当一个事务获取了一条记录S型记录锁后,其他食物也可以继续获取该记录的S型记录锁,但不可以继续获取X型记录锁

当一个事务获取一条记录X记录锁,其他事务既不可以继续获取该记录的S型记录锁，也不
可以继续获取X型记录锁。

② 间隙锁（Gap Locks）

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方
案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读
取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为
Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为 gap锁。比如，把id值为8的那条
记录加一个gap锁的示意图如下。

图中id值为8的记录加了gap锁，意味着不允许别的事务在id值为8的记录前边的间隙插入新记录，

其实就是id列的值(3,8)这个区间新纪录是不允许立即插入的,

gap锁的提出仅仅是为了防止插入幻影记录而提出的。

③ 临键锁（Next-Key Locks）

有时候锁住某条记录,又想阻止其他事务在该记录前边的间隙插入记录,所以innodb就提出一种叫

Next-Key Locks 的锁，LOCK_ORDINARY,我们也可以简称为,next-key锁。Next-Key Locks 是存储引擎innodb,事务级别在可重复读情况下使用数据库,

innodb默认锁

begin;
select * from student where id <=8 and id > 3 for update;

插入意向锁（Insert Intention Locks）

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁（ next-key锁
也包含 gap锁），如果有的话，插入操作需要等待，直到拥有 gap锁的那个事务提交。但是InnoDB规
定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是
现在在等待。InnoDB就把这种类型的锁命名为 Insert Intention Locks ，官方的类型名称为：
LOCK_INSERT_INTENTION ，我们称为插入意向锁。插入意向锁是一种 Gap锁，不是意向锁，在insert
操作时产生。

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。

事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁

页锁

页锁就是在页的粒度上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录,当我们使用页锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我
们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销
介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量
超过了这个层级的阈值时，就会进行锁升级。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如
InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

从对待锁的态度划分:乐观锁、悲观锁

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

悲观锁（Pessimistic Locking）

悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身
的锁机制来实现，从而保证数据操作的排它性

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

乐观锁

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新
的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过
程序来实现。在程序上，我们可以采用版本号机制或者 CAS机制实现。乐观锁适用于多读的应用类型，
这样可以提高吞吐量。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁
begin;
select * from student where id <=8 and id > 3 for update;的一种实现方式：CAS实现的。

乐观锁的版本号机制
在表中设计一个版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更
新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。此时
如果已经有事务对这条数据进行了更改，修改就不会成功。
乐观锁的时间戳机制
时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行
比较，如果两者一致则更新成功，否则就是版本冲突。
你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或
者时间戳），从而证明当前拿到的数据是否最新。

两种锁的适用场景

从这两种锁的设计思想中，我们总结一下乐观锁和悲观锁的适用场景：

乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁
问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层
面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

按加锁的方式划分：显式锁、隐式锁

隐式锁

情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的 事务
id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的的就是
当前事务的 事务id ，如果其他事务此时想对该记录添加 S锁 或者 X锁 时，首先会看一下该记录的
trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X
锁 （也就是为当前事务创建一个锁结构， is_waiting 属性是 false ），然后自己进入等待状态
（也就是为自己也创建一个锁结构， is_waiting 属性是 true ）。
情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page
Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如
果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已
经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记
录，然后再重复 情景一 的做法。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert INTO student VALUES(34,"周八","二班");
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student lock in share mode; #执行完，当前事务被阻塞

mysql> SELECT * FROM performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 140562531358232:7:4:9:140562535668584
REQUESTING_ENGINE_TRANSACTION_ID: 422037508068888
REQUESTING_THREAD_ID: 64
REQUESTING_EVENT_ID: 6
REQUESTING_OBJECT_INSTANCE_BEGIN: 140562535668584
BLOCKING_ENGINE_LOCK_ID: 140562531351768:7:4:9:140562535619104
BLOCKING_ENGINE_TRANSACTION_ID: 15902
BLOCKING_THREAD_ID: 64
BLOCKING_EVENT_ID: 6
BLOCKING_OBJECT_INSTANCE_BEGIN: 140562535619104
1 row in set (0.00 sec)

隐式锁的逻辑过程如下：
A. InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。
B. 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活
动的事务，首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)。
C. 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。
D. 等待加锁成功，被唤醒，或者超时。
E. 写数据，并将自己的trx_id写入trx_id字段。

显式锁

显示加共享锁：

select .... lock in share mode

显示加排它锁：

select .... for update

其它锁之：全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后
其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结
构等）和更新类事务的提交语句。全局锁的典型使用场景是：做全库逻辑备份。

其它锁之：死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。死
锁示例：

这时候，事务1在等待事务2释放id=2的行锁，而事务2在等待事务1释放id=1的行锁。事务1和事务2在互
相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数
innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级
排他锁的事务进行回滚），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为
on ，表示开启这个逻辑。

第二种策略的成本分析
方法1：如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有
一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后
通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是
业务有损的。
方法2：控制并发度。如果并发能够控制住，比如同一行同时最多只有10个线程在更新，那么死锁检测
的成本很低，就不会出现这个问题。
这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；甚至有能力修改MySQL
源码的人，也可以做在MySQL里面。基本思路就是，对于相同行的更新，在进入引擎之前排队，这样在
InnoDB内部就不会有大量的死锁检测工作了。

锁的内存结构

结构解析

锁所在的事务信息

不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个
事务的信息。
此锁所在的事务信息在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比
方说事务id等。
索引信息：
对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。
表锁／行锁信息：
表锁结构和行锁结构在这个位置的内容是不同的：
1表锁：
记载着是对哪个表加的锁，还有其他的一些信息。
行锁：
记载了三个重要的信息

Space ID ：记录所在表空间。
Page Number ：记录所在页号。
n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同
的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个
n_bits 属性代表使用了多少比特位。
n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后
也不至于重新分配锁结构

type_mode ：

这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：

锁的模式（ lock_mode ），占用低4位，可选的值如下：
    LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁 。
    LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁 。
    LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁 。
    LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁 。
    LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。

在InnoDB存储引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC都算是表级锁的模式，LOCK_S和
LOCK_X既可以算是表级锁的模式，也可以是行级锁的模式。

锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用：
    LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁。
    LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁。

行锁的具体类型（ rec_lock_type ），使用其余的位来表示。只有在 lock_type 的值为
    LOCK_REC 时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：
        LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁 。
        LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁 。
        LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示正经 记录
        锁 。
        LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入
        意向锁。其他的类型：还有一些不常用的类型我们就不多说了。

is_waiting 属性呢？基于内存空间的节省，所以把 is_waiting 属性放到了 type_mode 这个32
位的数字中：
    LOCK_WAIT （十进制的 256 ） ：当第9个比特位置为 1 时，表示 is_waiting 为 true ，也
    就是当前事务尚未获取到锁，处在等待状态；当这个比特位为 0 时，表示 is_waiting 为
    false ，也就是当前事务获取锁成功。

其他信息：
为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。
一堆比特位：
如果是行锁结构的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性
表示的。InnoDB数据页中的每条记录在记录头信息中都包含一个 heap_no 属性，伪记录 Infimum 的
heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，之后每插入一条记录， heap_no 值就增1。锁结
构最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no ，即一个比特位映射
到页内的一条记录。

锁监控

关于MySQL锁的监控，我们一般可以通过检查 InnoDB_row_lock 等状态变量来分析系统上的行锁的争
夺情况

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.01 sec)

对各个状态量的说明如下：
    Innodb_row_lock_current_waits：当前正在等待锁定的数量；
    Innodb_row_lock_time ：从系统启动到现在锁定总时间长度；（等待总时长）
    Innodb_row_lock_time_avg ：每次等待所花平均时间；（等待平均时长）
    Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
    Innodb_row_lock_waits ：系统启动后到现在总共等待的次数；（等待总次数）
对于这5个状态变量，比较重要的3个见上面（橙色）。

其他监控方法：

MySQL把事务和锁的信息记录在了 information_schema 库中，涉及到的三张表分别是
INNODB_TRX 、 INNODB_LOCKS 和 INNODB_LOCK_WAITS 。
MySQL5.7及之前，可以通过information_schema.INNODB_LOCKS查看事务的锁情况，但只能看到阻塞事
务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。
MySQL8.0删除了information_schema.INNODB_LOCKS，添加了 performance_schema.data_locks ，可
以通过performance_schema.data_locks查看事务的锁情况，和MySQL5.7及之前不同，
performance_schema.data_locks不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁。
同时，information_schema.INNODB_LOCK_WAITS也被 performance_schema.data_lock_waits 所代
替。
我们模拟一个锁等待的场景，以下是从这三张表收集的信息
锁等待场景，我们依然使用记录锁中的案例，当事务2进行等待时，查询情况如下：

（1）查询正在被锁阻塞的sql语句。

SELECT * FROM information_schema.INNODB_TRX\G;

重要属性代表含义已在上述中标注。

（2）查询锁等待情况

SELECT * FROM data_lock_waits\G;
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 139750145405624:7:4:7:139747028690608
REQUESTING_ENGINE_TRANSACTION_ID: 13845 #被阻塞的事务ID
REQUESTING_THREAD_ID: 72
REQUESTING_EVENT_ID: 26
REQUESTING_OBJECT_INSTANCE_BEGIN: 139747028690608
BLOCKING_ENGINE_LOCK_ID: 139750145406432:7:4:7:139747028813248
BLOCKING_ENGINE_TRANSACTION_ID: 13844 #正在执行的事务ID，阻塞了13845
BLOCKING_THREAD_ID: 71
BLOCKING_EVENT_ID: 24
BLOCKING_OBJECT_INSTANCE_BEGIN: 139747028813248
1 row in set (0.00 sec)

（3）查询锁的情况

mysql > SELECT * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145405624:1068:139747028693520
ENGINE_TRANSACTION_ID: 13847
THREAD_ID: 72
EVENT_ID: 31
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139747028693520
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145405624:7:4:7:139747028690608
ENGINE_TRANSACTION_ID: 13847
THREAD_ID: 72
EVENT_ID: 31
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139747028690608
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: WAITING
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145406432:1068:139747028816304
ENGINE_TRANSACTION_ID: 13846
THREAD_ID: 71
EVENT_ID: 28
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139747028816304
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139750145406432:7:4:7:139747028813248
ENGINE_TRANSACTION_ID: 13846
THREAD_ID: 71
EVENT_ID: 28
OBJECT_SCHEMA: atguigu
OBJECT_NAME: user
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139747028813248
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
4 rows in set (0.00 sec)
ERROR:
No query specified

从锁的情况可以看出来，两个事务分别获取了IX锁，我们从意向锁章节可以知道，IX锁互相时兼容的。所
以这里不会等待，但是事务1同样持有X锁，此时事务2也要去同一行记录获取X锁，他们之间不兼容，导
致等待的情况发生。

间隙锁加锁规则（共11个案例）

间隙锁是在可重复读隔离级别下才会生效的： next-key lock 实际上是由间隙锁加行锁实现的，如果切换
到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁
的部分。而在读提交隔离级别下间隙锁就没有了，为了解决可能出现的数据和日志不一致问题，需要把
binlog 格式设置为 row 。也就是说，许多公司的配置为：读提交隔离级别加 binlog_format=row。业务不
需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理
的。
next-key lock的加锁规则
总结的加锁规则里面，包含了两个 “ “ 原则 ” ” 、两个 “ “ 优化 ” ” 和一个 “bug” 。

原则 1 ：加锁的基本单位是 next-key lock 。 next-key lock 是前开后闭区间。
原则 2 ：查找过程中访问到的对象才会加锁。任何辅助索引上的锁，或者非索引列上的锁，最终
都要回溯到主键上，在主键上也要加一把锁。
优化 1 ：索引上的等值查询，给唯一索引加锁的时候， next-key lock 退化为行锁。也就是说如果
InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁
优化 2 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的
时候， next-keylock 退化为间隙锁。
一个 bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
我们以表test作为例子，建表语句和初始化语句如下：其中id为主键索引

CREATE TABLE `test` (
`id` int(11) NOT NULL,
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：唯一索引等值查询间隙锁

由于表 test 中没有 id=7 的记录
根据原则 1 ，加锁单位是 next-key lock ， session A 加锁范围就是 (5,10] ；同时根据优化 2 ，这是一个等
值查询 (id=7) ，而 id=10 不满足查询条件， next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)

案例二：非唯一索引等值查询锁

这里 session A 要给索引 col1 上 col1=5 的这一行加上读锁。

根据原则 1 ，加锁单位是 next-key lock ，左开右闭，5是闭上的，因此会给 (0,5] 加上 next-key lock
。
要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的（可能有col1=5的其他记
录），需要向右遍历，查到c=10 才放弃。根据原则 2 ，访问到的都要加锁，因此要给 (5,10] 加
next-key lock 。
但是同时这个符合优化 2 ：等值判断，向右遍历，最后一个值不满足 col1=5 这个等值条件，因此
退化成间隙锁 (5,10) 。
根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所
以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。
但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住这个例子说明，锁是加在
索引上的。
执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行
锁。
如果你要用 lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，因为
覆盖索引不会访问主键索引，不会给主键索引上加锁

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住这个例子说明，锁是加在
索引上的。
执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行
锁。
如果你要用 lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，因为
覆盖索引不会访问主键索引，不会给主键索引上加锁

案例三：主键索引范围查询锁

上面两个例子是等值查询的，这个例子是关于范围查询的，也就是说下面的语句

select * from test where id=10 for update
select * from tets where id>=10 and id<11 for update;

开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10] 。根据优化 1 ，主键
id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
它是范围查询，范围查找就往后继续找，找到 id=15 这一行停下来，不满足条件，因此需要加
next-key lock(10,15] 。

session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15] 。首次 session A 定位查找
id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

案例四：非唯一索引范围查询锁

与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c ，它是普通索引
这两条查语句肯定是等价的，但是它们的加锁规则不太一样

在第一次用 col1=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 col1 是非唯
一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和
(10,15] 这两个 next-keylock 。
这里需要扫描到 col1=15 才停止扫描，是合理的，因为 InnoDB 要扫到 col1=15 ，才知道不需要继续往后
找了。

案例五：唯一索引范围查询锁 bug

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock ，并且因
为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上， InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20 。而且由于这是个范围扫
描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。照理说，这里锁住 id=20 这一行的行为，其
实是没有必要的。因为扫描到 id=15 ，就可以确定不用往后再找了。

案例六：非唯一索引上存在 " " 等值 " " 的例子

这里，我给表 t 插入一条新记录：insert into t values(30,10,30);也就是说，现在表里面有两个c=10的行
但是它们的主键值 id 是不同的（分别是 10 和 30 ），因此这两个c=10 的记录之间，也是有间隙的。

这次我们用 delete 语句来验证。注意， delete 语句加锁的逻辑，其实跟 select ... for update 是类似的，
也就是我在文章开始总结的两个 “ 原则 ” 、两个 “ 优化 ” 和一个 “bug” 。
这时， session A 在遍历的时候，先访问第一个 col1=10 的记录。同样地，根据原则 1 ，这里加的是
(col1=5,id=5) 到 (col1=10,id=10) 这个 next-key lock 。
由于c是普通索引，所以继续向右查找，直到碰到 (col1=15,id=15) 这一行循环才结束。根据优化 2 ，这是
一个等值查询，向右查找到了不满足条件的行，所以会退化成 (col1=10,id=10) 到 (col1=15,id=15) 的间隙
锁。

这个 delete 语句在索引 c 上的加锁范围，就是上面图中蓝色区域覆盖的部分。这个蓝色区域左右两边都
是虚线，表示开区间，即 (col1=5,id=5) 和 (col1=15,id=15) 这两行上都没有锁

案例七： limit 语句加锁

session A 的 delete 语句加了 limit 2 。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2 ，删
除的效果都是一样的。但是加锁效果却不一样

这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (col1=10, id=30) 这一行之后，
满足条件的语句已经有两条，循环就结束了。因此，索引 col1 上的加锁范围就变成了从（ col1=5,id=5)
到（ col1=10,id=30) 这个前开后闭区间，如下图所示：

这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit 。
这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

session A 启动事务后执行查询语句加 lock in share mode ，在索引 col1 上加了 next-keylock(5,10] 和
间隙锁 (10,15) （索引向右遍历退化为间隙锁）；
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；实际上分成了两步，
先是加 (5,10) 的间隙锁，加锁成功；然后加 col1=10 的行锁，因为sessionA上已经给这行加上了读
锁，此时申请死锁时会被阻塞
然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁， InnoDB 让
session B 回滚

案例九：order by索引排序的间隙锁1

如下面一条语句

begin;
select * from test where id>9 and id<12 order by id desc for update;

首先这个查询语句的语义是 order by id desc ，要拿到满足条件的所有行，优化器必须先找到 “ 第
一个 id<12 的值 ” 。
这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终
没找到，但找到了 (10,15) 这个间隙。（ id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10,15) 。）
然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，又因为区间是左开右
闭的，所以会加一个next-key lock (0,5] 。也就是说，在执行过程中，通过树搜索的方式定位记录
的时候，用的是 “ 等值查询 ” 的方法。

案例十：order by索引排序的间隙锁2

由于是 order by col1 desc ，第一个要定位的是索引 col1 上 “ 最右边的 ”col1=20 的行。这是一个非
唯一索引的等值查询：
左开右闭区间，首先加上 next-key lock (15,20] 。向右遍历，col1=25不满足条件，退化为间隙锁所以会
加上间隙锁(20,25) 和 next-key lock (15,20] 。
在索引 col1 上向左遍历，要扫描到 col1=10 才停下来。同时又因为左开右闭区间，所以 next-key
lock 会加到 (5,10] ，这正是阻塞session B 的 insert 语句的原因。
在扫描过程中， col1=20 、 col1=15 、 col1=10 这三行都存在值，由于是 select * ，所以会在主键
id 上加三个行锁。因此， session A 的 select 语句锁的范围就是：
索引 col1 上 (5, 25) ；
主键索引上 id=15 、 20 两个行锁。

案例十一：update修改数据的例子-先插入后删除

注意：根据 col1>5 查到的第一个记录是 col1=10 ，因此不会加 (0,5] 这个 next-key lock 。
session A 的加锁范围是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。
之后 session B 的第一个 update 语句，要把 col1=5 改成 col1=1 ，你可以理解为两步：

插入 (col1=1, id=5) 这个记录；
删除 (col1=5, id=5) 这个记录。
通过这个操作， session A 的加锁范围变成了图 7 所示的样子：

好，接下来 session B 要执行 update t set col1 = 5 where col1 = 1 这个语句了，一样地可以拆成两步：

插入 (col1=5, id=5) 这个记录；
删除 (col1=1, id=5) 这个记录。第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住
了。

第16章_多版本并发控制

什么是MVCC

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

快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到
即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读 , 而非当前读。当前
读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞
读；比如这样：

SELECT * FROM player WHERE ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，
避免了加锁操作，降低了开销。
既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务
不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前
读。比如：

SELECT * FROM student LOCK IN SHARE MODE; # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ... # 排他锁
DELETE FROM student WHERE ... # 排他锁
UPDATE student SET ... # 排他锁

复习

再谈隔离级别

我们知道事务有 4 个隔离级别，可能存在三种并发问题：

隐藏字段、Undo Log版本链

回顾一下undo日志的版本链，对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必
要的隐藏列。
trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id 赋值给
trx_id 隐藏列。
roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志中，然
后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，它占用的Undo
Log Segment也会被系统回收（也就是该undo日志占用的Undo页面链表要么被重用，要么被释
放）。

假设之后两个事务id分别为 10 、 20 的事务对这条记录进行 UPDATE 操作，操作流程如下：

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个 roll_pointer 属性
（ INSERT 操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些 undo日志
都连起来，串成一个链表：

对该记录每次更新后，都会将旧值放到一条 undo日志中，就算是该记录的一个旧版本，随着更新次数
的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为版本链，版
本链的头节点就是当前记录最新的值。
每个版本中还包含生成该版本时对应的事务id 。

MVCC实现原理之ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

什么是ReadView

readview 就是事务A在使用MVCC 进行快照读操作时产生的读视图,当事务启动时,会生成数据系统当前的一个快照InnoD为这个事务构造了一个数组,用来记录并维护系统活跃事务ID(活跃指的是,启动了但是还没提交)

设计思路

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录
的最新版本就好了。
使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。
使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到已经提交了的事务修改
过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问
题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。
这个ReadView中主要包含4个比较重要的内容，分别如下：

creator_trx_id ，创建这个 Read View 的事务 ID。

说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为
事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
up_limit_id ，活跃的事务中最小的事务 ID。
low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系
统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1，
2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，
trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。

ReadView的规则

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。

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

MVCC整体操作流程

了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过MVCC找到它：

首先获取事务自己的版本号，也就是事务 ID；
获取 ReadView；
查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
最后返回符合规则的数据。
在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次
Read View。

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生
不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会
获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

举例说明

5.1 READ COMMITTED隔离级别下
READ COMMITTED ：每次读取数据前都生成一个ReadView。
现在有两个事务id 分别为 10 、 20 的事务在执行：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
# Transaction 20
BEGIN;
# 更新了一些别的表的记录

此刻，表student 中 id 为 1 的记录得到的版本链表如下所示：

假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

之后，我们把事务id 为 10 的事务提交一下：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;

然后再到事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录：

# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;

此刻，表student中 id 为 1 的记录的版本链就长这样

然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'
# SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'王五'

REPEATABLE READ隔离级别下

使用 REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个 ReadView ，之
后的查询就不会重复生成了。
比如，系统里有两个事务id 分别为 10 、 20 的事务在执行：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
# Transaction 20
BEGIN;
# 更新了一些别的表的记录

此刻，表student 中 id 为 1 的记录得到的版本链表如下所示：

假设现在有一个使用 REPEATABLE READ 隔离级别的事务开始执行：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

之后，我们把事务id 为 10 的事务提交一下，就像这样：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;

然后再到事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录

# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;

此刻，表student 中 id 为 1 的记录的版本链长这样：

然后再到刚才使用 REPEATABLE READ 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'
# SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值仍为'张三'

如何解决幻读

接下来说明InnoDB 是如何解决幻读的。
假设现在表 student 中只有一条数据，数据内容中，主键 id=1，隐藏的 trx_id=10，它的 undo log 如下图
所示。

假设现在有事务 A 和事务 B 并发执行，事务 A 的事务 id 为 20 ，事务 B 的事务 id 为 30 。
步骤1：事务 A 开始第一次查询数据，查询的 SQL 语句如下。

select * from student where id >= 1;

在开始查询之前，MySQL 会为事务 A 产生一个 ReadView，此时 ReadView 的内容如下： trx_ids=
[20,30] ， up_limit_id=20 ， low_limit_id=31 ， creator_trx_id=20 。

由于此时表 student 中只有一条数据，且符合 where id>=1 条件，因此会查询出来。然后根据 ReadView
机制，发现该行数据的trx_id=10，小于事务 A 的 ReadView 里 up_limit_id，这表示这条数据是事务 A 开
启之前，其他事务就已经提交了的数据，因此事务 A 可以读取到。
结论：事务 A 的第一次查询，能读取到一条数据，id=1。
步骤2：接着事务 B(trx_id=30)，往表 student 中新插入两条数据，并提交事务。

insert into student(id,name) values(2,'李四');
insert into student(id,name) values(3,'王五');

此时表student 中就有三条数据了，对应的 undo 如下图所示：

步骤3：接着事务 A 开启第二次查询，根据可重复读隔离级别的规则，此时事务 A 并不会再重新生成
ReadView。此时表 student 中的 3 条数据都满足 where id>=1 的条件，因此会先查出来。然后根据
ReadView 机制，判断每条数据是不是都可以被事务 A 看到。
1）首先 id=1 的这条数据，前面已经说过了，可以被事务 A 看到。
2）然后是 id=2 的数据，它的 trx_id=30，此时事务 A 发现，这个值处于 up_limit_id 和 low_limit_id 之
间，因此还需要再判断 30 是否处于 trx_ids 数组内。由于事务 A 的 trx_ids=[20,30]，因此在数组内，这表
示 id=2 的这条数据是与事务 A 在同一时刻启动的其他事务提交的，所以这条数据不能让事务 A 看到。
3）同理，id=3 的这条数据，trx_id 也为 30，因此也不能被事务 A 看见。

结论：最终事务 A 的第二次查询，只能查询出 id=1 的这条数据。这和事务 A 的第一次查询的结果是一样
的，因此没有出现幻读现象，所以说在 MySQL 的可重复读隔离级别下，不存在幻读问题。

总结

这里介绍了 MVCC 在 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行快照读操作时
访问记录的版本链的过程。这样使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
核心点在于 ReadView 的原理， READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同
就是生成ReadView的时机不同：
READ COMMITTD 在每一次进行普通SELECT操作前都会生成一个ReadView
REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复
使用这个ReadView就好了。

第17章_其他数据库日志

千万不要小看日志。很多看似奇怪的问题，答案往往就藏在日志里。很多情况下，只有通过查看日志才
能发现问题的原因，真正解决问题。所以，一定要学会查看日志，养成检查日志的习惯，对提升你的数
据库应用开发能力至关重要。

MySQL支持的日志

日志类型

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志
和慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志：中继日志和数据定义语句日志。使
用这些日志文件，可以查看MySQL内部发生的事情。

这6类日志分别是

慢查询日志:记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

通用查询日志:记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，
对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。

错误日志:记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的
状态，从而对服务器进行维护。

二进制日志:记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故
障时数据的无损失恢复

中继日志:用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。
从服务器通过读取中继日志的内容，来同步主服务器上的操作。

数据定义语句日志:记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于 MySQL数据目录中。

日志的弊端

日志功能会降低MySQL数据库的性能。
日志会占用大量的磁盘空间。

通用查询日志(general query log)

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止
时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，
还原操作时的具体场景，可以帮助我们准确定位问题。

查看当前状态

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | OFF | #通用查询日志处于关闭状态
| general_log_file | /var/lib/mysql/atguigu01.log | #通用查询日志文件的名称是atguigu01.log
+------------------+------------------------------+
2 rows in set (0.03 sec)

启动日志

方式1：永久性方式

修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务。格式如下：

[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名

如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，
hostname表示主机名。

方式2：临时性方式

SET GLOBAL general_log=on; # 开启通用查询日志
SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置

对应的，关闭操作SQL命令如下：

SET GLOBAL general_log=off; # 关闭通用查询日志

查看设置后情况：

SHOW VARIABLES LIKE 'general_log%';

查看日志

通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件。每台
MySQL服务器的通用查询日志内容是不同的。
在Windows操作系统中，使用文本文件查看器；
在Linux系统中，可以使用vi工具或者gedit工具查看；
在Mac OSX系统中，可以使用文本文件查看器或者vi等工具查看。
从 SHOW VARIABLES LIKE 'general_log%'; 结果中可以看到通用查询日志的位置。

/usr/sbin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2022-01-04T07:44:58.052890Z 10 Query SHOW VARIABLES LIKE '%general%'
2022-01-04T07:45:15.666672Z 10 Query SHOW VARIABLES LIKE 'general_log%'
2022-01-04T07:45:28.970765Z 10 Query select * from student
2022-01-04T07:47:38.706804Z 11 Connect root@localhost on using Socket
2022-01-04T07:47:38.707435Z 11 Query select @@version_comment limit 1
2022-01-04T07:48:21.384886Z 12 Connect [email protected] on using TCP/IP
2022-01-04T07:48:21.385253Z 12 Query SET NAMES utf8
2022-01-04T07:48:21.385640Z 12 Query USE `atguigu12`
2022-01-04T07:48:21.386179Z 12 Query SHOW FULL TABLES WHERE Table_Type !=
'VIEW'
2022-01-04T07:48:23.901778Z 13 Connect [email protected] on using TCP/IP
2022-01-04T07:48:23.902128Z 13 Query SET NAMES utf8
2022-01-04T07:48:23.905179Z 13 Query USE `atguigu`
2022-01-04T07:48:23.905825Z 13 Query SHOW FULL TABLES WHERE Table_Type !=
'VIEW'
2022-01-04T07:48:32.163833Z 14 Connect [email protected] on using TCP/IP
2022-01-04T07:48:32.164451Z 14 Query SET NAMES utf8
2022-01-04T07:48:32.164840Z 14 Query USE `atguigu`
2022-01-04T07:48:40.006687Z 14 Query select * from account

在通用查询日志里面，我们可以清楚地看到，什么时候开启了新的客户端登陆数据库，登录之后做了什
么 SQL 操作，针对的是哪个数据表等信息。

停止日志
方式1：永久性方式

修改 my.cnf 或者 my.ini 文件，把[mysqld]组下的 general_log 值设置为 OFF 或者把general_log一项
注释掉。修改保存后，再重启MySQL服务，即可生效。举例1：

[mysqld]
general_log=OFF

[mysqld]
#general_log=ON

方式2：临时性方式

使用SET语句停止MySQL通用查询日志功能：

SET GLOBAL general_log=off;

查询通用日志功能：

SHOW VARIABLES LIKE 'general_log%';

删除\刷新日志

如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很
长时间之前的查询日志，以保证MySQL服务器上的硬盘空间

手动删除文件

SHOW VARIABLES LIKE 'general_log%';

可以看出，通用查询日志的目录默认为MySQL数据目录。在该目录下手动删除通用查询日志
atguigu01.log。
使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文
件。前提一定要开启通用日志

mysqladmin -uroot -p flush-logs

错误日志

启动日志

在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。
默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或
hostname.err （mac系统）。如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置

[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

修改配置项后，需要重启MySQL服务以生效。

查看日志

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。
查询错误日志的存储路径：
mysql> SHOW VARIABLES LIKE 'log_err%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| log_error | /var/log/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
4 rows in set (0.01 sec)

执行结果中可以看到错误日志文件是mysqld.log，位于MySQL默认的数据目录下。

删除\刷新日志

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，
以保证MySQL服务器上的硬盘空间。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以
直接删除。

[root@atguigu01 log]# mysqladmin -uroot -p flush-logs
Enter password:
mysqladmin: refresh failed; error: 'Could not open file '/var/log/mysqld.log' for
error logging.

install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

二进制日志

binlog可以说是MySQL中比较 重要 的日志了，在日常开发及运维过程中，经常会遇到。
binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的
DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、
show等）。
binlog主要应用场景：
一是用于 数据恢复
二是用于 数据复制

查看默认情况

查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)

日志参数设置

方式1：永久性方式

修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数：

[mysqld]
#启用二进制日志
log-bin=atguigu-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

重新启动MySQL服务，查询二进制日志的信息，执行结果：

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/atguigu-bin |
| log_bin_index | /var/lib/mysql/atguigu-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)

设置带文件夹的bin-log日志存放目录

如果想改变日志文件的目录和名称，可以对my.cnf或my.ini中的log_bin参数修改如下：

[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"

注意：新建的文件夹需要使用mysql用户，使用下面的命令即可。

chown -R -v mysql:mysql binlog

方式2：临时性方式

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是
在mysql8中只有会话级别的设置，没有了global级别的设置。

# global 级别
mysql> set global sql_log_bin=0;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used
with SET GLOBAL
# session级别
mysql> SET sql_log_bin=0;
Query OK, 0 rows affected (0.01 秒)

查看日志

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一
个以“filename”为名称、以“.000001”为后缀的文件。
MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的
个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就
会创建一个新的日志文件。
查看当前的二进制日志文件列表及大小。指令如下：


mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156 | No |
+--------------------+-----------+-----------+
1 行于数据集 (0.02 秒)

下面命令将行事件以伪SQL的形式表现出来

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"
#220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10
exec_time=0 error_code=0
SET TIMESTAMP=1641345397/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio
n_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 324
#220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map:
`atguigu14`.`student` mapped to number 85
# at 391
#220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id
85 flags: STMT_END_F
BINLOG '
dfHUYRMBAAAAQwAAAIcBAAAAAFUAAAAAAAEACWF0Z3VpZ3UxNAAHc3R1ZGVudAADAw8PBDwAHgAG

AQEAAgEhkJj4dA==
dfHUYR8BAAAATwAAANYBAAAAAFUAAAAAAAEAAgAD//8AAQAAAAblvKDkuIkG5LiA54+tAAEAAAAL
5byg5LiJX2JhY2sG5LiA54+tkQSSyQ==
'/*!*/;
### UPDATE `atguigu`.`student`
### WHERE
### @1=1
### @2='张三'
### @3='一班'
### SET
### @1=1
### @2='张三_back'
### @3='一班'
# at 470
#220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15
COMMIT/*!*/;

前面的命令同时显示binlog格式的语句，使用如下命令不显示它

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"
#220105 9:16:37 server id 1 end_log_pos 324 CRC32 0x6b31978b Query thread_id=10
exec_time=0 error_code=0
SET TIMESTAMP=1641345397/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb3 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio
n_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 324
#220105 9:16:37 server id 1 end_log_pos 391 CRC32 0x74f89890 Table_map:
`atguigu14`.`student` mapped to number 85
# at 391
#220105 9:16:37 server id 1 end_log_pos 470 CRC32 0xc9920491 Update_rows: table id
85 flags: STMT_END_F
### UPDATE `atguigu14`.`student`
### WHERE
### @1=1
### @2='张三'
### @3='一班'
### SET
### @1=1
### @2='张三_back'
### @3='一班'
# at 470
#220105 9:16:37 server id 1 end_log_pos 501 CRC32 0xca01d30f Xid = 15

关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简
单分享几个常用的语句，更多操作可以参考官方文档。

# 可查看参数帮助
mysqlbinlog --no-defaults --help
# 查看最后100行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail
-100
# 根据position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A
20 '4939002'

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方
便的查询命令：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）　
FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
LIMIT [offset] ：偏移量(不指定就是0)
row_count :查询总条数（不指定就是所有行）

mysql> show binlog events in 'atguigu-bin.000002';
+--------------------+-----+----------------+-----------+-------------+---------------
--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+--------------------+-----+----------------+-----------+-------------+---------------
--------------------------------------------------------------+
| atguigu-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver:
8.0.26, Binlog ver: 4 |
| atguigu-bin.000002 | 125 | Previous_gtids | 1 | 156 |
|
| atguigu-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| atguigu-bin.000002 | 235 | Query | 1 | 324 | BEGIN
|
| atguigu-bin.000002 | 324 | Table_map | 1 | 391 | table_id: 85
(atguigu14.student) |
| atguigu-bin.000002 | 391 | Update_rows | 1 | 470 | table_id: 85
flags: STMT_END_F |
| atguigu-bin.000002 | 470 | Xid | 1 | 501 | COMMIT /*
xid=15 */ |
| atguigu-bin.000002 | 501 | Anonymous_Gtid | 1 | 578 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| atguigu-bin.000002 | 578 | Query | 1 | 721 | use
`atguigu14`; create table test(id int, title varchar(100)) /* xid=19 */ |
| atguigu-bin.000002 | 721 | Anonymous_Gtid | 1 | 800 | SET
@@SESSION.GTID_NEXT= 'ANONYMOUS' |
| atguigu-bin.000002 | 800 | Query | 1 | 880 | BEGIN
|
| atguigu-bin.000002 | 880 | Table_map | 1 | 943 | table_id: 89
(atguigu14.test) |
| atguigu-bin.000002 | 943 | Write_rows | 1 | 992 | table_id: 89
flags: STMT_END_F |
| atguigu-bin.000002 | 992 | Xid | 1 | 1023 | COMMIT /*
xid=21 */ |

上面我们讲了这么多都是基于binlog的默认格式，binlog格式查看

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 行于数据集 (0.02 秒)

除此之外，binlog还有2种格式，分别是Statement和Mixed

Statement
每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

Row
5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修
改。
优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下
的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

Mixed
从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
详细情况，下章讲解。

使用日志恢复数据

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

这个命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容
恢复到数据库中。
filename ：是日志文件名。
option ：可选项，比较重要的两对option参数是--start-date、--stop-date 和 --start-position、--
stop-position。
--start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
--start-position和--stop-position ：可以指定恢复数据的开始位置和结束位置。
注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必
须在atguigu-bin.000002之前恢复。

删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。
PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文
件。具体如下

PURGE MASTER LOGS：删除指定日志文件
PURGE MASTER LOGS语法如下：

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

其它场景

二进制日志可以通过数据库的 全量备份 和二进制日志中保存的 增量信息 ，完成数据库的 无损失恢复 。
但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数
据恢复，是很有挑战性的，因为起止位置不容易管理。
在这种情况下，一个有效的解决办法是 配置主从数据库服务器 ，甚至是 一主多从 的架构，把二进制日志
文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常
等问题。

再谈二进制日志(binlog)

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再
把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一
次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。为0的时候，表示每次提交事务都只
write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的
binglog 会丢失。如下图：

为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。
最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕
机，会丢失最近N个事务的binlog日志。

binlog与redolog对比

redo log 它是 物理日志 ，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生
的。
而 binlog 是 逻辑日志 ，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于
MySQL Server 层。

两阶段提交

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程
中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。

redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

使用两阶段提交后，写入binlog时发生异常也不会有影响

另一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对
应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

中继日志

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读
取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫
中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主
从服务器的数据同步。
搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。
文件名的格式是：从服务器名 -relay-bin.序号。中继日志还有一个索引文件：从服务器名 -relaybin.index ，用来定位当前正在使用的中继日志。

查看中继日志

中继日志与二进制日志的格式相同，可以用 mysqlbinlog 工具进行查看。下面是中继日志的一个片
段：

SET TIMESTAMP=1618558728/*!*/;
BEGIN
/*!*/;
# at 950
#210416 15:38:48 server id 1 end_log_pos 832 CRC32 0xcc16d651 Table_map:
`atguigu`.`test` mapped to number 91
# at 1000
#210416 15:38:48 server id 1 end_log_pos 872 CRC32 0x07e4047c Delete_rows: table id
91 flags: STMT_END_F -- server id 1 是主服务器，意思是主服务器删了一行数据
BINLOG '
CD95YBMBAAAAMgAAAEADAAAAAFsAAAAAAAEABGRlbW8ABHRlc3QAAQMAAQEBAFHWFsw=
CD95YCABAAAAKAAAAGgDAAAAAFsAAAAAAAEAAgAB/wABAAAAfATkBw==
'/*!*/;
# at 1040

这一段的意思是，主服务器（“server id 1”）对表 atguigu.test 进行了 2 步操作：

定位到表 atguigu.test 编号是 91 的记录，日志位置是 832；
删除编号是 91 的记录，日志位置是 872。

恢复的典型错误

如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的服务器名称与之
前不同。而中继日志里是包含从服务器名的。在这种情况下，就可能导致你恢复从服务器的时候，无法
从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。
解决的方法也很简单，把从服务器的名称改回之前的名称。

第18章_主从复制

主从复制概述

此外，一般应用对数据库而言都是“ 读多写少 ”，也就说对数据库读取数据的压力比较大，有一个思路就
是采用数据库集群的方案，做主从架构、进行读写分离，这样同样可以提升数据库的并发处理能力。但
并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。
如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何优化SQL和索引，这种方式
简单有效；其次才是采用缓存的策略，比如使用 Redis将热点数据保存在内存数据库中，提升读取的效
率；最后才是对数据库采用主从架构，进行读写分离。

主从复制的作用

主从同步设计不仅可以提高数据库的吞吐量，还有以下 3 个方面的作用。

第1个作用：读写分离。

第2个作用就是数据备份。
第3个作用是具有高可用性。

主从复制的原理

Slave 会从 Master 读取 binlog 来进行数据同步。

原理剖析

三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操
作，一个主库线程，两个从库线程。

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进
制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释
放掉。
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的
二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（Relay log）。
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤
步骤1： Master 将写操作记录到二进制日志（ binlog ）。
步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（ relay log ）；
步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化
的，而且重启后从接入点开始复制。

复制的问题
复制的最大问题：延时

复制的基本原则
每个 Slave 只有一个 Master
每个 Slave 只能有一个唯一的服务器ID
每个 Master 可以有多个 Slave

一主一从架构搭建

一台主机用于处理所有写请求，一台从机负责所有读请求，架构图如下

准备工作

1、准备 2台 CentOS 虚拟机
2、每台虚拟机上需要安装好MySQL (可以是MySQL8.0 )
说明：前面我们讲过如何克隆一台CentOS。大家可以在一台CentOS上安装好MySQL，进而通过克隆的方
式复制出1台包含MySQL的虚拟机。
注意：克隆的方式需要修改新克隆出来主机的：① MAC地址 ② hostname ③ IP 地址 ④ UUID 。
此外，克隆的方式生成的虚拟机（包含MySQL Server），则克隆的虚拟机MySQL Server的UUID相同，必
须修改，否则在有些场景会报错。比如： show slave status\G ，报如下的错误：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have
equal MySQL server UUIDs; these UUIDs must be different for replication to work.

修改MySQL Server 的UUID方式：

vim /var/lib/mysql/auto.cnf
systemctl restart mysqld

主机配置文件

建议mysql版本一致且后台以服务运行，主从所有配置项都配置在 [mysqld] 节点下，且都是小写字母。
具体参数配置如下：

#[必须]主服务器唯一ID
server-id=1
#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin
log-bin=atguigu-bin

可选

#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选]设置不要复制的数据库
binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字
#[可选]设置binlog格式
binlog_format=STATEMENT

binlog格式设置：

格式1： STATEMENT模式（基于SQL语句的复制(statement-based replication, SBR)）

binlog_format=STATEMENT

每一条会修改数据的sql语句会记录到binlog中。这是默认的binlog格式。
SBR 的优点：
历史悠久，技术成熟
不需要记录每一行的变化，减少了binlog日志量，文件较小
binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
binlog可以用于实时的还原，而不仅仅用于复制
主从版本可以不一样，从服务器版本可以比主服务器版本高
SBR 的缺点：
不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候
使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE()
(除非启动时启用了 --sysdate-is-now 选项)
INSERT ... SELECT 会产生比 RBR 更多的行级锁
复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的
行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发
生变化的记录产生影响
执行复杂语句如果出错的话，会消耗更多资源
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错

② ROW模式（基于行的复制(row-based replication, RBR)）

binlog_format=ROW

5.1.5版本的MySQL才开始支持，不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什
么样了。
RBR 的优点：
任何情况都可以被复制，这对复制来说是最 安全可靠 的。（比如：不会出现某些特定情况下
的存储过程、function、trigger的调用和触发无法被正确复制的问题）
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：INSERT ... SELECT、包含 AUTO_INCREMENT 字段的 INSERT、
没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执行 INSERT，UPDATE，DELETE 语句时锁更少
从服务器上采用 多线程 来执行复制成为可能
RBR 的缺点：
binlog 大了很多
复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一
次，这会导致频繁发生 binlog 的并发写问题
无法从 binlog 中看到都复制了些什么语句

③ MIXED模式（混合模式复制(mixed-based replication, MBR)）

binlog_format=MIXED

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

在Mixed模式下，一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复
制的操作，则采用row格式保存binlog。
MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选
择一种。

3.3 从机配置文件

要求主从所有配置项都配置在 my.cnf 的 [mysqld] 栏位下，且都是小写字母。

必选

#[必须]从服务器唯一ID
server-id=2

可选

#[可选]启用中继日志
relay-log=mysql-relay

重启后台mysql服务，使配置生效。

注意：主从机都关闭防火墙
service iptables stop #CentOS 6
systemctl stop firewalld.service #CentOS 7

主机：建立账户并授权

#在主机MySQL里执行授权主从复制的命令
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123';
#5.5,5.7

注意：如果使用的是MySQL8，需要如下的方式建立账户，并授权slave：

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行。否则见下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

注意：在从机执行show slave status\G时报错：
Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1
message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires
secure connection.

查询Master的状态，并记录下File和Position的值

show master status;

记录下File和Position的值

注意：执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化。

从机：配置需要复制的主机

步骤1：从机上复制主机的命令

CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;

CHANGE MASTER TO
MASTER_HOST='192.168.1.150',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_F
ILE='atguigu-bin.000007',MASTER_LOG_POS=154;

步骤2：

#启动slave同步
START SLAVE;

如果报错了

可以执行如下操作，删除之前的relay_log信息。然后重新执行 CHANGE MASTER TO ...语句即可。

mysql> reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

接着，查看同步状态：

SHOW SLAVE STATUS\G;

显式如下的情况，就是不正确的。可能错误的原因有：

1. 网络不通
2. 账户密码错误
3. 防火墙
4. mysql配置文件问题
5. 连接服务器时语法
6. 主服务器mysql权限

测试

CREATE DATABASE atguigu_master_slave;
CREATE TABLE mytbl(id INT,NAME VARCHAR(16));
INSERT INTO mytbl VALUES(1, 'zhang3');
INSERT INTO mytbl VALUES(2,@@hostname);

停止主从同步

停止主从同步命令：

stop slave;

如何重新配置主从

如果停止从服务器复制功能，再使用需要重新配置主从。否则会报错如下：

重新配置主从，需要在从机上执行：

stop slave;
reset master; #删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)

搭建主从复制：双主双从

同步数据一致性问题

主从同步的要求：
读库和写库的数据一致(最终一致)；
写数据必须写到写库；
读数据必须到读库(不一定)；

理解主从延迟问题

进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟
（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据
不一致性问题。

主从延迟问题原因

在网络正常的时候，日志从主库传给从库所需的时间是很短的，即T2-T1的值是非常小的。即，网络正常
情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。

主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。造
成原因：

1、从库的机器性能比主库要差
2、从库的压力大
3、大事务的执行

举例1：一次性用delete语句删除太多数据
结论：后续再删除数据的时候，要控制每个事务删除的数据量，分成多次删除。
举例2：一次性用insert...select插入太多数据
举例:3：大表DDL
比如在主库对一张500W的表添加一个字段耗费了10分钟，那么从节点上也会耗费10分钟。

如何减少主从延迟

若想要减少主从延迟的时间，可以采取下面的办法：

1. 降低多线程大事务并发的概率，优化业务逻辑
2. 优化SQL，避免慢SQL， 减少批量操作 ，建议写脚本以update-sleep这样的形式完成。
3. 提高从库机器的配置 ，减少主库写binlog和从库读binlog的效率差。
4. 尽量采用 短的链路 ，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输
的网络延时。
5. 实时性要求的业务读强制走主库，从库只做灾备，备份。

如何解决一致性问题

如果操作的数据存储在同一个数据库中，那么对数据进行更新的时候，可以对记录加写锁，这样在读取
的时候就不会发生数据不一致的情况。但这时从库的作用就是备份，并没有起到读写分离，分担主库
读压力的作用。

读写分离情况下，解决主从同步中数据不一致的问题，就是解决主从之间数据复制方式的问题，如果按
照数据一致性从弱到强来进行划分，有以下 3 种复制方式。

方法 1：异步复制

方法 2：半同步复制

方法 3：组复制

异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决
定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的
场景，比如金融领域。MGR 很好地弥补了这两种复制模式的不足。
组复制技术，简称 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一种新的数据复
制技术，这种复制技术是基于 Paxos 协议的状态机复制。

MGR 是如何工作的
首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层
（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节
点）的同意，大多数指的是同意的节点数量需要大于（N/2+1），这样才可以进行提交，而不是原发起
方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 COMMIT 即可。
在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消
息和全局有序消息，从而保证组内数据的一致性

MGR 将 MySQL 带入了数据强一致性的时代，是一个划时代的创新，其中一个重要的原因就是MGR 是基
于 Paxos 协议的。Paxos 算法是由 2013 年的图灵奖获得者 Leslie Lamport 于 1990 年提出的，有关这个算
法的决策机制可以搜一下。事实上，Paxos 算法提出来之后就作为分布式一致性算法被广泛应用，比如
Apache 的 ZooKeeper 也是基于 Paxos 实现的。

知识延伸

在主从架构的配置中，如果想要采取读写分离的策略，我们可以自己编写程序，也可以通过第三方的中
间件来实现。
自己编写程序的好处就在于比较自主，我们可以自己判断哪些查询在从库上来执行，针对实时性要
求高的需求，我们还可以考虑哪些查询可以在主库上执行。同时，程序直接连接数据库，减少了中
间件层，相当于减少了性能损耗。
采用中间件的方法有很明显的优势，功能强大，使用简单。但因为在客户端和数据库之间增加了
中间件层会有一些性能损耗，同时商业中间件也是有使用成本的。我们也可以考虑采取一些优秀的
开源工具。

① Cobar 属于阿里B2B事业群，始于2008年，在阿里服役3年多，接管3000+个MySQL数据库的
schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职，Cobar停止维护。
② Mycat 是开源社区在阿里cobar基础上进行二次开发，解决了cobar存在的问题，并且加入了许
多新的功能在其中。青出于蓝而胜于蓝。③ OneProxy 基于MySQL官方的proxy思想利用c语言进行开发的，OneProxy是一款商业 收费 的中
间件。舍弃了一些功能，专注在 性能和稳定性上 。
④ kingshard 由小团队用go语言开发，还需要发展，需要不断完善。
⑤ Vitess 是Youtube生产在使用，架构很复杂。不支持MySQL原生协议，使用 需要大量改造成
本 。
⑥ Atlas 是360团队基于mysql proxy改写，功能还需完善，高并发下不稳定。
⑦ MaxScale 是mariadb（MySQL原作者维护的一个版本） 研发的中间件
⑧ MySQLRoute 是MySQL官方Oracle公司发布的中间件

主动切换
被动切换
如何判断主库出问题了？如何解决过程中的数据不一致性问题？

第19章_数据库备份与恢复

物理备份与逻辑备份

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比
较大，MySQL中可以用 xtrabackup 工具来进行物理备份。
逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空
间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是备份sql语句，在恢复的
时候执行备份的sql语句实现数据库数据的重现。

mysqldump实现逻辑备份

备份一个数据库

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名
称.sql

说明：备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的。

举例：使用root用户备份atguigu数据库：

mysqldump -uroot -p atguigu>atguigu.sql #备份文件存储在当前目录下
mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql

备份文件剖析：

-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: atguigu
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;2.2 

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `atguigu`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `atguigu` /*!40100 DEFAULT CHARACTER SET
utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `atguigu`;
--
-- Table structure for table `student`
--
DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `student` (
`studentno` int NOT NULL,
`name` varchar(20) DEFAULT NULL,
`class` varchar(20) DEFAULT NULL,
PRIMARY KEY (`studentno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `student` VALUES (1,'张三_back','一班'),(3,'李四','一班'),(8,'王五','二班'),
(15,'赵六','二班'),(20,'钱七','>三班'),(22,'zhang3_update','1ban'),(24,'wang5','2ban');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
.
.
.
.
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-01-07 9:58:23

备份全部数据库

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql

备份部分数据库

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定
databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名
称.sql

mysqldump -uroot -p --databases atguigu atguigu12 >two_database.sql

mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql

备份部分表

比如，在表变更前做个备份。语法如下：

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

举例：备份atguigu数据库下的book表

mysqldump -uroot -p atguigu book> book.sql

book.sql文件内容如下

mysqldump -uroot -p atguigu book> book.sql^C
[root@node1 ~]# ls
kk kubekey kubekey-v1.1.1-linux-amd64.tar.gz README.md test1.sql two_database.sql
[root@node1 ~]# mysqldump -uroot -p atguigu book> book.sql
Enter password:
[root@node1 ~]# ls
book.sql kk kubekey kubekey-v1.1.1-linux-amd64.tar.gz README.md test1.sql
two_database.sql
[root@node1 ~]# vi book.sql
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: atguigu
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `book`
DROP TABLE IF EXISTS `book`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book` (
`bookid` int unsigned NOT NULL AUTO_INCREMENT,
`card` int unsigned NOT NULL,
`test` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`bookid`),
KEY `Y` (`card`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `book`
--
LOCK TABLES `book` WRITE;
/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` VALUES (1,9,NULL),(2,10,NULL),(3,4,NULL),(4,8,NULL),(5,7,NULL),
(6,10,NULL),(7,11,NULL),(8,3,NULL),(9,1,NULL),(10,17,NULL),(11,19,NULL),(12,4,NULL),
(13,1,NULL),(14,14,NULL),(15,5,NULL),(16,5,NULL),(17,8,NULL),(18,3,NULL),(19,12,NULL),
(20,11,NULL),(21,9,NULL),(22,20,NULL),(23,13,NULL),(24,3,NULL),(25,18,NULL),
(26,20,NULL),(27,5,NULL),(28,6,NULL),(29,15,NULL),(30,15,NULL),(31,12,NULL),
(32,11,NULL),(33,20,NULL),(34,5,NULL),(35,4,NULL),(36,6,NULL),(37,17,NULL),
(38,5,NULL),(39,16,NULL),(40,6,NULL),(41,18,NULL),(42,12,NULL),(43,6,NULL),
(44,12,NULL),(45,2,NULL),(46,12,NULL),(47,15,NULL),(48,17,NULL),(49,2,NULL),
(50,16,NULL),(51,13,NULL),(52,17,NULL),(53,7,NULL),(54,2,NULL),(55,9,NULL),
(56,1,NULL),(57,14,NULL),(58,7,NULL),(59,15,NULL),(60,12,NULL),(61,13,NULL),
(62,8,NULL),(63,2,NULL),(64,6,NULL),(65,2,NULL),(66,12,NULL),(67,12,NULL),(68,4,NULL),
(69,5,NULL),(70,10,NULL),(71,16,NULL),(72,8,NULL),(73,14,NULL),(74,5,NULL),
(75,4,NULL),(76,3,NULL),(77,2,NULL),(78,2,NULL),(79,2,NULL),(80,3,NULL),(81,8,NULL),
(82,14,NULL),(83,5,NULL),(84,4,NULL),(85,2,NULL),(86,20,NULL),(87,12,NULL),
(88,1,NULL),(89,8,NULL),(90,18,NULL),(91,3,NULL),(92,3,NULL),(93,6,NULL),(94,1,NULL),
(95,4,NULL),(96,17,NULL),(97,15,NULL),(98,1,NULL),(99,20,NULL),(100,15,NULL);
/*!40000 ALTER TABLE `book` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

可以看到，book文件和备份的库文件类似。不同的是，book文件只包含book表的DROP、CREATE和
INSERT语句。
备份多张表使用下面的命令，比如备份book和account表：

#备份多张表
mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where后面附
带需要满足的条件。
举例：备份student表中id小于10的数据：

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

内容如下所示，insert语句只有id小于10的部分

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,100002,'JugxTY',157,280),(2,100003,'QyUcCJ',251,277),
(3,100004,'lATUPp',80,404),(4,100005,'BmFsXI',240,171),(5,100006,'mkpSwJ',388,476),
(6,100007,'ujMgwN',259,124),(7,100008,'HBJTqX',429,168),(8,100009,'dvQSQA',61,504),
(9,100010,'HljpVJ',234,185);

排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些
表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

通过如下指定判定文件中没有student表结构：

grep "student" no_stu_bak.sql

只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为 -d 选项；只备份数据可以使用 --no-create-info 简写为
-t 选项。
只备份结构

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
#使用grep命令，没有找到insert相关语句，表示没有数据备份。
[root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql
[root@node1 ~]#

只备份数据

mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
#使用grep命令，没有找到create相关语句，表示没有数据结构。
[root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql
[root@node1 ~]#

备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备
份存储过程及函数，使用 --events 或 -E 参数来备份事件。

举例：备份整个atguigu库，包含存储过程及事件：

使用下面的SQL可以查看当前库有哪些存储过程或者函数

mysql> SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM
information_schema.Routines WHERE ROUTINE_SCHEMA="atguigu";
+---------------+--------------+----------------+
| SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA |
+---------------+--------------+----------------+
| rand_num | FUNCTION | atguigu |
| rand_string | FUNCTION | atguigu |
| BatchInsert | PROCEDURE | atguigu |
| insert_class | PROCEDURE | atguigu |
| insert_order | PROCEDURE | atguigu |
| insert_stu | PROCEDURE | atguigu |
| insert_user | PROCEDURE | atguigu |
| ts_insert | PROCEDURE | atguigu |
+---------------+--------------+----------------+
9 rows in set (0.02 sec)

下面备份atguigu库的数据，函数以及存储过程。

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

查询备份文件中是否存在函数，如下所示，可以看到确实包含了函数。

grep -C 5 "rand_num" fun_atguigu_bak.sql
--
--
-- Dumping routines for database 'atguigu'
--
/*!50003 DROP FUNCTION IF EXISTS `rand_num` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb3 */ ;
/*!50003 SET character_set_results = utf8mb3 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode =
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISIO
N_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `rand_num`(from_num BIGINT ,to_num BIGINT) RETURNS
bigint
BEGIN
DECLARE i BIGINT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END ;;
--
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END ;;
DELIMITER ;
--
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO order_test (order_id, trans_id ) VALUES
(rand_num(1,7000000),rand_num(100000000000000000,700000000000000000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END ;;
DELIMITER ;
--
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(),rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END ;;
DELIMITER ;
--
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO `user` ( name,age,sex ) VALUES ("atguigu",rand_num(1,20),"male");
UNTIL i = max_num
END REPEAT;
COMMIT;
END ;;
DELIMITER ;

mysqldump常用选项

mysqldump其他常用选项如下

--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。
--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。
--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skipcomments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skipdisable-keys和--skip-add-locking选项。
--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、
MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者
no_field_options。
--complete_insert, -c：使用包括列名的完整的INSERT语句。
--debug[=debug_options], -#[debug_options]：写调试日志。
--delete，-D：导入文本文件前清空表。
--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。
--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。
--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可
以加速插入。
--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f：在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关
闭--single-transaction和--lock-tables。
--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例
如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出-
-database或--all-database选项，就包含到输出中。
--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。
--no-data，-d：不写表的任何行信息，只转储表的结构。
--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，
但可以用--skip-opt禁用。
--password[=password]，-p[password]：当连接服务器时使用的密码。
-port=port_num，-P port_num：用于连接的TCP/IP端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。
--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--
replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不
指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent，-s：沉默模式。只有出现错误时才输出。
--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。
--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。
--verbose，-v：冗长模式，打印出程序操作的详细信息。
--xml，-X：产生XML输出。

运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。

提示 如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内
存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用--skip-opt禁用。如果使用最
新版本的mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用--opt
或-e选项。

mysql命令恢复数据

mysql –u root –p [dbname] < backup.sql

单库备份中恢复单库

使用root用户，将之前练习中备份的atguigu.sql文件中的备份导入数据库中，命令如下：
如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示

mysql -uroot -p < atguigu.sql

否则需要指定数据库名称，如下所示

mysql -uroot -p atguigu4< atguigu.sql

全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql
mysql -uroot -pxxxxxx < all.sql

执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。

从全量备份中恢复单库

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可
以从全量备份中分离出单个库的备份。

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql
> atguigu.sql
#分离完成后我们再导入atguigu.sql即可恢复单个库

从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。
举例：我们有atguigu整库的备份，但是由于class表误操作，需要单独恢复出这张表

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >
class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
use atguigu;
mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)

物理备份：直接复制整个数据库

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一
定相同：
在Windows平台下，MySQL 8.0存放数据库的目录通常默认为 “ C:\ProgramData\MySQL\MySQL
Server 8.0\Data ”或者其他用户自定义目录；
在Linux平台下，数据库目录位置通常为/var/lib/mysql/；
在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”
但为了保证备份的一致性。需要保证：
方式1：备份前，将服务器停止。
方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中
的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索
引页写入硬盘。
这种方式方便、快速，但不是最好的备份方法，因为实际情况可能不允许停止MySQL服务器或者锁住
表，而且这种方法对InnoDB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方
便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。
注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为

说明：在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相
同。

此外，还可以考虑使用相关工具实现备份。比如， MySQLhotcopy 工具。MySQLhotcopy是一个Perl脚
本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的
途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。多用于mysql5.5之
前。

物理恢复：直接复制到数据库目录

步骤：
1）演示删除备份的数据库中指定表的数据
2）将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
3）查询相关表的数据是否恢复。需要使用下面的 chown 操作。

要求：
必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。
因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。
这种方式对 MyISAM类型的表比较有效，对于InnoDB类型的表则不可用。
因为InnoDB表的表空间不能直接复制。
在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：

chown -R mysql.mysql /var/lib/mysql/dbname

其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参
数表示数据库目录。

提示 Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户
组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将
文件夹的用户组变为mysql，将用户变为mysql。

表的导出与导入

表的导出

使用SELECT…INTO OUTFILE导出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。

举例：使用SELECT…INTO OUTFILE将atguigu数据库中account表中的记录导出到文本文件。（1）选择数
据库atguigu，并查询account表，执行结果如下所示。

use atguigu;
select * from account;
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 90 |
| 2 | 李四 | 100 |
| 3 | 王五 | 0 |
+----+--------+---------+
3 rows in set (0.01 sec)

（2）mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操
作。
查询secure_file_priv值：

mysql> SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.02 sec)

（3）上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语
句如下。

SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

（4）查看 /var/lib/mysql-files/account.txt`文件。

1 张三 90
2 李四 100
3 王五 0

使用mysqldump命令导出文本文件

举例1：使用mysqldump命令将将atguigu数据库中account表中的记录导出到文本文件：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account

mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。
打开account.sql文件，其内容包含创建account表的CREATE语句

[root@node1 mysql-files]# cat account.sql
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: atguigu
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-01-07 23:19:27

打开account.txt文件，其内容只包含account表中的数据

[root@node1 mysql-files]# cat account.txt
1 张三 90
2 李四 100
3 王五 0

举例2：使用mysqldump将atguigu数据库中的account表导出到文本文件，使用FIELDS选项，要求字段之
间使用逗号“，”间隔，所有字符类型字段值用双引号括起来：

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

语句mysqldump语句执行成功之后，指定目录下会出现两个文件account.sql和account.txt。
打开account.sql文件，其内容包含创建account表的CREATE语句。

[root@node1 mysql-files]# cat account.sql
-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x86_64)
--
-- Host: localhost Database: atguigu
-- ------------------------------------------------------
-- Server version 8.0.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`balance` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-01-07 23:36:39

打开account.txt文件，其内容包含创建account表的数据。从文件中可以看出，字段之间用逗号隔开，字
符类型的值被双引号括起来。

[root@node1 mysql-files]# cat account.txt
1,"张三",90
2,"李四",100
3,"王五",0

使用mysql命令导出文本文件

举例1：使用mysql语句导出atguigu数据中account表中的记录到文本文件：

mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"

打开account.txt文件，其内容包含创建account表的数据。

[root@node1 mysql-files]# cat account.txt
id name balance
1 张三 90
2 李四 100
3 王五 0

举例2：将atguigu数据库account表中的记录导出到文本文件，使用--veritcal参数将该条件记录分为多行
显示：

mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu >
"/var/lib/mysql-files/account_1.txt"

打开account_1.txt文件，其内容包含创建account表的数据。

[root@node1 mysql-files]# cat account_1.txt
*************************** 1. row ***************************
id: 1
name: 张三
balance: 90
*************************** 2. row ***************************
id: 2
name: 李四
balance: 100
*************************** 3. row ***************************
id: 3
name: 王五
balance: 0

举例3：将atguigu数据库account表中的记录导出到xml文件，使用--xml参数，具体语句如下。

mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"

[root@node1 mysql-files]# cat account_3.xml
<?xml version="1.0"?>
<resultset statement="SELECT * FROM account"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="id">1</field>
<field name="name">张三</field>
<field name="balance">90</field>
</row>
<row>
<field name="id">2</field>
<field name="name">李四</field>
<field name="balance">100</field>
</row>
<row>
<field name="id">3</field>
<field name="name">王五</field>
<field name="balance">0</field>
</row>
</resultset>

说明：如果要将表数据导出到html文件中，可以使用 --html 选项。然后可以使用浏览器打开

表的导入

使用LOAD DATA INFILE方式导入文本文件

举例1：
使用SELECT...INTO OUTFILE将atguigu数据库中account表的记录导出到文本文件

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';

删除account表中的数据：

DELETE FROM atguigu.account;

从文本文件account.txt中恢复数据：

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;

查询account表中的数据：

mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 90 |
| 2 | 李四 | 100 |
| 3 | 王五 | 0 |
+----+--------+---------+
3 rows in set (0.00 sec)

举例2：选择数据库atguigu，使用SELECT…INTO OUTFILE将atguigu数据库account表中的记录导出到文本
文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号"，"间隔，所有字段值用双引号括起来：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';

删除account表中的数据：

DELETE FROM atguigu.account;

从/var/lib/mysql-files/account.txt中导入数据到account表中：

LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account
FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

查询account表中的数据，具体SQL如下：

select * from account;
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 90 |
| 2 | 李四 | 100 |
| 3 | 王五 | 0 |
+----+--------+---------+
3 rows in set (0.00 sec)

使用mysqlimport方式导入文本文件

举例：
导出文件account.txt，字段之间使用逗号"，"间隔，字段值用双引号括起来：

SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';

删除account表中的数据：

DELETE FROM atguigu.account;

使用mysqlimport命令将account.txt文件内容导入到数据库atguigu的account表中：

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

查询account表中的数据：

select * from account;
mysql> select * from account;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | 张三 | 90 |
| 2 | 李四 | 100 |
| 3 | 王五 | 0 |
+----+--------+---------+
3 rows in set (0.00 sec)

数据库迁移

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地
传输到另一个计算机存储系统的过程。此外， 验证迁移数据的完整性 和 退役原来旧的数据存储 ，也被认
为是整个数据迁移过程的一部分。
数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾
难恢复和数据中心迁移。
根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为 物理迁移
和 逻辑迁移 两类。通常以尽可能 自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来。

迁移方案

物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要
求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。
物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。
不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同
目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件
组权限，之后在新服务器这边使用 mysqld 命令启动数据库。
逻辑迁移
逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移 ，都可以使用逻辑迁移。逻辑迁移中使用最多的
就是通过 mysqldump 等备份工具。

迁移注意点

相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动。
方式1： 因为迁移前后MySQL数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁
移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据
库。

方式2： 最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用
MySQL命令导入。
举例

#host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上
mysqldump –h host1 –uroot –p –-all-databases|
mysql –h host2 –uroot –p

在上述语句中，“|”符号表示管道，其作用是将mysqldump备份的文件给mysql命令；“--all-databases”表
示要迁移所有的数据库。通过这种方式可以直接实现迁移。

不同版本的数据库之间迁移注意点
例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷，
因此需要把数据库升级到8.0版本
旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而
最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集
进行修改，不然可能无法正常显示数据。
高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL
数据库。
不同数据库之间迁移注意点
不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类
型的数据库。这种迁移没有普适的解决方法。
迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关
键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL
Server数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET
类型，这些SQL Server数据库不支持。
另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句有差
别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL
的SQL语句兼容。
不同类型数据库之间的差异造成了互相迁移的困难，这些差异其实是商业公司故意造成的技术壁垒。但
是不同类型的数据库之间的迁移并不是完全不可能。例如，可以使用 MyODBC 实现MySQL和SQL Server之
间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。
MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后，手动更改 sql文件中的CREATE语
句。

迁移小结

删库了不敢跑，能干点啥？

delete：误删行

经验之谈：

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执
行这些操作，然后再将确认过的临时库的数据，恢复回主库。如果直接修改主库，可能导致对数
据的二次破坏。
当然，针对预防误删数据的问题，建议如下：
把 sql_safe_updates 参数设置为 on 。这样一来，如果我们忘记在delete或者update语句
中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。
如果确定要把一个小表的数据全部删掉，在设置了sql_safe_updates=on情况下，可以
在delete语句中加上where条件，比如where id>=0。

truncate/drop ：误删库/表

方案：
这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的
全量备份，并且实时备份binlog。
在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

取最近一次全量备份，假设这个库是一天一备，上次备份是当天凌晨2点；
用备份恢复出一个临时库；
从日志备份里面，取出凌晨2点之后的日志；
把这些日志，除了误删除数据的语句外，全部应用到临时库。

延迟复制备库
如果有非常核心的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。一般的主备复制结构
存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的
数据表也都一起被误删了。
延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这
个备库持续保持跟主库有 N秒的延迟。比如你把N设置为3600，这就代表了如果主库上有数据被误删了，
并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备
库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

预防误删库/表的方法

1. 账号分离 。这样做的目的是，避免写错命令。比如：
只给业务开发同学DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，可
以通过开发管理系统得到支持。
即使是DBA团队成员，日常也都规定只使用 只读账号 ，必要的时候才使用有更新权限的账号。
2. 制定操作规范 。比如：
在删除数据表之前，必须先 对表做改名 操作。然后，观察一段时间，确保对业务无影响以后再删除
这张表。
改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted )，然后删除表的动作必须通
过管理系统执行。并且，管理系统删除表的时候，只能删除固定后缀的表。

rm：误删MySQL实例
对于一个有高可用机制的MySQL集群来说，不用担心 rm删除数据了。只是删掉了其中某一个节点的数据
的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这
个节点上把数据恢复回来，再接入整个集群。

附录：MySQL常用命令
9.1 mysql
该mysql不是指mysql服务，而是指mysql的客户端工具。
语法：

mysql [options] [database]

#参数 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器IP或域名
-P, --port=# 指定连接端口
#示例 ：
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -h127.0.0.1 -P3306 -uroot -p密码

执行选项

-e, --execute=name 执行SQL语句并退出

此选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这
种方式尤其方便。

#示例：
mysql -uroot -p db01 -e "select * from tb_book";

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除
数据库等。
可以通过 ： mysqladmin --help 指令查看帮助文档

#示例 ：
mysqladmin -uroot -p create 'test01';
mysqladmin -uroot -p drop 'test01';
mysqladmin -uroot -p version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使
用到mysqlbinlog 日志管理工具。
语法：

mysqlbinlog [options] log-files1 log-files2 ...
#选项：
-d, --database=name : 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# : 忽略掉日志中的前n行命令。
-r,--result-file=name : 将输出的文本格式日志输出到指定文件。
-s, --short-form : 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 : 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 : 指定位置间隔内的所有日志。

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插
入表的SQL语句。

mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A

连接选项

#参数 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器IP或域名
-P, --port=# 指定连接端口

输出内容选项

#参数：
--add-drop-database 在每个数据库创建语句前加上 Drop database 语句
--add-drop-table 在每个表创建语句前加上 Drop table 语句 , 默认开启 ; 不开启 (--
skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；
一个.txt文件，数据文件，相当于select into outfile

#示例 ：
mysqldump -uroot -p db01 tb_book --add-drop-database --add-drop-table > a
mysqldump -uroot -p -T /tmp test city

mysqlimport/source

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

语法：

mysqlimport [options] db_name textfile1 [textfile2...]

示例：

mysqlimport -uroot -p test /tmp/city.txt

如果需要导入sql文件,可以使用mysql中的source 指令 :

source /root/tb_book.sql

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索
引。
语法：

mysqlshow [options] [db_name [table_name [col_name]]]

参数：

--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息

#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p --count
[root@node1 atguigu2]# mysqlshow -uroot -p --count
Enter password:
+--------------------+--------+--------------+
| Databases | Tables | Total Rows |
+--------------------+--------+--------------+
| atguigu | 24 | 30107483 |
| atguigu12 | 1 | 1 |
| atguigu14 | 6 | 14 |
| atguigu17 | 1 | 1 |
| atguigu18 | 0 | 0 |
| atguigu2 | 1 | 3 |
| atguigu_myisam | 1 | 4 |
| information_schema | 79 | 34034 |
| mysql | 38 | 4029 |
| performance_schema | 110 | 399957 |
| sys | 101 | 7028 |

+--------------------+--------+--------------+
11 rows in set.
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p atguigu --count
[root@node1 atguigu2]# mysqlshow -uroot -p atguigu --count
Enter password:
Database: atguigu
+------------+----------+------------+
| Tables | Columns | Total Rows |
+------------+----------+------------+
| account | 3 | 3 |
| book | 3 | 100 |
| dept | 3 | 3 |
| emp | 8 | 10 |
| order1 | 2 | 5715448 |
| order2 | 2 | 8000327 |
| order_test | 2 | 8000327 |
| salgrade | 3 | 0 |
| stu2 | 6 | 5 |
| student | 5 | 8100010 |
| t1 | 3 | 210000 |
| t_class | 3 | 0 |
| test | 2 | 0 |
| test_frm | 2 | 0 |
| test_paper | 1 | 0 |
| ts1 | 2 | 79999 |
| type | 2 | 240 |
| undo_demo | 3 | 1 |
| user | 1 | 1 |
| user1 | 4 | 1000 |
+------------+----------+------------+
20 rows in set.
#查询test库中book表的详细情况
mysqlshow -uroot -p atguigu book --count
[root@node1 atguigu2]# mysqlshow -uroot -p atguigu book --count
Enter password:
Database: atguigu Table: book Rows: 100
+--------+--------------+-----------+------+-----+---------+----------------+---------
------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra |
Privileges | Comment |
+--------+--------------+-----------+------+-----+---------+----------------+---------
------------------------+---------+
| bookid | int unsigned | | NO | PRI | | auto_increment |
select,insert,update,references | |
| card | int unsigned | | NO | MUL | | |
select,insert,update,references | |
| test | varchar(255) | utf8_bin | YES | | | |
select,insert,update,references | |
+--------+--------------+-----------+------+-----+---------+----------------+---------
------------------------+---------+

标签：事务,SET,--,数据库,高级,mysql,日志,id
From： https://www.cnblogs.com/abldh12/p/16989385.html

第11章_数据库的设计规范

范式

键和相关属性的概念

第二范式(2nd NF)

反范式化

是否符合三范式

第四范式

实战案例

ER 模型中有三个要素，分别是实体、属性和关系。

关于索引

PowerDesigner的使用

第12章_数据库其它调优策略

数据库调优的措施

优化数据库结构

使用非空约束

分析表、检查表与优化表

大表优化

其它调优策略

创建全局通用表空间

MySQL 8.0新特性：隐藏索引对调优的帮助

MySQL 8.0新特性：隐藏索引对调优的帮助

数据库事务

数据库事务概述

事务的状态

如何使用事务

隐式事务

使用举例1：提交与回滚

事务隔离级别

数据并发问题☆

MySQL支持的四种隔离级别

如何设置事务的隔离级别

不同隔离级别举例

事务的常见分类

第14章_MySQL事务日志

redo组成

redo的整体流程

redo log的刷盘策略

写入redo log buffer 过程

undo log的生命周期

小结

锁

从数据操作的类型划分：读锁、写锁

从对待锁的态度划分:乐观锁、悲观锁

锁监控

第16章_多版本并发控制

MVCC实现原理之ReadView

MVCC整体操作流程

总结

第17章_其他数据库日志

MySQL支持的日志

日志的弊端

通用查询日志(general query log)

查看当前状态

删除\刷新日志

查看默认情况

第18章_主从复制

第19章_数据库备份与恢复

mysqldump实现逻辑备份

物理备份：直接复制整个数据库

物理恢复：直接复制到数据库目录

迁移小结

相关文章

赞助商

阅读排行