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

 

心灵毒鸡汤:只有一条路不能选择，那就是放弃的路；只有一条路不能拒绝，那就是成长的路。

 

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 是相同的。最后把数据返回给客户端，即使没有结果也要返回。