首页  思维笔记  详情

MySQL

2023-04-14 14:40:42   19  举报





为你推荐

查看更多



MySQL

mysql之mvcc与BufferPool缓存机制

MySQL

MySQL

MySQL部分知识点整理

作者其他创作

大纲/内容

数据库调优

SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

全值匹配我最爱

最佳左前缀规则

主键插入顺序

计算、函数、类型转换(自动或手动)导致索引失效

类型转换导致索引失效

优化器会根据联合索引的顺序来调整where中条件的顺序

“右边”指的是索引的右边，不是条件语句的右边

创建的联合索引中，务必把范围涉及到的字段写在最后

范围条件右边的列索引失效

不等于(!= 或者<>)索引失效

is null可以使用索引，is not null无法使用索引

like以通配符%开头索引失效

OR 前后存在非索引的列，索引失效

数据库和表的字符集统一使用utf8mb4

练习

对于单列索引，尽量选择针对当前query过滤性更好的索引。

在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。

在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

一般性建议

索引失效的11种情况

没有索引时，关联查询中会进行全表扫描，产生笛卡尔积（有几张表，就会嵌套几层循环）

在设计多表连接时，一定要保证连接字段的类型和长度等相同，否则会因类型转化导致索引失效

注意

SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

左外连接会查询驱动表中的全部数据以及被驱动表中的部分关联数据，即驱动表需要全表扫描，被驱动表不需要

CREATE INDEX Y ON book(card);

被驱动表中用到了索引

如果只能创建一个索引，最好在被驱动表（book）上创建索引

采用左外连接

CREATE INDEX X ON `type`(card);CREATE INDEX Y ON book(card);

查询优化器将type表作为被驱动表

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

内连接中的关联字段都有索引时，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

DROP INDEX X ON `type`;

此时，只有book有索引，查询优化器将book表作为被驱动表

如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

向book表中添加数据（20条数据）

book表中有20条数据，type表中有10条数据

在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”（两个表都没有索引的情况下，依然是“小表驱动大表”）

采用内连接

SELECT * FROM A JOIN B ON ...

EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

A一定是驱动表吗?

内连接

通常，大家会认为A就是驱动表，B就是被驱动表。但也未必。

创建表并添加数据

此时，表b作为驱动表，而表a作为被驱动表

EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

测试

SELECT * FROM A LEFT JOIN B ON ...SELECT * FROM B RIGHT JOIN A ON ...

外连接

驱动表和被驱动表

图示

从这里可以看出，当表A（驱动表）的记录数少时，总的开销就小，也就是所谓的“小表驱动大表”

开销统计

Simple Nested-Loop Join（简单嵌套循环连接）

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。

主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。

Index Nested-Loop Join（索引嵌套循环连接）

为了减少被驱动表的IO次数

不再是逐条获取驱动表的数据，而是一块一块的获取

引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表

被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作)

主要思路

这里缓存的不只是关联表的列，select 后面的列也会缓存起来

在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列（行）

通过show variables like ' %optimizer_ switch%' 查看block_ nested_ loop 状态。默认是开启的。

block_nested_loop

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_ buffer_ size=256k

join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的Join Buffer空间(64 位Windows除外，其大值会被截断为4GB并发出警告)。

join_buffer_size

参数设置

Block Nested-Loop Join (块嵌套循环连接)

JOIN语句原理

整体效率比较: INLJ> BNLJ> SNLJ

永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是表行数*每行大小)

为被驱动表匹配的条件增加索引|(减少内层表的循环匹配次数)

增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)

减少驱动表不必要的字段查询(字段越少， join buffer所缓存的数据就越多)

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

Nested Loop：对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。

这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。

在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。

Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。

Hash Join

其它

小结

关联查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即-一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

②子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

③对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

效率不高

在MySQL中，可以使用连接(JOIN) 查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

子查询优化

在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢?

优化建议

filesort，加载到内存中排序

没有索引（除主键索引）

原因是这里需要进行回表操作，优化器判定不采用索引效率更高

这里的数据量较少，采用索引时虽然需要进行回表操作，但总的来说效率更高

第一个可以使用索引，第二个不能使用索引

order by时不limit，索引失效

order by时顺序错误，索引失效

无过滤，不索引

测试（测试结果与数据量相关）

其中，where中用到了age索引，排序用到了全部索引

where语句中过滤后还剩18272条数据，优化器选择使用index索引排序

方案一：为了去掉filesort我们可以把索引建成

其中，where使用了idx_age_stuno_name索引中的age和stuno索引，排序使用filesort

where语句中过滤后还剩18条数据，如果采用index排序，则需要进行回表操作；优化器这里选择将数据加载到内存中排序（即filesort排序）

方案二：使用filesort排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

原因

结论

案例（filesort与index排序）

双路排序（慢）

尝试提高sort_buffer_size

提高这个参数，会增加用改进算法的概率。

尝试提高max_length_for_sort_data

单路排序（快）

filesort算法

排序优化

group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。

group by先排序再分组，遵照索引建的最佳左前缀法则

当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置

where效率高于having，能写在where限定的条件就不要写在having中了

包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

GROUP BY优化

问题引出

优化思路一

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

优化思路二

优化分页查询

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。“一个索引包含了满足查询结果的数据就叫做覆盖索引。“

非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)。简单说就是，索引列+主键包含SELECT到FROM之间查询的列。

什么是覆盖索引？

无法使用索引（基于回表成本的考虑）

索引中包含了所有要查询的字段，虽然where子句中使用了<>，但是优化器判断使用索引后，不需要再进行回表操作，成本相对较小，因此选择使用了索引

可以使用索引

索引中包含三个字段，age、NAME和id

举例

1.避免Innodb表进行索引的二次查询(回表)

2.可以把随机I0变成顺序I0加快查询效率

好处

弊端

优先考虑覆盖索引

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数

storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行

不使用ICP索引的扫描过程

storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。server 层：对返回的数据，使用table filter条件做最后的过滤。

使用ICP扫描的过程

使用前，存储层多返回了需要被index filter过滤掉的整行记录。

使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。ICP的加速效果取决于在存储引擎内通过 ICP筛选掉的数据的比例。

成本差别

因为聚簇索引中包含了所有的字段，不需要回表，ICP针对的是需要回表的情况

① 只能用于二级索引(secondary index)

② explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。

③ 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。

④ ICP可以用于MyISAM和InnnoDB存储引擎

⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持

⑥ 当SQL使用覆盖索引时，不支持ICP优化方法

ICP的使用条件

SET optimizer_switch =‘index_condition_pushdown=off ' ;

全局关闭

SELECT /*+ no_ .icp (people) */ * FROM people WHERE zipcode=' 000001' AND lastname LIKE '%张%' ;

临时关闭

关闭索引下推

SET optimizer_switch =‘index_condition_pushdown=on ' ;

开启索引下推

ICP的开启和关闭（默认开启）

SELECT * FROM tuserWHERE NAME LIKE '张%'AND age = 10AND ismale = 1;

案例1

首先，zipcode使用了索引中的zipcode字段，lastname不能使用索引（以通配符%开头），但联合索引的B+树的叶子节点中包含了这个字段，因此可以直接在符合条件1的叶子节点中对条件2进行对比，再次进行过滤（即索引条件下推），减少需要进行回表的记录；索引中不含有address字段，该字段需要进行回表比较

案例2

索引条件下推（ICP）

索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。

不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？

EXISTS 和 IN 的区分

SELECT COUNT(*)

SELECT COUNT(1)

SELECT COUNT(具体字段)

COUNT(*)与COUNT(具体字段)效率（不考虑字段是否为空）

① MySQL 在解析的过程中，会通过查询数据字典将\"*\"按序转换成所有列名，这会大大的耗费资源和时间。

② 无法使用覆盖索引

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。

关于SELECT(*)

针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。

LIMIT 1 对优化的影响

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

回滚段上用于恢复数据的信息

被程序语句获得的锁

redo / undo log buffer 中的空间

管理上述 3 种资源中的内部花费

COMMIT 所释放的资源

多使用COMMIT

其他查询优化策略

存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

1. 可靠性不高

对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

2. 安全性不高

自增ID的性能较差，需要在数据库服务器端生成。

3. 性能差

业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

4. 交互多

最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

5. 局部唯一性

自增ID的问题

卡号会被回收重复使用，上一个会员的信息会对下一个使用者产生影响

选择卡号（cardno）

用户可能不愿透露身份证号，而电话也存在重用的情况

选择会员电话或身份证号

为了能够唯一地标识一个会员的信息，需要为会员信息表设置一个主键。

所以，建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。

业务字段做主键

订单ID = 时间 + 去重字段 + 用户ID后6位尾号

这样的设计能做到全局唯一，且对分布式系统查询及其友好

大胆猜测，淘宝的订单ID设计应该是：

订单号是19位的长度，且订单的最后5位都是一样的，都是08113。且订单号的前面14位部分是单调递增的。

淘宝订单表的主键是如何设计的？

对应表的主键自增ID，如告警、日志、监控等信息

非核心业务

全局唯一保证在各系统之间都是唯一的

单调递增是希望插入时不影响数据库性能

主键设计至少应该是全局唯一且是单调递增

核心业务

推荐的主键设计

时间低位在前，高位在后，索引是无序的

UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00：00：00.00到现在的100ns的计数。

可以看到UUID存储的时间精度比TIMESTAMPE更高，时间维度发生重复的概率降低到1/100ns

时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。

MAC地址用于全局唯一

为什么UUID是全局唯一的？

一个字符存储占用一个字节

UUID根据字符串进行存储，设计时还带有无用\"-\"字符串，因此总共需要36个字节。

为什么UUID占用36个字节？

使用UUID

将时间高低位互换，则时间就是单调递增的了，也就变得单调递增

MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的\"-\"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

存储空间降低

可以通过MySQL8.0提供的uuid_to_bin函数实现上述功能，同样的，MySQL也提供了bin_to_uuid函数进行转化

在当今的互联网环境中，非常不推荐自增ID作为主键的数据库设计。更推荐类似有序UUID的全局唯一的实现。另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样的主键设计就更为考验架构师的水平了。

可以在总部 MySQL 数据库中，有一个管理信息表，在这个表中添加一个字段，专门用来记录当前会员编号的最大值

门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当前会员编号的最大值。

这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进行操作，就解决了各门店添加会员时会员编号冲突的问题。

比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。

手动赋值字段做主键！

如果不是MySQL8.0 肿么办？

改造UUID

一种思路

淘宝数据库，主键如何设计的？

索引优化与查询优化

要想设计一个结构合理的关系型数据库，必须满足一定的范式。

可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。

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

数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，

简介

范式的定义会使用到主键和候选键，数据库中的键(Key) 由一个或者多个属性组成

能唯一标识元组的属性集叫做超键

超键

如果超键不包括多余的属性，那么这个超键就是候选键

候选键

用户可以从候选键中选择一个作为主键

主键

如果数据表R1中的某属性集不是R1的主键，而是另一一个数据表R2的主键，那么这个属性集就是数据表R1的外键

外键

包含在任一候选键中的属性称为主属性

主属性

与主属性相对，指的是不包含在任何一个候选键中的属性

非主属性

相关参数设置

总共的redo日志文件大小其实就是： innodb_log_file_size × innodb_log_files_in_group

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下

已经失效的ib_logfile文件可以被后来的日志数据覆盖

checkpoint机制

采用循环使用的方式向redo日志文件组里写数据

日志文件组

redo log file

redo日志

是事务原子性的保证

在事务中更新数据的前置操作其实是要先写入一个 undo log

说明

至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了

插入了一条记录

至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了

删除了一条记录

至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了

修改了一条记录

数据改动层面的理解

undo用于将数据库物理地恢复到执行语句或事务之前的样子

undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子

所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同

作用1：回滚数据

这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

在InnoDB存储引擎中MVCC的实现是通过undo来完成

当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息以此实现非锁定读取

作用2：MVCC

作用

每个回滚段记录了1024 个 undo log segment ，而在每个undo log segment段中进行 undo页的申请

在 InnoDB1.1版本之前（不包括1.1版本），只有一个rollback segment，因此支持同时在线的事务限制为 1024 。对绝大多数的应用来说都已经够用

从1.1版本开始InnoDB支持最大 128个rollback segment ，故其支持同时在线的事务限制提高到了 128*1024

回滚段（rollback segment）与undo页

undo页的重用

1. 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务

2. 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。

如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用。

3. 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。

4. 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间。

将undo log放入列表中，以供之后的purge操作

判断undo log所在的页是否可以重用，若可以分配给下个事务使用

5. 当事务提交时，InnoDB存储引擎会做两件事情

回滚段与事务

未提交的回滚数据(uncommitted undo information)

事务提交后并不能马上删除undo log及undo log所在的页

可能还有其他事务需要通过undo log来得到行记录之前的版本

故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断

已经提交但未过期的回滚数据(committed undo information)

事务已经提交并过期的数据(expired undo information)

回滚段中的数据分类

存储结构

指在inser操作中产生的undo log

该undo log可以在事务提交后直接删除。不需要进行purge操作。

insert undo log

记录的是对delete和update操作产生的undo log

可能需要提供MVCC机制，因此不能在事务提交时就进行删除

update undo log

类型

清理undo页

清陈page里面带有Delete_ Bit标识的数据行

purge线程作用

例子

只有Buffer Pool的流程

有了Redo Log和Undo Log之后

简要过程

如果没有为表显式的定义主键，并且表中也没有定义唯一索引，那么InnoDB会自动为表添加一个row_ id的隐藏列作为主键。

DB_ROW_ID

DB_TRX_ID

回滚指针，本质上就是指向undo log的指针。

DB_ROLL_PTR

隐藏的列

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

执行INSERT时

更新的字段不是主键

UPDATE user SET name=\"Sun\" WHERE id=1;

更新的字段是主键，会新建一个行记录，原有的行记录标记为删除状态（deletemark=1)

UPDATE user SET id=2 WHERE id=1;

执行UPDATE时

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日志

MySQL事务日志

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据(比如订单、金额等)，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。

需要对并发操作进行控制，保证数据的一致性

锁机制也为实现MySQL的各个隔离级别提供了保证

锁冲突也是影响数据库并发访问性能的一个重要因素。

概述

读-读情况，即并发事务相继读取相同的记录。

读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

读-读情况

这种情况下会发生脏写的问题

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

锁其实是一个内存中的结构，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的

在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的

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

事务T1还没结束，事务T2也要操作该条记录，因此T2进入等待状态

事务T1执行结束，会检查有没有其他事务在等待操作该条记录，如果有，则将等待中的锁结构的is_waiting改为false

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

不加锁

就是在内存中生成了对应的锁结构，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作。

获取锁成功，或者加锁成功

获取锁失败，或者加锁失败，或者没有获取到锁

写-写情况

一个事务进行读取操作，另一个进行改动操作

可能发生脏读、不可重复读、幻读的问题

读-写或写-读情况

方案一：读操作利用多版本并发控制（ MVCC ，下章讲解），写操作进行加锁

方案二：读、写操作都采用加锁的方式

采用 MVCC 方式的话，读-写操作彼此并不冲突，性能更高

采用加锁方式的话，读-写操作彼此需要排队执行，影响性能

一般情况下我们当然愿意采用 MVCC 来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行。

解决方案

MySQL并发事务访问相同记录

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

读锁/共享锁

英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。

这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

写锁/排他锁

兼容情况

其他的事务无论是加了S锁还是X锁，都要等待

对读操作添加X锁

1. 锁定读

UPDATE

一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

INSERT

2. 写操作

特殊情况

对数据操作的类型划分

对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上

管理锁是很耗资源的事情(涉及获取、检查、释放锁等动作)，数据库系统需要在高并发响应和系统性能两方面进行平衡

锁的粒度

表锁是开销最小的策略(因为粒度比较大)

表级锁一次会将整个表锁定，所以可以很好的避免死锁问题

锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

概要

一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁和 X锁

只会在一些特殊情况下，比方说崩溃恢复过程中用到

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

两种模式

表级别的S锁、X锁

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

事务有意向对表中的某些行加共享锁（S锁）

事务要获取某些行的 S 锁，必须先获得表的 IS 锁

事务有意向对表中的某些行加排他锁（X锁）

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

意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，

在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在数据表的对应意向锁

意向锁之间

意向锁和其他表级别锁

1. InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存

2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与共享锁 / 排他锁互斥。

3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

4. 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

兼容关系

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。（不然我们直接用普通的表锁就行了）

并发性

② 意向锁（intention lock）

由于这个表的id字段声明了AUTO_INCREMENT，意味着在书写插入语句时不需要为其赋值，SQL语句修改如下所示

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

可以预先确定要插入的行数（当语句被初始处理时）的语句。

1. “Simple inserts” （简单插入）

事先不知道要插入的行数（和所需自动递增值的数量）的语句。

2. “Bulk inserts” （批量插入）

3. “Mixed-mode inserts” （混合模式插入）

插入的三种模式

因为是表级锁，当在同一时间多个事务中执行insert的时候，对于AUTO-INC锁的争夺会限制并发能力。

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

对于“Simple inserts”（要插入的行数事先已知），则通过在 mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。

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

在此锁定模式下，自动递增值保证在所有并发执行的所有类型的insert语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。

（3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)

介绍

③ 自增锁（AUTO-INC锁）

如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

保证读写的正确性

加 MDL读锁

当对一个表做增删改查操作的时候

加 MDL 写锁

当对一个表做结构变更操作的时候

可以有多个线程同时对一-张表增删改查

读锁之间不互斥

用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。

第1事务获得了读锁，还未释放；第2个事务想要获得写锁，则需要等待；第3个事务此时如果想要获得读锁，则需要等待

第1事务获得了读锁，还未释放；第2个事务可以获得读锁；第3个事务此时如果想要获得写锁，则需要等待

读写锁之间、写锁之间是互斥的

两个读锁有些情况下不能同时获得，降低了并发性

互斥关系

不需要显式使用，在访问一个表的时候会被自动加上

④ 元数据锁（MDL锁）

表级锁

行锁(Row Lock)也称为记录锁，就是锁住某一行(某条记录row)

MySQL 服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

InnoDB与MyISAM的最大不同有两点: 一是支持事务(TRANSACTION)；二是采用了行级锁

锁定力度小，发生锁冲突概率低，可以实现的并发度高

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

缺点

优缺点

记录锁也就是仅仅把一条记录锁上

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

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

有S锁和X锁之分的，称之为 S型记录锁和 X型记录锁

① 记录锁（Record Locks）

幻读的解决方案之一

可能导致死锁

② 间隙锁（Gap Locks

有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录

记录锁和间隙锁的合体

③ 临键锁（Next-Key Locks）

一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁（ next-key锁也包含 gap锁），如果有的话，插入操作需要等待，直到拥有 gap锁的那个事务提交

InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待

插入意向锁是一种 Gap锁，不是意向锁，在insert操作时产生。

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

④ 插入意向锁（Insert Intention Locks）

行级锁

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

开销介于表锁和行锁之间

会出现死锁

锁定粒度介于表锁和行锁之间，并发度一般

页级锁

每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的

当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级

锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了

锁的升级

从数据操作的粒度划分

对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁

这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）

行锁，表锁等，读锁，写锁等

Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现

悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突

商品秒杀

应用场景

1. 悲观锁（Pessimistic Locking）

认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁

但是在更新的时候会判断一下在此期间别人有没有去更新这个数据

不采用数据库自身的锁机制，而是通过程序来实现

可以采用版本号机制或者 CAS机制实现

适用于多读的应用类型，这样可以提高吞吐量

在业务处理中，可以把要处理的数据看作版本号的表现形式

数据更新前，与之前查到的数据进行比较

CSA（Compare and Set）

1.乐观锁的版本号机制

2.乐观锁的时间戳机制

实现机制

1. 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。

版本号机制

时间戳机制

秒杀案例

2. 乐观锁（Optimistic Locking）

从对待锁的态度划分

如果执行插入语句时，没有间隙锁阻止，这个时候就会出现隐式锁，保证插入数据事务的完整性

A. InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。

B. 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务，首先将隐式锁转换为显式锁 (就是为该事务添加一个锁)。

C. 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。

D. 等待加锁成功，被唤醒，或者超时。

E. 写数据，并将自己的trx_id写入trx_id字段。

隐式锁的逻辑过程

1. 隐式锁

2. 显式锁

按加锁的方式划分

整个库处于只读状态

数据更新语句（数据的增删改）

数据定义语句（包括建表、修改表结构等）

更新类事务的提交语句

其他线会被阻塞

全局锁就是对整个数据库实例加锁

全库逻辑备份

Flush tables with read lock

命令

全局锁

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。

1. 两个或者两个以上事务

2. 每个事务都已经持有锁并且申请新的锁

3. 锁资源同时只能被同一个事务持有或者不兼容

4. 事务之间因为持有锁和申请锁导致彼此循环等待

产生死锁的必要条件

方式1:等待，直到超时(innodb_ lock. _wait_ _timeout=50s) 。

方式1检测死锁太过被动，innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发；是一种主动的死锁检测机制

方式2:使用死锁检测进行死锁处理

如何处理

如何避免

死锁

在同一个事务中进行加锁操作

被加锁的记录在同一个页面中

加锁的类型是一样的

等待状态是一样的

一个事务对多条记录加锁，就要创建多个锁结构呢?

不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个事务的信息。

此锁所在的事务信息在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比方说事务id等

1. 锁所在的事务信息

对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

2. 索引信息

记载着是对哪个表加的锁，还有其他的一些信息

表锁

记录所在表空间

Space ID

记录所在页号

Page Number

对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁

为此在行锁结构的末尾放置了一堆比特位，这个n_bits 属性代表使用了多少比特位

n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后也不至于重新分配锁结构

n_bits

行锁

3. 表锁／行锁信息

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

占用低4位

锁的模式（ lock_mode ）

占用第5～8位，不过现阶段只有第5位和第6位被使用

锁的类型（ lock_type ）

使用其余的位来表示

行锁的具体类型（ rec_lock_type ）

4. type_mode

基于内存空间的节省，所以把 is_waiting 属性放到了 type_mode 这个32位的数字中

is_waiting 属性呢？

5. 其他信息

6. 一堆比特位

锁的内存结构

锁监控

锁

trx_id

roll_pointer

隐藏字段

undo日志实现多版本

readview实现并发控制

MVCC 是通过数据行的多个版本管理来实现数据库的并发控制

换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁

使得在InnoDB的事务隔离级别下执行一致性读操作有了保证

快照读又叫一致性读，读取的是快照数据

不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读

快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

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

快照读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据）

读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

当前读

快照读与当前读

MySQL可以在可重复读的隔离级别下解决幻读问题，原因在于采用了MVCC，并发性能比串行化要高

MySQL中的隔离级别

每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id 赋值给trx_id 隐藏列

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

聚簇索引记录中都包含两个必要的隐藏列

形成版本链

对版本记录进行管理

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图

当事务启动时，会生成数据库系统当前的一个快照，InnoDB 为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID (“活跃”指的就是，启动了但还没提交)。

哪些隔离级别中使用

使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

创建这个 Read View 的事务 ID

1. creator_trx_id

表示在生成ReadView时当前系统中活跃的读写事务的事务id列表

2. trx_ids

活跃的事务中最小的事务 ID

3. up_limit_id

表示生成ReadView时系统中应该分配给下一个事务的 id 值。

4. low_limit_id

4个重要内容

意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问

被访问版本的trx_id属性值==ReadView中的 creator_trx_id 值

表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

被访问版本的trx_id属性值 < ReadView中的 up_limit_id 值

表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。

被访问版本的trx_id属性值>=ReadView中的 low_limit_id 值

如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问

如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

需要判断一下trx_id属性值是不是在 trx_ids 列表中

ReadView的up_limit_id值 < 被访问版本的trx_id属性值 < low_limit_id 之间

ReadView的规则

readview

“三板斧”

1. 首先获取事务自己的版本号，也就是事务 ID；

2. 获取 ReadView；

3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

5. 最后返回符合规则的数据。

MVCC整体操作流程

undo日志都连起来，串成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。每个版本中还包含生成该版本时对应的事务id 。

举例

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

READ COMMITED

一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View

REPEATABLE READ

不同隔离级别下的readview生成时机

READ COMMITTD 在每一次进行普通SELECT操作前都会生成一个ReadView

REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了

1.读写之间阻塞的问题。通过MVCC可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力

2.降低了死锁的概率。这是因为MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。

3.解决快照读的问题。当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

可以解决的问题

总结

多版本并发控制（MVCC）

事务

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

慢查询日志

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

通用查询日志

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

错误日志

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

二进制日志 binlog

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

中继日志 rely log

记录数据定义语句执行的元数据操作

数据定义语句日志

日志类型

降低MySQL数据库的性能

占用大量的磁盘空间

慢查询日志(slow query log)

启动和关闭MySQL服务

所有用户的连接开始时间和截止时间

发给 MySQL 数据库服务器的所有 SQL 指令

默认关闭

用来记录用户的所有操作

查看当前状态

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

方式1：永久性方式

SET GLOBAL general_log=on; # 开启通用查询日志

SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置

方式2：临时性方式

启动日志

通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件

查看日志

修改 my.cnf 或者 my.ini 文件，把[mysqld]组下的 general_log 值设置为 OFF 或者把general_log一项注释掉。

修改保存后，再重启MySQL服务，即可生效

SET GLOBAL general_log=off;

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

停止日志

手动删除文件

mysqladmin -uroot -p flush-logs

使用如下命令重新生成查询日志文件

删除\\刷新日志

通用查询日志(general query log)

在MySQL数据库中，错误日志功能是默认开启的

错误日志无法被禁止

名称默认为 mysqld.log （Linux系统）或hostname.err （mac系统）

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)

查询错误日志的存储路径

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的硬盘空间

MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除

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

[root@atguigu01 log]# mysqladmin -uroot -p flush-logs

错误日志(error log)

binlog即binary log，二进制日志文件，也叫作变更日志（update log）

记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句

它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程。

数据恢复

由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

数据复制

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

默认情况

[mysqld]#启用二进制日志log-bin=atguigu-binbinlog_expire_logs_seconds=600 #保存时间max_binlog_size=100M # 文件大小

重新启动MySQL服务

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

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

mysql> set global sql_log_bin=0;ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be usedwith SET GLOBAL

global 级别

session级别

日志参数设置

MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。

MySQL服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。

mysqlbinlog -v \"/var/lib/mysql/binlog/atguigu-bin.000002\"

使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。

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

filename ：是日志文件名。

可以指定恢复数据库的起始时间点和结束时间点

--start-date、--stop-date

可以指定恢复数据的开始位置和结束位置

--start-position、--stop-position

option ：可选项

恢复数据

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

PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

PURGE MASTER LOGS：删除指定日志文件

RESET MASTER 删除所有的二进制日志文件

删除二进制日志

事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache

表示每次提交事务都只write，由系统自行判断什么时候执行fsync；虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失

表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样；

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

设置为N(N>1)

write和fsync的时机参数设置

写入机制

记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的

redo log 它是物理日志

记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层

binlog 是逻辑日志

redo log让InnoDB存储引擎拥有了崩溃恢复能力。

binlog保证了MySQL集群架构的数据一致性。

都属于持久化的保证，但是则重点不同

与redo log的对比

以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样

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

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

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

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

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

图解

两阶段提交

二进制日志(bin log)

中继日志只在主从服务器架构的从服务器上存在

从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志

如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的服务器名称与之前不同

中继日志里是包含从服务器名的

恢复的典型错误

中继日志(relay log)

其他日志

一般应用对数据库而言都是“ 读多写少 ”，也就说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做主从架构、进行读写分离，这样同样可以提升数据库的并发处理能力

如何提升数据库并发能力

首先考虑的是如何优化SQL和索引

其次才是采用缓存的策略

最后才是对数据库采用主从架构，进行读写分离

提升数据库高并发访问的效率

读写分离

主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。

数据备份

高可用性

是一个主库线程

当从库线程连接的时候，主库可以将二进制日志发送给从库

当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。

二进制日志转储线程（Binlog dump thread）

会连接到主库，向主库发送请求更新 Binlog

这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（Relay log）

从库 I/O 线

会读取从库中的中继日志

执行日志中的事件，将从库中的数据与主库保持同步

从库 SQL 线程

三个线程

原理剖析

步骤1： Master 将写操作记录到二进制日志（ binlog ）。

步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（ relay log ）；

步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制

复制三步骤

每个 Slave 只有一个 Master

每个 Slave 只能有一个唯一的服务器ID

每个 Master 可以有多个 Slave

基本原则

binlog格式设置

读库和写库的数据一致(最终一致)；

写数据必须写到写库；

读数据必须到读库(不一定)；

要求

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

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

1、从库的机器性能比主库要差

2、从库的压力大

3、大事务的执行

1. 降低多线程大事务并发的概率，优化业务逻辑

2. 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。

3. 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。

4. 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。

5. 实时性要求的业务读强制走主库，从库只做灾备，备份

如何减少主从延迟

主从延迟问题

读写分离情况下，解决主从同步中数据不一致的问题，就是解决主从之间数据复制方式的问题

方法 1：异步复制

至少保证了一个从库接收到了binlog；增加了主库等待从库的响应时间

方法 2：半同步复制

将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于（N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 COMMIT 即可

方法 3：组复制（MGR）

解决一致性问题

同步数据一致性问题

主从复制

MySQL

 收藏

立即使用

MySQL

osgpjw5g4Aro2Vj

职业：暂无

去主页

