文章目录
5 SQL架构
5.1 整体架构
- 由图可知,Mysql为典型的sc(server/client)结构,其中server的数据管理系统由连接层,服务层和引擎层组成。
-
连接层:
主要用于接受客户端的一系列相关服务,如连接者的授权认证、限权查询、线程池分配的问题,说白了就是给查询他是否有资格访问服务器,能访问哪些数据库,如果能的话就给他分配一个线程。(线程池:由客户端连接就分配一下,没有就回到线程池)
-
服务层:服务层由sql接口、解析器、优化器、查询缓存组成
- sql接口:用于接受命令,并返回结果
- 解析器:将客户端的sql命令解析成可以执行的内部逻辑,解析的结果就可以执行了
- 优化器:对sql语句进行优化,包括物理优化和逻辑优化
- 查询缓存:记录有着之前查询的结果,若出现重复查询时可以迅速访问(在mysql8中被删除了)
-
引擎层:
存储着不同的引擎,真正的负责了 MySQL 中数据的存储和提取,服务器通过 API 与存储引擎进行通信。不同 的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。
5.2 SQL的执行流程
-
mysql执行流程大致为:client --> 连接层 --> 查询缓存 --> 解析器 --> 优化器 --> 查询引擎
- 客户端申请连接MySQL服务器
- (连接层)server连接层对申请进行权限查询,若权限通过则给予其一个线程进行执行。
- (服务层:查询缓存)首先sql语句会先在查询缓存中查看是否存在有缓存,若有则直接返回结果(查询缓存在MySQL8就被删除,原因见下文)
- (服务层:解析器)若没有在查询缓存中发现相同的sql语句,则将sql语句传入解析器,进行词法分析和语法分析,若没有通过则会报错,返回给客户端;反之,生成" 语法树 ",传入优化器
- (服务层:优化器)对传入的语法树进行优化,包括物理优化(添加索引)和逻辑优化(更换查询思路),最后生成" 执行计划 ",传入引擎层
- (引擎层)根据传入的执行计划进行执行。
-
查询缓存:
- 查询缓存会将之前的查询sql与语句和结果以key-value的形式进行储存,当新的sql语句进入时,会在查询缓存中进行查找,若能找到完全相同的sql语句,则直接返回结果。记住是完全相同,从他的机制可以看出,查询缓存存在有如下弊端:
- 命中率不高,只有完全相同的sql语句才会被储存上,显然这样很低效
- 会出现缓存失效的情况,如now()函数,很明显两个时间点的now()返回值不一,但缓存没法做到这一点。除此之外,还会出现数据被删除了,然后缓存中仍然保存的情况。
-
解析器:
-
进行语法分析:检查sql的拼写是否正确
-
进行语义分析:检查sql中的访问对象是否存在,比如列名写错了,就会报错。
-
若两者都通过,则会将sql语句解析为如下树:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I1rOnHw3-1685083518520)(…/img/MySQL/image-20230409101511707.png)]
-
-
优化器:
- 物理优化(添加索引)
- 逻辑优化(更换查询思路)
- 返回执行计划
5.3 执行情况记录
- MySQL中可以记录我们的历史执行命令,并查看每一个执行记录的内部情况,以及消耗资源,通过profile开启。
-
检查是否开始profile,若没有则开启:
-
检查:查看变量profiling的值,为0则表示未开启
mysql> select @@session.profiling; +---------------------+ | @@session.profiling | +---------------------+ | 0 | +---------------------+
-
开启:修改变量的值
mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+
-
-
查看历史执行记录,show profiles;
mysql> show profiles; +----------+------------+-----------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------+ | 1 | 0.00017825 | select @@session.profiling | | 2 | 0.00025075 | SELECT DATABASE() | | 3 | 0.00013550 | SELECT DATABASE() | | 4 | 0.00459875 | select * from employees | | 5 | 0.00078525 | select * from employees | | 6 | 0.00011525 | show profile cpu, block, io for query 6 | | 7 | 0.00008125 | show @@profiling | | 8 | 0.00018650 | select @@session.profiling | | 9 | 0.00012475 | select @@session.profiling | | 10 | 0.00019875 | set profiling=1 | | 11 | 0.00016475 | select @@profiling | +----------+------------+-----------------------------------------+ 11 rows in set, 1 warning (0.00 sec)
-
查看某个记录的执行情况。
show profile cpu,block io for query n;
通过此命令可以获得sql语句的执行情况,其中Duration表示耗时。
mysql> show profile cpu,block io for query 5; +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000123 | 0.000000 | 0.000000 | NULL | NULL | | Executing hook on transaction | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | | starting | 0.000014 | 0.000000 | 0.000000 | NULL | NULL | | checking permissions | 0.000012 | 0.000000 | 0.000000 | NULL | NULL | | Opening tables | 0.000085 | 0.000000 | 0.000000 | NULL | NULL | | init | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | | System lock | 0.000013 | 0.000000 | 0.000000 | NULL | NULL | | optimizing | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | statistics | 0.000029 | 0.000000 | 0.000000 | NULL | NULL | | preparing | 0.000024 | 0.000000 | 0.000000 | NULL | NULL | | executing | 0.000207 | 0.000000 | 0.000000 | NULL | NULL | | end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | waiting for handler commit | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | | closing tables | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | | freeing items | 0.000206 | 0.000000 | 0.000000 | NULL | NULL | | cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | +--------------------------------+----------+----------+------------+--------------+---------------+ 17 rows in set, 1 warning (0.00 sec)
5.4 数据库缓冲池(buffer pool)
5.4.1 缓冲池 & 查询缓存
缓冲池
-
缓冲池的作用:将数据页提取到内存中去,减少磁盘io次数
众所周知,数据库的数据是放在磁盘中的,倘若每次读取同样的数据时,都从磁盘中进行提取的话,磁盘io次数很多,执行效率不高。缓冲池的出现就是来解决这个问题的:
对于innodb引擎,所有数据(真实记录,索引,系统数据等)的储存都是以"页"的形式进行储存的
,那么当读取某一条数据时,会先将该数据所在的整个数据页进行读取到缓冲池中去,再进行读写操作,当读写操作结束后,并不会直接释放掉,而是进行储存,将来又需要访问到该数据页的信息时,就不用再进行磁盘io了 -
缓冲池存放的内存:
-
缓冲原则:频次原则
缓冲池是存放于内存中的,内存有限,因此应该
对访问频次较高的数据页进行缓冲
. -
预读特性:
在读取数据页时,会大概率将其周围的数据页也一起存放于缓冲中去
查询缓存
- 查询缓存和缓冲池是两种东西:
- 缓冲池在所有的sql中都会存在,而查询缓存在MySQL8.0之后就已经取消了
- 查询缓存储存的是查询语句和对应的执行结果,而不是数据
5.4.2 缓冲池怎么读取数据
缓冲池 --> 引擎
- 缓冲池会尽量将需要访问的数据储存起来,当每次进行读写操作时,数据库会
先去缓冲池中检查是否有对应的数据页
,如果有则直接对缓冲池中的数据进行读写操作。如果没有,则再进入磁盘将对应数据页读取到缓冲池中。
当修改了缓冲池中的数据后,会直接同步到磁盘中去吗?
-
不会,缓冲池中的数据会以一定频次同步到磁盘中去,比如有checkpoint机制:
当checkpoint时间到了后,会将缓冲池中的数据全部同步到磁盘中去。
当读取新数据页时,
发现缓冲池内存不够用,则checkpoint会将不常用的数据页同步到磁盘中去
。
5.4.3 存在问题
-
buffer pool的存在,使得每次读写都会先经过buffer pool,再以一定频次同步到磁盘中去。那么就存在一个问题:当修改完buffer pool中的数据后,还没同步到磁盘中去,MySQL服务就挂机了,
数据无法进行同步
。那该怎么办?答案:
事务回滚
5.4.4 缓冲池参数
查看、修改缓冲池大小(innodb引擎)
-- 查看
show variables like 'innodb_buffer_pool_size'
-- 修改
set global innodb_buffer_pool_size = 268435496 -- 256M
缓冲池的个数
缓冲池在多线程中时,需要为其添加锁,当并发较高时,又会影响性能
。因此可以设置多个缓冲池
-- 查看
show variables like 'innodb_buffer_pool_instances'
-- 设置
set global innodb_buffer_pool_instances = 2