最近在学习 MySQL 实战 45 讲, 觉得里面的内容很受用, 做一些笔记记录下:
首先是 MySQL 的一个基础架构的解释, 如下图:
从上图我们可以清晰的看到, MySQL 的基础架构主要分为两个部分, 一个是 server 层(负责大多数核心服务功能的实现), 一个是存储引擎层(负责数据的存储与提取).
server 层是跨存储引擎的, 也就是说, 当客户端执行一条查询语句时, 必须经过 server 层, 不管当前查询的表指定的存储引擎是 INNODB 还是 MYISAM.
server 层主要包括以下几个部分:
(1)连接器: 管理与客户端的连接, 包括建立以及维持连接. 可以用以下命令连接 MySQL 数据库:
MySQL -h$ip -P$port -u$user -p
连接又分为长连接与短连接. 其实长连接是相对于通常的短连接而说的, 也就是长时间保持客户端与服务端的连接状态. 通常的短连接操作步骤是: 连接 ->数据传输 ->关闭连接; 而长连接通常就是: 连接 ->数据传输 ->保持连接 ->数据传输 ->保持连接 ->............->关闭连接; 这就要求长连接在没有数据通信时, 定时发送数据包, 以维持连接状态, 短连接在没有数据传输时直接关闭就行了. 长连接主要用于在少数客户端与服务端的频繁通信, 因为这时候如果用短连接频繁通信常会发生 Socket 出错, 并且频繁创建 Socket 连接也是对资源的浪费. 但是对于服务端来说, 长连接也会耗费一定的资源, 这是因 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的, 这些资源会在连接断开的时候才释放. 所以如果长连接累积下来, 可能导致内存占用太大, 被系统强行杀掉 (OOM), 从现象看就是 MySQL 异常重启了. 需要专门的线程(unix 下可以用进程管理) 来负责维护连接状态. 总之, 长连接和短连接的选择要视情况而定.
(2)查询缓存: MySQL 查询缓存保留了查询返回给客户端的完整结果, 当缓存命中的时候, 服务器马上返回保存的结果(会先检查权限), 并跳过解析, 优化和执行步骤.
当 MySQL 接收到查询请求时, 会查询缓存是否命中, 若命中, 则直接返回查询结果, 这里的 key 就是查询语句, value 就是查询结果. 在数据频繁更新的数据库中, 查询缓存一般不建议使用, 因为每当有表更新操作时, 所有的缓存都会失效. 可以通过设置 query_cache_type=demand 按需使用查询缓存, 如:
MySQL> select SQL_CACHE * from ...
mysql8.0 版本已将该功能去掉.
缓存配置参数:
query_cache_limit: MySQL 能够缓存的最大结果, 如果超出, 则增加 Qcache_not_cached 的值, 并删除查询结果
query_cache_min_res_unit: 分配内存块时的最小单位大小
query_cache_size: 缓存使用的总内存空间大小, 单位是字节, 这个值必须是 1024 的整数倍, 否则 MySQL 实际分配可能跟这个数值不同(感觉这个应该跟文件系统的 blcok 大小有关)
query_cache_type: 是否打开缓存 OFF: 关闭 ON: 总是打开
query_cache_wlock_invalidate: 如果某个数据表被锁住, 是否仍然从缓存中返回数据, 默认是 OFF, 表示仍然可以返回.
(3)分析器: 对用户输入的查询语句进行词法分析, 语法分析, 语义分析, 构造执行树.
a)首先是解析器将查询分解成一个个标识, 然后构造一颗 "解析树", 解析器保证查询中的标识都是有效的, 会检查其中的基本错误, 比如字符串上面的引号没有闭合等.
b)然后预处理器检查解析器生成的解析树, 解决解析器无法解析的语义. 比如, 它会检查表和列名是否存在, 检查名字和别名, 保证没有歧义. 最后, 预处理器检查权限.
eg:
a)以下代码为当我们查询一个不存在的列如 k 时, 应该是在预处理器阶段返回的错误;
select * from T where k=1
b)以下代码就是在语法分析阶段, MySQL 发现语句不对, 报错: You have an error in your SQL syntax;
- MySQL> elect * from t where ID=1;
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1
ps: 语法检查阶段的具体顺序如下图:
(4)优化器: 优化器把解析树变成执行计划. 一个查询通常可以有很多种执行方式, 并且返回同样的结果, 优化器的任务就是找到最好的方式.
MySQL 使用基于成本的优化器, 它将尝试预测一个查询使用某种执行计划的成本, 并选择其中成本最小的一个. 最初, 成本的最小单位是随机读取一个 4K 数据页的成本, 后来成本计算公式变得更加复杂, 并且引入了一些 "因子" 来估算某些操作的代价, 如当执行一次 where 条件比较的成本. 可以通过查询当前会话的 last_query_cost 的值来得知 MySQL 计算的当前查询的成本.
有很多种原因会导致 MySQL 优化器选择错误的执行计划, 比如:
1. 统计信息不准确.
2. 执行计划中的成本估算不等同于实际的执行计划的成本.
3. MySQL 的最优可能与你想的最优不一样.
4. MySQL 从不考虑其他并发的查询, 这可能会影响当前查询的速度.
5. MySQL 也不是任何时候都是基于成本的优化, 有时候也会基于一些固定的规则.
6. MySQL 不会考虑不受其控制的成本, 例如执行存储过程或者用户自定义的函数的成本.
(5)执行器: 对优化器生成的执行计划进行执行操作.
在执行之前, 会检查当前用户是否有查询权限, 如果没有对应的权限, 会报错如下:
- MySQL> select * from T where ID=10;
- ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
这里有个疑问, 为什么丁奇老师讲的是权限检查是在执行器阶段进行, 而网上的很多资料都表明在分析器阶段进行权限检查???
丁奇老师给出的回答是: 有些时候, SQL 语句要操作的表不只是 SQL 字面上那些. 比如如果有个触发器, 得在执行器阶段 (过程中) 才能确定. 优化器阶段前是无能为力的. 而通过实验, 如下代码:
- MySQL> select * from T where k=1
- ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
可以发现, 在创建了一个没有 select 权限的用户之后, 执行以下语句, 报错的确是没有权限, 按照丁奇老师的说法, 这个语句应该是通过了分析器 (预处理器检查每个列是否存在) 阶段, 进入了执行器阶段进行权限检查然后报错的, 但其实这个语句在分析器阶段就会报错了, 与丁奇老师说的在执行器阶段进行的权限检查其实是矛盾的, 虽然丁奇老师给出的回答是: 这个是一个安全方面的考虑. 你想想一个用户如果没有查看这个表的权限, 你是会告诉他字段不对还是没权限? 如果告诉他字段不对, 其实给的信息太多了, 因为没权限的意思还包含了: 没权限知道字段是否存在. 但个人还是比较信服于在分析器阶段其实就已经进行了权限检查了.
ps: 结合丁奇老师以及网上的一些资料, 会不会有可能进行了两次权限检查呢??? 查阅了一些资料, 发现针对 procedure 和 function 的 create 和 alter 是一种特殊权限 , 他们是全局的. 猜测可能是第一次是对表的权限检查, 第二次在执行器阶段是进行特殊权限检查.
最后就是调用存储引擎的 API 接口获取数据并返回给客户端了.
来源: https://www.cnblogs.com/cfYu/p/10129135.html