MySQL数据存储及查询流程假设现在我们建了如下表CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'学号',`name`varchar(10)NOTNULLCOMMENT'学号',`age`int(11)NOTNULLCOMMENT'学生年龄',PRIMARYKEY(`id`),KEY`idx_name`(`name`))ENGINE=InnoDB;插入如下sqlinsertintostudent(`name`,`age`)value('a',10);insertintostudent(`name`,`age`)value('c',12);insertintostudent(`name`,`age`)value('b',9);insertintostudent('name','age')value('d',15);insertintostudent('name','age')value('h',17);insertintostudent(`名字`,`年龄`)值('l',13);inserttostudent(`name`,`age`)value('k',12);insertintostudent(`name`,`age`)value('x',9);数据如下这些数据最终会被持久化到文件中，那么数据在文件中是如何组织的呢？它是逐行附加到文件中的吗？其实不是，“数据其实是存储在页中的，一个页的大小是16k，一个表由很多页组成，这些页组成了一颗B+树”。最终的组织形式如下。具体的构建过程我就不详细介绍了。可以看看我之前的文章《10张图，搞懂索引为什么会失效?》那么SQL语句是怎么执行的呢？MySQL的逻辑架构图如下所示，详细结构为“当我们要更新一条数据时，是否应该从磁盘加载这条数据，更新后持久化到磁盘？”如果是这样，那条sql的执行过程可是太慢了，因为对一个大的磁盘文件的读写操作需要几百万毫秒。真正的执行过程是，当我们要更新或读取一段数据时，相应的page会被加载到内存中的BufferPool缓冲池中（默认是128m，当然是为了提高并发度）系统，你可以把这个值设置大一点）之所以把页面加载到BufferPool中，是因为当你使用这个页面的数据时，使用这个页面其他数据的概率很大，随机IO需要很长时间时间。因此，将更多数据加载到缓冲池中。更新数据时，如果对应的页面在BufferPool中，直接更新BufferPool中的页面即可。当相应的页面不在BufferPool中时，会从磁盘中加载相应的页面。PagetoBufferPool，然后update，“此时BufferPool中的页面和磁盘中的页面数据不一致，称为脏页。”这些脏页将被刷新回磁盘。“这些脏页会多久刷新回磁盘一次？”有以下几种情况，当BufferPool不足以为新加载的页面腾出空间时，会使用改进的LRU算法将一些脏页刷新回磁盘后台线程当MySQL不忙时，会刷新脏页当redolog写满时写入磁盘（后面会讲到redolog的作用），当数据库关闭时，所有脏页都会刷新回磁盘，效率是不是高了很多？当要更新数据的页已经是在BufferPool中，只需要对内存进行操作，效率不是一般的高。”看到这里你可能会有疑问？如果对应的脏页还没有被刷入磁盘，数据库宕机了，那么我们的更改会丢失吗？”解决这个问题，就不得不提到rodolog了。既然要说rodolog，那我们就来说说mysql中的三种日志：undolog,rodolog,binlogundolog：如何让更新后的数据回滚?以上面的student表为例，当我们想把id=1的名字从a改成abc时，原来的值id=1,name=a会被写入undolog，当这个update语句在一个事务中执行时，当事务回滚时，可以通过undolog将数据恢复到原来的样子。另外，undolog在mvcc的实现中也起着重要的作用，看我之前的文章《面试官：MVCC是如何实现的?》rodolog：系统宕机，如何避免数据丢失？那么我们上面的问题，如果对应的脏页还没有的话刷新到磁盘，数据库会宕机，所以我们的修改会丢失很快吗？为了解决这个问题，我们需要将内存中的变化写入到redologbuffer中，redologbuffer是内存中的一个缓冲区。用于存储重做日志。rodo日志记录了你对数据所做的修改，比如“把id=1的数据的名字从a改成abc”，物理日志，后面会提到。“redolog是顺序写入的，所以比随机写入效率更高。”“InnoDB的重做日志具有固定大小。”例如可以配置为4个文件一组，每个文件大小为1GB，总大小为4GB。从头开始写，然后回到开头在结尾循环写，如下图。writepos是当前要写入的位置，checkpoint是要擦除的位置。在擦除之前，必须将相应的脏页刷新回磁盘。writepos和checkpoint之间的位置是可写位置。当我们系统能够支持的并发比较低的时候，我们可以检查对应的redolog是不是设置的太小了。太小会导致脏页频繁清理，影响并发。您可以通过工具监控重做日志的大小。redolog的大小=innodb_log_file_size*innodb_log_files_in_group(defaultis2)》事务没有提交，MySQL宕机了。这种情况下，BufferPool中的数据会丢失，redologbuffer中的日志也会丢失会丢失，不会影响数据提交事务的成功，redologbuffer中的数据不会刷到磁盘，事务提交的数据会丢失。“针对这种情况，我们可以通过设置innodb_flush_log_at_trx_commit来确定redolog的flush策略。”查看innodb_flush_log_at_trx_commit的配置SHOWGLOBALVARIABLESLIKE'innodb_flush_log_at_trx_commit'innodb_flush_log_at_trx_commit值作为0。提交事务时，redologbuffer中的数据不会写入osbuffer，而是每秒写入osbuffer并刷写到磁盘。1、提交事务时，redolog必须从内存中flush到磁盘文件中。2.提交事务时，将rodo日志写入os缓冲区。默认情况下，操作系统缓冲区每1秒写入一次。刷入磁盘的数据应该是0和2，这可能会导致事务更新丢失，所以一般系统中innodb_flush_log_at_trx_commit的值为1，可以看看你的系统使用的是哪个值？binlog:主从库是如何同步数据的？当我们将mysql主库的数据同步到从库或者其他数据源，比如es、bi数据库时，只需要订阅主库的binlog即可。“binlog这一段很多内容参考了?的Section02，部分内容在Section02有详细解释，我就不介绍了，大家可以一起看这篇文章。”这都是由历史原因决定的。MySQL最早使用binlog来实现归档功能，但是binlog不具备crash-safe的能力，所以后来InnoDB引擎加入了redolog来实现crash-safe。如果MySQL中只有一个InnoDB引擎，或许可以利用redolog来实现归档。这时候redolog和binlog就可以合二为一了。两种日志的区别如下：由MySQL的server层实现，所有引擎都可以将redolog作为物理日志，记录数据页上的修改。Binlog是逻辑日志，记录了语句的原始逻辑，比如给id=2的行的c字段加1。redolog是固定空间，循环写入。Binlog是追加写入。当binlog文件写入到一定大小时，会切换到下一个，不会覆盖之前的日志。“我们可以通过设置sync_binlog来决定binlog的flushing策略。”binlog写入osbuffer，由操作系统决定何时刷盘。多次交易的数据可能会丢失。1.将binlog写入osbuffer。sync_binlog的值设置为1表示分两阶段提交。接下来我们看一下id=2的行的c字段加1的执行过程。前面的阶段大家应该都能看懂。不懂的可以看看《MySQL实战45讲 》。重点关注最后三个阶段。引擎将新数据更新到内存中，并将操作记录在重做日志中。此时redolog处于prepare状态，然后通知executor执行完成。可以提交事务执行器生成的binlog，并将binlog写入磁盘。引擎将写入的重做日志更改为已提交状态，更新完成。写分两步？也就是prepareandcommit,two-phasecommit”因为不管先写redolog还是binlog，在crash发生后，原始数据库最终可能会和log恢复的数据库不一致”而Two-phasecommit可以避免这个问题。”redolog和binlog有关联行。恢复数据时，redolog用于在主机故障时恢复未更新的物理数据，binlog用于备份操作。每个阶段的日志操作都记录在磁盘上。恢复数据时，如果redolog状态为commit，说明binlog也成功，直接恢复数据；如果redolog是prepare，需要检查对应的binlog事务是否成功，决定是返回Roll还是execute。先说一下我踩过的一些坑。“1、数据库支持的并发度不高。”在一些对并发要求较高的系统中，可以增加BufferPool和redolog，这样可以避免频繁出现脏页，提高并发性。“2交易提交很慢。”原来我负责的一个系统运行的很正常，直到上游系统每天2点钟疯狂调整我的界面，然后我这边就全是交易方法，交易提交很慢。被监控了“BufferPool和redolog的设置合理，不会太小，问题出在哪里？我也不知道。”后来dba找到原因，将复制方式从半同步复制改为异步复制解决这个问题。问题”“异步复制”：MySQL默认的复制是异步的。主库在执行完客户端提交的事务后会立即返回结果给客户端，并不关心从库是否接收并处理。会有一个问题，主要是如果crash丢了，此时master已经提交的事务可能还没有传到slave数据库，如果这个时候强行将slave提升为master，可能会导致新主人的数据不完整。“同步复制”：是介于全同步复制和全异步复制之间的一种，主库只需要等待至少一个从库节点接收并FlushBinlog到RelayLog文件即可，主库不需要等待对所有的从库给主库反馈。同时这里只是一个收到的反馈，不是已经完全完成提交的反馈，这样可以节省很多时间。返回给客户端。因为有必要等待所有从库都执行完事务再返回，全同步复制的性能必然会受到严重影响。”3.在一个方法中，我先插入了一条数据，后面再查。插入成功，但是没有检测到。”这个比较容易排错，如果系统采用数据库读写分离，写和插入使用主库，从库用于正在阅读，当binlog同步慢的时候，就会出现这种情况，这时候只需要让这个方法强制主库走就行了，转载请联系Java石塘公众号。
