阅读文本大概需要3分钟。

数据库如何解析执行SQL_SQL

 

0x01：数据库客户端发送查询SQL

      客户端将查询的select sql，按照mysql通信协议传输到数据库服务。数据库服务接受查询sql，执行sql前判断要执行的sql是否是查询语句。

 

0x02：查询缓存先行       MySQL在开启查询缓存的情况下，首先会先在查询缓存中查找该SQL是否完全匹配，如果完全匹配，验证当前用户是否具备查询权限，如果权限验证通过，直接返回结果集给客户端，该查询也就完成了。如果不匹配继续向下执行。

缓存配置参数：

数据库如何解析执行SQL_SQL_02

  • query_cache_limit: MySQL能够缓存的最大结果，如果超出，则增加 Qcache_not_cached的值，并删除查询结果

  • query_cache_min_res_unit: 分配内存块时的最小单位大小

  • query_cache_size: 缓存使用的总内存空间大小，单位是字节，这个值必须是1024的整数倍，否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)

  • query_cache_type: 是否打开缓存 OFF 关闭 ；ON 总是打开

  • query_cache_wlock_invalidate: 如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回

 

0x03：语法分析器和查询预处理器       如果在查询缓存中未匹配成功，则将语句交给分析器作语法分析。MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询，通过分析语法知道要查的内容。这个步骤会对语法进行检验，如果语法不对就会返回语法错误中断查询。否则，下一步预处理器会验证权限。

 

0x04：查询优化器

      语法树被认为合法之后，由优化器将其转化为执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

      MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次where条件比较的成本。可以通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。

有很多种原因会导致MySQL优化器选择错误的执行计划，比如：

1. 统计信息不准确。

2. 执行计划中的成本估算不等同于实际的执行计划的成本。

3. MySQL的最优可能与你想的最优不一样。

4. MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度。

5. MySQL也不是任何时候都是基于成本的优化，有时候也会基于一些固定的规则。

6. MySQL不会考虑不受其控制的成本，例如执行存储过程或者用户自定义的函数的成本。

      MySQL的查询优化使用了很多优化策略来生成一个最优的执行的计划。优化策略可以分为两种，静态优化和动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如优化器可以通过一些简单的代数变换将where条件转换成另一种等价形式。静态优化不依赖于特别的数值，如where条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复查询也不会变化，可以认为是一种“编译时优化”。

      相反，动态优化则和查询的上下文有关。也可能和很多其他因素有关，例如where条件中的取值、索引中条目对应的数据行数等，这些需要每次查询的时候重新评估，可以认为是“运行时优化”。

下面是一些MySQL能够处理的优化类型：

1. 重新定义关联表的顺序

      数据表的关联并不总是按照在查询中指定的顺序进行，决定关联的顺序是优化器很重要的一部分功能。

2. 将外连接转化成内连接

       并不是所有的outer join语句都必须以外连接的方式执行。诸多因素，例如where条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询，让其可以调整关联顺序。

3. 使用等价变换规则

       MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如：（5=5 and a>5）将被改写为a>5。类似的，如果有（a<b and b=c）and a=5，则会被改写为 b>5 and b=c and a=5。

4. 优化count()、min()和max()

      索引和列是否为空通常可以帮助MySQL优化这类表达式。例如，要找到一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会讲这个表达式最为一个常数对待。类似的，如果要查找一个最大值，也只需要读取B-tree索引的最后一个记录。如果MySQL使用了这种类型的优化，那么在explain中就可以看到“select tables optimized away”。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。

       类似的，没有任何where条件的count(*)查询通常也可以使用存储引擎提供的一些优化，例如，MyISAM维护了一个变量来存放数据表的行数。

5. 预估并转化为常数表达式

6. 覆盖索引扫描

       当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。

7. 子查询优化

      MySQL在某些情况下可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。

8. 提前终止查询

      在发现已经满足查询需求的时候，MySQL总是能够立即终止查询。一个典型的例子就是当使用了limit子句的时候。除此之外，MySQL还有几种情况也会提前终止查询，例如发现了一个不成立的条件，这时MySQL可以立即返回一个空结果。

数据库如何解析执行SQL_SQL_03

上面的例子可以看出，查询在优化阶段就已经终止。

9. 等值传播

10. 列表in()的比较

      在很多数据库系统中，in()完全等同于多个or条件的字句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将in()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个o(log n)复杂度的操作，等价转换成or的查询的复杂度为o(n)，对于in()列表中有大量取值的时候，MySQL的处理速度会更快。


0x05：查询执行器

       在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。

       相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口就是我们称为“handler API”的接口。实际上，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等。

     最后，交给执行器去具体执行该查询语句。执行器开始执行后，会逐渐将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

 

数据库如何解析执行SQL_SQL_04