MySQL实战45讲学习笔记(一)

查询语句执行过程 (select * from T where ID=10;)
mysql的逻辑架构图
大体来说,MySQL 可以分为 Server 层和存储引擎层两部分,不同的存储引擎共用一个Server 层。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。不过可以在 create table
语句中使用 engine=xxx
来指定引擎
下文涉及到的是select * from T where ID=10;
这条SQL运行的过程
1. 连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令:
1 | mysql -h$ip -P$port -u$user -p |
可以只写个-p
并换行再输密码,这样安全一些。如果你连的是生产服务器,强烈建议你这么做。
命令中的mysql
是客户端工具,用来跟服务端建立连接。
在输入用户名和密码后,如果有误,则报错Access denied for user
否则在权限表内查出你的权限,你接下来的操作就限于这个权限内了
但是,如果一个用户已经登录了,那即便管理员修改了他的权限,他当前的权限也不会收到影响,除非他重新登录,建立新的连接
连接完成后如果不在这个连接上进行操作,那么这个连接就处于空闲状态,可以在 show processlist
命令中看到它。
客户端超过8h没响应,就会断开这个连接,这个时间是由参数wait_timeout
控制的,即默认是8h
此时再次发送请求的话,就会收到一个错误提醒: Lost connection to MySQL server during query。
在使用中肯定要尽量使用长连接,但是长连接也有弊端:MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。这些内存累计起来就可能导致进程被系统杀死,即异常重启
对此有两种解决方案:
- 规定一个最长连接时间,连接时间超过这个时间的连接就断开;如果刚刚执行过了一个占内存很大的操作,就断开。
- 如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。
2. 查询缓存
大多数情况下作者不建议使用查询缓存,因为弊大于利
原因:只要你对一张表进行了更新,这张表的查询缓存就会清空,所以除非是很长时间都不会更改的、近似静态或就是静态的表,就推荐用这个查询缓存。
需要注意的是,MySQL 8.0 版本直接将查询缓存的整块功能删掉了,也就是说 8.0 开始彻底没有这个功能了。
3. 分析器
即解析SQL语句
- 词法分析:提取出”select”这个查询操作关键词,”T”这个表名
- 语法分析:分析SQL是否符合语法,不符合就会
You have an error in your SQL syntax
,查错要关注的是紧接use near
之后的内容。
4. 优化器
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。有时候几个执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。
5. 执行器
先判断用户有没有对表T进行查询操作的权限
如果有权限,就打开表继续执行,执行器就会根据表的引擎定义,去使用这个引擎提供的接口,获得结果集返回给客户端。
你会在数据库的慢查询日志中看到一个 rows_examined
的字段,表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的,但是引擎扫描行数跟 rows_examined
并不是完全相同的。
课后问题:
如果表 T 中没有字段 k,而你执行了这个语句 select * from T where k=1
, 那肯定是会报Unknown column ‘k’ in ‘where clause
,这个错误是在我们上面提到的哪个阶段报出来的呢?
答:分析器。分析器会提取出输入的SQL的关键词,并且判断语句是否正确,表是否存在,列是否存在等。
更新操作过程中的日志模块 (update T set c=c+1 where ID=2;)
与查询流程不一样的是,更新流程还涉及两个重要的日志模块,redo log(重做日志)
和 binlog(归档日志)
。redo log
是 InnoDB 引擎特有的日志, binlog
是server层的日志
redo log
如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。
所以有了Mysql的WAL(Write-Ahead Logging)
技术,即先写日志,再写磁盘。当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log
里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做,
当然,redo log
也有大小限制,当大小不足时会先停下来把一部分记录更新到磁盘,以空出空间给后面的操作用
有了 redo log
,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe
。
两个日志模块的比较
有两个日志模块的原因:Mysql自带的MyISAM引擎不能crash-safe
,**binlog
日志只能用于归档**,所以那个写了innoDB的公司就在innoDB里写了具有crash-safe
能力的redo log
两种日志的不同:
redo log
是InnoDB 引擎特有的;binlog
是 MySQL 的 Server 层实现的,所有引擎都可以使用。redo log
是物理日志,记录的是“在某个数据页上做了什么修改”;`binlog1 是逻辑日志,记录的是这个SQL语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。redo log
是循环写的,空间固定会用完;binlog
是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。redo log(重做日志)让InnoDB存储引擎拥有了崩溃恢复能力。
binlog(归档日志)保证了MySQL集群架构的数据一致性。
执行器在用innoDB引擎执行这个update操作的流程
- 执行器先从引擎中找到数据,如果在内存则直接返回,如果不在内存查询后返回
- 执行器拿到数据后,会先修改数据,然后调用引擎接口重新写入数据
- 引擎将数据更新到内存,同时写入
redo log
,此时处于prepare
状态 - 执行器生成这个操作的
binlog
- 执行器调用引擎的事务提交接口,将
redo log
状态改成commit
状态,更新完成
作者的图
两阶段提交
注意最下面的这三个
发现一个记将录写入redo log
的操作,愣是先把先设为prepare
状态,、再设置为commit
状态,难道不能直接设置吗?
我们假设不搞两阶段提交,并且举出两种情况
先写binlog,再写redo log
当前事务提交后,写入binlog成功,之后主节点崩溃。在主节点重启后,由于没有写入redo log,因此不会恢复该条数据。
而从节点依据binlog在本地回放后,会相对于主节点多出来一条数据,从而产生主从不一致。先写redo log,再写binlog
当前事务提交后,写入redo log成功,之后主节点崩溃。在主节点重启后,主节点利用redo log进行恢复,就会相对于从节点多出来一条数据,造成主从数据不一致。
因此,只写一次redo log与binlog,无法保证这两种日志在事务提交后的一致性。也就是无法保证主节点崩溃恢复与从节点本地回放数据的一致性。
不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候,也就是需要再多搭建一些备库来增加系统的读能力的时候,现在常见的做法也是用全量备份加上应用 binlog 来实现的,这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
redo log
和 binlog
都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。
即:
redo
还没写入之前崩溃,这时binlog
也还没写入,恢复数据不受影响redo
写好了,binlog
还没写入之前崩溃,这是redo
处于prepare
状态,还没有提交,恢复时事务会回滚,binlog
也还没有记录,所以不会影响redo
写好了,binlog
写好了,但是还没有commit
时崩溃了,这时会判断对应事务的binlog是否存在并完整:
(1)如果存在并完整则提交事务,这时恢复到事务提交之后的状态,因为binlog
中有记录,所以恢复成功
(2)如果binlog
不存在或者不完整,这时会恢复到事务提交之前的状态,因为binlog
中无记录或者不完整的记录不会生效,所以恢复也成功。redo
已经有了commit
标识,则直接提交事务,同时因为binlog
有记录,则恢复数据也不受影响
redo log
用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit
这个参数设置成 1
的时候,表示每次事务的 redo log
都直接持久化到磁盘。这个参数建议设置成 1
,这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog
这个参数设置成 1
的时候,表示每次事务的 binlog
都持久化到磁盘。这个参数也建议设置成 1
,这样可以保证 MySQL 异常重启之后 binlog
不丢失。
- 标题: MySQL实战45讲学习笔记(一)
- 作者: urlyy
- 创建于 : 2022-02-26 11:46:27
- 更新于 : 2025-03-16 01:04:15
- 链接: https://urlyy.github.io/2022/02/26/MySQL实战45讲学习笔记(一)/
- 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。