MySQL实战

1. 系统是如何跟MySQL打交道的

image.png

系统采用数据库连接池的方式去并发访问数据库，然后数据库自己其实也会维护一个连接池，其
中管理了各种系统跟这台数据库服务器建立的所有连接，当我们的系统只要能从数据库连接池获取到一个数据库连接之后，我们就可以执行增删改查的SQL语句了。

2. MySQL架构设计

image.png

1. 网络连接必须由线程去监听和读取请求；
2. SQL接口：负责处理接收到的SQL语句；
3. 解析器：按照既定的SQL语法，对我们按照SQL语法规则编写的SQL语句进行解析，让MySQL能看懂SQL语句；
4. 优化器：选择最优的查询路径；
5. 执行器：根据优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划。

3. 更新语句在MySQL中是如何执行的？

#示例SQL语句
update users set name='xxx' where id=10

image.png

在你执行更新的时候，每条SQL语句，都会对应修改buffer pool里的缓存数据、写undo日志、写redo log buffer几个步骤；
但是当你提交事务的时候，一定会把redo log刷入磁盘，binlog刷入磁盘，完成redo log中的事务commit标记；最后后台的IO线程会随机的把buffer pool里的脏数据刷入磁盘里去。

redo日志刷盘策略 innodb_flush_log_at_trx_commit
0：log buffer将每秒一次地写入log file中，并且log file（OS Cache）的flush(刷到磁盘)操作同时进行。
1：每次事务提交时MySQL都会把log buffer的数据写入log file（OS Cache），并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时MySQL都会把log buffer的数据写入log file（OS Cache），但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

binlog日志刷盘策略 sync_binlog
0：binlog写入磁盘的时候，其实不是直接进入磁盘文件，而是进入os cache内存缓存。
1：强制在提交事务的时候，把binlog直接写入到磁盘文件里去。

4. Buffer Pool内存数据结构

Buffer Pool的大小
默认128MB
可在配置文件中修改其大小

[server]
innodb_buffer_pool_size = 2147483648

MySQL对数据抽象出来了一个数据页的概念，他是把很多行数据放在了一个数据页里，也就是说我
们的磁盘文件中就是会有很多的数据页，每一页数据里放了很多行数据。
Buffer Pool中存放的是一个一个的从磁盘上加载过来的缓存页，每个缓存页大小为16KB。
对于每个缓存页，实际上都会有一个描述信息，这个描述信息大体可以认为是用来描述这个缓存页的信息：这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址等等。
Buffer Pool中的描述数据大概相当于缓存页大小的5%左右。

image.png

5. Free链表和数据页缓存哈希表

数据库会为Buffer Pool设计一个free链表，他是一个双向链表数据结构，这个free链表里，每个节点就是一个空闲的缓存页的描述数据块的地址，也就是说，只要你一个缓存页是空闲的，那么他的描述数据块就会被放入这个free链表中。除此之外，这个free链表有一个基础节点，他会引用链表的头节点和尾节点，里面还存储了链表中有多少个描述数据块的节点，也就是有多少个空闲的缓存页。
数据库还会有一个哈希表数据结构，他会用表空间号+数据页号，作为一个key，然后缓存页的地址作为value。当你要使用一个数据页的时候，通过“表空间号+数据页号”作为key去这个哈希表里查一下，如果没有就读取数据页，如果已经有了，就说明数据页已经被缓存了。

image.png
在执行增删改查的时候，肯定是先看看这个数据页有没有被缓存，如果没被缓存，从free链表中找到一个空闲的缓存页，从磁盘上读取数据页写入缓存页，写入描述数据，从free链表中移除这个描述数据块。
每次你读取一个数据页到缓存之后，都会在这个哈希表中写入一个key-value对，key就是表空间号+数据页号，value就是缓存页的地址，那么下次如果你再使用这个数据页，就可以从哈希表里直接读取出来他已经被放入一个缓存页了。

6. Flush链表—解决Buffer Pool中的脏页

更新了Buffer Pool的缓存页中的数据，但还没有刷入到磁盘中，此时Buffer Pool与磁盘中的数据不一致，称为脏数据、脏页。
MySQL通过一个后台IO线程定时将脏页同步到磁盘中。但并不是所有缓存页都是脏页，所以MySQL通过一个flush链表来标记这些脏页，这个flush链表本质也是通过缓存页的描述数据块中的两个指针，让被修改过的缓存页的描述数据块，组成一个双向链表。凡是被修改过的缓存页，都会把他的描述数据块加入到flush链表中去，flush的意思就是这些都是脏页，后续都是要flush刷新到磁盘上去的。

image.png

7. 缓存页不够时，使用LRU链表淘汰部分缓存页？

Buffer Pool中引入一个新的LRU链表，来记录哪些缓存页最近最少被使用。
工作原理：
只要是刚从磁盘上加载数据到缓存页里去，这个缓存页就放入LRU链表的头部，后续如果对任何一个缓存页访问了，也把缓存页从LRU链表中移动到头部去。这样在LRU链表的尾部，一定是最近最少被访问的那个缓存页，把他的数据刷入磁盘，腾出来一个空闲缓存页，然后加载需要的新的磁盘数据页到空闲缓存页里去。

image.png

8. 预读机制导致LRU算法淘汰缓存命中率高的数据页

预读机制：当从磁盘上加载一个数据页的时候，可能会连带着把这个数据页相邻的其他数据页，也加载到缓存里去。
预读机制加载进来的缓存页可能根本不会有人访问，结果他却放在了LRU链表的前面，此时可能会把LRU尾部的那些被频繁访问的缓存页刷入磁盘中！

image.png

哪些情况下会触发MySQL的预读机制？

1. innodb_read_ahead_threshold：默认值56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去。
2. innodb_random_read_ahead：默认关闭（OFF），如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去。

全表扫描也会造成预读的同样问题，类似如下的SQL语句：SELECT * FROM USERS，此时他没加任何一个where条件，会导致他直接一下子把这个表里所有的数据页，都从磁盘加载到Buffer Pool里去。

9. 基于冷热数据分离的方案优化LRU算法

真正的LRU链表，会被拆分为两个部分，一部分是热数据，一部分是冷数据，这个冷热数据的比例是由innodb_old_blocks_pct参数控制的，他默认是37，也就是说冷数据占比37%。
第一次被加载了数据的缓存页，都会不停的移动到冷数据区域的链表头部。
当在innodb_old_blocks_time时间后（默认1000ms），再次访问这个缓存页，才会被挪动到热数据区域的链表头部去。

image.png
此时预读机制和全表扫描加载进来的一大堆缓存页，都在冷数据区域里，跟热数据区域里的频繁访问的缓存页，是没关系的。如果缓存页不够，直接就是可以找到LRU链表中的冷数据区域的尾部的缓存页淘汰。
image.png

LRU链表的热数据区域的优化
LRU链表的热数据区域的访问规则被优化了一下，即你只有在热数据区域的后3/4部分的缓存页被访问了，才会给你移动到链表头部去。如果你是热数据区域的前面1/4的缓存页被访问，他是不会移动到链表头部去的。这样的话，他就可以尽可能的减少链表中的节点移动了。

定时把LRU尾部的部分缓存页刷入磁盘
并不是在缓存页满的时候，才会挑选LRU冷数据区域尾部的几个缓存页刷入磁盘，而是有一个后台线程，他会运行一个定时任务，这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页，刷入磁盘里去，清空这几个缓存页，把他们加入回free链表去！

image.png

flush链表中的一些缓存页定时刷入磁盘
这个后台线程同时也会在MySQL不怎么繁忙的时候，找个时间把flush链表中的缓存页都刷入磁盘中，这样被你修改过的数据，迟早都会刷入磁盘的！只要flush链表中的一波缓存页被刷入了磁盘，那么这些缓存页也会从flush链表和lru链表中移除，然后加入到free链表中去！

MySQL的Buffer Pool缓存机制的运行原理总结
一边不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动。
另外一边，你的后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页，刷入磁盘中来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。

10. MySQL的生产优化经验

1. 多个Buffer Pool优化并发能力
一般来说，MySQL默认的规则是，如果你给Buffer Pool分配的内存小于1GB，那么最多就只会给你一个BufferPool。
但是如果你的机器内存很大，那么你必然会给Buffer Pool分配较大的内存，比如给他个8G内存，那么此时你是同时可以设置多个Buffer Pool的，比如说下面的MySQL服务器端的配置。

[server]
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

MySQL在运行的时候就会有4个Buffer Pool了！每个Buffer Pool负责管理一部分的缓存页和描述数据块，有自己独立的free、flush、lru等链表。一旦你有了多个buffer pool之后，你的多线程并发访问的性能就会得到成倍的提升，因为多个线程可以在不同的buffer pool中加锁和执行自己的操作，大家可以并发来执行了！

image.png

2. 基于chunk机制在运行期间，动态调整buffer pool大小
buffer pool的真实的数据结构，是可以由多个buffer pool组成的，每个buffer pool是多个chunk组成的。他的大小是innodb_buffer_pool_chunk_size参数控制的，默认值就是128MB。

image.png
比如buffer pool总大小是8GB，现在要动态加到16GB，那么此时只要申请一系列的128MB大小的chunk就可以了，只要每个chunk是连续的128MB内存就行了。然后把这些申请到的chunk内存分配给buffer pool就行了。有个这个chunk机制，此时并不需要额外申请16GB的连续内存空间，然后还要把已有的数据进行拷贝。

2. 基于机器配置来合理设置Buffer Pool

1. 通常来说，我们建议一个比较合理的、健康的比例，是给buffer pool设置你的机器内存的50%~60%左右。
2. buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数。假设你的buffer pool的数量是16个，，那么此时chunk大小 * buffer pool的数量 = 16 * 128MB =2048MB，然后buffer pool总大小如果是20GB，此时buffer pool总大小就是2048MB的10倍，这就符合规则了。

3. SHOW ENGINE INNODB STATUS
（1）Total memory allocated，这就是说buffer pool最终的总大小是多少
（2）Buffer pool size，这就是说buffer pool一共能容纳多少个缓存页
（3）Free buffers，这就是说free链表中一共有多少个空闲的缓存页是可用的
（4）Database pages和Old database pages，就是说lru链表中一共有多少个缓存页，以及冷数据区域里的缓存页数量
（5）Modified db pages，这就是flush链表中的缓存页数量
（6）Pending reads和Pending writes，等待从磁盘上加载进缓存页的数量，还有就是即将从lru链表中刷入磁盘的数量、即将从flush链表中刷入磁盘的数量
（7）Pages made young和not young，这就是说已经lru冷数据区域里访问之后转移到热数据区域的缓存页的数量，以及在lru冷数据区域里1s内被访问了没进入热数据区域的缓存页的数量
（8）youngs/s和not youngs/s，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量
（9）Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，这里就是说已经读取、创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量
（10）Buffer pool hit rate xxx / 1000，这就是说每1000次访问，有多少次是直接命中了buffer pool里的缓存的
（11）young-making rate xxx / 1000 not xx / 1000，每1000次访问，有多少次访问让缓存页从冷数据区域移动到
了热数据区域，以及没移动的缓存页数量
（12）LRU len：这就是lru链表里的缓存页的数量
（13）I/O sum：最近50s读取磁盘页的总数
（14）I/O cur：现在正在读取磁盘页的数量

4. Buffer Pool数据结构和工作原理总结
数据库在生产环境运行的时候，必须根据机器的内存设置合理的buffer pool的大小，然后设置buffer pool的数量，这样的话，可以尽可能的保证你的数据库的高性能和高并发能力。
然后在线上运行的时候，buffer pool是有多个的，每个buffer pool里多个chunk但是共用一套链表数据结构，然后执行crud的时候，就会不停的加载磁盘上的数据页到缓存页里来，然后会查询和更新缓存页里的数据，同时维护一系列的链表结构。然后后台线程定时根据lru链表和flush链表，去把一批缓存页刷入磁盘释放掉这些缓存页，同时更新free链表。如果执行crud的时候发现缓存页都满了，没法加载自己需要的数据页进缓存，此时就会把lru链表冷数据区域的缓存页刷入磁盘，然后加载自己需要的数据页进来。

11. 一行数据在磁盘上是如何存储的？

一行数据的存储格式大致如下：

image.png

1. 变长字段存放格式：倒序存储每个变长字段的16进制值。
2. NULL值存储格式：倒序存储所有允许值为NULL的字段，每个字段都以一个二进制bit位的值进行存储，如果bit值是1说明是NULL，如果bit值是0说明不是NULL。一般起码是8个bit位的倍数，如果不足8个bit位
   就高位补0。

12. 用于存放磁盘上的多行数据的数据页到底长个什么样子？

image.png

1. 每个数据页，实际上是默认有16kb的大小。
2. 一个数据页拆分成了很多个部分，大体上来说包含了文件头、数据页头、最小记录和最大记录、多个数据行、空闲空间、数据页目录、文件尾部。
3. 其中文件头占据了38个字节，数据页头占据了56个字节，最大记录和最小记录占据了26个字节，数据行区域的大小是不固定的，空闲区域的大小也是不固定的，数据页目录的大小也是不固定的，然后文件尾部占据8个字节。

13. 表空间以及数据区

我们平时创建的那些表都是有对应的表空间的，每个表空间就是对应了磁盘上的数据文件（表名.ibd），在表空间里有很多组数据区，一组数据区是256个数据区，每个数据区包含了64个数据页，是1mb。然后表空间的第一组数据区的第一个数据区的头三个数据页，都是存放特殊信息的；表空间的其他组数据区的第一个数据区的头两个数据页，也都是存放特殊信息的。大家今天只要了解到这个程度就可以了。

image.png

14. 磁盘随机读写和磁盘顺序读写两种机制

1. 磁盘随机读写

   
   image.png
   

   图里有一个磁盘文件的示意，里面有很多数据页，然后你可能需要在一个随机的位置读取一个数据页到缓存，这就是磁盘随机读。

2. 磁盘顺序读写

   
   image.png
   

   所谓顺序写，就是说在一个磁盘日志文件里，一直在末尾追加日志。写redo log日志的时候，其实是不停的在一个日志文件末尾追加日志的，这就是磁盘顺序写。
   磁盘顺序写的性能其实是很高的，某种程度上来说，几乎可以跟内存随机读写的性能差不多，尤其是在数据库里其实也用了os cache机制，就是redo log顺序写入磁盘之前，先是进入os cache，就是操作系统管理的内存缓存里。

15. 为什么redo日志刷磁盘优于数据页刷磁盘？

image.png

引入一个redo log机制，意义在于提交事务的时候，绝对是保证把你对缓存页做的修改以日志的形式，写入到redo log日志文件里去的。

redo log日志格式.png

1. 如果你把修改过的缓存页都刷入磁盘，这首先缓存页一个就是16kb，数据比较大，刷入磁盘比较耗时，而且你可能就修改了缓存页里的几个字节的数据，难道也把完整的缓存页刷入磁盘吗？
2. 而且你缓存页刷入磁盘是随机写磁盘，性能是很差的，因为他一个缓存页对应的位置可能在磁盘文件的一个随机位置，比如偏移量为45336这个地方。
3. 但是如果是写redo log，第一个一行redo log可能就占据几十个字节，就包含表空间好、数据页号、磁盘文件偏移量、更新值，这个写入磁盘速度很快。
4. 此外，redo log写日志，是顺序写入磁盘文件，每次都是追加到磁盘文件末尾去，速度也是很快的。
   所以你提交事务的时候，用redo log的形式记录下来你做的修改，性能会远远超过刷缓存页的方式，这也可以让你的数据库的并发能力更强。

16. redo log block

1. 对于redo log也不是单行单行的写入日志文件的，他是用一个redo log block来存放多个单行日志的。
   一个redo log block是512字节，这个redo log block的512字节分为3个部分，一个是12字节的header块头，一个是496字节的body块体，一个是4字节的trailer块尾。
2. 对于我们的redo log而言，他确实是不停的追加写入到redo log磁盘文件里去的，但是其实每一个redo log都是写入到文件里的一个redo log block里去的，一个block最多放496字节的redo log日志。

3. 要写入磁盘的redo log，其实应该是先进入到redo log block这个数据结构里去的，等内存里的一个redo log block的512字节都满了，再一次性把这个redo log block写入磁盘文件。

   
   redo log block数据结构.png

redo log block与redo log文件的关系.png

17. redo log buffer

redo log buffer其实就是MySQL在启动的时候，就跟操作系统申请的一块连续内存空间，大概可以认为相当于是buffer pool吧。那个buffer pool是申请之后划分了N多个空的缓存页和一些链表结构，让你把磁盘上的数据页加载到内存里来的。redo log buffer也是类似的，他是申请出来的一片连续内存，然后里面划分出了N多个空的redo log block。
其实在我们平时执行一个事务的过程中，每个事务会有多个增删改操作，那么就会有多个redo log，这多个redo log就是一组redo log，其实每次一组redo log都是先在别的地方暂存，然后都执行完了，再把一组redo log给写入到redo log buffer的block里去的。

image.png

18. redo log buffer中的缓冲日志，到底什么时候可以写入磁盘？

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刷到磁盘文件里去。

19. insert语句的undo log回滚日志结构

image.png

1. 主键的各列长度和值，意思就是你插入的这条数据的主键的每个列，他的长度是多少，具体的值是多少。即使你没有设置主键，MySQL自己也会给你弄一个row_id作为隐藏字段，做你的主键；
2. 表id，插入一条数据必然是往一个表里插入数据的，那当然得有一个表id，记录下来是在哪个表里插入的数据了。
3. undo log日志编号，每个undo log日志都是有自己的编号的。在一个事务里会有多个SQL语句，就会有多个undo log日志，在每个事务里的undo log日志的编号都是从0开始的，然后依次递增。
4. undo log日志类型，就是TRX_UNDO_INSERT_REC，insert语句的undo log日志类型就是这个东西。
5. undo log日志的结束位置，就是undo log日志结束的位置是什么。

万一要是你现在在buffer pool的一个缓存页里插入了一条数据了，执行了insert语句，然后你写了一条上面的那种undo log，现在事务要是回滚了，你直接就把这条insert语句的undo log拿出来。
然后在undo log里就知道在哪个表里插入的数据，主键是什么，直接定位到那个表和主键对应的缓存页，从里面删除掉之前insert语句插入进去的数据就可以了，这样就可以实现事务回滚的效果了！

20. 多个事务并发更新以及查询数据产生的问题

多事务并发执行场景.png

实际上会涉及到脏写、脏读、不可重复读、幻读，四种问题。

1. 脏写

   
   脏写.png
   

   本质是事务B去修改了事务A修改过的值，但是此时事务A还没提交，所以事务A随时会回滚，导致事务B修改的值也没了。

2. 脏读

   
   脏读.png
   

   本质是事务B去查询了事务A修改过的数据，但是此时事务A还没提交，所以事务A随时会回滚导致事务B再次查询就读不到刚才事务A修改的数据了！

   无论是脏写还是脏读，都是因为一个事务去更新或者查询了另外一个还没提交的事务更新过的数据。因为另外一个事务还没提交，所以他随时可能会反悔会回滚，那么必然导致你更新的数据就没了，或者你之前查询到的数据就没了，这就是脏写和脏读两种坑爹场景

3. 不可重复读

   
   不可重复读.png
   

   一个事务多次查询一条数据，结果每次读到的值都不一样，这个过程中可能别的事务会修改这条数据的值，而且修改值之后事务都提交了，结果导致人家每次查到的值都不一样，都查到了提交事务修改过的值，这就是所谓的不可重复读。

4. 幻读

   
   幻读.png
   

   幻读指的就是你一个事务用一样的SQL多次查询，结果每次查询都会发现查到了一些之前没看到过的数据。

21. 事务的四个隔离级别

1. read uncommitted（读未提交）：可以避免脏写。
2. read committed（读已提交）：可以避免脏写和脏读。
3. repeatable read（可重复读）：可以避免脏读、脏写和不可重复读，不能避免幻读。
4. serializable（串行化）：不允许你多个事务并发执行，四个问题都可以避免。

MySQL默认的事务隔离级别是RR（可重复读），而且MySQL的RR级别是可以避免幻读发生。也就是说，MySQL里执行的事务，默认情况下不会发生脏写、脏读、不可重复读和幻读的问题。

如何修改MySQL隔离级别？

修改MySQL隔离级别命令.png

Spring中默认隔离级别与MySQL一致，Spring中如何修改？

Spring修改事务隔离级别.png

22. undo log版本链

undo log版本链.png
多个事务串行执行的时候，每个人修改了一行数据，都会更新隐藏字段txr_id和roll_pointer，同时之前多个数据快照对应的undo log，会通过roll_pinter指针串联起来，形成一个重要的版本链！

23. 基于undo log多版本链条实现的ReadView机制

简单来说，就是执行一个事务的时候，就生成一个ReadView，里面比较关键的东西有4个：

• m_ids：这个就是说此时有哪些事务在MySQL里执行还没提交的；
• min_trx_id：就是m_ids里最小的值；
• max_trx_id：这是说mysql下一个要生成的事务id，就是最大事务id；
• creator_trx_id：就是你这个事务的id

示例：

1. 两个事务并发过来执行了，一个是事务A（id=45），一个是事务B（id=59），事务B是要去更新这行数
   据的，事务A是要去读取这行数据的值的。
2. 现在事务A直接开启一个ReadView，这个ReadView里的m_ids就包含了事务A和事务B的两个id，45和59，然后min_trx_id就是45，max_trx_id就是60，creator_trx_id就是45，是事务A自己。

3. 这个时候事务A第一次查询这行数据，会走一个判断，就是判断一下当前这行数据的txr_id是否小于ReadView中的min_trx_id，此时发现txr_id=32，是小于ReadView里的min_trx_id就是45的，说明你事务开启之前，修改这行数据的事务早就提交了，所以此时可以查到这行数据，如下图所示。

   
   image.png

4. 事务B更新数据，事务A再次查询，此时数据行里的txr_id=59，那么这个txr_id是大于ReadView里的min_txr_id(45)，同时小于ReadView里的max_trx_id（60）的，说明更新这条数据的事务，很可能就跟自己差不多同时开启的，于是会看一下这个txr_id=59，是否在ReadView的m_ids列表里？果然，在ReadView的m_ids列表里，有45和59两个事务id，直接证实了，这个修改数据的事务是跟自己同一时段并发执行然后提交的，所以对这行数据是不能查询的！

   
   image.png

5. 事务A自己更新了这行数据的值，改成值A，trx_id修改为45，同时保存之前事务B修改的值的快照。此时事务A来查询这条数据的值，会发现这个trx_id=45，居然跟自己的ReadView里的creator_trx_id（45）是一样的，说明这行数据就是自己修改的啊！自己修改的值当然是可以看到的了！

   
   image.png

6. 接着在事务A执行的过程中，突然开启了一个事务C，这个事务的id是78，然后他更新了那行数据的值为值C，还提交了。这个时候事务A再去查询，会发现当前数据的trx_id=78，大于了自己的ReadView中的max_trx_id（60），说明是这个事务A开启之后，然后有一个事务更新了数据，自己当然是不能看到的了！

   
   image.png

通过undo log多版本链条，加上你开启事务时候生产的一个ReadView，然后再有一个查询的时候，根据ReadView进行判断的机制，你就知道你应该读取哪个版本的数据。

24. RC隔离级别是如何实现的？

关键点在于每次查询都生成新的ReadView，那么如果在你这次查询之前，有事务修改了数据还提交了，你这次查询生成的ReadView里，那个m_ids列表当然不包含这个已经提交的事务了，既然不包含已经提交的事务了，那么当然可以读到人家修改过的值了。

25. 总结MVCC机制（multi-version concurrent control）

首先我们先要明白，多个事务并发运行的时候，同时读写一个数据，可能会出现脏写、脏读、不可重复读、幻读几个问题。
脏写，就是两个事务都更新一个数据，结果有一个人回滚了把另外一个人更新的数据也回滚没了。
脏读，就是一个事务读到了另外一个事务没提交的时候修改的数据，结果另外一个事务回滚了，下次读就读不到了。
不可重复读，就是多次读一条数据，别的事务老是修改数据值还提交了，多次读到的值不同。
幻读，就是范围查询，每次查到的数据不同，有时候别的事务插入了新的值，就会读到更多的数据。

针对这些问题，所以才有RU、RC、RR和串行四个隔离级别。
RU隔离级别，就是可以读到人家没提交的事务修改的数据，只能避免脏写问题；
RC隔离级别，可以读到人家提交的事务修改过的数据，可以避免脏写和脏读问题；
RR是不会读到别的已经提交事务修改的数据，可以避免脏读、脏写和不可重复读的问题；
串行是让事务都串行执行，可以避免所有问题。

然后MySQL实现MVCC机制的时候，是基于undo log多版本链条+ReadView机制来做的，默认的RR隔离级别，就是基于这套机制来实现的，依托这套机制实现了RR级别，除了避免脏写、脏读、不可重复读，还能避免幻读问题。因此一般来说我们都用默认的RR隔离级别就好了。

26. 如何通过加锁避免脏写？

在多个事务并发更新数据的时候，都是要在行级别加独占锁的，这就是行锁，独占锁都是互斥的，所以不可能发生脏写问题，一个事务提交了才会释放自己的独占锁，唤醒下一个事务执行。
依靠锁机制让多个事务更新一行数据的时候串行化，避免同时更新一行数据，从而避免脏写的发生。

1. 事务A先更新，事务就会创建一个锁，里面包含了自己的trx_id和等待状态，然后把锁跟这行数据关联在一起。

   
   image.png

2. 事务B来更新，发现已被事务A加锁，事务B也会生成一个锁数据结构，里面有他的trx_id，还有自己的等待状态，但是他因为是在排队等待，所以他的等待状态就是true。

   
   image.png

3. 事务A这个时候更新完了数据，就会把自己的锁给释放掉了。这个时候，就会把事务B的锁里的等待状态修改为false，然后唤醒事务B继续执行，此时事务B就获取到锁了。

   
   image.png

27. 共享锁和互斥锁

更新数据的时候必然加独占锁，独占锁和独占锁是互斥的，此时别人不能更新；但是此时你要查询，默认是不加锁的，走mvcc机制读快照版本，但是你查询是可以手动加共享锁的，共享锁和独占锁是互斥的，但是共享锁和共享锁是不互斥的。

image.png
查询时手动加共享锁：select * from table lock in share mode。
查询时手动加互斥锁：select * from table for update。这个意思就是，查出来数据以后还要更新，此时加独占锁了，其他闲杂人等，都不要更新这个数据了。

不是太建议在数据库粒度去通过行锁实现复杂的业务锁机制，而更加建议通过redis、zookeeper来用分布式锁实现复杂业务下的锁机制，其实更为合适一些。
为什么呢？因为如果你把分布式系统里的复杂业务的一些锁机制依托数据库查询的时候，在SQL语句里加共享锁或者独占锁，会导致这个加锁逻辑隐藏在SQL语句里，在你的Java业务系统层面其实是非常的不好维护的，所以一般是不建议这么做的。
比较正常的情况而言，其实还是多个事务并发运行更新一条数据，默认加独占锁互斥，同时其他事务读取基于mvcc机制进行快照版本读，实现事务隔离。

28. 磁盘数据页的存储结构

image.png

数据页之间是组成双向链表的，然后数据页内部的数据行是组成单向链表的，而且数据行是根据主键从小到大排序的。

数据页存储数据.png

29. 没有索引，会发生全表扫描

每个数据页里都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是被分散存储到不同的槽位里去的，所以实际上每个数据页的目录里，就是这个页里每个主键跟所在槽位的映射关系。

image.png

1. 假设你要是没有建立任何索引，那么无论是根据主键查询，还是根据其他字段来条件查询，实际上都没有什么取巧的办法。
2. 一个表里所有数据页都是组成双向链表的，直接从第一个数据页开始遍历所有数据页，从第一个数据页开始，得先把第一个数据页从磁盘上读取到内存buffer pool的缓存页里来。
3. 然后就在第一个数据页对应的缓存页里，假设是根据主键查找的，可以在数据页的页目录里二分查找，假设你要是根据其他字段查找的，只能是根据数据页内部的单向链表来遍历查找。
4. 如果没有查到数据，只能根据双向链表继续加载下一个数据页到缓存页里来了，以此类推，循环往复。这就是所谓的全表扫描。

30. 插入数据时导致页分裂

1. 索引运作的一个核心基础就是要求后一个数据页的主键值都大于前面一个数据页的主键值；

2. 万一主键值都是自己设置的，那么在增加一个新的数据页的时候，实际上会把前一个数据页里主键值较大的，挪动到新的数据页里来，然后把你新插入的主键值较小的数据挪动到上一个数据页里去，保证新数据页里的主键值一定都比上一个数据页里的主键值大。这就是页分裂。

   
   image.png

31. 索引的页存储物理结构，是如何用B+树来实现的？

表的实际数据是存放在数据页里的，然后表的索引其实也是存放在页里的，此时索引放在页里之后，就会有索引页。
当为一个表的主键建立起来索引之后，其实这个主键的索引就是一颗B+树，然后当你要根据主键来查数据的时候，直接就是从B+树的顶层开始二分查找，一层一层往下定位，最终一直定位到一个数据页里，在数据页内部的目录里二分查找，找到那条数据。

image.png

32. 聚族索引

如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引！

image.png
其实在InnoDB存储引擎里，你在对数据增删改的时候，就是直接把你的数据页放在聚簇索引里的，数据就在聚簇索引里，聚簇索引就包含了数据！比如你插入数据，那么就是在数据页里插入数据。
如果你的数据页开始进行页分裂了，他此时会调整各个数据页内部的行数据，保证数据页内的主键值都是有顺序的，下一个数据页的所有主键值大于上一个数据页的所有主键值。
同时在页分裂的时候，会维护你的上层索引数据结构，在上层索引页里维护你的索引条目，不同的数据页和最小主键值。
然后如果你的数据页越来越多，一个索引页放不下了，此时就会再拉出新的索引页，同时再搞一个上层的索引页，上层索引页里存放的索引条目就是下层索引页页号和最小主键值。
按照这个顺序，以此类推，如果你的数据量越大，此时可能就会多出更多的索引页层级来，不过说实话，一般索引页里可以放很多索引条目，所以通常而言，即使你是亿级的大表，基本上大表里建的索引的层级也就三四层而已。
这个聚簇索引默认是按照主键来组织的，所以你在增删改数据的时候，一方面会更新数据页，一方面其实会给你自动维护B+树结构的聚簇索引，给新增和更新索引页，这个聚簇索引是默认就会给你建立的。

33. 非聚族索引（二级索引）

image.png

搜索原理同聚族索引一样，只不过找到叶子节点也仅仅可以找到对应的主键值，而找不到这行数据完整的所有字段，需要回表查询。

34. 总结

正常我们在一个表里灌入数据的时候，都会基于主键给我们自动建立聚簇索引，这个聚簇索引大概看起来就是下面的样子。

image.png

随着我们不停的在表里插入数据，他就会不停的在数据页里插入数据，然后一个数据页放满了就会分裂成多个数据页，这个时候就需要索引页去指向各个数据页。
然后如果数据页太多了，那么索引页里里的数据页指针也就会太多了，索引页也必然会放满的，此时索引页也会分裂成多个，再形成更上层的索引页。
默认情况下MySQL给我们建立的聚簇索引都是基于主键的值来组织索引的，聚簇索引的叶子节点都是数据页，里面放的就是我们插入的一行一行的完整的数据了！
在一个索引B+树中，他有一些特性，那就是数据页/索引页里面的记录都是组成一个单向链表的，而且是按照数据大小有序排列的；然后数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据大小有序排列的，所以其实B+树索引是一个完全有序的数据结构，无论是页内还是页之间。
正是因为这个有序的B+树索引结构，才能让我们查找数据的时候，直接从根节点开始按照数据值大小一层一层往下找，这个效率是非常高的。
然后如果是针对主键之外的字段建立索引的话，实际上本质就是为那个字段的值重新建立另外一颗B+树索引，那个索引B+树的叶子节点，存放的都是数据页，里面放的都是你字段的值和主键值，然后每一层索引页里存放的都是下层页的引用，包括页内的排序规则，页之间的排序规则，B+树索引的搜索规则，都是一样的。
但是唯一要清晰记住的一点是，假设我们要根据其他字段的索引来搜索，那么只能基于其他字段的索引B+树快速查找到那个值所对应的主键，接着再次做回表查询，基于主键在聚簇索引的B+树里，重新从根节点开始查找那个主键值，找到主键值对应的完整数。

35. 几个最常见和最基本的索引使用规则

1. 全值匹配
2. 最左侧列匹配
3. 最左前缀匹配原则
4. 范围查找规则：where语句里如果有范围查询，那只有对联合索引里最左侧的列进行范围查询才能用到索引！
5. 等值匹配+范围匹配的规则

综上所述，一般我们如果写SQL语句，都是用联合索引的最左侧的多个字段来进行等值匹配+范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索，这就要写符合规则的SQL语句，才能用上我们建立好的联合索引！

36. 如何在order by语句排序的时候用上索引？

1. order by的联合字段建立索引；
2. 多个字段排序同时使用升序或者降序；
3. 要是你order by语句里有的字段不在联合索引里，或者是你对order by语句里的字段用了复杂的函数，这些也不能使用索引去进行排序了。可能会导致文件排序FileSort

37. 如何在group by语句排序的时候用上索引？

这个group by和order by用上索引的原理和条件都是差不多的，本质都是在group by和order by之后的字段顺序和联合索引中的从最左侧开始的字段顺序一致，然后就可以充分利用索引树里已经完成排序的特性，快速的根据排序好的数据执行后续操作了。
这样就不再需要针对杂乱无章的数据利用临时磁盘文件加上部分内存数据结构进行耗时耗力的现场排序和分组，那真是速度极慢，性能极差的。

38. 回表查询对性能的损害以及覆盖索引是什么？

回表查询：
select * from table order by xx1,xx2,xx3的语句，可能你就是得从联合索引的索引树里按照顺序取出来所有数据，接着对每一条数据都走一个主键的聚簇索引的查找，其实性能也是不高的。
有的时候MySQL的执行引擎甚至可能会认为，你要是类似select * from table order by xx1,xx2,xx3的语句，相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索引了，直接全表扫描得了，这样还就扫描一个索引而已。
但是你如果要是select * from table order by xx1,xx2,xx3 limit 10这样的语句，那执行引擎就知道了，你先扫描联合索引的索引树拿到10条数据，接着对10条数据在聚簇索引里查找10次就可以了，那么就还是会走联合索引的。

覆盖索引：
覆盖索引不是一种索引，他就是一种基于索引查询的方式。针对类似select xx1,xx2,xx3 from table order by xx1,xx2,xx3这样的 语句，这种情况下，你仅仅需要联合索引里的几个字段的值，那么其实就只要扫描联合索引的索引树就可以了，不需要回表去聚簇索引里找其他字段了。

总结：
尽可能还是在SQL里指定你仅仅需要的几个字段，不要搞一个select *把所有字段都拿出来，甚至最好是直接
走覆盖索引的方式，不要去回表到聚簇索引。
即使真的要回表到聚簇索引，那你也尽可能用limit、where之类的语句限定一下回表到聚簇索引的次数，就从联合索引里筛选少数数据，然后再回表到聚簇索引里去，这样性能也会好一些。

38. 设计索引需要考虑的因素

1. 保证你的每个SQL语句的where、order by和group by都可以用上索引。
2. 尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
3. 对于字段长度较长，必须建立索引但又不想浪费空间的情况，可以考虑建立前缀索引。仅仅包含部分字符到索引树里去，where查询还是可以用的，但是order by和group by就用不上了。
4. 设计索引别太多，建议两三个联合索引就应该覆盖掉你这个表的全部查询了。因为插入的数据值可能根本不是按照顺序来的，很可能会导致索引树里的某个页就会自动分裂，这个页分裂的过程就很耗费时间。
5. 主键一定是自增的，别用UUID之类的，因为主键自增，那么起码你的聚簇索引不会频繁的分裂，主键值都是有序的，就会自然的新增一个页而已，但是如果你用的是UUID，那么也会导致聚簇索引频繁的页分裂。