MySQL 架构设计
研究MySQL数据库的底层原理和各种实践案例,以及互联网公司的技术方案
文章目录
一、系统如何与MySQL交互
1.MySQL驱动
访问数据库必须先和数据库建立一个网络连接,这个连接就是由MySQL驱动来完成。
当系统与数据库建立网络连接后,才能基于连接执行各种SQL语句
2. 数据库连接池
Tomcat中每个线程访问数据库时,都基于MySQL驱动去创建一个数据库连接,然后执行SQL语句,执行完后再销毁这个数据库连接。而频繁创建和销毁数据库连接的代价太大,因此必须使用数据库连接池,在数据库连接池中维护多个数据库连接,让多个线程使用里面不同的数据库连接去执行SQL语句,执行完后将连接放回数据库连接池供后续继续使用。常见的数据库连接池有DBCP、C3P0、Druid等。
多个系统与MySQL数据库建立连接,MySQL也必然需要维护与多个系统之间的连接,所以MySQL架构体系中的第一个环节就是连接池。
二、MySQL如何执行SQL语句
1.网络连接必须让线程来处理
代码如下(示例):思考:假设数据库服务器的连接池中的某个连接接收到了网络请求,谁负责从这个连接中监听网络请求呢?谁负责从网络连接中将这个请求数据读取出来呢?
答案是线程,网络连接必须得分配一个线程去进行处理,由一个线程来监听请求以及读取请求数据:
2.SQL接口:负责处理接收到的SQL语句
MySQL内部提供了一个组件:SQL接口,它是一套执行SQL语句的接口,专门用于执行我们发送的MySQL的增删改查的SQL语句。因此,MySQL的工作线程接收到SQL语句之后,就会转交给SQL接口执行:
3.查询解析器:让MySQL能看懂SQL语句
查询解析器(Parser):负责对SQL语句进行解析,例如:select id,name,age from users where id=1 查询解析器对这条SQL语句进行拆解,拆解成一下几个部分:
1.从“users”表里查询数据;
2.查询“id”字段等于1的那行数据;
3.对查询出来的那行数据提取“id”,“name”,“age”三个字段;
所谓SQL解析,就是按照既定的SQL语法,对SQL语句进行解析,然后理解这个SQL语句要干什么事情。
4.查询优化器:选择最优的查询路径
查询优化器(Optimizer):选择一个最优的查询路径
5.存储引擎接口:真正执行SQL语句
存储引擎:其实就是执行SQL语句的,按照一定的步骤去查询内存缓存的数据,更新磁盘数据,查询磁盘数据,等等,执行诸如此类的一系列操作。
6.执行器:根据执行计划调用存储引擎的接口
执行器:根据优化器选择的执行方案,调用存储引擎的接口按照一定的顺序和步骤,执行SQL语句的逻辑。即执行器会根据优化器生成的一套执行计划,不停地调用存储引擎的各种接口来完成SQL语句的执行计划。
三、利用数据更新流程了解InnoDB存储引擎的结构设计
1.InnoDB重要内存结构:缓冲池
缓冲池:缓存数据,减少查询磁盘次数;
例如:update user set name=‘test’ where id = 1;
引擎要执行更新语句时,先查询缓冲池中是否存在id=1的这行数据,如果不存在,则从磁盘里将这行数据加载到缓冲池中,并且对这行数据加独占锁。(因为更新这行数据时,不允许别人同时更新)
2.undo日志文件
执行name=‘test’之前,需先将name的原值和id=1写入undo日志文件中,以便数据的回滚。
3.更新buffer pool中的缓存数据
当把要更新的那行记录从磁盘文件加载到缓冲池,同时对这行数据加锁,并且把更新前的旧值写入undo日志文件之后,即可正式开始更新这行记录了。更新时,先更新缓冲池中的记录,此时这个数据为脏数据(内存与磁盘数据不一致)。
4.Redo Log Buffer
如果内存里的数据进行了修改,但是磁盘上的数据还没修改,此时MySQL所在的机器宕机,将会导致内存里修改过的数据丢失。这时,就必须将内存所做的修改写入到一个Redo Log Buffer文件中,这也是内存里的一个缓冲区,用来存放redo日志的。
这个redo日志是用来在MySQL突然宕机的时候,用来恢复更新过的数据。
5.提交事务的时候将redo日志写入磁盘
提交一个事务时,就会根据一定的策略把redo日志从redo log buffer刷到磁盘文件。
这个策略是通过innodb_flush_log_at_trx_commit来配置的:
- 当参数值为0时,提交事务时就不会把redo log buffer里的数据刷入磁盘文件,此时可能提交了事务,由于机器宕机,内存数据将会全部丢失;
- 当参数值为1时,提交事务时,必须把redo log从内存刷入磁盘文件,只要事务提交成功,那么redo log就必然在磁盘里;
- 当参数值为2时,提交事务时,把redo日志写入磁盘文件对应的os cache缓存中,而不是直接进入磁盘文件,可能1秒后才会把os cache里的数据写入到磁盘文件里去。
redo日志刷盘:通常设置为1,提交事务时,redo日志必须是输入磁盘文件里;这样可以严格保证提交事务后,数据不会丢失,因为redo日志在磁盘文件里可以恢复所有的修改。
6.binlog
redo log 属于偏向物理性质的重做日志,它记录的是:对哪些数据页中的什么记录做了什么修改;redo log属于InNoDB存储引擎。
**binlog(归档日志)**记录偏向于逻辑性的日志,类似:对user表中的id=1的一行数据做了更新操作,更新后的值为什么;binlog属于mysql server。
提交事务时,redo log日志会写入磁盘文件中,同时对应的binlog日志也会写入到磁盘文件中,如下图:
执行器:负责和InnoDB进行交互,包括从磁盘里加载数据到Buffer Pool中进行缓存,写入undo日志,更新Buffer Pool 里的数据,以及写入redo log buffer ,redo log刷入磁盘,写binlog等。
- binlog日志的刷盘策略
binlog的刷盘策略通过sync_binlog参数控制,默认为0;
- 当sync_binlog为0时,binlog写入磁盘时,并不是直接进入磁盘文件,而是进入os cache内存缓存;如果机器宕机,os cache中的binlog日志会丢失;
- 当sync_binlog为1时,提交事务时,binlog直接写入到磁盘文件;
binlog写入磁盘文件后,接着会完成最终的事务提交,此时会把本次更新对应的binlog文件名称和文件位置都写入到redo log日志文件中,同时在redo log日志文件里写入一个commit标记。
完成这一步后,才算最终完成了事务的提交,如图所示:
commit标记用来保持redo log日志和binlog日志一致
- 后台IO线程随机将内存更新后的脏数据刷回磁盘
事务提交完成后,内存里的buffer pool缓存数据已被更新,同时磁盘里有redo log日志和binlog日志,都记录了我们指定的“id=1 这行数据修改了name=test”。此时,磁盘上的数据文件中的数据还是旧值,MySQL会通过一个后台的IO线程,在某个时间里随机将内存buffer pool中修改后的脏数据刷回到磁盘上的数据文件中。如图所示:
总结:
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里的脏数据输入磁盘文件。