TiDB索引优化

动态调整创建索引的速度

观察系统压力

创建索引的时候通过Grafana的dashboard查看tidb的压力，当压力不大的时候可以适当通过上面的参数来调增创建的速度；

查看DDL进度

        ADMIN SHOW DDL JOBS;zeng

mysql> admin show ddl jobs;
+--------+---------+------------+--------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE     | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE  |
+--------+---------+------------+--------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
|    140 | test    | hero5      | drop table   | queueing     |         1 |      110 |         0 | 2022-01-23 18:27:42 | 2022-01-23 18:27:42 | synced |
|    139 | test    | hero4      | drop table   | queueing     |         1 |      107 |         0 | 2022-01-23 18:27:40 | 2022-01-23 18:27:41 | synced |
|    138 | test    | hero3      | drop table   | queueing     |         1 |      101 |         0 | 2022-01-23 18:27:38 | 2022-01-23 18:27:39 | synced |
|    137 | test    | hero2      | drop table   | queueing     |         1 |       99 |         0 | 2022-01-23 18:27:36 | 2022-01-23 18:27:37 | synced |
|    136 | test    | t2         | add index    | public       |         1 |      132 |     10000 | 2022-01-23 17:45:05 | 2022-01-23 17:45:11 | synced |
|    135 | test    | t1         | add index    | public       |         1 |      130 |     10000 | 2022-01-23 17:44:51 | 2022-01-23 17:44:58 | synced |
|    134 | test    | t1         | add index    | public       |         1 |      130 |     10000 | 2022-01-23 17:44:42 | 2022-01-23 17:44:47 | synced |
|    133 | test    | t2         | create table | public       |         1 |      132 |         0 | 2022-01-23 17:38:52 | 2022-01-23 17:38:52 | synced |
|    131 | test    | t1         | create table | public       |         1 |      130 |         0 | 2022-01-23 17:23:09 | 2022-01-23 17:23:09 | synced |
|    129 | test    | t          | drop table   | queueing     |         1 |      116 |         0 | 2022-01-23 17:22:52 | 2022-01-23 17:22:53 | synced |
+--------+---------+------------+--------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
10 rows in set (0.01 sec)

增加索引对线上业务的影响

读写场景下

 

只读场景下的影响

 没有读写场景下，修改并发度对业务的影响

 总结

TiDB 索引是Online DDL，不会锁表，但是回填数据的时候可能会造成锁冲突，从而影响业务性能； 如果数据库频繁更新（UPDATE/INSERT/DELETE）时，默认配置会造成较为频繁的些冲突，使在线负载较大； 

如果目标库只涉及查询负载，或者雨线上负载不直接相关的时候，可以直接使用默认配置；