mysql如何执行流程 |
MySQL架构与SQL执行流程MySQL主要而分为server层和存储引擎层两部分
SQL查询语句执行流程 select * from T where ID=10;
连接命令 mysql -h$ip -P$port -u$user -p 连接命令中的 mysql 是客户端工具,用来跟服务端建立连接 。 在完成经典的 TCP 握手后,连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码 。 如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端程序结束执行 。 如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限 。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限 。 一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限 。修改完成后,只有再新建的连接才会使用新的权限设置 。 连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以在 show processlist 命令中看到它 。 客户端如果太长时间没动静,连接器就会自动将它断开 。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时 。 全部使用长连接后,有些时候 MySQL 占用内存涨得特别快,这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的 。这些资源会在连接断开的时候才释放 。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是 MySQL 异常重启了 。 解决这个问题,以下两种方案 定期断开长连接 。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连 。 如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源 。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态 。
查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空 对于更新压力大的数据库来说,查询缓存的命中率会非常低 。除非你的业务就是有一张静态表,很长时间才会更新一次 。比如,一个系统配置表,那这张表上的查询才适合使用查询缓存 。
对 SQL 语句做解析 分析器先会做“词法分析” 。你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么,代表什么 。 MySQL 从你输入的"select"这个关键字识别出来,这是一个查询语句 。它也要把字符串“T”识别成“表名 T”,把字符串“ID”识别成“列 ID” 。 做完了这些识别以后,就要做“语法分析” 。根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法 。 如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒,比如下面这个语句 select 少打了开头的字母“s” 。 一般语法错误会提示第一个出现错误的位置,所以你要关注的是紧接“use near”的内容 。
优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序
开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证 。查询也会在优化器之前调用 precheck 验证权限) 。 如果有权限,就打开表继续执行 。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口 。 select * from T where ID=10 ID 字段没有索引,那么执行器的执行流程是这样的: 1、调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是 10,如果不是则跳过,如果是则将这行存在结果集中; 2、调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行 。 3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端 。 对于有索引的表,执行的逻辑也差不多 。第一次调用的是“取满足条件的第一行”这个接口,之后循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的 。 你会在数据库的慢查询日志中看到一个 rows_examined 的字段,表示这个语句执行过程中扫描了多少行 。这个值就是在执行器每次调用引擎获取数据行的时候累加的 。 在有些场景下,执行器调用一次,在引擎内部则扫描了多行,因此引擎扫描行数跟 rows_examined 并不是完全相同的 SQL更新语句执行
当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log 里面,并更新内存,这个时候更新就算完成了 。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做 记录这个页做了什么改动 redo log 用于保证 crash-safe 能力 。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘 。这个参数我建议你设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失 。
Binlog有两种模式, statement 格式的话是记sql语句, row格式会记录行的内容,记两条,更新前和更新后都有 。
1、redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用 。 2、redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ” 。 3、redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的 。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志 。
update T set c=c+1 where ID=2; 1、执行器先找引擎取 ID=2 这一行 。ID 是主键,引擎直接用树搜索找到这一行 。如果 ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回 。 2、执行器拿到引擎给的行数据,把这个值加上 1,比如原来是 N,现在就是 N+1,得到新的一行数据,再调用引擎接口写入这行新数据 。 3、引擎将这行新数据更新到内存中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态 。然后告知执行器执行完成了,随时可以提交事务 。 4、执行器生成这个操作的 binlog,并把 binlog 写入磁盘 。 5、执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状态,更新完成 。 两阶段提交(跨系统维持数据逻辑一致性时常用的一个方案) 数据库恢复 binlog 会记录所有的逻辑操作,并且是采用“追加写”的形式 需要恢复到指定的某一秒时
先写 redo log 后写 binlog 。 假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启 。由于我们前面说过的,redo log 写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行 c 的值是 1 。但是由于 binlog 没写完就 crash 了,这时候 binlog 里面就没有记录这个语句 。因此,之后备份日志的时候,存起来的 binlog 里面就没有这条语句 。 然后你会发现,如果需要用这个 binlog 来恢复临时库的话,由于这个语句的 binlog 丢失,这个临时库就会少了这一次更新,恢复出来的这一行 c 的值就是 0,与原库的值不同 。 先写 binlog 后写 redo log 。 如果在 binlog 写完之后 crash,由于 redo log 还没写,崩溃恢复以后这个事务无效,所以这一行 c 的值是 0 。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志 。所以,在之后用 binlog 来恢复的时候就多了一个事务出来,恢复出来的这一行 c 的值就是 1,与原库的值不同 。 InnoDB
索引:索引优化是对查询性能优化的最有效手段; B+Tree(平衡树,查找任意节点耗时相同,比较次数就是树高) 索引可以分为聚集索引和辅助索引
锁
意向锁不会阻塞全表扫描之外的任何请求,主要目的是为了表示是否有人请求锁定标中某一行数据
意向锁不会阻塞全表扫描之外的任何请求,主要目的是为了表示是否有人请求锁定标中某一行数据 总结以上为个人经验,希望能给大家一个参考,也希望大家多多支持 。 |