MySQL从入门到入土

01_系统如何跟MySQL打交道

1. MySQL驱动到底是什么东西?

我们如果要在Java系统中去访问一个MySQL数据库,必须得在系统的依赖中加入一个MySQL驱动,有了这个MySQL驱动才能跟MySQL数据库建立连接,然后执行各种各样的SQL语句。

如果我们要访问数据库,必须得跟数据库建立一个网络连接,那么这个连接由谁来建立呢?其实答案就是这个MySQL驱动,他会在底层跟数据库建立网络连接,有网络连接,接着才能去发送请求给数据库服务器!然后当跟数据库之间有了网络连接之后,代码才能基于这个连接去执行各种各样的增删改查SQL语句。

2. 数据库连接池到底是用来干什么的?

一个Java系统难道只会跟数据库建立一个连接吗?

这个肯定是不行的,因为我们要明白一个道理,假设我们用Java开发了一个Web系统,是部署在Tomcat中的,那么Tomcat本身肯定是有多个线程来并发的处理同时接收到的多个请求的。这个时候,如果Tomcat中的多个线程并发处理多个请求的时候,都要去抢夺一个连接去访问数据库的话,那效率肯定是很低下的。那么如果Tomcat中的每个线程在每次访问数据库的时候,都基于MySQL驱动去创建一个数据库连接,然后执行SQL语句,然后执行完之后再销毁这个数据库连接,这样行不行呢?可能Tomcat中上百个线程会并发的频繁创建数据库连接,执行SQL语句,然后频繁的销毁数据库连接。这也是非常不好的,因为每次建立一个数据库连接都很耗时,好不容易建立好了连接,执行完了SQL语句,你还把数据库连接给销毁了,下一次再重新建立数据库连接,那肯定是效率很低下的。

所以一般我们必须要使用一个数据库连接池,也就是说在一个池子里维持多个数据库连接,让多个线程使用里面的不同的数据库连接去执行SQL语句,然后执行完SQL语句之后,不要销毁这个数据库连接,而是把连接放回池子里,后续还可以继续使用。基于这样的一个数据库连接池的机制,就可以解决多个线程并发的使用多个数据库连接去执行SQL语句的问题,而且还避免了数据库连接使用完之后就销毁的问题。

3. MySQL数据库的连接池是用来干什么的?

我们任何一个系统都会有一个数据库连接池去访问数据库,也就是说这个系统会有多个数据库连接,供多线程并发的使用。同时我们可能会有多个系统同时去访问一个数据库,这都是有可能的。肯定会有很多系统要与MySQL数据库建立很多个连接,那么MySQL也必然要维护与系统之间的多个连接,所以MySQL架构体系中的第一个环节,就是连接池。

02_为了执行SQL语句,MySQL用了什么样的架构设计

我们的系统采用数据库连接池的方式去并发访问数据库,然后数据库自己其实也会维护一个连接池,其中管理了各种系统跟这台数据库服务器建立的所有连接。

当我们的系统只要能从数据库连接池获取到一个数据库连接之后,我们就可以执行增删改查的SQL语句了。

1. 一个不变的原则:网络连接必须让线程来处理

现在假设我们的数据库服务器的连接池中的某个连接接收到了网络请求,假设就是一条SQL语句,那么谁负责从这个连接中去监听网络请求?谁负责从网络连接里把请求数据读取出来?网络连接必须得分配给一个线程去进行处理,由一个线程来监听请求以及读取请求数据,比如从网络连接中读取和解析出来一条我们的系统发送过去的SQL语句。

2. SQL接口:负责处理接收到的SQL语句

当MySQL内部的工作线程从一个网络连接中读取出来一个SQL语句之后,此时会如何来执行这个SQL语句呢?其实SQL是一项伟大的发明,他发明了简单易用的数据读写的语法和模型,即使不会技术,也能轻松学会使用SQL语句。

但如果你要去执行这个SQL语句,去完成底层数据的增删改查,那这就是一项极度复杂的任务了!

所以MySQL内部首先提供了一个组件,就是SQL接口(SQL Interface),他是一套执行SQL语句的接口,专门用于执行我们发送给MySQL的那些增删改查的SQL语句。因此MySQL的工作线程接收到SQL语句之后,就会转交给SQL接口去执行。

3. 查询解析器:让MySQL能看懂SQL语句

接着下一个问题来了,SQL接口怎么执行SQL语句呢?你直接把SQL语句交给MySQL,他能看懂和理解这些SQL语句吗?这个SQL语句,我们用人脑是直接就可以处理一下,只要懂SQL语法的人,立马大家就知道他是什么意思,但是MySQL自己本身也是一个系统,是一个数据库管理系统,他没法直接理解这些SQL语句!所以此时有一个关键的组件要出场了,那就是查询解析器。

这个查询解析器(Parser)就是负责对SQL语句进行解析的。所谓的SQL解析,就是按照既定的SQL语法,对我们按照SQL语法规则编写的SQL语句进行解析,然后理解这个SQL语句要干什么事情。

4. 查询优化器:选择最优的查询路径

当我们通过解析器理解了SQL语句要干什么之后,接着会找查询优化器(Optimizer)来选择一个最优的查询路径。

查询优化器会针对你编写的几十行、几百行甚至上千行的复杂SQL语句生成查询路径树,然后从里面选择一条最优的查询路径出来。相当于他会告诉你,你应该按照一个什么样的步骤和顺序,去执行哪些操作,然后一步一步的把SQL语句就给完成了。

5. 调用存储引擎接口,真正执行SQL语句

最后一步,就是把查询优化器选择的最优查询路径,也就是你到底应该按照一个什么样的顺序和步骤去执行这个SQL语句的计划,把这个计划交给底层的存储引擎去真正的执行。这个存储引擎是MySQL的架构设计中很有特色的一个环节。

真正在执行SQL语句的时候,要不然是更新数据,要不然是查询数据,那么数据存放在哪里?数据库自己就是一个编程语言写出来的系统而已,然后启动之后也是一个进程,执行他里面的各种代码,也就是我们上面所说的那些东西。所以对数据库而言,我们的数据要不然是放在内存里,要不然是放在磁盘文件里,没什么特殊的地方!那么现在问题来了,我们已经知道一个SQL语句要如何执行了,但是我们现在怎么知道哪些数据在内存里?哪些数据在磁盘里?我们执行的时候是更新内存的数据?还是更新磁盘的数据?我们如果更新磁盘的数据,是先查询哪个磁盘文件,再更新哪个磁盘文件?所以这个时候就需要存储引擎了,存储引擎其实就是执行SQL语句的,他会按照一定的步骤去查询内存缓存数据,更新磁盘数据,查询磁盘数据,等等,执行诸如此类的一系列的操作。

MySQL的架构设计中,SQL接口、SQL解析器、查询优化器其实都是通用的,他就是一套组件而已。但是存储引擎的话,他是支持各种各样的存储引擎的,比如我们常见的InnoDB、MyISAM、Memory等等,我们是可以选择使用哪种存储引擎来负责具体的SQL语句执行的。当然现在MySQL一般都是使用InnoDB存储引擎的。

6. 执行器:根据执行计划调用存储引擎的接口

存储引擎可以帮助我们去访问内存以及磁盘上的数据,那么是谁来调用存储引擎的接口呢?

执行器会根据优化器选择的执行方案,去调用存储引擎的接口按照一定的顺序和步骤,就把SQL语句的逻辑给执行了。执行器就会去根据我们的优化器生成的一套执行计划,然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划,大致就是不停的更新或者提取一些数据出来。

03_初步了解InnoDB存储引擎的架构设计

1. 更新语句在MySQL中是如何执行的?

MySQL最常用的就是InnoDB存储引擎。首先系统通过一个数据库连接发送到了MySQL上,然后经过SQL接口、解析器、优化器、执行器几个环节,解析SQL语句,生成执行计划,接着去由执行器负责这个计划的执行,调用InnoDB存储引擎的接口去执行。

2. InnoDB的重要内存结构:缓冲池

InnoDB存储引擎中有一个非常重要的放在内存里的组件,就是缓冲池(Buffer Pool),这里面会缓存很多的数据,以便于以后在查询的时候,万一你要是内存缓冲池里有数据,就可以不用去查磁盘了。所以当我们的InnoDB存储引擎要执行更新语句的时候 ,他其实会先将这一行数据看看是否在缓冲池里,如果不在的话,那么会直接从磁盘里加载到缓冲池里来,而且接着还会对这行记录加独占锁。因为在我们更新这一行数据的时候,肯定是不允许别人同时更新的,所以必须要对这行记录加独占锁。

3. undo日志文件:如何让你更新的数据可以回滚?

假设这行数据的name原来是“zhangsan”,现在我们要更新为“xxx”,那么此时我们得先把要更新的原来的值,写入到undo日志文件中去。如果我们执行一个更新语句,要是他是在一个事务里的话,那么事务提交之前我们都是可以对数据进行回滚的,也就是把你更新为“xxx”的值回滚到之前的“zhangsan”去。所以为了考虑到未来可能要回滚数据的需要,这里会把你更新前的值写入undo日志文件。

4. 更新buffer pool中的缓存数据

当我们把要更新的那行记录从磁盘文件加载到缓冲池,同时对他加锁之后,而且还把更新前的旧值写入undo日志文件之后,我们就可以正式开始更新这行记录了,更新的时候,先是会更新缓冲池中的记录,此时这个数据就是脏数据了。这里所谓的更新内存缓冲池里的数据,意思就是把内存里的这行数据的name字段修改为“xxx”。那么为什么说此时这行数据就是脏数据了呢?因为这个时候磁盘上这行数据的name字段还是“zhangsan”,但是内存里这行数据已经被修改了,所以就会叫他是脏数据。

5. Redo Log Buffer:万一系统宕机,如何避免数据丢失?

接着我们来思考一个问题,现在已经把内存里的数据进行了修改,但是磁盘上的数据还没修改。那么此时万一MySQL所在的机器宕机了,必然会导致内存里修改过的数据丢失,这可怎么办呢?这个时候,就必须要把对内存所做的修改写入到一个Redo Log Buffer里去,这也是内存里的一个缓冲区,是用来存放redo日志的。所谓的redo日志,就是记录下来你对数据做了什么修改,比如对这行记录修改了name字段的值为xxx”,这就是一个日志。这个redo日志其实是用来在MySQL突然宕机的时候,用来恢复你更新过的数据的。

6. 如果还没提交事务,MySQL宕机了怎么办?

其实在数据库中,哪怕执行一条SQL语句,其实也可以是一个独立的事务,只有当你提交事务之后,SQL语句才算执行结束。所以这里我们都知道,到目前为止,其实还没有提交事务,那么此时如果MySQL崩溃,必然导致内存里Buffer Pool中的修改过的数据都丢失,同时你写入Redo Log Buffer中的redo日志也会丢失。

那么此时数据丢失要紧吗?其实是不要紧的,因为你一条更新语句,没提交事务,就代表他没执行成功,此时MySQL宕机虽然导致内存里的数据都丢失了,但是你会发现,磁盘上的数据依然还停留在原样子。也就是说那行数据的name字段的值还是老的值,“zhangsan”,所以此时你的这个事务就是执行失败了,没能成功完成更新,你会收到一个数据库的异常。然后当mysql重启之后,你会发现你的数据并没有任何变化。

所以此时如果mysql宕机,不会有任何的问题。

7. 提交事务的时候将redo日志写入磁盘中

接着我们想要提交一个事务了,此时就会根据一定的策略把redo日志从redo log buffer里刷入到磁盘文件里去。此时这个策略是通过innodb_flush_log_at_trx_commit来配置的,他有几个选项。

当这个参数的值为0的时候,那么你提交事务的时候,不会把redo log buffer里的数据刷入磁盘文件的,此时可能你都提交事务了,结果mysql宕机了,然后此时内存里的数据全部丢失。相当于你提交事务成功了,但是由于MySQL突然宕机,导致内存中的数据和redo日志都丢失了。

当这个参数的值为1的时候,你提交事务的时候,就必须把redo log从内存刷入到磁盘文件里去,只要事务提交成功,那么redo log就必然在磁盘里了,那么只要提交事务成功之后,redo日志一定在磁盘文件里,此时你肯定会有一条redo日志说了,“我此时对哪个数据做了一个什么修改,比如name字段修改为xxx了”。然后哪怕此时buffer pool中更新过的数据还没刷新到磁盘里去,此时内存里的数据是已经更新过的“name=xxx”,然后磁盘上的数据还是没更新过的“name=zhangsan”。此时如果说提交事务后处于上图的状态,然后mysql系统突然崩溃了,此时会如何?会丢失数据吗?肯定不会啊,因为虽然内存里的修改成name=xxx的数据会丢失,但是redo日志里已经说了,对某某数据做了修改name=xxx。所以此时mysql重启之后,他可以根据redo日志去恢复之前做过的修改。

最后来看看,如果innodb_flush_log_at_trx_commit参数的值是2呢?他的意思就是,提交事务的时候,把redo日志写入磁盘文件对应的os cache缓存里去,而不是直接进入磁盘文件,可能1秒后才会把os cache里的数据写入到磁盘文件里去。这种模式下,你提交事务之后,redo log可能仅仅停留在os cache内存缓存里,没实际进入磁盘文件,万一此时你要是机器宕机了,那么os cache里的redo log就会丢失,同样会让你感觉提交事务了,结果数据丢了。

其实对于redo日志的三种刷盘策略,通常建议是设置为1。也就是说,提交事务的时候,redo日志必须是刷入磁盘文件里的。这样可以严格的保证提交事务之后,数据是绝对不会丢失的,因为有redo日志在磁盘文件里可以恢复你做的所有修改。如果要是选择0的话,可能你提交事务之后,mysql宕机,那么此时redo日志没有刷盘,导致内存里的redo日志丢失,你提交的事务更新的数据就丢失了;如果要是选择2的话,如果机器宕机,虽然之前提交事务的时候,redo日志进入os cache了,但是还没进入磁盘文件,此时机器宕机还是会导致os cache里的redo日志丢失。

8. MySQL binlog到底是什么东西?

实际上我们说的redo log,他是一种偏向物理性质的重做日志,因为他里面记录的是类似这样的东西,“对哪个数据页中的什么记录,做了个什么修改”。而且redo log本身是属于InnoDB存储引擎特有的一个东西。

而binlog叫做归档日志,他里面记录的是偏向于逻辑性的日志,类似于“对users表中的一行数据做了更新操作,更新以后的值是什么”。binlog不是InnoDB存储引擎特有的日志文件,是属于mysql server自己的日志文件。

9. 提交事务的时候,同时会写入binlog

所以其实我们上一讲讲到,在我们提交事务的时候,会把redo log日志写入磁盘文件中去。然后其实在提交事务的时候,我们同时还会把这次更新对应的binlog日志写入到磁盘文件中去。

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

10. binlog日志的刷盘策略分析

对于binlog日志,其实也有不同的刷盘策略,有一个sync_binlog参数可以控制binlog的刷盘策略。

他的默认值是0,此时你把binlog写入磁盘的时候,其实不是直接进入磁盘文件,而是进入os cache内存缓存。所以跟之前分析的一样,如果此时机器宕机,那么你在os cache里的binlog日志是会丢失的。

如果要是把参数设置为1的话,那么此时会强制在提交事务的时候,把binlog直接写入到磁盘文件里去,那么这样提交事务之后,哪怕机器宕机,磁盘上的binlog是不会丢失的。

11. 基于binlog和redo log完成事务的提交

当我们把binlog写入磁盘文件之后,接着就会完成最终的事务提交,此时会把本次更新对应的binlog文件名称和这次更新的binlog日志在文件里的位置,都写入到redo log日志文件里去,同时在redo log日志文件里写入一个commit标记。在完成这个事情之后,才算最终完成了事务的提交。

12. 最后一步在redo日志中写入commit标记的意义是什么?

说白了,他其实是用来保持redo log日志与binlog日志一致的。如果没有最终的事务commit标记在redo日志里,那么此次事务可以判定为不成功。不会说redo日志文件里有这次更新的日志,但是binlog日志文件里没有这次更新的日志,不会出现数据不一致的问题。必须是在redo log中写入最终的事务commit标记了,然后此时事务提交成功,而且redo log里有本次更新对应的日志,binlog里也有本次更新对应的日志 ,redo log和binlog完全是一致的。

13. 后台IO线程随机将内存更新后的脏数据刷回磁盘

现在我们假设已经提交事务了,此时他已经把内存里的buffer pool中的缓存数据更新了,同时磁盘里有redo日志和binlog日志,都记录了把我们指定的这行数据修改了“name='xxx'”。但是这个时候磁盘上的数据文件里的这行数据的name字段还是等于zhangsan这个旧的值啊!所以MySQL有一个后台的IO线程,会在之后某个时间里,随机的把内存buffer pool中的修改后的脏数据给刷回到磁盘上的数据文件里去。当IO线程把buffer pool里的修改后的脏数据刷回磁盘的之后,磁盘上的数据才会跟内存里一样,都是name=xxx这个修改以后的值了!

在你IO线程把脏数据刷回磁盘之前,哪怕mysql宕机崩溃也没关系,因为重启之后,会根据redo日志恢复之前提交事务做过的修改到内存里去,就是这行数据的name修改为了xxx,然后等适当时机,IO线程自然还是会把这个修改后的数据刷到磁盘上的数据文件里去的。

14. 基于更新数据的流程,总结一下InnoDB存储引擎的架构原理

通过一次更新数据的流程,就可以清晰地看到,InnoDB存储引擎主要就是包含了一些buffer pool、redo log buffer等内存里的缓存数据,同时还包含了一些undo日志文件,redo日志文件等东西,同时mysql server自己还有binlog日志文件。在你执行更新的时候,每条SQL语句,都会对应修改buffer pool里的缓存数据、写undo日志、写redo log buffer几个步骤;但是当你提交事务的时候,一定会把redo log刷入磁盘,binlog刷入磁盘,完成redo log中的事务commit标记;最后后台的IO线程会随机的把buffer pool里的脏数据刷入磁盘里去。

04_Buffer Pool

任何一个系统在使用数据库的时候,一定是从插入数据开始的,也就是首先先会对数据进行增删改的操作。当你的数据库中有了数据之后,接着才会执行各种各样的查询操作。

  1.    Buffer Pool是个什么东西?
    

数据库中的Buffer Pool是一个非常关键的组件,那就是数据库中的数据实际上最终都是要存放在磁盘文件上的。但是我们在对数据库执行增删改操作的时候,不可能直接更新磁盘上的数据的,因为如果你对磁盘进行随机读写操作,那速度是相当的慢,随便一个大磁盘文件的随机读写操作,可能都要几百毫秒。如果要是那么搞的话,可能你的数据库每秒也就只能处理几百个请求了!在对数据库执行增删改操作的时候,实际上主要都是针对内存里的Buffer Pool中的数据进行的,也就是你实际上主要是对数据库的内存里的数据结构进行了增删改。

在数据库的内存里执行了一堆增删改的操作,内存数据是更新了,但是这个时候如果数据库突然崩溃了,那么内存里更新好的数据不是都没了吗,MySQL就怕这个问题,所以引入了一个redo log机制,你在对内存里的数据进行增删改的时候,他同时会把增删改对应的日志写入redo log中。万一你的数据库突然崩溃了,没关系,只要从redo log日志文件里读取出来你之前做过哪些增删改操作,瞬间就可以重新把这些增删改操作在你的内存里执行一遍,这就可以恢复出来你之前做过哪些增删改操作了。

所以这里我们简单对Buffer Pool这个东西做一下总结,他其实是数据库中我们第一个必须要搞清楚的核心组件,因为增删改操作首先就是针对这个内存中的Buffer Pool里的数据执行的,同时配合了后续的redo log、刷磁盘等机制和操作。

所以Buffer Pool就是数据库的一个内存组件,里面缓存了磁盘上的真实数据,然后我们的Java系统对数据库执行的增删改操作,其实主要就是对这个内存数据结构中的缓存数据执行的。

  1.    如何配置你的Buffer Pool的大小?
    

因为Buffer Pool本质其实就是数据库的一个内存组件,你可以理解为他就是一片内存数据结构,所以这个内存数据结构肯定是有一定的大小的,不可能是无限大的。这个Buffer Pool默认情况下是128MB,还是有一点偏小了,我们实际生产环境下完全可以对Buffer Pool进行调整。

比如我们的数据库如果是16核32G的机器,那么你就可以给Buffer Pool分配个2GB的内存,使用下面的配置就可以了。

[server]innodb_buffer_pool_size = 2147483648

  1.    数据页:MySQL中抽象出来的数据单位
    

接着我们来看下一个问题,假设现在我们的数据库中一定有一片内存区域是Buffer Pool了,那么我们的数据是如何放在Buffer Pool中的?

我们都知道数据库的核心数据模型就是表+字段+行的概念,也就是说我们都知道数据库里有一个一个的表,一个表有很多字段,然后一个表里有很多行数据,每行数据都有自己的字段值。所以大家觉得我们的数据是一行一行的放在Buffer Pool里面的吗?这就明显不是了,实际上MySQL对数据抽象出来了一个数据页的概念,他是把很多行数据放在了一个数据页里,也就是说我们的磁盘文件中就是会有很多的数据页,每一页数据里放了很多行数据。

所以实际上假设我们要更新一行数据,此时数据库会找到这行数据所在的数据页,然后从磁盘文件里把这行数据所在的数据页直接给加载到Buffer Pool里去。也就是说,Buffer Pool中存放的是一个一个的数据页。

  1.    磁盘上的数据页和Buffer Pool中的缓存页是如何对应起来的?
    

实际上默认情况下,磁盘中存放的数据页的大小是16KB,也就是说,一页数据包含了16KB的内容。而Buffer Pool中存放的一个一个的数据页,我们通常叫做缓存页,因为毕竟Buffer Pool是一个缓冲池,里面的数据都是从磁盘缓存到内存去的。而Buffer Pool中默认情况下,一个缓存页的大小和磁盘上的一个数据页的大小是一一对应起来的,都是16KB。

  1.    缓存页对应的描述信息是什么?
    

对于每个缓存页,他实际上都会有一个描述信息,这个描述信息大体可以认为是用来描述这个缓存页的。比如包含如下的一些东西:这个数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址以及别的一些杂七杂八的东西。每个缓存页都会对应一个描述信息,这个描述信息本身也是一块数据,在Buffer Pool中,每个缓存页的描述数据放在最前面,然后各个缓存页放在后面。

而且这里我们要注意一点,Buffer Pool中的描述数据大概相当于缓存页大小的5%左右,也就是每个描述数据大概是800个字节左右的大小,然后假设你设置的buffer pool大小是128MB,实际上Buffer Pool真正的最终大小会超出一些,可能有个130多MB的样子,因为他里面还要存放每个缓存页的描述数据。

  1.    数据库启动的时候,是如何初始化Buffer Pool的?
    

Buffer Pool里面就是会包含很多个缓存页,同时每个缓存页还有一个描述数据,也可以叫做是控制数据,或者缓存页的元数据,都是可以的。那么在数据库启动的时候,他是如何初始化Buffer Pool的呢?

数据库只要一启动,就会按照你设置的Buffer Pool大小,稍微再加大一点,去找操作系统申请一块内存区域,作为Buffer Pool的内存区域。然后当内存区域申请完毕之后,数据库就会按照默认的缓存页的16KB的大小以及对应的800个字节左右的描述数据的大小,在Buffer Pool中划分出来一个一个的缓存页和一个一个的他们对应的描述数据。只不过这个时候,Buffer Pool中的一个一个的缓存页都是空的,里面什么都没有,要等数据库运行起来之后,当我们要对数据执行增删改查的操作的时候,才会把数据对应的页从磁盘文件里读取出来,放入Buffer Pool中的缓存页中。

  1.    我们怎么知道哪些缓存页是空闲的呢?
    

当你的数据库运行起来之后,肯定会不停的执行增删改查的操作,此时就需要不停的从磁盘上读取一个一个的数据页放入Buffer Pool中的对应的缓存页里去,把数据缓存起来,那么以后就可以对这个数据在内存里执行增删改查了。

但是此时在从磁盘上读取数据页放入Buffer Pool中的缓存页的时候,必然涉及到一个问题,那就是哪些缓存页是空闲的?

因为默认情况下磁盘上的数据页和缓存页是一 一对应起来的,都是16KB,一个数据页对应一个缓存页。所以我们必须要知道Buffer Pool中哪些缓存页是空闲的状态。

所以数据库会为Buffer Pool设计一个free链表,他是一个双向链表数据结构,这个free链表里,每个节点就是一个空闲的缓存页的描述数据块的地址,也就是说,只要你一个缓存页是空闲的,那么他的描述数据块就会被放入这个free链表中。刚开始数据库启动的时候,可能所有的缓存页都是空闲的,因为此时可能是一个空的数据库,一条数据都没有,所以此时所有缓存页的描述数据块,都会被放入这个free链表中。

这个free链表里面就是各个缓存页的描述数据块,只要缓存页是空闲的,那么他们对应的描述数据块就会加入到这个free链表中,每个节点都会双向链接自己的前后节点,组成一个双向链表。除此之外,这个free链表有一个基础节点,他会引用链表的头节点和尾节点,里面还存储了链表中有多少个描述数据块的节点,也就是有多少个空闲的缓存页。

  1.    free链表占用多少内存空间?
    

可能有的人会以为这个描述数据块,在Buffer Pool里有一份,在free链表里也有一份,好像在内存里有两个一模一样的描述数据块,是么?

这里要给大家讲明白一点,这个free链表,他本身其实就是由Buffer Pool里的描述数据块组成的,你可以认为是每个描述数据块里都有两个指针,一个是free_pre,一个是free_next,分别指向自己的上一个free链表的节点,以及下一个free链表的节点。

通过Buffer Pool中的描述数据块的free_pre和free_next两个指针,就可以把所有的描述数据块串成一个free链表。对于free链表而言,只有一个基础节点是不属于Buffer Pool的,他是40字节大小的一个节点,里面就存放了free链表的头节点的地址,尾节点的地址,还有free链表里当前有多少个节点。

  1.    如何将磁盘上的页读取到Buffer Pool的缓存页中去?
    

当你需要把磁盘上的数据页读取到Buffer Pool中的缓存页里去的时候,是怎么做到的?

其实有了free链表之后,这个问题就很简单了。首先,我们需要从free链表里获取一个描述数据块,然后就可以对应的获取到这个描述数据块对应的空闲缓存页。接着我们就可以把磁盘上的数据页读取到对应的缓存页里去,同时把相关的一些描述数据写入缓存页的描述数据块里去,比如这个数据页所属的表空间之类的信息,最后把那个描述数据块从free链表里去除就可以了。

  1.    你怎么知道数据页有没有被缓存?
    

我们在执行增删改查的时候,肯定是先看看这个数据页有没有被缓存,如果没被缓存就走上面的逻辑,从free链表中找到一个空闲的缓存页,从磁盘上读取数据页写入缓存页,写入描述数据,从free链表中移除这个描述数据块。

但是如果数据页已经被缓存了,那么就会直接使用了。

所以其实数据库还会有一个哈希表数据结构,他会用表空间号+数据页号,作为一个key,然后缓存页的地址作为value。

当你要使用一个数据页的时候,通过“表空间号+数据页号”作为key去这个哈希表里查一下,如果没有就读取数据页,如果已经有了,就说明数据页已经被缓存了。也就是说,每次你读取一个数据页到缓存之后,都会在这个哈希表中写入一个key-value对,key就是表空间号+数据页号,value就是缓存页的地址,那么下次如果你再使用这个数据页,就可以从哈希表里直接读取出来他已经被放入一个缓存页了。

  1.    脏数据页到底为什么会脏?
    

接着我们看一个很关键的问题,你在执行增删改的时候,如果发现数据页没缓存,那么必然会基于free链表找到一个空闲的缓存页,然后读取到缓存页里去,但是如果已经缓存了,那么下一次就必然会直接使用缓存页。反正不管怎么样,你要更新的数据页都会在Buffer Pool的缓存页里,供你在内存中直接执行增删改的操作。接着你肯定会去更新Buffer Pool的缓存页中的数据,此时一旦你更新了缓存页中的数据,那么缓存页里的数据和磁盘上的数据页里的数据,是不是就不一致了?这个时候,我们就说缓存页是脏数据,脏页。

  1.    哪些缓存页是脏页呢?
    

我们都是知道一点的,最终这些在内存里更新的脏页的数据,都是要被刷新回磁盘文件的。但是这里就有一个问题了,不可能所有的缓存页都刷回磁盘的,因为有的缓存页可能是因为查询的时候被读取到Buffer Pool里去的,可能根本没修改过!

所以数据库在这里引入了另外一个跟free链表类似的flush链表,这个flush链表本质也是通过缓存页的描述数据块中的两个指针,让被修改过的缓存页的描述数据块,组成一个双向链表。凡是被修改过的缓存页,都会把他的描述数据块加入到flush链表中去,flush的意思就是这些都是脏页,后续都是要flush刷新到磁盘上去的。所以flush链表的结构跟free链表几乎是一样的。

当你更新缓存页的时候,通过变换缓存页中的描述数据块的flush链表的指针,就可以把脏页的描述数据块组成一个双向链表,也就是flush链表,而且flush链表的基础节点会指向起始节点和尾巴节点。

通过这个flush链表,就可以记录下来哪些缓存页是脏页了!

  1.    如果Buffer Pool中的缓存页不够了怎么办?
    

上方内容已经了解了Buffer Pool中的缓存页的划分,包括free链表的使用,然后磁盘上的数据页是如何加载到缓存页里去的,包括对缓存页修改之后,flush链表是如何用来记载脏数据页的。

我们接着来分析Buffer Pool的工作原理,当你要执行CRUD操作的时候,无论是查询数据,还是修改数据,实际上都会把磁盘上的数据页加载到缓存页里来。那么在加载数据到缓存页的时候,必然是要加载到空闲的缓存页里去的,所以必须要从free链表中找一个空闲的缓存页,然后把磁盘上的数据页加载到那个空闲的缓存页里去。

随着你不停的把磁盘上的数据页加载到空闲的缓存页里去,free链表中的空闲缓存页会越来越少,因为只要你把一个数据页加载到一个空闲缓存页里去,free链表中就会减少一个空闲缓存页。所以,当你不停的把磁盘上的数据页加载到空闲缓存页里去,free链表中不停的移除空闲缓存页,迟早有那么一瞬间,你会发现free链表中已经没有空闲缓存页了。这个时候,当你还要加载数据页到一个空闲缓存页的时候,怎么办呢?

针对上述的问题,如果所有的缓存页都被塞了数据了,此时无法从磁盘上加载新的数据页到缓存页里去了,那么此时只有一个办法,就是淘汰掉一些缓存页。顾名思义,必须把一个缓存页里被修改过的数据,给他刷到磁盘上的数据页里去,然后这个缓存页就可以清空了,让他重新变成一个空闲的缓存页。接着你再把磁盘上你需要的新的数据页加载到这个腾出来的空闲缓存页中去。那么下一个问题来了,如果要把一个缓存页里的数据刷入磁盘,腾出来一个空闲缓存页,那么应该把哪个缓存页的数据给刷入磁盘呢?

要解答这个问题,我们就得引入一个缓存命中率的概念。假设现在有两个缓存页,一个缓存页的数据,经常会被修改和查询,比如在100次请求中,有30次都是在查询和修改这个缓存页里的数据。那么此时我们可以说这种情况下,缓存命中率很高。另外一个缓存页里的数据,就是刚从磁盘加载到缓存页之后,被修改和查询过1次,之后100次请求中没有一次是修改和查询这个缓存页的数据的,那么此时我们就说缓存命中率有点低,因为大部分请求可能还需要走磁盘查询数据,他们要操作的数据不在缓存中。

  1.    引入LRU链表来判断哪些缓存页是不常用的
    

接着我们就要解决下一个问题了,就是你怎么知道哪些缓存页经常被访问,哪些缓存页很少被访问?

此时就要引入一个新的LRU链表了,这个所谓的LRU就是Least Recently Used,最近最少使用的意思。通过这个LRU链表,我们可以知道哪些缓存页是最近最少被使用的,那么当你缓存页需要腾出来一个刷入磁盘的时候,不就可以选择那个LRU链表中最近最少被使用的缓存页了么?

简单来说,假设我们从磁盘加载一个数据页到缓存页的时候,就把这个缓存页的描述数据块放到LRU链表头部去,那么只要有数据的缓存页,他都会在LRU里了,而且最近被加载数据的缓存页,都会放到LRU链表的头部去。然后假设某个缓存页的描述数据块本来在LRU链表的尾部,后续你只要查询或者修改了这个缓存页的数据,也要把这个缓存页挪动到LRU链表的头部去,也就是说最近被访问过的缓存页,一定在LRU链表的头部。

那么这样的话,当你的缓存页没有一个空闲的时候,你是不是要找出来那个最近最少被访问的缓存页去刷入磁盘?此时你就直接在LRU链表的尾部找到一个缓存页,他一定是最近最少被访问的那个缓存页!然后你就把LRU链表尾部的那个缓存页刷入磁盘中,然后把你需要的磁盘数据页加载到腾出来的空闲缓存页中就可以了!

\15. 简单的LRU链表在Buffer Pool实际运行中,可能导致哪些问题?

这样的一个LRU机制在实际运行过程中,是会存在巨大的隐患的。

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

所以我们来看看,到底哪些情况下会触发MySQL的预读机制呢?有一个参数是innodb_read_ahead_threshold,他的默认值是56,意思就是如果顺序的访问了一个区里的多个数据页,访问的数据页的数量超过了这个阈值,此时就会触发预读机制,把下一个相邻区中的所有数据页都加载到缓存里去;如果Buffer Pool里缓存了一个区里的13个连续的数据页,而且这些数据页都是比较频繁会被访问的,此时就会直接触发预读机制,把这个区里的其他的数据页都加载到缓存里去。这个机制是通过参数innodb_random_read_ahead来控制的,他默认是OFF,也就是这个规则是关闭的。所以默认情况下,主要是第一个规则可能会触发预读机制,一下子把很多相邻区里的数据页加载到缓存里去,这些缓存页如果一下子都放在LRU链表的前面,而且他们其实并没什么人会访问的话,那就会如上图,导致本来就在缓存里的一些频繁被访问的缓存页在LRU链表的尾部。这样的话,一旦要把一些缓存页淘汰掉,刷入磁盘,腾出来空闲缓存页,就会如上所述,把LRU链表尾部一些频繁被访问的缓存页给刷入磁盘和清空掉了!这是完全不合理的,并不应该这样!

接着我们看看另外一种可能导致频繁被访问的缓存页被淘汰的场景,那就是全表扫描!他可能会一下子就把这个表的所有数据页都一一装入各个缓存页里去!此时可能LRU链表中排在前面的一大串缓存页,都是全表扫描加载进来的缓存页!那么如果这次全表扫描过后,后续几乎没用到这个表里的数据呢?此时LRU链表的尾部,可能全部都是之前一直被频繁访问的那些缓存页!然后当你要淘汰掉一些缓存页腾出空间的时候,就会把LRU链表尾部一直被频繁访问的缓存页给淘汰掉了,而留下了之前全表扫描加载进来的大量的不经常访问的缓存页!

如果你使用简单的LRU链表的机制,其实是漏洞百出的,因为很可能预读机制,或者全表扫描的机制,都会一下子把大量未来可能不怎么访问的数据页加载到缓存页里去,然后LRU链表的前面全部是这些未来可能不怎么会被访问的缓存页!而真正之前一直频繁被访问的缓存页可能此时都在LRU链表的尾部了!如果此时此刻,需要把一些缓存页刷入磁盘,腾出空间来加载新的数据页,那么此时就只能把LRU链表尾部那些一直频繁被访问的缓存页给刷入磁盘了!

  1.    MySQL是如何基于冷热数据分离的方案,来优化LRU算法的?
    

所以为了解决上一讲我们说的简单的LRU链表的问题,真正MySQL在设计LRU链表的时候,采取的实际上是冷热数据分离的思想。所以真正的LRU链表,会被拆分为两个部分,一部分是热数据,一部分是冷数据,这个冷热数据的比例是由innodb_old_blocks_pct参数控制的,他默认是37,也就是说冷数据占比37%。

数据页第一次被加载到缓存的时候,实际上这个时候,缓存页会被放在冷数据区域的链表头部。

冷数据区域的缓存页什么时候会被放入到热数据区域?MySQL设定了一个规则,他设计了一个innodb_old_blocks_time参数,默认值1000,也就是1000毫秒,也就是说,必须是一个数据页被加载到缓存页之后,在1s之后,你访问这个缓存页,他才会被挪动到热数据区域的链表头部去。因为假设你加载了一个数据页到缓存去,然后过了1s之后你还访问了这个缓存页,说明你后续很可能会经常要访问它,这个时间限制就是1s,因此只有1s后你访问了这个缓存页,他才会给你把缓存页放到热数据区域的链表头部去。

在这样的一个LRU链表方案下,预读机制以及全表扫描加载进来的一大堆缓存页,明显是放在LRU链表的冷数据区域的前面!假设这个时候热数据区域已经有很多被频繁访问的缓存页了,你会发现热数据区域还是存放被频繁访问的缓存页的,只要热数据区域有缓存页被访问,他还是会被移动到热数据区域的链表头部去。如果你仅仅是一个全表扫描的查询,此时你肯定是在1s内就把一大堆缓存页加载进来,然后就访问了这些缓存页一下,通常这些操作1s内就结束了。所以基于目前的一个机制,可以确定的是,这种情况下,那些缓存页是不会从冷数据区域转移到热数据区域的!除非你在冷数据区域里的缓存页,在1s之后还被人访问了,那么此时他们就会判定为未来可能会被频繁访问的缓存页,然后移动到热数据区域的链表头部去!

如果此时缓存页不够了,需要淘汰一些缓存,直接就是可以找到LRU链表中的冷数据区域的尾部的缓存页,他们肯定是之前被加载进来的,而且加载进来1s过后都没人访问过,说明这个缓存页压根儿就没人愿意去访问他!他就是冷数据!所以此时就直接淘汰冷数据区域的尾部的缓存页,刷入磁盘,就可以了。这样的话,在淘汰缓存的时候,一定是优先淘汰冷数据区域几乎不怎么被访问的缓存页的!

  1.    MySQL是如何将LRU链表的使用性能优化到极致的?
    

接着我们来看看LRU链表的热数据区域的一个性能优化的点,在热数据区域中,如果你访问了一个缓存页,是不是应该要把他立马移动到热数据区域的链表头部去?但是你要知道,热数据区域里的缓存页可能是经常被访问的,所以这么频繁的进行移动是不是性能也并不是太好?也没这个必要。

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

  1.    对于LRU链表中尾部的缓存页,是如何淘汰他们刷入磁盘的?
    

定时把LRU尾部的部分缓存页刷入磁盘。首先第一个时机,并不是在缓存页满的时候,才会挑选LRU冷数据区域尾部的几个缓存页刷入磁盘,而是有一个后台线程,他会运行一个定时任务,这个定时任务每隔一段时间就会把LRU链表的冷数据区域的尾部的一些缓存页,刷入磁盘里去,清空这几个缓存页,把他们加入回free链表去!所以实际上在缓存页没用完的时候,可能就会清空一些缓存页了。只要有这个后台线程定时运行,可能你的缓存页都没用完呢,人家就给你把一批冷数据的缓存页刷入磁盘,清空出来一批缓存页,那么你就多了一批可以使用的空闲缓存页了!

所以如果在一个动态的运行效果中思考,大概就是你不停的加载数据到一些空闲的缓存页里去,然后这些缓存页可能被使用,会在lru链表中各种移动。然后同时有一个后台线程还不停的把冷数据区域的一些不用的缓存页刷入磁盘中,清空一些缓存页出来。只要有缓存页被刷人磁盘,那么这个缓存页必然会加入到free链表中,从flush链表中移除,从lru链表中移除。

把flush链表中的一些缓存页定时刷入磁盘。如果仅仅是把LRU链表中的冷数据区域的缓存页刷入磁盘,明显不够,因为在lru链表的热数据区域里的很多缓存页可能也会被频繁的修改,所以这个后台线程同时也会在MySQL不怎么繁忙的时候,找个时间把flush链表中的缓存页都刷入磁盘中,这样被你修改过的数据,迟早都会刷入磁盘的!只要flush链表中的一波缓存页被刷入了磁盘,那么这些缓存页也会从flush链表和lru链表中移除,然后加入到free链表中去!

所以可以理解为,一边不停的加载数据到缓存页里去,不停的查询和修改缓存数据,然后free链表中的缓存页不停的在减少,flush链表中的缓存页不停的在增加,lru链表中的缓存页不停的在增加和移动。另外一边,你的后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页,刷入磁盘中来清空缓存页,然后flush链表和lru链表中的缓存页在减少,free链表中的缓存页在增加。

实在没有空闲缓存页了怎么办?

此时可能所有的free链表都被使用了,然后flush链表中有一大堆被修改过的缓存页,lru链表中有一大堆的缓存页,根据冷热数据进行了分离,大致是如此的效果。这个时候如果要从磁盘加载数据页到一个空闲缓存页中,此时就会从LRU链表的冷数据区域的尾部找到一个缓存页,他一定是最不经常使用的缓存页!然后把他刷入磁盘和清空,然后把数据页加载到这个腾出来的空闲缓存页里去!

这就是MySQL的Buffer Pool缓存机制的一整套运行原理!我们已经完整的了解了缓存页的加载和使用,以及free链表、flush链表、lru链表是怎么使用的,包括缓存页是如何刷入磁盘腾出来空闲缓存页的,以及缓存页没有空闲的时候应该怎么处理。

05_数据在磁盘上如何存储

  1.    初涉MySQL物理数据存储格式:一行数据在磁盘上是如何存储的?
    

对数据页中的每一行数据,他在磁盘上是怎么存储的?其实这里涉及到一个概念,就是行格式。我们可以对一个表指定他的行存储的格式是什么样的,比如我们这里用一个COMPACT格式。

你可以在建表的时候,就指定一个行存储的格式,也可以后续修改行存储的格式。

这里指定了一个COMPACT行存储格式,在这种格式下,每一行数据他实际存储的时候,大概格式类似下面这样:变长字段的长度列表,null值列表,数据头,column01的值,column02的值,column0n的值......

对于每一行数据,他其实存储的时候都会有一些头字段对这行数据进行一定的描述,然后再放上他这一行数据每一列的具体的值,这就是所谓的行格式。除了COMPACT以外,还有其他几种行存储格式,基本都大同小异。

  1.    变长字段在磁盘中是怎么存储的?
    

大家都知道,在MySQL里有一些字段的长度是变长的,是不固定的,比如VARCHAR(10)之类的这种类型的字段,实际上他里面存放的字符串的长度是不固定的,有可能是“hello”这么一个字符串,也可能是“a”这么一个字符串。

现在有一行数据,他的几个字段的类型为VRACHAR(10),CHAR(1),CHAR(1),那么他第一个字段是VARCHAR(10),这个长度是可能变化的,所以这一行数据可能就是类似于:hello a a,这样子,第一个字段的值是“hello”,后面两个字段的值都是一个字符,就是一个a。然后另外一行数据,同样也是这几个字段,他的第一个字段的值可能是“hi”,后面两个字段也是“a”,所以这一行数据可能是类似于:hi a a。一共三个字段,第一个字段的长度是是不固定的,后面两个字段的长度都是固定的1个字符。

那么现在,我们来假设你把上述两条数据写入了一个磁盘文件里,两行数据是挨在一起的,那么这个时候在一个磁盘文件里可能有下面的两行数据:hello a a hi a a

没错!其实平时你看到的表里的很多行数据,最终落地到磁盘里的时候,都是上面那种样子的,一大坨数据放在一个磁盘文件里都挨着存储的。

  1.    存储在磁盘文件里的变长字段,为什么难以读取?
    

假设现在我们要读取上面的磁盘文件里的数据,要读取出来hello a a这一行数据。这个过程比你想象的可能要困难一些。

假如现在你要读取hello a a这行数据,第一个问题就是,从这个磁盘文件里读取的时候,到底哪些内容是一行数据?我不知道啊!因为这个表里的第一个字段是VARCHAR(10)类型的,第一个字段的长度是多少我们是不知道的!所以有可能你读取出来“hello a a hi”是一行数据,也可能是你读取出来“hello a”是一行数据,你在不知道一行数据的每个字段到底是多少长度的情况下,胡乱的去读取是不现实的,根本不知道磁盘文件里混成一坨的数据里,哪些数据是你要读取的一行?

  1.    引入变长字段的长度列表,解决一行数据的读取问题
    

所以说才要在存储每一行数据的时候,都保存一下他的变长字段的长度列表,这样才能解决一行数据的读取问题。也就是说,你在存储“hello a a”这行数据的时候,要带上一些额外的附加信息,比如第一块就是他里面的变长字段的长度列表。也就是说,这个hello是VARCHAR(10)类型的变长字段的值,那么这个“hello”字段值的长度到底是多少?我们看到“hello”的长度是5,十六进制就是0x05,所以此时会在“hello a a”前面补充一些额外信息,首先就是变长字段的长度列表,你会看到这行数据在磁盘文件里存储的时候,其实是类似如下的格式:0x05 null值列表 数据头 hello a a。这个时候假设你有两行数据,还有一行数据可能就是:0x02 null值列表 数据头 hi a a,两行数据放在一起存储在磁盘文件里,看起来是如下所示的:

0x05 null值列表 数据头 hello a a 0x02 null值列表 数据头 hi a a

  1.    引入变长字段长度列表后,如何解决变长字段的读取问题?
    

所以假设此时你要读取“hello a a”这行数据,你首先会知道这个表里的三个字段的类型是VARCHAR(10) CHAR(1) CHAR(1),那么此时你先要读取第一个字段的值,那么第一个字段是变长的,到底他的实际长度是多少呢?

此时你会发现第一行数据的开头有一个变长字段的长度列表,里面会读取到一个0x05这个十六进制的数字,发现第一个变长字段的长度是5,于是按照长度为5,读取出来第一个字段的值,就是“hello”。接着你知道后续两个字段都是CHAR(1),长度都是固定的1个字符,于是此时就依次按照长度为1读取出来后续两个字段的值,分别是“a”“a”,于是最终你会读取出来“hello a a”这一行数据!

接着假设你要读取第二行数据,你先看一下第二行数据后的变长字段长度列表,发现他第一个变长字段的长度是0x02,于是就读取长度为2的字段值,就是“hi”,再读取两个长度固定为1的字符值,都是“a”,此时读取出来“hi a a”这行数据。

  1.    如果有多个变长字段,如何存放他们的长度?
    

比如一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1),一共5个字段,其中三个是变长字段,此时假设一行数据是这样的:hello hi hao a a

此时在磁盘中存储的,必须在他开头的变长字段长度列表中存储几个变长字段的长度,一定要注意一点,他这里是逆序存储的!也就是说先存放VARCHAR(20)这个字段的长度,然后存放VARCHAR(5)这个字段的长度,最后存放VARCHAR(10)这个字段的长度。现在hello hi hao三个字段的长度分别是0x05 0x02 0x03,但是实际存放在变长字段长度列表的时候,是逆序放的,所以一行数据实际存储可能是下面这样的:0x03 0x02 0x05 null值列表 头字段 hello hi hao a a

  1.    为什么一行数据里的NULL值不能直接存储?
    

这个所谓的NULL值列表,顾名思义,说的就是你一行数据里可能有的字段值是NULL,比如你有一个name字段,他是允许为NULL的,那么实际上在存储的时候,如果你没给他赋值,他这个字段的值就是NULL。好,那么假设这个字段的NULL值我们在磁盘上存储的时候,就是按照“NULL”这么个字符串来存储,是不是很浪费存储空间?本来他就是个NULL,说明什么值都没有,你还给他存个“NULL”字符串,你说你这是干什么呢?所以实际在磁盘上存储数据的时候,一行数据里的NULL值是肯定不会直接按照字符串的方式存放在磁盘上浪费空间的。

  1.    NULL值是以二进制bit位来存储的
    

对所有的NULL值,不通过字符串在磁盘上存储,而是通过二进制的bit位来存储,一行数据里假设有多个字段的值都是NULL,那么这多个字段的NULL,就会以bit位的形式存放在NULL值列表中。

接着来看NULL值列表,这个NULL值列表是这样存放的,你所有允许值为NULL的字段,注意,是允许值为NULL,不是说一定值就是NULL了,只要是允许你为NULL的字段,在这里每个字段都有一个二进制bit位的值,如果bit值是1说明是NULL,如果bit值是0说明不是NULL。但是实际放在NULL值列表的时候,他是按逆序放的,所以在NULL值列表里,放的是:0101。

另外就是他实际NULL值列表存放的时候,不会说仅仅是4个bit位,他一般起码是8个bit位的倍数,如果不足8个bit位就高位补0,所以实际存放看起来是如下的:

0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN

  1.    磁盘上的一行数据到底如何读取出来的?
    

首先他必然要把变长字段长度列表和NULL值列表读取出来,通过综合分析一下,就知道有几个变长字段,哪几个变长字段是NULL,因为NULL值列表里谁是NULL谁不是NULL都一清二楚。

此时就可以从变长字段长度列表中解析出来不为NULL的变长字段的值长度,然后也知道哪几个字段是NULL的,此时根据这些信息,就可以从实际的列值存储区域里,把你每个字段的值读取出来了。

如果是变长字段的值,就按照他的值长度来读取,如果是NULL,就知道他是个NULL,没有值存储,如果是定长字段,就按照定长长度来读取,这样就可以完美的把你一行数据的值都读取出来了!

  1.    磁盘文件中, 40个bit位的数据头以及真实数据是如何存储的? 
    

每一行数据存储的时候,还得有40个bit位的数据头,这个数据头是用来描述这行数据的。

这40个bit位里,第一个bit位和第二个bit位,都是预留位,是没任何含义的。

然后接下来有一个bit位是delete_mask,他标识的是这行数据是否被删除了,其实看到这个bit位,很多人可能已经反映过来了,这么说在MySQL里删除一行数据的时候,未必是立马把他从磁盘上清理掉,而是给他在数据头里搞1个bit标记他已经被删了?

然后下一个bit位是min_rec_mask,他其实就是说在B+树里每一层的非叶子节点里的最小值都有这个标记。

接下来有4个bit位是n_owned,他其实就是记录了一个记录数。

接着有13个bit位是heap_no,他代表的是当前这行数据在记录堆里的位置。

然后是3个bit位的record_type,这就是说这行数据的类型,0代表的是普通类型,1代表的是B+树非叶子节点,2代表的是最小值数据,3代表的是最大值数据。

最后是16个bit的next_record,这个是指向他下一条数据的指针。

  1.    我们每一行的实际数据在磁盘上是如何存储的?
    

实际上字符串这些东西都是根据我们数据库指定的字符集编码,进行编码之后再存储的,所以大致看起来一行数据是如下所示的:

0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262

大家会看到上面,我们的字符串和其他类型的数值最终都会根据字符集编码,搞成一些数字和符号存储在磁盘上。在实际存储一行数据的时候,会在他的真实数据部分,加入一些隐藏字段。

首先有一个DB_ROW_ID字段,这就是一个行的唯一标识,是他数据库内部给你搞的一个标识,不是你的主键ID字段。如果我们没有指定主键和unique key唯一索引的时候,他就内部自动加一个ROW_ID作为主键。

接着是一个DB_TRX_ID字段,这是跟事务相关的,他是说这是哪个事务更新的数据,这是事务ID。

最后是DB_ROLL_PTR字段,这是回滚指针,是用来进行事务回滚的。

所以如果你加上这几个隐藏字段之后,实际一行数据可能看起来如下所示:

0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C(DB_ROW_ID)00000000032D(DB_TRX_ID) EA000010078E(DB_ROL_PTR) 616161 636320 6262626262

  1.    行溢出是什么东西?
    

我们之前已经初步了解到,实际上我们每一行数据都是放在一个数据页里的,这个数据页默认的大小是16KB,那么之前就有人在后台提过一个问题:万一 一行数据的大小超过了页的大小怎么办呢?

比如有一个表的字段类型是VARCHAR(65532),意思就是最大可以包含65532个字符,那也就是65532个字节,这就远大于16kb的大小了,也就是说这一行数据的这个字段都远超一个数据页的大小了!

     这个时候实际上会在那一页里存储你这行数据,然后在那个字段中,仅仅包含他一部分数据,同时包含一个20个字节的指针,指向了其他的一些数据页,那些数据页用链表串联起来,存放这个VARCHAR(65532)超大字段里的数据。

上面说的这个过程,其实就叫做行溢出,就是说一行数据存储的内容太多了,一个数据页都放不下了,此时只能溢出这个数据页,把数据溢出存放到其他数据页里去,那些数据页就叫做溢出页。包括其他的一些字段类型都是一样的,比如TEXT、BLOB这种类型的字段,都有可能出现溢出,然后一行数据就会存储在多个数据页里。

  1.    表空间以及划分多个数据页的数据区,又是什么概念?
    

现在我们在大致了解了数据页的结构和使用之后,我们可以继续来了解下一个概念,就是表空间和数据区的概念。

简单来说,就是我们平时创建的那些表,其实都是有一个表空间的概念,在磁盘上都会对应着“表名.ibd”这样的一个磁盘数据文件。所以其实在物理层面,表空间就是对应一些磁盘上的数据文件。有的表空间,比如系统表空间可能对应的是多个磁盘文件,有的我们自己创建的表对应的表空间可能就是对应了一个“表名.ibd”数据文件。

然后在表空间的磁盘文件里,其实会有很多很多的数据页,因为大家都知道一个数据页不过就是16kb而已,总不可能一个数据页就是一个磁盘文件吧。所以一个表空间的磁盘文件里,其实是有很多的数据页的。但是现在有一个问题,就是一个表空间里包含的数据页实在是太多了,不便于管理,所以在表空间里又引入了一个数据区的概念,英文就是extent。

一个数据区对应着连续的64个数据页,每个数据页是16kb,所以一个数据区是1mb,然后256个数据区被划分为了一组。

对于表空间而言,他的第一组数据区的第一个数据区的前3个数据页,都是固定的,里面存放了一些描述性的数据。比如FSP_HDR这个数据页,他里面就存放了表空间和这一组数据区的一些属性。IBUF_BITMAP数据页,里面存放的是这一组数据页的所有insert buffer的一些信息。INODE数据页,这里也是存放了一些特殊的信息。

然后这个表空间里的其他各组数据区,每一组数据区的第一个数据区的头两个数据页,都是存放特殊信息的,比如XDES数据页就是用来存放这一组数据区的一些相关属性的,其实就是很多描述这组数据区的东西。

我们平时创建的那些表都是有对应的表空间的,每个表空间就是对应了磁盘上的数据文件,在表空间里有很多组数据区,一组数据区是256个数据区,每个数据区包含了64个数据页,是1mb。然后表空间的第一组数据区的第一个数据区的头三个数据页,都是存放特殊信息的;表空间的其他组数据区的第一个数据区的头两个数据页,也都是存放特殊信息的。

当我们需要执行crud操作的时候,说白了,就是从磁盘上的表空间的数据文件里,去加载一些数据页出来到Buffer Pool的缓存页里去使用。

06_Redo log和Undo log

  1.      redo日志对于事务提交后,数据绝对不会丢失的意义 
    

我们知道,在更新完Buffer Pool中的缓存页之后,必须要写一条redo log,这样才能记录下来我们对数据库做的修改。

redo log可以保证我们事务提交之后,如果事务中的增删改SQL语句更新的缓存页还没刷到磁盘上去,此时MySQL宕机了,那么MySQL重启过后,就可以把redo log重做一遍,恢复出来事务当时更新的缓存页,然后再把缓存页刷到磁盘就可以了。

redo log本质是保证事务提交之后,修改的数据绝对不会丢失的。

首先我们都知道,执行增删改SQL语句的时候,都是针对一个表中的某些数据去执行的,此时的话,首先必须找到这个表对应的表空间,然后找到表空间对应的磁盘文件,接着从磁盘文件里把你要更新的那批数据所在的数据页从磁盘读取出来,放到Buffer Pool的缓存页里去。接着实际上你的增删改SQL语句就会针对Buffer Pool中的缓存页去执行你的更新逻辑,比如插入一行数据,或者更新一行数据,或者是删除一行数据。

其实你更新缓存页的时候,会更新free链表、flush链表、lru链表,然后有专门的后台IO线程,不定时的根据flush链表、lru链表,会把你更新过的缓存页刷新回磁盘文件的数据页里去。所以大家都知道这个机制里最大的漏洞就在于,万一你一个事务里有增删改SQL更新了缓存页,然后事务提交了,结果万一你还没来得及让IO线程把缓存页刷新到磁盘文件里,此时MySQL宕机了,然后内存数据丢失,你事务更新的数据就丢失了!

但是也不可能每次你事务一提交,就把你事务更新的缓存页都刷新回磁盘文件里去,因为大家之前也都知道,缓存页刷新到磁盘文件里,是随机磁盘读写,性能是相当的差!这会导致你数据库性能和并发能力都很弱的!所以此时才会引入一个redo log机制,这个机制就是说,你提交事务的时候,绝对是保证把你对缓存页做的修改以日志的形式,写入到redo log日志文件里去的。这种日志大致的格式如下:对表空间XX中的数据页XX中的偏移量为XXXX的地方更新了数据XXX。

只要你事务提交的时候保证你做的修改以日志形式写入redo log日志,那么哪怕你此时突然宕机了,也没关系!因为你MySQL重启之后,把你之前事务更新过做的修改根据redo log在Buffer Pool里重做一遍就可以了,就可以恢复出来当时你事务对缓存页做的修改,然后找时机再把缓存页刷入磁盘文件里去。

那么有人会问了,你事务提交的时候把修改过的缓存页都刷入磁盘,跟你事务提交的时候把你做的修改的redo log都写入日志文件,他们不都是写磁盘么?差别在哪里?实际上,如果你把修改过的缓存页都刷入磁盘,这首先缓存页一个就是16kb,数据比较大,刷入磁盘比较耗时,而且你可能就修改了缓存页里的几个字节的数据,难道也把完整的缓存页刷入磁盘吗?而且你缓存页刷入磁盘是随机写磁盘,性能是很差的,因为他一个缓存页对应的位置可能在磁盘文件的一个随机位置,比如偏移量为45336这个地方。

但是如果是写redo log,第一个一行redo log可能就占据几十个字节,就包含表空间号、数据页号、磁盘文件偏移量、更新值,这个写入磁盘速度很快。此外,redo log写日志,是顺序写入磁盘文件,每次都是追加到磁盘文件末尾去,速度也是很快的。所以你提交事务的时候,用redo log的形式记录下来你做的修改,性能会远远超过刷缓存页的方式,这也可以让你的数据库的并发能力更强。

  1.    写入日志文件的redo log长什么样?
    

redo log里本质上记录的就是在对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值,具体修改的值是什么,他里面需要记录的就是表空间号+数据页号+偏移量+修改几个字节的值+具体的值。

所以根据你修改了数据页里的几个字节的值,redo log就划分为了不同的类型,MLOG_1BYTE类型的日志指的就是修改了1个字节的值,MLOG_2BYTE类型的日志指的就是修改了2个字节的值,以此类推,还有修改了4个字节的值的日志类型,修改了8个字节的值的日志类型。当然,如果你要是一下子修改了一大串的值,类型就是MLOG_WRITE_STRING,就是代表你一下子在那个数据页的某个偏移量的位置插入或者修改了一大串的值。

所以其实一条redo log看起来大致的结构如下所示:

日志类型(就是类似MLOG_1BYTE之类的),表空间ID,数据页号,数据页中的偏移量,具体修改的数据

大致就是一条redo log中依次排列上述的一些东西,这条redo log表达的语义就很明确了,他的类型是什么,类型就告诉了你他这次增删改操作修改了多少字节的数据;然后在哪个表空间里操作的,这个就是跟你SQL在哪个表里执行的是对应的;接着就是在这个表空间的哪个数据页里执行的,在数据页的哪个偏移量开始执行的,具体更新的数据是哪些呢。有了上述信息,就可以精准完美的还原出来一次数据增删改操作做的变动了。只不过如果是MLOG_WRITE_STRING类型的日志,因为不知道具体修改了多少字节的数据,所以其实会多一个修改数据长度,就告诉你他这次修改了多少字节的数据,如下所示他的格式:

日志类型(就是类似MLOG_1BYTE之类的),表空间ID,数据页号,数据页中的偏移量,修改数据长度,具体修改的数据

  1.    redo log是直接一条一条写入文件的吗?
    

平时我们执行CRUD的时候,从磁盘加载数据页到buffer pool的缓存页里去,然后对缓存页执行增删改,同时还会写redo log到日志文件里去,后续不定时把缓存页刷回磁盘文件里去,大概就是这个原理。

redo log就是一条一条的直接就写入到磁盘上的日志文件里去了吗?显然不是的!

其实MySQL内有另外一个数据结构,叫做redo log block,大概你可以理解为,平时我们的数据不是存放在数据页了的么,用一页一页的数据页来存放数据。那么对于redo log也不是单行单行的写入日志文件的,他是用一个redo log block来存放多个单行日志的。一个redo log block是512字节,这个redo log block的512字节分为3个部分,一个是12字节的header块头,一个是496字节的body块体,一个是4字节的trailer块尾。

在这里面,12字节的header头又分为了4个部分。

包括4个字节的block no,就是块唯一编号;2个字节的data length,就是block里写入了多少字节数据;2个字节的first record group。这个是说每个事务都会有多个redo log,是一个redo log group,即一组redo log。那么在这个block里的第一组redo log的偏移量,就是这2个字节存储的;4个字节的checkpoint on。

所以其实对于我们的redo log而言,他确实是不停的追加写入到redo log磁盘文件里去的,但是其实每一个redo log都是写入到文件里的一个redo log block里去的,一个block最多放496自己的redo log日志。

此时可能有人会有疑问了,到底一个一个的redo log block在日志文件里是怎么存在的?那么一条一条的redo log又是如何写入日志文件里的redo log block里去的呢?假设你有一个redo log日志文件,平时我们往里面写数据,你大致可以认为是从第一行开始,从左往右写,可能会有很多行,那么所以现在既然如此,假设你要写第一个redo log了,是不是应该起码是先在内存里把这个redo log给弄到一个redo log block数据结构里去?然后似乎你应该是等内存里的一个redo log block的512字节都满了,再一次性把这个redo log block写入磁盘文件?然后其实按照我们所说的,一个redo log block就是512字节,那么是不是真正写入的时候,把这个redo log block的512字节的数据,就写入到redo log文件里去就可以了?那么redo log文件里就多了一个block。

  1.    redo log buffer中的缓冲日志,到底什么时候可以写入磁盘?
    

redo log buffer的缓冲机制,redo log在写的时候,都是一个事务里的一组redo log,先暂存在一个地方,完事儿了以后把一组redo log写入redo log buffer。写入redo log buffer的时候,是写入里面提前划分好的一个一个的redo log block的,选择有空闲空间的redo log block去写入,然后redo log block写满之后,其实会在某个时机刷入到磁盘里去。那么到底redo log buffer里的redo log block什么时候可以刷入到磁盘文件里去呢?另外,磁盘上到底有几个redo log日志文件?不可能大量的redo log日志都放一个文件里吧?磁盘空间会占用的越来越多吗?

首先,我们先来看看redo log block是哪些时候会刷入到磁盘文件里去:

①如果写入redo log buffer的日志已经占据了redo log buffer总容量的一半了,也就是超过了8MB的redo log在缓冲里了,此时就会把他们刷入到磁盘文件里去。

②一个事务提交的时候,必须把他的那些redo log所在的redo log block都刷入到磁盘文件里去,只有这样,当事务提交之后,他修改的数据绝对不会丢失,因为redo log里有重做日志,随时可以恢复事务做的修改。

③后台线程定时刷新,有一个后台线程每隔1秒就会把redo log buffer里的redo log block刷到磁盘文件里去。

④MySQL关闭的时候,redo log block都会刷入到磁盘里去。

忽略上面的第四条不说,因为关闭MySQL的时候必然会刷redo log到磁盘,其他三条其实我们都看到了,也就是说,如果你瞬间执行大量的高并发的SQL语句,1秒内就产生了超过8MB的redo log,此时占据了redo log buffer一半的空间了,必然会直接把你的redo log刷入磁盘里去。

上面这种redo log刷盘的情况,在MySQL承载高并发请求的时候比较常见,比如每秒执行上万个增删改SQL语句,每个SQL产生的redo log假设有几百个字节,此时却是会在瞬间生成超过8MB的redo log日志,必然会触发立马刷新redo log到磁盘。

其次,第二种情况,其实就是平时执行一个事务,这个事务一般都是在几十毫秒到几百毫秒执行完毕的,说实在的,一般正常性能情况下,MySQL单事务性能一般不会超过1秒,否则数据库操作就太慢了。那么如果在几十毫秒,或者几百毫秒的时候,执行完毕了一个事务,此时必然会立马把这个事务的redo log都刷入磁盘。

第一种情况其实是不常见的,第二种情况是比较常见的,往往redo log刷盘都是以一个短事务提交时候发生的,第三种情况就是后台线程每秒自动刷新redo log到磁盘去,这个就是说假设没有别的情况触发,后台线程自己都会不停的刷新redo log到磁盘。但是不管怎么说,主要是保证一个事务执行的时候,redo log都进入redo log buffer,提交事务的时候,事务对应的redo log必须是刷入磁盘文件,接着才算是事务提交成功,否则事务提交就是失败,保证这一点,就能确保事务提交之后,数据不会丢,有redo log在磁盘里就行了。

当然,绝对保证数据不丢,还得配置一个参数,提交事务把redo log刷入磁盘文件的os cache之后,还得强行从os cache刷入物理磁盘。最后说一下redo log日志文件的问题,我们都知道平时不停的执行增删改,那么MySQL会不停的产生大量的redo log写入日志文件,那么日志文件就用一个写入全部的redo log?对磁盘占用空间越来越大怎么办?

这些问题都可以解决,实际上默认情况下,redo log都会写入一个目录中的文件里,这个目录可以通过show variables like 'datadir'来查看,可以通过innodb_log_group_home_dir参数来设置这个目录的。然后redo log是有多个的,写满了一个就会写下一个redo log,而且可以限制redo log文件的数量,通过innodb_log_file_size可以指定每个redo log文件的大小,默认是48MB,通过innodb_log_files_in_group可以指定日志文件的数量,默认就2个。所以默认情况下,目录里就两个日志文件,分别为ib_logfile0和ib_logfile1,每个48MB,最多就这2个日志文件,就是先写第一个,写满了写第二个。那么如果第二个也写满了呢?别担心,继续写第一个,覆盖第一个日志文件里原来的redo log就可以了。

所以最多这个redo log,mysql就给你保留了最近的96MB的redo log而已,不过这其实已经很多了,毕竟redo log真的很小,一条通常就几个字节到几十个字节不等,96MB足够你存储上百万条redo log了!如果你还想保留更多的redo log,其实调节上述两个参数就可以了,比如每个redo log文件是96MB,最多保留100个redo log文件。

  1.    如果事务执行到一半要回滚怎么办?
    

在执行增删改操作时候的redo log的重做日志原理,其实说白了,就是你对buffer pool里的缓存页执行增删改操作的时候,必须要写对应的redo log记录下来你做了哪些修改,redo log都是先进入redo log buffer中的一个block,然后事务提交的时候就会刷入磁盘文件里去。

这样万一要是你提交事务了,结果事务修改的缓存页还没来得及刷入磁盘上的数据文件,此时你MySQL关闭了或者是宕机了,那么buffer pool里被事务修改过的数据就全部都丢失了!但是只要有redo log,你重启MySQL之后完全是可以把那些修改了缓存页,但是缓存页还没来得及刷入磁盘的事务,他们所对应的redo log都加载出来,在buffer pool的缓存页里重做一遍,就可以保证事务提交之后,修改的数据绝对不会丢!

假设现在我们一个事务里要执行一些增删改的操作,那么必然是先把对应的数据页从磁盘加载出来放buffer pool的缓存页里,然后在缓存页里执行一通增删改,同时记录redo log日志,对吧?但是现在问题来了,万一要是一个事务里的一通增删改操作执行到了一半,结果就回滚事务了呢?比如一个事务里有4个增删改操作,结果目前为止已经执行了2个增删改SQL了,已经更新了一些buffer pool里的数据了,但是还有2个增删改SQL的逻辑还没执行,此时事务要回滚了怎么办?

这个时候就很尴尬了,如果你要回滚事务的话,那么必须要把已经在buffer pool的缓存页里执行的增删改操作给回滚了。但是怎么回滚呢?毕竟无论是插入,还是更新,还是删除,该做的都已经做了啊!所以在执行事务的时候,才必须引入另外一种日志,就是undo log回滚日志。

这个回滚日志,他记录的东西其实非常简单,比如你要是在缓存页里执行了一个insert语句,那么此时你在undo log日志里,对这个操作记录的回滚日志就必须是有一个主键和一个对应的delete操作,要能让你把这次insert操作给回退了。那么比如说你要是执行的是delete语句,那么起码你要把你删除的那条数据记录下来,如果要回滚,就应该执行一个insert操作把那条数据插入回去。如果你要是执行的是update语句,那么起码你要把你更新之前的那个值记录下来,回滚的时候重新update一下,把你之前更新前的旧值给他更新回去。如果你要是执行的是select语句呢?不好意思,select语句压根儿没有在buffer pool里执行任何修改,所以根本不需要undo log!

其实你在执行事务期间,你除了写redo log日志还必须要写undo log日志,这个undo log日志是至关重要的,没有他,你根本都没办法回滚事务!

  1.    INSRET语句的undo log回滚日志长什么样?
    

undo log回滚日志的作用,就是你执行事务的时候,里面很多INSERT、UPDATE和DELETE语句都在更新缓存页里的数据,但是万一事务回滚,你必须有每条SQL语句对应的undo log回滚日志,根据回滚日志去恢复缓存页里被更新的数据。

INSERT语句的undo log的类型是TRX_UNDO_INSERT_REC,这个undo log里包含了以下一些东西:

这条日志的开始位置;主键的各列长度和值;表id;undo log日志编号;undo log日志类型;这条日志的结束位置;

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

05_数据在磁盘上如何存储

  1.    初涉MySQL物理数据存储格式:一行数据在磁盘上是如何存储的?
    

对数据页中的每一行数据,他在磁盘上是怎么存储的?其实这里涉及到一个概念,就是行格式。我们可以对一个表指定他的行存储的格式是什么样的,比如我们这里用一个COMPACT格式。

你可以在建表的时候,就指定一个行存储的格式,也可以后续修改行存储的格式。

这里指定了一个COMPACT行存储格式,在这种格式下,每一行数据他实际存储的时候,大概格式类似下面这样:变长字段的长度列表,null值列表,数据头,column01的值,column02的值,column0n的值......

对于每一行数据,他其实存储的时候都会有一些头字段对这行数据进行一定的描述,然后再放上他这一行数据每一列的具体的值,这就是所谓的行格式。除了COMPACT以外,还有其他几种行存储格式,基本都大同小异。

  1.    变长字段在磁盘中是怎么存储的?
    

大家都知道,在MySQL里有一些字段的长度是变长的,是不固定的,比如VARCHAR(10)之类的这种类型的字段,实际上他里面存放的字符串的长度是不固定的,有可能是“hello”这么一个字符串,也可能是“a”这么一个字符串。

现在有一行数据,他的几个字段的类型为VRACHAR(10),CHAR(1),CHAR(1),那么他第一个字段是VARCHAR(10),这个长度是可能变化的,所以这一行数据可能就是类似于:hello a a,这样子,第一个字段的值是“hello”,后面两个字段的值都是一个字符,就是一个a。然后另外一行数据,同样也是这几个字段,他的第一个字段的值可能是“hi”,后面两个字段也是“a”,所以这一行数据可能是类似于:hi a a。一共三个字段,第一个字段的长度是是不固定的,后面两个字段的长度都是固定的1个字符。

那么现在,我们来假设你把上述两条数据写入了一个磁盘文件里,两行数据是挨在一起的,那么这个时候在一个磁盘文件里可能有下面的两行数据:hello a a hi a a

没错!其实平时你看到的表里的很多行数据,最终落地到磁盘里的时候,都是上面那种样子的,一大坨数据放在一个磁盘文件里都挨着存储的。

  1.    存储在磁盘文件里的变长字段,为什么难以读取?
    

假设现在我们要读取上面的磁盘文件里的数据,要读取出来hello a a这一行数据。这个过程比你想象的可能要困难一些。

假如现在你要读取hello a a这行数据,第一个问题就是,从这个磁盘文件里读取的时候,到底哪些内容是一行数据?我不知道啊!因为这个表里的第一个字段是VARCHAR(10)类型的,第一个字段的长度是多少我们是不知道的!所以有可能你读取出来“hello a a hi”是一行数据,也可能是你读取出来“hello a”是一行数据,你在不知道一行数据的每个字段到底是多少长度的情况下,胡乱的去读取是不现实的,根本不知道磁盘文件里混成一坨的数据里,哪些数据是你要读取的一行?

  1.    引入变长字段的长度列表,解决一行数据的读取问题
    

所以说才要在存储每一行数据的时候,都保存一下他的变长字段的长度列表,这样才能解决一行数据的读取问题。也就是说,你在存储“hello a a”这行数据的时候,要带上一些额外的附加信息,比如第一块就是他里面的变长字段的长度列表。也就是说,这个hello是VARCHAR(10)类型的变长字段的值,那么这个“hello”字段值的长度到底是多少?我们看到“hello”的长度是5,十六进制就是0x05,所以此时会在“hello a a”前面补充一些额外信息,首先就是变长字段的长度列表,你会看到这行数据在磁盘文件里存储的时候,其实是类似如下的格式:0x05 null值列表 数据头 hello a a。这个时候假设你有两行数据,还有一行数据可能就是:0x02 null值列表 数据头 hi a a,两行数据放在一起存储在磁盘文件里,看起来是如下所示的:

0x05 null值列表 数据头 hello a a 0x02 null值列表 数据头 hi a a

  1.    引入变长字段长度列表后,如何解决变长字段的读取问题?
    

所以假设此时你要读取“hello a a”这行数据,你首先会知道这个表里的三个字段的类型是VARCHAR(10) CHAR(1) CHAR(1),那么此时你先要读取第一个字段的值,那么第一个字段是变长的,到底他的实际长度是多少呢?

此时你会发现第一行数据的开头有一个变长字段的长度列表,里面会读取到一个0x05这个十六进制的数字,发现第一个变长字段的长度是5,于是按照长度为5,读取出来第一个字段的值,就是“hello”。接着你知道后续两个字段都是CHAR(1),长度都是固定的1个字符,于是此时就依次按照长度为1读取出来后续两个字段的值,分别是“a”“a”,于是最终你会读取出来“hello a a”这一行数据!

接着假设你要读取第二行数据,你先看一下第二行数据后的变长字段长度列表,发现他第一个变长字段的长度是0x02,于是就读取长度为2的字段值,就是“hi”,再读取两个长度固定为1的字符值,都是“a”,此时读取出来“hi a a”这行数据。

  1.    如果有多个变长字段,如何存放他们的长度?
    

比如一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1),一共5个字段,其中三个是变长字段,此时假设一行数据是这样的:hello hi hao a a

此时在磁盘中存储的,必须在他开头的变长字段长度列表中存储几个变长字段的长度,一定要注意一点,他这里是逆序存储的!也就是说先存放VARCHAR(20)这个字段的长度,然后存放VARCHAR(5)这个字段的长度,最后存放VARCHAR(10)这个字段的长度。现在hello hi hao三个字段的长度分别是0x05 0x02 0x03,但是实际存放在变长字段长度列表的时候,是逆序放的,所以一行数据实际存储可能是下面这样的:0x03 0x02 0x05 null值列表 头字段 hello hi hao a a

  1.    为什么一行数据里的NULL值不能直接存储?
    

这个所谓的NULL值列表,顾名思义,说的就是你一行数据里可能有的字段值是NULL,比如你有一个name字段,他是允许为NULL的,那么实际上在存储的时候,如果你没给他赋值,他这个字段的值就是NULL。好,那么假设这个字段的NULL值我们在磁盘上存储的时候,就是按照“NULL”这么个字符串来存储,是不是很浪费存储空间?本来他就是个NULL,说明什么值都没有,你还给他存个“NULL”字符串,你说你这是干什么呢?所以实际在磁盘上存储数据的时候,一行数据里的NULL值是肯定不会直接按照字符串的方式存放在磁盘上浪费空间的。

  1.    NULL值是以二进制bit位来存储的
    

对所有的NULL值,不通过字符串在磁盘上存储,而是通过二进制的bit位来存储,一行数据里假设有多个字段的值都是NULL,那么这多个字段的NULL,就会以bit位的形式存放在NULL值列表中。

接着来看NULL值列表,这个NULL值列表是这样存放的,你所有允许值为NULL的字段,注意,是允许值为NULL,不是说一定值就是NULL了,只要是允许你为NULL的字段,在这里每个字段都有一个二进制bit位的值,如果bit值是1说明是NULL,如果bit值是0说明不是NULL。但是实际放在NULL值列表的时候,他是按逆序放的,所以在NULL值列表里,放的是:0101。

另外就是他实际NULL值列表存放的时候,不会说仅仅是4个bit位,他一般起码是8个bit位的倍数,如果不足8个bit位就高位补0,所以实际存放看起来是如下的:

0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN

  1.    磁盘上的一行数据到底如何读取出来的?
    

首先他必然要把变长字段长度列表和NULL值列表读取出来,通过综合分析一下,就知道有几个变长字段,哪几个变长字段是NULL,因为NULL值列表里谁是NULL谁不是NULL都一清二楚。

此时就可以从变长字段长度列表中解析出来不为NULL的变长字段的值长度,然后也知道哪几个字段是NULL的,此时根据这些信息,就可以从实际的列值存储区域里,把你每个字段的值读取出来了。

如果是变长字段的值,就按照他的值长度来读取,如果是NULL,就知道他是个NULL,没有值存储,如果是定长字段,就按照定长长度来读取,这样就可以完美的把你一行数据的值都读取出来了!

  1.    磁盘文件中, 40个bit位的数据头以及真实数据是如何存储的? 
    

每一行数据存储的时候,还得有40个bit位的数据头,这个数据头是用来描述这行数据的。

这40个bit位里,第一个bit位和第二个bit位,都是预留位,是没任何含义的。

然后接下来有一个bit位是delete_mask,他标识的是这行数据是否被删除了,其实看到这个bit位,很多人可能已经反映过来了,这么说在MySQL里删除一行数据的时候,未必是立马把他从磁盘上清理掉,而是给他在数据头里搞1个bit标记他已经被删了?

然后下一个bit位是min_rec_mask,他其实就是说在B+树里每一层的非叶子节点里的最小值都有这个标记。

接下来有4个bit位是n_owned,他其实就是记录了一个记录数。

接着有13个bit位是heap_no,他代表的是当前这行数据在记录堆里的位置。

然后是3个bit位的record_type,这就是说这行数据的类型,0代表的是普通类型,1代表的是B+树非叶子节点,2代表的是最小值数据,3代表的是最大值数据。

最后是16个bit的next_record,这个是指向他下一条数据的指针。

  1.    我们每一行的实际数据在磁盘上是如何存储的?
    

实际上字符串这些东西都是根据我们数据库指定的字符集编码,进行编码之后再存储的,所以大致看起来一行数据是如下所示的:

0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262

大家会看到上面,我们的字符串和其他类型的数值最终都会根据字符集编码,搞成一些数字和符号存储在磁盘上。在实际存储一行数据的时候,会在他的真实数据部分,加入一些隐藏字段。

首先有一个DB_ROW_ID字段,这就是一个行的唯一标识,是他数据库内部给你搞的一个标识,不是你的主键ID字段。如果我们没有指定主键和unique key唯一索引的时候,他就内部自动加一个ROW_ID作为主键。

接着是一个DB_TRX_ID字段,这是跟事务相关的,他是说这是哪个事务更新的数据,这是事务ID。

最后是DB_ROLL_PTR字段,这是回滚指针,是用来进行事务回滚的。

所以如果你加上这几个隐藏字段之后,实际一行数据可能看起来如下所示:

0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C(DB_ROW_ID)00000000032D(DB_TRX_ID) EA000010078E(DB_ROL_PTR) 616161 636320 6262626262

  1.    行溢出是什么东西?
    

我们之前已经初步了解到,实际上我们每一行数据都是放在一个数据页里的,这个数据页默认的大小是16KB,那么之前就有人在后台提过一个问题:万一 一行数据的大小超过了页的大小怎么办呢?

比如有一个表的字段类型是VARCHAR(65532),意思就是最大可以包含65532个字符,那也就是65532个字节,这就远大于16kb的大小了,也就是说这一行数据的这个字段都远超一个数据页的大小了!

     这个时候实际上会在那一页里存储你这行数据,然后在那个字段中,仅仅包含他一部分数据,同时包含一个20个字节的指针,指向了其他的一些数据页,那些数据页用链表串联起来,存放这个VARCHAR(65532)超大字段里的数据。

上面说的这个过程,其实就叫做行溢出,就是说一行数据存储的内容太多了,一个数据页都放不下了,此时只能溢出这个数据页,把数据溢出存放到其他数据页里去,那些数据页就叫做溢出页。包括其他的一些字段类型都是一样的,比如TEXT、BLOB这种类型的字段,都有可能出现溢出,然后一行数据就会存储在多个数据页里。

  1.    表空间以及划分多个数据页的数据区,又是什么概念?
    

现在我们在大致了解了数据页的结构和使用之后,我们可以继续来了解下一个概念,就是表空间和数据区的概念。

简单来说,就是我们平时创建的那些表,其实都是有一个表空间的概念,在磁盘上都会对应着“表名.ibd”这样的一个磁盘数据文件。所以其实在物理层面,表空间就是对应一些磁盘上的数据文件。有的表空间,比如系统表空间可能对应的是多个磁盘文件,有的我们自己创建的表对应的表空间可能就是对应了一个“表名.ibd”数据文件。

然后在表空间的磁盘文件里,其实会有很多很多的数据页,因为大家都知道一个数据页不过就是16kb而已,总不可能一个数据页就是一个磁盘文件吧。所以一个表空间的磁盘文件里,其实是有很多的数据页的。但是现在有一个问题,就是一个表空间里包含的数据页实在是太多了,不便于管理,所以在表空间里又引入了一个数据区的概念,英文就是extent。

一个数据区对应着连续的64个数据页,每个数据页是16kb,所以一个数据区是1mb,然后256个数据区被划分为了一组。

对于表空间而言,他的第一组数据区的第一个数据区的前3个数据页,都是固定的,里面存放了一些描述性的数据。比如FSP_HDR这个数据页,他里面就存放了表空间和这一组数据区的一些属性。IBUF_BITMAP数据页,里面存放的是这一组数据页的所有insert buffer的一些信息。INODE数据页,这里也是存放了一些特殊的信息。

然后这个表空间里的其他各组数据区,每一组数据区的第一个数据区的头两个数据页,都是存放特殊信息的,比如XDES数据页就是用来存放这一组数据区的一些相关属性的,其实就是很多描述这组数据区的东西。

我们平时创建的那些表都是有对应的表空间的,每个表空间就是对应了磁盘上的数据文件,在表空间里有很多组数据区,一组数据区是256个数据区,每个数据区包含了64个数据页,是1mb。然后表空间的第一组数据区的第一个数据区的头三个数据页,都是存放特殊信息的;表空间的其他组数据区的第一个数据区的头两个数据页,也都是存放特殊信息的。

当我们需要执行crud操作的时候,说白了,就是从磁盘上的表空间的数据文件里,去加载一些数据页出来到Buffer Pool的缓存页里去使用。

06_Redo log和Undo log

  1.      redo日志对于事务提交后,数据绝对不会丢失的意义 
    

我们知道,在更新完Buffer Pool中的缓存页之后,必须要写一条redo log,这样才能记录下来我们对数据库做的修改。

redo log可以保证我们事务提交之后,如果事务中的增删改SQL语句更新的缓存页还没刷到磁盘上去,此时MySQL宕机了,那么MySQL重启过后,就可以把redo log重做一遍,恢复出来事务当时更新的缓存页,然后再把缓存页刷到磁盘就可以了。

redo log本质是保证事务提交之后,修改的数据绝对不会丢失的。

首先我们都知道,执行增删改SQL语句的时候,都是针对一个表中的某些数据去执行的,此时的话,首先必须找到这个表对应的表空间,然后找到表空间对应的磁盘文件,接着从磁盘文件里把你要更新的那批数据所在的数据页从磁盘读取出来,放到Buffer Pool的缓存页里去。接着实际上你的增删改SQL语句就会针对Buffer Pool中的缓存页去执行你的更新逻辑,比如插入一行数据,或者更新一行数据,或者是删除一行数据。

其实你更新缓存页的时候,会更新free链表、flush链表、lru链表,然后有专门的后台IO线程,不定时的根据flush链表、lru链表,会把你更新过的缓存页刷新回磁盘文件的数据页里去。所以大家都知道这个机制里最大的漏洞就在于,万一你一个事务里有增删改SQL更新了缓存页,然后事务提交了,结果万一你还没来得及让IO线程把缓存页刷新到磁盘文件里,此时MySQL宕机了,然后内存数据丢失,你事务更新的数据就丢失了!

但是也不可能每次你事务一提交,就把你事务更新的缓存页都刷新回磁盘文件里去,因为大家之前也都知道,缓存页刷新到磁盘文件里,是随机磁盘读写,性能是相当的差!这会导致你数据库性能和并发能力都很弱的!所以此时才会引入一个redo log机制,这个机制就是说,你提交事务的时候,绝对是保证把你对缓存页做的修改以日志的形式,写入到redo log日志文件里去的。这种日志大致的格式如下:对表空间XX中的数据页XX中的偏移量为XXXX的地方更新了数据XXX。

只要你事务提交的时候保证你做的修改以日志形式写入redo log日志,那么哪怕你此时突然宕机了,也没关系!因为你MySQL重启之后,把你之前事务更新过做的修改根据redo log在Buffer Pool里重做一遍就可以了,就可以恢复出来当时你事务对缓存页做的修改,然后找时机再把缓存页刷入磁盘文件里去。

那么有人会问了,你事务提交的时候把修改过的缓存页都刷入磁盘,跟你事务提交的时候把你做的修改的redo log都写入日志文件,他们不都是写磁盘么?差别在哪里?实际上,如果你把修改过的缓存页都刷入磁盘,这首先缓存页一个就是16kb,数据比较大,刷入磁盘比较耗时,而且你可能就修改了缓存页里的几个字节的数据,难道也把完整的缓存页刷入磁盘吗?而且你缓存页刷入磁盘是随机写磁盘,性能是很差的,因为他一个缓存页对应的位置可能在磁盘文件的一个随机位置,比如偏移量为45336这个地方。

但是如果是写redo log,第一个一行redo log可能就占据几十个字节,就包含表空间号、数据页号、磁盘文件偏移量、更新值,这个写入磁盘速度很快。此外,redo log写日志,是顺序写入磁盘文件,每次都是追加到磁盘文件末尾去,速度也是很快的。所以你提交事务的时候,用redo log的形式记录下来你做的修改,性能会远远超过刷缓存页的方式,这也可以让你的数据库的并发能力更强。

  1.    写入日志文件的redo log长什么样?
    

redo log里本质上记录的就是在对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值,具体修改的值是什么,他里面需要记录的就是表空间号+数据页号+偏移量+修改几个字节的值+具体的值。

所以根据你修改了数据页里的几个字节的值,redo log就划分为了不同的类型,MLOG_1BYTE类型的日志指的就是修改了1个字节的值,MLOG_2BYTE类型的日志指的就是修改了2个字节的值,以此类推,还有修改了4个字节的值的日志类型,修改了8个字节的值的日志类型。当然,如果你要是一下子修改了一大串的值,类型就是MLOG_WRITE_STRING,就是代表你一下子在那个数据页的某个偏移量的位置插入或者修改了一大串的值。

所以其实一条redo log看起来大致的结构如下所示:

日志类型(就是类似MLOG_1BYTE之类的),表空间ID,数据页号,数据页中的偏移量,具体修改的数据

大致就是一条redo log中依次排列上述的一些东西,这条redo log表达的语义就很明确了,他的类型是什么,类型就告诉了你他这次增删改操作修改了多少字节的数据;然后在哪个表空间里操作的,这个就是跟你SQL在哪个表里执行的是对应的;接着就是在这个表空间的哪个数据页里执行的,在数据页的哪个偏移量开始执行的,具体更新的数据是哪些呢。有了上述信息,就可以精准完美的还原出来一次数据增删改操作做的变动了。只不过如果是MLOG_WRITE_STRING类型的日志,因为不知道具体修改了多少字节的数据,所以其实会多一个修改数据长度,就告诉你他这次修改了多少字节的数据,如下所示他的格式:

日志类型(就是类似MLOG_1BYTE之类的),表空间ID,数据页号,数据页中的偏移量,修改数据长度,具体修改的数据

  1.    redo log是直接一条一条写入文件的吗?
    

平时我们执行CRUD的时候,从磁盘加载数据页到buffer pool的缓存页里去,然后对缓存页执行增删改,同时还会写redo log到日志文件里去,后续不定时把缓存页刷回磁盘文件里去,大概就是这个原理。

redo log就是一条一条的直接就写入到磁盘上的日志文件里去了吗?显然不是的!

其实MySQL内有另外一个数据结构,叫做redo log block,大概你可以理解为,平时我们的数据不是存放在数据页了的么,用一页一页的数据页来存放数据。那么对于redo log也不是单行单行的写入日志文件的,他是用一个redo log block来存放多个单行日志的。一个redo log block是512字节,这个redo log block的512字节分为3个部分,一个是12字节的header块头,一个是496字节的body块体,一个是4字节的trailer块尾。

在这里面,12字节的header头又分为了4个部分。

包括4个字节的block no,就是块唯一编号;2个字节的data length,就是block里写入了多少字节数据;2个字节的first record group。这个是说每个事务都会有多个redo log,是一个redo log group,即一组redo log。那么在这个block里的第一组redo log的偏移量,就是这2个字节存储的;4个字节的checkpoint on。

所以其实对于我们的redo log而言,他确实是不停的追加写入到redo log磁盘文件里去的,但是其实每一个redo log都是写入到文件里的一个redo log block里去的,一个block最多放496自己的redo log日志。

此时可能有人会有疑问了,到底一个一个的redo log block在日志文件里是怎么存在的?那么一条一条的redo log又是如何写入日志文件里的redo log block里去的呢?假设你有一个redo log日志文件,平时我们往里面写数据,你大致可以认为是从第一行开始,从左往右写,可能会有很多行,那么所以现在既然如此,假设你要写第一个redo log了,是不是应该起码是先在内存里把这个redo log给弄到一个redo log block数据结构里去?然后似乎你应该是等内存里的一个redo log block的512字节都满了,再一次性把这个redo log block写入磁盘文件?然后其实按照我们所说的,一个redo log block就是512字节,那么是不是真正写入的时候,把这个redo log block的512字节的数据,就写入到redo log文件里去就可以了?那么redo log文件里就多了一个block。

  1.    redo log buffer中的缓冲日志,到底什么时候可以写入磁盘?
    

redo log buffer的缓冲机制,redo log在写的时候,都是一个事务里的一组redo log,先暂存在一个地方,完事儿了以后把一组redo log写入redo log buffer。写入redo log buffer的时候,是写入里面提前划分好的一个一个的redo log block的,选择有空闲空间的redo log block去写入,然后redo log block写满之后,其实会在某个时机刷入到磁盘里去。那么到底redo log buffer里的redo log block什么时候可以刷入到磁盘文件里去呢?另外,磁盘上到底有几个redo log日志文件?不可能大量的redo log日志都放一个文件里吧?磁盘空间会占用的越来越多吗?

首先,我们先来看看redo log block是哪些时候会刷入到磁盘文件里去:

①如果写入redo log buffer的日志已经占据了redo log buffer总容量的一半了,也就是超过了8MB的redo log在缓冲里了,此时就会把他们刷入到磁盘文件里去。

②一个事务提交的时候,必须把他的那些redo log所在的redo log block都刷入到磁盘文件里去,只有这样,当事务提交之后,他修改的数据绝对不会丢失,因为redo log里有重做日志,随时可以恢复事务做的修改。

③后台线程定时刷新,有一个后台线程每隔1秒就会把redo log buffer里的redo log block刷到磁盘文件里去。

④MySQL关闭的时候,redo log block都会刷入到磁盘里去。

忽略上面的第四条不说,因为关闭MySQL的时候必然会刷redo log到磁盘,其他三条其实我们都看到了,也就是说,如果你瞬间执行大量的高并发的SQL语句,1秒内就产生了超过8MB的redo log,此时占据了redo log buffer一半的空间了,必然会直接把你的redo log刷入磁盘里去。

上面这种redo log刷盘的情况,在MySQL承载高并发请求的时候比较常见,比如每秒执行上万个增删改SQL语句,每个SQL产生的redo log假设有几百个字节,此时却是会在瞬间生成超过8MB的redo log日志,必然会触发立马刷新redo log到磁盘。

其次,第二种情况,其实就是平时执行一个事务,这个事务一般都是在几十毫秒到几百毫秒执行完毕的,说实在的,一般正常性能情况下,MySQL单事务性能一般不会超过1秒,否则数据库操作就太慢了。那么如果在几十毫秒,或者几百毫秒的时候,执行完毕了一个事务,此时必然会立马把这个事务的redo log都刷入磁盘。

第一种情况其实是不常见的,第二种情况是比较常见的,往往redo log刷盘都是以一个短事务提交时候发生的,第三种情况就是后台线程每秒自动刷新redo log到磁盘去,这个就是说假设没有别的情况触发,后台线程自己都会不停的刷新redo log到磁盘。但是不管怎么说,主要是保证一个事务执行的时候,redo log都进入redo log buffer,提交事务的时候,事务对应的redo log必须是刷入磁盘文件,接着才算是事务提交成功,否则事务提交就是失败,保证这一点,就能确保事务提交之后,数据不会丢,有redo log在磁盘里就行了。

当然,绝对保证数据不丢,还得配置一个参数,提交事务把redo log刷入磁盘文件的os cache之后,还得强行从os cache刷入物理磁盘。最后说一下redo log日志文件的问题,我们都知道平时不停的执行增删改,那么MySQL会不停的产生大量的redo log写入日志文件,那么日志文件就用一个写入全部的redo log?对磁盘占用空间越来越大怎么办?

这些问题都可以解决,实际上默认情况下,redo log都会写入一个目录中的文件里,这个目录可以通过show variables like 'datadir'来查看,可以通过innodb_log_group_home_dir参数来设置这个目录的。然后redo log是有多个的,写满了一个就会写下一个redo log,而且可以限制redo log文件的数量,通过innodb_log_file_size可以指定每个redo log文件的大小,默认是48MB,通过innodb_log_files_in_group可以指定日志文件的数量,默认就2个。所以默认情况下,目录里就两个日志文件,分别为ib_logfile0和ib_logfile1,每个48MB,最多就这2个日志文件,就是先写第一个,写满了写第二个。那么如果第二个也写满了呢?别担心,继续写第一个,覆盖第一个日志文件里原来的redo log就可以了。

所以最多这个redo log,mysql就给你保留了最近的96MB的redo log而已,不过这其实已经很多了,毕竟redo log真的很小,一条通常就几个字节到几十个字节不等,96MB足够你存储上百万条redo log了!如果你还想保留更多的redo log,其实调节上述两个参数就可以了,比如每个redo log文件是96MB,最多保留100个redo log文件。

  1.    如果事务执行到一半要回滚怎么办?
    

在执行增删改操作时候的redo log的重做日志原理,其实说白了,就是你对buffer pool里的缓存页执行增删改操作的时候,必须要写对应的redo log记录下来你做了哪些修改,redo log都是先进入redo log buffer中的一个block,然后事务提交的时候就会刷入磁盘文件里去。

这样万一要是你提交事务了,结果事务修改的缓存页还没来得及刷入磁盘上的数据文件,此时你MySQL关闭了或者是宕机了,那么buffer pool里被事务修改过的数据就全部都丢失了!但是只要有redo log,你重启MySQL之后完全是可以把那些修改了缓存页,但是缓存页还没来得及刷入磁盘的事务,他们所对应的redo log都加载出来,在buffer pool的缓存页里重做一遍,就可以保证事务提交之后,修改的数据绝对不会丢!

假设现在我们一个事务里要执行一些增删改的操作,那么必然是先把对应的数据页从磁盘加载出来放buffer pool的缓存页里,然后在缓存页里执行一通增删改,同时记录redo log日志,对吧?但是现在问题来了,万一要是一个事务里的一通增删改操作执行到了一半,结果就回滚事务了呢?比如一个事务里有4个增删改操作,结果目前为止已经执行了2个增删改SQL了,已经更新了一些buffer pool里的数据了,但是还有2个增删改SQL的逻辑还没执行,此时事务要回滚了怎么办?

这个时候就很尴尬了,如果你要回滚事务的话,那么必须要把已经在buffer pool的缓存页里执行的增删改操作给回滚了。但是怎么回滚呢?毕竟无论是插入,还是更新,还是删除,该做的都已经做了啊!所以在执行事务的时候,才必须引入另外一种日志,就是undo log回滚日志。

这个回滚日志,他记录的东西其实非常简单,比如你要是在缓存页里执行了一个insert语句,那么此时你在undo log日志里,对这个操作记录的回滚日志就必须是有一个主键和一个对应的delete操作,要能让你把这次insert操作给回退了。那么比如说你要是执行的是delete语句,那么起码你要把你删除的那条数据记录下来,如果要回滚,就应该执行一个insert操作把那条数据插入回去。如果你要是执行的是update语句,那么起码你要把你更新之前的那个值记录下来,回滚的时候重新update一下,把你之前更新前的旧值给他更新回去。如果你要是执行的是select语句呢?不好意思,select语句压根儿没有在buffer pool里执行任何修改,所以根本不需要undo log!

其实你在执行事务期间,你除了写redo log日志还必须要写undo log日志,这个undo log日志是至关重要的,没有他,你根本都没办法回滚事务!

  1.    INSRET语句的undo log回滚日志长什么样?
    

undo log回滚日志的作用,就是你执行事务的时候,里面很多INSERT、UPDATE和DELETE语句都在更新缓存页里的数据,但是万一事务回滚,你必须有每条SQL语句对应的undo log回滚日志,根据回滚日志去恢复缓存页里被更新的数据。

INSERT语句的undo log的类型是TRX_UNDO_INSERT_REC,这个undo log里包含了以下一些东西:

这条日志的开始位置;主键的各列长度和值;表id;undo log日志编号;undo log日志类型;这条日志的结束位置;

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

09_MySQL锁机制

  1.    多个事务更新同一行数据时,是如何加锁避免脏写的?
    

脏写是绝对不允许的,那么这个脏写是靠什么防止的呢?说白了,就是靠锁机制,依靠锁机制让多个事务更新一行数据的时候串行化,避免同时更新一行数据。

在MySQL里,假设有一行数据摆在那儿不动,此时有一个事务来了要更新这行数据,这个时候他会先琢磨一下,看看这行数据此时有没有人加锁?一看没人加锁,太好了,说明他是第一个人,捷足先登了。

此时这个事务就会创建一个锁,里面包含了自己的trx_id和等待状态,然后把锁跟这行数据关联在一起。同时,更新一行数据必须把他所在的数据页从磁盘文件里读取到缓存页里来才能更新的,所以说,此时这行数据和关联的锁数据结构,都是在内存里的。因为事务A给那行数据加了锁,所以此时就可以说那行数据已经被加锁了。那么既然被加锁了,此时就不能再让别人访问了!

现在呢,有另外一个事务B过来了,这个事务B就也想更新那行数据,此时就会检查一下,当前这行数据有没有别人加锁。然而他一下子发现,真是糟糕啊,事务A这家伙太不地道了,居然抢先给这行数据加锁了,这怎么办呢?事务B这个时候一想,那行,我也加个锁,然后等着排队不就得了,这个时候事务B也会生成一个锁数据结构,里面有他的trx_id,还有自己的等待状态,但是他因为是在排队等待,所以他的等待状态就是true了,意思是我在等着呢。

接着事务A这个时候更新完了数据,就会把自己的锁给释放掉了。锁一旦释放了,他就会去找,此时还有没有别人也对这行数据加锁了呢?他会发现事务B也加锁了。于是这个时候,就会把事务B的锁里的等待状态修改为false,然后唤醒事务B继续执行,此时事务B就获取到锁了。

上述就是MySQL中锁机制的一个最基本的原理,其实是跟Java里的锁机制,思路是完全类似的,从这种简单的锁里可以引申出很多其他的概念,比如读写锁,共享锁,独占锁,公平锁,非公平锁,等等。Java里的锁,也同样具备这些锁的概念。

  1.    共享锁和独占锁到底是什么?
    

那么在这多个事务运行的时候,他们加的是什么锁呢?其实是X锁,也就是Exclude独占锁,当有一个事务加了独占锁之后,此时其他事务再要更新这行数据,都是要加独占锁的,但是只能生成独占锁在后面等待。

当有人在更新数据的时候,其他的事务可以读取这行数据吗?默认情况下需要加锁吗?答案是:不用。因为默认情况下,有人在更新数据的时候,然后你要去读取这行数据,直接默认就是开启mvcc机制的。也就是说,此时对一行数据的读和写两个操作默认是不会加锁互斥的,因为MySQL设计mvcc机制就是为了解决这个问题,避免频繁加锁互斥。此时你读取数据,完全可以根据你的ReadView,去在undo log版本链条里找一个你能读取的版本,完全不用去顾虑别人在不在更新。就算你真的等他更新完毕了还提交了,基于mvcc机制你也读不到他更新的值啊!因为ReadView机制是不允许的,所以你默认情况下的读,完全不需要加锁,不需要去care其他事务的更新加锁问题,直接基于mvcc机制读某个快照就可以了。

那么假设万一要是你在执行查询操作的时候,就是想要加锁呢?那也是ok的,MySQL首先支持一种共享锁,就是S锁,这个共享锁的语法如下:select * from table lock in share mode,你在一个查询语句后面加上lock in share mode,意思就是查询的时候对一行数据加共享锁。

如果此时有别的事务在更新这行数据,已经加了独占锁了,此时你的共享锁能加吗?当然不行了,共享锁和独占锁是互斥的!此时你这个查询就只能等着了。那么如果你先加了共享锁,然后别人来更新要加独占锁行吗?当然不行了,此时锁是互斥的,他只能等待。

那么如果你在加共享锁的时候,别人也加共享锁呢?此时是可以的,你们俩都是可以加共享锁的,共享锁和共享锁是不会互斥的。所以这里可以先看出一个规律,就是更新数据的时候必然加独占锁,独占锁和独占锁是互斥的,此时别人不能更新;但是此时你要查询,默认是不加锁的,走mvcc机制读快照版本,但是你查询是可以手动加共享锁的,共享锁和独占锁是互斥的,但是共享锁和共享锁是不互斥的,如下规律。

不过说实话,一般开发业务系统的时候,其实你查询主动加共享锁,这种情况较为少见,数据库的行锁是实用功能,但是一般不会在数据库层面做复杂的手动加锁操作,反而会用基于redis/zookeeper的分布式锁来控制业务系统的锁逻辑。

另外就是,查询操作还能加互斥锁,他的方法是:select * from table for update。这个意思就是,我查出来数据以后还要更新,此时我加独占锁了,其他闲杂人等,都不要更新这个数据了。一旦你查询的时候加了独占锁,此时在你事务提交之前,任何人都不能更新数据了,只能你在本事务里更新数据,等你提交了,别人再更新数据。

  1.    在数据库里,哪些操作会导致在表级别加锁呢?
    

在多个事务并发更新数据的时候,都是要在行级别加独占锁的,这就是行锁,独占锁都是互斥的,所以不可能发生脏写问题,一个事务提交了才会释放自己的独占锁,唤醒下一个事务执行。如果你此时去读取别的事务在更新的数据,有两种可能:第一种可能是基于mvcc机制进行事务隔离,读取快照版本,这是比较常见的;第二种可能是查询的同时基于特殊语法去加独占锁或者共享锁。如果你查询的时候加独占锁,那么跟其他更新数据的事务加的独占锁都是互斥的;如果你查询的时候加共享锁,那么跟其他查询加的共享锁是不互斥的,但是跟其他事务更新数据就加的独占锁是互斥的,跟其他查询加的独占锁也是互斥的。

在数据库里,你不光可以通过查询中的特殊语法加行锁,比如lock in share mode、for update等等,还可以通过一些方式在表级别去加锁。有些人可能会以为当你执行增删改的时候默认加行锁,然后执行DDL语句的时候,比如alter table之类的语句,会默认在表级别加表锁。这么说也不太正确,但是也有一定的道理,因为确实你执行DDL的时候,会阻塞所有增删改操作;执行增删改的时候,会阻塞DDL操作。

但这是通过MySQL通用的元数据锁实现的,也就是Metadata Locks,但这还不是表锁的概念。因为表锁其实是InnoDB存储引擎的概念,InnoDB存储引擎提供了自己的表级锁,跟这里DDL语句用的元数据锁还不是一个概念。只不过DDL语句和增删改操作,确实是互斥的。

  1.    表锁和行锁互相之间的关系以及互斥规则是什么呢?
    

MySQL里是如何加表锁的。这个MySQL的表锁,其实是极为鸡肋的一个东西,几乎一般很少会用到,表锁分为两种,一种就是表锁,一种是表级的意向锁。

首先说表锁,这个表锁,可以用如下语法来加:

LOCK TABLES xxx READ:这是加表级共享锁

LOCK TABLES xxx WRITE:这是加表级独占锁

其实一般来讲,几乎没人会用这两个语法去加表锁,这不是纯属没事儿找事儿么,所以才说表锁特别的鸡肋。

还有就是有另外两个情况会加表级锁。如果有事务在表里执行增删改操作,那在行级会加独占锁,此时其实同时会在表级加一个意向独占锁;如果有事务在表里执行查询操作,那么会在表级加一个意向共享锁。

其实平时我们操作数据库,比较常见的两种表锁,反而是更新和查询操作加的意向独占锁和意向共享锁,但是这个意向独占锁和意向共享锁,大家暂时可以当他是透明的就可以了,因为两种意向锁根本不会互斥。为啥呢?因为假设有一个事务要在表里更新id=10的一行数据,在表上加了一个意向独占锁,此时另外一个事务要在表里更新id=20的一行数据,也会在表上加一个意向独占锁,你觉得这两把锁应该互斥吗?明显是不应该互斥的啊,因为他们俩更新的都是表里不同的数据,你让他们俩在表上加的意向独占锁互斥干什么呢?所以意向锁之间是根本不会互斥的。

同理,假设一个事务要更新表里的数据,在表级加了一个意向独占锁,另外一个事务要在表里读取数据,在表级加了一个意向共享锁,此时你觉得表级的意向独占锁和意向共享锁应该互斥吗?当然不应该了!一个人要更新数据,一个人要读取数据,俩人在表上加的意向锁,凭什么要互斥?没天理啊!

所以说,这个所谓的表级的意向独占锁和意向共享锁,似乎是跟脱了裤子放屁一样,多此一举?但是我们接下来就要看看,手动加表级共享锁和独占锁,以及更新和查询的时候自动在表级加的意向共享锁和意向独占锁,他们之间反而是有一定的互斥关系,关系如下表所示。img

上面表格说的是在表上面手动加的独占锁和共享锁,以及更新数据和查询数据默认自动加的意向独占锁和意向共享锁,他们互相之间的互斥关系。其实更新数据自动加的表级意向独占锁,会跟你用 LOCK TABLES xxx WRITE 手动加的表级独占锁是互斥的,所以说,假设你手动加了表级独占锁,此时任何人都不能执行更新操作了!或者你用LOCK TABLES xxx READ手动加了表级共享锁,此时任何人也不能执行更新操作了,因为更新就要加意向独占锁,此时是跟你手动加的表级共享锁,是互斥的!其实一般来说,根本就不会手动加表级锁,所以一般来说读写操作自动加的表级意向锁,互相之间绝对不会互斥。

10_数据库索引原理及查询原理

  1.    假设没有任何索引,数据库是如何根据查询语句搜索数据的?
    

数据页在磁盘文件中的物理存储结构,数据页之间是组成双向链表的,然后数据页内部的数据行是组成单向链表的,而且数据行是根据主键从小到大排序的。然后每个数据页里都会有一个页目录,里面根据数据行的主键存放了一个目录,同时数据行是被分散存储到不同的槽位里去的,所以实际上每个数据页的目录里,就是这个页里每个主键跟所在槽位的映射关系。

所以假设你要根据主键查找一条数据,而且假设此时你数据库里那个表就没几条数据,那个表总共就一个数据页,那么就太简单了!首先就会先到数据页的页目录里根据主键进行二分查找。然后通过二分查找在目录里迅速定位到主键对应的数据是在哪个槽位里,然后到那个槽位里去,遍历槽位里每一行数据,就能快速找到那个主键对应的数据了。每个槽位里都有一组数据行,你就是在里面遍历查找就可以了。

但是假设你要是根据非主键的其他字段查找数据呢?那就尴尬了,此时你是没办法使用主键的那种页目录来二分查找的,只能进入到数据页里,根据单向链表依次遍历查找数据了,这就性能很差了。

好,那么现在假如我们有很多数据页呢?对了,一个表里往往都是有大量数据的,可能有多达成百上千个数据页,这些数据页就存放在物理磁盘文件里。所以此时是如何查询数据的呢?假设你要是没有建立任何索引,那么无论是根据主键查询,还是根据其他字段来条件查询,实际上都没有什么取巧的办法。

你一个表里所有数据页都是组成双向链表的吧?好,有链表就好办了,直接从第一个数据页开始遍历所有数据页,从第一个数据页开始,你得先把第一个数据页从磁盘上读取到内存buffer pool的缓存页里来。然后你就在第一个数据页对应的缓存页里,按照上述办法查找,假设是根据主键查找的,你可以在数据页的页目录里二分查找,假设你要是根据其他字段查找的,只能是根据数据页内部的单向链表来遍历查找。

那么假设第一个数据页没找到你要的那条数据呢?没办法,只能根据数据页的双向链表去找下一个数据页,然后读取到buffer pool的缓存页里去,然后按一样的方法在一个缓存页内部查找那条数据。如果依然还是查找不到呢?那只能根据双向链表继续加载下一个数据页到缓存页里来了,以此类推,循环往复。似乎是在做一个数据库里很尴尬的操作:全表扫描?

对了,其实上述操作过程,就是全表扫描,在你没有任何索引数据结构的时候,无论如何查找数据,说白了都是一个全表扫描的过程,就是根据双向链表依次把磁盘上的数据页加载到缓存页里去,然后在一个缓存页内部来查找那条数据。最坏的情况下,你就得把所有数据页里的每条数据都得遍历一遍,才能找到你需要的那条数据,这就是全表扫描!

  1.    不断在表中插入数据时,物理存储是如何进行页分裂的?
    

上述描述,其实就是没有索引情况下在一个表中的数据查询情况,这个速度可以说是慢到惊人,所以一般肯定是不能让查询走全表扫描的。因此正常在数据库中的查询,必须要运用到索引来加速查询的执行。

我们在一个表里不停的插入数据的时候,会涉及到一个页分裂的过程,也就是说,这个表里是如何出现一个又一个的数据页的。正常情况下我们在一个表里插入一些数据后,他们都会进入到一个数据页里去,在数据页内部,他们会组成一个单向链表。

是这样的,假设你不停的在表里插入数据,那么刚开始是不是就是不停的在一个数据页插入数据?接着数据越来越多,越来越多,此时就要再搞一个数据页了。但是此时会遇到一个问题,索引运作的一个核心基础就是要求你后一个数据页的主键值都大于前面一个数据页的主键值,但是如果你的主键是自增的,那还可以保证这一点,因为你新插入后一个数据页的主键值一定都大于前一个数据页的主键值。但是有时候你的主键并不是自增长的,所以可能会出现你后一个数据页的主键值里,有的主键是小于前一个数据页的主键值的。比如在第一个数据页里有一条数据的主键是10,第二个数据页里居然有一条数据的主键值是8,那此时肯定有问题了。

所以此时就会出现一个过程,叫做页分裂,就是万一你的主键值都是你自己设置的,那么在增加一个新的数据页的时候,实际上会把前一个数据页里主键值较大的,挪动到新的数据页里来,然后把你新插入的主键值较小的数据挪动到上一个数据页里去,保证新数据页里的主键值一定都比上一个数据页里的主键值大。

假设新数据页里,有两条数据的主键值明显是小于上一个数据页的主键值的。第一个数据页里有1、5、6三条数据,第二个数据页里有2、3、4三条数据,明显第二个数据页里的数据的主键值比第一个数据页里的5和6两个主键都小,所以这个是不行的。此时就会出现页分裂的行为,把新数据页里的两条数据挪动到上一个数据页,上一个数据页里挪两条数据到新数据页里去。所以上述就是一个页分裂的过程,核心目标就是保证下一个数据页里的主键值都比上一个数据页里的主键值要大。有了这个过程,保证了每个数据页的主键值,就能为后续的索引打下基础。

  1.    基于主键的索引是如何设计的,以及如何根据主键索引查询?
    

现在是这样的,假设我们有多个数据页,然后我们想要根据主键来查询数据,那么直接查询的话也是不行的,因为我们也不知道主键到底是在哪里,是不是?现在假设你要搜id=4的数据,你怎么知道在哪个数据页里?没有任何证据可以告诉你他到底是在哪个数据页里啊!所以假设还是这个样子的话,你也就只能全表扫描了,从第一个数据页开始,每个数据页都进入到页目录里查找主键,最坏情况下,所有数据页你都得扫描一遍,还是很坑的。

所以其实此时就需要针对主键设计一个索引了,针对主键的索引实际上就是主键目录,这个主键目录呢,就是把每个数据页的页号,还有数据页里最小的主键值放在一起,组成一个索引的目录。

现在我们有了主键目录就方便了,直接就可以到主键目录里去搜索,比如你要找id=3的数据,此时就会跟每个数据页的最小主键来比,首先id=3大于了数据页2里的最小主键值1,接着小于了数据页8里的最小主键值4。所以既然如此,你直接就可以定位到id=3的数据一定是在数据页2里的!假设你有很多的数据页,在主键目录里就会有很多的数据页和最小主键值,此时你完全可以根据二分查找的方式来找你要找的id到底在哪个数据页里!所以这个效率是非常之高的,主键目录,就可以认为是主键索引。

而我们的数据页都是一坨一坨的连续数据放在很多磁盘文件里的,所以只要你能够根据主键索引定位到数据所在的数据页,此时假设我们有别的方式存储了数据页跟磁盘文件的对应关系,此时你就可以找到一个磁盘文件。而且我们假设数据页在磁盘文件里的位置也就是offset偏移量,你也是可以知道的,此时就可以直接通过随机读的方式定位到磁盘文件的某个offset偏移量的位置,然后就可以读取连续的一大坨数据页了!

  1.    索引的页存储物理结构,是如何用B+树来实现的?
    

你的表里的数据可能很多很多,比如有几百万,几千万,甚至单表几亿条数据都是有可能的,所以此时你可能有大量的数据页,然后你的主键目录里就要存储大量的数据页和最小主键值,这怎么行呢?

所以在考虑这个问题的时候,实际上是采取了一种把索引数据存储在数据页里的方式来做的。也就是说,你的表的实际数据是存放在数据页里的,然后你表的索引其实也是存放在页里的,此时索引放在页里之后,就会有索引页,假设你有很多很多的数据页,那么此时你就可以有很多的索引页。

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

现在就好了,假设我们要查找id=46的,直接先到最顶层的索引页35里去找,直接通过二分查找可以定位到下一步应该到索引页20里去找,接下来到索引页20里通过二分查找定位,也很快可以定位到数据应该在数据页8里,再进入数据页8里,就可以找到id=46的那行数据了。

那么现在问题再次来了,假如你最顶层的那个索引页里存放的下层索引页的页号也太多了,怎么办呢?此时可以再次分裂,再加一层索引页。索引页不知不觉中组成了多个层级,搞的是不是有点像一棵树?没错了,这就是一颗B+树,属于数据结构里的一种树形数据结构,所以一直说MySQL的索引是用B+树来组成的,其实就是这个意思。

我们就以最简单最基础的主键索引来举例,当你为一个表的主键建立起来索引之后,其实这个主键的索引就是一颗B+树,然后当你要根据主键来查数据的时候,直接就是从B+树的顶层开始二分查找,一层一层往下定位,最终一直定位到一个数据页里,在数据页内部的目录里二分查找,找到那条数据。这就是索引最真实的物理存储结构,采用跟数据页一样的页结构来存储,一个索引就是很多页组成的一颗B+树。

  1.    更新数据的时候,自动维护的聚簇索引到底是什么?
    

首先呢,现在假设我们要搜索一个主键id对应的行,此时你就应该先去顶层的索引页88里去找,通过二分查找的方式,很容易就定位到你应该去下层哪个索引页里继续找。基于索引数据结构去查找主键的一个过程,其实最下层的索引页,都是会有指针引用数据页的,所以实际上索引页之间跟数据页之间是有指针连接起来的。

另外呢,其实索引页自己内部,对于一个层级内的索引页,互相之间都是基于指针组成双向链表的。

假设你把索引页和数据页综合起来看,他们都是连接在一起的,看起来就如同一颗完整的大的B+树一样,从根索引页88开始,一直到所有的数据页,其实组成了一颗巨大的B+树。在这颗B+树里,最底层的一层就是数据页,数据页也就是B+树里的叶子节点了!所以,如果一颗大的B+树索引数据结构里,叶子节点就是数据页自己本身,那么此时我们就可以称这颗B+树索引为聚簇索引!也就是说,所有的索引页+数据页组成的B+树就是聚簇索引!

其实在InnoDB存储引擎里,你在对数据增删改的时候,就是直接把你的数据页放在聚簇索引里的,数据就在聚簇索引里,聚簇索引就包含了数据!比如你插入数据,那么就是在数据页里插入数据。如果你的数据页开始进行页分裂了,他此时会调整各个数据页内部的行数据,保证数据页内的主键值都是有顺序的,下一个数据页的所有主键值大于上一个数据页的所有主键值。同时在页分裂的时候,会维护你的上层索引数据结构,在上层索引页里维护你的索引条目,不同的数据页和最小主键值。然后如果你的数据页越来越多,一个索引页放不下了,此时就会再拉出新的索引页,同时再搞一个上层的索引页,上层索引页里存放的索引条目就是下层索引页页号和最小主键值。

按照这个顺序,以此类推,如果你的数据量越大,此时可能就会多出更多的索引页层级来,不过说实话,一般索引页里可以放很多索引条目,所以通常而言,即使你是亿级的大表,基本上大表里建的索引的层级也就三四层而已。这个聚簇索引默认是按照主键来组织的,所以你在增删改数据的时候,一方面会更新数据页,一方面其实会给你自动维护B+树结构的聚簇索引,给新增和更新索引页,这个聚簇索引是默认就会给你建立的。

  1.    针对主键之外的字段建立的二级索引,又是如何运作的?
    

如果我们想要对其他的字段建立索引,甚至是基于多个字段建立联合索引,此时这个索引结构又是如何的呢?

其实假设你要是针对其他字段建立索引,比如name、age之类的字段,这都是一样的原理,简单来说,比如你插入数据的时候,一方面会把完整数据插入到聚簇索引的叶子节点的数据页里去,同时维护好聚簇索引,另一方面会为你其他字段建立的索引,重新再建立一颗B+树。

比如你基于name字段建立了一个索引,那么此时你插入数据的时候,就会重新搞一颗B+树,B+树的叶子节点也是数据页,但是这个数据页里仅仅放主键字段和name字段。这是独立于聚簇索引之外的另外一个索引B+树了,严格来说是name字段的索引B+树,所以在name字段的索引B+树里,叶子节点的数据页里仅仅放主键和name字段的值,至于排序规则之类的,都是一样的。然后呢,name字段的索引B+树也会构建多层级的索引页,这个索引页里存放的就是下一层的页号和最小name字段值,整体规则都是一样的,只不过存放的都是name字段的值,根据name字段值排序罢了。

所以假设你要根据name字段来搜索数据,那搜索过程简直都一样了,不就是从name字段的索引B+树里的根节点开始找,一层一层往下找,一直找到叶子节点的数据页里,定位到name字段值对应的主键值。然后呢?此时针对select * from table where name='xx'这样的语句,你先根据name字段值在name字段的索引B+树里找,找到叶子节点也仅仅可以找到对应的主键值,而找不到这行数据完整的所有字段。所以此时还需要进行“回表”,这个回表,就是说还需要根据主键值,再到聚簇索引里从根节点开始,一路找到叶子节点的数据页,定位到主键对应的完整数据行,此时才能把select *要的全部字段值都拿出来。

因为我们根据name字段的索引B+树找到主键之后,还要根据主键去聚簇索引里找,所以一般把name字段这种普通字段的索引称之为二级索引,一级索引就是聚簇索引,这就是普通字段的索引的运行原理。

其实我们也可以把多个字段联合起来,建立联合索引,比如name+age。此时联合索引的运行原理也是一样的,只不过是建立一颗独立的B+树,叶子节点的数据页里放了id+name+age,然后默认按照name排序,name一样就按照age排序,不同数据页之间的name+age值的排序也如此。然后这个name+age的联合索引的B+树的索引页里,放的就是下层节点的页号和最小的name+age的值,以此类推,所以当你根据name+age搜索的时候,就会走name+age联合索引的这颗B+树了,搜索到主键,再根据主键到聚簇索引里去搜索。

以上,就是innodb存储引擎的索引的完整实现原理了,其实一步一步看下来,会发现索引这块知识也没那么难,不过就是建立B+树,根据B+树一层一层二分查找罢了,然后不同的索引就是建立不同的B+树,然后你增删改的时候,一方面在数据页里更新数据,一方面就是维护你所有的索引。

  1.    插入数据时到底是如何维护好不同索引的B+树的?
    

在插入数据的时候,是如何维护不同索引的B+树的。首先呢,其实刚开始你一个表搞出来以后,其实他就一个数据页,这个数据页就是属于聚簇索引的一部分,而且目前还是空的。此时如果你插入数据,就是直接在这个数据页里插入就可以了,也没必要给他弄什么索引页。然后呢,这个初始的数据页其实就是一个根页,每个数据页内部默认就有一个基于主键的页目录,所以此时你根据主键来搜索都是ok没有问题的,直接在唯一一个数据页里根据页目录找就行了。

然后你表里的数据越来越多了,此时你的数据页满了,那么就会搞一个新的数据页,然后把你根页面里的数据都拷贝过去,同时再搞一个新的数据页,根据你的主键值的大小进行挪动,让两个新的数据页根据主键值排序,第二个数据页的主键值都大于第一个数据页的主键值。此时根页就升级为索引页了,这个根页里放的是两个数据页的页号和他们里面最小的主键值。接着你肯定会不停的在表里灌入数据,然后数据页不停的页分裂,分裂出来越来越多的数据页。

此时你的唯一一个索引页,也就是根页里存放的数据页索引条目越来越多,连你的索引页都放不下了,那你就让一个索引页分裂成两个索引页,然后根页继续往上走一个层级引用了两个索引页。接着就是依次类推了,你的数据页越来越多,那么根页指向的索引页也会不停分裂,分裂出更多的索引页,当你下层的索引页数量太多的时候,会导致你的根页指向的索引页太多了,此时根页继续分裂成多个索引页,根页再次往上提上去去一个层级。

这其实就是你增删改的时候,整个聚簇索引维护的一个过程,其实其他的二级索引也是类似的一个原理。比如你name字段有一个索引,那么刚开始的时候你插入数据,一方面在聚簇索引的唯一的数据页里插入,一方面在name字段的索引B+树唯一的数据页里插入。然后后续数据越来越多了,你的name字段的索引B+树里唯一的数据页也会分裂,整个分裂的过程跟上面说的是一样的,所以你插入数据的时候,本身就会自动去维护你的各个索引的B+树。

另外补充一点,name字段的索引B+树里的索引页中,其实除了存放页号和最小name字段值以外,每个索引页里还会存放那个最小name字段值对应的主键值。这是因为有时候会出现多个索引页指向的下层页号的最小name字段值是一样的,此时就必须根据主键判断一下。比如你插入了一个新的name字段值,此时他需要根据name字段的B+树索引的根页面开始,去逐层寻找和定位自己这个新的name字段值应该插入到叶子节点的哪个数据页里去。此时万一遇到一层里不同的索引页指向不同的下层页号,但是name字段值一样,此时就得根据主键值比较一下。新的name字段值肯定是插入到主键值较大的那个数据页里去的。

  1.    一个表里是不是索引搞的越多越好?那你就大错特错了!
    

好处显而易见,你可以直接根据某个字段的索引B+树来查找数据,不需要全表搜索,性能提升是很高的。但是坏处呢?索引当然有缺点了,主要是两个缺点,一个是空间上的,一个是时间上的。

空间上而言,你要是给很多字段创建很多的索引,那你必须会有很多棵索引B+树,每一棵B+树都要占用很多的磁盘空间啊!所以你要是搞的索引太多了,是很耗费磁盘空间的。其次,你要是搞了很多索引,那么你在进行增删改查的时候,每次都需要维护各个索引的数据有序性,因为每个索引B+树都要求页内是按照值大小排序的,页之间也是有序的,下一个页的所有值必须大于上一个页的所有值!所以你不停的增删改查,必然会导致各个数据页之间的值大小可能会没有顺序,比如下一个数据页里插入了一个比较小的值,居然比上一个数据页的值要小!此时就没办法了,只能进行数据页的挪动,维护页之间的顺序。

或者是你不停的插入数据,各个索引的数据页就要不停的分裂,不停的增加新的索引页,这个过程都是耗费时间的。所以你要是一个表里搞的索引太多了,很可能就会导致你的增删改的速度就比较差了,也许查询速度确实是可以提高,但是增删改就会受到影响,因此通常来说,我们是不建议一个表里搞的索引太多的!

那么怎么才能尽量用最少的索引满足最多的查询请求,还不至于让索引占用太多磁盘空间,影响增删改性能呢?这就需要我们深入理解索引的使用规则了,我们的SQL语句要怎么写,才能用上索引B+树来查询!

  1.    深入理解联合索引查询原理以及全值匹配规则
    

平时我们设计系统的时候一般都是设计联合索引,很少用单个字段做索引,我们还是要尽可能的让索引数量少一些,避免磁盘占用太多,增删改性能太差。

此时就涉及到了一个索引使用的规则,那就是你发起的SQL语句里,where条件里的几个字段都是基于等值来查询,都是用的等于号!而且where条件里的几个字段的名称和顺序也跟你的联合索引一模一样!此时就是等值匹配规则,SQL语句是百分百可以用联合索引来查询的。那么查询的过程也很简单了,首先到索引页里去找,索引页里有多个数据页的最小值记录,此时直接在索引页里基于二分查找法来找就可以了,先是根据班级名称来找1班这个值对应的数据页,直接可以定位到他所在的数据页。

然后你就直接找到索引指向的那个数据页就可以了,在数据页内部本身也是一个单向链表,你也是直接就做二分查找就可以了。然后就根据主键id到聚簇索引里按照一样的思路,从索引根节点开始二分查找迅速定位下个层级的页,再不停的找,很快就可以找到那条数据,然后从里面提取所有字段,就可以了。

上面整个过程就是联合索引的查找过程,以及全值匹配规则,假设你的SQL语句的where条件里用的几个字段的名称和顺序,都跟你的索引里的字段一样,同时你还是用等号在做等值匹配,那么直接就会按照上述过程来找。对于联合索引而言,就是依次按照各个字段来进行二分查找,先定位到第一个字段对应的值在哪个页里,然后如果第一个字段有多条数据值都一样,就根据第二个字段来找,以此类推,一定可以定位到某条或者某几条数据!

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

等值匹配规则,就是你where语句中的几个字段名称和联合索引的字段完全一样,而且都是基于等号的等值匹配,那百分百会用上我们的索引,这个大家是没有问题的,即使你where语句里写的字段的顺序和联合索引里的字段顺序不一致,也没关系,MySQL会自动优化为按联合索引的字段顺序去找。

现在看第二个规则,就是最左侧列匹配,这个意思就是假设我们联合索引是KEY(class_name, student_name, subject_name),那么不一定必须要在where语句里根据三个字段来查,其实只要根据最左侧的部分字段来查,也是可以的。

第三个规则,是最左前缀匹配原则,即如果你要用like语法来查,比如select * from student_score where class_name like '1%',查找所有1打头的班级的分数,那么也是可以用到索引的。因为你的联合索引的B+树里,都是按照class_name排序的,所以你要是给出class_name的确定的最左前缀就是1,然后后面的给一个模糊匹配符号,那也是可以基于索引来查找的,这是没问题的。

第四个规则,就是范围查找规则,这个意思就是说,我们可以用select * from student_score where class_name>'1班' and class_name<'5班'这样的语句来范围查找某几个班级的分数。这个时候也是会用到索引的,因为我们的索引的最下层的数据页都是按顺序组成双向链表的,所以完全可以先找到'1班'对应的数据页,再找到'5班'对应的数据页,两个数据页中间的那些数据页,就全都是在你范围内的数据了!

第五个规则,就是等值匹配+范围匹配的规则,如果你要是用select * from student_score where class_name='1班' and student_name>'' and subject_name<'',那么此时你首先可以用class_name在索引里精准定位到一波数据,接着这波数据里的student_name都是按照顺序排列的,所以student_name>''也会基于索引来查找,但是接下来的subject_name<''是不能用索引的。

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

  1.    当我们在SQL里进行排序的时候,如何才能使用索引?
    

通常而言,就我们自己想象一下,假设你有一个select * from table where xxx=xxx order by xxx这样的一个SQL语句,似乎应该是基于where语句通过索引快速筛选出来一波数据,接着放到内存里,或者放在一个临时磁盘文件里,然后通过排序算法按照某个字段走一个排序,最后把排序好的数据返回。但是这么搞通常速度有点慢,尤其是万一你要排序的数据量比较大的话,还不能用内存来排序,如果基于磁盘文件来排序,那在MySQL里有一个术语,叫做filesort,这速度就比较慢了。

通常而言,咱们尽量是最好别这么搞,尤其是类似于select * from table order by xx1,xx2,xx3 limit 100这样的SQL语句,按照多个字段进行排序然后返回排名前100条数据,类似的语句其实常常见于分页SQL语句里,可能需要对表里的数据进行一定的排序,然后走一个limit拿出来指定部分的数据。你要是纯粹把一坨数据放到一个临时磁盘文件里,然后直接硬上各种排序算法在磁盘文件里搞一通排序,接着按照你指定的要求走limit语句拿到指定分页的数据,这简直会让SQL的速度慢到家了!

所以通常而言,在这种情况下,假设我们建立了一个INDEX(xx1,xx2,xx3)这样的一个联合索引,这个时候默认情况下在索引树里本身就是依次按照xx1,xx2,xx3三个字段的值去排序的,那么此时你再运行select * from table order by xx1,xx2,xx3 limit 100这样的SQL语句,你觉得还需要在什么临时磁盘文件里排序吗?显然是不用了啊!因为他要求也不过就是按照xx1,xx2,xx3三个字段来进行排序罢了,在联合索引的索引树里都排序好了,直接就按照索引树里的顺序,把xx1,xx2,xx3三个字段按照从小到大的值获取前面100条就可以了。然后拿到100条数据的主键再去聚簇索引里回表查询剩余所有的字段。

所以说,在你的SQL语句里,应该尽量最好是按照联合索引的字段顺序去进行order by排序,这样就可以直接利用联合索引树里的数据有序性,到索引树里直接按照字段值的顺序去获取你需要的数据了。但是这里有一些限定规则,因为联合索引里的字段值在索引树里都是从小到大依次排列的 ,所以你在order by里要不然就是每个字段后面什么都不加,直接就是order by xx1,xx2,xx3,要不然就都加DESC降序排列,就是order by xx1 DESC,xx2 DESC,xx3 DESC。

如果都是升序排列,直接就从索引树里最小的开始读取一定条数就可以了,要是都是降序排列,就是从索引树里最大的数据开始读取一定的条数就可以了,但是你不能order by语句里有的字段升序有的字段降序,那是不能用索引的。另外,要是你order by语句里有的字段不在联合索引里,或者是你对order by语句里的字段用了复杂的函数,这些也不能使用索引去进行排序了。

  1.    当我们在SQL里进行分组的时候,如何才能使用索引?
    

在SQL语句里假设你要是用到了group by分组语句的话是否可以用上索引,有时候我们会想要做一个group by把数据分组接着用count sum之类的聚合函数做一个聚合统计。那假设你要是走一个类似select count(*) from table group by xx的SQL语句,似乎看起来必须把你所有的数据放到一个临时磁盘文件里还有加上部分内存,去搞一个分组,按照指定字段的值分成一组一组的,接着对每一组都执行一个聚合函数,这个性能也是极差的,因为毕竟涉及大量的磁盘交互。

因为在我们的索引树里默认都是按照指定的一些字段都排序好的,其实字段值相同的数据都是在一起的,假设要是走索引去执行分组后再聚合,那性能一定是比临时磁盘文件去执行好多了。所以通常而言,对于group by后的字段,最好也是按照联合索引里的最左侧的字段开始,按顺序排列开来,这样的话,其实就可以完美的运用上索引来直接提取一组一组的数据,然后针对每一组的数据执行聚合函数就可以了。

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

在考虑好之后,就可以为表设计两三个常用的索引,覆盖常见的where筛选、order by排序和group by分组的需求,保证常见的SQL语句都可以用上索引,这样你真正系统跑起来,起码是不会有太大的查询性能问题了。毕竟只要你所有的查询语句都可以利用索引来执行,那么速度和性能通常都不会太慢。如果查询还是有问题,那就要深度理解查询的执行计划和执行原理了,然后基于执行计划来进行深度SQL调优。

对于更新语句而言,其实最核心的就是三大问题,一个是你索引别太多,索引太多了,更新的时候维护很多索引树肯定是不行的;一个是可能会涉及到一些锁等待和死锁的问题;一个就是可能会涉及到MySQL连接池、写redo log文件之类的问题。

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

一般我们自己建的索引不管是单列索引还是联合索引,其实一个索引就对应着一颗独立的索引B+树,索引B+树的节点仅仅包含了索引里的几个字段的值以及主键值。即使我们根据索引树按照条件找到了需要的数据,那也仅仅是索引里的几个字段的值和主键值,万一你搞了一个select *还需要很多其他的字段,那还得走一个回表操作,根据主键跑到主键的聚簇索引里去找,聚簇索引的叶子节点是数据页,找到数据页里才能把一行数据的所有字段值提取出来。

所以其实大家可以思考一下,假设你是类似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语句的时候,一方面是你要注意一下也许你会用到联合索引,但是是否可能会导致大量的回表到聚簇索引,如果需要回表到聚簇索引的次数太多了,可能就直接给你做成全表扫描不走联合索引了;一方面是尽可能还是在SQL里指定你仅仅需要的几个字段,不要搞一个select *把所有字段都拿出来,甚至最好是直接走覆盖索引的方式,不要去回表到聚簇索引。即使真的要回表到聚簇索引,那你也尽可能用limit、where之类的语句限定一下回表到聚簇索引的次数,就从联合索引里筛选少数数据,然后再回表到聚簇索引里去,这样性能也会好一些。

  1.    设计索引的时候,我们一般要考虑哪些因素呢?
    

首先,我们在针对业务需求建立好一张表的结构之后,就知道这个表有哪些字段,每个字段是什么类型的,会包含哪些数据。

接着设计好表结构之后,接下来要做的,就是要设计表的索引,这个设计索引的时候,我们要考虑第一点,就是未来我们对表进行查询的时候,大概会如何来进行查询?此时我们完全可以在表结构设计完毕之后,先别急着设计索引,因为此时你根本不知道要怎么查询表。

接着我们就可以进入系统开发的环节,也就是说根据需求文档逐步逐步的把你的Java业务代码给写好,在写代码的过程中,现在一般我们都是用MyBatis作为数据持久层的框架的,你肯定会写很多的MyBatis的DAO和Mapper以及SQL吧?

那么当你系统差不多开发完毕了,功能都跑通了,此时你就可以来考虑如何建立索引了,因为你的系统里所有的MyBatis的SQL语句都已经写完了,你完全知道对每一张表会发起些什么样的查询语句,对吧?

那么这个时候,第一个索引设计原则就来了,针对你的SQL语句里的where条件、order by条件以及group by条件去设计索引。也就是说,你的where条件里要根据哪些字段来筛选数据?order by要根据哪些字段来排序?group by要根据哪些字段来分组聚合?

此时你就可以设计一个或者两三个联合索引,每一个联合索引都尽量去包含上你的where、order by、group by里的字段,接着你就要仔细审查每个SQL语句,是不是每个where、order by、group by后面跟的字段顺序,都是某个联合索引的最左侧字段开始的部分字段?比如你有一个联合索引是INDEX(a,b,c),此时你一看发现有三个SQL,包含了where a=? and b=?,order by a,b,group by a这些部分,那么此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段,这就可以了,说明你的每个SQL语句都会用上你的索引了。

但是在设计索引的时候还得考虑其他的一些问题,首先一个就是字段基数问题,举个例子,有一个字段他一共在10万行数据里有10万个值对吧?结果呢?这个10万值,要不然就是0,要不然就是1,那么他的基数就是2,为什么?因为这个字段的值就俩选择,0和1。假设你要是针对上面说的这种字段建立索引的话,那就还不如全表扫描了,因为你的索引树里就仅仅包含0和1两种值,根本没法进行快速的二分查找,也根本就没有太大的意义了,所以这种时候,选用这种基数很低的字段放索引里意义就不大了。

一般建立索引,尽量使用那些基数比较大的字段,就是值比较多的字段,那么才能发挥出B+树快速二分查找的优势来。

其次的话,你尽量是对那些字段的类型比较小的列来设计索引,比如说什么tinyint之类的,因为他的字段类型比较小,说明这个字段自己本身的值占用磁盘空间小,此时你在搜索的时候性能也会比较好一点。不过当然了,这个所谓的字段类型小一点的列,也不是绝对的,很多时候你就是要针对varchar(255)这种字段建立索引,哪怕多占用一些磁盘空间,那你也得去设计这样的索引,比较关键的其实还是尽量别把基数太低的字段包含在索引里,因为意义不是太大。

那当然了,万一要是你真的有那种varchar(255)的字段,可能里面的值太大了,你觉得都放索引树里太占据磁盘空间了,此时你仔细考虑了一下,发现完全可以换一种策略,也就是仅仅针对这个varchar(255)字段的前20个字符建立索引,就是说,对这个字段里的每个值的前20个字符放在索引树里而已。此时你建立出来的索引其实类似于KEY my_index(name(20),age,course),就这样的一个形式,假设name是varchar(255)类型的,但是在索引树里你对name的值仅仅提取前20个字符而已。

此时你在where条件里搜索的时候,如果是根据name字段来搜索,那么此时就会先到索引树里根据name字段的前20个字符去搜索,定位到之后前20个字符的前缀匹配的部分数据之后,再回到聚簇索引提取出来完整的name字段值进行比对就可以了。但是假如你要是order by name,那么此时你的name因为在索引树里仅仅包含了前20个字符,所以这个排序是没法用上索引了!group by也是同理的。

假设你设计好了一个索引,非常棒,接着你在SQL里这么写:where function(a) = xx,你给你的索引里的字段a套了一个函数,你觉得还能用上索引吗?明显是不行了。所以尽量不要让你的查询语句里的字段搞什么函数,或者是搞个计算。

现在设计索引的时候需要注意的点都已经讲完了,其实就是好好设计索引,让你的查询语句都能用上索引,同时注意一下字段基数、前缀索引和索引列套函数的问题,尽量让你的查询都能用索引,别因为一些原因用不上索引了。

接着我们来看看索引设计好之后,接着你系统跑起来,有数据插入也有查询的情况,其实查询基本都能走索引一般问题都不会太大的,但是插入就有点讲究了,之前也跟大家说过,其实你插入数据的时候,他肯定会更新索引树。你插入数据肯定有主键吧,那有主键就得更新聚簇索引树,你插入一条数据肯定会包含索引里各个字段的值吧,那你的联合索引的B+树是不是也要更新?对了,你不停的增删改数据,就会不停的更新你的索引树。

所以因为你插入的数据值可能根本不是按照顺序来的,很可能会导致索引树里的某个页就会自动分裂,这个页分裂的过程就很耗费时间,因此一般让大家设计索引别太多,建议两三个联合索引就应该覆盖掉你这个表的全部查询了。否则索引太多必然导致你增删改数据的时候性能很差,因为要更新多个索引树。

另外很关键一点,建议大家主键一定是自增的,别用UUID之类的,因为主键自增,那么起码你的聚簇索引不会频繁的分裂,主键值都是有序的,就会自然的新增一个页而已,但是如果你用的是UUID,那么也会导致聚簇索引频繁的页分裂。

11_SQL执行计划

  1.    SQL语句的执行计划和性能优化有什么关系?
    

一般开发一个系统,都是先设计表结构,表结构必须满足业务需求,然后写代码,代码都写好之后,再根据你的代码如何查询表的,来设计里面的索引,考虑设计几个索引,是不是联合索引,选择哪些字段,字段顺序如何排列,才能让查询语句都用上索引。

根据查询语句设计良好的索引,让所有查询都尽可能用上索引,这本身就是一种SQL优化的技巧,但是他仅仅只是其一罢了,并不能说掌握这个,就掌握了所有的SQL优化技巧。反过来说,SQL优化技巧中包含了我们之前讲的设计索引以及让SQL用上索引,但是SQL优化还有很多其他的东西。

实际上有时候往往你会发现自己的数据库里有很多表,每个表的数据量也不小,然后写出来的SQL也比较复杂,各种关联和嵌套子查询,搞的人看的都眼晕,然后表面上看起来这个SQL部分用上了索引,结果性能还是差,差,差,这是为什么呢?所以说,基础的以及日常的SQL优化就是设计好索引,让一般不太复杂的普通查询都用上索引,但是针对复杂表结构和大数据量的上百行复杂SQL的优化,必须得建立在你先懂这个复杂SQL是怎么执行的。

你有那么多的数据表,每个表都有一个聚簇索引,聚簇索引的叶子就是那个表的真实数据,同时每个表还设计了一些二级索引,那么上百行的复杂SQL跑起来的时候到底是如何使用各个索引,如何读取数据的?这个SQL语句(不管是简单还是复杂),在实际的MySQL底层,针对磁盘上的大量数据表、聚簇索引和二级索引,如何检索查询,如何筛选过滤,如何使用函数,如何进行排序,如何进行分组,到底怎么能把你想要的东西查出来,这个过程就是一个很重要的东西:执行计划!

也就是说,每次你提交一个SQL给MySQL,他内核里的查询优化器,都会针对这个SQL语句的语义去生成一个执行计划,这个执行计划就代表了,他会怎么查各个表,用哪些索引,如何做排序和分组,看懂这个执行计划,你就学会了真正的SQL优化的一半了!当你看懂执行计划之后,还能根据他的实际情况去想各种办法改写你的SQL语句,改良你的索引设计,进而优化SQL语句的执行计划,最终让SQL语句的性能得到提升,这个就是所谓的SQL调优。

  1.    MySQL单表查询执行计划包含哪些内容?
    

假设你写一个select * from table where id=x,或者select * from table where name=x的语句,直接就可以通过聚簇索引或者二级索引+聚簇索引回源,轻松查到你要的数据,这种根据索引直接可以快速查找数据的过程,在执行计划里称之为const,意思就是性能超高的常量级的。

所以你以后在执行计划里看到const的时候,就知道他就是直接通过索引定位到数据,速度极快,这就是const的意思。但是这里有一个要点,你的二级索引必须是唯一索引,才是属于const方式的,也就是说你必须建立unique key唯一索引,保证一个二级索引的每一个值都是唯一的,才可以。那么如果你是一个普通的二级索引呢?就是个普通的KEY索引,这个时候如果你写一个select * from table where name=x的语句,name是个普通二级索引,不是唯一索引,那么此时这种查询速度也是很快的,他在执行计划里叫做ref。

如果你是包含多个列的普通索引的话,那么必须是从索引最左侧开始连续多个列都是等值比较才可以是属于ref方式,就是类似于select * from table where name=x and age=x and xx=xx,然后索引可能是个KEY(name,age,xx)。然后一个例外,就是如果你用name IS NULL这种语法的话,即使name是主键或者唯一索引,还是只能走ref方式。

但是如果你是针对一个二级索引同时比较了一个值还有限定了IS NULL,类似于select * from table where name=x or name IS NULL,那么此时在执行计划里就叫做ref_or_null。说白了,就是在二级索引里搜你要的值以及是NULL的值,然后再回源去聚簇索引里查罢了,因为同时有索引等值比较和NULL值查询,就叫做ref_or_null了,其实也没啥。

那这个ref就说完了,我们换个角度看,假设你以后在分析执行计划的时候看到了const,那是什么?对,肯定是通过主键或者唯一索引的访问,速度超高。

如果你看到了ref是什么意思?对,就是用了普通的索引,或者用主键/唯一索引搞了一个IS NULL/IS NOT NULL。

不管怎么说,只要你看到const或者ref,那恭喜你,说明起码这部分执行速度是很快的!

说说range这个东西,这个东西顾名思义,其实就是你SQL里有范围查询的时候就会走这个方式。比如写一个SQL是select * from table where age>=x and age <=x,假设age就是一个普通索引,此时就必然利用索引来进行范围筛选,一旦利用索引做了范围筛选,那么这种方式就是range。

别担心,他们都是说基于索引在查询,总之都是走索引,所以一般问题不是太大,除非你通过索引查出来的数据量太多了,比如上面那个范围筛选,一下子查出来10万条数据,那不是想搞死MySQL么!是不是!

下面我们来讲一种比较特殊的数据访问方式,就是index,可能有的人看到这个index,天真的认为,这不就是通过索引来获取数据么,从索引根节点开始一通二分查找,不停的往下层索引跳转,就可以了,速度超快,感觉上跟ref或者range是一回事。那你就大错特错了!

假设我们有一个表,里面完整的字段联合索引是KEY(x1,x2,x3),好,现在我们写一个SQL语句是select x1,x2,x3 from table where x2=xxx,相信大多数同学看到这里,都会觉得,完蛋了,x2不是联合索引的最左侧的那个字段啊!对的,这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找,快速一层一层跳转的,那么他会怎么执行呢?不知道大家是否发现这个SQL里要查的几个字段,就是联合索引里的几个字段,巧了!所以针对这种SQL,在实际查询的时候,就会直接遍历KEY(x1,x2,x3)这个联合索引的索引树的叶子节点。

聚簇索引的叶子节点放的是完整的数据页,里面包含完整的一行一行的数据,联合索引的叶子节点放的也是页,但是页里每一行就x1、x2、x3和主键的值!所以此时针对这个SQL,会直接遍历KEY(x1,x2,x3)索引树的叶子节点的那些页,一个接一个的遍历,然后找到 x2=xxx 的那个数据,就把里面的x1,x2,x3三个字段的值直接提取出来就可以了!这个遍历二级索引的过程,要比遍历聚簇索引快多了,毕竟二级索引叶子节点就包含几个字段的值,比聚簇索引叶子节点小多了,所以速度也快!

也就是说,此时只要遍历一个KEY(x1,x2,x3)索引就可以了,不需要回源到聚簇索引去!针对这种只要遍历二级索引就可以拿到你想要的数据,而不需要回源到聚簇索引的访问方式,就叫做index访问方式!

没错,所以理解执行计划的前提,是对索引结构和使用索引的原理有一个透彻的理解,在这个基础之上,很容易就可以理解各种各样的执行计划里的访问方式了 ,脑子里甚至直接可以知道不同的访问方式在图里的执行路径。现在我们思考一下,之前说的const、ref和range,本质都是基于索引树的二分查找和多层跳转来查询,所以性能一般都是很高的,然后接下来到index这块,速度就比上面三种要差一些了,因为他是走遍历二级索引树的叶子节点的方式来执行了,那肯定比基于索引树的二分查找要慢多了,但是还是比全表扫描好一些的。

另外最次的一种就是all了,all意思就是直接全表扫描,扫描你的聚簇索引的所有叶子节点,也就是一个表里一行一行数据去扫描,如果一个表就几百条数据那还好,如果是有几万条,或者几十万,几百万数据,全表扫描基本就得跪了。

  1.    写出各种SQL语句的时候,会用什么执行计划?
    

SQL语句你写出来之后,会用什么样的执行计划。首先看一个SQL语句:select * from table where x1=xx and x2>=xx,这个SQL语句要查一个表,用了x1和x2两个字段,此时有人可能会说了,要是你对x1和x2建了一个联合索引,那不就直接可以通过索引去扫描了?

但是万一要是你建的索引是两个呢?比如(x1,x3),(x2,x4),你建了两个联合索引,此时你这个SQL只能选择其中一个索引去用,此时会选择哪个呢?这里MySQL负责生成执行计划的查询优化器,一般会选择在索引里扫描行数比较少的那个条件。比如说x1=xx,在索引里只要做等值比较,扫描数据比较少,那么可能就会挑选x1的索引,做一个索引树的查找,在执行计划里,其实就是一个ref的方式,找到几条数据之后,接着做一个回表,回到聚簇索引里去查出每条数据完整数据,接着加载到内存里,根据每条数据的x2字段的值,根据x2>=xx条件做一个筛选。这就是面对两个字段都能用索引的时候如何选择,以及如何进行处理的方式。

接着我们再来考虑另外一种情况,就是:select * from table where x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL

其实我们平时经常会写出来类似这样的SQL语句,就是在一个SQL的所有筛选条件里,就一个x1是有索引的,其他字段都是没有索引的。这种情况其实也是非常常见的,一般我们在写好一个系统之后,针对所有的SQL分析时,当然不可能针对所有的SQL里的每一个where里的字段都加一个索引,那是不现实的,最终我们只能在所有的SQL语句里,抽取部分经常在where里用到的字段来设计两三个联合索引。

所以在这种情况下,必然很多SQL语句里,可能where后的条件有好几个,结果就一个字段可以用到索引的,此时查询优化器生成的执行计划,就会仅仅针对x1字段走一个ref访问,直接通过x1字段的索引树快速查找到指定的一波数据。接着对这波数据都回表到聚簇索引里去,把每条数据完整的字段都查出来,然后都加载到内存里去。接着就可以针对这波数据的c1、c2、c3字段按照条件进行筛选和过滤,最后拿到的就是符合条件的数据了。所以你的x1索引的设计,必然尽可能是要让x1=xx这个条件在索引树里查找出来的数据量比较少,才能保证后续的性能比较高。

我们来看一个比较奇特的SQL语句以及特殊的执行计划,之前我们都是一般一个SQL语句只能用到一个二级索引,但是有一些特殊的情况下,可能会对一个SQL语句用到多个二级索引,这是怎么回事呢?

比如有这么一个SQL:select * from table where x1=xx and x2=xx,然后x1和x2两个字段分别都有一个索引,其实也有一定的可能会让查询优化器生成一个执行计划,执行计划里,就先对x1字段的索引树进行查找,查出一波数据,接着对x2的索引树查出一波数据,然后对两波数据,按照主键值做一个交集。这个交集就是符合两个条件的数据了,接着回表到聚簇索引去查完整数据就可以了。

但是其实之前我们对这种情况一直说的是,选择x1或者x2其中一个字段的索引,就查一个字段的索引,找出一波数据,接着直接回表到聚簇索引查完整数据,然后根据另外一个字段的值进行过滤就可以了。那么到底什么情况下,会直接对两个字段的两个索引一起查,然后取交集再回表到聚簇索引呢?也就是什么情况下可能会对一个SQL执行的时候,一下子查多个索引树呢?

假设就上面那个SQL语句吧,比如你x1和x2两个字段,如果你先查x1字段的索引,一下子弄出来上万条数据,这上万条数据都回表到聚簇索引查完整数据,再根据x2来过滤,你有没有觉得效果不是太好?那如果说同时从x2的索引树里也查一波数据出来,做一个交集,一下子就可以让交集的数据量变成几十条,再回表查询速度就很快了。一般来说,查索引树速度都比较快,但是到聚簇索引回表查询会慢一些。

所以如果同时查两个索引树取一个交集后,数据量很小,然后再回表到聚簇索引去查,此时会提升性能。但是如果要在一个SQL里用多个索引,那有很多硬性条件的要求,比如说如果有联合索引,你必须把联合索引里每个字段都放SQL里,而且必须都是等值匹配;或者是通过主键查询+其他二级索引等值匹配,也有可能会做一个多索引查询和交集。

在执行SQL语句的时候,有可能是会同时查多个索引树取个交集,再回表到聚簇索引的,这个可能性是有的。

那么其实如果你在SQL里写了类似x1=xx or x2=xx的语句,也可能会用多个索引,只不过查多个大索引树之后,会取一个并集,而不是交集罢了。

  1.    多表关联的SQL语句到底是如何执行的?
    

我们往往从驱动表里查出来一波数据之后,要对每一条数据都循环一次去被驱动表里查询数据,所以万一你要是被驱动表的索引都没建好,总不能每次都全表扫描吧?另外一个,刚开始对你的驱动表根据WHERE条件进行查询的时候,也总不能全表扫描吧?这也是一个问题!

所以说,为什么有的时候多表关联很慢呢?答案就在这里了,你两个表关联,先从驱动表里根据WHERE条件去筛选一波数据,这个过程如果你没给驱动表加索引,万一走一个all全表扫描,岂不是速度很慢?其次,假设你好不容易从驱动表里扫出来一波数据,接着又来一个for循环一条一条去被驱动表里根据ON连接条件和WHERE筛选条件去查,万一你对被驱动表又没加索引,难道又来几十次或者几百次全表扫描?那速度岂不是慢的跟蜗牛一样了!

所以说,通常而言,针对多表查询的语句,我们要尽量给两个表都加上索引,索引要确保从驱动表里查询也是通过索引去查找,接着对被驱动表查询也通过索引去查找。如果能做到这一点,你的多表关联语句性能就会很高!

  1.    MySQL是如何根据成本优化选择执行计划的?
    

我们先了解一下MySQL里的成本是什么意思,简单来说,跑一个SQL语句,一般成本是两块,首先是那些数据如果在磁盘里,你要不要从磁盘里把数据读出来?这个从磁盘读数据到内存就是IO成本,而且MySQL里都是一页一页读的,读一页的成本的约定为1.0。然后呢,还有一个成本,那就是说你拿到数据之后,是不是要对数据做一些运算?比如验证他是否符合搜索条件了,或者是搞一些排序分组之类的事,这些都是耗费CPU资源的,属于CPU成本,一般约定读取和检测一条数据是否符合条件的成本是0.2.

这个所谓1.0和0.2就是他自定义的一个成本值,代表的意思就是一个数据页IO成本就是1.0,一条数据检测的CPU成本就是0.2,就这个意思罢了。

然后呢,当你搞一个SQL语句给MySQL的时候,比如:select * from t where x1=xx and x2=xx

此时你有两个索引,分别是针对x1和x2建立的,就会先看看这个SQL可以用到哪几个索引,此时发现x1和x2的索引都能用到,他们俩索引就是possible keys。

接着会针对这个SQL计算一下全表扫描的成本,这个全表扫描的话就比较坑了,因为他是需要先磁盘IO把聚簇索引里的叶子节点上的数据页一页一页都读到内存里,这有多少数据页就得耗费多少IO成本,接着对内存里的每一条数据都判断是否符合搜索条件的,这有多少条数据就要耗费多少CPU成本。

所以说,此时就得计算一下这块成本有多少,怎么算呢?简单,一个命令:show table status like "表名"

可以拿到你的表的统计信息,你在对表进行增删改的时候,MySQL会给你维护这个表的一些统计信息,比如这里可以看到rows和data_length两个信息,不过对于innodb来说,这个rows是估计值。rows就是表里的记录数,data_length就是表的聚簇索引的字节数大小,此时用data_length除以1024就是kb为单位的大小,然后再除以16kb(默认一页的大小),就是有多少页,此时知道数据页的数量和rows记录数,就可以计算全表扫描的成本了。IO成本就是:数据页数量 * 1.0 + 微调值,CPU成本就是:行记录数 * 0.2 + 微调值,他们俩相加,就是一个总的成本值,比如你有数据页100个,记录数有2万条,此时总成本值大致就是100 + 4000 = 4100,在这个左右。

除了全表扫描之外,还可能多个索引都可以使用,但是当然同时一般只能用一个索引,所以不同索引的使用成本都得计算一下。这个使用索引访问数据的方式,其实很简单,除非你直接根据主键查,那就直接走一个聚簇索引就ok了,否则普通索引,一般都是两步走,先从二级索引查询一波数据,再根据这波数据的主键去聚簇索引回表查询。

这个过程的成本计算方法稍微有点特别,首先,在二级索引里根据条件查一波数据的IO成本,一般是看你的查询条件涉及到几个范围,比如说name值在25~100,250~350两个区间,那么就是两个范围,否则name=xx就仅仅是一个范围区间。一般一个范围区间就粗暴的认为等同于一个数据页,所以此时可能一般根据二级索引查询的时候,这个IO成本都会预估的很小,可能就是1 * 1.0 = 1,或者是n * 1.0 = n,基本就是个位数这个级别。

但是到此为止,还仅仅是通过IO读取了二级索引的数据页而已,这仅仅是二级索引读取的IO成本,但是二级索引数据页到内存里以后,还得根据搜索条件去拿出来一波数据,拿这波数据的过程就是根据搜索条件在二级索引里搜索的过程。此时就要估算从二级索引里读取符合条件的数据的成本了,这需要估算一下在二级索引里会查出多少条数据,总之呢,他会根据一个不是怎么太准确的算法去估算一下根据查询条件可能会在二级索引里查出多少条数据来。

估算出来之后,比如估算可能会查到100条数据,此时从二级索引里查询数据的CPU成本就是100 * 0.2 + 微调值,总之就是20左右而已。接着你拿到100条数据之后,就得回表到聚簇索引里去查询完整数据,此时先估算回表到聚簇索引的IO成本,这里比较粗暴的直接默认1条数据就得回表到聚簇索引查询一个数据页,所以100条数据就是100个数据页的IO成本,也就是100 * 1.0 + 微调值,大致是100左右。

接着因为在二级索引里搜索到的数据是100条,然后通过IO成本最多回表到聚簇索引访问100个数据页之后,就可以拿到这100条数据的完整值了,此时就可以针对这100条数据去判断,他们是否符合其他查询条件了,这里耗费的CPU成本就是100 * 0.2 + 微调值,就是20左右。

把上面的所有成本都加起来,就是1 + 20 + 100 + 20 = 141,这就是使用一个索引进行查询的成本的计算方法,假设你直接根据主键查询,那么也参考上述估算过程就可以了,那就不过是仅仅查询一个聚簇索引罢了。总之,全表扫描发现成本是4100左右,根据索引查找可能就141,所以,很多时候,使用索引和全表扫描,他的成本差距是非常之大的。所以一般就会针对全表扫描和各个索引的成本,都进行估算,然后比较一下,选择一个成本最低的执行计划。

其实多表查询的执行计划选择思路,基本跟单表查询的执行计划选择思路是类似的,单表查询的时候,主要就是对这个表的多种访问方式(全表查询 ,各个索引查询)来根据一定的公式计算出来每种访问方式的成本,接着选择一个成本最低的访问方式,那么就可以确定下来这个表怎么访问了。要在一个查询执行之前,就可以针对不同的访问方法精准计算他的成本,那是根本不现实的,最后只能是根据一些相对较为简单粗暴的办法,大致估算一下,估算结果可能不是太准确,但是也没办法了,反正算出来也就这么比较就是了。

那么接着如果我们要看看多表关联的成本计算访问和执行计划选择方式,那就很简单了。

select * from t1 join t2 on t1.x1=t2.x1 where t1.x2=xxx and t1.x3=xxx and t2.x4=xxx and t2.x5=xxx

一般来说,都会先选择一个驱动表,比如t1作为驱动表,此时就需要根据t1.x2=xxx和t1.x3=xxx这个条件从表里查询一波符合条件的数据出来,此时就有一个问题了,这里用到了t1的两个字段来筛选数据,可能x2和x3字段都建了索引了,此时到底选择哪个索引呢?或者干脆直接就是全表扫描?此时就会按照之前讲的那套方法来计算针对t1表查询的全表扫描和不同索引的成本,选择一个针对t1表的最佳访问方式,用最低成本从t1表里查出符合条件的数据来,接着就根据这波数据得去t2表里查数据,按照连接条件t1.x1=t2.x1去查,同时要符合t2.x4=xxx和t2.x5=xxx这两个条件。

此时一样会根据之前讲解的办法去估算,针对t2表的全表扫描以及基于x4、x5、x1几个字段不同索引的访问的成本,挑选一个成本最低的方法,然后从t2表里把数据给查找出来,就可以,这就完成了多表关联!所以其实多表关联的成本估算以及执行计划选择方式,跟单表关联基本上是差不多的,只不过多表关联要多查几个表罢了。

  1.    MySQL是如何基于各种规则去优化执行计划的?
    

MySQL在执行一些相对较为复杂的SQL语句的时候,因为他有时候可能会觉得你写的SQL一点都不好,直接按你的SQL生成的执行计划效率还是不够高,需要自动帮你改改。

首先呢,要是MySQL觉得你的SQL里有很多括号,那么无关紧要的括号他会给你删除了,其次比如你有类似于i = 5 and j > i这样的SQL,就会改写为i = 5 and j > 5,做一个常量替换。

还有比如x = y and y = k and k = 3这样的SQL,都会给你优化成x = 3 and y = 3 and k = 3,本质也是做个常量替换。或者是类似于什么b = b and a = a这种一看就是乱写的SQL,一看就是没意义的,就直接给你删了。这些SQL的改写,他本质都是在优化SQL语句的清晰语义,方便后续在索引和数据页里进行查找。

还有一些是比较有意思的改写,比如下面的SQL语句:select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1

这个SQL明显是针对t1表的id主键进行了查询,同时还要跟t2表进行关联,其实这个SQL语句就可能在执行前就先查询t1表的id=1的数据,然后直接做一个替换,把SQL替换为:

select t1表中id=1的那行数据的各个字段的常量值, t2.* from t1 join t2 on t1表里x1字段的常量值=t2.x1

上面的SQL就是直接把t1相关的字段都替换成了提前查出来的id=1那行数据的字段常量值了。

子查询是如何执行的,以及他的执行计划是如何优化的。比如说类似于下面的SQL语句:select * from t1 where x1 = (select x1 from t2 where id=xxx)

也就是说上面的SQL语句在执行的时候,其实会被拆分为两个步骤:第一个步骤先执行子查询,也就是:select x1 from t2 where id=xxx,直接根据主键定位出一条数据的x1字段的值。接着再执行select * from t1 where x1=子查询的结果值,这个SQL语句。这个第二个SQL执行,其实也无非就是跟之前讲的单表查询的方式是一样的,其实大家看到最后会发现,这个SQL语句最核心的就是单表查询的几种执行方式,其他的多表关联,子查询,这些都是差不多这个意思。最多就是在排序、分组聚合的时候,可能有的时候会直接用上索引,有的时候用不上索引就会基于内存或者临时磁盘文件执行。

另外还有一种子查询,就是:select * from t1 where x1 = (select x1 from t2 where t1.x2=t2.x2)

这种时候,你会发现子查询里的where条件依赖于t1表的字段值,所以这种查询就会效率很低下,他需要遍历t1表里每一条数据,对每一条数据取出x2字段的值,放到子查询里去执行,找出t2表的某条数据的x1字段的值,再放到外层去判断,是否符合跟t1表的x1字段匹配。

我们重点来看一下IN语句结合子查询的一个优化手段,假设有如下的一个SQL语句:select * from t1 where x1 in (select x2 from t2 where x3=xxx)

这个SQL语句就是典型的一个子查询运用,子查询查一波结果,然后判断t1表哪些数据的x1值在这个结果集里。这个可能大家会想当然的认为先执行子查询,然后对t1表再进行全表扫描,判断每条数据是否在这个子查询的结果集里,但是这种方式其实效率是非常低下的。所以其实对于上述的子查询,执行计划会被优化为,先执行子查询,也就是select x2 from t2 where x3=xxx这条SQL语句,把查出来的数据都写入一个临时表里,也可以叫做物化表,意思就是说,把这个中间结果集进行物化。这个物化表可能会基于memory存储引擎来通过内存存放,如果结果集太大,则可能采用普通的b+树聚簇索引的方式放在磁盘里。但是无论如何,这个物化表都会建立索引,所以大家要清楚,这波中间结果数据写入物化表是有索引的。

接着大家可能会想,此时是不是全表扫描t1表,对每条数据的x1值都去物化表里根据索引快速查找一下是否在这个物化表里?如果是的话,那么就符合条件了。但是这里还有一个优化的点,那就是他可以反过来思考。也就是说,假设t1表的数据量是10万条,而物化表的数据量只有500条,那么此时完全可以改成全表扫描物化表,对每个数据值都到t1表里根据x1这个字段的索引进行查找,查找物化表的这个值是否在t1表的x1索引树里,如果在的话,那么就符合条件了。所以基于IN语句的子查询执行方式,实际上会在底层被优化成如上所述。

我们再来看看MySQL里对子查询的执行计划进行优化的一种方式,就是semi join,也就是半连接。

这个半连接是什么意思呢,其实就是假设你有一个子查询语句:select * from t1 where x1 in (select x2 from t2 where x3=xxx),此时其实可能会在底层把他转化为一个半连接,有点类似于下面的样子:select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx

当然,其实并没有提供semi join这种语法,这是MySQL内核里面使用的一种方式,上面就是给大家说那么个意思,其实上面的semi join的语义,是和IN语句+子查询的语义完全一样的,他的意思就是说,对于t1表而言,只要在t2表里有符合t1.x1=t2.x2和t2.x3=xxx两个条件的数据就可以了,就可以把t1表的数据筛选出来了。

当然,其实还是要给大家提醒一句,在互联网公司里,我们比较崇尚的是尽量写简单的SQL,复杂的逻辑用Java系统来实现就可以了,SQL能单表查询就不要多表关联,能多表关联就尽量别写子查询,能写几十行SQL就别写几百行的SQL,多考虑用Java代码在内存里实现一些数据就的复杂计算逻辑,而不是都放SQL里做。其实一般的系统,只要你SQL语句尽量简单,然后建好必要的索引,每条SQL都可以走索引,数据库性能往往不是什么大问题,当然SQL高级调优也是程序员必须掌握的。

  1.    通过explain命令得到的SQL执行计划
    

平时我们只要用类似于:explain select * from table,这种SQL前面加一个explain命令,就可以轻松拿到这个SQL语句的执行计划。首先,当你执行explain命令之后,拿到的执行计划可能是类似下面这样的东西:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+|

1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |

那所谓的id、select_type、table、partitions、type之类的东西了吗,其实这些就是所谓的执行计划里包含的东西。大致来说,如果是一个简单的单表查询,可能这里就只有一条数据,也就是代表了他是打算如何访问这一个表而已。但是如果你的SQL语句极为的复杂,可能这里会有很多条数据,因为一个复杂的SQL语句的执行是要拆分为很多步骤的,比如先访问表A,接着搞一个排序,然后来一个分组聚合,再访问表B,接着搞一个连接,类似这样子。

好,那么接下来我们就先来研究一下这个所谓的执行计划里包含的各个字段都是什么意思,首先是id这个东西。

这个id呢,就是说每个SELECT都会对应一个id,其实说穿了,就是一个复杂的SQL里可能会有很多个SELECT,也可能会包含多条执行计划,每一条执行计划都会有一个唯一的id,这个没啥好说的。

select_type,顾名思义,说的就是这一条执行计划对应的查询是个什么查询类型,table就是表名,意思是要查询哪个表,partitions是表分区的概念。

type,就是比较关键了,针对当前这个表的访问方法,比如说const、ref、range、index、all之类的,分别代表了使用聚簇索引、二级索引、全表扫描之类的访问方式。

possible_keys,这也很关键,他是跟type结合起来的,意思就是说你type确定访问方式了,那么到底有哪些索引是可供选择,可以使用的呢,这都会放这里。key,就是在possible_keys里实际选择的那个索引,而key_len就是索引的长度。

ref,就是使用某个字段的索引进行等值匹配搜索的时候,跟索引列进行等值匹配的那个目标值的一些信息。rows,是预估通过索引或者别的方式访问这个表的时候,大概可能会读取多少条数据。filtered,就是经过搜索条件过滤之后的剩余数据的百分比。extra是一些额外的信息,不是太重要。

所以其实在SQL调优的时候,核心就是分析执行计划里哪些地方出现了全表扫描,或者扫描数据过大,尽可能通过合理优化索引保证执行计划每个步骤都可以基于索引执行,避免扫描过多的数据。

12_Mysql主从复制架构

  1.    为什么要搭建一套MySQL的主从复制架构?
    

在MySQL真正的生产环境中,他一定不是一个单机版的架构,因为单机版的MySQL一般仅能用于本地开发环境和测试环境,是绝对不可能运用于生产环境的。

那么生产环境的MySQL架构应该是什么样子的呢?简单来说,MySQL在生产环境中,必须要搭建一套主从复制的架构,同时可以基于一些工具实现高可用架构。

另外如果有需求,还需要基于一些中间件实现读写分离架构,最后就是如果数据量很大,还必须可以实现分库分表的架构。

我们先来看看MySQL的主从复制架构,这个主从复制架构,顾名思义,就是部署两台服务器,每台服务器上都得有一个MySQL,其中一个MySQL是master(主节点),另外一个MySQL是slave(从节点)。然后我们的系统平时连接到master节点写入数据,当然也可以从里面查询了,就跟你用一个单机版的MySQL是一样的,但是master节点会把写入的数据自动复制到slave节点去,让slave节点可以跟master节点有一模一样的数据。

如果你的MySQL就单机部署,那么一旦他宕机了,岂不是你的数据库就完蛋了?数据库完蛋了,你的Java业务系统是不是也就完蛋了?所以说,真正生产架构里,MySQL必须得做高可用架构。那么高可用架构怎么做呢?他的一个先决条件就是主从复制架构。你必须得让主节点可以复制数据到从节点,保证主从数据是一致的,接着万一你的主节点宕机了,此时可以让你的Java业务系统连接到从节点上去执行SQL语句,写入数据和查询数据,因为主从数据是一致的,所以这是没问题的。如果实现这样的一个效果,自然就实现了MySQL的高可用了,他单机宕机不影响你的Java业务系统的运行。但是大家也得注意,这里其实是没这么简单的,因为实际哪怕这套架构运用到生产环境,也是有大量的问题要解决的。

比如主从进行数据复制的时候,其实从节点通常都会落后一些,所以数据不完全一致。另外,主节点宕机后,要能自动切换从节点对外提供服务,这个也需要一些中间件的支持,也没那么容易。

那么我们如果做了这个MySQL主从复制架构之后,除了这个高可用之外,还有什么作用呢?其实这就得说到大名鼎鼎的读写分离架构了!这个读写分离架构,也是依赖于MySQL的主从复制架构的。

读写分离架构的意思就是,你的Java业务系统可以往主节点写入数据,但是从从节点去查询数据,把读写操作做一个分离,分离到两台MySQL服务器上去,一台服务器专门让你写入数据,然后复制数据到从节点,另外一台服务器专门让你查询数据。可是好端端的,我们吃饱了没事儿,为什么要做读写分离呢?难道就为了好玩儿吗?当然不是了!因为假设我们的MySQL单机服务器配置是8核16GB,然后每秒最多能抗4000读写请求,现在假设你真实的业务负载已经达到了,每秒有2500写请求+2500读请求,也就是每秒5000读写请求了,那么你觉得如果都放一台MySQL服务器,能抗的住吗?必然不行啊!所以此时如果你可以利用主从复制架构,搭建起来读写分离架构,就可以让每秒2500写请求落到主节点那台服务器,2500读请求落到从节点那台服务器,用2台服务器来抗下你每秒5000的读写请求。

接着现在问题来了,大家都知道,其他大部分Java业务系统都是读多写少,读请求远远多于写请求,那么接着发现随着系统日益发展,读请求越来越多,每秒可能有6000读请求了,此时一个从节点服务器也抗不下来啊,那怎么办呢?

简单!因为MySQL的主从复制架构,是支持一主多从的,所以此时你可以再在一台服务器上部署一个从节点,去从主节点复制数据过来,此时你就有2个从节点了,然后你每秒6000读请求不就可以落到2个从节点上去了,每台服务器主要接受每秒3000的读请求。Java业务系统每秒以2500的TPS写入主库,然后主库会复制数据到两个从库,接着你每秒6000 QPS的读请求分散在两个从库上,一切似乎很完美,这就是主从复制架构的另外一个经典的应用场景,就是读写分离,通过读写分离,可以让你抗下很高的读请求。

而且在上述架构之下,还可以融合高可用架构进去,因为你有多个从库,所以当你主库宕机的时候,可以通过中间件把一个从库切换为主库,此时你的Java业务系统可以继续运行,在实现读写分离的场景下,还可以同时实现高可用。不过其实一般在项目中,高可用架构是必须做的,但是读写分离架构并不是必须的,因为对于大多数公司来说,读请求QPS并没那么高,远远达不到每秒几千那么夸张,但是高可用你是必须得做的,因为你必须保证主库宕机后,有另外一个从库可以接管提供服务,避免Java业务系统中断运行。

除此之外,这个从库其实还有很多其他的应用场景,比如你可以挂一个从库,专门用来跑一些报表SQL语句,那种SQL语句往往是上百行之多,运行要好几秒,所以可以专门给他一个从库来跑。也可以是专门部署一个从库,让你去进行数据同步之类的操作。

接着我们来说一下MySQL实现主从复制的一个基本的工作原理。首先呢,MySQL自己在执行增删改的时候会记录binlog日志,所以这个binlog日志里就记录了所有数据增删改的操作。然后从库上有一个IO线程,这个IO线程会负责跟主库建立一个TCP连接,接着请求主库传输binlog日志给自己,这个时候主库上有一个IO dump线程,就会负责通过这个TCP连接把binlog日志传输给从库的IO线程。接着从库的IO线程会把读取到的binlog日志数据写入到自己本地的relay日志文件中去,然后从库上另外有一个SQL线程会读取relay日志里的内容,进行日志重做,把所有在主库执行过的增删改操作,在从库上做一遍,达到一个还原数据的过程。

简单来说,你只要给主节点挂上一个从节点,从节点的IO线程就会跟主节点建立网络连接,然后请求主节点传输binlog日志,主节点的IO dump线程就负责传输binlog日志给从节点,从节点收到日志后就可以回放增删改操作恢复数据。在这个基础之上,就可以实现MySQL主从节点的数据复制以及基本一致,进而可以实现高可用架构以及读写分离架构。

  1.    如何为MySQL搭建一套主从复制架构?
    

首先呢,要确保主库和从库的server-id是不同的,这个是必然的,其次就是主库必须打开binlog功能,你必须打开binlog功能主库才会写binlog到本地磁盘,接着就可以按如下步骤在主库上执行一通操作了。

首先在主库上要创建一个用于主从复制的账号:

create user 'backup_user'@'192.168.31.%' identified by 'backup_123';

grant replication slave on . to 'backup_user'@'192.168.31.%';

flush privileges;

接着你要考虑一个问题,假设你主库都跑了一段时间了,现在要挂一个从库,那从库总不能把你主库从0开始的所有binlog都拉一遍吧!这是不对的,此时你就应该在凌晨的时候,在公司里直接让系统对外不可用,说是维护状态,然后对主库和从库做一个数据备份和导入。可以使用如下的mysqldump工具把主库在这个时刻的数据做一个全量备份,但是此时一定是不能允许系统操作主库了,主库的数据此时是不能有变动的。

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot --master-data=2 -A > backup.sql

注意,mysqldump工具就在你的MySQL安装目录的bin目录下,然后用上述命令就可以对你主库所有的数据都做一个备份,备份会以SQL语句的方式进入指定的backup.sql文件,只要执行这个backup.sql文件,就可以恢复出来跟主库一样的数据。至于上面命令里的--master-data=2,意思就是说备份SQL文件里,要记录一下此时主库的binlog文件和position号,这是为主从复制做准备的。

接着你可以通过scp之类的命令把这个backup.sql文件拷贝到你的从库服务器上去就行了。

接着操作步骤转移到从库上去执行,在从库上执行如下命令,把backup.sql文件里的语句都执行一遍,这就相当于把主库所有的数据都还原到从库上去了,主库上的所有database、table以及数据,在从库里全部都有了。

接着在从库上执行下面的命令去指定从主库进行复制。

CHANGE MASTER TO MASTER_HOST='192.168.31.229', MASTER_USER='backup_user',MASTER_PASSWORD='backup_123',MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=1689;

上面的master机器的ip地址我们是知道的,master上用于执行复制的用户名和密码是我们自己创建的,也没问题,但是master的binlog文件和position是怎么知道的?这不就是之前我们mysqldump导出的backup.sql里就有,大家在执行上述命令前,打开那个backup.sql就可以看到如下内容:

MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=1689

然后你就把上述内容写入到主从复制的命令里去了。

接着执行一个开始进行主从复制的命令:start slave,再用show slave status查看一下主从复制的状态,主要看到Slave_IO_Running和Slave_SQL_Running都是Yes就说明一切正常了,主从开始复制了。

接着就可以在主库插入一条数据,然后在从库查询这条数据,只要能够在从库查到这条数据,就说明主从复制已经成功了。

这仅仅是最简单的一种主从复制,就是异步复制,就是之前讲过的那种原理,从库是异步拉取binlog来同步的,所以肯定会出现短暂的主从不一致的问题的,比如你在主库刚插入数据,结果在从库立马查询,可能是查不到的。

只要你搭建出来主从复制架构,就可以实现读写分离了。比如可以用mycat或者sharding-sphere之类的中间件,就可以实现你的系统写入主库,从从库去读取了。

但是现在搭建出来的主从复制架构有一个问题,那就是之前那种搭建方式他默认是一种异步的复制方式,也就是说,主库把日志写入binlog文件,接着自己就提交事务返回了,他也不管从库到底收到日志没有。那万一此时要是主库的binlog还没同步到从库,结果主库宕机了,此时数据不就丢失了么?即使你做了高可用自动切换,一下子把从库切换为主库,但是里面是没有刚才写入的数据的,所以这种方式是有问题的。因此一般来说搭建主从复制,都是采取半同步的复制方式的,这个半同步的意思,就是说,你主库写入数据,日志进入binlog之后,起码得确保 binlog日志复制到从库了,你再告诉客户端说本次写入事务成本了是不是?这样起码你主库突然崩了,他之前写入成功的数据的binlog日志都是到从库了,从库切换为主库,数据也不会丢的,这就是所谓的半同步的意思。

这个半同步复制,有两种方式,第一种叫做AFTER_COMMIT方式,他不是默认的,他的意思是说,主库写入日志到binlog,等待binlog复制到从库了,主库就提交自己的本地事务,接着等待从库返回给自己一个成功的响应,然后主库返回提交事务成功的响应给客户端。另外一种是现在MySQL 5.7默认的方式,主库把日志写入binlog,并且复制给从库,然后开始等待从库的响应,从库返回说成功给主库了,主库再提交事务,接着返回提交事务成功的响应给客户端。总而言之,这种方式可以保证你每个事务提交成功之前,binlog日志一定都复制到从库了,所以只要事务提交成功,就可以认为数据在从库也有一份了,那么主库崩溃,已经提交的事务的数据绝对不会丢失的。

搭建半同步复制也很简单,在之前搭建好异步复制的基础之上,安装一下半同步复制插件就可以了,先在主库中安装半同步复制插件,同时还得开启半同步复制功能:

install plugin rpl_semi_sync_master soname 'semisync_master.so';

set global rpl_semi_sync_master_enabled=on;

show plugins;

可以看到你安装了这个插件,那就ok了。接着在从库也是安装这个插件以及开启半同步复制功能:

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

set global rpl_semi_sync_slave_enabled=on;

show plugins;

接着要重启从库的IO线程:stop slave io_thread; start slave io_thread;

然后在主库上检查一下半同步复制是否正常运行:show global status like '%semi%';,如果看到了Rpl_semi_sync_master_status的状态是ON,那么就可以了。到此半同步复制就开启成功了,其实一般来说主从复制都建议做成半同步复制,因为这样配合高可用切换机制,就可以保证数据库有一个在线的从库热备份主库的数据了,而且主要主库宕机,从库立马切换为主库,数据不丢失,数据库还高可用。

上面是MySQL传统的主从复制搭建方式,其实一般大家在生产中都会采用半同步的复制模式,但是其实除了那种传统搭建方式之外,还有一种更加简便一些的搭建方式,就是GTID搭建方式。

首先在主库进行配置:

gtid_mode=on

enforce_gtid_consistency=on

log_bin=on

server_id=单独设置一个

binlog_format=row

接着在从库进行配置:

gtid_mode=on

enforce_gtid_consistency=on

log_slave_updates=1

server_id=单独设置一个

接着按照之前的步骤在主库创建好用于复制的账号之后,就可以跟之前一样进行操作了,比如在主库dump出来一份数据,在从库里导入这份数据,利用mysqldump备份工具做的导出,备份文件里会有SET @@GLOBAL.GTID_PURGED=***一类的字样,可以照着执行一下就可以了。接着其余步骤都是跟之前类似的,最后执行一下show master status,可以看到executed_gtid_set,里面记录的是执行过的GTID,接着执行一下SQL:select * from gtid_executed,可以查询到,对比一下,就会发现对应上了。

其实无论是GTID复制,还是传统复制,都不难,很简单,往往这就是比较典型的MySQL主从复制的搭建方式了,然后MyCat中间件或者是Sharding-Sphere的官方文档,其实也都不难,照着文档做,整合到Java代码里去,就可以做出来基于主从复制的读写分离的效果了。那些中间件都是支持读写分离模式的,可以仅仅往主库去写,从从库去读,这都没问题的。如果落地到项目里,那么就完成了一个主从架构以及读写分离的架构了,此时按照我们之前所说的,如果说你的数据库之前对一个库的读写请求每秒总共是2000,此时读写分离后,也许就对主库每秒写TPS才几百,从库的读QPS是1000多。

那么万一你要是从库的读QPS越来越大,达到了每秒几千,此时你是不是会压力很大?没关系,这个时候你可以给主库做更多的从库,搭建从库,给他挂到主库上去,每次都在凌晨搞,先让系统停机,对外不使用,数据不更新。接着对主库做个dump,导出数据,到从库导入数据,做一堆配置,然后让从库开始接着某个时间点开始继续从主库复制就可以了,一旦搭建完毕,就等于给主库挂了一个新的从库上去,此时继续放开系统的对外限制,继续使用就可以了,整个过程基本在1小时以内。如果在凌晨比如2点停机1小时,基本对业务是没有影响的。

  1.    主从复制架构中的数据延迟问题,应该如何解决?
    

主从复制可能会有较大的延迟。这个延迟是什么意思呢?就是说主库可能你都写入了100条数据了,结果从库才复制过去了50条数据,那么从库就比主库落后了50条数据。这就是所谓的主从延迟的问题。

可是为什么会产生这个主从延迟的问题呢?也很简单,其实你主库是多线程并发写入的,这个大家都知道的,所以主库写入数据的速度可能是很快的,但是从库是单个线程缓慢拉取数据的,所以才会导致从库复制数据的速度是比较慢的。那自然会导致主从之间的延迟问题了。

那么这个主从之间到底延迟了多少时间呢?这个可以用一个工具来进行监控,比较推荐的是percona-toolkit工具集里的pt-heartbeat工具,他会在主库里创建一个heartbeat表,然后会有一个线程定时更新这个表里的时间戳字段,从库上就有一个monitor线程会负责检查从库同步过来的heartbeat表里的时间戳。把时间戳跟当前时间戳比较一下,其实就知道主从之间同步落后了多长时间了,主从之间延迟了多长时间,我们这里实际上是可以看到的。

那么这个主从同步延迟的问题,会导致一些什么样的不良情况呢?如果你做了读写分离架构,写都往主库写,读都从从库读,那么会不会你的系统刚写入一条数据到主库,接着代码里立即就在从库里读取,可能此时从库复制有延迟,你会读不到刚写入进去的数据!没错,就是这个问题。另外就是有可能你的从库同步数据太慢了,导致你从库读取的数据都是落后和过期的,也可能会导致你的系统产生一定的业务上的bug。

所以针对这个问题,首先你应该做的,是尽可能缩小主从同步的延迟时间,那么怎么做呢?其实就是让从库也用多线程并行复制数据就可以了,这样从库复制数据的速度快了,延迟就会很低了。

MySQL 5.7就已经支持并行复制了,可以在从库里设置slave_parallel_workers>0,然后把slave_parallel_type设置为LOGICAL_CLOCK,就ok了。

另外,如果你觉得还是要求刚写入的数据你立马强制必须一定可以读到,那么此时你可以使用一个办法,就是在类似MyCat或者Sharding-Sphere之类的中间件里设置强制读写都从主库走,这样你写入主库的数据,强制从主库里读取,一定立即可以读到的。

总体而言就是这样了,在落实读写分离架构的时候,要注意一下复制方式,是异步还是半同步?如果说你对数据丢失并不是强要求不能丢失的话,可以用异步模式来复制,再配合一下从库的并行复制机制。如果说你要对MySQL做高可用保证数据绝对不丢失的话,建议还是用半同步机制比较好一些,同理最好是配合从库的并行复制机制。

  1.    数据库高可用:基于主从复制实现故障转移
    

那么读写分离的模式确定了,接着就可以来考虑一下数据库的高可用架构了,所谓的高可用就是说,如果数据库突然宕机了一台机器,比如说主库或者从库宕机了,那么数据库还能正常使用吗?其实如果从库宕机了影响并不是很大,因为大不了就是让所有的读流量都从主库去读就可以了,但是如果主库宕机了呢?那就真的麻烦了,因为主库一旦宕机,你就没法写入数据了,从库毕竟是不允许写入的,只允许读取。

所以有没有一种办法,可以在主库宕机之后,就立马把从库切换为主库呢,然后所有人都对从库切换为的主库去写入和读取呢?如果能实现这样的一个效果,那数据库不就实现高可用了吗?没错,就这么简单,这就是数据库的高可用架构。一般生产环境里用于进行数据库高可用架构管理的工具是MHA,也就是Master High Availability Manager and Tools for MySQL,是日本人写的,用perl脚本写的一个工具,这个工具就是专门用于监控主库的状态,如果感觉不对劲,可以把从库切换为主库。

这个MHA自己也是需要单独部署的,分为两种节点,一个是Manager节点,一个是Node节点,Manager节点一般是单独部署一台机器的,Node节点一般是部署在每台MySQL机器上的,因为Node节点得通过解析各个MySQL的日志来进行一些操作。Manager节点会通过探测集群里的Node节点去判断各个Node所在机器上的MySQL运行是否正常,如果发现某个Master故障了,就直接把他的一个Slave提升为Master,然后让其他Slave都挂到新的Master上去,完全透明。