[大数据]MySQL中表的主从复制与拆分

on和where的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户

在使用left join时，on和where条件的区别如下：
- on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表的记录，右边条件满足的就展示数据，不满足的展示空。
- where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（不是必须返回左边表的记录了），条件不为真	   的就全部过滤掉。

下面我们来举个例子看一下：

我们有两张表，一张是学生表，表如下：

另一张表是课程表，表如下：

下面我们分别分析一下这两条sql查询语句：

select * from student s left join class c on (s.classid = c.id) where c.name = '三年级三班' order by s.id;

select * from student s left join class c on (s.classid = c.id and c.name = '三年级三班') order by s.id;

• 第一条sql语句：

  首先利用s.classid = c.id初步过滤后中间表如下：

之后再利用where c.name = '三年级三班’进行过滤，只保留满足条件的那行记录，结果如下：

• 第二条sql语句：

  过滤条件s.classid = c.id and c.name = ‘三年级三班’，左表的记录全部显示，右表满足条件的数据显示，不满足条件的显示空

inner on 是内连接，就和我们平常使用where进行多表查询时一样
left join ···  on 是左外连接			左边表记录一定全部显示，右边满足条件的显示数据，不满足的显示空
right join ··· on 是右外连接			右边表记录一定全部显示，左边满足条件的显示数据，不满足的显示空
full join ···· on 是全连接，是左外连接和右外连接的并集，只要左表和右表有一个条件满足就可以了。

详细内容可以点击这里进行学习啊

一图看懂join、left join、right join、fulljoin间的区别

关于锁的一些补充

在InnoDB引擎中，默认使用行锁，且行锁一定是添加在索引上的，也就是说如果不使用索引进行查询，就不会使用行锁，而会退化使用表锁。
其中行锁有三种类型：
- 记录锁（Record Lock）
- 间隙锁（Gap Lock）
- 临键锁 （Next-Key Lock）

记录锁（Record Lock）是为某行记录加锁：
-- id列为主键列或唯一索引列
SELECT * FROM 表名称 WHERE id = 1 FOR UPDATE;
需要注意的是：id 必须为主键列或唯一索引列才会使用记录锁进行锁住，否则使用其他索引就会使用临键锁进行锁住。且只能在索引上加行锁，否则会退化为表锁。
-- 同时查询语句也必须为精确匹配=，不能为>、<、like等，否则会使用临键锁进行锁住。


在通过主键索引或唯一索引进行update操作时，也会对该行记录加上记录锁：
-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;


间隙锁（Gap Lock）是为一个间隙加上锁：
间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的一条记录。
SELECT * FROM 表名称 WHERE id BETWEN 1 AND 10 FOR UPDATE;
所有id在(1, 10)的记录都会被锁住，但是1和10两条记录行并不会被锁住。间隙锁区间是左开右开。


临键锁（Next-Key Lock）是一种特殊的间隙锁，会锁住一段左开右闭的区间，
临键锁基于费唯一索引列，会锁住一段左开右闭的区间。

下面是一个关于临键锁的例子：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 24;
-- 或根据非唯一索引列 锁住某条记录
SELECT * FROM table WHERE age = 24 FOR UPDATE;

临键锁会锁住前面和后面之间的

不管执行哪一条语句，锁住的区间都是(10, 32);

# 额外补充：MySQl如何实现悲观锁和乐观锁？
- 乐观锁：更新时带上版本号
- 悲观锁：使用共享锁和排它锁（lock in share mode/for update）

关于MVCC

关于MVCC机制，以前认为是每个字段保存了两个隐藏列，分别是创建版本号和删除版本号，
每次查询时访问创建版本号小于（说明在当前事务之前创建）当前事务版本号且删除版本号
大于当前的事务的版本号（说明在当前事务之后删除）或删除版本号为空（还未删除）的记录
但是最近看了一些文章，发现这样的理解可能是错的，比如两个A、B两个事务先后开启，但是
A、B事务还没提交，这个时候B事务应该是看不到A事务未提交的记录的，但是按照上面的理解
B事务是可以看到A事务提交的记录的，于是学习了其他大佬的文章：
参考文章：
https://blog.csdn.net/cy973071263/article/details/104490345
https://joonwhee.blog.csdn.net/article/details/106893197
下面我们来介绍一下：

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非空唯一键时都不会包含row_id列）：

• trx_id:每次对某条事务进行改动时，都会把对应的事务id赋值给trx_id隐藏列
• rolll_pointer:每次对某条记录进行改动时，这个隐藏列都会存一个指针，可以通过该指针找到该记录修改前的信息。

当某条记录被多次修改时，该行记录会存在多个版本，通过roll_pointer链接形成一个类似版本连的概念：

版本链是从上到下依次链接的，每一层就代表着一个事务对某一行进行的操作。最上面是最新的事务版本，最下面是最老的事务版本。

每一层的最右边是一个指针（roll_pointer），指向的是当前事务修改前的记录。这个指针也可以用来回滚事务。

每一层右边第二个是事务id(trx_id)是事务的唯一标识,表示生成链中某行记录的事务id.版本链中的事务id是递增的,id越大,代表事务越新.

每一层剩下的就是真实的行数据（也有可能包含数据库自己生成的row_id）

ReadView

对于RU(读未提交的隔离级别)的事务来说直接读取记录的最新版本就好了，对于使用Serializable(可串行化隔离级别)的事务来说，使用加锁的方式来访问记录。对于使用RC(读已提交)和RR（可重复读）隔离级别的事务来说，就需要用到我们上面所说的版本链了。核心问题就是：需要判断一下版本链中的哪个版本对于当前事务来说是可见的。为了实现这个功能，就引入了事务快照ReadView。

ReadView中主要包含4个比较重要的内容：

1. m_ids:表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。可以理解为里面存的是还没有提交的事务id
2. min_trx_id:表示在生成ReadView时当前系统中活跃的最小的事务id，是m_ids中的最小值。可以理解为当前活跃的事务中最老的事务。
3. max_trx_id:表示在生成ReadView时系统应分配给下一个事务的id值。
4. creator_trx_id:表示生成该ReadView的事务的事务id

ReadView会在事务进行select的时候被创建，每一个事务都会有一个自己的ReadView，但是增删改操作不会使用ReadView，只有查询操作才会使用。

注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比如说现在有1,2,3三个事务，都作出了修改但是都未提交，之后id为3的事务
提交了，那么一个新的事务在生成ReadView时，m_ids就包括1,2，min_trx_id就是1，max_trx_id的值就是4

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

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

RC（读已提交隔离级别）的实现方式：

每次读取数据前都生成一个ReadView，然后查询版本链中的事务id，从上面的事务开始往下找，找到第一个事务id不在ReadView的m_ids中的，读取版本链中该事务的真实行记录。即读最新的已提交事务的行数据

RR（可重复读隔离级别）的实现方式：

在第一次读取数据时生成一个ReadView，然后后面的所有读操作都是用第一次生成的这个ReadView。这样只会读取第一次读数据时最新的已提交数据，就算是后来那些存在ReadView的m_ids的事务提交了，已经从m_ids中去除了，但是后面的查询操作还是会使用最开始的ReadView，认为他们还没有提交，这也就实现了可重复读。

MVCC总结

MVCC指的就是在使用RC和RR这两种隔离级别的事务在执行普通的select操作时（其他的修改操作不会使用MVCC）访问记录的版本链的过程。可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。RC和RR这两个隔离级别的一个很大的不同就是：生ReadView的时机不同。RC在每一次执行普通的select操作前都会生成一个ReadView，而RR只在第一次进行普通select操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

关于执行增删查改时锁的知识

delete:删除一条数据时，先对记录加锁，再执行删除操作。
insert:插入一条数据时，会先加隐式锁来保护这条本事务插入的数据在提交之前不被其他事务访问到，
update：
	如果被更新的列，修改前后没有导致存储空间发生变化，那么会先给记录加x锁，再直接对记录进行修改。
	如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加x锁，然后将记录删掉，再insert一条新记录。

关于幻读的解决

SQL标准中规定的RR并不能消除幻读，但是MySQL的RR可以，靠的就是Gap锁。在RR级别中，Gap锁是默认开启的，而在RC级别下，Gap锁是关闭的。

MySQL表的拆分

如何做慢SQL优化？

首先要明白慢的原因是什么：是查询条件没有命中索引？还是加载了不需要的列？还是数据量太大？所以优化也是针对这三个方向来的：

• 首先用explain分析语句的执行计划，查看使用索引的情况，是不是查询没有走索引，如果可以加索引解决，优先采用加索引解决。
• 分析语句，看看是否存在一些导致索引失效的用法，是否加载了额外的数据，是否加载了许多结果中不需要的列，对语句进行分析以及重写。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行垂直拆分或者水平拆分。

水平分表

例如：QQ的登录表

• 假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候都要从这100亿中寻找，会很慢。如果将这些表分成100份，每张表有1亿条，就小了很多，例如qq0，qq1，····qq99表（后面的数字是每个qq可以根据qq号%100存储到哪个表中）。

垂直分表

• 垂直分割指的是：表的记录不多，但是字段却很长，表占用空间很大，检索表的时候需要检索大量的IO，严重降低了性能，这时需要把大的字段拆分到另一个表中，并且该表与原表是一对一的关系。

例如学生答题表student，有5个字段：

id，name，分数，题目，回答，其中题目和回答是比较小的字段，id，name，分数是比较小的字段。

假如我们只想查询id为8的学生的分数，select 分数 from student where id = 8

• 虽然只是查询分数，但是题目和回答这两个大字段也是要扫描的，很消耗性能，然而我们只是要分数，并不想要查询题目和回答，这个时候我们就可以使用垂直拆分。
• 我们可以把题目单独放到一个表中，通过id与题目表建立一一对应的关系，同样将回答单独放在一个回答表里，这样我们利用上面的语句查询时就不会扫描题目和回答这两个大字段了。

小结：

• 水平分割是表中的数据量过大，严重影响查询效率时，将1张大的表分割成N张表明不同但是字段和数据类型相同的表
• 垂直分割是表中的记录不多，但是字段很长时，表占用的空间很大，将大的字段拆分到另一张表中，并且该表与原表是一一对应的关系。

MySQL主从复制

主从复制介绍

MySQL主从复制涉及到三个线程，一个运行在主节点（Log Dump Thread），其余两个（I/O Thread，SQL Thread）运行在从节点，如下图所示：

主从复制默认是异步的方式，具体过程如下：

• 从节点上的I/O线程连接主节点，请求读取主库的二进制文件（bin log日志）的指定位置（bin log position）之后的日志内容。

• 主节点线程接收到来自从节点I/O线程的请求后，读取主节点中的二进制日志文件（bin log日志）的指定位置之后的日志信息，返回给从节点。

  • 返回给从节点的内容信息除了日志所包含的信息之外，还包括本次返回的信息的bin-log-file（二进制日志文件）以及bin-log position（读取的位置）

• 从节点的I/O线程接收到内容后，将接收到的日志内容更新到relay log（中继日志）中，并将读取到的bin log file（文件名）和position（位置）保存到master-info文件中，以便在下一次读取的时候能够清晰的告诉Master“我需要从某个bin-log-file的哪个位置开始往后的内容”

• 从节点的SQL线程检测到relay-log中新增加的内容后，会解析relay-log的内容，并在本数据库中执行。

异步复制，主库宕机后，数据可能丢失

可以使用半同步复制或全同步复制

• 半同步复制

  修改语句写入bin log后，不会立即给客户端返回结果。而是首先通过log dump线程将bin log发送给从节点，从节点的I/O线程收到bin log后，写入到relay log，然后返回ACK给主节点，主节点收到ACK后，再返回给客户端成功

半同步复制的特点：

1. 确保事务提交后bin log至少传输到一个从库，不保证从库利用完这个事务的bin log。
2. 性能有一定的降低，响应时间会更长。
3. 网络异常或从库宕机，卡主库，直到超时或从库恢复。

• 全同步复制

  主节点和所有从节点全部执行了该事务并确认才会给客户端返回成功。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会受到严重的影响。

# 主库写压力大，从库复制很可能出现延迟

- 可以使用并行复制（并行是指从库多个SQL线程并行执行relay log），解决从库复制延迟的问题。
- MySQL5.7中引入基于组提交的并行复制，其核心思想：一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，
  则说明事务之间没有任何冲突（否则就不可能提交）。
- 判断事务是否处于一个组是通过last_committed变量，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的
  last_committed，则表示这些事务都在一组内，可以进行并行的回放。

文章学习与：
https://joonwhee.blog.csdn.net/article/details/106893197
https://csp1999.blog.csdn.net/article/details/116088206