关于程序员:GaussDBfor-MySQL如何快速创建索引华为云数据库资深架构师为您揭秘

5次阅读

共计 4305 个字符，预计需要花费 11 分钟才能阅读完成。

苏斌，华为云数据库资深架构师，领有 16 年数据库内核研发教训，之前作为 MySQL 官网 InnoDB 团队次要研发人员，参加和主导了多个重要个性的开发和公布。目前在华为公司负责和参加华为云 RDS 次要产品 RDS for MySQL 和 GaussDB(for MySQL) 内核性能的设计和研发。云服务环境下，如何解决客户基于大量数据创立索引的性能问题，成为云服务厂商的一个挑战。华为云 GaussDB(for MySQL)通过引入并行创立索引技术，很好地解决了批量索引创立和长期增加索引等性能瓶颈问题，帮忙用户更快建设好索引。想要进一步理解疾速创立索引的秘诀，请不要错过本文。

咱们都晓得，数据库应用索引技术放慢数据的查问。MySQL 数据库也反对若干种索引构造进步查问的性能（详情请参见 MySQL 官网官网文档），其中应用最宽泛的是 B +tree 索引，因为 B +tree 索引在查问和批改的性能之间有很好的均衡，同时其存储和保护的代价也是比拟优的。MySQL 的表自身由聚簇索引（必须是 B +tree 索引）示意，再加上若干个二级索引，包含 B +tree 索引，独特组成一个 MySQL 的独立表，能够说 MySQL 的表是由一组索引独特组成的。咱们都晓得索引是一把双刃剑，充沛的索引能够更好地晋升能够适配的查问的性能，然而须要保护这些索引使得其和数据同步，所以在数据批改操作阶段，更多的索引也会带来更高的开销。索引创立与否的衡量通常是动静的，用户不肯定能做到在表定义之初就晓得须要建设哪些索引，须要随着业务的倒退变动而调整索引，这也带来了动静索引创立的一些问题。

咱们先看一下 MySQL 索引创立的逻辑。首先，MySQL 索引的创立能够应用两种不同的 DDL（Data Definition Language: 数据定义语言）算法来实现。第一种是 COPY 算法，它十分低效，就是在两个表之间进行数据拷贝，来实现表构造相干的批改，尤其是它要求加表锁，当初根本不应用了。第二种是 INPLACE 算法，该算法不要求加锁，因而很多 DDL 操作是不阻塞 DML（Data Manipulation Language: 数据操纵语句）操作的，比方创立索引。该算法具体的实现在存储引擎层面实现，能够进行更多的优化。实际上 DDL 语句还有一种 INSTANT 算法，然而它无奈反对创立索引操作，这里不开展介绍。对于 INPLACE 算法，在 5.7 版本之前，是采纳索引记录一直地向建好的空索引插入的形式。因为插入的数据的无序性，该办法导致了显著的性能问题和潜在的空间节约。在 5.7 版本当前，MySQL 优化了建索引步骤，将其改良为对已排序的索引记录进行自底向上批量插入并且紧凑拼装的创立形式，如果有多个索引要创立，会独自对每个索引执行雷同的算法。新的算法会经验读取数据、排序数据和创立索引这几个次要步骤。总体而言，创立索引这类 DDL 操作，会比一般的 DML 等操作要费时，而该类 DDL 耗时会导致用户在持续动静增加索引减速查问的时候，须要期待很长的工夫，极大影响业务；而且用户的 MySQL 实例开启了 Binlog 复制，耗时的 DDL 操作容易引起备库的长时间落后。

MySQL 的创立索引流程图

随着越来越多用户把数据托管在云服务上，以及用户数据量的一直增长，前述的动静增加索引导致的问题十分影响用户体验。同时客户的单表数据逐步达到几 TB 甚至几十 TB，客户对创立索引太慢所带来的性能问题的埋怨越来越多，尤其是创立索引周期如果太长，咱们可能很难找到一段适合的业务低峰期来动态创建索引，防止业务的稳定。因而，如何在云服务环境下，解决客户基于大量数据创立索引的性能问题，成为云服务厂商的一个挑战。
在云化场景下，还有一个次要场景对客户的体验十分重要。咱们晓得客户的业务要迁徙上云，须要对数据进行大规模的迁徙（华为云提供了数据复制服务 DRS 工具反对各类数据迁徙场景），数据迁徙比拟高效的形式为：

逻辑导出源端数据
在指标端建表（留神，表不含二级索引）
将源端导出的数据插入到指标端
对指标端的表建设二级索引

如果波及动态数据同步，相干步骤会更简单一些，因为和该主题无关，这里不开展。以上步骤中，须要重点留神的是步骤 2 和 4，在指标端创立表的时候先不创立二级索引。这个优化对性能影响很大，尤其是一个表有很多二级索引的场景。咱们晓得 B +tree 索引的插入如果是有序的，对插入性能和后果的空间利用率是最好的，因为 B +tree 索引的决裂会在插入区域的尾部产生，同时因为决裂算法的优化，决裂产生的页面填充率会比拟高；相同地，如果是随机插入，尤其是并发地随机插入，很容易导致 B +tree 索引在不同的节点进行决裂，并且决裂后的页面填充率都处于一个半满的状态，导致 B +tree 最终的一个收缩。

有了这个背景之后，咱们就容易了解下面的问题，插入表数据的时候，咱们屏蔽了二级索引，等所有数据都筹备好了，再采纳批量建设索引的形式创立二级索引，这对于二级索引创立效率是最高的。如果不这么做，每插入一条记录，就要去插入相应的二级索引，那么二级索引就是一个无序的随机插入，并发起来性能会变差很多。尽管在数据同步筹备好后，批量创立二级索引是一个无效的计划，然而如果数据量很大，这么创立二级索引还是十分耗时，导致客户在数据迁徙完之后须要期待很长时间能力开展业务，这个期待周期可能是小时甚至天级别的。尽管能够思考表级别的并发创立索引，然而这个办法也有显著的毛病：利用场景无限，要求有多表；以及表和表之间的并发其实不是一个最无效的并发模式，相互影响比拟大。

综上所述，在创立索引这个点上存在两个性能瓶颈点：一个是用户迁徙数据之后的批量索引创立；第二个是用户长期须要增加一个二级索引。无论哪个点，咱们都须要更快的建设好索引，晋升用户的应用体验。

华为云 GaussDB(for MySQL)引入了 并行创立索引的技术，它改良了社区版 MySQL 创立索引只用单线程的问题，以此进步创立索引的效率，并一起解决了前述两个痛点。后面提到的社区版创立索引逻辑是单线程的，首先存在资源利用率不够丰满的问题；其次创立索引过程是 CPU 和 IO 开销交替进行的过程，在做一个操作的时候，即便不是资源竞争的操作也只有期待。多线程创立索引能够充分利用 CPU 和 IO 资源，同时有的线程在做 CPU 计算时，别的线程能够并发的做 IO 操作。

GaussDB(for MySQL)应用的并行创立索引，是一个全链路的并行技术。后面提到，创立索引蕴含了若干个阶段，咱们的并行创立算法，对这里的每个阶段都做并行处理，从读取数据、排序、到创立索引，都是并行操作，每一步都由指定的 N 个线程并发解决。它的逻辑如下图所示：

GaussDB(for MySQL)尤其对数据的归并排序做了多种优化，使得咱们惯例的归并排序可能充沛的并行，充分利用 CPU、内存和 IO 的资源。在并行创立索引之后的合并步骤，也应用了一套简化的算法，正确处理各种索引构造的场景。

GaussDB(for MySQL)的并行创立索引性能，目前反对的索引为 B +tree 二级索引。对于 VIRTUAL index 二级索引，将会在不久的未来提供全面的反对，而 MySQL 的 SPATIAL index 和 FULLTEXT index 不在该并行创立索引覆盖范围内。
特地要留神的是，主键索引的创立目前也是不反对并行的，因而如果一个并行创立索引的 SQL 语句蕴含创立主键索引，或者后面提及的 SPATIAL index 与 FULLTEXT index，那么客户端将会收到一个告警，提醒该操作不反对并行创立索引，同时该语句会采纳单线程创立索引的形式执行实现。

从 SQL 语句的角度，如前所述，创立索引能够采纳不同的算法，因为 COPY 算法（ALGORITHM=COPY）不是采纳批量插入的形式，因而不会受害于该并行创立索引优化。而对于 INPLACE 算法，如果创立索引用的是非 rebuild 的形式，都能够受害于该优化；一旦须要应用 rebuild 的形式创立索引，因为波及到主键索引的建设，将无奈应用并行创立索引的算法。

上面咱们通过几个实例来理解一下如何应用并行创立索引算法放慢创立速度，以及咱们的条件束缚是如何失效的。

咱们应用 sysbench 的表，表内有 1 亿条数据
在该表的 k 字段建索引，采纳社区默认单线程，耗时 146.82s
通过设置 innodb_rds_parallel_index_creation_threads= 4 启用 4 个线程建索引，能够看到建索引耗时 38.72s，速度晋升 3.79 倍。
假如咱们要批改主键索引，尽管指定了多线程，然而会收到一个 warning，实际上只能通过单线程建索引

首先对 innodb_rds_parallel_index_creation_threads 这个参数进行一下阐明，它管制了零碎中所有并行 DDL 能够应用的总线程数，取值范畴是[1-128]。该参数取值为 1 示意应用原始的单线程创立索引，取值为 N，示意接下来的 DDL 应用 N 个线程创立。如果一个 DDL 应用了 100 个线程在执行，那么另外一个也要应用并行的 DDL 且最多只能应用剩下的 28 个线程；而如果 128 个线程都被并行 DDL 语句占用了，新来的 DDL 只能走原始的单线程创立的逻辑。

尽管该并行创立索引放慢了索引的创立速度，然而在具体应用场景下，还是须要有审慎的评估。咱们晓得在并行算法利用之后，该 DDL 对硬件资源的应用会尽可能的充沛，这也意味着其它操作就得不到太多的资源了。因而，针对不同的场景须要具体地剖析，它决定了咱们如何创立索引。

对于迁徙场景，因为这时候还没有任何业务接入，用户心愿尽快实现所有索引的创立，因而能够尽量设置多线程数，比方咱们是 16 核规格的实例，那么咱们就能够把并行线程的数量指定为 16，减速实现操作。

如果是用户业务运行阶段要创立索引，咱们还是不心愿 DDL 操作，对正在运行的业务如 DML 操作等有太多的影响。因而，这时候创立索引能够指定绝对少一些的线程数量，比方 2 -4（或者依据 CPU 规格以及负载决定，同时不激励并发地执行多个 DDL 操作）。这样既能绝对地减速创立索引的过程，也能保障 DML 的失常进行。

综上所述，GaussDB(for MySQL)反对了并行创立索引，通过缩短创立索引应用的工夫，很好地解决了客户关切的两类问题，晋升了客户的体验。但技术无止境，在创立索引畛域，还有其它的问题须要咱们优化解决，例如如何缩小创立索引步骤对 IO 的影响等等。咱们后续会针对这些点进行优化，给客户带来更多的惊喜。