MySql系列整体栏目

---

内容	链接地址
【一】深入理解mysql索引本质	https://blog.csdn.net/zhenghuishengq/article/details/121027025
【二】深入理解mysql索引优化以及explain关键字	https://blog.csdn.net/zhenghuishengq/article/details/124552080
【三】深入理解mysql的索引分类，覆盖索引(失效)，回表，MRR	https://blog.csdn.net/zhenghuishengq/article/details/128273593
【四】深入理解mysql事务本质	https://blog.csdn.net/zhenghuishengq/article/details/127753772
【五】深入理解mvcc机制	https://blog.csdn.net/zhenghuishengq/article/details/127889365
【六】深入理解mysql的内核查询成本计算	https://blog.csdn.net/zhenghuishengq/article/details/128820477
【七】深入理解mysql性能优化以及解决慢查询问题	https://blog.csdn.net/zhenghuishengq/article/details/128854433
【八】深入理解innodb和buffer pool底层结构和原理	https://blog.csdn.net/zhenghuishengq/article/details/128993871
【九】深入理解mysql执行的底层机制	https://blog.csdn.net/zhenghuishengq/article/details/128100377
【十】深入理解mysql集群的高可用机制	https://blog.csdn.net/zhenghuishengq/article/details/126239652
【彩蛋篇】深入理解顺序io和随机io	https://blog.csdn.net/zhenghuishengq/article/details/129080088

---

一，一条sql语句的执行流程

1，连接器

客户端和服务端建立连接之后才能发送命令，基于tcp的webSocket实现，同时会对用户的权限与密码做一个校验。mysql里面有一个系统库，里面有一张系统表user表，然后会对输入的这个host，password进行一个校验，校验成功之后进行连接，mysql会开辟一个专属的会话空间，用于临时操作。建立连接之后默认长连接时间为8小时。

如果修改了user表里面的权限值之后，需要用户重启才能生效。可以查询全部连接这个mysql的客户端

#mysql登录
mysql -h localhost -u root -p
show processlist
#查看长连接默认时间
show global variables like "wait_timeout";

2，词法分析器

先会对这条sql进行一个语法分析，看语句是否合法，会对这条sql语句进行拆分。主要会有一些词法分析，语法分析，语义分析等。最后可以通过一个语法树，来分析具体的某一条sql语句，如select，delete，insert等。

3，优化器

在表里面存在多个索引的时候，回去判断走哪个索引。如在联表查询中，两张表的字段都有在name字段上面加上索引，那么这个优化器会决定先查a表还是先查b表，哪个效率高就会先查哪张表。

select * from a join b on a.id = b.id where a.name=xx and b.name=xxx;

4，执行器

会进行一个权限的认证，判断一下是否有操作这张表的权限，如果有权限的话，就会去遍历表里面的每一行，看是否符合这个条件查询，如果符合，就把这行加入到结果集里面。

5，bin-log归档

记录mysql的执行语句，如增删改查等，以及执行后的结果集影响。会有多个文件存储这些日志，通过轮询的方式将日志加入到这些文件里面，每条执行命令都会有一个position的位置来记录，因此可以通过这个bin-log来实现这个主从复制.

binlog特点
1、Binlog在MySQL的Server层实现（引擎共用）
2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
3、Binlog不限大小,追加写入,不会覆盖以前的日志

二，mysql环境的高可用架构

1，mysql的主从集群

1.1，实现原理

通过这个bin-log的日志实现，bin-log里面会记录这个mysql的增删改查操作。在主结点执行完那些命令之后，会将这些操作以及对应的影响行记录到这个bin-log的日志里面，从结点会通过一个IO线程去读取主结点中的bin-log日志，从结点会将这些命令同步到Relay-log里面，从结点再将这些命令再执行一次，这样就实现了这个主从复制。

同时也可以通过这个bin-log日志，和redis实现这些缓存和数据库的数据复制。

1.2，主从集群好处

数据安全
给主服务增加一个数据备份。基于这个目的，可以搭建主从架构，或者也可以基于主从架构搭建互主的架构。

读写分离
当主服务的访问压力过大时，可以将数据读请求转为由从服务来分担

故障转移
当主结点宕机之后，可以切换从结点作为主结点，继续提供数据的读写功能

1.3，主从复制集群

master主结点

修改这个 /etc/my.cnf 的配置文件，主要是打开这个binlog的配置文件，以及指定这个serverId。

#服务节点位移标识，集群中的每个结点都要有一个位移id
server-id=47 
#开启binlog日志记录，并指定文件名
log_bin=master-bin
#binlog日志文件
log_bin-index=master-bin.index

刷新这个配置

flush privileges;
#查看主结点同步状态
show master status;

file指的是当前binlog文件的文件名，position指的是binlog文件中的索引。
Binlog_Do_DB和Binlog_Ignore_DB这两个字段是表示需要记录binlog文件的库以及不需要记录binlog文件的库。

#只会去同步这个数据库里面的命令
binlog-do-db = masterDb
#不会去同步哪个数据库的bin-log日志
binlog-ignore-db = masterSecondDb

开启这个binlog后，数据库中的所有操作都会被记录到datadir中，以一组轮询文件的方式循环记录。在后面配置这个从服务器时，就需要通过这个file和position通知从服务从哪个地方开始记录这个bin-log。

slave从结点

修改这个 /etc/my.cnf 的配置文件，需要增加一个relay-log的日志

#主库和从库需要不一致,id需要唯一
server-id=48
#打开MySQL中继日志 
relay-log-index=slave-relay-bin.index 
relay-log=slave-relay-bin
#打开从服务二进制日志 
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1

启动mysql的服务，并设置同步主节点的状态

#设置的同步结点
CHANGE MASTER TO 
MASTER_HOST='192.168.11.12', 
MASTER_PORT=3306, 
MASTER_USER='root', 
MASTER_PASSWORD='root', 
MASTER_LOG_FILE='master-bin.000004', 
MASTER_LOG_POS=156 GET_MASTER_PUBLIC_KEY=1;

#开启同步结点
start slave;
#查看主从同步状态 
show slave status;

change master指令需要指定这个主结点的master_log_file和master_log_pos，即master的日志的存储文件地址和position位置。如果这两个的数据和主结点的数据一致，那么就表示这个主从同步搭建是成功的。

1.4，读写分离

由于主从复制是单向的，因此通过这个主库来实现这个写，从库来实现这个读。如果需要限制用户写数据，可以在从服务中将read_only参数的值设为1( set global read_only=1; )，这样就可以限制用户写入数据。

1.5，GTID

GTID的本质也是基于Binlog来实现主从同步，只是他会基于一个全局的事务ID来标识同步进度。GTID就是全局事务id，全局唯一并且趋势递增，他可以保证为每一个在主结点上提交的事务在备份集群中可以生成一个唯一的id。GTID可以保证同一个事务只在指定的从库上执行一次，这样可以避免偏移量造成的数据不一致的问题。从服务器会告诉主服务器已经执行完了哪些事务的GTID的值，主库会把这个没有执行的事务发送到这个从库里面。
主结点需要添加的配置

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on server_id=单独设置一个
binlog_format=row

从结点需要添加的配置

gtid_mode=on
enforce_gtid_consistency=on 
log_slave_updates=1 
server_id=单独设置一个

1.6, 集群扩容

如果需要增加一个从结点，那么只需要增加一个bin_log的日志文件即可，但是bin_log文件只能记录开启之后的操作命令记录，在此之前的的记录不能被同步。因此可以通过一个mysql的bin目录下面的mysqldump根据来实现。

mysqldump -u root -p --all-databases > datas.sql

通过这个命令就可以将数据库所有的数据导入到这个datas.sql表，然后会把这个datas.sql发布到新的mysql的服务器上，输入一下指令将值导入到新的mysql的服务中。这样子新的服务器里面的数据库就会有之前的历史数据了。

mysql -u root -p < datas.sql

2，mysql的半同步复制

2.1，异步复制

MySQL主从集群默认采用的是一种异步复制的机制。主服务在执行用户提交的事务后，写入binlog日志，然后就给客户端返回一个成功的响应了。而binlog会由一个dump线程异步发送给Slave从服务

这里就会有一个问题，由于发送这个bin-log到这个从节点采用这种异步的方式，那么这个主结点就不知道这个从结点是否接同步到了这个数据，如果主服务器出现宕机的情况下，从结点就可能没有来得及同步这个数据，就会造成这个数据丢失的情况。

2.2，MySQL的半同步复制

为了解决上面数据丢失的问题，因此将异步换成了同步的方式来执行，但是为了防止从结点出现宕机，造成整个链路出现阻塞的问题，因此将整个链路变成为半同步复制。就是说在数据同步的过程加了一个超时的机制，默认为10s，如果这个主节点在10s内没有接收到这个从结点的ack的确认，那么就会将该条数据降级为异步复制。至少有一个从结点接收到数据并且将数据加入到relay-log的日志里面，才会给这个主结点返回一个ack的确认机制。

虽然有效的提高了数据的安全性，但是也会造成一定程度上的延迟，延迟时间至少是一个TCP/IP请求的往返时间。

2.3，主从数据延迟问题

一般都是因为主服务数据都是多线程并发写入的，而从服务器是通过单线程dump线程来拉取数据的，因此这个组件就会有一定的效率差。
1，从节点多配置几个
2，dump线程是单线程，因此也可以将dump单线程配置成这个多线程来实现这种效率问题

三，mysql的高可用方案

1，MMM

MMM：Master-Master replication managerfor Mysql，Mysql主主复制管理器
可以对mysql集群进行监控和故障迁移，他需要两个Master，同一时间只有一个Master对外提供服务，可以说是主备模式，他是通过一个VIP(虚拟IP)的机制来保证集群的高可用。
会有一个监控端去监控这两个主结点，只监控主结点，如果其中一个主结点挂了之后，就会进行主节点的切换，通过vip的一个飘移来实现节点的切换问题。

使用场景
1，读写都需要高可用的
2，基于日志点的复制方式

2，MHA(用的最多)

MHA：Master High Availability Manager and Tools for MySQL，主结点的高可用。

由manager和node节点组成，其中manager节点需要单独部署一台机器，node节点一般部署在mysql的机器上。MHA实现了这个从结点到主结点的切换，并且如果主结点发生了宕机问题，MHA可以在30s之内实现这个故障切换，并且可以在切换的过程中，可以早最大的程度上保证这个数据的一致性。但是转移的脚本需要自己开发。

3，MGR

MGR：MySQL Group Replication：组复制。
多个节点共同组成一个复制组，一个事务提交后，必须经过超过半数节点的决议并通过后，才可以提交。主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。
使用场景
1，对主从延迟比较敏感
2，希望对对写服务提供高可用，又不想安装第三方软件
3，数据强一致的场景