GaussDB(for MySQL)如何快速建立索引?华为云数据库资深架构师为您揭秘

2021年09月16日 阅读数:1
这篇文章主要向大家介绍GaussDB(for MySQL)如何快速建立索引?华为云数据库资深架构师为您揭秘,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。
摘要:云服务环境下,如何解决客户基于大量数据建立索引的性能问题,成为云服务厂商的一个挑战。华为云GaussDB(for MySQL)经过引入并行建立索引技术,很好地解决了批量索引建立和临时添加索引等性能瓶颈问题,帮助用户更快创建好索引。想要进一步了解快速建立索引的秘诀,请不要错过本文。

本文分享自华为云社区《GaussDB(for MySQL)如何快速建立索引?华为云数据库资深架构师为您揭秘》,做者:华为云数据库资深架构师苏斌。html

苏斌,华为云数据库资深架构师,拥有16年数据库内核研发经验,以前做为MySQL官方InnoDB团队主要研发人员,参与和主导了多个重要特性的开发和发布。目前在华为公司负责和参与华为云RDS主要产品RDS for MySQL和GaussDB(for MySQL)内核功能的设计和研发。mysql

导读

云服务环境下,如何解决客户基于大量数据建立索引的性能问题,成为云服务厂商的一个挑战。华为云GaussDB(for MySQL)经过引入并行建立索引技术,很好地解决了批量索引建立和临时添加索引等性能瓶颈问题,帮助用户更快创建好索引。想要进一步了解快速建立索引的秘诀,请不要错过本文。web

关于MySQL索引

咱们都知道,数据库使用索引技术加快数据的查询。MySQL数据库也支持若干种索引结构提升查询的性能(参见MySQL文档:https://dev.mysql.com/doc/refman/8.0/en/create-index.html),其中使用最普遍的是B+tree索引,由于B+tree索引在查询和修改的性能之间有很好的平衡,同时其存储和维护的代价也是比较优的。算法

MySQL的表自己由聚簇索引(必须是B+tree索引)表示,再加上若干个二级索引,包括B+tree索引,共同组成一个MySQL的独立表,能够说MySQL的表是由一组索引共同组成的。咱们都知道索引是一把双刃剑,充分的索引能够更好地提高能够适配的查询的性能,可是须要维护这些索引使得其和数据同步,因此在数据修改操做阶段,更多的索引也会带来更高的开销。索引建立与否的权衡一般是动态的,用户不必定能作到在表定义之初就知道须要创建哪些索引,须要随着业务的发展变化而调整索引,这也带来了动态索引建立的一些问题。sql

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,客户对建立索引太慢所带来的性能问题的抱怨愈来愈多,尤为是建立索引周期若是太长,咱们可能很难找到一段合适的业务低峰期来动态建立索引,避免业务的波动。所以,如何在云服务环境下,解决客户基于大量数据建立索引的性能问题,成为云服务厂商的一个挑战。app

在云化场景下,还有一个主要场景对客户的体验很是重要。咱们知道客户的业务要迁移上云,须要对数据进行大规模的迁移(华为云提供了数据复制服务DRS工具支持各种数据迁移场景),数据迁移比较高效的方式为:

  1. 逻辑导出源端数据
  2. 在目标端建表(注意,表不含二级索引)
  3. 将源端导出的数据插入到目标端
  4. 对目标端的表创建二级索引

若是涉及动态数据同步,相关步骤会更复杂一些,因为和该主题无关,这里不展开。以上步骤中,须要重点注意的是步骤2和4,在目标端建立表的时候先不建立二级索引。这个优化对性能影响很大,尤为是一个表有不少二级索引的场景。咱们知道Btree索引的插入若是是有序的,对插入性能和结果的空间利用率是最好的,由于Btree索引的分裂会在插入区域的尾部产生,同时因为分裂算法的优化,分裂产生的页面填充率会比较高;相反地,若是是随机插入,尤为是并发地随机插入,很容易致使Btree索引在不一样的节点进行分裂,而且分裂后的页面填充率都处于一个半满的状态,致使Btree最终的一个膨胀。

有了这个背景以后,咱们就容易理解上面的问题,插入表数据的时候,咱们屏蔽了二级索引,等全部数据都准备好了,再采用批量创建索引的方式建立二级索引,这对于二级索引建立效率是最高的。若是不这么作,每插入一条记录,就要去插入相应的二级索引,那么二级索引就是一个无序的随机插入,并发起来性能会变差不少。

虽然在数据同步准备好后,批量建立二级索引是一个有效的方案,可是若是数据量很大,这么建立二级索引仍是很是耗时,致使客户在数据迁移完以后须要等待很长时间才能开展业务,这个等待周期多是小时甚至天级别的。虽然能够考虑表级别的并发建立索引,可是这个方法也有明显的缺点:应用场景有限,要求有多表;以及表和表之间的并发其实不是一个最有效的并发形式,相互影响比较大。

GaussDB(for MySQL)如何快速建立索引?

综上所述,在建立索引这个点上存在两个性能瓶颈点:一个是用户迁移数据以后的批量索引建立;第二个是用户临时须要添加一个二级索引。不管哪一个点,咱们都须要更快的创建好索引,提高用户的使用体验。

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

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

GaussDB(for MySQL)尤为对数据的归并排序作了多种优化,使得咱们常规的归并排序可以充分的并行,充分利用CPU、内存和IO的资源。在并行建立索引以后的合并步骤,也使用了一套简化的算法,正确处理各类索引结构的场景。

支持的索引和场景

GaussDB(for MySQL)的并行建立索引功能,目前支持的索引为Btree二级索引。对于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的影响等等。咱们后续会针对这些点进行优化,给客户带来更多的惊喜。

目前,华为云GaussDB(for MySQL) 并行建立索引优化功能已上线,欢迎你们前往华为云官网体验:https://whttps://www.huaweicloud.com/product/gaussdb_mysql.html/gaussdb_mysql.html

附:华为云GaussDB(for MySQL)内核专家系列文章

 

华为海外女科学家为您揭秘:GaussDB(for MySQL)云栈垂直集成的力量有多大?

华为云数据库内核专家为您揭秘:GaussDB(for MySQL)并行查询有多快?

 

 

点击关注,第一时间了解华为云新鲜技术~