亿级表线上无锁添加索引：加索引的语句不加锁,也可以改表

发表评论
3,826

A+

所属分类：数据库

注意：

需求 mysql版本最好5.7以上

只能通过ALTER TABLE不能create index

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

1	ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

参数说明：

ALGORITHM=INPLACE
更优秀的解决方案，在当前表加索引，步骤：
1.创建索引(二级索引)数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插
入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束

ALGORITHM=COPY
通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
1.新建带索引（主键索引）的临时表
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename，升级字典锁
5.完成创建索引操作

LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操
作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

ALGORITHM=INPLACE

更优秀的解决方案，在当前表加索引，步骤：

1.创建索引(二级索引)数据字典

2.加共享表锁，禁止DML，允许查询

3.读取聚簇索引，构造新的索引项，排序并插

入新索引

4.等待打开当前表的所有只读事务提交

5.创建索引结束

ALGORITHM=COPY

通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：

1.新建带索引（主键索引）的临时表

2.锁原表，禁止DML，允许查询

3.将原表数据拷贝到临时表

4.禁止读写,进行rename，升级字典锁

5.完成创建索引操作

LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表

LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操

作不支持对表的继续写入，则DDL操作失败，对表修改无效

LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取

LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

实战

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7995759
Server version: 5.7.25-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use sdorica_exp

MySQL [sdorica_exp]> show index from gashapon_outcome_records;
Empty set (0.00 sec)

MySQL [sdorica_exp]> select count(1) from gashapon_outcome_records;
+-----------+
| count(1)  |
+-----------+
| 111579926 |
+-----------+
1 row in set (1 min 10.13 sec)

MySQL [sdorica_exp]> ALTER TABLE gashapon_outcome_records ADD INDEX idx_roll_gashapon_record_id (roll_gashapon_record_id) , ALGORITHM=INPLACE, LOCK=NONE;


Query OK, 0 rows affected (15 min 34.16 sec)
Records: 0  Duplicates: 0  Warnings: 0


MySQL [sdorica_exp]> show index from gashapon_outcome_records;
+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                    | Non_unique | Key_name                    | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| gashapon_outcome_records |          1 | idx_roll_gashapon_record_id |            1 | roll_gashapon_record_id | A         |    51825872 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

MySQL [sdorica_exp]>

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 7995759

Server version: 5.7.25-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use sdorica_exp

MySQL [sdorica_exp]> show index from gashapon_outcome_records;

Empty set (0.00 sec)

MySQL [sdorica_exp]> select count(1) from gashapon_outcome_records;

+-----------+

| count(1) |

+-----------+

| 111579926 |

+-----------+

1 row in set (1 min 10.13 sec)

MySQL [sdorica_exp]> ALTER TABLE gashapon_outcome_records ADD INDEX idx_roll_gashapon_record_id (roll_gashapon_record_id) , ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (15 min 34.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

MySQL [sdorica_exp]> show index from gashapon_outcome_records;

+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+