【MySQL】2-深入理解MySQL体系认识及SQL的执行

蔚落 2022-12-29 09:06 93阅读 0赞

### 文章目录 ###

*  MySQL体系认识及SQL的执行
 *   *  MySQL体系结构
     *   *  client connectors
         *  Connection Pool
         *  SQL Interface
         *  Parser
         *  Optimizer
         *  文件系统
         *  Cache
         *   *  工作原理
             *  缓存配置
             *  限制条件
     *  各存储引擎
     *   *  CSV引擎【表格存储】
         *  Archive存储引擎【压缩协议】
         *  Memory存储引擎【存储内存中、热点数据】
         *  Myisam【8.0淡出了历史舞台】
         *  Innodb
     *  SQL执行过程
     *  执行计划
     *   *  查看执行计划
         *  深入理解执行计划
         *   *  执行计划-id【SQL执行的顺序】
             *  执行计划-select\_type【执行类型】
             *  执行计划-Table【数据表】
             *  执行计划-type
             *  partitions【分区信息】
             *  possible key【可能用到的索引】
             *  key【真正用到的索引】
             *  执行计划-key\_len【索引的长度】
             *  rows
             *  filtered
         *  数据返回

# MySQL体系认识及SQL的执行 #

## MySQL体系结构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70]

### client connectors ###

*  各个语言连接数据库的方式
 *  如：JDBC、ODBC、.NET

### Connection Pool ###

*  整个接入的连接池
 *  连接的处理、安全和用户名的管控、线程的处理

### SQL Interface ###

*  SQL 语句的入门，负责接收用户的 SQL 语句，返回结果

### Parser ###

*  进行词法和语法的分析，解析成MySQL能够识别的语法

### Optimizer ###

*  查询优化器，用来查询优化的，执行最优的执行计划
 *  找到执行SQL语句的最优执行计划
 *  物理优化、逻辑优化

### 文件系统 ###

*  数据落盘
 *  日志

### Cache ###

*  负责存储MySQL查询的索引

#### 工作原理 ####

将SQL语句以及执行的结果做为K-V键值对缓存在caches模块中，后续基于接收的SQL语句，先去查询缓存,判断是否存在可用的记录集

#### 缓存配置 ####

*  query\_cache\_type 设置我似查询缓存开关
 *  query\_cache\_size
 *  query\_cache\_limit

#### 限制条件 ####

*  缓存失效与表数据是一致的，表数据发生任何改动都将让缓存失效
 *  需要手工开启，在MySQL5.6以后默认关闭了查询缓存功能
 *  判断条件苛刻，SQL语句必须完全一致
    
     *  多个空格也不可以
     *  将传进来的语句作为Key，每次必须要和Key相等才可以
 *  带来额外的性能消耗，数据的返回过程中若开启了缓存需先缓存SQL和结果

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 1]

## 各存储引擎 ##

*  存储引擎用于修饰表，一个表只能有一个存储引擎
 *  默认的存储引擎 **Innodb**
 *  存储引擎是插拔式的，可以随时进行加载和卸载

### CSV引擎【表格存储】 ###

特点

*  不能定义索引、列定义必须定义NOT NULL、不能设置自增列
 *  CSV表的数据的存储格式用 “,” 隔开，可直接编辑文件进行数据的编排
 *  **数据安全性低**  
    应用场景
 *  不适用大表和数据的在线处理
 *  数据的快速导入导出
 *  表格直接转换成CSV

### Archive存储引擎【压缩协议】 ###

**特点**

*  压缩协议进行数据的压缩，占用磁盘的空间少
 *  只支持 insert 和 select 两种操作
 *  只允许自增ID列建立索引

**应用场景**

*  数据备份系统（日志系统、文档归档）
 *  大量设备高频的数据采集

### Memory存储引擎【存储内存中、热点数据】 ###

**特点**

*  数据都是存储在内存中，处理效率高，表**大小限定默认16M**
 *  不支持大数据存储类型的字段如 blog、text varchar(n)【可变的】 – > char(n)【不可变的】
 *  支持Hash索引，等值查询效率高
 *  数据的可靠性低，重启或系统崩溃时数据丢失

**应用场景**

*  热点数据快速加载（功能类似缓存中间件）
 *  MySQL临时表存储（查询结果于内存中计算数据）

### Myisam【8.0淡出了历史舞台】 ###

**特点**

*  较快的数据插入和读取性能
 *  数据存储既有较小的磁盘空间占用
 *  **支持表级别的锁，不支持事务**
 *  **数据文件与索引文件分开存储，无主键索引之分**

**应用场景**

*  只读应用或者以读为主的业务

### Innodb ###

**特点**

*  支持事务【ACID ： 原子性、隔离性、一致性、持久性】
 *  行级锁
 *  聚集索引【主键索引】
 *  外键支持，保证数据的完成性【带来额外的性能开销】

**应用场景**

*  无脑选择

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 2]

## SQL执行过程 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 3]  
**DQL语句的执行**

*  由我们的 SQL Interface 拦截到用户的 DQL 语句
 *  去我们的缓存中查看是否存在
 *  不存在，则进入我们解析器、优化器、执行器，最后交给我们的存储引擎

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 4]  
**通讯阶段**

*  通讯协议：TCP/IP，Unix Socket
 *  通讯方式：长连接、半双工
 *  思考：若一个SQL语句发出去很久没有回应怎么办？【show processlist / kill 18(杀死某个端口)】

## 执行计划 ##

### 查看执行计划 ###

*  explain/desc SQL：查看生成的最优的执行计划
 *  set global optimizer\_trace=‘enable=on’：打开记录SQL的执行计划开关【以一个json记录至optimizer\_trace】

### 深入理解执行计划 ###

#### 执行计划-id【SQL执行的顺序】 ####

*  id 相同：执行顺序由上到下
 *  id 不同：id 值越大的优先级越高，优先被执行

#### 执行计划-select\_type【执行类型】 ####

*  Simlpe：简单查询
 *  PRIMARY：最外层查询
 *  SUBQUERY：子查询
 *  UNION：连接查询
 *  UNION RESULT：连接查询的结果集

#### 执行计划-Table【数据表】 ####

*  查询涉及到的表或者表的别名

#### 执行计划-type ####

SQL执行数据的获取方式  
SQL执行优化中一个很重要的指标，评测SQL好坏最直观的参数

取值：

*  system
 *  const：唯一性索引、常量比较
 *  eq\_ref：唯一性索引扫描
 *  ref：普通索引扫描
 *  range：基于索引的范围查找
 *  index：full index scan【基于全量的一个扫描】
 *  all：full all scan【基于全量的一个扫描】

#### partitions【分区信息】 ####

#### possible key【可能用到的索引】 ####

#### key【真正用到的索引】 ####

#### 执行计划-key\_len【索引的长度】 ####

*  索引使用的包括列长度

#### rows ####

*  根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
 *  越少越好

#### filtered ####

*  指返回结果的行占需要读取的行（rows）的百分比
 *  越大越好，扫描的数据的准确性越高

### 数据返回 ###

*  配置了MySQL的缓存机制，执行缓存操作
 *  SQL执行返回结果以增量的返回方法进行返回
    
     *  开始生成第一条结果时，MySQL就开始往请求方逐步返回数据
     *  MySQL服务器无须保存过多的数据，浪费内存
     *  用户体验好，若无须等待所有数据可将拿到的数据展示

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70]: /images/20221120/e94dc2d016a7436894994133ae9affab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 1]: /images/20221120/3081383777e54d6d93597dcd923f558e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 2]: /images/20221120/4d58a8512b69484f87ee25826554710c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 3]: /images/20221120/846048d6d3104b0c84f6586429bd7ebf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 4]: /images/20221120/a7b5d85163734a0fad8442da4eabe92e.png