mysql 修改约束会锁表吗

对于 MySQL 的表，修改约束（如添加、删除或修改主键、唯一键、外键等）有时会锁表。具体是否会锁表取决于执行的修改操作以及表的类型和当前的锁状态。

在 InnoDB 存储引擎中，添加或删除主键约束、外键约束或唯一键约束时，会使用“排他锁”（Exclusive Lock），这会导致其他事务无法读取或修改被锁定的表。这种情况下，修改约束会锁表。

然而，如果是修改非唯一键的约束（如修改列上的普通索引），则不会锁表，只会在修改索引时对相关行进行锁定。

需要注意的是，对于大型表或频繁进行写操作的表，修改约束可能需要一段时间来完成，期间可能会对其他事务的性能产生影响。

因此，在进行修改约束操作之前，建议先评估表的大小、使用情况以及对系统性能的影响，并在低峰期执行操作，以减少潜在的影响。此外，可以使用 ALTER TABLE 语句的 ALGORITHM 和 LOCK 参数来控制锁定行为，以满足具体需求。

相关问题

mysql 插入 锁表

### MySQL 插入操作引起锁表的原因

当执行 `INSERT` 操作时，MySQL 可能会因为多种原因导致锁定整个表。这通常发生在高并发环境下，多个事务试图同时修改同一张表的数据。具体来说：

- **元数据锁（Metadata Lock, MDL）**：在某些情况下，特别是涉及 DDL 或者长时间运行的查询时，可能会出现等待元数据锁的情况[^3]。

- **行级锁升级为表级锁**：如果大量行被加锁，在特定条件下 InnoDB 存储引擎可能将行级锁升級成表级别的锁。

### 解决方案

#### 1. 调整锁超时设置

通过降低 `lock_wait_timeout` 参数来减少因等待锁而导致的操作阻塞时间。默认情况下这个参数值非常大（一年），可以适当缩短至更合理的数值比如半小时：

SET GLOBAL lock_wait_timeout = 1800;

这样做能够使那些无法及时获得所需资源的任务更快失败并释放占用的连接和其他资源。

#### 2. 避免长事务

确保所有的数据库交互尽可能短小精悍，尤其是对于写入密集型应用而言更为重要。长时间未提交的事物不仅增加了发生冲突的概率也使得回滚成本更高。

#### 3. 使用批量插入代替单条记录插入

对于大批量数据加载场景下推荐采用批量方式而不是逐行插入。这样不仅可以提高效率还能有效减轻对共享资源的竞争压力。

INSERT INTO t (c,d) VALUES (1,1),(2,2),(3,3);

这种方式减少了每次插入之间所需的额外开销以及潜在产生的间隙锁数量[^4]。

#### 4. 合理设计索引结构

良好的索引策略有助于加速查找过程从而间接降低了持有排他锁的时间长度；另外还需注意避免不必要的唯一约束或其他可能导致热点争用的设计缺陷。

#### 5. 尽早释放不再使用的锁

应用程序层面应尽早完成必要的读取或更新动作之后立即结束当前事物以尽快解除所持有的任何类型的锁对象。

mysql 主键自增约束

### MySQL 中 AUTO_INCREMENT 主键的使用方法及其常见问题

#### 自动增量列的工作原理
当定义表结构时，可以指定某一列为 `AUTO_INCREMENT` 类型。这意味着每当向此表插入新记录而未提供特定值时，数据库会自动为这一字段生成唯一的递增值。

对于 `INSERT ... ON DUPLICATE KEY UPDATE` 的情况，在更新阶段可能会也可能不会使用预先分配给 `AUTO_INCREMENT` 列的数值[^2]。这主要依赖于具体的执行路径以及是否存在重复键冲突等情况。

#### 插入操作中的唯一性检查
如果尝试插入具有相同主键或其他唯一索引的数据行，则会发生错误。例如：

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(10),
    PRIMARY KEY(id)
);

INSERT INTO example VALUES (NULL,'b'), (101,'c');

上述命令无论 `innodb_autoinc_lock_mode` 参数如何配置都将触发编号为 23000 的异常——即无法写入；存在重复的关键字在表格内，因为 `(NULL, 'b')` 被赋予了自增值得到 101 后再试图加入另一条带有同样 ID 值的新纪录就会失败并抛出相应提示信息[^3]。

#### 控制并发性能与数据一致性
通过调整服务器变量 `innodb_autoinc_lock_mode` 可以影响 InnoDB 存储引擎处理批量插入请求的方式，从而达到优化性能的目的。不同的模式适用于不同场景下的需求平衡点。

- **传统锁定方式 (`innodb_autoinc_lock_mode = 0`)**

对每次涉及 `AUTO_INCREMENT` 字段的操作都加锁直到事务结束为止，确保绝对的安全性和准确性但牺牲了一定程度上的效率。

- **连续序列号分配策略 (`innodb_autoinc_lock_mode = 1`)**

此选项允许更高效的多线程环境下工作，它会在必要时候采取间隙锁来防止竞态条件的发生而不必完全阻塞其他进程访问资源。

- **无序批处理机制 (`innodb_autoinc_lock_mode = 2`)**

提供最高级别的吞吐量表现，不过可能导致某些情况下产生的ID不是严格按顺序排列的结果。

#### 动态元数据缓存行为
从 MySQL 8.0 版本起，默认启用了动态表元数据缓存功能。可以通过修改全局系统变量 `information_schema_stats` 来控制是从内存中读取还是每次都重新查询存储引擎获取最新状态。默认设置为 `cached` ，意味着大多数时间里应用程序看到的是最近一次快照而不是实时变化后的版本[^5]。