北京企业免费建站,绵阳网站建设高端品牌,网络系统设计的步骤,广州财税公司排行榜文章目录概述逻辑架构示意图Server 层功能模块连接器查询缓存分析器优化器执行器存储引擎层InnoDBInnoDB 主要特性InnoDB 引擎下的查询过程MyISAMMyISAM 的主要特性MyISAM 引擎下的查询过程InnoDB 和 MyISAM 的对比概述
MySQL 是我们平时开发中最常用的关系型数据库#xff0…
文章目录概述逻辑架构示意图Server 层功能模块连接器查询缓存分析器优化器执行器存储引擎层InnoDBInnoDB 主要特性InnoDB 引擎下的查询过程MyISAMMyISAM 的主要特性MyISAM 引擎下的查询过程InnoDB 和 MyISAM 的对比概述
MySQL 是我们平时开发中最常用的关系型数据库学习 MySQL 的逻辑架构相当于从全局去理解 MySQL 的运行机制对于 MySQL 的学习和使用都会有较大的帮助。
逻辑架构示意图
下面就是 MySQL 的逻辑架构示意图
从宏观角度来看MySQL 可以分为两部分
Server 层是客户端与存储引擎的中间层提供了 MySQL 对外暴露的所有功能存储引擎层负责数据的实际存储和读取
Server 层功能模块
MySQL 的 Server 层提供了对外暴露的所有功能例如请求连接、认证、语法/词法分析、执行语句优化、查询缓存、内置函数、语句执行等功能。这些功能分别由不同的功能模块提供模块与模块之间的分工非常明确同时也是由这些模块的相互协作最终给我们提供了可用的 MySQL 服务。
连接器
连接器是负责与客户端建立连接权限管理以及管理连接的功能模块。 连接器的功能职责比较清晰但也有些细节需要关注
建立连接时如果认证成功连接器会把当前时刻的用户权限快照作为这个连接后续的权限判断逻辑的依据直到连接断开 这也意味着当一个用户成功建立了连接后即使对这个用户的权限进行了修改也不会影响已经存在的连接权限判断 连接建立成功后连接的最大空闲时间即什么操作都不执行的时间由 wait_timeout 参数控制默认为 8 小时 如果在空闲时间超时后再发送操作请求那么将会收到 MySQL 返回的错误消息Lost connection to MySQL server during query如果在空闲时间超时后想要再次正常地发送请求那么需要重新建立连接
查询缓存
查询缓存主要用于将相同的查询语句的结果给缓存起来。查询缓存的工作原理如下所示
执行查询语句之前MySQL 会在内存中查看之前是否执行过相同的一模一样的语句 如果有那么直接将缓存中的结果集返回并结束本次的查询语句执行流程 如果没有那么将正常走后面的逻辑直到拿到结果集查询缓存拿到结果集后将本条查询语句与查询得到的结果集以 K-V 的形式缓存到内存中将结果集返回本次查询语句执行流程结束
既然有缓存那么就需要考虑数据一致性的问题。MySQL 给出的解决方案就是当一个表进行了更新/新增操作后这个表上的所有查询缓存都会失效。 这样一来查询缓存的功能就变得非常鸡肋了。具体的原因有
查询语句完全相同的概率可能并不高执行缓存操作本身也是需要耗费时间和空间的最主要的原因是清空缓存的触发条件过于简单但是影响却十分巨大只有表上有一个更新/新增操作那么这个表上的所有查询缓存都会被清空
综合来说即在大多数场景下查询缓存带来的性能提升效果可能比不上执行缓存操作本身带来的性能消耗即查询缓存不值得使用。 当然在一些几乎完全不会发生更新/新增操作的表上这个查询缓存还是可能会起到提升性能的作用的。 MySQL 8.0 之前可以通过将 query_cache_type 改为 DEMAND并在查询语句的查询返回字段前增加 SQL_CACHE 关键字来显示指定使用查询缓存例如
select SQL_CACHE * from user where id 1;需要注意的是MySQL 在 8.0 及之后的版本将查询缓存功能彻底移除了。
分析器
在客户端向服务端发送了一条 SQL 语句之后MySQL 需要分析这条 SQL 语句是否合法如果合法那么这条 SQL 语句究竟是想要执行什么操作。这就是分析器的职责。 分析的过程主要分为词法分析和语法分析
词法分析将输入的 SQL 语句中的所有单词由空格隔开的字符串识别为不同的含义 例如把 select、update 给识别出来这是一个操作关键字把输入的 distinct 识别为一个去重的关键字 语法分析根据词法分析的结果以及当前配置的 SQL 执行模式sql_mode 参数判断这条 SQL 语句是否合法 如果不合法那么将会直接返回一个语法错误如果合法那么 MySQL 就会将 SQL 语句的执行意图给解析出来
优化器
在经过分析器的词法分析和语法分析后SQL 语句的执行流程就来到了优化器。由上面的逻辑我们可以知道到达优化器的语句必定是一个合法的且执行意图已知的 SQL 语句。 优化器的作用就是尝试为 SQL 语句的执行意图挑选出一种效率最高的执行方案。 例如在一个要执行查询语句的目标数据表中可能存在多个索引优化器将会根据这些索引的类型以及字段组合结合查询语句本身的条件为其挑选一个最优的索引以便用于后续真正的数据查询。 又或者在一个有多表关联的查询语句中根据表连接的字段以及各表的数据量决定表与表之间的连接顺序以及使用的算法。 优化器的工作结束后这条语句的执行方案就确定下来了。值得一提的是我们使用 explain 关键字用于分析一条 SQL 语句的执行计划时返回的正是优化器的一部分分析结果。
执行器
MySQL 通过分析器已经知道了 SQL 语句的执行意图并且通过优化器已经为这条 SQL 语句挑选除了一种效率最高的执行方案那么 SQL 语句的执行流程将会来到执行器。 执行器的主要工作为
首先查看当前用户是否具有 SQL 语句中的目标表的对应操作权限 如果没有例如当前用户没有对于目标表的查询权限那么将会直接返回权限错误 如果有权限那么将会调用当前使用的存储引擎的对应操作接口执行这条 SQL 语句真正的执行意图 例如当前使用的存储引擎是 InnoDB当前执行的 SQL 语句是 select * from user where id1那么执行器将会调用存储引擎层的查询接口执行对于 user 表的具体查询操作
存储引擎层
存储引擎层负责真正的数据存储和提取其结构对于 Server 层来说是插件式的封装了具体的存储引擎的操作逻辑。 存储引擎的服务对象是表。意思就是说不同的表可以使用不同的存储引擎同一个数据库中的不同表也可能使用不同的存储引擎。 下面将介绍常用的两个存储引擎InnoDB 和 MyISAM。
InnoDB
InnoDB 是 MySQL 5.5 版本后的默认存储引擎也是日常开发过程中使用的最多的存储引擎。 使用 InnoDB 作为引擎来存储的表 会对应磁盘上的两个文件
*.ibd索引及数据文件存储的是聚集索引索引与数据在同一棵 B 树中以及非聚集索引*.frm表结构文件存储的是表结构
InnoDB 主要特性
InnoDB 的主要特性如下所示
支持事务支持更细粒度的锁行锁拥有崩溃后安全恢复crash safe能力支持外键
InnoDB 引擎下的查询过程
在使用了 InnoDB 引擎的表的单表查询语句的执行过程将会是这样
首先看查询条件中是否使用了聚集索引 如果有直接在聚集索引中进行 BTree 查找直到找到数据并将数据返回时间复杂度为 OlogN 如果不是聚集索引则看查询条件中是否命中的二级索引非聚集索引 如果可以命中二级索引则首先在对应的二级索引树中查找如果找到了则取叶子节点上的聚集索引值再回到聚集索引中使用刚刚查找到的聚集索引值进行查询回表操作并将数据返回时间复杂度为 OlogN 如果二级索引也不能命中则直接在聚集索引树中遍历所有叶子节点待全表扫描完后再将中途查找到的符合条件的所有数据返回时间复杂度为 ON
MyISAM
MyISAM 是 MySQL 最早出现的一批存储引擎之一但是现在在日常开发过程中已经比较少用。 MyISAM 也有很多优点但是有一个致命的缺点不支持事务没有 crash safe 能力。 使用 MyISAM 作为引擎来存储的表 会对应磁盘上的三个文件
*.myi索引文件存储的是非聚集索引叶子节点上存储的是数据对应的地址.myd 文件中的位置*.myd数据文件存储的是实际的数据*.frm表结构文件存储的是表结构
MyISAM 的主要特性
MyISAM 的主要特性如下所示
只支持表锁内置了一个计数器来存储表的行数延迟更新索引键如果在创建表时指定了 DELAY_KEY_WRITE 参数那么每次更新了索引相关的数据后并不会立刻将修改的索引数据写入磁盘中而是采用了缓冲区延时批量写入的设计来延后地、批量地写入更新的索引数据。这样可以极大地提升写入的性能设计简单数据以紧密的格式存储在更新较少的场景下性能表现很好
MyISAM 引擎下的查询过程
在使用了 MyISAM 引擎的表的单表查询语句的执行过程将会是这样
首先看查询条件中是否有可以命中的索引 如果有则在索引文件中进行 BTree 查找直到找到数据的地址然后再通过数据地址在数据文件中找到对应的数据时间复杂度为 OlogN 如果没有则在数据文件中遍历所有数据行待全表扫描完成后再将中途查找到的符合条件的所有数据返回时间复杂度为 ON
InnoDB 和 MyISAM 的对比
InnoDB 和 MyISAM 的主要区别有
MyISAM 不支持事务InnoDB 支持事务两个存储引擎最大的两个区别之一MyISAM 不支持事务的特性导致了它在注重数据一致性的场景下无法使用MyISAM 不支持崩溃后的安全恢复crash safe而 InnoDB 则支持也是两个存储引擎最大的两个区别之一MyISAM 不支持 crash safe 导致了它在注重数据安全的场景下无法使用MyISAM 只支持表锁而InnoDB 既支持表锁也支持行级锁MyISAM 只支持表锁的特性在更新操作稍多的场景下读写性能会大幅下降这也导致了在这种场景下 MyISAM 的使用率将会比较低对表的行数查询的支持不同 MyISAM 内置了一个计数器来存储表的行数在需要查询表的行数时直接从计数器中拿出即可InnoDB 需要去统计所有的行数在高版本的 MySQL 中InnoDB 也会有一个存了行数的变量但这只是个估计值需要准确的值时仍需要去实时统计 MyISAM 不支持外键InnoDB 支持外键delete from table 的处理方式不一样 MyISAM直接重新建表InnoDB 会一行一行的删除 文件存储方式不同 MyISAM 一个表在磁盘上对应三个文件*.myi索引文件、*.myd数据文件、 *.frm表结构文件Innodb一个表在磁盘上对应两个文件*.ibd数据及索引文件、 *.frm表结构文件
总的来说在不考虑数据一致性以及数据安全性且查询操作远多于更新操作的场景下可以考虑选择 MyISAM 作为存储引擎否则都应该选择 Innodb 引擎
