01 | 基础架构：一条SQL查询语句是如何执行的？

2023-06-08 16:16 由甲兵匪已发表于 #数据库

Server 层

存储引擎层

连接器负责跟客户端建立连接、获取权限、维持和管理连接
获取权限：连接器从权限表中获取用户所拥有的权限，
- 如果是全局权限，则将权限信息加载到内存 acl_users 数组中，并且将权限信息拷贝一份到线程对象中，连接中的权限判断以线程对象中保存的权限信息作为依据，因此全局权限的 grant/revoke 只对新建连接生效，对已存在的连接不生效。
- 如果是库级别权限，则将数据表的权限信息加载到内存 acl_dbs 数组中，之后，权限判断逻辑以内存 acl_dbs 数组中的权限信息作为依据，不过存在一种特殊情况，使用 use db 命令时，会将数组 acl_dbs 中的用户权限信息拷贝一份保存到会话变量中，之后，在该会话中的权限检验以该会话变量中保存的用户权限为准。
- 如果是表、列权限，则将权限信息加载到 column_priv_hash 哈希结构中，之后权限判断，以该哈希结构中存储的数据为准，对已建立的连接立刻生效。

权限范围	存储权限信息的数据表	存储权限信息的内存数据结构	grant/revoke 语句对数据表和内存权限信息的影响	特殊逻辑	grant/revoke对已建立连接的影响	对新建立连接的影响
全局权限	mysql.user	acl_users	每次 grant/revoke 时，不仅修改数据表，也会同步修改内存 acl_users 数组	mysql 会为连接维护一个线程对象，并将内存 acl_users 数组中的用户权限信息拷贝到线程对象中，在此之后，此连接中的所有全局权限的判断，直接使用线程对象中保存的权限位	不影响已建立连接	立刻生效
库级别权限	mysql.db	acl_dbs	每次 grant/revoke 时，不仅修改数据表，也会同步修改内存 acl_dbs 数组	use db 语句会将获取到的库权限信息保存在会话变量中，在此之后，此连接中的所有库权限的判断，都直接使用该会话变量中保存的权限位	- 不影响执行过 use db 命令的已建立连接 - 对未指定库的连接，立即生效	立刻生效
表和列权限	mysql.tables_priv, mysql.columns_priv	column_priv_hash	每次 grant/revoke 时，不仅修改数据表，也会同步修改内存 column_priv_hash 结构	无	立刻生效	立刻生效

如果客户端使用了连接池，则客户端和RDS实例之间的连接为长连接（建立连接并读写完成后不会主动断开的连接），反之则为短连接（建立连接并读写完成后主动断开的连接）。

因为 MySQL 在执行过程中临时使用的内存是在连接对象里进行管理的。而在默认情况下，连接对象中申请的资源会在连接断开时才会释放（释放给了系统还是 MySQL?）。因此如果长连接长时间不断开连接，可能导致 MySQL 内存占用太大，从而被系统强行杀掉（OOM）

两种方案

定期断开长连接。使用一段时间，或者在程序里判断执行过一个占用内存的大查询后，断开连接，之后查询再重建连接
在 MySQL 5.7 或更新版本，每次执行完一个占用内存较大的操作后，执行 [mysql_reset_connection](https://www.yuque.com/docs/share/7ff2ccda-65c6-4dcb-b93c-f7d08f7fecd7?# 《cmd_reset_connection》) 来重新初始化连接资源。并且不需要重连和重新校验权限，但会将连接恢复到刚刚创建完成时的状态
1. C API：mysql_reset_connection
2. Python API：cmd_reset_connection（无法重新初始化连接资源，释放不了所占用的内存，只能重置用户和系统变量）

为什么叫交互式和非交互式连接？通俗解释不知道？

会话级变量 wait_timeout

参数	作用
wait_timeout	非交互式连接建立完成后，使用过程中的等待时间（单位：秒）
interactive_timeout	交互式连接建立完成后，使用过程中的等待时间（单位：秒）

交互式连接的会话变量 wait_timeout 和 interactive_timeout 都继承自全局变量 interactive_timeout
非交互式连接的会话变量 wait_timeout 和 interactive_timeout 分别继承自全局变量 wait_timeout 和 interactive_timeout
无论是交互式还是非交互式连接，连接闲置阈值都由会话变量 wait_timeout 控制，与会话变量 interactive_timeout 无关。

查询缓存中存储着执行过的语句和其结果，并以 key-value 的形式存储在内存中，其中 key 对应查询的语句，value 对应查询的结果。当查询语句能够在查询缓存中匹配到的话，则直接返回查询结果。

查询缓存的失效机制：只要表上有一个更新，那么查询缓存中关于这张表的所有查询缓存都将清空，这对于更新频繁的数据库来说，查询缓存的命中率会非常低，因此查询缓存基本没用

解析 SQL 语句，以获悉该 SQL 语句要做什么。

SQL 语句由多个字符串和空格、换行组成，词法解析会将 SQL 中的字符串识别出来，并确定每个字符串代表的含义（表、字段、关键字等）
比如，查询语句 select id from t where id = 10，词法解析会将字符串 ”t“ 识别为”表名 t“，字符串 ”id“ 识别为“列 id”

语法解析器根据语法规则判断 SQL 语句是否满足 MySQL 语法

调用存储引擎提供的接口去读写数据