A table having billions of rows. I'm using MySQL 5.7 When I'm trying to add an index to the table it takes very long time and it locks the table too. As a result, many queries wait in queue and server load increases.
I don't understand while adding an index to a table is creates table lock which is metadata lock. Can you please explain why is this happing and also provide a solution for the same.
CREATE TABLE
CREATE TABLE `user_log` (
`id` bigint(14) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`latitude` decimal(10,6) NOT NULL,
`longitude` decimal(10,6) NOT NULL,
`created_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=20000000000 DEFAULT CHARSET=latin1
ALTER COMMAND
ALTER TABLE `user_log` ADD INDEX (`user_id`, `created_date`);
Why mysql need lock table
Firstly, there are lots of DDL operations in mysql, such as alter table, drop table...These statements differed from DML operations such as insert,update, select.The DDL operations will change table schema,while DML operations just effect rows.
Secondly, there are multiple well-known locks in innodb, such as row lock, next-key lock.But MetaData lock very few people knows.The DDL operations will acquire exclusive MetaData lock to guarantee data consistency, which will block DML operations.For what situation the DDL operations will leading to data exception without MetaData lock,you can reference bug#989 .
How to DDL without block
Nowadays, mysql suport online ddl to do DDL operations without lock.
There are no magics in online ddl,which just create a new table with same structure, and operate DDL statement on new table,then rename new table with old table.
To add an index to a table without a lock resulting on UPDATE/ INSERT, the following statement format can be used:
ALTER TABLE my_table ADD INDEX my_table__idx (my_column), ALGORITHM=INPLACE, LOCK=NONE;