Steps

You can only use ALTER TABLE, not CREATE INDEX

ALTER TABLE tbl_name ADD INDEX (column), ALGORITHM=INPLACE, LOCK=NONE;

Parameter Notes

ALGORITHM=INPLACE A better solution that adds the index on the current table. The steps are:

1. Create the secondary index data dictionary
2. Acquire a shared table lock, block DML, allow queries
3. Read the clustered index, build new index entries, sort them, and insert them
4. Wait for all read-only transactions on the current table to finish
5. Index creation completes

ALGORITHM=COPY Create the index through a temporary table. It requires about double the storage and more I/O. The steps are:

1. Create a temporary table with indexes (primary key index)
2. Lock the original table, block DML, allow queries
3. Copy data from the original table to the temporary table
4. Disable reads and writes, perform rename, and upgrade the dictionary lock
5. Finish creating the index

LOCK=DEFAULT: Default mode. MySQL decides which LOCK mode to use and tries not to lock the table. LOCK=NONE: No lock. Concurrent reads and writes are allowed during Online DDL. If the DDL operation does not support continued writes to the table, the DDL fails and the table is left unchanged. LOCK=SHARED: Shared lock. Writes are blocked during Online DDL, but reads are not affected. LOCK=EXCLUSIVE: Exclusive lock. No operations are allowed on the table during Online DDL.

Source

https://dev.mysql.com/doc/refman/8.0/en/create-index.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes https://stackoverflow.com/questions/4244685/create-an-index-on-a-huge-mysql-production-table-without-table-locking https://www.jianshu.com/p/81d7d7ed6d86