加速TiDB DDL速度

2022-05-24 张天师发布于所有文章 366 字 1 分钟

执行DDL

虽然TiDB的DDL操作都是Online DDL，不会堵塞DML操作，但是不代表立刻可以完成

alter table ying99_fundtxn.fund_order add KEY `FUND_ORDER_CUST_NO` (`account_id`);

虽然执行DDL SQL后，看起来操作已经被卡住是同步执行的，CTRL + C也没办法终止进程，其实任务已经提交到DDL Queue中，即便你把终端直接关闭，也不会导致DDL被误删。如果想取消正在执行的DDL的话，可以参考官方文档，使用命令:
ADMIN CANCEL DDL JOBS job_id [, job_id] ...;

查看DDL进度

使用admin show ddl/admin show ddl jobs查看

mysql> admin show ddl\G
*************************** 1. row ***************************
   SCHEMA_VER: 371
     OWNER_ID: 0c47830f-1771-47a7-89f8-adf90e21516f
OWNER_ADDRESS: 172.18.243.12:4000
 RUNNING_JOBS: ID:468, Type:add index, State:running, SchemaState:write reorganization, SchemaID:54, TableID:163, RowCount:136391501, ArgLen:0, start time: 2022-05-24 18:06:52.265 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:433425432593039364
      SELF_ID: 5cac3bba-1e52-4af7-8ee2-191bb62bee2f
        QUERY: alter table ying99_fundtxn.fund_order add KEY `FUND_ORDER_CUST_NO` (`account_id`)
1 row in set (0.01 sec)

mysql> admin show ddl jobs 1;
+--------+----------------+----------------------+----------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------+
| JOB_ID | DB_NAME        | TABLE_NAME           | JOB_TYPE             | SCHEMA_STATE         | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE   |
+--------+----------------+----------------------+----------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------+
|    468 | ying99_fundtxn | fund_order           | add index            | write reorganization |        54 |      163 | 136811054 | 2022-05-24 18:06:52 | NULL                | running |
|    467 | ying99_fundtxn | wallet_daily_balance | modify table comment | public               |        54 |      464 |         0 | 2022-05-12 20:53:25 | 2022-05-12 20:53:25 | synced  |
+--------+----------------+----------------------+----------------------+----------------------+-----------+----------+-----------+---------------------+---------------------+---------+
2 rows in set (0.01 sec)

mysql>

调整DDL速度

参考

TiDB DDL速度相关参数