乐趣区

关于mysql:SQL是如何在数据库中执行的

jiezi

对很多开发者来说，数据库就是个黑盒子，你会写 SQL，会用数据库，但不晓得盒子外面到底是怎么一回事儿，这样你只能机械地去记住他人通知你的那些优化规定，却不晓得为什么要遵循这些规定，也就谈不上灵活运用。

数据库的服务端，能够划分为 执行器 (Execution Engine) 和 存储引擎 (Storage Engine) 两局部。

  • 执行器负责解析 SQL 执行查问
  • 存储引擎负责保留数据。

SQL 是如何在执行器中执行的？

咱们通过一个例子来看一下，执行器是如何来解析执行一条 SQL 的。

  • 这个 SQL 语义是，查问用户 ID 大于 50 的用户的所有订单，这是很简略的一个联查，须要查问 users 和 orders 两张表，WHERE 条件就是，用户 ID 大于 50。

数据库收到查问申请后，须要先解析 SQL 语句，把这一串文本解析成便于程序处理的结构化数据:

  • 转换后的结构化数据，就是一棵树，这个树的名字叫形象语法树（AST，Abstract Syntax Tree）。下面这个 SQL，它的 AST 大略是这样的：

这个树太简单，我只画了次要的局部，你大抵看一下，能了解这个 SQL 的语法树长什么样就行了。执行器解析这个 AST 之后，会生成一个逻辑执行打算。所谓的执行打算，能够简略了解为如何一步一步地执行查问和计算，最终失去执行后果的一个分步骤的打算。这个逻辑执行打算是这样的：

和 SQL、AST 不同的是，这个逻辑执行打算曾经很像能够执行的程序代码了。你看下面这个执行打算，很像咱们编程语言的函数调用栈，外层的办法调用内层的办法。所以，要了解这个执行打算，得从内往外看。

  1. 最内层的 2 个 LogicalTableScan 的含意是，把 USERS 和 ORDERS 这两个表的数据都读出来。
  2. 而后拿这两个表所有数据做一个 LogicalJoin，JOIN 的条件就是第 0 列 (u.id) 等于第 6 列 (o.user_id)。
  3. 而后再执行一个 LogicalFilter 过滤器，过滤条件是第 0 列 (u.id) 大于 50。
  4. 最初，做一个 LogicalProject 投影，只保留第 0(user_id)、1(user_name)、5(order_id) 三列。这里“投影 (Project)”的意思是，把不须要的列过滤掉。

把这个逻辑执行打算翻译成代码，而后依照程序执行，就能够正确地查问出数据了。然而，依照下面那个执行打算，须要执行 2 个全表扫描，而后再把 2 个表的所有数据做一个 JOIN 操作，这个性能是十分十分差的。

优化的总体思路是，在执行打算中，尽早地缩小必须解决的数据量。也就是说，尽量在执行打算的最内层缩小须要解决的数据量。看一下简略优化后的逻辑执行打算：

比照原始的逻辑执行打算，这里咱们做了两点简略的优化：

  • 尽早地执行投影，去除不须要的列；
  • 尽早地执行数据过滤，去除不须要的行。

到这里，执行器只是在逻辑层面剖析 SQL，优化查问的执行逻辑，咱们执行打算中操作的数据，依然是表、行和列。在数据库中，表、行、列都是逻辑概念，所以，这个执行打算叫“逻辑执行打算”。执行查问接下来的局部，就须要波及到数据库的物理存储构造了。

转 SQL 是如何存在存储引擎中执行的？

数据真正存储的时候，无论在磁盘里，还是在内存中，都没法间接存储这种带有行列的二维表。数据库中的二维表，实际上是怎么存储的呢？这就是存储引擎负责解决的问题，存储引擎次要性能就是把逻辑的表行列，用适合的物理存储构造保留到文件中。不同的数据库，它们的物理存储构造是齐全不一样的，这也是各种数据库之间微小性能差距的根本原因。

在 InnoDB 中，数据表的物理存储构造是以主键为关键字的 B+ 树，每一行数据间接就保留在 B+ 树的叶子节点上。比方，下面的订单表组织成 B+ 树，是这个样的：

  • 在 InnoDB 中，表的索引也是以 B+ 树的形式来存储的，和存储数据的 B+ 树的区别是，在索引树中，叶子节点保留的不是行数据，而是行的主键值。
  • 如果通过索引来检索一条记录，须要先后查问索引树和数据树这两棵树：先在索引树中检索到行记录的主键值，而后再用主键值去数据树中去查找这一行数据。

优化后的逻辑执行打算将会被转换成物理执行打算，物理执行打算是和数据的物理存储构造相干的。还是用 InnoDB 来举例，间接将逻辑执行打算转换为物理执行打算：

物理执行打算同样能够依据数据的物理存储构造、是否存在索引以及数据多少等各种因素进行优化。这一块儿的优化规定同样是非常复杂的，比方，咱们能够把对用户树的全树扫描再依照主键过滤这两个步骤，优化为对树的范畴查找

最终，依照优化后的物理执行打算，一步一步地去执行查找和计算，就能够失去 SQL 的查问后果了。

了解数据库执行 SQL 的过程，以及不同存储引擎中的数据和索引的物理存储构造，对于正确应用和优化 SQL 十分有帮忙:

  • 为什么主键不能太长？

因为表的每个索引保留的都是主键的值，过长的主键会导致每一个索引都很大。

  • 有的时候明明有索引却不能命中的起因是?

数据库在对物理执行打算优化的时候，评估发现不走索引，间接全表扫描是更优的抉择。

退出移动版