大数据量的表如何快速增加索引

🪐 TiDB 技术问题 性能调优
1

为提高效率，提问时请尽量提供详细背景信息，问题描述清晰可优先响应。以下信息点请尽量提供：

  • 【系统版本 & kernel 版本】
  • 【TiDB 版本】
  • 【磁盘型号】
  • 【集群节点分布】
  • 【数据量 & region 数量 & 副本数】数据量40亿行左右
  • 【问题描述（我做了什么）】一个数据量很大的表，如何快速添加新的索引？
  • 【关键词】大数据量表；新增索引
2

1、建议在业务低峰期执行添加索引的操作。

2、适当调整下述参数：

tidb_ddl_reorg_batch_size

tidb_ddl_reorg_worker_cnt

tidb_ddl_reorg_priority

参数链接如下：
https://pingcap.com/docs-cn/v2.1/reference/configuration/tidb-server/tidb-specific-variables/#tidb_ddl_reorg_batch_size

3

  • tidb_ddl_reorg_worker_cnt 同时并发工作的worker数量 默认值 16

  • tidb_ddl_reorg_batch_size 默认值1024

  • tidb_ddl_reorg_priority 优先级 可设置为 PRIORITY_LOW/PRIORITY_NORMAL/PRIORITY_HIGH

  • 请问下 前两个数值的设定有没有建议值，还是需要自己做下尝试？

4

一般情况下 3个 tidb server ，建议 16个 worker ，batch_size 建议：4096 。具体的参数需要根据当前系统的负载情况进行调整。可以参考下述链接

https://pingcap.com/docs-cn/v3.0/benchmark/add-index-with-load/#线上负载与-add-index-相互影响测试

5

好的，感谢。

6

你好，请问下执行过程中如果失败了，如连接中断，出现索引没建成功（show index没有），再建时却提示已存在该索引，这种情况该怎么处理。

7

是这样的，如果发起了 create index 的操作，等待客户端返回 success 后，终端可以正常关闭。这个 create index 的 job 会添加到 add index 的队列中，此时可以通过 admin show ddl 相关命令查看该 job 的执行情况，添加索引需要回填数据。如果是 2.1 及以上版本，那么普通的 ddl 和 index 做了拆分，具体需要看你的版本号到底是什么。如果要停止弄个 ddl job 可以使用 cancel 。

1、查询 ddl 状态的相关命令如下：
https://pingcap.com/docs-cn/v2.1/reference/sql/statements/admin/#admin-语句

2、tidb online ddl 的介绍：
https://github.com/ngaut/builddatabase/blob/master/f1/schema-change-implement.md

8

WeChat7346f594895f5a37800ad59412fcad49
WeChat7346f594895f5a37800ad59412fcad492232×706 361 KB

请问下这个 add index的状态是正在运行，后面的drop index状态none是指无效不会执行还是？
之前添加索引时，没有修改参数，现在速度很慢；连接中断过，以为是失败了，后面执行了删除。

1 个赞
9

你是 add index 的操作没有执行完，然后就 提交了很多个 drop 这个 index 的操作吗？

1、add index 的并且 job id 是 721 的正在执行，可以定时刷新下，schema_state 状态是 write reorg 表示正在回写数据

2、drop index 的其他 job ，状态是 schema_state 是 none 表示：

image
image1420×48 14.6 KB

但是这些 drop index 的 job 的 state 的状态是：

image
image1484×530 99.8 KB

表示，这些 drop index 的 job 已经在队里中等待执行，或者等待取消。

1 个赞
10

之前 add index 时连接中断了，以为执行失败了，后面想再 add index 时，提示已存在，就试着执行 drop index。 那我需要使用 ADMIN CANCEL DDL JOBS 手动取消这些 drop index 的任务是吗？ 这个 add index 速度很慢，因为之前没有调整参数（tidb_ddl_reorg_worker_cnt、tidb_ddl_reorg_batch_size、tidb_ddl_reorg_priority），如果现在调整会有效果吗？

11

1、建议把后面 drop index 的操作都 cancel掉，有些 任务已经在队列中等待执行了，如果不 cancel，add index 的操作执行完会接着执行 drop 的操作的。

2、现在调整参数对已经运行的任务不生效。

12

好的，谢谢！:handshake:

13

WeChat00cf5a308127b305ed154e8cf9bdac86
WeChat00cf5a308127b305ed154e8cf9bdac862218×1170 711 KB

再请教下，ADMIN CANCEL DDL JOBS 722，命令执行后，这个任务队列的先后是有关系吗？执行 cancel 后这个任务队列 job_id重复，有的job_id消失了？？

命令 ADMIN SHOW DDL，返回的是 ID:722, Type:drop index, State:none, SchemaState:none, SchemaID:146, TableID:698, RowCount:0, ArgLen:0, start time: 2019-10-10 21:17:18.818 +0800 CST, Err:, ErrCount:0, SnapshotVersion:0
ID:721, Type:add index, State:running, SchemaState:write reorganization, SchemaID:146, TableID:698, RowCount:455656946, ArgLen:0, start time: 2019-10-10 20:44:30.918 +0800 CST, Err:[kv:9007]Write conflict, txnStartTS=411771139330670593, conflictStartTS=411754809751764993, conflictCommitTS=0, key={tableID=698, handle=391317290} primary={tableID=698, indexID=5, indexValues={0, 391317246, }} [try again later], ErrCount:313, SnapshotVersion:411752587989614594
这边是不是冲突了？

14

你那边用的是什么版本？版本信息发下

15

怎么查看版本信息呢？

16

1、从 tidb.log 可以看

2、数据库中 select version() 也可以看

17

版本：5.7.25-TiDB-v3.0.0

18

我这边将之前的任务取消了，然后重建，速度快了很多，应该是add index 和 drop index冲突导致速度慢了。

19

问下，你用的客户端是？navicat（这个显示有问题，job id 位数显示有问题）？可以用 mysql client 执行试一下。

“Write conflict” 是写冲突了。

1 个赞
20

客户端是navicat，还有这个问题！！我看下。。。。
现在是正常了！！！:handshake: