前言

“我正在参加「掘金·启航计划」”

🍊作者简介： 不肯过江东丶，一个来自二线城市的程序员，致力于用“猥琐”办法解决繁琐问题，让复杂的问题变得通俗易懂。

🍊支持作者： 点赞👍、关注💖、留言💌~

在我们的日常工作当中，经常会遇到需要修改数据库表结构的情况，比如增加字段、删除字段、修改字段类型，甚至是增加字段索引。尤其是针对千万级以上数据量的大表增加字段索引，如果处理不当就会往往会引发锁表的巨大隐患。特别是在生产环境中，一旦在变更表结构过程中，出现了长时间锁表，会导致用户产生的数据长时间无法正常变更到表中，进而导致服务功能异常，结果将是灾难性的。那么今天就来一起看看如何在不锁表的情况下完成大表增加索引的操作。

影子拷贝

不知道各位小伙伴有没有听说过“影子拷贝”这个名词，我第一次见到它还是在《高性能Mysql》中看到的。其实影子拷贝也非常好理解：

影子拷贝：先创建一张和原表无关的新表，然后通过改造新表，再对其进行重命名的操作交换两张表即可。

我们以大表增加索引为例，写一下影子拷贝的几个步骤 👇

// 操作步骤：
// 1、创建一张和原表结构一样的空表
    create table 新表名 like 原表名;
 
// 2、把新建的空表非主键索引都删掉，因为这样在往新表导数据的时候效率会很快
    alter 新表名 drop index 索引名;
 
// 3、从旧表往新表里导数据，如果数据太大，建议选择分批导入，只需确保无重复数据即可。如果一次性导入大量数据（如千万级），则会可能因为导入数据太大，而影响原表的线上业务。
    insert into 新表名 select * from 原表名 where id between start_id and end_id;
 
// 4、数据导完后，再对新表进行添加索引
     create index 索引名 on 新表名(字段名);
 
// 5、当索引也建立完成后，由于线上的业务并未停止，所以原表中数据量还是会继续增长，这时候为了确保新表和原表的数据一至性和平滑切换，建议写一个脚本，判断当旧表的数据行数与新表一致时就进行切换。即将新表与旧表的表名进行切换。
     table 原表名 to 原表名_bak;
     table 新表名 to 原表名;

无锁增加索引

我们不能直接操作生产环境的数据库表，是因为如果处理不当就会往往会引发锁表，从而导致线上业务无法正常运行而引发生产事故。其实在 MySQL 5.6版本以后，就给我们提供了一个无锁增加索引的方案 👇

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

小结

本人经验有限，有些地方可能讲的没有特别到位，如果您在阅读的时候想到了什么问题，欢迎在评论区留言，我们后续再一一探讨🙇‍

希望各位小伙伴动动自己可爱的小手，来一波点赞+关注 (✿◡‿◡) 让更多小伙伴看到这篇文章~ 蟹蟹呦(●'◡'●)

如果文章中有错误，欢迎大家留言指正；若您有更好、更独到的理解，欢迎您在留言区留下您的宝贵想法。

爱你所爱行你所行听从你心无问东西

大聪明教你学Java | 带你学会如何给 MySQL 千万级大表增加索引

前言

影子拷贝

无锁增加索引

小结