ToB企服应用市场:ToB评测及商务社交产业平台

标题: 01 | 基础架构：一条SQL查询语句是如何执行的？ [打印本页]

作者: 络腮胡菲菲 时间: 2023-6-8 16:29
标题: 01 | 基础架构：一条SQL查询语句是如何执行的？
FAQ

画出 MySQL 的基本架构图

Server 层和存储引擎层各自有哪些组件？

Server 层

连接器
查询缓存
分析器
优化器
执行器
所有内置函数（日期、时间、数学和加密函数等）
所有跨存储引擎的功能（存储过程、触发器、视图等）

存储引擎层

负责数据的存储和读取

连接器的作用？

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

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

针对表级别的权限修改，无论连接是新建的还是后来创建的，命令立马生效，
针对全局权限或者库级别权限的修改，已有的连接不受影响（特殊情况：库级别权限修改并且使用 use db 命令，一般程序都会使用该命令），后续新建的连接才会生效。

什么是长连接、短连接？

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

使用长连接，为什么有时可能会导致 MySQL 占用内存涨得特别快，从而导致内存占用太大，被系统强行杀掉（OOM）

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

如何解决长连接长时间不断开，导致连接内存占用太大，进而导致 MySQL 内存占用太大，从而被系统强行杀掉？

两种方案

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

[wait_timeout and interactive_timeout 参数的区别和联系](https://www.yuque.com/docs/share/26d83ed1-bcff-4211-a315-1c32b2745a1e?# 《wait_timeout and interactive_timeout 参数的区别和联系》)

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

交互式连接：通过 mysql 客户端与 mysql server 建立的连接是交互式连接
非交互式连接：不是通过 mysql 客户端，而是通过 jdbc 等方法与 mysql server 建立的连接称为非交互式连接

会话级变量 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 语法

优化器的作用？

优化器在表中有多个索引时，决定使用哪个索引
或者在一个语句有多表关联时，决定各个表的连接顺序

执行器的作用？

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

执行器执行查询前，需要判断是否有查询语句中相关表的查询权限，为什么不将这个权限校验的过程放到优化器或分析器中去做？

连接阶段只是“获得权限信息”，真正开始查询动作，才判断“有没有操作这个表的权限”
连接阶段是去系统表读数据，结果放在变量，执行器使用这个变量

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)