CSDN下载自视觉中国作者
sowhat责编
XXX话不多说，直接上干货。SQL语句执行流程MySQL大体上可分为Server层和存储引擎层两部分。Server层：连接器：TCP握手后服务器来验证登陆用户身份，A用户创建连接后，管理员对A用户权限修改了也不会影响到已经创建的链接权限，必须重新登录。查询缓存：查询后的结果存储位置，MySQL8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。分析器：根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。优化器：多种执行策略可实现目标，系统自动选择最优进行执行。执行器：判断是否有权限，将最终任务提交到存储引擎。存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL5.5.5版本开始成为了默认存储引擎(经常用的也是这个)。SQL执行顺序BinLog、RedoLog、UndoLog2.1BinLogBinLog是记录所有数据库表结构变更（例如create、altertable）以及表数据修改（insert、update、delete）的二进制日志，主从数据库同步用到的都是BinLog文件。BinLog日志文件有三种模式。STATEMENT模式内容：binlog只会记录可能引起数据变更的sql语句优势：该模式下，因为没有记录实际的数据，所以日志量和IO都消耗很低，性能是最优的劣势：但有些操作并不是确定的，比如uuid()函数会随机产生唯一标识，当依赖binlog回放时，该操作生成的数据与原数据必然是不同的，此时可能造成无法预料的后果。ROW模式内容：在该模式下，binlog会记录每次操作的源数据与修改后的目标数据，StreamSets就要求该模式。优势：可以绝对精准的还原，从而保证了数据的安全与可靠，并且复制和数据恢复过程可以是并发进行的劣势：缺点在于binlog体积会非常大，同时，对于修改记录多、字段长度大的操作来说，记录时性能消耗会很严重。阅读的时候也需要特殊指令来进行读取数据。MIXED模式内容：是对上述STATEMENT跟ROW两种模式的混合使用。细节：对于绝大部分操作，都使用STATEMENT来进行binlog的记录，只有以下操作使用ROW来实现：表的存储引擎为NDB，使用了uuid()等不确定函数，使用了insertdelay语句，使用了临时表。主从同步流程：主节点必须启用二进制日志，记录任何修改了数据库数据的事件。从节点开启一个线程（I/OThread)把自己扮演成mysql的客户端，通过mysql协议，请求主节点的二进制日志文件中的事件。主节点启动一个线程（dumpThread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。从节点接收到主节点发送过来的数据把它放置到中继日志（Relaylog）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。从节点启动另外一个线程（sqlThread），把Relaylog中的事件读取出来，并在本地再执行一次。mysql默认的复制方式是异步的，并且复制的时候是有并行复制能力的。主库把日志发送给从库后不管了，这样会产生一个问题就是：假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念：全同步复制和半同步复制。全同步复制主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。半同步复制半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。还可以延伸到由于主从配置不一样、主库大事务、从库压力过大、网络震荡等造成主备延迟，如何避免这个问题？主备切换的时候用可靠性优先原则还是可用性优先原则？如何判断主库Crash了？互为主备情况下如何避免主备循环复制？被删库跑路了如何正确恢复？(⊙o⊙)…感觉越来越扯到DBA的活儿上去了。2.2RedoLog可以先通过下面demo理解：饭点记账可以把账单写在账本上也可以写在粉板上。有人赊账或者还账的话，一般有两种做法：直接把账本翻出来，把这次赊的账加上去或者扣除掉。先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。生意忙时选后者，因为前者太麻烦了。得在密密麻麻的记录中找到这个人的赊账总额信息，找到之后再拿出算盘计算，最后再将结果写回到账本上。同样，在MySQL中如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。而粉板和账本配合的整个过程就是MySQL用到的是Write-AheadLogging技术，它的关键点就是先写日志，再写磁盘。此时账本=BinLog，粉板=RedoLog。1、记录更新时，InnoDB引擎就会先把记录写到RedoLog（粉板）里面，并更新内存。同时，InnoDB引擎会在空闲时将这个操作记录更新到磁盘里面。2、如果更新太多RedoLog处理不了的时候，需先将RedoLog部分数据写到磁盘，然后擦除RedoLog部分数据。RedoLog类似转盘。RedoLog有writepos跟checkpointwritepos：是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。writepos和checkpoint之间的是粉板上还空着的部分，可以用来记录新的操作。如果writepos追上checkpoint，表示粉板满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。有了redolog，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。redolog两阶段提交：为了让binlog跟redolog两份日志之间的逻辑一致。提交流程大致如下：prepare阶段--写binlog--