一、逻辑架构剖析
1.1服务器处理客户端请求
①MySQL是典形的C/S架构,服务器端使用mysqld
②客户端进程向服务器进程发送SQL语句,服务器进程在向客户端进程发送处理结果。
查询请求演示图
查询请求详细图
1.2第1层:连接层
①客户端访问MySQL服务器前,第一件事是建立TCP连接。
②经过三次握手建立连接成功后,MySQL服务器对TCP传输的账号和密码做认证。如果错误,客户端结束执行。如果正确,从权限表查出此账号的权限。
③TCP连接收到请求会在线程池分配一个线程专门与这个客户端交互。每一个连接从线程池获取线程,省去了创建和销毁线程的开销。
1.3第2层:服务层
①SQL接口
接收用户的SQL命令,并且返回用户的查询结果。select *** from
②解析器
在解析器中对SQL语句进行语法分析,语义分析。将SQL语句分解成数据结构,语法树。根据数据字典丰富查询语法树,验证客户端是否具有该查询的权限。
③查询优化器
SQL语句使用优化器确定SQL语句的执行路径,生成执行计划。
执行计划表明使用哪些索引查询,表之间的连接顺序,存储引擎,并将结果返回给用户。
使用“选取-投影-连接”,先根据where语句进行选取,再通过select查询条件进行投影,最后将两个查询条件连接起来生成最终的结果。
④查询缓存(8.0删除)
缓存一条select语句的执行结果,查询缓存可以在不同客户端共享。
1.4第3层:引擎层
负责MySQL数据的存储和提取,对物理服务器级别维护的底层数据执行操作。
1.5小结
①连接层:客户端和服务器端建立连接,客户端发送SQL到服务器端
②服务层:对SQL语句进行查询,与数据库文件的存储方式无关
③引擎层:与数据库文件打交道,负责数据的存储和读取
二、SQL执行流程
2.1MySQL中的SQL执行流程
MySQL的查询流程:
1.查询缓存:server在查询缓存发现这条SQL语句,就会直接将结果返回给客户端。没有就接入解析器阶段。效率不高,在MySQL8.0删除了。
2.解析器:在解析器对SQL语句进行语法分析,语义分析。
分析器先做“词法分析”,把SQL语句的字符串识别出关键字,表名。
然后做“语法分析”看SQL语句是否满足语法,语句正确生成一个语法树。
3.优化器:在优化器中确定SQL语句的执行路径,比如是全表检索还是索引检索。一条查询有多种执行方式,找出执行时间最短的计划。
物理查询优化:通过索引和表连接方式来进行优化
逻辑查询优化:换一种更优的SQL查询写法
4.执行器:
判断此查询是否有权限。
执行SQL查询返回结果。如果设置了缓存,将查询结果进行缓存。
【总结】
SQL语句—查询缓存—解析器—优化器—执行器。
三、数据库缓冲池buffer pool
3.1 缓冲池VS查询缓存
1.缓冲池
①概念
InnoDB存储引擎是以页来管理存储空间的,在内存中进行操作效率会高很多。DBMS会申请占用内存作为数据缓冲池。
减少与磁盘直接进行的I/O的时间。
②缓存原则
优先把使用频次高的如数据进行加载到内存
③缓冲池的预读特性
当我们使用一些数据,大概率会使用周围的一些数据,采用“预读”的机制提前加载,减少未来的磁盘I/O操作。
2.查询缓存
提前把查询结果存起来,K-V。K是select语句,V是查询结果。
3.2 缓冲池如何读取数据
在数据库进行页面读操作的时候,先判断该页是否在缓冲池中,存在就直接读取。不存在就从磁盘或内存加载到缓冲池中。
【问题】执行SQL语句更新了缓冲池的数据,会立刻同步到磁盘吗?
数据库会一定频率刷新到磁盘,并不是每次更新完就立即回写。当缓冲池不够用时,会把脏页(修改过的页,不经常查询)回写到磁盘,释放掉脏页。
3.3 查看/设置缓冲池的大小
1.查看缓冲池大小128MB
show variables like 'innodb_buffer_pool_size';
2.修改缓冲池大小
set global innodb_buffer_pool_size = 268435456;
3.4更新数据流程
当更新某个数据的时候,先把数据从内存或磁盘加载到Buffer Pool,然后修改内存的数据,最后定期刷入磁盘。
如果在刷入磁盘的时候宕机,数据会回滚Redo Log&Undo Log