首页  思维笔记  详情

MySQL

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





MySQL部分知识点整理

数据库

学习笔记

读书笔记

作者其他创作

大纲/内容

数据库调优

索引优化与查询优化

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

索引失效的11种情况

全值匹配我最爱

最佳左前缀规则

主键插入顺序

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

类型转换导致索引失效

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

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

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

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

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

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

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

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

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

练习

一般性建议

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

在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。

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

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

关联查询优化

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

注意

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

采用左外连接

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

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

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

CREATE INDEX Y ON book(card);

被驱动表中用到了索引

采用内连接

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

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

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

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

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

DROP INDEX X ON `type`;

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

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

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

向book表中添加数据（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))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

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

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

JOIN语句原理

驱动表和被驱动表

内连接

SELECT * FROM A JOIN B ON ...

A一定是驱动表吗?

不一定， 优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表, 反之就是被驱动表。通过explain关键字可以查看。

EXPLAIN SELECT * FROM a 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就是驱动表，B就是被驱动表。但也未必。

测试

创建表并添加数据

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB; CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB; INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

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

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

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

图示

开销统计

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

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

图示

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

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

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

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

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

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访 问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然 后再从驱动表中加载一条记录, 然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了I0的次数。

主要思路

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

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

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

将简单嵌套循环中的多次比较合并成一次, 降低了被驱动表的访问频率

注意

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

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

图示

开销统计

参数设置

block_nested_loop

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

join_buffer_size

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

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

小结

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

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

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

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

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

Hash Join

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

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

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

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

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

它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的 重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1 = B.COL2),这是由Hash的特点决定的。

其它

子查询优化

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

效率不高

①执行子查询时, MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记 录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和0资源，产生大量的慢查询。

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

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

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

排序优化

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

优化建议

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

没有索引（除主键索引）

filesort，加载到内存中排序

创建索引 CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

order by时不limit，索引失效

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

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

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

创建索引 CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno);

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

order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

无过滤，不索引

小结

案例（filesort与index排序）

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

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

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

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

方案二：使用filesort排序

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

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

原因

结论

filesort算法

双路排序（慢）

单路排序（快）

尝试提高sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程 (connection)的1M-8M之间调整。MySQL5.7, InnoDB存储引擎默认值是1048576字节，1MB。

尝试提高max_length_for_sort_data

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

但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/0活动和低的处理 器使用率。如果需要返回的列的总长度大于max_length_for_sort_data,使用双路算法，否则使用单路算法。 1024-8192字节之间调整

GROUP BY优化

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

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

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

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

减少使用order by,和业务沟通能不排序就不排序，或将排序放到程序端去做。 Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

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

优化分页查询

问题引出

一般分页查询时， 通过创建覆盖索弓|能够比较好地提高性能。一个常见又非常头疼的问题就是limit 200000,10 ，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非 常大。

优化思路一

在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。

优化思路二

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

优先考虑覆盖索引

什么是覆盖索引？

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

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

举例

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

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

可以使用索引

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

好处

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

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

弊端

索引条件下推（ICP）

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

不使用ICP索引的扫描过程

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

使用ICP扫描的过程

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

成本差别

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

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

ICP的使用条件

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

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

② 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 ' ;

案例1

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

案例2

CREATE TABLE `people` (   `id` INT NOT NULL AUTO_INCREMENT,   `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,   `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,   `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,   `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`) ) ENGINE=INNODB AUTO_INCREMENT=5  DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

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

其他查询优化策略

EXISTS 和 IN 的区分

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

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

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

SELECT COUNT(*)

SELECT COUNT(1)

SELECT COUNT(具体字段)

关于SELECT(*)

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

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

② 无法使用 覆盖索引

LIMIT 1 对优化的影响

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

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

多使用COMMIT

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

COMMIT 所释放的资源

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

被程序语句获得的锁

redo / undo log buffer 中的空间

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

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

自增ID的问题

1. 可靠性不高

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

2. 安全性不高

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

3. 性能差

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

4. 交互多

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

5. 局部唯一性

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

业务字段做主键

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

选择卡号（cardno）

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

选择会员电话 或 身份证号

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

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

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

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

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

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

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

推荐的主键设计

非核心业务

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

核心业务

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

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

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

一种思路

使用UUID

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

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

为什么UUID是全局唯一的？

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

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

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

MAC地址用于全局唯一

为什么UUID占用36个字节？

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

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

改造UUID

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

存储空间降低

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

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

SET @uuid = UUID(); SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

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

如果不是MySQL8.0 肿么办？

手动赋值字段做主键！

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

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

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

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

数据库的设计规范

范式（Normal Form）

简介

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

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

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

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

相关概念

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

超键

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

候选键

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

主键

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

外键

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

主属性

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

非主属性

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

分类

图示

第一范式 1st NF

确保数据表中每个字段的值必须具有 原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元

第二范式 2ed NF

第一范式的基础上，还要满足数据表里的每一条数据记录， 都是可唯一标识的。 而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

如果知道主键的所有属性的值，就可以检索到任何元组(行)的任何属性的任何值。(要求中的主键， 其实可以拓展替换为候选键)。

1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只表达一个意思。

第二范式(2NF) 要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体， 新实体与元实体之间是一对多的关系。

举例

举例1

举例2

非主属性不完全依赖候选键

不满足后带来的问题

1. 数据冗余

如果一个球员可以参加m场比赛,那么球员的姓名和年龄就重复了m-1次。

一个比赛也可能会有n个球员参加，比赛的时间和地点就重复了n-1 次。

2. 插入异常

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

3. 删除异常

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

4. 更新异常

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

改进

举例3

违反了第二范式，因为有非主键属性仅依赖于候选键（或主键）的一部分。

例如，可以仅通过orderid找到订单的 orderdate，以及 customerid 和 companyname，而没有必要再去使用productid。

第三范式 3rd NF

确保数据表中的每一个非主键字段都和主键字段直接相关

也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段(即, 不能存在非主属性A依赖于非主属性B，非主属性 B依赖于主键C的情况，即存在“A→B→C”的决定关系)

通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

这里的主键可以拓展为候选键。

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

举例

举例1

球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于 球员编号，因此不符合 3NF 的要求

举例2

此时的Orders关系包含 orderid、orderdate、customerid 和 companyname 属性，主键定义为 orderid。 customerid 和companyname均依赖于主键--orderid。

小结

数据库设计至少要遵循前三个范式

(1)第一范式(1NF) ，确保每列保持原子性

数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项。

(2) 第二范式(2NF) ，确保每列都和主键完全依赖

尤其在复合主键的情况下，非主键部分不应该依赖于部分主键。

(3)第三范式(3NF) 确保每列都和主键列直接相关，而不是间接相关

范式的优点

数据的标准化有助于消除数据库中的数据冗余，第三范式(3NF)通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

范式的缺点

范式的使用，可能降低查询的效率。因为范式等极越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。

其他范式

巴斯科德范式 BCNF

BCNF被认为没有新的设计规范加入，只是对第三范式中设计规范要求更强，使得数据库冗余度更小。 所以，称为是修正的第三范式，或扩充的第三范式，BCNF不被称为第四范式。

若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式

在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系

第四范式 (4NF)

多值依赖

第四范式即在满足巴斯-科德范式(BCNF) 的基础上，消除非平凡且非函数依赖的多值依赖(即把同一表内的多 对多关系删除)。

第五范式(5NF, 又称完美范式）、域键范式

反范式化

业务优先的原则

有的时候不能简单按照规范要求设计数据表,因为有的数据看似冗余,其实对业务来说十分重要。首先满足业务需求,再尽量减少冗余。

如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MySQL的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化也是一种优化思路。此时，可以通过在数据表中增加冗余字段来提高数据库的读性能。

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要

2. 在数据规范化的同时 , 要综合考虑数据库的性能

3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间

4. 通过在给定的表中插入计算列，以方便查询

问题

存储 空间变大 了

一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致

若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源

在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂

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

增加冗余字段的建议

1)这个冗余字段不需要经常进行修改;

2)这个冗余字段查询的时候不可或缺

适用场景

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

在现实生活中，我们经常需要一些冗余信息， 比如订单中的收货人信息，包括姓名、电话和地址等。每次发生的 订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这些冗余信息是非常 有必要的。

数据仓库

数据仓库通常存储历史数据，对增删改的实时性要求不强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

数据库与数据仓库在使用上的区别

1.数据库设计的目的在于捕获数据，而数据仓库设计的目的在于分析数据

2.数据库对数据的增删改实时性要求强，需要存储在线的用户数据，而数据仓库存储的一般是历史数据

3.数据库课计需要尽量避免冗余，但为了提高查询效率也允许一定的冗余度，而数据仓库在设计.上更偏向采用 反范式设计。

事务

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

概念

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

原则

保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。

当在一个事务中执行多个操作时，要么所有的事务都被提交( commit)，那么这些修改就永久地保存下来

要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。

事务的ACID特性

原子性（atomicity）

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

一致性（consistency）

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

隔离性（isolation）

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

举例

持久性（durability）

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

通过事务日志来保证

日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

事务的状态

活动的（active）

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

部分提交的（partilly committed）

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

失败的（failed）

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

中止的（aborted）

如果事务执行了一部分而变为 失败的 状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态

换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了 中止的 状态

提交的（committed）

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

关系

事务的使用

显式事务

开启事务

begin

start transaction

+ read only

标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据

特例：只有当前事务操作该数据时，可以进行写操作

+ read write（默认）

标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据

+ with consistent snapshot

启动一致性读

一系列DML操作

事务结束的状态

提交的（COMMIT）

中止的（ROLLBACK）

回滚到事务开始前的状态

回滚到保存点（savepoint），再决定接下来的操作

隐式事务

开启

SET autocommit = on;(默认开启)

每一条语句后面都会自动commit

关闭

方式1

SET autocommit = FALSE/OFF;

针对于DML操作是有效的，对DDL操作是无效的。

方式2

我们在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据

隐式提交数据的情况

数据定义语言 DDL

数据库对象，指的就是数据库、表、视图、 存储过程 等结构。当我们使用CREATE、ALTER、 DROP 等语句 去修改数据库对象时，就会隐式的提交前边语句所属于的事务

隐式使用或修改mysql数据库中的表

当我们使用ALTER USER、‘CREATE USER I、DROP USER、 GRANT、 RENAME USER、 REVOKE 、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

事务控制或关于锁定的语句

① 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会 隐式的提交 上一个事务。

② 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会 隐式的提交 前边语句所属的事务。

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

加载数据的语句

使用LOAD DATA 语句来批量往数据库中导入数据时,也会隐式的提交前边语句所属的事务。

关于MySQL复制的一些语句

使用START SLAVE、STOP SLAVE、 RESET SLAVE、CHANGE MASTER TO 等语句时会隐式的提交前边语句所属的事务。

其它的一些语句

使用ANALYZE TABLE、 CACHE INDEX、 CHECK TABLE、 FLUSH、 L0AD INDEX INTO CACHE 、OPTIMIZE TABLE、REPAIR TABLE、RESET 等语句也会隐式的提交前边语句所属的事务。

事务的分类

扁平事务（Flat Transactions）

扁平事务中所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚

扁平事务是应用程序成为原子操作的基本组成模块。

扁平事务的主要限制是不能提交或者回滚事务的某一部分， 或分几个步骤提交。

带有保存点的扁平事务（Flat Transactions with Savepoints）

该类型事务除了支持扁平事务支持的操作外，还允许在事务执行过程中回滚到同一事务中较早的一个状态。这是

因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销太大。

保存点(Savepoint)

用来通知事务系统应该记住事务当前的状态，以便当之后发生错误时，事务能回到保存点当时的状态。

对于扁平的事务来说，隐式的设置了一个保存点，然而在整个事务中，只有这一个保存点， 因此，回滚只能会滚到事务开始时的状态。

链事务（Chained Transactions）

是指一个事务由多个子事务链式组成，它可以被视为保存点模式的一个变种。

带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，这意味着当进行恢复时，事务需要从开始处重新执行，而不能 从最近的一个保存点继续执行

链事务的思想

在提交一个事务时, 释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务

前一个子事务的提交操作和下一个子事务的开始操作合并成一个原子操作，这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行一样。

这样，在提交子事务时就可以释放不需要的数据对象，而不必等到整个事务完成后才释放。

图示

与带有保存点的扁平事务的不同之处

①带有保存点的扁平事务能回滚到任意正确的保存点，而链事务中的回滚仅限于当前事务,即只能恢复到最近的一个保存点。

②对于锁的处理，两者也不相同，链事务在执行COMMIT后即释放了当前所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。

嵌套事务（Nested Transactions）

是一个层次结构框架，可以看成是一棵树

由一个顶层事务(Top-Level Transaction)控制着各个层次的事务，

顶层事务之下嵌套的事务被称为子事务(Subtransaction) ，其控制着每一个局部的变换， 子事务本身也可以是嵌套事务。

分布式事务（Distributed Transactions）

通常是在一个分布式环境下运行的扁平事务

需要根据数据所在位置访问网络中不同节点的数据库资源。

例如，一个银行用户从招商银行的账户向工商银行的账户转账1000元，这里需要用到分布式事务，因为不能仅调用某一家银行的数据库就完成任务。

事务的隔离级别

场景

MySQL是一个客户端/服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端 与服务器连接上之后，就可以称为一个会话( Session)。

每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。

在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。

但是这样对性能影响太大，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，那就看二者如何权衡取舍了。

数据的并发问题

1. 脏写（ Dirty Write ）

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

示意图

2. 脏读（ Dirty Read ）

两个事务 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中的事务相当于读到了一个不存在的数据，这种现象就称之为 脏读 。

3. 不可重复读（ Non-Repeatable Read ）

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

示意图

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

4. 幻读（ Phantom ）

两个事务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中的四种隔离级别

问题的严重性排序

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

READ UNCOMMITTED

读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。

不能避免脏读、不可重复读、幻读

READ COMMITTED

读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）

可以避免脏读，但不可重复读、幻读问题仍然存在

REPEATABLE READ

可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容

可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

SERIALIZABLE

可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。

所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读

图示

隔离级别与并发性能之间的关系

MySQL中支持的四种隔离级别

查看隔离级别

MySQL 5.7.20的版本之前

SHOW VARIABLES LIKE 'tx_isolation';

MySQL 5.7.20的版本及之后

SHOW VARIABLES LIKE 'transaction_isolation';

不同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 关键字（在会话范围影响）

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

如果在事务之间执行，则对后续的事务有效

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

MySQL事务日志

事务的四种特性的实现

事务的隔离性由 锁机制 实现

REDO LOG 重做日志

提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性

存储引擎层(innodb)生成的日志，记录的是"物理级别"上的页修改操作，比如页号xxx、偏移量yyy 写入了2zz数据。主要为了保证数据的可靠性;

UNDO LOG 回滚日志

回滚行记录到某个特定版本，用来保证事务的原子性、一致性

是存储引擎层(innodb)生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句操作,那么undo log就记录一条与之相反的DELETE操作。

主要用于事务的回滚(undo log 记录的是每个修改操作的逆操作)和一致性非锁定读(undo log回滚行记录到某种特定的版本--VCC,即多版本并发控制。

redo日志

主要流程

InnoDB存储引擎是以页为单位来管理存储空间的。

在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool 之后才可以访问。

所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘( checkPoint机制) , 通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

为什么需要REDO日志

checkpoint 并不是每次变更的时候就触发，而是master线程隔一段时间去处理的， 最坏的情况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复

事务包含 持久性 的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩 溃，这个事务对数据库中所做的更改也不能丢失

采用技术

WAL技术( Write-Ahead Logging )

思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功

优点

redo日志降低了刷盘频率

redo日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

特点

redo日志是顺序写入磁盘的

在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的, 也就是使用顺序IO，效率比随机IO快。

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

redo log跟bin log的区别，rledo log是存储引擎层产生的，而bin log是数据库层产生的

假设一个事务， 对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录， 而bin log不会记录,直到这个事务提交,才会一次写入到bin log文件中。

redo的组成

重做日志的缓冲 (redo log buffer)

结构示意图

一个redo block占用512个字节

保存在内存中，是易失的

参数设置：innodb_log_buffer_size

默认 16M ，最大值是4096M，最小值为1M

mysql> show variables like '%innodb_log_buffer_size%'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | innodb_log_buffer_size | 16777216 | +------------------------+----------+

重做日志文件 (redo log file)

保存在硬盘中，是持久的

整体流程

图示

以更新事务为例

第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值

第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式

第4步：定期将内存中修改的数据刷新到磁盘中

redo log的刷盘策略

图示

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）

innodb_flush_log_at_trx_commit 参数

系统默认master thread每隔1s进行一次重做日志的同步

也就是说，一个没有提交事务的redo log记录,也可能会刷盘。因为在事务执行过程redo log记录是会写入 redo log buffer 中，这些redo log记录会被后台线程刷盘。

设置为0

表示每次事务提交时不进行刷盘操作

图示

master thread中每1秒进行一次重做日志的fsync操作

因此实例crash最多丢失1秒钟内的事务。(master thread是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性)

是一种折中的做法，它的IO效率理论是高于1的，低于2的，这种策略也有丢失数据的风险，也无法保证D

设置为1

表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）

图示

只要事务提交成功，redo log 记录就一定在硬盘里，不会有任何数据丢失

如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失

可以保证ACID的D，数据绝对不会丢失，但是效率最差的。

建议使用默认值，虽然操作系统宕机的概率理论小于数据库宕机的概率，但是一般既然使用了事务，那么数据的安全相对来说更重要些。

设置为2

表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步

图示

由os自己决定什么时候同步到磁盘文件

只要事务提交成功，redo log buffer 中的内容只写入文件系统缓存( page cache) 。

仅仅只是MySQL挂了不会有任何数据丢失

操作系统宕机可能会有1秒数据的丢失，这种情况下无法满足ACID中的D。

数值2肯定是效率最高的。

效率问题？

写入redo log buffer 过程

补充概念：Mini-Transaction

MySQL把对底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr。比如，向某个索引对 应的B+树中插入一条记录的过程就是一个 Mini-Transaction。一个所谓的mtr可以包含一组redo日志， 在进行 崩溃恢复时这一组 redo日志作为一个不可分割的整体。

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条redo日志

图示

每个mtr都会产生一组redo日志，mtr产生的日志情况

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

redo log block的结构图

磁盘的默认扇区大小时512字节

redo log file

相关参数设置

innodb_log_group_home_dir

指定 redo log 文件组所在的路径，默认值为 ./ ，表示在数据库的数据目录下

MySQL的默认数据目录（ var/lib/mysql ）下默认有两个名为 ib_logfile0 和ib_logfile1 的文件， log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。

此redo日志文件位置还可以修改

innodb_log_files_in_group

指明redo log file的个数，命名方式如：ib_logfile0，iblogfile1...iblogfilen。

默认2个，最大100个

innodb_flush_log_at_trx_commit

控制 redo log 刷新到磁盘的策略，默认为1。

innodb_log_file_size

单个 redo log 文件设置大小，默认值为 48M

最大值为512G，注意最大值指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大 于最大值512G

日志文件组

示意图

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

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

checkpoint机制

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

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

小结

undo日志

说明

是事务原子性的保证

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

数据改动层面的理解

插入了一条记录

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

对于每个INSERT, InnoDB存储引擎会完成一个DELETE

删除了一条记录

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

对于每个DELETE, InnoDB存储引擎会执行一个INSERT

修改了一条记录

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

对于每个UPDATE, InnoDB存储引擎会执行一个相反的UPDATE, 将修改前的行放回去

此外, undo log会产生redo log ，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护

作用

作用1：回滚数据

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

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

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

作用2：MVCC

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

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

存储结构

回滚段（rollback segment）与undo页

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

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

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

undo页的重用

回滚段与事务

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

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

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

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

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

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

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

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

回滚段中的数据分类

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

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

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

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

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

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

类型

insert undo log

指在inser操作中产生的undo log

inser操作的记录,只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)

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

update undo log

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

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

提交时放入undo log链表,等待purge线程进行最后的删除

生命周期

简要过程

例子

只有Buffer Pool的流程

有了Redo Log和Undo Log之后

详细过程

隐藏的列

图示

DB_ROW_ID

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

DB_TRX_ID

每个事务都会分配一个事务ID,当对某条记录发生变更时，就会将这个事务的事务ID写入trx_ id中。

DB_ROLL_PTR

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

举例

执行INSERT时

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

执行UPDATE时

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

更新的字段不是主键

UPDATE user SET id=2 WHERE id=1;

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

如何回滚的

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的数据删除

锁

概述

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

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

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

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

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

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

MySQL并发事务访问相同记录

读-读情况

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

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

写-写情况

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

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

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

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

图示

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

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

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

小结

不加锁

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

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

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

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

意思就是在内存中生成了对应的锁结构，不过锁结构的is. waiting属性为true,也就是事务需要等待，不可以继续执行操作。

读-写或写-读情况

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

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

解决方案

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

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

小结

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

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

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

分类

对数据操作的类型划分

读锁/共享锁

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

写锁/排他锁

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

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

兼容情况

特殊情况

1. 锁定读

对读操作添加X锁

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

2. 写操作

UPDATE

INSERT

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

从数据操作的粒度划分

锁的粒度

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

表级锁

概要

是MySQL中最基本的锁策略，并不依赖于存储引擎(不管你是MySQL的什么存储引擎,对于表锁的策略都是一样的)

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

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

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

表级别的S锁、X锁

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

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

两种模式

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

关系

② 意向锁 （intention lock）

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

1、意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。 2、意向锁是一种不与行级锁冲突表级锁这一点非常重要。 3、表明“某个事务正在某些行持有了锁或该事务准备去持有锁”

分类

意向共享锁（intention shared lock, IS）

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

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

意向排他锁（intention exclusive lock, IX）

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

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

特点

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

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

兼容关系

意向锁之间

意向锁和其他表级别锁

结论

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

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

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

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

并发性

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

③ 自增锁（AUTO-INC锁）

在使用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;

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

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

插入的三种模式

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

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

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

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

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

例如 INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。另一种类型的“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE 。

介绍

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

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

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

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

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

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

④ 元数据锁（MDL锁）

作用

保证读写的正确性

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

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

加 MDL读锁

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

加 MDL 写锁

互斥关系

读锁之间不互斥

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

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

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

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

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

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

行级锁

概述

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

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

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

优缺点

优点

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

缺点

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

① 记录锁（Record Locks）

例子

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

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

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

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

② 间隙锁（Gap Locks

图示

图中id值为8的记录加了gap锁，意味着 不允许别的事务在id值为8的记录前边的 间隙插入新记录 ，其实就是id列的值(3, 8)这个区间的新记录是不允许立即插入的。

幻读的解决方案之一

可能导致死锁

③ 临键锁（Next-Key Locks）

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

记录锁和间隙锁的合体

④ 插入意向锁（Insert Intention Locks）

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

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

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

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

图示

页级锁

概述

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

开销介于表锁和行锁之间

会出现死锁

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

锁的升级

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

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

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

从对待锁的态度划分

1. 悲观锁（Pessimistic Locking）

概述

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

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

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

举例

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

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

适用场景

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

应用场景

商品秒杀

2. 乐观锁（Optimistic Locking）

概述

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

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

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

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

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

实现机制

1.乐观锁的版本号机制

CSA（Compare and Set）

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

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

2.乐观锁的时间戳机制

适用场景

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

秒杀案例

版本号机制

时间戳机制

图示

按加锁的方式划分

1. 隐式锁

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

隐式锁的逻辑过程

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

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

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

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

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

2. 显式锁

其它

全局锁

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

整个库处于 只读状态

其他线会被阻塞

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

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

更新类事务的提交语句

应用场景

全库逻辑备份

命令

Flush tables with read lock

死锁

概念

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

举例

产生死锁的必要条件

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

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

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

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

如何处理

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

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

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

如何避免

锁的内存结构

图示

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

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

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

加锁的类型是一样的

等待状态是一样的

1. 锁所在的事务信息

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

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

2. 索引信息

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

3. 表锁／行锁信息

表锁

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

行锁

Space ID

记录所在表空间

Page Number

记录所在页号

n_bits

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

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

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

4. type_mode

图示

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

锁的模式（ lock_mode ）

占用低4位

锁的类型（ lock_type ）

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

行锁的具体类型（ rec_lock_type ）

使用其余的位来表示

is_waiting 属性呢？

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

5. 其他信息

6. 一堆比特位

锁监控

多版本并发控制（MVCC）

概述

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

隐藏字段

trx_id

roll_pointer

undo日志实现多版本

readview实现并发控制

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

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

快照读与当前读

概述

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

快照读

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

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

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

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

当前读

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

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

MySQL中的隔离级别

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

“三板斧”

隐藏字段

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

trx_id

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

roll_pointer

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

undo日志

形成版本链

readview

概述

对版本记录进行管理

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

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

哪些隔离级别中使用

READ COMMITTED

REPEATABLE READ

4个重要内容

1. creator_trx_id

创建这个 Read View 的事务 ID

2. trx_ids

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

3. up_limit_id

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

4. low_limit_id

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

图示

ReadView的规则

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

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

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

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

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

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

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

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

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

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

MVCC整体操作流程

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

2. 获取 ReadView；

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

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

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

举例 

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

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

READ COMMITED

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

REPEATABLE READ

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

总结

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

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

可以解决的问题

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

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

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

其他日志

日志类型

慢查询日志

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

通用查询日志

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

错误日志

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

二进制日志 binlog

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

中继日志 rely log

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

数据定义语句日志

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

弊端

降低MySQL数据库的性能

占用大量的磁盘空间

慢查询日志(slow query log)

通用查询日志(general query log)

用来 记录用户的所有操作

启动和关闭MySQL服务

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

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

默认关闭

查看当前状态

启动日志

方式1：永久性方式

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

方式2：临时性方式

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

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

查看日志

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

停止日志

方式1：永久性方式

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

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

方式2：临时性方式

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

SET GLOBAL general_log=off;

删除\刷新日志

手动删除文件

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

mysqladmin -uroot -p flush-logs

错误日志(error log)

概述

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

错误日志 无法被禁止

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

查看日志

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。

查询错误日志的存储路径

删除\刷新日志

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

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

命令

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

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

二进制日志(bin log)

概述

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

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

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

应用场景

数据恢复

如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作, 对数据库服务器文件做了哪些修改,然后根据二进制日志文件中的记录来恢复数据库服务器。

数据复制

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

图示

默认情况

在MySQL8中默认情况下，二进制文件是开启的

日志参数设置

方式1：永久性方式

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

重新启动MySQL服务

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

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

方式2：临时性方式

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递增。

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

恢复数据

命令

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

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

filename ：是日志文件名。

option ：可选项

--start-date、--stop-date  

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

--start-position、--stop-position

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

删除二进制日志

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

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

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

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

写入机制

概述

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

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

图示

write和fsync的时机参数设置

设置为0

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

设置为1

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

设置为N(N>1)

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

与redo log的对比

redo log 它是 物理日志

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

binlog 是 逻辑日志

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

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

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

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

两阶段提交

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

图解

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

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

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

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

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

中继日志(relay log)

概述

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

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

恢复的典型错误

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

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

主从复制

概述

如何提升数据库并发能力

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

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

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

其次才是采用 缓存的策略

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

作用

读写分离

图示

数据备份

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

高可用性

数据备份实际上是一种冗余的机制, 通过这种冗余的方式可以换取数据库的高可用性

也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上,保证服务的正常运行。

原理剖析

三个线程

图示

二进制日志转储线程 （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

binlog格式设置

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

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

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

同步数据一致性问题

要求

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

写数据必须写到写库；

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

主从延迟问题

概述

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

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

原因

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

2、从库的压力大

3、大事务的执行

如何减少主从延迟

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

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

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

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

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

解决一致性问题

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

方法 1：异步复制

方法 2：半同步复制

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

方法 3：组复制（MGR）

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