一看就懂系列之 Mysql主从延迟分析

前言

在进入正题之前，需要先明白一个东西，DML和DDL是什么东西？

DDL

数据定义语言，create 、alter 、drop、truncate、create、drop
当执行DDL语句时，在每一条语句前后，都将提交当前的事务。如果用户使用insert命令将记录插入到数据库后，执行了一条DDL语句(如create table)，此时来自insert命令的数据将被提交到数据库。当DDL语句执行完成时，DDL语句会被自动提交，不能回滚。

DML

数据操作语言，insert、update、delete，当执行DML命令如果没有提交，将不会被其他会话看到。除非在DML命令之后执行了DDL命令或DCL命令，或用户退出会话，或终止实例，此时系统会自动发出commit命令，使未提交的DML命令提交。

DCL

数据控制语言，commit、savepoint、rollback、set transaction。用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

mysql主从延迟原理

要说延时原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作,主库对所有DDL和DML产binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个 DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需 要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

mysql主从延迟怎么产生的

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

###如何解决

最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也 可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
mysql-5.6.3已经支持了多线程的主从复制。以表做多线程，不同的库可以使用不同的复制线程。

如果你觉得有收获～可以关注我的公众号【咖啡色的羊驼】～第一时间收到我的分享和知识梳理～