ke1ying的博客

Mysql锁--mysql详解（十二）

Mvcc--mysql详解（十一）

Undo日志--mysql详解（十）

Redo日志--mysql详解（九）

Buffer pool--mysql详解（八）

Mysql优化器-mysql详解（六）

连接查询-mysql详解（五）

系统表空间-mysql详解（四）

索引-mysql详解（三）

InnoDB & index页-mysql详解（二）

学习 不怕慢 就怕停

我们都知道innoDb刷新数据到磁盘是通过页为单位来刷新的，那么总不能每次修改一条数据或者几条，就把整个页刷新到磁盘上，所以这时候redo日志的作用就是记录每条新增或者修改后的数据，在以后特定的时间flush到磁盘上。Flush什么东西到磁盘呢？Flush那些修改之后的脏页，当redo日志满了之后，就会有限flush脏页到磁盘，来腾出redolog空间，这时候就会影响数据库的查询性能。刷新脏页的时机是mysql服务器空闲的时候，当系统内存不足的时候，当服务器正常关闭的时候，这时候就可能发生flush脏页

在日常开发中，mysql存储引擎默认是用innoDB，存储引擎分为innoDB，myISAM,memory，innoDB支持事务，myISAM不支持事务，memory是在内存中，不存储在磁盘，所以memory适用于临时表，特性是mysql服务器重启之后，内存里的数据就会消失。一条数据的查询过程是mysql客户端与服务端之前的连接过程，先通过tcp与ip连接客户端，然后转码解析，查询缓存，sql语句优化，前面的都属于service层，后面的是访问存储引擎，最后再把数据返回给客户端。根据电脑和sqlse

我们在日常使用sql中，查询数据库反映的时间过长，这时候可能是flush脏页导致的，而脏页会什么时候触发呢？当查询的数量太多，每次全量查询都会淘汰掉脏页从而触发磁盘的I/O操作户导致查询时间过长。当innoDB的redo log满了后，这时候会占用内存优先刷新redo日志，执行flush吧数据刷到磁盘，腾出redo log空间。系统内存不足时候，需要淘汰脏页给新的页使用。当mysql系统认为空闲的时候，会刷新脏页到磁盘。当mysql服务器正常关闭，会刷新脏页到磁盘。脏页和干净页都

Mysql隔离级别默认是repeatable read，他是不可以解决不可重复读，不可重复读是用mysql里面的mvcc解决，mvcc全称是mulit-version Concurrent Controller多版本并发控制，里面有个版本链readView。一条数据记录都是由隐藏列、真实数据、额外数据，额外数据指描述真实数据的数据，也称呼为元数据，里面有最长字段长度列表，null值列表，头部信息，里面放着delete_mark，innoDB删除并不是真的删除，这也是插入数据回滚的时候，那个id会自动

前面说了解决并发事务访问有两种解决办法，一种用mvcc的版本链解决，读的时候用版本链readView控制，写的时候加锁。一种是读写都加锁，比如只允许读取最后数据的银行业务。锁又分为共享锁（s锁）和排它锁（x锁），锁的颗粒度分为表锁和行锁，所以当向上表的排他锁的时候，必须里面的行没有上x锁或者s锁，当然不是遍历所有行，于是在上行锁的时候，会有一个is和ix的锁，代表当前表上了行锁。Mysql锁共享锁排它锁 (1)—mysql进阶（六十八)行锁、表锁我们主要说的是innoDB存储引擎的锁，其

前面说了为了解决脏读，幻读，不可重复读，mysql设置了四种隔离级别，read committed和read uncommitted会发生幻读和不可重复读，repeatable read会发生不可重复读，seriliztable，mysql默认是repeatable read，用mvcc解决不可重复读。设置隔离级别set global|session transaction isolation level …。当global时候，代表执行完之后其他所有session都可以使用当前设置的事务，如果是sessi

前面我们说了undo日志写入undo页面链表时，先需要把undo page header、undo segment header、undo log header等。每个事务都会有相应的undo链表，如果只存储一点数据不是很浪费吗，于是有了可重用，满足当前链表只有一个页，并且小于总空间的3/4。还介绍了回滚段，默认128个回滚段，每个段有1024个undo slot，每个slot分配给不同的事务，对应一个单独的undo页面链表。Undo日志也会记录redo日志，但临时表的undo日志写入不会记录redo日志，

前面说了undo日志的文件格式，第一页和后面的页是不同的，填入undo日志之前，会先把undo_page_header属性填满，还有undo_segment_header,undo_log_header。List base node存在undo segment header，list node存在每个undo 页面的undo_page_header。不重用的undo日志 (2)—mysql进阶（六十五)重用undo页面前面说了为了提高并发性能，多个事务写入undo日志性能，innoDB每个事

前面我们说了undo日志在insert，update，delete存储的日志格式，delete存储的type是trx_undo_del_mark_rec里面有个参数old roll_pointer会指向insert的地址值，恢复需要的数据。Select是没有undo日志的，因为select不需要回滚事务。undo日志insert，update，delete (1)—mysql进阶（六十四)通用链表结构在写入undo日志的过程会使用到多个链表，很多链表都有同样的节点结构，List node

前面说了redo日志为了保证系统宕机的情况下，能够恢复数据，恢复数据是在以checkpoint_lsn为起始位子来恢复，在该值之前的都是已经持久化到磁盘的，可以为了提升效率而放弃，而之后的数据，也可能在checkpoint之后，被后台异步运行的线程刷新到磁盘，这时候如果file header里file_page_lsn值大于checkpoint_lsn值，代表已经持久化，也可以跳过。还有会吧同一个页的space id和page number放入一个hash表，这样避免同一个页反复I/O插入。Redo日志

前面说了lsn值就是log sequence number代表记录redo日志存了多少字节，默认值是8000多，算偏移量直接减一下就好，还有flush_to_disk的lsn值，这个值之前的数据代表都已经持久化，这个持久化代表redo日志持久化，但是对应的buffer pool数据还不能覆盖，这时候又checkpoint lsn值，这个值之前的数据都代表已经持久化完毕，是可以覆盖的。因为redo日志存储有限，存满之后，又会从第一个文件循环存储。可以用show engine innoDB status查看。

前面说了redo日志的格式，刷新到磁盘后台有线程每秒运行一次，还有事务提交的时候，buffer pool不会刷新到磁盘，但是log buffer会刷新到磁盘。Log buffer会分成若干的block，吧这些block持久化到磁盘上，mysql根目录有两个log_file0和log_file1,可以增加，当存满的时候，从0循环继续存储，默认是48Mb。每个block是512个字节，前面四个block占比2048个字节是记录管理信息，2048字节后面开始记录block。redo日志文件格式(3)—mysq

前面我们说了修改一条数据总不能吧16kb的页全部持久化到磁盘上，于是有了redo日志，记录哪些修改的数据，redo日志也有自己的缓存区，并不是直接把数据记录到磁盘上，缓存区是innoDB_redo_buffer_size，默认是16mb，为了保证原子性，他会分为不同的组，当乐观插入的时候，只有一条数据需要插入，则type的第一个字节是1，代表只有一条插入，当悲观插入多条插入的时候，会有一个MLOG_MULTI_REC_END的日志，表示这组记录完毕，若系统宕机重启，解析redo日志时候，没有解析到这个，则前

前面我们说了为了吧buffer pool的数据持久化到磁盘上，比如修改了一条数据，不可能每次吧整个页的数据都刷新过去，这样耗费性能，innoDB就是把修改的数据记录在redo日志里，redo日志格式主要是spaceId，type，page_number，offset，Data等。Offset记录上一条数据的地址，为了修改上一条记录头部新的next record，data记录的就是真实数据。Redo日志主要关注的就是一条语句修改了多少b+树，针对其中某颗树，可能增加了页，也可能更新了叶子节点或者内节点。他会记

前面说了当设置的buffer_pool_size在1个G内，则不管如何设置，buffer_pool_instances都是一个，当在1个G以上，mysql才支持多个instances设置，每个都有自己独立的链表，多线程的情况下互不干扰运行。Show engine innodb status\G磁盘太慢，内存很有必要，buffer_pool在mysql启动的时候会向系统申请连续的内存空间，buffer_pool_instances 和buffer_pool_chunk_size这几个参数是倍数关系，设

上篇文章说mysql5.6之后新增了系统变量optimizer_tance可以看到他的优化过程。Optimizer trance—mysql进阶（五十三)缓存的重要性前面我们说过innoDB表示吧数据存在表空间，不管是用于存储表的系统数据，还是存储用户数据的索引(聚簇索引 和 二级索引)，表空间实际是在磁盘上的。但我们不可能每次查询数据都从磁盘上查询，那样以磁盘的龟速，怎么能配的上快速的cpu呢。所以我们在磁盘访问数据的时候，会把整个页的数据全部都放入缓存中，即使只是访问一条，也是会把整个页

前面说了mysql优化器访问数据库的方法有const，ref，ref_or_null，range，index，all。然后又分为条件全部是索引回表查询，和条件有非索引查询，则需要回表之后，在过滤。又有intersection合并索引和union并集索引，当两个单独二级索引查询，不是联合索引查询，可能会触发这两个索引查询，用and是intersection，用or是union查询，触发有两个注重点：二级索引必须等值匹配，联合索引必须所有值匹配。 主键索引可以范围匹配。因为二级索引建立在主键索引等值的情

前面说了explain参数的type代表访问数据库的方法，如果用主键和唯一二级索引，测试最快的const方法，若用普通索引，则是ref，还有ref_or_null，range是代表区间查询，若用index则代表查询联合索引的非最左边索引，最后是all。访问方法access method---单表访问方法（三十六)注意事项我们先回忆一下二级索引+回表的查询方法：SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

前面我们说了extra，这个主要显示额外的信息，比如如果没有填写表，会显示no table，用了索引会显示using index，全表扫描或者回表，则会显示using where，如果mysql优化器转内部查询，还会吧内部查询选择的策略显示出来，比如内部连接临时表去重复值查询，比如松散查询，比如最原则的方法，循环查询。Extra（5）—mysql执行计划（五十一)如果我们在explain中加个 format=JSON会发生什么呢？这时候我们可以获取到json数据，mysql> EXP

前面说了系统表空间的整体结构，与独立表空间大致类似，多了五个特有的系统属性页，因为整个表空间只有一个系统表空间，所以他的重要性不言而喻，space id为0。整体结构&InnoDB数据字典(1) --系统表空间结构（三十三)Data dirctionary header只要有四个基本表，就意味着可以获取其他系统表以及用户定义表的所有元数据。有表的名称之后：可以从sys_table里获取到表的唯一table_id。 通过table_id可以在sys_columnts表里获取表的所

上篇文章说了acid四个事务的特性，原子性保证要不两个sql一起执行，要么不执行，隔离性，两个事务之间必须互不干扰，一致性，两边的数据必须保持一致，可以说一致性的前提是原子性和隔离性必须正常，但原子性和隔离性都正常，就能保证一致性吗？并不是，还必须满足其他一些约束，比如金额不能为负数。持久性就是必须持久化到磁盘才算事务成功。transaction (1)—mysql进阶（五十七)Mysql中事务语法前面我们重点介绍了理论知识，那么我们在mysql里如何使用呢？开启事务我们可以用命

上篇文章我们说了，使用索引的注意事项，前面我们总结了查询数据库的方式有const，ref，ref_or_null，range，index，all，而使用时候需要注意，当where语句后面全是索引查询，当where语句后面跟着非索引的时候，当用and连接，比如where key1 and 非索引 = ‘abc’，这时候会先二级索引查询索引b+树进行回表。若用where key1 or 非索引 = ‘abc’，这时候会直接全表查询。二级索引查询注意事项(2)--单表访问方法（三十七)索引合并my

前面介绍了，如果加个format=JOSN会把数据以json的格式返回，如果想看查询的额外信息，还可以在explain之后加个show warning查看，其中如果code为1003，则代表message里的内容是mysql优化器优化之后的sql。JSON格式执行计划（6）—mysql执行计划（五十二)对于mysql5.6之前的版本说，mysql像黑盒子，只能通过explain语句查看最后优化器决定使用的执行计划，却无法知道他做什么决定。在mysql5.6之后，mysql设计为我们贴心的加了op

上篇文章说了连接查询的成本，主要由驱动表的扇出值和被驱动表的查询方法决定，而成本这些都是可以在%cost%表查看的，因为分为server和engine表，server不管理数据成本，里面包含连接管理，查询缓存，sql解码，sql优化，engine就是数据引擎成本，而distinct，union等特殊查询，会建立临时表，临时表看数据量可能建立磁盘或者内存，比如distinct会用unique索引建立临时表去重。连接查询成本(2)---mysql进阶（四十二)我们前面说了show index fro

前面说了explain的table是表名，显示在前面的代表驱动表，正常select会出现不同的id，但如果子查询本来是两个select，但被优化成连接查询，就会导致是相同的id，union查询会出现临时表，id为null，这个临时表作用于去重，union all不需要去重，所以也就不需要建立临时表。id，table列(1)—mysql执行计划（四十七)Select type我们都知道sql里会包含若干个select，每个select代表一个小的查询语句，每个select的from都可以关联若

前面说了xdes 类型页面，第一页的extent0里面的xdes页面叫做fsp 因为里面还存着表空间的数据file space header，这里面主要存着xdes entry几个链表的基点，和inode entry链表的基点，后面的每个组第一页都是xdes类型页面。Extent0里面还有inode类型页面，这里面主要存着seg_inode_free和seg_inode_full两个链表存储着inode entry，他们的基点也在file space header。Segment header因为每

前面说了子查询里有no/any/all不能用limit，group by，order by等，他会被查询优化器优化掉，子查询可能会物化转成内连接semi-join查询，物化就是会吧子查询看做一个表，如果数据太大，超过系统变量tmp_table_size，则会在磁盘里创建b+树的临时表，如果比较小，则会创建内存里hash树的临时表，之后会物化表转连接，但如果直接转where 和on，则可能会出现子查询多条的情况，我们的真实需求并不需要多条，所以有了semi-join。子查询注意事项&semi-jo

前面说了semi-join，这个是在where或者on语句后面，in里面，并且外层的条件必须用and与子查询连接，semi-join的作用就是，不管子查询有多少条数据返回，都不管，外层都只查询出来外层表数据，如果不符合条件，可以用物化表或者in变exists方法优化。还有派生表查询，可以内外合并，不行的话就物化查询。Semi-join使用条件，派生表优化 (3)—mysql基于规则优化（四十六)Explain一条查询语句经过mysql优化器之后，会生成一个执行计划，这个计划展现了接下来具体查询方

上篇文章回忆了innodDB的独立表空间和系统表空间的结构，因为需要梳理的知识点太多，所以额外用一篇。链接链接单表的访问方法对我们开发来说，mysql就是个软件，用sql查询我们需要的数据，当遇到性能差的sql，如果我们连怎么优化都不知道，岂不是很尴尬。我们前面说过mysqlSql Service 有一个查询优化器的模块，一条sql进行预发解析后会进行查询优化，生成一个执行计划，这个执行计表明有哪些索引进行查询，表之间的链接顺序是什么样的，最后调用执行计划的步骤来真正的查询，吧结果返回给用户。不