MySQL ALTER TABLE在小表中占用很长时间 [英] MySQL ALTER TABLE taking long in small table

问题描述

我的场景中有两个表

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

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

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

When I try to execute the following query:

ALTER TABLE table1 MODIFY vccolumn VARCHAR(1000);

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

It takes forever. Why is it taking that long? I have read that it should not, because it only has 20 tuples.

是否有任何方法可以在不导致服务器停机的情况下加快速度?因为查询也在锁定表.

Is there any way to speed it up without having server downtime? Because the query is locking the table, also.

推荐答案

我想ALTER TABLE正在等待元数据锁定，并且实际上并没有开始更改任何内容.

I would guess the ALTER TABLE is waiting on a metadata lock, and it has not actually starting altering anything.

什么是元数据锁定?

对表运行任何查询(如SELECT/INSERT/UPDATE/DELETE)时，它必须获取元数据锁.这些查询不会互相阻塞.该类型的任何数量的查询都可以具有元数据锁定.

When you run any query like SELECT/INSERT/UPDATE/DELETE against a table, it must acquire a metadata lock. Those queries do not block each other. Any number of queries of that type can have a metadata lock.

但是DDL语句(例如CREATE/ALTER/DROP/TRUNCATE/RENAME或事件CREATE TRIGGER或LOCK TABLES)必须获得排他元数据锁.如果任何事务仍然拥有元数据锁，则DDL语句将等待.

But a DDL statement like CREATE/ALTER/DROP/TRUNCATE/RENAME or event CREATE TRIGGER or LOCK TABLES, must acquire an exclusive metadata lock. If any transaction still holds a metadata lock, the DDL statement waits.

您可以证明这一点.打开两个终端窗口，然后在每个窗口中打开mysql客户端.

You can demonstrate this. Open two terminal windows and open the mysql client in each window.

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

• 窗口1:SELECT * FROM foo;-表格没有数据没关系

• Window 1: CREATE TABLE foo ( id int primary key );
• Window 1: START TRANSACTION;

• Window 1: SELECT * FROM foo; -- it doesn't matter that the table has no data

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

Window 2: DROP TABLE foo; -- notice it waits

窗口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 |
+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

您可以看到放置表正在等待表元数据锁定.等一下等待多长时间?直到窗口1中的事务完成为止.最终它将在lock_wait_timeout秒后超时(默认情况下，此时间设置为 1年).

You can see the drop table waiting for the table metadata lock. Just waiting. How long will it wait? Until the transaction in window 1 completes. Eventually it will time out after lock_wait_timeout seconds (by default, this is set to 1 year).

• 窗口1:COMMIT;

窗口2:请注意，它停止等待，并立即放下 表.

Window 2: Notice it stops waiting, and it immediately drops the table.

那你该怎么办?确保没有长时间运行的事务阻塞您的ALTER TABLE.即使是对您的表进行了快速SELECT的事务，也将保持其元数据锁定，直到该事务提交为止.

So what can you do? Make sure there are no long-running transactions blocking your ALTER TABLE. Even a transaction that ran a quick SELECT against your table earlier will hold its metadata lock until the transaction commits.

这篇关于MySQL ALTER TABLE在小表中占用很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！