Mysql Inndb 架构图

如何知道数据页有没有被缓存？     数据库还会有一个哈希表数据结构，他会用表空间号+数据页号，作为一个key，然后缓存页的地址作为value。

描述数据

trx_id大于ReadView的max_id，所以不能查询到值C

                           数据库莫名其妙的随机性能抖动优化第一个，可能buffer pool的缓存页都满了，此时你执行一个SQL查询很多数据，一下子要把很多缓存页flush到磁盘上去，刷磁盘太慢了，就会导致你的查询语句执行的很慢。因为你必须等很多缓存页都flush到磁盘了，你才能执行查询从磁盘把你需要的数据页加载到buffer pool的缓存页里来。第二个，可能你执行更新语句的时候，redo log在磁盘上的所有文件都写满了，此时需要回到第一个redo log文件覆盖写，覆盖写的时候可能就涉及到第一个redo log文件里有很多redo log日志对应的更新操作改动了缓存页，那些缓存页还没flush到磁盘，此时就必须把那些缓存页flush到磁盘，才能执行后续的更新语句，那你这么一等待，必然会导致更新执行的很慢了。

根据用户id查询对应的订单

根据orderId路由，路由规则：orderId%32=哪个库，orderId/32%32=哪张表

order_32

实现MVCC机制的Undo log版本链

4.写redo日志

2

指针

索引页35

innodb_read_ahead_threshold    他的默认值是56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去。innodb_old_blocks_time（默认1000ms）      一个数据页被加载到缓存页之后，在1s之后，你访问这个缓存页，他才会被挪动到热数据区域的链表头部去。innodb_buffer_pool_size：设置Buffer Pool大小innodb_buffer_pool_instances：设置Buffer Pool个数innodb_log_buffer_size：可以指定redo log buffer的大小，默认的值就是16MBnnodb_io_capacity这个参数是告诉数据库采用多大的IO速率把缓存页flush到磁盘里去的。innodb_flush_neighbors在flush缓存页到磁盘的时候，可能会控制把缓存页临近的其他缓存页也刷到磁盘，但是这样有时候会导致flush的缓存页太多了。

可以根据userId和orderId建立映射表，然后以userId纬度进行分表。根据userId进行路由，路由规则同上，找到userId对应的映射表，然后取出userId对应的订单id列表，取出orderId之后，然后再根据orderId去查询订单信息。如果想根据某个时间段的订单的话，可以在映射表中再添加一个订单时间字段，然后就可以根据订单时间查询了

userId

orderId

orderTime

包含很多数据页

开始节点

order_2

描述数据（热数据区域）

值C

trx_id=70

roll_pointer

一行数据

1

最小主键3

页号=8

chunk

事务C（id=70）

事务B（id=59）

      如果频繁这么搞，那么很多crud操作，每次都要执行两次磁盘IO，一次是缓存页刷入磁盘，一次是数据页从磁盘里读取出来，性能是很不高的。      所以本质上缓存页一边会被你使用，一边会被后台线程定时的释放掉一批。      所以如果你的缓存页使用的很快，然后后台线程释放缓存页的速度很慢，那么必然导致你频繁发现缓存页被使用完了。但是缓存页被使用的速度你是没法控制的，因为那是由你的Java系统访问数据库的并发程度来决定的，你高并发访问数据库，缓存页必然使用的很快了！     然后你后台线程定时释放一批缓存页，这个过程也很难去优化，因为你要是释放的过于频繁了，那么后台线程执行磁盘IO过于频繁，也会影响数据库的性能。     所以这里的关键点就在于，你的buffer pool有多大！     如果你的数据库要抗高并发的访问，那么你的机器必然要配置很大的内存空间，起码是32GB以上的，甚至64GB或者128GB。此时你就可以给你的buffer pool设置很大的内存空间，比如20GB，48GB，甚至80GB。    而一旦你的数据库高峰过去，此时缓存页被使用的速率下降了很多很多，然后后台线程会定是基于flush链表和lru链表不停的释放缓存页，那么你的空闲缓存页的数量又会在数据库低峰的时候慢慢的增加了。

                       redo log文件格式日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据

索引页28（非叶子结点）

7.写入binlog文文件位置写入Commit标记

数据区

user_order_3

Redo LogBuffer

数据页8

user_order_32

db32

索引页21

查trx_id小于min_trx_id事务编号

执行器

在free链表中找到一个空闲的缓存页

主键

5.准备提交事务redolog日志刷入磁盘

3

0

id=3

字段

数据页2

id=2

页目录

数据页（16KB）

Buffer Pool缓冲池

原始值

trx_id=30

redo日志文件

InnoDB存储引擎

读取脏数据

磁盘文件

修改

                                简单的LRU链表会有什么问题？1、预读机制导致热缓存页失效2、全表扫描导致热缓存页失效                       引入冷热数据区域解决热缓存页失效问题                                 在LRU链表中，区分冷热节点，刚从磁盘中加载的缓存页放入冷节点头部。加入冷节点的缓存页在1s后，再次被访问会将其移动到热节点头部。                                对热数据缓存页的再次优化    但是你要知道，热数据区域里的缓存页可能是经常被访问的，所以这么频繁的进行移动是不是性能也并不是太好？也没这个必要。      所以说，LRU链表的热数据区域的访问规则被优化了一下，即你只有在热数据区域的后3/4部分的缓存页被访问了，才会给你移动到链表头部去。如果你是热数据区域的前面1/4的缓存页被访问，他是不会移动到链表头部去的。

Mysql增删改流程

索引页20（叶子结点）

order_1

链表指针

槽位

结束节点

最小id=58

索引页号=28

发现trx_id在ReadView的m_ids列表中，不能查

binlog文件

。。。。。。。。

     按照这个顺序，以此类推，如果你的数据量越大，此时可能就会多出更多的索引页层级来，不过说实话，一般索引页里可以放很多索引条目，所以通常而言，即使你是亿级的大表，基本上大表里建的索引的层级也就三四层而已。

描述数据（冷数据区域）

                        索引使用规则最左列匹配最左前缀匹配范围查找原则等值匹配+范围匹配规则而且假设你的查询里还有age进行范围查询，那么我们之前说过，范围查询的时候，也就只有第一个范围查询是可以用上索引的，第一个范围查询之后的其他范围查询是用不上索引的。所以说，实际设计索引的时候，必须把经常用做范围查询的字段放在联合索引的最后一个，才能保证你SQL里每个字段都能基于索引去查询。

数据区（64个数据页 1MB）

第一次加载的数据页放在冷数据头部

      其实通过之前的学习，我们都是知道一点的，最终这些在内存里更新的脏页的数据，都是要被刷新回磁盘文件的。      所以数据库在这里引入了另外一个跟free链表类似的flush链表，这个flush链表本质也是通过缓存页的描述数据块中的两个指针，让被修改过的缓存页的描述数据块，组成一个双向链表。      凡是被修改过的缓存页，都会把他的描述数据块加入到flush链表中去，flush的意思就是这些都是脏页，后续都是要flush刷新到磁盘上去的

user_order_1

索引页20（非叶子结点）

表空间号+数据页号=缓存页地址表空间号+数据页号=缓存页地址表空间号+数据页号=缓存页地址

                         页分裂     在你不停的往表里灌入数据的时候，会搞出来一个一个的数据页，如果你的主键不是自增的，他可能会有一个数据行的挪动过程，保证你下一个数据页的主键值都大于上一个数据页的主键值。

脏写，就是两个事务都更新一个数据，结果有一个人回滚了把另外一个人更新的数据也回滚没了。脏读，就是一个事务读到了另外一个事务没提交的时候修改的数据，结果另外一个事务回滚了，下次读就读不到了。不可重复读，就是多次读一条数据，别的事务老是修改数据值还提交了，多次读到的值不同。幻读，就是范围查询，每次查到的数据不同，有时候别的事务插入了新的值，就会读到更多的数据。

描述信息是什么？      包含如下的一些东西：这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址以及别的一些杂七杂八的东西。     Buffer Pool中的描述数据只占总大小5%，也就是每个描述数据大概800个字节左右。

值B

trx_id=59

db0

事务A（id=45）

。。。。。。。。。。

free 链表

缓存页

LRU 链表

      多线程访问Buffer Pool会加锁，以防多个线程同时操作free、lru链表。虽然是在内存中操作，性能很快，但是有没有优化的地方?Mysql设置多个Buffer Pool来优化并发能力

基础节点

2.写入旧数据，用于回滚

一组数据区（265个数据区 256MB）

如果想要根据用户查找订单怎么办？根据用户、订单状态、订单时间筛选订单怎么办？

取出对应的订单Id，然后二次回表查询

     但是现在又会存在一个问题了，你现在有很多索引页，但是此时你需要知道，你应该到哪个索引页里去找你的主键数据，是索引页20？还是索引页28？这也是个大问题。     于是接下来我们又可以把索引页多加一个层级出来，在更高的索引层级里，保存了每个索引页和索引页里的最小主键值，如下图所示。

Buffer Pool（128MB）

flush 链表

如果事务A修改了此行数据，再次读取时发现trx_id正好是当前id，可以读取此条数据

值A

trx_id=45

把一个缓存页，刷新到磁盘中去，腾出一个空闲的缓存页。

一个数据页等于一个缓存页，加载数据页到buffer Pool中去

                          ReadView机制m_ids：正在执行的事务idmin_trx_id：m_ids中的最小值max_trx_id：mysql中下一个要生成的事务idcreator_trx_id：当前的事务idReadView机制对多事务并发读提供了极高的性能，但是在多事务并发写时，是需要加锁的。

生产环境应该给Buffer Pool设置多少内存？设置你机器内存的50%～60%左右。buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数

表空间

如果free链表中没有空闲的缓存页怎么办？此时需要淘汰一些缓存页，引入LRU链表

IO线程

数据页缓存hash表

3、更新内存数据

加载到冷数据区域1s后，再次访问移动到热数据头部

最小id=1

索引页号=20

如果数据页被使用，从free链表中移除

undo日志文件

          buffer pool中的缓存页是如何淘汰他们刷入磁盘的？（1）定时把lru尾部的部分缓存页刷入磁盘，然后将其加入free链表。（2）把flush链表中的一些缓存页定时刷入磁盘。（3）实在没有空闲的缓存页怎么办，此时就会在lru尾部淘汰一个缓存页，然后将其刷入磁盘。

一组数据区

1.如果缓冲池中没有，需要加载到缓存

刚开始分表时，尽可能的多分，避免后续扩容困难。一共分为32个库，每个库32张表，以后扩容呈倍数扩容。

8.随机刷磁盘

                         redo log刷入磁盘机制（1）如果写入redo log buffer的日志已经占据了redo log buffer总容量的一半了，也就是超过了8MB的redo log在缓冲里了，此时就会把他们刷入到磁盘文件里去。（2）一个事务提交的时候，必须把他的那些redo log所在的redo log block都刷入到磁盘文件里去，只有这样，当事务提交之后，他修改的数据绝对不会丢失，因为redo log里有重做日志，随时可以恢复事务做的修改。（3）后台线程定时刷新，有一个后台线程每隔1秒就会把redo log buffer里的redo log block刷到磁盘文件里去（4）MySQL关闭的时候，redo log block都会刷入到磁盘里去。

最小主键1

页号=2

如何确定buffer pool中有哪些空闲页？

LRU链表的大小和初始free链表的大小是相等的，加入缓存页到lru链表时，如果超出容量限制，则需移除一个缓存页，将此缓存页刷新到磁盘中去。

id=1

包含页目录

如何避免执行crud的时候，频繁的发现缓存页都用完了，完了还得先把一个缓存页刷入磁盘腾出一个空闲缓存页，然后才能从磁盘读取一个自己需要的数据页到缓存页里来？

id=4

user_order_2

6.准备提交事务写入binlog日志

                         explain执行计划index：然后index方式其实是扫描二级索引的意思，就是说不通过索引树的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，这种速度还是比较慢的，大家尽量还是别出现这种情况。all：当然index方式怎么也比all方式好一些，all就是直接全表扫描了，也就是直接扫描聚簇索引的叶子节点，那是相当的慢，index虽然扫描的是二级索引的叶子节点，但是起码二级索引的叶子节点数据量比较小，相对all要快一些。