美文网首页
亿级Mysql线上无锁添加索引,也可以改表

亿级Mysql线上无锁添加索引,也可以改表

作者: lyy910203 | 来源:发表于2020-10-21 11:06 被阅读0次

    只能通过ALTER TABLE不能create index

    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操作期间不允许对锁表进行任何操作
    

    给1E数据库在线加索引

    数据库在线加索引
    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]> 
    

    相关文章

      网友评论

          本文标题:亿级Mysql线上无锁添加索引,也可以改表

          本文链接:https://www.haomeiwen.com/subject/nxmsmktx.html