我们在写sql的时候crud这些基本的操作想必大家已经是得心应手了,专栏会更倾向于sql优化与运行过程模型的角度重新学习sql,方便大家在做性能优化上更加熟练。
专栏的第一篇文章,首先从最简单的sql语句入手:
select * from T where ID=10;
接下来我们来拆解一下这个sql语句,从中就可以看出sql在mysql中各个模块的执行过程。
总体来说,mysql分为了Server层和存储引擎层。
Server层包括了连接器,查询缓存,分析器,优化器,执行器等等,涵盖mysql的大多数核心服务功能,以及内置函数,实现了跨存储功能。如:存储过程,视图,触发器。
而存储引擎层负责数据的存储和提取。支持InnoDB,MMAISAM,Memory等多个存储引擎。现在最常用的存储引擎是InnoDB,也就是我们MySQL的默认存储引擎。
换句话说,如果不指定哪种存储引擎,默认都是InnoDB,如果指定其他种类也没问题,但存取方式更不相同,支持的方式也不同。
我们可以从图中看出,不同的存储引擎公用一个Server层,也就是从连接器到执行器。可以先对每个组件的名字有个印象,以下来分别介绍一下:
- 连接器
第一步,先连接到这个数据库上,这时候就是连接器来接待。连接器用来进行认证,连接和管理。连接命令一般是这么写:
[root@localhost ~]# docker exec -it mysql04 bash
root@8c6e753e3a28:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
这里是以docker举例,大家也可以用自己的方式,总之是进行连接了。
连接过程中,mysql是客户端工具,用来与服务端建立连接。使用TCP协议进行握手,然后连接器认证身份进行连接。
- 如果认证错误,就会结束程序。
- 如果认证成功,就会认证权限,然后根据权限去判断逻辑。
连接完成之后,进入空闲状态,可以使用如下命令查看连接状态:
然后默认8小时不进行操作,就会自动断开连接,由wait_timeout控制。
数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。
如同线程一样,建立连接本身是十分复杂的,所以不要频繁建立连接。尽量使用长连接。
但是如果使用长连接,内存就会长得很快,这是因为mysql的执行过程中内存会囤积在连接对象,只有断开连接才会释放,所以就会oom了。
解决方案:
- 定期重连
- 通过执行 mysql_reset_connection来重新初始化连接资源。
2. 查询缓存
连接结束之后来到第二阶段,查询缓存。
当我们执行语句,都会从cache里面找历史记录,历史记录以KV形式存储,K 是sql语句,V是执行结果,如果能搜索到K,就会直接返回V,节省系统资源。
如果语句不在缓存,就会正常执行,然后存储查询缓存。
但是要注意的是,查询缓存本身有很大的弊端
首先是他的失效,你的sql不可能永远都存在sql中,只要update一个表,所有cache就会清空,所以要频繁更新的表,就不能依靠查询缓存。一般静态表比较适合用查询缓存。
3. 分析器
如果查询缓存失败,就要真的去执行sql了。首先,mysql会对sql进行解析。
首先识别你输入的这个字符串分别是什么,代表什么。
然后找出类似于select update delete insert这样的语句,判断你大体是查询,删除还是什么的。
将表名,列名找出,进行转化,表A就是tableA 列B就是ColumnB。
然后最后就是语法分析,判断正误。
4.优化器
当识别出没有问题,就会进行优化。
首先找出索引,决定使用哪一个索引最好,或者多表关联,会计算出连接顺序。
这里既可以先取出d.dept is Null然后再左外连到e表,也可以先关联,再将不满足条件的删除。
最后结果一样,但是效率不同。
优化器就是用选择哪个方案最好的。
5. 执行器
当分析器知道要做什么,优化器想好了该怎么优化,最后就是执行了。
执行之前先会判断权限问题,权限满足许可才能继续运行。
举例来看:
select * from mysqlTable where number = 1;
然后说一下正常流程:
(1) 首先InnoDB先会用where去循环判断number的条件,满足就放到结果集,不满足就跳过继续遍历,直到结束。
(2) 然后将结果集返回到客户端。
对于有索引的表,执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口,之后 循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的。