2.sql架构

2.1一条sql查询语句是如何执行的？

mysql> select from T where ID=10;

【拆解mysql的组件，来看基础架构】
大体来说，Mysql可以分为Server层和存储引擎层两部分。
Server层包括连接器、查询缓存、分析器、优化器、执行器等，函数了mysql的大多数核心服务功能，以及所有的内置函数（如时间、日期、数学、加密函数等），所有跨存储引擎的功能都在这一层实现。
【连接器】
连接器负责跟客户端建立TCP连接、获取权限、维持和管理连接。
mysql -h$ip -P$port -u$user -p
输完命令后，就需要在交互对象里输入密码。如果用户名或者密码不对，就会收到错误信息；如果用户名密码认证通过，连接器会到权限表里查出你拥有的权限。之后，这个链接里面的权限判断逻辑，都将依赖于此时读到的权限。如果客户端太长时间没动静，连接器就会自动断开，这个时间参数wait_timeout默认是8小时。
【查询缓存】
mysql拿到第一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以key-value对的形式，被直接缓存在内存中。如果你的查询能直接在这个缓存里找到key，那么这个value就会直接返回给客户端。
但是在大多数情况下建议不要使用查询缓存，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，因要有对一个表的额更新，这个表上所有查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率非常低，除非你的业务就是一张静态表。
【分析器】
（1）词法分析：如果没有命中查询缓存，就要开始真正执行语句了。分析器先会做”词法分析”，你输入的是由多个字符串和空格组成的一条sql语句，mysql需要识别出里面的字符串分别代表什么。比如把字符串”T”识别成”表名T”，把字符串”id”识别成”列id”。
（2）语法分析：语法分析根据语法的规则，判断输入的sql语句是否满足sql语法。如果语句不对就会受到”You have an error in your SQL syntax”的错误提醒。
【优化器】
经过了分析器，mysql就知道你要做什么了。在开始执行之前，还要先经过优化器的处理，优化器是在表里面有多个索引的时候，决定使用哪个索引；或者是一个语句有多表关联(join)的时候，决定各个表的连接顺序。
mysql> select from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
它既可以先从t1表取出c=10的记录的id值，再根据id关联到表t2，再判断t2里面的d值是否等于20；
也可以从表t2里面取出d=20的记录的id值，再根据id关联到t1，再判断t1里面的c值是否等于10；
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。
【执行器】
执行器开始执行之前，需要先判断你对表T是否有执行查询的权限，如果没有，就会返回权限的错误。如果查询命中缓存，会在查询缓存返回结果的时候做权限验证。如果有权限，就继续向下执行。
（1）调用InnoDB引擎接口取这个表的第一行，判断id是不是10，如果不是则跳过，如果是则将这行数据记录存在结果集中；
（2）调用引擎接口取”下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
（3）执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

2.2一条SQL更新语句是如何执行的？

mysql> update T set c=c+1 where ID=2;
分析器通过词法和语法解析这是一条更新语句，优化器决定要使用id这个索引。然后，执行器负责具体执行，找到这一行然后更新。
与查询流程不一样的是，更新流程还涉及到两个重要的日志模块。redo log(重做日志)和binlog(归档日志)。
【redo-log日志】
如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题，mysql就设计者使用了WAL(Write-Ahead Logging)技术，它的关键点就是先写日志，再写磁盘。
InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小都是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就回到开头循环写。

write position是当前记录的位置，一边写一遍后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write position和checkpoint之间的还空着的部分，可以用来记录新的操作。如果write position追上check point这时候就不能再执行新的更新，得擦除一些记录，把checkpoint推进一下。
有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。这个能力称为crash-safe。
【binlog】
与redolog日志的区别：
（1）redolog是InnoDB引擎特有的日志，而binlog是mysql的Server层也有自己的日志，所有的引擎都可以使用。
（2）redolog是物理日志，记录的是”在某个数据页上做了什么”，mysql的数据最终保存在数据页中，物理日志记录的是数据页的变更；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如”给ID=2这一行的c字段加1，简单理解的话记录的就是sql语句
（3）redolog是循环写的，空间固定会用完；binlog是可以追加写的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
【执行流程】：
（1）执行器先找引擎取ID=2这一行。id是主键，引擎直接用树搜索找到这一行。如果id=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
（2）执行器拿到引擎层给的行数据，这c这个值加1，比如原来是n，现在就是n+1，得到新的一行数据，再调用引擎接口写入这行新数据。
（3）引擎将这行新数据更新到内存中，同时将这个更新操作记录到redolog里面，此时redolog处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
（4）执行器生成这个操作的binlog，并把binlog写入磁盘。
（5）执行器调用引擎的提交事务接口，引擎把刚刚写入的redolog改成提交(commit)状态，更新完成。

【为什么是两阶段提交】？
假设相当字段c的值是0，再假如执行upd语句过程中在写完第一个日志后，第二个日志还没写完期间就发生了crash，会出现什么情况？
（1）先写redolog后写binlog。假如在redolog写完，binlog还没写完的时候，mysql进程异常重启。redolog写完以后，系统即使发生崩溃，仍然能够把数据恢复回来，所以恢复过来这一行c的值是1。但是由于binlog还没写完就crash了，这时候binlog里面就没有记录这个语句。因此，做备份日志的时候，存起来的binlog里面就没有这条语句。如果用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就少了这次更新，恢复出来的这一行c值就是0，与原库的值不同。
（2）先写binlog后写redolog。如果在binlog写完之后crash，由于redolog还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0.但是binlog里面已经记录了把c从0改成1的日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。
（3）说说崩溃恢复的判断规则？

1. 如果redo log处于prepare阶段，在写binlog之前发生了崩溃，由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。
2. 如果binlog写完，redo log还没commit之前crash，那崩溃恢复时会判断redo log里的事务是否是完整的，如果已经有了commit标识则直接提交；如果redo log里面的事务只有完整的prepare，则判断对应的binlog是否存在并完整，如果是则提交，否则回滚。

（4）redo log和binlog是怎么关联起来的？
它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会顺序扫描redo log。

1. 如果碰到既有prepare、又有commit的redo log，就直接提交；
2. 如果碰有只有prepare，而没有commit的redo log，就拿着xid去binlog找对应的事务

（5）只写biglog来支持崩溃恢复，又能支持归档，这样可以嘛？

1. 不可以，InnoDB不是Mysql的原生存储引擎，Mysql的原生存储引擎是MyIsam，设计之初就没有支持崩溃恢复的能力。binlog没有能力恢复数据页。

（6）只写redolog，不写binlog行不行？

1. 如果只是崩溃恢复的角度的话，redo log也是可以的，依然是crash-safe的。但是binlog有redolog无法替代的功能，一个是归纳。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。

(7)redo log buffer是什么？

1. 这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

（8）实际上数据库的 crash-safe 保证的是：

1. 如果客户端收到事务成功的消息，事务就一定持久化了；
2. 如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；
3. 如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

2.3介绍一下Mysql的主从同步机制？

主从同步的用途：
（1）读写分离；
（2）备份主库、避免数据丢失；
（3）当主库发生故障时，从库切换成主库，进行故障切换；
主从同步的条件：
（1）主库开启binlog日志；
（2）主从服务器的server_id不同；
（3）主从服务器之间能够连通；
主从同步的原理：M-S结构

Mysql主从复制中主要有三个线程：master(binlog_dump_thread)、slave(IO thread、SQL thread)的两条线程；
（1）主节点数据库所有的变更都会记录到binlog中；
（2）主节点binlog_dump线程，当binlog有变动时，线程读取其内容发送给从节点；
（3）从节点的IO线程接收binlog内容，并将其写入到relay log文件中；
（4）从节点的sql线程读取relay log(中转日志)文件内容对数据更新进行重放，最终保证主从数据库的一致性。
【注意】：主从节点使用binlog文件+position偏移量来定位主从同步的位置(增量同步)，从节点会保存自己已接收到的偏移量，如果从节点发生宕机重启，会自动从position的位置发起同步。
另外由于Mysql默认的复制方式是异步复制的，主节点把binlog日志发送给从库后不关心从库是否已经接收、处理，这样就会产生一个问题：假设主库挂了，从库处理是失败，这时主库升为从库后，日志就丢失了。
解决方案：
（1）全同步复制：主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但显然采用这种方式的话性能会受到严重影响。
（2）半同步复制：主库在执行完客户端提交的事务后，并不是立刻返回客户端响应，而是会等待至少一个从库接收并写入到realy log中，才会返回客户端。Mysql在等待确认时，默认会等待10s，如果超过10s没有收到ack，这时就会降级成异步复制。
这种半同步方式相比于异步复制，能够有效的提高数据安全性和主从一致性。他只能保证事务提交后的binlog至少传输到了一个从库，并不能保证整个从库都能应用到这个事务的binlog。另一方面，这种半同步方式也会造成一定程度的延迟，这个延迟时间至少是一个TCP/IP请求往返的时间，所以整个服务的性能是会有所下降的。而当从节点发生故障时，主节点需要等待的时间就会更长，要等待从节点的服务恢复或者请求超时才能给用户响应。
以上是M-S结构的执行原理，但实际生产中用的比较多的是双M的结构，就是说节点A与节点B互为主备关系，这样在切换的时候就不用再修改修改主备关系。所以双M结构还有循环复制的问题需要解决：业务逻辑在节点A上更新了一条数据，节点A的binlog_dump线程把binlog发送给节点B，节点B执行完这条更新数据后也会生成binlog，然后再发给节点A，这就是循环复制的问题。
解决方案：
（1）规定两个节点的server_id必须不同；
（2）一个备库接收到binlog并在重放的过程中，生成与原binlog的server id相同的binlog；
（3）每个库在收到自己的主库发过来的日志后，先判断server_id，如果跟自己的相同，就表示这个日志是自己生成的，就可以直接丢弃这个日志。