一条查询的SQL语句到底怎么执行的?

时间:2020-11-21 15:30:00 来源:互联网 作者: 神秘的大神 字体:

1. 一条查询的SQL语句是如何执行的?

抛出问题:我们平时操作MySQL数据库,大多数都是使用Navicat连接远程服务器上的数据库,编写一条语句,例如:select name from user where id=1 ,然后就会返回结果,但是有没有思考过,当我们的工具或者程序连接到数据库之后,实际上发生了什么事情?它的内部是如何工作的?

其实一条查询的语句的执行流程分为以下几步:

 

 

 待我一步步解释:

1.1、查询缓存

    ①、MySQL数据库自带的。

    ②、存储形式是以KV对进行存储的,例如Reids,为了就是加快数据的查询效能。较少服务器的处理时间。

    ③、默认关闭的(不推荐使用)。查询缓存是否开启语句: show variables like 'query_cache%';

           原因:例如:user表里面有100万数据,但是没索引,其中查询语句为:select * from user where name='Kelly' ,第一次用时为5秒,那么执行同样的语句第二次会变快嘛?答案:不会,因为查询缓存时默认关闭的,可以使用语句:show variables like 'query_cache%';进行               查看,开启后查询会变快嘛,结果会变快。

           为什么不推荐使用查询缓存:主要还是自带的查询缓存太过于鸡肋,使用的场景有限。

           a、第一个是它要求 SQL 语句必须一模一样,中间多一个空格,字母大小写不同都被认为是不同的的 SQL。

           b、表里面任何一条数据发生变化的时候,这张表所有缓存都会失效,所以对于有大量数据更新的应用,也不适合。

           c、专门做专事,缓存还是交给 ORM 框架(比如 MyBatis 默认开启了一级缓存),或者独立的缓存服务,比如 Redis 来处理更合适。

    ④、8.0版本查询缓存被移除掉了。

1.2、解析器

         解析器:主要做的事情是对语句基于 SQL 语法进行词法和语法分析和语义的解析。例如:select * from user where name='Kelly' 可以执行,但是如果是:slect * form user whree name='Kelly',就会提示你SQL语句报错,这就是解析器做的事情。

         ①、语法分析:select name from user where id=88 ,这条sql语句就会被拆分为8个符号,每个符合什么类型,从哪里开始到哪里结束等。例如数据结构中的:链表。

         ②、语义分析:语法分析会对 SQL 做一些语法检查,比如单引号有没有闭合,然后根据 MySQL 定义的语法规则,根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树(select_lex)。

                 

 

                任何数据库的中间件,比如 Mycat,Sharding-JDBC(用到了 Druid Parser),都必须要有词法和语法分析功能,在市面上也有很多的开源的词法解析的工具(比如 LEX,Yacc)。

 

 1.3、预处理器

          ①、解释器是分析语法有没有错误,但是它无法知道数据库中有没有数据表和字段,例如:select name from user1 where id=88,这条语句语法没问题,但是执行依然报错,这就是预处理器做的活,因为数据库中没有user1这张表,只有user表。解析 SQL 的环节里面                     就会用到这个预处理器。

          ②、它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。

          ③、预处理之后得到一个新的解析树。

 1.4、查询优化器

         ①、什么是优化器?

                得到解析树之后,是不是执行 SQL 语句了呢?

                这里我们有一个问题,一条 SQL 语句是不是只有一种执行方式?

                或者说数据库最终 执行的 SQL 是不是就是我们发送的 SQL?

                这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的,最终返回相同的结果,他们是等价的。但是如果有这么多种执行方式,这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?

                这个就是 MySQL 的查询优化器的模块(Optimizer)。             

               查询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划,MySQL 里面使用的是基于开销(cost)的优化器,那种执行计划开销最小,就用哪种。

               可以使用这个命令查看查询的开销:show status like 'Last_query_cost';

        ②、优化器可以做什么?

               MySQL 的优化器能处理哪些优化类型呢?

              举两个简单的例子:             

              a、当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表。           

              b、有多个索引可以使用的时候,选择哪个索引。

              实际上,对于每一种数据库来说,优化器的模块都是必不可少的,他们通过复杂的算法实现尽可能优化查询效率的目标。

             但是优化器也不是万能的,并不是再垃圾的 SQL 语句都能自动优化,也不是每次都能选择到最优的执行计划,大家在编写 SQL 语句的时候还是要注意。

       ③、优化器是怎么得到执行计划的?

             首先我们要启用优化器的追踪(默认是关闭的):SHOW VARIABLES LIKE 'optimizer_trace'; set optimizer_trace='enabled=on';

             注意开启这开关是会消耗性能的,因为它要把优化分析的结果写到表里面,所以不要轻易开启,或者查看完之后关闭它(改成 off)。

             接着我们执行一个 SQL 语句,优化器会生成执行计划:select u.id  from user u,user_config uc where u.id=uc.id           

             这个时候优化器分析的过程已经记录到系统表里面了,我们可以查询:

             select * from information_schema.optimizer_trace\G

            它是一个 JSON 类型的数据,主要分成三部分,准备阶段、优化阶段和执行阶段。

            

 

 

           expanded_query 是优化后的 SQL 语句。

           considered_execution_plans 里面列出了所有的执行计划。

           分析完记得关掉它:set optimizer_trace="enabled=off"; SHOW VARIABLES LIKE 'optimizer_trace';

 1.5、执行计划

          优化器完之后,得到一个什么东西呢?

          优化器最终会把解析树变成一个查询执行计划,查询执行计划是一个数据结构。

          当然,这个执行计划是不是一定是最优的执行计划呢?

          不一定,因为 MySQL 也有可能覆盖不到所有的执行计划。

          我们怎么查看 MySQL 的执行计划呢?比如多张表关联查询,先查询哪张表?在执行查询的时候可能用到哪些索引,实际上用到了什么索引?

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

          例如:EXPLAIN select name from user where id=1;

 1.6、存储引擎

          得到执行计划以后,SQL 语句是不是终于可以执行了?

         问题又来了:

         a、从逻辑的角度来说,我们的数据是放在哪里的,或者说放在一个什么结构里面?
         b、执行计划在哪里执行?是谁去执行?
        ①、 存储引擎基本介绍
              我们先回答第一个问题:在关系型数据库里面,数据是放在什么结构里面的?
           (放在表 Table 里面的)
             我们可以把这个表理解成 Excel 电子表格的形式。所以我们的表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。
             在 MySQL 里面,支持多种存储引擎,他们是可以替换的,所以叫做插件式的存储引擎。为什么要搞这么多存储引擎呢?一种还不够用吗?
             这个问题先留着。

        ②、查看存储引擎

               比如我们数据库里面已经存在的表,我们怎么查看它们的存储引擎呢?

               show table status from `plaso1`;

               

 

              或者通过 DDL 建表语句来查看。

              在 MySQL 里面,我们创建的每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且,创建表之后还可以修改存储引擎。我们说一张表使用的存储引擎决定我们存储数据的结构,那在服务器上它们是                 怎么存储的呢?我们先要找到数据库存放数据的路径:

             show variables like 'datadir';

 

 

 

            默认情况下,每个数据库有一个自己文件夹,以 plaso1 数据库为例

            任何一个存储引擎都有一个 frm 文件,这个是表结构定义文件。

            

 

 

 

           不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb 是 1 个,memory 没有,myisam 是两个。这些存储引擎的差别在哪呢?
     ③、存储引擎比较

           MyISAM 和 InnoDB 是我们用得最多的两个存储引擎,在 MySQL 5.5 版本之前,默认的存储引擎是 MyISAM,它是 MySQL 自带的。我们创建表的时候不指定存储引擎,它就会使用 MyISAM 作为存储引擎。   

           MyISAM 的前身是 ISAM(Indexed Sequential Access Method:利用索引,顺序存取数据的方法)。5.5 版本之后默认的存储引擎改成了 InnoDB,它是第三方公司为 MySQL 开发的。
           为什么要改呢?最主要的原因还是 InnoDB 支持事务,支持行级别的锁,对于业务一致性要求高的场景来说更适合。

           那么除了这两个我们最熟悉的存储引擎,数据库还支持其他哪些常用的存储引擎呢?

           我们可以用这个命令查看数据库对存储引擎的支持情况

           show engines ;

           其中有存储引擎的描述和对事务、XA 协议和 Savepoints 的支持。

           XA 协议用来实现分布式事务(分为本地资源管理器,事务管理器)。
          Savepoints 用来实现子事务(嵌套事务)。创建了一个 Savepoints 之后,事务就可以回滚到这个点,不会影响到创建 Savepoints 之前的操作。

            

 

 

 

             这些数据库支持的存储引擎,分别有什么特性呢?
             MyISAM ( 3 个文件)
             应用范围比较小。表级锁定限制了读/写的性能,因此在 Web 和数据仓库配置中,它通常用于只读或以读为主的工作。
             特点:
             支持表级别的锁(插入和更新会锁表)。不支持事务。
             拥有较高的插入(insert)和查询(select)速度。
             存储了表的行数(count 速度更快)。
           (怎么快速向数据库插入 100 万条数据?我们有一种先用 MyISAM 插入数据,然后修改存储引擎为 InnoDB 的操作。)
              适合:只读之类的数据分析的项目。
              InnoDB (2 个文件)
              mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全(与 ACID 兼容)的 MySQL存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁(不升级为更粗粒度的锁)和 Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户                数据存储在聚集索引中,以减少基于主键的常见查询的 I/O。为了保持数据完整性,InnoDB 还支持外键引用完整性约束。
              特点:
              支持事务,支持外键,因此数据的完整性、一致性更高。
              支持行级别的锁和表级别的锁。
              支持读写并发,写不阻塞读(MVCC)。
              特殊的索引存放方式,可以减少 IO,提升查询效率。
              适合:经常更新的表,存在并发读写或者有事务处理的业务系统。
              Memory (1  个文件)
              将所有数据存储在 RAM 中,以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少;InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中,而 ndbcluster 为大型分布式数                  据集提供了快速的键值查找。
              特点:
              把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失。只适合做临时表。将表中的数据存储到内存中。
              CSV (3 个文件)
              它的表实际上是带有逗号分隔值的文本文件。csv表允许以csv格式导入或转储数据,以便与读写相同格式的脚本和应用程序交换数据。因为 csv 表没有索引,所以通常在正常操作期间将数据保存在 innodb 表中,并且只在导入或导出阶段使用 csv 表。
              特点:

              不允许空行,不支持索引。格式通用,可以直接编辑,适合在不同数据库之间导入导出

              Archive (2 个文件)
              这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。
              特点:

              不支持索引,不支持 update delete。
              这是 MySQL 里面常见的一些存储引擎,我们看到了,不同的存储引擎提供的特性都不一样,它们有不同的存储机制、索引方式、锁定水平等功能。
              我们在不同的业务场景中对数据操作的要求不同,就可以选择不同的存储引擎来满 足我们的需求,这个就是 MySQL 支持这么多存储引擎的原因。
        ④、 如何选择存储引擎?
                如果对数据一致性要求比较高,需要事务支持,可以选择 InnoDB。
                如果数据查询多更新少,对查询性能要求比较高,可以选择 MyISAM。
                如果需要一个用于查询的临时表,可以选择 Memory。
                如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部手册用 C 语言开发一个存储引擎:
. 1.7、 执行引擎(y Query n Execution Engine ),返回结果
            OK,存储引擎分析完了,它是我们存储数据的形式,继续第二个问题,是谁使用执行计划去操作存储引擎呢?
             这就是我们的执行引擎,它利用存储引擎提供的相应的 API 来完成操作。
             为什么我们修改了表的存储引擎,操作方式不需要做任何改变?因为不同功能的存储引擎实现的 API 是相同的。最后把数据返回给客户端,即使没有结果也要返回。