索引加速功能真能提升 10 倍吗？--TiDB V6.1.0-V7.1.0 建索引速度对比
作者：TiDB 社区干货传送门
- 2023-07-07 北京
本文字数：8942 字
阅读完需：约 29 分钟
作者： 我是咖啡哥原文来源：https://tidb.net/blog/a93d7c03
记得在某个 6 的版本发布宣传里面看到，新版本建索引速度提升了 10 倍，一直没抽时间亲自测试验证下，前段时间 V7.1 发布，趁着这次机会，把我的一个 V6.1.0 的环境做下升级，顺便测试了下各版本创建索引速度对比下。
不测不知道，一测吓一跳，满满的惊喜。一个 3kw 的表新索引速度从 v6.1.0 的 21min，到 v7.1.0 只要 1min8s。提升了 20 倍！
以下来自官方文档相关版本关于索引加速功能的 Release Notes：
TiDB v6.3.0 支持开启添加索引加速功能，提升了创建索引回填过程的速度。开启该功能后，TiDB 添加索引的性能提升约为原来的 3 倍。从 v6.5.0 起，添加索引加速功能默认开启。
1、环境说明
环境信息
单节点 TiDB 环境，物理机。配置：40c512G，7T SSDTiDB 初始版本：v6.1.0，逐步升级到 v6.5.0 和 v7.1.0
表信息：3kw
直接使用的 tpcc 里面的 customer 表，3000w，大小约 18G
[root@127.0.0.1][tpcc][05:17:48]> show create table customer\G*************************** 1. row *************************** Table: customerCreate Table: CREATE TABLE `customer` ( `c_id` int(11) NOT NULL, `c_d_id` int(11) NOT NULL, `c_w_id` int(11) NOT NULL, `c_first` varchar(16) DEFAULT NULL, `c_middle` char(2) DEFAULT NULL, `c_last` varchar(16) DEFAULT NULL, `c_street_1` varchar(20) DEFAULT NULL, `c_street_2` varchar(20) DEFAULT NULL, `c_city` varchar(20) DEFAULT NULL, `c_state` char(2) DEFAULT NULL, `c_zip` char(9) DEFAULT NULL, `c_phone` char(16) DEFAULT NULL, `c_since` datetime DEFAULT NULL, `c_credit` char(2) DEFAULT NULL, `c_credit_lim` decimal(12,2) DEFAULT NULL, `c_discount` decimal(4,4) DEFAULT NULL, `c_balance` decimal(12,2) DEFAULT NULL, `c_ytd_payment` decimal(12,2) DEFAULT NULL, `c_payment_cnt` int(11) DEFAULT NULL, `c_delivery_cnt` int(11) DEFAULT NULL, `c_data` varchar(500) DEFAULT NULL, PRIMARY KEY (`c_w_id`,`c_d_id`,`c_id`) /*T![clustered_index] NONCLUSTERED */, KEY `idx_customer` (`c_w_id`,`c_d_id`,`c_last`,`c_first`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin1 row in set (0.01 sec)
[root@127.0.0.1][tpcc][05:21:48]> show variables like 'tidb_ddl%';+----------------------------+--------------+| Variable_name | Value |+----------------------------+--------------+| tidb_ddl_error_count_limit | 512 || tidb_ddl_reorg_batch_size | 256 || tidb_ddl_reorg_priority | PRIORITY_LOW || tidb_ddl_reorg_worker_cnt | 4 |+----------------------------+--------------+4 rows in set (0.00 sec)
复制代码
2、v6.1.0 测试 3kw 的表加索引
测试加 3 个不同类型列的索引
分别测试三个索引，一个是 varchar 列、一个是 char 列，一个组合索引
alter table tpcc.customer add index idx_01(c_city);alter table tpcc.customer add index idx_02(c_phone);alter table tpcc.customer add index idx_03(c_phone,c_since);
复制代码
修改 tidb_ddl_reorg_worker_cnt，观察建索引的速度。
经测试，时间差不多，后面就主要以组合索引 idx_03 为主来做对比。
--默认4时
[root@127.0.0.1][tpcc][05:25:40]> alter table tpcc.customer add index idx_01(c_city);Query OK, 0 rows affected (21 min 4.48 sec)
[root@127.0.0.1][tpcc][05:46:48]> alter table tpcc.customer add index idx_02(c_phone);Query OK, 0 rows affected (21 min 8.08 sec)
复制代码
[root@127.0.0.1][tpcc][06:08:50]> set global tidb_ddl_reorg_worker_cnt=8;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][06:08:58]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (10 min 44.26 sec)
[root@127.0.0.1][tpcc][06:22:43]> set global tidb_ddl_reorg_worker_cnt=16;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][06:23:01]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (9 min 6.01 sec)
[root@127.0.0.1][tpcc][08:38:03]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.33 sec)
[root@127.0.0.1][tpcc][08:38:18]> set global tidb_ddl_reorg_worker_cnt=32;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][08:38:29]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (8 min 35.48 sec)
复制代码
加索引时间
--平均每秒6000行，4个workerselect 30000000/(6000*4)/60;--平均每秒1600行，32个workerselect 30000000/(1600*32)/60;
复制代码
tidb 日志中可以看到：当 tidb_ddl_reorg_worker_cnt=4 时，创建索引的读取数据的速度。单线程每秒 5k-7k 行左右。当 tidb_ddl_reorg_worker_cnt=32 时，创建索引的读取数据的速度。单线程每秒只有 1600 行左右。
[2023/06/19 17:35:06.521 +08:00] [INFO] [backfilling.go:271] ["[ddl] backfill worker back fill index"] [workerID=1] [addedCount=90624] [scanCount=90624] [nextHandle=12317094.next] [speed(rows/s)=6611.8594994214745][2023/06/19 17:35:06.521 +08:00] [INFO] [backfilling.go:271] ["[ddl] backfill worker back fill index"] [workerID=3] [addedCount=90624] [scanCount=90624] [nextHandle=12609699.next] [speed(rows/s)=6681.175296206864][2023/06/19 17:35:06.689 +08:00] [INFO] [backfilling.go:271] ["[ddl] backfill worker back fill index"] [workerID=2] [addedCount=90624] [scanCount=90624] [nextHandle=12463112.next] [speed(rows/s)=6661.681631740738][2023/06/19 17:35:06.904 +08:00] [INFO] [backfilling.go:271] ["[ddl] backfill worker back fill index"] [workerID=0] [addedCount=90624] [scanCount=90624] [nextHandle=12171054.next] [speed(rows/s)=6364.5596641745815]
....
[2023/06/20 08:45:14.810 +08:00] [INFO] [backfilling.go:271] ["[ddl] backfill worker back fill index"] [workerID=28] [addedCount=30208] [scanCount=30208] [nextHandle=27312608.next] [speed(rows/s)=1657.5438087595428]
复制代码
3、v6.5.0 测试 3kw 的表加索引
确认 TiDB 版本和表信息
[root@127.0.0.1][tpcc][09:14:33]> select version();+--------------------+| version() |+--------------------+| 5.7.25-TiDB-v6.5.0 |+--------------------+1 row in set (0.00 sec)
[root@127.0.0.1][tpcc][09:14:47]> select count(0) from tpcc.customer;+----------+| count(0) |+----------+| 30000000 |+----------+1 row in set (0.02 sec)
复制代码
测试加索引速度
alter table tpcc.customer add index idx_01(c_city);alter table tpcc.customer add index idx_02(c_phone);alter table tpcc.customer add index idx_03(c_phone,c_since);
复制代码
默认：tidb_ddl_reorg_worker_cnt=4
[root@127.0.0.1][tpcc][09:19:36]> show variables like 'tidb_ddl_reorg_worker_cnt';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| tidb_ddl_reorg_worker_cnt | 4 |+---------------------------+-------+1 row in set (0.00 sec)
[root@127.0.0.1][tpcc][09:19:40]> alter table tpcc.customer add index idx_01(c_city);Query OK, 0 rows affected (1 min 19.36 sec)
[root@127.0.0.1][tpcc][09:21:03]> alter table tpcc.customer add index idx_02(c_phone);Query OK, 0 rows affected (1 min 19.49 sec)
[root@127.0.0.1][tpcc][09:23:51]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (1 min 16.22 sec)
复制代码
分别测试 tidb_ddl_reorg_worker_cnt=8、16、32
set global tidb_ddl_reorg_worker_cnt=8;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
set global tidb_ddl_reorg_worker_cnt=16;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
set global tidb_ddl_reorg_worker_cnt=32;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
复制代码
操作日志：
[root@127.0.0.1][tpcc][09:39:18]> set global tidb_ddl_reorg_worker_cnt=8;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][09:40:07]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.73 sec)
[root@127.0.0.1][tpcc][09:40:08]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (53.23 sec)
[root@127.0.0.1][tpcc][09:41:03]> set global tidb_ddl_reorg_worker_cnt=16;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][09:41:41]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.49 sec)
[root@127.0.0.1][tpcc][09:41:41]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (43.99 sec)
[root@127.0.0.1][tpcc][09:42:27]> set global tidb_ddl_reorg_worker_cnt=32;Query OK, 0 rows affected (0.24 sec)
[root@127.0.0.1][tpcc][09:43:24]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.68 sec)
[root@127.0.0.1][tpcc][09:43:25]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (38.04 sec)
复制代码
索引加速功能
执行 ADMIN SHOW DDL JOBS 语句查看 JOB_TYPE 一列中是否含有 ingest 字样
[root@127.0.0.1][tpcc][09:17:35]> admin show ddl jobs;+--------+---------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE |+--------+---------+------------+------------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------------------+---------+| 23464 | tpcc | customer | add index /* ingest */ | write reorganization | 23386 | 23447 | 25853098 | 2023-06-21 09:22:31 | 2023-06-21 09:22:32 | NULL | running || 23463 | tpcc | customer | add index /* ingest */ | public | 23386 | 23447 | 30000000 | 2023-06-21 09:19:44 | 2023-06-21 09:19:44 | 2023-06-21 09:21:03 | synced || 23462 | tpcc | customer | drop index | none | 23386 | 23447 | 0 | 2023-06-21 09:19:34 | 2023-06-21 09:19:34 | 2023-06-21 09:19:34 | synced || 23461 | tpcc | customer | add index /* ingest */ | public | 23386 | 23447 | 30000000 | 2023-06-21 09:16:40 | 2023-06-21 09:16:40 | 2023-06-21 09:17:44 | synced |
复制代码
相关参数：
tidb_ddl_enable_fast_reorgtidb_ddl_disk_quota
复制代码
在升级到 v6.5.0 及以上版本时，建议你检查 TiDB 的 temp-dir 路径是否正确挂载了 SSD 磁盘。该参数是 TiDB 的配置参数，设置后需要重启 TiDB 才能生效。因此，在升级前提前进行设置，可以避免再次重启。
使用快速索引功能，建索引的时候，数据会回填到 temp-dir 指定目录，过程中可以看到该目录空间在持续增长。
[tidb@host_130 tmp_ddl-4000]$ du -sh /tmp/tidb/tmp_ddl-4000/0 /tmp/tidb/tmp_ddl-4000/[tidb@host_130 tmp_ddl-4000]$ du -sh /tmp/tidb/tmp_ddl-4000/88M /tmp/tidb/tmp_ddl-4000/[tidb@host_130 tmp_ddl-4000]$ du -sh /tmp/tidb/tmp_ddl-4000/193M /tmp/tidb/tmp_ddl-4000/[tidb@host_130 tmp_ddl-4000]$ tree /tmp/tidb/tmp_ddl-4000//tmp/tidb/tmp_ddl-4000/└── 23464 ├── d4cacd68-ac06-558d-8dca-9bd229f322d2 │ ├── 000002.log │ ├── CURRENT │ ├── LOCK │ ├── MANIFEST-000001 │ └── OPTIONS-000003 └── d4cacd68-ac06-558d-8dca-9bd229f322d2.sst ├── 010ff049-964c-4f42-8880-47d69a44fede.sst ├── 078d2401-a319-4a62-928a-9af030faf742.sst ├── 2497f448-a7fe-4e6f-a9be-cea2bd4413c4.sst ├── 26ac2d83-2ca9-4962-80a3-2dcdf556e3f1.sst ├── 39549396-c9f2-4f06-8edf-7b325376e6a4.sst ├── 69be99c9-cc7c-42bf-97cc-f67547d72865.sst ├── 70b137b4-0fcf-4139-8c25-d03a4a1ef932.sst ├── 735fe13f-cf00-468c-91f9-2d9578064d60.sst ├── 7c50175e-f97f-49e5-8a5b-eb7ea4acbfb1.sst ├── 86e551b8-93fb-4862-82cb-a3a0c34ce280.sst ├── 913eef57-b90b-45df-b1d5-85ee52a95471.sst ├── 9d736cbb-fc5b-4df2-a203-aab03afdedea.sst ├── def3a75f-bee9-4e45-b1ae-a8085f7daa57.sst ├── e0151d63-ff16-4f09-946f-c18f35fb1e94.sst └── f32bbfe0-9672-4f25-a20f-af391e7c50d6.sst
3 directories, 20 files
复制代码
4、v7.1.0 测试 3kw 的表加索引
TiDB 版本和表信息
[root@127.0.0.1][tpcc][08:40:23]> select version();+--------------------+| version() |+--------------------+| 5.7.25-TiDB-v7.1.0 |+--------------------+1 row in set (0.00 sec)
[root@127.0.0.1][tpcc][08:40:42]> select count(0) from tpcc.customer;+----------+| count(0) |+----------+| 30000000 |+----------+1 row in set (4.00 sec)
复制代码
不同 worker 下，加索引速度对比
默认：tidb_ddl_reorg_worker_cnt=4
[root@127.0.0.1][tpcc][08:40:55]> set global tidb_ddl_reorg_worker_cnt=4;Query OK, 0 rows affected (0.04 sec)
[root@127.0.0.1][tpcc][08:41:20]> show variables like 'tidb_ddl_%';+--------------------------------+--------------+| Variable_name | Value |+--------------------------------+--------------+| tidb_ddl_disk_quota | 107374182400 || tidb_ddl_enable_fast_reorg | ON || tidb_ddl_error_count_limit | 512 || tidb_ddl_flashback_concurrency | 64 || tidb_ddl_reorg_batch_size | 256 || tidb_ddl_reorg_priority | PRIORITY_LOW || tidb_ddl_reorg_worker_cnt | 4 |+--------------------------------+--------------+7 rows in set (0.01 sec)
[root@127.0.0.1][tpcc][10:37:58]> alter table tpcc.customer add index idx_01(c_city);Query OK, 0 rows affected (1 min 8.36 sec)
[root@127.0.0.1][tpcc][10:29:31]> alter table tpcc.customer add index idx_02(c_phone);Query OK, 0 rows affected (1 min 7.93 sec)
[root@127.0.0.1][tpcc][10:36:00]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (1 min 7.64 sec)
复制代码
分别测试 tidb_ddl_reorg_worker_cnt=8、16、32
set global tidb_ddl_reorg_worker_cnt=8;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
set global tidb_ddl_reorg_worker_cnt=16;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
set global tidb_ddl_reorg_worker_cnt=32;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
复制代码
执行记录：
[root@127.0.0.1][tpcc][10:43:58]> set global tidb_ddl_reorg_worker_cnt=8;Query OK, 0 rows affected (0.03 sec)
[root@127.0.0.1][tpcc][10:44:00]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.47 sec)
[root@127.0.0.1][tpcc][10:44:01]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (44.50 sec)
[root@127.0.0.1][tpcc][10:44:47]> set global tidb_ddl_reorg_worker_cnt=16;Query OK, 0 rows affected (0.01 sec)
[root@127.0.0.1][tpcc][10:46:04]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.47 sec)
[root@127.0.0.1][tpcc][10:46:05]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (36.20 sec)
[root@127.0.0.1][tpcc][10:46:42]> set global tidb_ddl_reorg_worker_cnt=32;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][10:46:46]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.41 sec)
[root@127.0.0.1][tpcc][10:46:46]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (31.45 sec)
复制代码
不同 tidb_ddl_reorg_batch_size 下速度对比
tidb_ddl_reorg_batch_size 默认 256.
set global tidb_ddl_reorg_worker_cnt=4;
set global tidb_ddl_reorg_batch_size=512;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
set global tidb_ddl_reorg_batch_size=1024;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
set global tidb_ddl_reorg_batch_size=2048;alter table tpcc.customer drop index idx_03;alter table tpcc.customer add index idx_03(c_phone,c_since);
复制代码
操作日志：
[root@127.0.0.1][tpcc][05:23:46]> set global tidb_ddl_reorg_batch_size=512;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][05:23:53]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.44 sec)
[root@127.0.0.1][tpcc][05:23:54]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (1 min 5.60 sec)
[root@127.0.0.1][tpcc][05:25:01]> show variables like 'tidb_ddl_%';+--------------------------------+--------------+| Variable_name | Value |+--------------------------------+--------------+| tidb_ddl_disk_quota | 107374182400 || tidb_ddl_enable_fast_reorg | ON || tidb_ddl_error_count_limit | 512 || tidb_ddl_flashback_concurrency | 64 || tidb_ddl_reorg_batch_size | 512 || tidb_ddl_reorg_priority | PRIORITY_LOW || tidb_ddl_reorg_worker_cnt | 4 |+--------------------------------+--------------+7 rows in set (0.00 sec)
[root@127.0.0.1][tpcc][05:25:26]> set global tidb_ddl_reorg_batch_size=1024;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][05:25:31]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.47 sec)
[root@127.0.0.1][tpcc][05:25:32]> alter table tpcc.customer add index idx_03(c_phone,c_since);
Query OK, 0 rows affected (58.42 sec)
[root@127.0.0.1][tpcc][05:26:30]> [root@127.0.0.1][tpcc][05:26:30]> set global tidb_ddl_reorg_batch_size=2048;Query OK, 0 rows affected (0.02 sec)
[root@127.0.0.1][tpcc][05:26:57]> alter table tpcc.customer drop index idx_03;Query OK, 0 rows affected (0.42 sec)
[root@127.0.0.1][tpcc][05:26:58]> alter table tpcc.customer add index idx_03(c_phone,c_since);Query OK, 0 rows affected (56.39 sec)
复制代码
5、小结
从上面的数据我们可以看到，在 V6.5.0 版本中，默认 4 个 worker 下，建索引速度对比 v6.1.0 提升了约 20 倍，随着 worker 个数增加速度会进一步提升，但是越到后面提升越少，可能跟数据量有关系哈，测试结果仅供参考。
V7.1.0，相比 V6.5.0 也有一定提升，在 worker 为 8 和 16 的情况下，大约提升了 16%（(53-44)/53=0.1698，(43-36)/43=0.1628）。
另外，在 V7.1.0 下，测试了不同 tidb_ddl_reorg_batch_size 下的影响，也是会有一定提升。
以上就是在同一台机器上测试不同 TiDB 版本建索引速度的对比。V6.3 开始新增的索引加速功能确实提升非常大，还在用老版本的同学，快升级测试下吧。
划线
评论
复制
发布于: 4 小时前阅读数: 13
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/9a3f0ea278aa6360545a8cded】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论