Mysql

执行器
执行器是一个非常核心的组件，负责跟存储引擎配合完成一个sql语句在磁盘和内存层面的全部操作，包括从磁盘加载数据到Buffer Pool中进行缓存，包括写入undo日志，包括更新Buffer Pool里的数据，以及写入redo log buffer，redo log刷入磁盘，写binlog等等。

提交事务根据配置的策略把redo日志从redo log buffer里刷到redo log磁盘中
配置项：innodb_flush_log_at_trx_commit。
1、当值为0时，在提交事务时，不会把redo log buffer刷入磁盘。相当于提交事务成功了，mysql突然宕机，会导致内存中数据和redo日志都丢失
2、当值为1时，提交事务时，就必须把redo log buffer刷入磁盘redo log里。这样哪怕buffer pool中更新的数据还没有更新到磁盘中，此时mysql突然崩溃，在重启之后，可以根据redo日志去恢复之前做过的修改
3、当值为2时，提交事务时，把redo log buffer刷到os cache内存缓存里，没有实际进入磁盘文件，如果此时机器宕机，os cache中的redo log会丢失，同样会导致事务提交，结果数据丢失

基于binlog和redo log完成事务提交
1、binlog有个参数：sync_binlog，控制binlog的刷盘策略，默认值为0，此时当你把binlog写入磁盘的时候，其实不是直接进入磁盘文件，而是进入os cache内存中，此时机器宕机，会造成在os cache的binlog日志丢失
2、把sync_binlog参数值设置为1，此时会强制在提交事务的时候，把binlog日志刷入磁盘，这样哪怕提交事务后宕机，也不会造成binlog日志丢失
3、binlog写入磁盘文件后，会完成事务提交，此时会把本次更新的binlog文件名和日志在文件里的位置，都写入到redo log日志文件中，同时在redo log中写入一个commit标记。完成这些操作之后，才算最终完成了事务的提交

最后在redo log中写入commit标记的意义是什么
用来保持redo log和binlog日志一致。
上图在提交事务的时候，一共有5、6、7三个步骤，必须都执行完毕才算提交事务成功。如果在完成步骤5的时候，也就是redo log刚刷入磁盘，mysql宕机，此时会因为没有最终的事务commit标记在redo log中，此次事务判断为提交失败。同理在步骤6的时候，binlog写入磁盘，此时myslq宕机，因为redo log中没有commit标记，此次事务提交也是失败的。

在IO线程把buffer pool里修改的脏数据刷回磁盘之后，磁盘数据才会和内存一样，在IO线程把脏数据刷到磁盘之前mysql宕机也没关系，因为重启之后会根据redo log日志恢复提交的事务做过的修改到内存中去。

事务提交之后，把修改的数据redo log buffer 刷回到redo log中，为什么不直接把修改的缓存页数据刷回磁盘
1、修改的数据可能很小，一个缓存页的数据16kb比较大，刷入磁盘耗时久。而写入redo log可能就几十个字节
2、缓存页刷回磁盘是随机写，性能很差。redo log写日志是顺序写入磁盘文件，性能非常好

数据库压测工具：sysbench

Buffer Pool
默认配置为128mb，有点太小了，实际生产环境可以进行调整

mysql对磁盘文件的数据抽象出来一个数据页的概念，把很多行数据放到一个数据页里，当我们要获取行数据的时候，会从磁盘文件中拿当前这行数据所在的数据页，加载这页的数据到buffer pool中。所以buffer pool中存放的是一个一个的数据页。

默认情况下，磁盘中存放数据页的大小是16kb，一页中包含了16kb的内容。buffer pool中存放的一个一个数据页，通常叫做缓存页，默认情况下，一个缓存页的大小和磁盘上的一个数据页大小是一一对应的，都是16kb。

对于每个缓存页，都会有一个描述信息，大体是用来描述这个缓存页的，比如这个数据页所属表空间，数据页的编号，这个缓存页在buffer pool中的地址等等。这个描述信息本身也是一块数据，在buffer pool中，每个缓存页的描述信息放在最前面，然后各个缓存页放后面。

buffer pool中的描述数据大概相当于缓存页大小的5%，假设设置的buffer pool大小为128mb，实际上的buffer pool大小为128+128*5%左右。

数据库启动如何初始化buffer pool
数据库启动后，根据配置的buffer pool大小，稍微再加大一点，去找操作系统申请一块内存区域用做buffer pool。申请完成后，会按照默认的缓存页16kb的大小和对应的800个字节左右的描述信息，在buffer pool中划分出来一个一个的缓存也和对应的描述数据。划分完毕之后，目前里面一个一个缓存页都是空的，等运行之后会把数据对应的页读取出来放到buffer pool中。

如何知道哪些缓存页是空闲的
数据库为buffer pool设计了一个free链表，是一个双向链表数据结构，这个free链表里每个节点就是一个空闲的缓存页的描述数据块的地址。也就是说，只要一个缓存页是空闲的，他的描述数据块就会在这个free链表中。每个节点都会双向链接自己的前后节点，组成一个双向链表，除此之外，这个free链表有一个基础节点，会引用链表的头节点和尾节点，40字节大小，里面存储了链表中头节点和尾节点的位置，还有free链表中当前有多少个节点。

如何将磁盘上的页读取到buffer pool的缓存页中
首先从free链表中取一个描述数据块，就可以得到对应的空闲缓存页的地址，接着把磁盘上的数据页数据读取到对应的缓存页中，同时把相关的一下描述数据写入描述数据区中，最后把这个描述数据块从free链表中去除就可以了。

如何知道数据页有没有被缓存
在执行增删改查的时候，先看这个数据页有没有被缓存，有被缓存则直接在buffer pool中使用。数据库还有一个哈希表的数据结构，用表空间号+数据页号，作为一个key，然后缓存页的地址作为value。每次读取数据页到缓存之后，都会往这个哈希表写入一个key-value，当要使用一个数据页的时候，先通过"表空间号+数据页号"作为key去这个哈希表查询一下是否有对应的缓存页数据。

flush链表
当数据库执行增删改查操作后，buffer pool中的缓存页数据有部分是有做了修改，有的缓存页只做了查询没有修改，当进行buffer pool中缓存页数据刷回磁盘时，只需要把有修改过的脏数据进行刷回就可以了。此时就需要用到和free链表类似的flush链表。这个flush链表也是通过被修改过的缓存页的描述数据块中的指针，让被修改过的缓存页的描述数据块组成一个双向链表。而且flush链表的基础节点会指向头节点和尾节点。

如果Buffer Pool中的缓存页不够了，怎么淘汰
基于LRU链表实现，当有加载一个数据页到buffer pool中的时候，就把这个缓存页的描述数据块放到LRU链表的头部，后续如果有查询或者修改这个缓存页的数据会把这个描述数据块挪到LRU链表的头部，这样最近被访问过的缓存页就在链表的头部位置了，尾部位置的数据就是近期未被范围的数据。当free链表节点不够用的时候，就从LRU链表的尾部找到缓存页，把他刷回到磁盘中，然后空闲出来一个缓存页用来存放新的数据。

mysql的预读机制
1、参数：innodb_read_ahead_threshold，默认值为56。如果顺序的访问一个区里的多个数据页，访问的数据页数量超过这个阈值，就会触发预读机制，把下一个相邻区的所有数据页都加载到缓存中
2、参数：innodb_random_read_ahead，默认值为off关闭。如果buffer pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁被范围的，此时会触发预读机制，把这个区里的其他数据页都加载到缓存中

基于简单LRU链表实现存在的不足
1、mysql预读机制会把其他未访问的数据页加载到buffer pool中，并一起放到LRU链表的前面，导致尾部那些相对更频繁被访问的数据被清除
2、全表扫描的数据，全部获取的大量数据都被放在头部，但是后续几乎不用到其中的大部分数据，也会导致尾部相对更频繁被访问的数据被清除

基于冷热数据分离设计LRU链表
实际上LRU链表在设计的时候会被分为2部分，一部分是冷数据，一部分是热数据，参数：“innodb_old_blocks_pct”，默认37，即默认冷数据占比为37%。
当数据从磁盘加载到buffer pool的时候，会把数据页放在冷数据链表区域的头部位置。参数：“innodb_old_blocks_time”，默认1000，即一个数据页被加载到缓存页之后，在1000毫秒之后再次被访问，则会把这个缓存页挪到热数据链表区域的头部。这样就解决了上面所提到的问题。

热数据区域的访问规则优化
热数据区域的数据是可能经常被访问的，如果每次访问其中一个缓存页，就要把对应的描述区域节点在LRU链表中的位置挪到头部，则会造成热数据区域链表里节点的频繁移动，非常影响性能。所以mysql对此做了优化，实际上只有在热数据区域的后3/4部分的数据被访问才会把他挪到链表的头部。

什么时候把LRU冷数据区域的缓存页刷入磁盘然后释放缓存空间
1、定时任务每隔一段时间会把LRU链表冷数据区域的尾部一些缓存页刷回到磁盘，然后清空这几个缓存页，把他们加到free链表中，同时将他们在LRU链表及flush链表中移除。实际上在缓存页还没用完的时候，可能就会清空一些缓存页了
2、同时后台线程在mysql不怎么繁忙的时候，会把flush链表的缓存页都刷回到磁盘中，那么这些缓存页会在flush链表，LRU链表中移除，然后加入到free链表中

mysql的生成优化经验：多个Buffer Pool优化并发能力
1、多线程并发访问一个Buffer Pool，是需要加锁的，先让一个线程完成一系列的操作，比如加载数据页到缓存页，更新free链表，更新LRU链表，然后释放锁，接着下个线程再执行一系列的操作
2、mysql默认的规则是，如果给Buffer Pool分配的内存空间小于1g，那么最多只会给1个Buffer Pool。如果机器内存很大，那可以设置多个Buffer Pool。比如参数:innodb_buffer_pool_size=8g，innodb_buffer_pool_instances=4。这样就可以设置4个Buffer Pool，每个为2g大小，分散并发压力提高数据库的并发性能。

基于chunk机制把Buffer Pool给拆小
Buffer Pool是由多个chunk组成的，参数：innodb_buffer_pool_chunk_size控制chunk的大小，默认值是128mb。假设每个Buffer Pool为2g，那么这个Buffer Pool就有16个chunk。每个chunk里就是一系列的描述数据块和缓存页。每个Buffer Pool里的多个chunk共享一套free、flush、lru这些链表。这套chunk机制可以在运行期间支持动态调整Buffer Pool的大小。

通过SHOW ENGINE INNODB STATUS可查看当前数据库innodb中Buffer Pool的使用情况

一行数据在磁盘上是如何存储的
数据库数据都是基于数据页为单位进行获取更新，对应数据页中的每一行数据，在创建表的时候可以指定他的行存储格式，比如COMPACT格式。对于每一行数据实际存储的时候，都会有一些头字段对这行数据进行一定的描述，再放上这行数据具体的值。

变长字段如何存储
需要保存变长字段的实际长度值，比如存储：hello a a，在磁盘文件中存放时是类似于格式：0*05 null值列表 数据头 hello a a。多个变长字段以倒序放在行开头位置。

null值如何存储
所有的null值，不是通过字符串在磁盘上存储，而是通过二进制的bit位，所有字段可为null的会以bit位的形式存放在null值列表中，bit值为1说明是null，0说明不是null，并且以倒序存放，不足8位则高位用0填充。
如：0*05 00000010 头数据 hello a。

40个bit位的数据头
1、数据头是描述这行数据的。第一位和第二位都是预留位，没有任何含义
2、接下来有个bit位是delete_mask，用来标识这行数据是否被删除
3、下一个bit位是min_rec_mask，B+树里每一层的非叶子节点里的最小值都有这个标记
4、接下来4个bit位是n_owned，记录数
5、接下来13个bit位是heap_no，代表当前这行数据在记录堆里的位置
6、然后是3个bit位的record_type，就是这行数据的类型，0代表普通类型，1代表B+树的非叶子节点，2代表的是最小值数据，3代表的是最大值数据
7、最后16个bit位是next_record，指向下一条数据的指针

实际一行数据如何进行存储
1、根据上述一行数据，会有可变字符串长度值，null值列表，数据头，行数据，经过字符集编码后进行存储
2、在真实数据部分，会有一些隐藏字段
3、DB_ROW_ID，作为一个行的唯一标识，不是主键id
4、DB_TRX_ID，和事务相关，说明是哪个事务更新的数据
5、DB_ROLL_PTR，回滚指针，用来进行事务回滚

行溢出
当一行数据的大小超过一个数据页16kb的存储空间时，会在那一页数据的行数据中只存储部分数据，同时包含20个字节的指针，指向了其他一些数据页，那些数据页用链表串联起来，进行存放。比如大字符串、TEXT、BLOB类型等。

数据页到底是怎么样的
每个数据页拆分成了很多个部分，包含文件头(38个字节)、数据页头(56个字节)，最小记录和最大记录(26个字节)，多个数据行，空闲空间，数据页目录，文件尾部(8个字节)。初始数据页是空的，没有行的内容。

当磁盘中的一个空数据页被加载到缓存页中，往缓存页插入一条数据，实际上就是往数据行这个区域插入一行数据，然后空闲区域就会减少一些，接着不停插入数据，直到空闲区域都耗尽，就是这个页满了。

表空间和数据区
平时数据库中创建的表，都是有一个表空间的概念，在磁盘上会有一个对应"表名.ibd"的磁盘文件，物理层面上，表空间就是对应一些磁盘上的数据文件。一个表空间文件里包含了很多很多的数据页，数据页太多不便于管理，所以在表空间里引入了数据区的概念，extent。一个数据区对应着连续的64个数据页，即1mb的空间，然后256个数据区被划分为一个组。

对于表空间而言，他的第一个数据组的第一个数据区的前3个数据页都是固定的，用来存放一些描述性的数据。然后这个表空间的其他数据组，每个数据组的第一个数据区的头2个数据页，都是存放特殊信息的，比如一些相关属性及描述信息。

生产经验
io请求进行调度的时候，默认会走CFQ公平调度算法，这个可能会导致部分sql一直等待其他sql的io执行完成，所以一般建议mysql生产环境改成deadline io调度算法，就是任何一个io都不能一直不停的等待，在指定时间范围内都必须让他执行。

Too many connections故障
设置mysql配置文件参数：max_connections 最大连接数为800，而实际使用过程中发现Too many connections故障，通过
show variables like ‘max_connections’; 命令查看当前mysql只建立了214个连接。原因是因为底层Linux操作系统把进程可以打开的文件句柄数限制为1024，导致无法创建指定的800个最大连接数。通过命令：ulimit -HSn 65535。修改最大文件句柄数为65535，即可让mysql设置的最大连接数生效。

redo log长什么样
redo log里本质上记录的就是在对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值，具体修改的值是什么，他里面需要记录的就是日志类型+表空间号+数据页号+偏移量+修改几个字节的值+具体的值。日志类型就告诉了你他这次增删改操作修改了多少字节的数据

一条redo log看起来大致的结构如下所示：
日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据

redo log block
对于redo log不是单行单行的写入日志文件，用一个redo log block来存放多行日志，一个redo log block是512字节，分为3个部分，12个字节的header头信息、496个字节的body块、4字节的trailer块尾。redo log里存放一个一个的redo log block。

12个字节的header包括4个部分
1、4字节的bolck no，块唯一编号
2、2字节的data length，就是block里写入了多少字节的数据
3、2字节的first record group。每个事物会有多个redo log，即一组redo log，第一个redo log的偏移量
4、4字节的checkpoint on

redo log buffer
申请一片连续内存，里面划分出N多个redo log block，通过配置：innodb_log_buffer_size，可以指定大小，默认为16mb，一个redo log block是512字节，所以可以存放非常多的block，默认大小已经够了。

什么时候将redo log block刷入磁盘文件
1、配置项：innodb_flush_log_at_trx_commit，设置为1时，每个事务提交，都必须把那些redo log所在的redo log block都刷回到磁盘文件中
2、如果写入redo log buffer的日志已经超过redo log buffer总容量的一半了，会把他们刷入磁盘文件
3、后台线程定时刷新，每隔1秒会把redo log buffer里的redo log block刷入磁盘
4、mysql关闭时，redo log block都会被刷入磁盘

redo log日志文件越来越大，怎么存放
redo log会写入一个目录的文件中，可通过参数：innodb_log_group_home_dir，来设置目录的路径。每个redo log 文件大小为48mb，可通过参数：innodb_log_file_size来设置大小。可通过参数：innodb_log_file_size来设置文件数量，默认2个。所以默认情况下，目录中有2个日志文件，分别为ib_logfile0和ib_logfile1，每个48mb。第一个写满了写第二个，第二个写满了重新写第一个。

undo log日志
如果事务执行一半需要回滚，则需要用到undo log，将已经在buffer pool缓存页中执行的增删改操作回滚。undo log会逆向生成增删改语句来支持事务回滚。

多个事务并发执行会出现的问题
1、脏读或脏写，一个事务读到了另外一个事务还没有提交的数据，或者一个事务修改后的数据被另外一个事务回滚
2、不可重复读，多次读取同一条数据，返回的值不一样
3、幻读，范围查询，每次查询到的数据不一样，有时候别的事务插入新的值，会读到多的数据

undo log版本链
之前提到每条数据其实有2个隐藏字段，trx_id和roll_pointer。trx_id就是最近一次事务提交的id，roll_pointer就是指向了更新这个事务之前生成的undo log。undo log中记录了这个事务之前的数据值。这样多次修改会通过roll_pointer串联起来，形成一个重要的版本链。

ReadView机制
执行事务的时候，会生成一个ReadView，里面主要包含4个部分
1、m_ids，当前有哪些事务在mysql中正在执行没有提交
2、min_trx_id，就是m_ids里的最小值
3、max_trx_id，mysql下个要生成事务的id
4、creator_trx_id，当前事务自己的id

通过ReadView机制和undo log多版本链条，即MVCC机制，在开启事务查询时，判断数据当前事务id是否小于min_trx_id，或者是否是自己的事务id，如果是的话即可查看，不是就通过undo log版本链进行下个事务id比对，找到可查看的那个数据。

如何基于ReadView机制实现RC隔离级别
当事务设置为RC隔离级别时，每次查询都会重新生成一个ReadView。

如何基于ReadView机制实现RR隔离级别，避免不可重复读和幻读
开启事务查询时，只生成一个ReadView。

上面为普通快照读的实现方式，当前读的实现方式为利用行锁和间隙锁来实现。

如何避免脏写
多个事务对同个数据进行并发写的时候，会用到锁机制，在更新数据时会对数据进行加锁，里面包含了自己的trx_id和锁状态，使用的是独占锁。此时读时间会根据MVCC机制获取数据。

共享锁
mysql支持查询加共享锁，语法如下：select * from table lock in share mode
就是查询的时候加上共享锁，期间其他事物要更新加独占锁是不行的。

mysql支持查询加独占锁，语法如下：select * from table for update

生产案例性能优化
1、参数：innodb_io_capaticy，数据库参与多大的io速率把缓存页flush到磁盘上，根据测算硬盘的最大随机io速率来设置。
2、参数：innodb_flush_neighbors，flush缓存页页到磁盘的时候，可能会把附近的其他缓存页页刷到磁盘，但是这样有时候会导致flush的缓存页太多。如果使用ssd固态硬盘，可以把他设置为0，这样把每次刷新的缓存页数量降低到最少。

聚簇索引
B+树索引结构里，叶子节点就是数据页自己本身，那么这个索引就是聚簇索引，如主键索引。

回表
普通索引叶子节点的数据页里存放的是索引值和主键，通过索引拿到数据的主键，再通过主键到聚簇索引的数据页中找到数据。

联合索引
对应联合索引而已，就是依次按照各个字段来进行二分查找，先定位到第一个字段对应的值在哪里，如果第一个字段有多个数据值相同，就根据第二个字段来找，以此类推，最终定位到要查找的数据。

索引基本使用规则
1、等值匹配
2、最左侧列匹配
3、最左前缀匹配，如like ‘aaa%’
4、范围查找，对联合索引来说，只有第一个范围所在列的索引生效

执行计划
type
1、值为const时，即为直接可以找到数据的，速度最快，主键id或者是唯一索引2、值为ref时，为普通索引，经过回表获取到数据，速度也很快
3、值为range时，为普通索引进行范围筛选
4、值为index时，通过一个索引里的key值获取到数据直接返回，不需要回表。
比如索引：key(a,b,c)，sql：select a,b,c from table where b=1;
5、值为all时，全表扫描，性能最差

extra
1、using index，查询仅使用到一个二级索引，没有回表操作
2、using index condition，查询经过二级索引后，还需要筛选操作
3、using where，使用了索引外其他条件搜索
4、using join buffer，进行多表关联查询，关联条件不是索引，用jion buffer技术提高关联性能
5、using fileSort，没有直接根据索引进行排序，需要通过磁盘文件排序，性能差
6、using temporary，把数据放到临时表做大量的磁盘文件操作，性能差

一个sql里可能会对多个索引树进行查询，接着用交集、并集的方式做合并。

嵌套循环关联
sql进行关联查询的原理，如果有2个表需要执行关联，需要先在一个驱动表里根据他的where筛选出一批数据，然后对这些数据走一个循环，用每条数据都到另外一个被驱动表里去根据on连接条件和where里的被驱动表筛选条件去查找数据，找到数据就进行关联。假设驱动表查询了10条数据，那么就要到被驱动表去查10次。

通常而言，针对多表查询的语句，要尽量给两个表都加上索引，索引要确保从驱动表里查询是通过索引来查询，接着对被驱动表查询也通过索引去查找，这样才能保证多表关联查询sql的性能。

查询优化
1、无用sql删除，如：where b=b and a=a等无效sql
2、常量替换，如：where i>5 and j>i，改成i>5 and j>5
3、select * from t1 where x1 in (select x2 from t2 where x3=xxx)。会先执行子查询，把所得结果放到一个临时表里，也称为物化表，这个物化表默认会通过内存进行临时存放，如果结果集太大，可能会存放在磁盘上，物化表都会建立索引。

查看sql耗时
show profiles;
show profile cpu,block io for QUERY 10;
show engine innodb status；查看innodb存储引擎当前状态

主从复制
主库需开启binlog，然后从库开启对主库的binlog异步更新，start slave

半同步复制
为了保证主库执行sql提交后突然崩溃，从库没能更新数据，导致主从数据不一致的问题。mysql5.7默认采用半同步复制，主库把日志写入binlog，并且复制给从库，从库完成更新后返回状态给主库，主库再提交事务，然后返回提交事务成功相应给客户端。开启半同步复制需要在主库和从库安装半同步复制插件，同时开启半同步复制功能。

主从复制延迟问题
主库是多线程执行，从库为单线程执行，这样会导致主库更新数据，马上从库里读取会有延迟，mysql5.7开始支持并行复制，可以在从库设置slave_parallel_workers>0，slave_parallel_type设置为LOGICAL_CLOCK，这样开启从库多线程并行复制数据。

主从架构高可用
在主库宕机之后，立马选择一个从库切换为主库。一般用的工具为MHA，即Master Hight Availability Manager and Tools for Mysql

分库分表
一般按业务id分库分表，建立索引映射表同时分库分表，数据同步到ES做复杂搜索。这样用户搜索时，根据映射获取业务id，找到对应的业务数据。运营端根据复杂条件通过ES进行搜索。