mysql select 语句执行流程

发表于

mysql 官方说明文档请参考: 官方说明文档.

mysql sql 执行流程图

在这里插入图片描述

建立连接

mysql 服务端默认监听的端口是 3306，客户端可以使用不同的协议和服务端建立连接（常见的协议有:TCP、Unix socket 等）。

查看连接详情
  • 命令
1
sql复制代码show global status like 'Thread%';
  • 查询结果以及解释
字段 含义
Threads cached 缓存中的线程连接数
Threads connected 当前打开的连接数
Threads created 为处理连接创建的线程数
Threads running 非睡眠状态的连接数，通常指并发连接数

客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理，故可以通过线程来看连接情况。
几个线程的详细介绍可以参考:Mysql查看状态，连接数，线程数以及Mysql性能监控工具doDBA的使用以及优化.

连接关键参数
  • 超时时间，默认都是28800秒，8小时
1
2
3
4
sql复制代码--非交互式超时时间，如 JDBC 程序
show global variables like 'wait timeout';
--交互式超时时间，如数据库工具
show global variables like 'interactive timeout';
  • 最大连接数：在5.7版本中默认是151个，最大可以设置成100000
1
sql复制代码show variables like 'max connections';

查询缓存

mysql 查询缓存查询功能比较鸡肋，在 5.7 版本中默认是关闭的，8.+ 版本以后已经被弃用，主要原因有如下两点:

  • 第一个是它要求SQL语句必须一 模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。
  • 第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对 于有大量数据更新的应用，不适合。

解析器

语法解析

将sql语句拆分成一个一个的单词

词法解析（Parser）

对SQL做一些语法检查，比如单引号有没有闭合, 然后根据MySQL定义的语法规则，根据SQL语句生成解析树，如下图所示：
在这里插入图片描述

预处理器（Preprocessor）

预处理器会检査生成的解析树，解决解析器无法解析的语义的问题。比如，它会检査表和列名是否存在，检査名字和别名，保证没有歧义。

预处理器处理的结果是得到一棵新的解析树。

优化器(Optimizer)

一条SQL语句可以有很多种执行方式，最终返回相同的结果，他们是等价的。优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选 择一种最优的执行计划，MySQL里面使用的是基于开销(cost)的优化器，选择开销最小的一种执行(有时候不能光从sql判断查询是否走索引也是这个原因)。

  • 可以使用这个命令査看査询的开销：
1
sql复制代码show status like 'Last query cost';

优化器处理后主要得到执行计划。

MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划，例如：

1
sql复制代码EXPLAIN select name from user where id=1;

存储引擎

存储引擎主要负责数据的存储
不同的存储引擎提供了统一标准的api接口，供执行器操作。所以 mysql 的存存储引擎是动语选择的。

存储引擎存放文件详情
  • 查看mysql的数据存放路径(每个数据库是一个文件夹)
1
sql复制代码show variables like 'datadir';

不同的存储引擎文件的存储形式如下：
在这里插入图片描述

  • 不同的存储引擎存放文件说明
    • frm 存放的是表结构定义的文件，每个存储引擎都有
    • 不同的存储引擎存放数据的方式不一样，产生的文件也不一样
      • innodb只有一个数据文件
      • memory是放在内存中，故磁盘没有文件
      • myisam 是两个文件，table_name.MYD 存放数据，table_name.MYI存放索引
各个存储引擎之间的对比
  • MylSAM （3个文件）
    应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。
    特点:
    • 支持表级别的锁（插入和更新会锁表）。
    • 不支持事务。拥有较高的插入（insert）和查询（select）速度。
    • 存储了表的行数（count速度更快）。
      怎么快速向数据库插入100万条数据？
      有一种先用MylSAM插入数据,然后修改存储引擎为InnoDB的操作。）
      适合：只读之类的数据分析的项目。

-InnoDB （2个文件）
mysql 5.7中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级 为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将 用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性， InnoDB还支持外键引用完整性约束。
特点：

  • 支持事务
  • 支持外键，因此数据的完整性、一致性更高。
  • 支持行级别的锁和表级别的锁
  • 支持读写并发写不阻塞读（MVCC）
  • 特殊的索引存放方式，可以减少I/O,提升査询效率

详情请参考:dev.mysql.com/doc/refman/…
在这里插入图片描述
5.5.5之后，mysql默认的存储引擎是InnoDB

执行器(执行引擎)

执行引擎，利用存储引擎提供的相应的API来完成操作,将存储引擎返回的处理的数据返回给客户端

总结

MySQL分成三层:跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层。

  • 连接层
    我们的客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。
  • 服务层
    连接层会把SQL语句交给服务层，这里面又包含一系列的流程：比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语 法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。然后就是优化器，MySQL底层会根据一定的规则对我们的SQL语句进行优化,最后再交给执行器去执行。
  • 存储引擎
    存储引擎就是我们的数据真正存放的地方，在MySQL里面支持不同的存储弓I擎。再往下就是内存或者磁盘。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%