MySQL新增字段或索引会不会锁表

MySQL 中，新增字段或索引是否锁表，是一个复杂的问题，因为它取决于多种因素，包括 MySQL 的版本、存储引擎、具体的 DDL（数据定义语言）操作类型以及表的数据量和并发事务等。以下是对这一问题的深入讨论：

一、新增字段时的锁表情况

1. 存储引擎的影响：

   • MyISAM：MyISAM 存储引擎在新增字段时，通常会锁定整个表，直到操作完成。这意味着在添加字段的过程中，其他对该表的读写操作都会被阻塞。
   • InnoDB：InnoDB 存储引擎在新增字段时的行为相对复杂。在 MySQL 5.6 及更早版本中，InnoDB 在添加字段时也可能锁定整个表。但从 MySQL 5.7 开始，InnoDB 引入了即时 DDL 功能，允许在不锁表的情况下添加某些类型的字段（例如，添加非空且带有默认值的列）。然而，对于某些复杂的字段添加操作（例如，添加没有默认值的非空列），仍然可能需要锁表。在 MySQL 8.0 中，即时 DDL 功能得到了进一步的增强，但仍然有一些限制。

2. 操作类型的影响：

   • 简单的字段添加操作（如添加非空且带有默认值的列）在 InnoDB 中可能不会锁表。
   • 复杂的字段添加操作（如添加没有默认值的非空列）可能需要锁表，因为 MySQL 需要对每一行数据进行修改以添加新的字段。

3. 数据量和并发事务的影响：

   • 当表中有大量数据时，新增字段的操作可能需要更长时间，从而增加锁表的可能性。
   • 如果在新增字段的过程中有其他事务正在对表进行读写操作，这些事务可能会被阻塞，直到新增字段的操作完成。

二、新增索引时的锁表情况

1. 存储引擎的影响：

   • MyISAM：MyISAM 在新增索引时，会锁定整个表，直到索引创建完成。
   • InnoDB：InnoDB 在新增索引时，通常使用在线 DDL 功能，允许在添加索引的过程中表仍然可以被读写。然而，这并不意味着完全没有锁。InnoDB 可能会在某些阶段获取短暂的元数据锁（metadata lock），并且在索引创建过程中，可能会有一些性能影响，但通常不会导致长时间的锁表。

2. 操作类型的影响：

   • 在 InnoDB 中，新增普通索引（如 B-tree 索引）通常不会导致长时间的锁表。
   • 新增全文索引或空间索引等复杂索引类型可能需要更长时间的锁表。

3. 并发事务的影响：

   • 如果在新增索引的过程中有其他写操作在执行，MySQL 可能会将该操作提升为表级锁，从而阻塞其他写操作。
   • 读取操作通常不会被阻塞，但在索引创建过程中，读取性能可能会受到影响。

三、减少锁表影响的策略

1. 使用在线 DDL：在 MySQL 5.6 及以上版本中，可以使用 ALGORITHM=INPLACE 选项来执行在线 DDL 操作，以减少锁表时间。
2. 分批处理：如果表中的数据量非常大，可以考虑分批进行新增字段或索引的操作，每次处理一部分数据。
3. 低峰期操作：选择在数据库访问量较低的时段进行新增字段或索引的操作，以减少对业务的影响。
4. 使用临时表：可以先创建一个与原表结构相同的临时表，将原表的数据导入临时表，然后在临时表上进行新增字段或索引的操作，最后将临时表重命名为原表。
5. 监控和备份：在进行任何结构更改之前，最好先在测试环境中进行验证，并确保有最新的备份。同时，监控数据库的性能以确保操作不会对生产环境造成不可接受的影响。

总结，MySQL 新增字段或索引是否会锁表取决于多种因素。为了最小化锁表的影响，可以采取上述策略来优化操作过程。