时间戳

首页 > 解决方案 > MySQL ALTER TABLE 在小表中花费很长时间

mysql - MySQL ALTER TABLE 在小表中花费很长时间

问题描述

我的场景中有两张桌子

table1, which has about 20 tuples
table2, which has about 3 million tuples

table2 has a foreign key referencing table1 "ID" column.

当我尝试执行以下查询时：

ALTER TABLE table1 MODIFY vccolumn VARCHAR(1000);

它需要永远。为什么要花这么长时间？我读过它不应该，因为它只有 20 个元组。

有没有办法在不停机的情况下加快速度？因为查询也锁定了表。

标签： mysqlquery-optimization

解决方案

我猜 ALTER TABLE 正在等待元数据锁，它实际上并没有开始改变任何东西。

什么是元数据锁？

当您对表运行任何查询（如 SELECT/INSERT/UPDATE/DELETE）时，它必须获取元数据锁。这些查询不会相互阻止。该类型的任意数量的查询都可以具有元数据锁。

但是像 CREATE/ALTER/DROP/TRUNCATE/RENAME 或事件 CREATE TRIGGER 或 LOCK TABLES 之类的 DDL 语句必须获取独占元数据锁。如果任何事务仍持有元数据锁，则 DDL 语句将等待。

你可以证明这一点。打开两个终端窗口，在每个窗口中打开mysql客户端。

  • 窗口 1：CREATE TABLE foo ( id int primary key );
  • 窗口 1：START TRANSACTION;

  • 窗口1：SELECT * FROM foo;——表无数据无所谓

  • 窗口 2: DROP TABLE foo;-- 注意它在等待

  • 窗口 1：SHOW PROCESSLIST;

    +-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+
| Id  | User | Host      | db   | Command | Time | State                           | Info             | Rows_sent | Rows_examined |
+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+
| 679 | root | localhost | test | Query   |    0 | starting                        | show processlist |         0 |             0 |
| 680 | root | localhost | test | Query   |    4 | Waiting for table metadata lock | drop table foo   |         0 |             0 |
+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

可以看到 drop table 正在等待 table 元数据锁。只是等待。还要等多久？直到窗口 1 中的事务完成。最终它将在几秒钟后超时lock_wait_timeout（默认情况下，设置为1 year）。

  • 窗口 1：COMMIT;

  • 窗口 2：注意它停止等待，并立即丢弃表。

所以，你可以做什么？确保没有长时间运行的事务阻塞您的 ALTER TABLE。即使是之前对您的表运行快速 SELECT 的事务也将保持其元数据锁定，直到事务提交。

推荐阅读