MySQL 5.7使用pt-online-schema-change對大表加索引

業務需求：

線上對MySQL 5.7的一個大表加索引。

普通加索引方式，適合於小表：

ALTER TABLE `EMP`

ADD INDEX IDX_EMP_ORDERSYSNO (ORDERSYSNO) USING BTREE;

對大表需要使用pt-online-schema-change工具，可以不鎖表。

加組合欄位索引：

[root@DB02 ~]# pt-online-schema-change --user=root --password=123456 --host=192.168.1.18  --alter "ADD INDEX IDX_DOSTELLE_ST (SEGMENT,transaction) USING BTREE" D=ora,t=dostelle --no-check-replication-filters  --execute --charset=utf8  --max-load=Threads_running=20

加單一欄位索引：

[root@DB001 ~]# pt-online-schema-change --user=root --password=XXX --host=192.168.1.98  --alter "ADD INDEX IDX_ORDERID (ORDERID) USING BTREE" D=ora,t=idemp --no-check-replication-filters  --execute --charset=utf8  --max-load=Threads_running=20

Found 1 slaves:

localhost.localdomain -> 192.168.1.99:socket

Will check slave lag on:

localhost.localdomain -> 192.168.1.99:socket

Operation, tries, wait:

  analyze_table, 10, 1

  copy_rows, 10, 0.25

  create_triggers, 10, 1

  drop_triggers, 10, 1

  swap_tables, 10, 1

  update_foreign_keys, 10, 1

Altering `ora`.`idemp`...

Creating new table...

Created new table ora._idemp_new OK.

Altering new table...

Altered `ora`.`_idemp_new` OK.

2021-02-01T11:33:58 Creating triggers...

2021-02-01T11:34:40 Created triggers OK.

2021-02-01T11:34:40 Copying approximately 6915514 rows...

Copying `ora`.`idemp`:  11% 04:01 remain

Copying `ora`.`idemp`:  20% 03:46 remain

Copying `ora`.`idemp`:  31% 03:18 remain

Copying `ora`.`idemp`:  41% 02:49 remain

Copying `ora`.`idemp`:  50% 02:25 remain

Copying `ora`.`idemp`:  60% 01:59 remain

Copying `ora`.`idemp`:  70% 01:29 remain

Copying `ora`.`idemp`:  80% 00:58 remain

Copying `ora`.`idemp`:  90% 00:29 remain

Copying `ora`.`idemp`:  99% 00:00 remain

2021-02-01T11:50:59 Copied rows OK.

2021-02-01T11:50:59 Analyzing new table...

2021-02-01T11:50:59 Swapping tables...

2021-02-01T11:51:13 Swapped original and new tables OK.

2021-02-01T11:51:13 Dropping old table...

2021-02-01T11:51:14 Dropped old table `ora`.`_idemp_old` OK.

2021-02-01T11:51:14 Dropping triggers...

2021-02-01T11:51:14 Dropped triggers OK.

Successfully altered `ora`.`idemp`.

You have mail in /var/spool/mail/root