写在最前

随着互联网日渐发展，开发人员需要处理的数据量愈来愈大，一些不起眼的小问题可能就会引发燎原之火，而其中一个例子就是海量数据的检索。

由于历史原因及业务因素，某些数据我们不能转移到非关系型数据库如MongoDB上，这时我们就需要考虑如何在旧有的关系型数据库上进行快速检索。

温故知新

要调优关系型数据库上的检索性能，我们首先得了解，关系型数据库上检索的原理。

执行计划

我们先看看维基百科怎么描述一句Select查询的语句的执行：

As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.【1】

这里说出了一个重要的信息，每次检索语句执行，数据库系统都会根据自身的情况执行“查询优化”，最后生成一个“执行计划”，这个执行计划与我们的SQL定义可能不完全一致，比如查询条件运行的先后顺序，函数的执行顺序等等。

讨论对象

市面上流行的关系型数据库一般有Oracle，SQL Server和MySQL。Oracle和SQL Server是典型的商业软件，他的license通常都非常昂贵，但是方案非常成熟可靠，是过往许多大型企业、政务系统的优先选择。而MySQL凭借其免费且开源的优势，在互联网企业创业潮中，被广泛应用。

由于执行计划针对不同的数据库系统有不同的表现，今天我们就限定讨论的对象为MySQL这个被互联网企业广泛应用的关系型数据库。

获取执行计划

如前所述，每一句select语句都会被转换成执行计划，所以要提升select语句的性能，我们首先要知道如何获取执行计划，才能更好的分析。

在MySQL中，我们使用explain关键字获取执行计划。

The EXPLAIN statement provides information about how MySQL executes statements:

• EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

• When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN to obtain execution plan information.

• When EXPLAIN is used with FOR CONNECTION connection_id rather than an explainable statement, it displays the execution plan for the statement executing in the named connection.

• For SELECT statements, EXPLAIN produces additional execution plan information.

• EXPLAIN is useful for examining queries involving partitioned tables.

• The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSONformat displays the information in JSON format.【2】

数据库索引

一般在检索较慢的情况下，我们能立刻想到的就是是否有使用索引，连MySQL官方文档也将这一条通用优化方案记录在案：

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.【3】

索引能提升检索的速度，主要原因是索引的数据结构，不同类型的索引，效果也不一样。那么让我们再来温习一下索引的类型。

主键索引

顾名思义，主键索引即建立在主键上的索引，他必定是唯一索引，通过主键可以快速定位一条记录主要就是主键索引的原因。

外键索引

外键索引是建立在外键上的索引，需要关联另一个表的字段，这对表关联的速度有着很大的提升。国内开发的时候，一般都喜欢降低数据库范式以达到减少关联查询或者多次查询，从而提升检索效率。然而这其实也是一种无奈的选择，因为国内开发人员的素质问题，架构师们多数采取这种不过不失的方法防止开发人员出错。实际上，如果我们能将关键数据保存在主表，次要数据分存，速度绝对不会慢，也是提升应用速度的一个方法。但这也要求开发要精准理解需求，将数据划分到相应的表里面，进而精准控制程序的获取数据的规则。

单字段索引

对表中某一字段的索引，可以选择建立普通索引，唯一索引和全文索引，也可以选择其数据结构为B-TREE或hash。文档中还有index prefixes和spatial index两种索引，index prefixes可以理解为前置索引，而普通索引则是全字段长度的前置索引，所以当我们使用右模糊匹配('key%')时仍能使用索引；而spatial index则是涉及到存储的索引，一般开发比较少涉及。

多字段索引

对表中多个字段进行索引，使用时可以使用左边最小匹配原则，比如说：索引(A,B,C,D)，查询时单独使用A作为查询条件，不需要额外建立索引；使用(A,B)和(A,B,C)也不需要建立额外的索引；但使用B,(B,C)和(B,C,D)查询时，需要另外建立索引。

索引的数据结构

简单地说，常用的B-TREE和HASH主要区别在于，B-TREE因其树形结构擅长区域查找，而HASH擅长于快速定位。

小结

一般数据库查询调优的基本知识都准备完成了，我们试试用这些知识来解决一下实际问题吧。

实际案例

这里是我曾经遇到的一个案例：我们需要把B逻辑库中的T2表同步到A库的T1逻辑表中，数据总量在2亿左右，数据经过分库分表，每月数据量单库单表在1000万左右，累计数据一般在3000万左右。检索经过优化，查询已经确认使用索引，数据在程序中按分页获取，分页步长1000，程序GC正常，但仍然时常有同步较慢的查询出现，查询延迟达到8秒以上。

案件重演

为了重现这个慢查询，当时在测试库中的尝试了同样的查询，结果同样高达8秒的查询耗时。今天，我再度在自己的测试库中创建了一张30个字段，差不多400万数据的表（生产实际情况更加大，字段达到100多个，数据量3000万），使用类似的SQL查询，结果仍然需要2.273秒。

image.png

眼尖的同学大概已经看出问题了，但我们还是一步步来看。

首先，我们查看一下执行计划。

image.png

从执行计划可以看出，语句使用了索引t2，但是还是扫描了150万行数据。而t2=10的数据总量才80万。

image.png

这是因为数据量太多，筛选条件不能限定足够少的数据时，索引失效了。

其实这个情况应该很多人都曾经遇到过，通常我们可以使用一种替换方案来转换这句SQL，比如说按某个字段为依据，比如primary key，在这里是t1，那么SQL变为：

select * from t1 where t2=10 and t1>800000 limit 1000;

再试一下，我们发现的确快了很多，大概缩短到原来的5%左右了。

image.png image.png

可以看到差别在于Extra里多了一项Extra描述“Using where”。查看官方文档的解释：

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. 【4】

所以问题明了了，之前的where条件限定因为数据量太大，执行计划已经无法用where来限定输出的行，所以导致了全表扫描而导致慢。优化后，性能已经达到可以接受的轻度，但是扫描的行数还是居高不下，这个时候，我们其实还应该想办法将其降低。比如说，我现在增加了一行唯一索引，数据和t1一样，我们对这行数据按照其生成规则进行限定：

image.png

可以看到，检索的行数大幅减少，我们再看看实际查询时间：

image.png

时间再次缩短为原来的1/3。

坑从中来

满足于速度感的我很快以为这样就解决了问题了，然而问题真的解决了吗？

第一坑——区段限定值获取

然而问题不止于此，关键在于如何获取正确的区间段呢？我们需要一个效率哋获取区段限定值的方法，如果这个区段值检索性能消耗太大，我们将会得不偿失。

我们首先尝试用max和min函数来获取:

image.png

结果得到的是两个NULL，因为Max返回的是不看limit情况下的最大值和最小值，如果结果集被limit限定且最大值或最小值不在结果集里，返回值为NULL。

其实根据前面我们发现的特性，我们不难得出，我们实际需要的值一个为前一次查询遗留的起始值，另一个则为页步长顺位值，而最快的获取方式为以前置值为限定条件，通过limit限定最终获取行。

image.png

这时候，两个查询总体消耗为0.093+0.032=0.125s。（别在意具体值，截图麻烦，实际应该按你SQL语句的起始结束值来使用>=或者>号来获取实际值。）

第二坑——结果集的顺序

优化后，我们发现时不时，我们就有丢数的现象。排查后，发现我们的数据插入有一个特点：因为使用了spark，任务插入的顺序不可控，生成的ID在各个分库也不可控，所以导致有可能逻辑ID在一定时间内顺序是不可控的。这个逻辑字段对应的正式我们演示中的t31字段。（物理ID——系统自增的ID应该是有序可控的，如果数据没有被分库分表，完全依赖物理ID看似是可以的，但是实际还有问题，后面会说到。）

由于逻辑ID的顺序不可控，所以我们使用ID区段的时候，就有可能出现一个情况，在获取区段限定值时获取的刚好是跳值的逻辑ID，比如说，数据库存储顺序为【1,2,3,4,9,5,6,7,8,10】，分页为5，第一次取区段值时为【1,9】，第二次取值为【9,10】，这时，如果我们没有加limit限定取值个数，我们不能控制每个取值区间处理个数是均匀的，程序处理时间不可控；如果limit限定了取值个数，我们又回丢失第二区段中【5,6,7,8】这四个值。所以为了区段取值符合我们预设的均匀，稳健的需求，我们需要保证逻辑ID必须是有序的。

那么MySQL中，如何保证数据时有序的呢？

答案就是使用Order By语句。

MySQL中没有固定的默认排序，只有固定的默认排序方式（ASC，升序）。

image.png

结果你会发现，作为物理ID的t1字段竟然倒序了，而t32是正序的。

观察他的执行计划：

image.png

我们发现，他会多了一个Extra的内容“Using index”，这说明这句SQL使用了t32的索引了。并且，t32成为它默认排序的字段。

因此，MySQL如果硬说有默认的排序字段，那就是他执行计划中所使用的索引正序。但我们前面温故知新的时候知道，执行计划会被数据库系统自身优化掉，所以你的SQL实际执行的时候，很可能执行计划已经不是你预计的那样了。这就是为什么我们要求数据有序的话，必须加Order By的原因。

第三坑——这种优化必要吗？

我们知道优化是针对大数据量的，但是如果数据量少的话，还有必要吗？我们看看一下语句：

image.png

这个查询时间已经是可以接受的范围了，如果转换为两部法，那么时间分别为：

image.png image.png

总共时间是0.014+0.028=0.042s，还要另有IO开销，因此完全是没必要使用两分法的。

这时候，我们需要人工确定一个阈值，当数据量超过这个阈值的时候，我们就可以转为使用两分法来进行数据检索。这个阈值会根据生产环境，表结构，索引等因素变化，所以需要人为测试确定，比如说，一个30个字段的表和一个100多字段的表，在海量数据中处理时间是不一样的，后者会大好几倍。

第四坑——JAVA的多线程

Java多线程前面已经带来了插入ID不连续的坑，但实际生产上，多线程还会带来更多的坑，比如说，我们的线程如何划分。我们知道，两分法中，我们是需要前一次起始ID来确定下一个限定值的ID的。但是由于多线程不是线性的，我们一般不会精确地控制他们的执行顺序。所以这就要求我们的任务划分要做提前规划，或者划分具体区片进行处理了。关于程序这一块，我们暂时不过多讨论了。

总结

在关系型数据库中进行海量数据的检索，我们可以按实际数据量的大少，选择使用两部查询法分页查询获取。第一步，我们确立有序的唯一索引区间；第二步，通过该区间过滤我们需要的数据。

参考SQL范例

第一步

select this_end_value from table_name where unique_index_field_name > last_end_value order by unique_index_field_name limit page_size, 1;

第二步

select select_expr from table_name where unique_index_field_name between last_end_value+1 and this_end_value order by unique_index_field_name limit page_size;

参考资料

【1】https://en.wikipedia.org/wiki/Select_(SQL)

【2】https://dev.mysql.com/doc/refman/5.7/en/explain.html

【3】https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

【4】https://dev.mysql.com/doc/refman/5.7/en/explain-output.html