暗夜零星
暗夜零星

MySQL添加索引是否会锁表呢？（Mysql Online DDL）

Eddie Eddie 收录于 性能调优 和 面试题
  约 2900 字 预计阅读 6 分钟  - 次阅读  

MySQL Online DDL（Data Definition Language，数据定义语言）是MySQL数据库提供的一项功能，允许在不中断数据库服务的情况下执行DDL操作。

定义与特点

定义：MySQL Online DDL指的是在执行DDL操作（如表结构的修改）的同时，允许对表进行正常的DML（Data Manipulation Language，数据操纵语言）操作（如INSERT、UPDATE、DELETE等），从而不阻塞表的读写操作。

特点：

  • 提高数据库的可用性和响应效率，减少DDL操作对业务的影响。
  • 适用于大数据量的表格，避免传统DDL操作锁定整个表导致无法读写的问题。

发展历程

  • MySQL 5.5及之前的版本：在进行DDL操作时，整个表都会被阻塞，无法进行DML操作，这在生产环境中通常是不能接受的。
  • MySQL 5.6版本：正式引入了Online DDL功能，支持DDL操作期间允许DML操作同时进行，尽量避免DDL过程中对业务SQL产生的阻塞。
  • MySQL 8.0版本：对DDL的实现重新进行了设计，支持了原子特性，并引入了INSTANT算法，使得某些DDL操作几乎瞬间完成，且不阻塞DML。

执行Online DDL时经历的阶段

在执行Online DDL时，无论采用哪种算法，一般都会经历以下三个阶段：

  1. 准备阶段（Prepare）：

    • 在此阶段，服务器会根据存储引擎的能力、语句中指定的操作以及用户指定的ALGORITHM和LOCK选项来确定操作期间允许的并发量。
    • 使用共享的元数据锁（MDL）来保护当前表定义，根据DDL的类型和指定的算法，可能会创建新的临时文件或进行其他准备工作。

  2. 执行阶段（DDL 语句执行）：

    • 在这一阶段，实际的DDL操作被执行。对于Online DDL，重要的是在此期间允许DML操作（如INSERT、UPDATE、DELETE）并发执行。
    • 根据采用的算法不同，执行阶段的具体逻辑也会有所不同。例如，Inplace算法可能会在原表上直接修改结构，而Copy算法则可能会将数据复制到新表中。

  3. 提交阶段（Commit）：

    • 在DDL操作完成后，进入提交阶段。此时，可能会将元数据锁升级为排他锁，以完成表定义的更新。
    • 对于涉及数据复制的算法（如Copy算法），在此阶段会完成数据的最终同步和旧表的清理工作。

实现原理

MySQL Online DDL通过不同的算法和策略来实现在线修改数据库结构的目标，主要包括Copy算法、Inplace算法和Instant算法（MySQL 8.0.12引入）：

  1. Copy算法：

    • 大体逻辑：按照原表定义创建一个新的临时表，对原表加写锁（禁止DML，允许SELECT），将原表中的数据逐行拷贝到新表中，然后在原表上执行DDL操作，最后将新表替换为原表名。
    • 特点：Copy算法在执行期间会阻塞DML操作，因此通常不是纯粹的Online DDL。它适用于需要大量结构修改且表数据较少的情况。

  2. Inplace算法：

    • 大体逻辑：在原表上直接执行DDL操作，无需创建临时表或拷贝全表数据。根据DDL操作的具体类型，Inplace算法可能会分为rebuild和no-rebuild两种方式。Rebuild方式需要重建表（如添加索引、添加/删除列等），而no-rebuild方式则只需修改表的元数据（如删除索引、修改列名等）。
    • 特点：Inplace算法减少了额外的存储空间和IO开销，但DDL操作期间可能会产生较大的锁、阻塞和性能开销。在Inplace算法的某些阶段（如准备和提交阶段），仍然可能需要加锁以保证数据的一致性。

  3. Instant算法：

    • 大体逻辑：MySQL 8.0.12引入的新算法，只需修改数据字典中的元数据，无需拷贝数据也无需重建整表。整个DDL过程几乎是瞬间完成的，也不会阻塞DML操作。
    • 特点：Instant算法极大地提高了DDL操作的效率和对业务的影响，但当前支持的操作范围较为有限，主要包括修改二级索引类型、新增/删除/重命名列、修改列默认值等。

是否锁表

Online DDL（在线数据定义语言）在MySQL中的行为是否会导致锁表，取决于多个因素，包括具体的DDL操作类型、MySQL的版本、以及是否使用了特定的算法（如INPLACE或COPY）。

INPLACE算法

  • 不锁表或短暂锁表：在大多数情况下，使用INPLACE算法的DDL操作不会显著影响对表的并发访问。MySQL会尽量减少锁的使用，以便在DDL操作期间允许DML操作继续进行。然而，在某些准备阶段或提交阶段，可能需要短暂地获取元数据锁（MDL），这可能会导致短暂的锁表现象。
  • 锁的类型：在Online DDL过程中，主要使用的锁是元数据锁（MDL）。MDL锁分为共享锁（允许并发读取）和排他锁（阻止其他读写操作）。DDL操作通常需要排他MDL锁来修改表结构，但在执行阶段，这个锁可能会被降级为共享锁，以便允许DML操作。

COPY算法

  • 会锁表：使用COPY算法的DDL操作会创建一个新表，将原表的数据复制到新表中，然后在新表上执行DDL操作。在这个过程中，原表通常会被锁定，以防止数据不一致。因此，这种操作会导致显著的锁表现象，影响对表的并发访问。

MySQL版本差异

  • 不同版本的MySQL在Online DDL的支持和锁表行为上可能存在差异。例如，MySQL 5.6引入了Online DDL功能，但在某些操作上可能仍然需要锁表。而MySQL 8.0及更高版本则提供了更完善的Online DDL支持，包括INSTANT算法，该算法在某些情况下可以几乎瞬间完成DDL操作，无需锁表或拷贝数据。

常用操作

MySQL Online DDL支持多种类型的DDL操作，包括但不限于：

  • 索引相关操作：新建索引、删除索引等。
  • 主键相关操作：增加主键、删除主键（注意，重建聚集索引通常需要Copy算法）。
  • 列相关操作：新增列、删除列、重命名列、设置/删除列的缺省值、修改列类型等。
  • 表相关操作：改变表的字符集、优化表（optimize table）等。

注意事项

  • 即使在执行Online DDL操作期间，某些阶段（如准备阶段和提交阶段）仍然可能需要加锁，因此可能会对DML操作产生短暂影响。
  • 对于大表的DDL操作，应评估其对主从复制延迟的影响，并考虑在低峰时段执行。
  • 在执行Online DDL操作之前，最好查阅MySQL的官方文档以了解具体的支持情况和限制。
  • 在高并发场景下，考虑在低峰时段执行DDL操作，以减少对业务的影响。

Online DDL Support

Mysql 5.7 Online DDL

Mysql 8.0 Online DDL

Mysql 5.7

Table 14.10 Online DDL Support for Index Operations

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Creating or adding a secondary index Yes No Yes No
Dropping an index Yes No Yes Yes
Renaming an index Yes No Yes Yes
Adding a FULLTEXT index Yes* No* No No
Adding a SPATIAL index Yes No No No
Changing the index type Yes No Yes Yes

Table 14.11 Online DDL Support for Primary Key Operations

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a primary key Yes* Yes* Yes No
Dropping a primary key No Yes No No
Dropping a primary key and adding another Yes Yes Yes No

其他更多操作参见Mysql 5.7 Online DDL

Mysql 8.0

Table 17.16 Online DDL Support for Index Operations

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Creating or adding a secondary index No Yes No Yes No
Dropping an index No Yes No Yes Yes
Renaming an index No Yes No Yes Yes
Adding a FULLTEXT index No Yes* No* No No
Adding a SPATIAL index No Yes No No No
Changing the index type Yes Yes No Yes Yes

Table 17.17 Online DDL Support for Primary Key Operations

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a primary key No Yes* Yes* Yes No
Dropping a primary key No No Yes No No
Dropping a primary key and adding another No Yes Yes Yes No

其他更多操作参见Mysql 8.0 Online DDL

收录于 合集・面试题 8
更新于 2024-07-13 4680d94
查看源码编辑此页报告问题
mysql online ddl
返回 | 主页
0%