记录一次生产环境更新 （Mysql 大表的）索引

青衣画白扇
1,153 阅读3分钟

这是我参与「掘金日新计划 · 8 月更文挑战」的第28天，点击查看活动详情

前言

在实际生产环境中，很多时候我们都只能热更新，不能冷更新当然肯定也不能锁表锁库。再生产环境中当单表数据量上来之后，我们要维护索引添加 有大概率会造成锁表的。锁表之后就芭比Q了（只能去查看进程杀掉）这一次更新也算是get到了一个 新的 方式 下面我们在复盘一下操作（在本地环境中）

方法一

我们将需要添加索引或者修改索引的表备份出来 ，在数据库中创建新的表结构（就是将表的结构索引什么的都创建好）然后将备份的数据导入到新表中导入（数据量大的时候可能会慢点但是 靠谱） 当数据导入完成之后，先将旧表的表明改为 name01 然后讲我们的新表表名改为 name 这回就可以看到一切顺利 完成了索引的更新虽然比较麻烦但是会避免 锁表 。
mysql 版本查看

mysql> select version();
+----------+
| version() |
+----------+
| 5.7.37-log |
+----------+
1 row in set (0.04 sec)

查看当前运行中的锁以及事务

innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系
select * from information_schema.innodb_locks

测试表数据查看

select count(id) FROM test_data

image.png image.png 上图：

1663643825316.png 可以看到我们修改表名的时候并没有任何锁

方法二

添加索引时 设置 ALGORITHM 和 LOCK参数

ALTER TABLE test002.test_data002 ADD INDEX index_type (type) , ALGORITHM=INPLACE, LOCK=NONE;

执行sql： 467c9a2c8cb00e2683ce8d67b353ca5.png 查询锁：

1661423971164.png

发现并没有锁表。
ALGORITHM ：

  1. INPLACE ALGORITHM=INPLACE可用于对表进行在线 ADD COLUMN、ADD INDEX （包括CREATE INDEX语句）和 DROP INDEX操作 [NDB]，[NDB]还支持 在线重命名 表，加共享锁，禁止DML操作 允许查询 等待打开表的所有只读事务提交。
  2. COPY
  • 告诉数据节点创建表的空副本，并对该副本进行所需的架构更改。
  • 从原始表中读取行，并将它们写入副本。
  • 告诉数据节点删除原始表，然后重命名副本。 我们有时将其称为“复制”或 “离线” ALTER TABLE。DML 操作不允许与复制同时进行 ALTER TABLE。 发出复制语句 的 ALTER TABLE采用元数据锁，但这仅在该[**mysqld**]其他 NDB客户端可以在复制过程中修改行数据ALTER TABLE，导致不一致。对于ALGORITHM=INPLACE，NDB Cluster 处理程序告诉数据节点进行所需的更改，并且不执行任何数据复制。我们也将此称为“非复制”或 “在线” ALTER TABLE。非复制ALTER TABLE`允许并发 DML 操作。

LOCK：

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

参考： mysql 官方文档
实践是检验真理的唯一准则，感兴趣的可以去试试呀！明天见咯 😃😃😃😃

avatar
文章
阅读
粉丝