首页 > > 网络编程 > Mysql >

从集合的无序性看待关系型数据库中的"…

2018-06-18 01:13:04来源：未知阅读 ()

1.集合的特征

关系型数据库，一方面它是数据库，可以存储数据，另一方面，它是关系的，也就是基于关系模型的。在关系型数据库中，专门为关系模型设计了对应的"关系引擎"，关系引擎中包含了语句分析器、优化器、查询执行器。语句分析器用于分析语句是否正确，优化器用于生成查询的执行计划，查询执行器按照优化器生成的执行计划去执行查询操作，并将相关操作指令交给存储引擎，由存储引擎跟底层的数据(磁盘/缓存)打交道。

这里我们不谈数据存储，而是站在数据库的角度上谈关系模型的一个特性：基于集合理论。

高中数学里，我们都学过集合有三个特征：确定性、互异性和无序性。其中确定性和互异性是成为集合的条件，无序性是集合的特性。

确定性指的是集合中的元素必须是明确的，不能在集合中存放一个可能大于2，也可能大于3这样的元素。在关系型数据库中，这一特征可以不用考虑，因为数据只要存到数据库中，数据就一定是确定的。

互异性是指集合中的元素不能重复。在关系型数据库中，记录是否重复的问题没有严格的规范。一方面，各种各样的业务逻辑不允许在数据库的角度上严格限制记录的重复性。另一方面，可以通过设置主键或者唯一索引来保证记录之间不重复，也就是"唯一性"。在很多情况下，具有唯一性的表能优化查询，减少记录的检索次数。

无序性是指集合中的元素之间是无序的。在关系型数据库中，对集合的无序性实现的最完整。甚至可以说，无序性贯穿了整个关系型数据库，尤其是关系引擎中的优化器更是关注"序"这个概念。

因此，本文主要围绕集合的"序"，来解释关系型数据库中和"序"有关的行为。

2.集合的无序性

这里的"序"，不是指数据按大小排过序，也不是指物理存储数据时排过序，而是站在集合的角度或关系引擎的角度(若不理解，就当是数据库角度)上看"数据是否有序"的概念，它是逻辑上的"序"。

举个例子就能理解。集合A(1,2,3,4)和集合B(2,1,4,3)，看上去集合A中元素是有序的，集合B中元素是无序的。

如果要从集合A和集合B中取出小于2的元素，无论是集合A还是集合B都要比较4次才能得到最终结果，因为集合并不知道元素2的后面是否还有比2小的值。而且对于集合来说，根本就没有前面和后面的概念。

也就是说，对于集合A来说，2是第二个元素是错误的说法。集合中不应该有"第"这种说法(数据库也如此，只要检索的对象不是order by后的结果或游标对象，取第几行这种概念将总是按照物理存储顺序去访问的)。

如果我们使用下图的模式去看待这两个集合，也用这种模式去看到数据库表中的记录，很多时候更有助于理解sql语法的本质和sql优化。

所以，这两个集合是等价的集合，只不过这里的集合A，在我们人眼中碰巧有序而已。这也是数据库中索引的意义，我们人为地将集合中的元素排序，人为地告诉优化器它们是有序的，即使关系引擎依旧认为它是无序的。

我们可以站在集合整体的角度上看待"序"这个概念。集合不关注其内部的元素是什么，它只关注它自身是一个容器，包含了一堆满足集合条件的元素。当需要找出集合中某个或某些具体的元素时，需要扫描整个集合。

3.表中记录的无序性

站在数据库层面来讲，关系型数据库表中的数据是无序的，也就是我们俗称的"堆heap"。我们应该这样看待表中的每行记录：

你可能会疑惑，使用(B+树)索引不是可以将表中所有数据排序后存储吗？没错，但在关系引擎看来，这个表仍然是集合的，它是一个乱序的整体，其内每行数据也都认为是无序的。只不过在检索数据的时候，优化器在生成执行计划时能发现已经存在索引，它知道这些数据是根据索引排过序的，藉此生成成本更低的执行计划。

转换成上面集合的说法，索引的意义就是让集合B(2,1,4,3)变成集合A(1,2,3,4)，让集合中的元素能够使用"第几"这种概念。

例如，在集合的层次上要找出值大于2的元素，需要扫描整个集合，即需要比较4次才能得到结果。同理，没有使用索引的表也一样会进行表扫描才能得到最终结果。当使用索引后，也就是人为地将集合B变成集合A，对于我们人来说，只要从前向后找，当找到第一个大于2的元素后(请注意，不是等于2，而是大于2，这两种行为是有区别的)，就知道它后面的所有元素一定是大于2的。对于数据库来说，索引就是我们人为告诉优化器这个表排序过，优化器自然知道只要找到符合条件的记录。

题外话：从这里是否感受到了关系引擎和优化器之间的关系？

对于关系引擎来说，整个表都是无序的。如果没有优化器组件，关系引擎(查询执行器，后文将直接使用关系引擎来表示查询执行器的行为)会进行表扫描，如果没有索引，关系引擎也会进行表扫描。但是有了优化器，且经过"我们的提醒"后，优化器就能决定关系引擎的执行计划：走索引。

另外，既然我们能隐式"提醒"优化器表存在索引，那么我们也能显式"提醒"优化器优化器有别的索引，甚至强制"提醒"优化器没有索引。这就是为什么关系型数据库中都会有"hint"关键字的原因。

绝大多数情况下，我们应该完全信任优化器，相信它能帮我们选出成本最低的执行计划。但优化器有时候也会"聪明反被聪明误"，选出一条不怎么好甚至性能极低的执行计划，这时我们要hint强制干涉，由我们自己告诉优化器应该怎么走索引、走哪条索引。

从这方面看，一个不支持hint功能的数据库系统是不合格的数据库系统，比如老版本的PostgreSQL。当然，在2012年它已经添加了hint的扩展功能。

再来看看无序表联接的问题。当无序表1和无序表2进行内联接、外联接时，我们应该这样看待联接的本质：

这也是站在关系引擎角度上看联接的本质。由于无序，关系引擎只能对两个表都进行表扫描并逐一比对，这样就形成了我们常说的"笛卡尔积"。无疑，这样的效率很低。为了提高联接时的效率，应该尽可能多地减少记录的扫描次数，这是联接语句优化的本质。

虽然总是提到索引，感觉索引能带来性能上的提高，但无论如何请记得，对于关系引擎来说，表是集合的，是无序的，不管它是否有索引，是否人为排序过。至于索引，它是优化器才认识的东西，关系引擎不认识。

4.集合的"序"和物理存储顺序之间的关系

先简单说说数据库是如何存储数据的。

在数据库系统中，表中的所有数据都存储在数据库文件中，这是磁盘上的文件。但在数据库看来，表中的每一行数据都是存放在"页"上的。页是数据库操作的最小单位，例如想读取某一行数据时(假如走索引，不会表扫描)，存储引擎会将这行数据所在的一整页地加载到内存中，并扫描这一页。

数据页中，使用槽位(slot)来记录每一行数据，每个槽表示一行数据。例如，下图是一个页面的大致示意图(不同数据库系统有所不同，但不影响理解)：

这个页面中每插入一行数据，就分配一个槽位，并在槽位图上标记这个槽的位置(比如距离页面顶端的偏移字节是多少)，这样就能知道这一行数据在页中的位置。

而我们所说的"物理存储顺序"就是槽位图标记的顺序。注意，不是页面空间上的前后位置。因为槽位的顺序和页面空间的位置可能是不一致的，例如下图：

在页面的空间位置上，slot2对应的记录行在3的后面，但是扫描这个页面的时候，将先扫描slot2，再扫描slot3。也就是说，物理顺序是slot位图的顺序(或者说，将先返回slot2对应的行)。

在堆表中，slot位图的和页面的空间位置是完全对应的。删除一行数据，这行数据的槽位会保留，只不过槽位图上的偏移会指向0。当插入新数据的时候，这个新数据可能会直接插入到这个槽位中(如果这个槽位装不下这行数据，则会寻找其他的槽位)。

而在有索引的情况下，slot的顺序和页面空间的位置顺序可能不一样，这关乎到索引的类型。例如插入2，它是1和3中间的值，按理说应该插在slot1对应行的后面，但这样会使得slot3向后移动。而这样的设计，可以让数据直接插在页面的尾部，只需要对slot号码重新编号即可。性能要提高不少。

上面说的是单个页内部的数据行顺序问题。除了页内顺序，还有页间的顺序。例如页面2紧跟在页面1的后面，我们称之为"页面是连续的"，但如果页面2在页面1的前面，或者页面1和页面2中间隔了很多其他的页，我们称之为"页面不连续"。在页面不连续的时候，存储引擎需要不断地进行页面跳跃，反映到磁盘上就是需要不断的寻址。而我们知道，机械硬盘花在寻址的时间上远远高于读取数据的时间。这也称之为"页面碎片"，当碎片较多的时候，它会对性能造成极大的影响。

本文不会去详细介绍这些东西。在这里，唯一需要知道的就是"数据存储的物理顺序并不是空间上的前后顺序"。

那么，集合的"序"和物理存储顺序之间有什么关系呢？

在关系引擎看来表中的数据是无序的，但即使无序，数据也已经持久化到磁盘上了。它总要找出一个能扫描所有数据的方案。在不走索引的情况下，优化器无其他路可选，它只能按照物理存储顺序进行表扫描。在这之后，如果没有排序算法对数据进行排序，那么之后所有的操作都按照这个顺序访问数据。

因此，★★★★★★物理存储顺序是无序的起点，是数据随机性的起点。★★★★★★虚拟表之所以无序，就是从这里物理存储顺序开始的，当表扫描(或加载部分页面)完成后，已经加载完成的数据已经固定在内存中，是有固定顺序的，这时候已经不适合称之为"无序"，而应该称之为"随机"。

5.查询结果(虚拟表)的无序性、随机性

除了数据库中的实体表，在查询的时候，中途生成的虚拟表都是无序的，但order by和distinct后的结果除外。关于order by的结果，见后文的说明。

简单的几个例子。

首先创建示例表，并查看表结构和数据如下：

MariaDB [test]> create table Student1 (sid int ,name char(20),age int,class char(20));
MariaDB [test]> insert into  Student1 values(3,'zhangsan',21,'Java');
                                            (6,'zhaoliu',19,'Java'),
                                            (2,'huanger',23,'Python'),
                                            (1,'chenyi',22,'Java'),
                                            (4,'lisi',20,'C#'),
                                            (5,'wangwu',21,'Python'),
                                            (7,'qianqi',22,'C'),
                                            (8,'sunba',20,'C++'),
                                            (9,'yangjiu',24,'Java');

MariaDB [test]> select * from Student1;
+------+----------+------+--------+
| sid  | name     | age  | class  |
+------+----------+------+--------+
|    3 | zhangsan |   21 | Java   |
|    6 | zhaoliu  |   19 | Java   |
|    2 | huanger  |   23 | Python |
|    1 | chenyi   |   22 | Java   |
|    4 | lisi     |   20 | C#     |
|    5 | wangwu   |   21 | Python |
|    7 | qianqi   |   22 | C      |
|    8 | sunba    |   20 | C++    |
|    9 | yangjiu  |   24 | Java   |
+------+----------+------+--------+

这里面没有任何索引，无论是关系引擎，还是优化器，都认为这个表是无序的，因此只能执行表扫描。而表扫描的过程是按照数据的"物理存储顺序"进行访问的，sid=3的记录先存进数据库，就先访问这个记录(按照前文的物理存储方式，这个说法是错误的，但现在忽略这个问题)。在没有任何索引、没有任何优化"提醒"时，优化器就会生成"按照物理存储顺序"的执行计划去表扫描。

但是表扫描的结果对于我们人类来说是无序的结果，更准确的说，是随机的结果，是我们无法去预料的结果。因为堆中的数据在进行物理存储时，可能会"见缝插针"，而我们根本不知道这根"针"插在哪个"缝"里，也不知道它前面的数据是什么，后面的数据是什么。例如，堆表中的部分数据删除了，再插入一部分数据，新插入的数据有些可能会插入在表的尾部，有些也可能插在数据删除后留下的"槽"(slot)中。

再来说明查询执行过程中生成的虚拟表。虚拟表是逻辑的概念，是SQL语句执行过程中每一个阶段产生的数据集合，在我们人的感官上，我们会把这个集合看成虚拟表。但多数时候，它们并不真的是二维表结构的形式，只是内存中一段存储了数据的缓存空间。少数时候，由于算法或某些操作的需要，会实实在在地创建虚拟临时表，例如使用DISTINCT子句对结果去重时，就会先生成一张临时表用于排序并去重。

例如，在两表联接时我们总说会产生"笛卡尔积"，然后用一张二维表的形式去感受这个笛卡尔积的虚拟表。

但在实际执行过程中不会是这样的表结构，而应该是下面这种结构。

也就是说，虚拟结果集中的数据是无序的，随后对该虚拟结果集的操作也是随机而无法保证顺序的。例如上面的笛卡尔积结果集，如果使用了WHERE子句筛选某些行，则筛选的过程是对笛卡尔积进行"表"扫描，但笛卡尔积本就是不保证顺序的，所以当where筛选出多行时，这些行的顺序可能会和我们预料的结果有所不同。当然，优化器不会真的采用这样的方案，但站在逻辑角度上看，因为虚拟结果集无序，要从中检索数据只能进行"表"扫描。

再比如说TOP子句(MySQL、MariaDB中等价的是LIMIT子句)，如果没有结合ORDER BY子句，那么TOP将从其前面的虚拟结果集中按某种顺序挑出满足数量的行出来，挑出的这些行是我们人无法预料的，所以TOP的结果是随机的。这里的"某种顺序"并非有序，例如从上图的笛卡尔积中选一行时，由于笛卡尔积是无序的，挑选的这一行将受表A的物理存储顺序、表B的物理存储顺序影响。

只有使用了ORDER BY子句，才能保证TOP的结果是可预料而非随机的，因为ORDER BY的虚拟结果集是有序的。事实上，ORDER BY的结果不应该叫"集"，而应该叫"游标对象"，因为排序后的结果中，每一行都按照我们期待的顺序固定好了位置，之后TOP再去操作这样的结果就一定能得到我们预料之中的数据。

6.为什么总是强调"无序"

通过前面的内容，我们已经发现无序性的最大问题在于返回结果的无法预测性。返回结果无法预测，意味着数据增、删、改的时候存在危险，意味着我们可能对检索的数据认知不足。

7.什么时候的数据是有序的？

前面说了一大堆，总结一下就是：数据都是无序的，每一步的检索都有随机性，无法保证能达到我们人的期待。但是，关系型数据库中的所有数据都是无序，都是随机的吗？换句话说，上面的总结对吗？答案是不对。

在关系型数据库中，我们除了考虑从存储引擎到磁盘这段路程(受物理存储顺序影响)，还要考虑语句执行过程中内存中的虚拟表。在前面，我们说虚拟表是无序的，这句话不准确。

一方面，数据加载成功后，它们在内存中已经是有序的，但对我们人来说，我们无法看到这样的"序"，也就是说结果是随机的，是我们无法预料的。

另一方面，当有排序算法对虚拟表进行排序后，结果也是有序的，这样的结果是符合我们人所预料的。对于排序后的结果，ANSI将其称之为"游标对象"。

常见的两个涉及到排序算法的子句是ORDER BY和DISTINCT。此处之外，还有游标自身，它的结果也是有序的。

对于ORDER BY子句，它会将它前面的虚拟结果集进行排序，排序的结果集中，每行记录都固定好了位置，我们可以预料到任何一行数据处于哪个位置，也知道它前面是什么数据，后面是什么数据。也就是说，排序后的数据是固定而非随机的。

对于DISTINCT子句，在对其前面的虚拟结果集进行去重操作时，DISTINCT总会带有排序操作(即使没有指定order by，内部也自带排序)，排序之后再对结果集进行去重。例如下面的表。

 id   name
----  -----
 5     e
 2     b
 4     d
 1     a
 2     x
 3     c

对id列去重时，它将对id列进行排序，得到的结果将是：

 id
----
 1
 2
 3
 4
 5

结果是有序的。但对于id=2的记录来说，在去重时应该保留name=b的还是name=x的记录呢？无法保证，因为DISTINCT只对id列排序，不对id之外的列排序。因此对于有重复值的记录，DISTINCT只能返回一个随机记录，我们无法预料这个记录是不是我们想要的结果。

在SQL Server和Oracle中这没什么问题，因为使用DISTINCT后，它后面的过程不允许使用非DISTINCT列(DISTINCT后面还有ORDER BY和TOP，但涉及到列的只有ORDER BY子句)，因此最终得到的结果只有指定的去重列(id列)。但是在MySQL/MariaDB中，ORDER BY允许使用非DISTINCT列，例如select distinct id from t1 order by name，它将先按name排序，再按id排序，最后对id去重。也许你发现了，这种情况下DISTINCT是在ORDER BY之后才执行的，没错，事实就是如此。