李文轩 2019-06-23
声明：这是本人学习极客时间的SQL必知必会的笔记，有侵权请联系我。
- Oracle中的SQL执行方法，硬解析和软解析
- MySQL中的SQL执行方法，它的体系结构是怎么样的
- 存储引擎，MySQL有什么存储引擎
Oracle中的SQL执行方法，硬解析和软解析
Oracle 的 SQL 语句执行步骤
- 语法检查：检查SQL的拼写
- 语义检查：检查SQL中的访问对象是否存在。这个对象包括我们自定义的列名、表名等等
- 权限检查：查看用户是否具备访问该数据的权限
- 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存SQL语句和该语句的执行计划。Oracle通过检查共享池是否存在SQL语句的执行计划，来判断用软解析还是硬解析。在共享池中，Oracle首先对 SQL 语句进行 Hash 运算，根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行（直接进入“执行器“），此为
软解析。如果没有相应的执行计划，Oracle就要创建解析树进行解析，然后生成执行计划，进入“优化器”，此为硬解析。 - 优化器：硬解析的一环，创建解析树，生成执行计划。
- 执行器：SQL的执行需要解析树和执行计划，有了这两个必要条件就能执行了。
Oracle中的术语
- 库缓存：主要缓存 SQL语句和执行计划。通过它，Oracle判断 SQL 语句是否需要进入硬解析。为了提升执行效率，应该尽量避免硬解析
- 数据字典缓冲区：对象定义，比如表、视图、索引等对象。对 SQL 语句进行解析的时候，如果需要相关数据，会从数据字典缓冲区中提取。
优化
- 避免硬解析：
Oracle 在库缓存步骤决定硬或软解析；为了提升执行效率，往往需要避免硬解析，因为执行过程中，创建解析树和生产执行计划很消耗资源。
Oracle的绑定变量帮助我们解决此问题。绑定变量在 SQL语句中使用的变量，通过不同的变量取之来改变 SQL 的执行结果。
优点：提升软解析的可能性
不足：可能会导致生成的执行计划不够优化
-
例子：
-- 普通的查询语句 select * from player where player_id = 10001; -- 查询不同id时，都会创建一个新的查询解析。 -- 绑定变量 select * from player where player_id = :player_id; -- 在第一次查询后，在共享池中就会存在这类的查询计划；库缓存的步骤会决定进行软解析。
MySQL中的SQL执行方法
MySQL 的分层
- MySQL用 Client/Server 架构，服务器端是使用 mysqld
组成层
- 连接层：客户端和服务器端建立连接，客户端发送SQL至服务端
- SQL层：对SQL语句进行查询处理
- 存储引擎层：与数据库文件通信，负责数据的读写。
- 磁盘
- 内存
- 网络
SQL层的执行逻辑
MySQL 的 SQL 语句执行步骤
- 查询缓存：8.0后无使用。Server若在缓存查询中找到此SQL语句，直接返回结果；反之，进入解析器阶段。
- 解析器：对SQL语句进行语法分析、语义分析。
- 优化器：在优化器中确定SQL语句的执行路径，像全表检索、索引检索等等。
- 执行器：检查用户权限、若具备权限则返回结果。8.0版本在这步骤会将结果放入查询缓存。
- 查询缓存在8.0后关闭的原因：一旦数据表有变更，缓存都将清空。因此只有数据表是静态或很少变化时，缓存查询才有价值。如果碰到经常更新的情况，反而增加了查询时间。
MySQL各热门存储引擎
- InnoDB：5.5版本后的默认存储引擎。支持食物、行级锁定、外键约束
- MyISAM：在5.5版本前的默认存储引擎。不支持事物、不支持外键。优点为速度快、占用资源少
- Memory：使用系统内存为存储介质，获得更快响应速度。不足是，若mysqld进程崩溃，所有数据将丢失。如果使用场景是当数据是临时的情况下才会使用。
- NDB：NDB cluster，主要用于 MySQL cluster分布式集群环境，类似于 Oracle 的RAC集群。
- Archive：良好的压缩机制，用于文件归档
- 一个数据库中，每个表都能采用不同存储引擎。根据实际的数据处理需要来选择存储引擎。
查看一条 SQL 语句的执行情况
- profiling，开启此功能可以让 MySQL 收集在 SQL 执行时所使用的资源情况
-- 查看 profiling 是否开启 select @@profiling; -- 0代表关闭，1代表开启 -- 开启 profiling set profiling=1; - 查看 profile
show profiles; -- 查看当前会话所产生的所有 profiles（query_id、duration、query） show profile； -- 直接获取上一次查询的执行时间 -- 这里会列出执行 SQL 语句的每个步骤的耗时 -- 或者直接查看某一指定 query id show profile for query 2;
