MySQL常见问题原理实现

1.MySQL的存储引擎

我们可以设置默认的存储引擎

SET DEFAULT_STORAGE_ENGINE=MyISAM;

不同的表设置不同的存储引擎

1.1常见的两大存储引擎

1 InnoDB 引擎：具备外键支持功能的事务存储引擎

1. MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。
2. InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
3. 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
4. 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。 
5. 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）
   1. 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
   2. 表名.ibd 存储数据和索引
6. InnoDB是为处理巨大数据量的最大性能设计 。
   1. 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了。
7. 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引。
8. MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高 ，而且内存大小对性能有决定性的影响。
9. Innodb支持行级锁

2 MyISAM 引擎：主要的非事务处理存储引擎

1. MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
2. 5.5之前默认的存储引擎
3. 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
4. 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
5. 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）
   1. 表名.frm 存储表结构
   2. 表名.MYD 存储数据 (MYData)
   3. 表名.MYI 存储索引 (MYIndex)
6. 应用场景：只读应用或者以读为主的业务

3 MyISAM和InnoDB 对比

2.索引的数据结构

2.1 索引的概念

索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。

优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主要的原因。 （2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。 （3）在实现数据的 参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。 （4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗。

缺点

增加索引也有许多不利的方面，主要表现在如下几个方面： （1）创建索引和维护索引要 耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。 （2）索引需要占 磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。 （3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。因此，选择使用索引时，需要综合考虑索引的优点和缺点。

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集 索引称为二级索引或者辅助索引。

1.聚簇索引

特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

1. 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
2. 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
3. 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。

2. B+树的 叶子节点 存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

1. 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2. 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
3. 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的io操作 。

缺点：

1. 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2. 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
3. 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

2. 二级索引（辅助索引、非聚簇索引）

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

2.2 不同的存储引擎的索引的实现方案

多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

1.Innodb

默认的索引是B+Tree

叶子节点存储的是所有的数据

2.MyISAM

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址 。 

3.MyISAM 与 InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

2.3 MySQL数据结构选择的合理性

1.Hash结构

Hash结构效率高，那为什么索引结构要设计成树型呢？

 

采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。

2.二叉搜索树

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。

为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量 降低树的高度 ，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。

3.B-Tree

而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。 B 树相比于平衡二叉树来说磁盘 I/O 操作要少 ，在数据查询中比平衡二叉树效率要高。所以 只要树的高度足够低，IO次数足够少，就可以提高查询性能 。

4.B+Tree

B+ 树和 B 树的差异：

1. 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数+1。

2. 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最

小）。

3. 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， 非

叶子节点既保存索引，也保存数据记录 。

4. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大

小从小到大顺序链接。

3.索引设计原则

3.1适合建索引的情况

1. 字段的数值有唯一性的限制

2. 频繁作为 WHERE 查询条件的字段

尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率

3. 经常 GROUP BY 和 ORDER BY 的列

可以避免file sort

4. UPDATE、DELETE 的 WHERE 条件列

5.DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时，创建索引注意事项

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在

student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

7. 使用列的类型小的创建索引

这是因为类型小，占用的空间更小，会使树更矮更胖。

8. 使用字符串前缀创建索引

也是为了节约空间，这里需要注意区分度

9. 区分度高(散列性高)的列适合作为索引

这样建立的索引才更有意义

10. 使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

3.2不适合建立索引的情况

1. 在where中使用不到的字段，不要设置索引

2. 数据量小的表最好不要使用索引

3. 有大量重复数据的列上不要建立索引

也就是区分度不高，建立索引并不能有很好效果，反而影响效率。

4. 避免对经常更新的表创建过多的索引

5. 不建议用无序的值作为索引

这里因为为了避免页分裂和页回收。

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引

4.性能工具的使用

4.1 数据库服务器的优化步骤

整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

统计SQL的查询成本：last_query_cost

定位执行慢的 SQL：慢查询日志

查看 SQL 执行成本：SHOW PROFILE

分析查询语句：EXPLAIN

type ☆

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，

index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

我们详细解释一下：

const

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

eq_ref

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。

ref

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

range

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

或者：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

index

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

小结：

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见上图中的蓝色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

key_len ☆

key_len的长度计算公式：

EXPLAIN不考虑各种Cache

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

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

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

5.索引优化与查询优化

5.1索引失效的情况

1.最佳左前缀法则

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2.主键插入顺序

如果我们没有顺序的插入，会有在一个满页中间插入一条数据的情况，会导致页分裂，会导致性能损耗，所以我们需要尽量避免这种情况的发生，最好让插入的记录的 主键值依次递增 。

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

4.类型转换导致索引失效

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

一般将范围条件放在最后，等值条件放在前面，最大利用索引

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

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

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

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

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

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

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

5.2关联查询优化

1.join语句原理

三种join方式

Index Nested-Loop Join

在这个流程里：

1. 对驱动表t1做了全表扫描，这个过程需要扫描100行；

2. 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；

3. 所以，整个执行流程，总扫描行数是200。

两个结论：

1. 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；

2. 如果使用join语句的话，需要让小表做驱动表。

Simple Nested-Loop Join

Block Nested-Loop Join

总结3：什么叫作“小表”？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

小结

1. 保证被驱动表的JOIN字段已经创建了索引
2. 需要JOIN 的字段，数据类型保持绝对一致。
3. LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
4. INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
5. 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
6. 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
7. 衍生表建不了索引

5.3子查询优化

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

子查询效率不高的原因：

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

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

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

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

总结：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

5.4排序优化

为什么在 ORDER BY 字段上还要加索引呢？

优化建议：

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

结论：

1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。

2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过

滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

filesort算法：双路排序和单路排序

优化策略

1. 尝试提高 sort_buffer_size

2. 尝试提高 max_length_for_sort_data

3. Order by 时select * 是一个大忌。最好只Query需要的字段。

5.5 GROUP BY优化

1. group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
2. group by 先排序再分组，遵照索引建的最佳左前缀法则
3. 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
4. where效率高于having，能写在where限定的条件就不要写在having中了减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
5. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

5.6 优先考虑覆盖索引

好处：

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

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

弊端：

索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

5.7 如何给字符串添加索引

前缀索引

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

前缀索引对覆盖索引的影响

结论：

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

5.8 索引下推

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优化方法。

6.数据库其它调优策略

6.1 数据库调优的措施

调优的目标

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

如何定位调优问题

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

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

调优的维度和步骤

第1步：选择适合的 DBMS

第2步：优化表设计

第3步：优化逻辑查询

第4步：优化物理查询

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

第5步：使用 Redis 或 Memcached 作为缓存

第6步：库级优化

1、读写分离

2、数据分片

但需要注意的是，分拆在提升数据库性能的同时，也会增加维护和使用成本。

6.2 优化MySQL服务器

优化服务器硬件

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

优化MySQL的参数

MySQL有许多的参数，我们可以根据自己的业务数据实际情况配置相应的参数，合适参数可以很好的提高效率。

6.3 优化数据库结构

1拆分表：冷热数据分离

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

2增加中间表

建立中间表适用于需要经常联表查询，将联表查询结果提前存在中间表里，就不需要每次联表，直接查询。

3增加冗余字段

其实也是为了避免联表，但是有许多的缺点

4 优化数据类型

情况1：对整数类型数据进行优化。

对于非负整数时，要用无符号整数，因为相同的空间，无符号的整数的范更大。

情况2：既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。

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

情况4：避免使用ENUM类型

情况5：使用TIMESTAMP存储时间

情况6：用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

总之，遇到数据量大的项目时，一定要在充分了解业务需求的前提下，合理优化数据类型，这样才能充分发挥资源的效率，使系统达到最优。

5 使用非空约束

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

小结

上述这些方法都是有利有弊的。比如：

1. 修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围；
2. 增加冗余字段的时候，不要忘了确保数据一致性；
3. 把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。

因此，你一定要结合实际的业务需求进行权衡。

6.4 大表优化

1 限定查询的范围

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

2 读/写分离

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

一主一从模式

双主双从模式

3 垂直拆分

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

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

4 水平拆分

下面补充一下数据库分片的两种常见方案：

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

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

7.MySQL事务日志

7.1 redo日志

1 为什么需要REDO日志

由于缓冲池是每隔一段时间才会去刷盘，但是如果遇到事务提交，但是还没有刷盘，此时宕机了，那这部分没有刷盘的数据就丢失了，为了保证事务的持久性，用redo日志来解决，它在事务提交的时候会在redo日志中简单的记录事务。

2 REDO日志的好处、特点

1. 好处

1. redo日志降低了刷盘频率
2. redo日志占用的空间非常小

2. 特点

1. redo日志是顺序写入磁盘的
2. 事务执行过程中，redo log不断记录

3 redo的组成

Redo log可以简单分为以下两个部分：

1. 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的。
2. 重做日志文件 (redo log file) ，保存在硬盘中，是持久的。

4 redo的整体流程

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

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

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

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

5 redo log的刷盘策略

Redo log的写入并不是直接写入磁盘中去，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以一定的频率 刷入到真正的redo log file 中。这里的频率就会更我们刷盘策略有关。

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日志文件组里写数据的话，会导致后写入的redo日志覆盖掉前边写的redo日志？当然！所以InnoDB的设计者提出了checkpoint的概念。

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

7.2 Undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log 。

1 Undo日志的作用

作用1：回滚数据

作用2：MVCC

2 undo的存储结构

InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了1024 个 undo log segment ，而在每个undo log segment段中进行 undo页 的申请。

1. 在 InnoDB1.1版本之前 （不包括1.1版本），只有一个rollback segment，因此支持同时在线的事务限制为 1024 。虽然对绝大多数的应用来说都已经够用。
2. 从1.1版本开始InnoDB支持最大 128个rollback segment ，故其支持同时在线的事务限制提高到了 128*1024 。

回滚段与事务

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

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

据会被复制到回滚段。

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

用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘

区或者在回滚段允许的情况下扩展新的盘区来使用。

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

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

1. 将undo log放入列表中，以供之后的purge操作
2. 判断undo log所在的页是否可以重用，若可以分配给下个事务使用

回滚段中的数据分类

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

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

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

undo的类型

在InnoDB存储引擎中，undo log分为：

insert undo log

update undo log

undo log的生命周期

8.锁

8.1不同的并发事务访问情况

1 读-读的情况

这个情况本身是不会对数据造成影响。

2 写-写的情况

这种情况可能会发生脏写。脏写是任何一个隔离级别都不允许发生的，所以多个事务并发对同一个数据进行修改时，需要他们顺序执行，就是通过锁来实现。锁其实就是内存中的一种结构

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

3 读-写或写-读情况

读-写 或 写-读 ，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读 、 不可重复读 、 幻读 的问题。

各个数据库厂商对 SQL标准 的支持都可能不一样。比如MySQL在 REPEATABLE READ 隔离级别上就已经解决了 幻读 问题。

4 并发问题的解决方案

怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢？其实有两种可选的解决方案：

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

普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。

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

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

小结对比发现：

1. 采用 MVCC 方式的话， 读-写 操作彼此并不冲突， 性能更高 。
2. 采用 加锁 方式的话， 读-写 操作彼此需要 排队执行 ，影响性能。

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

下，要求必须采用 加锁 的方式执行。下面就讲解下MySQL中不同类别的锁。

8.2锁的不同角度分类

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

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

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

① 表级别的S锁、X锁

一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。

② 意向锁 （intention lock）

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

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

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

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

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

2. InnoDB中的行锁

① 记录锁（Record Locks）

② 间隙锁（Gap Locks）

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

③ 临键锁（Next-Key Locks）

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

3. 页锁

页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁的数量是有限制的，当某个层级的锁的数量超过这层锁数量的阙值，就会进行锁升级，用更大粒度的锁来代替许多小粒度的锁。

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

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

1. 悲观锁

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

2. 乐观锁

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

1. 乐观锁的版本号机制

2. 乐观锁的时间戳机制

3. 两种锁的适用场景

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

1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁

问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。

2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层

面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。

9.多版本并发控制

9.1 什么是MVCC

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

9.2 快照读与当前读

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

1 快照读

不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读。之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

2 当前读

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

9.3 隔离级别与对应的问题解决方案

1 隐藏字段、Undo Log版本链

回顾一下undo日志的版本链，对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。

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

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

9.4 MVCC实现原理之ReadView

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

设计思路

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。

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

ReadView中主要包含4个比较重要的内容，分别如下：

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

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

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

3. up_limit_id ，活跃的事务中最小的事务 ID。

4. 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。

3 ReadView的规则

有了readview后，我们读取数据的规则：

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

1. 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
2. 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

4 MVCC整体操作流程

当查询一条记录的时候，系统如何通过MVCC找到它：

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

2. 获取 ReadView；

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

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

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

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次 Read View。

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

10.其他数据库日志

10.1 MySQL支持的日志

1 日志类型

这6类日志分别为：

1. 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
2. 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
3. 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
4. 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
5. 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
6. 数据定义语句日志：记录数据定义语句执行的元数据操作。

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

2 日志的弊端

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

10.2 通用查询日志

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

查看当前状态,是否开启日志

启动日志

方式1：永久性方式

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

如果不指定目录和文件名，通用查询日志将默认存储在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%'; 结果中可以看到通用查询日志的位置。

停止日志

方式1：永久性方式

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

方式2：临时性方式

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

SET GLOBAL general_log=off;

查询通用日志功能：

SHOW VARIABLES LIKE 'general_log%';

删除\刷新日志

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

手动删除文件

SHOW VARIABLES LIKE 'general_log%';

可以看出，通用查询日志的目录默认为 MySQL 数据目录。在该目录下手动删除通用查询日志

atguigu01.log 。

10.3 错误日志

在 MySQL 数据库中，错误日志功能是 默认开启 的。而且，错误日志 无法被禁止 。

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

10.4 二进制日志

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

binlog主要应用场景：

1. 一是用于 数据恢复
2. 二是用于 数据复制

使用日志恢复数据

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 之前恢复。

其它场景

二进制日志可以通过数据库的 全量备份 和二进制日志中保存的 增量信息 ，完成数据库的 无损失恢复 。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。

在这种情况下，一个有效的解决办法是 配置主从数据库服务器 ，甚至是 一主多从 的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。

10.5 再细谈二进制日志

1 写入机制

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

2 binlog与redolog对比

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

3 两阶段提交

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

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

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

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

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

10.6 中继日志(relay log)

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的 数据同步 。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。

恢复的典型错误

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

11.主从复制

11.1 如何提升数据库并发能力

对于一般的情况来说，我们遇到的都是“写少读多”的情况，那么这就导致我们数据库的读去数据压力很大，为了解决这个问题，我们可以考虑数据库集群，做主从架构，进行读写分离，这样可以提升我们数据库的一个并发处理能力。但是并不是所有的系统或业务都适合用主从架构，因为主从架构本身是需要成本的。

如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何 优化SQL和索引 ，这种方式简单有效；其次才是采用 缓存的策略 ，比如使用 Redis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用 主从架构 ，进行读写分离。

2 主从复制的作用

第1个作用：读写分离。

第2个作用就是数据备份。

第3个作用是具有高可用性。

11.2 主从复制的原理

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

1 原理剖析

实际上主从同步的原理就是基于 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 复制是异步的且串行化的，而且重启后从 接入点 开始复制。

复制的问题

复制的最大问题： 延时

11.3 同步数据一致性问题

主从同步的要求：

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

写数据必须写到写库；

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

11.3.1 理解主从延迟问题

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

11.3.2 主从延迟问题原因

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

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

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

2、从库的压力大

3、大事务的执行

举例1：一次性用delete语句删除太多数据

结论：后续再删除数据的时候，要控制每个事务删除的数据量，分成多次删除。

举例2：一次性用insert...select插入太多数据

举例:3：大表DDL

比如在主库对一张500W的表添加一个字段耗费了10分钟，那么从节点上也会耗费10分钟

11.3.3 如何减少主从延迟

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

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

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

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

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

11.3.4 如何解决一致性问题

读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间 数据复制方式 的问题，如果按照数据一致性 从弱到强 来进行划分，有以下 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 实现的。

注：本笔记是观看哔哩哔哩尚硅谷-康师傅视频整理的，如果想看更加详细的内容可以去b站搜索观看。