mysql create table as select from_为什么要避免使用“CREATE TABLE AS SELECT”语句

This statement is waiting for the metadata lock:此语句正在等待元数据锁：

**会话3:**

mysql> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------

| Id | User | Host | db | Command | Time | State | Info

+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------

| 2 | root | localhost | test | Query | 18 | Sending data | create table test2 as select * from test1

| 3 | root | localhost | test | Query | 7 | Waiting for table metadata lock | select * from test2 limit 10

| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist

+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------

The same can happen another way: a slow select query can prevent some DDL operations (i.e., rename, drop, etc.):

同样地，可以采用另一种方式：慢查询可以阻塞某些DDL操作(即重命名，删除等)：

mysql> show processlistG

*************************** 1. row ***************************

Id: 4

User: root

Host: localhost

db: reporting_stage

Command: Query

Time: 0

State: NULL

Info: show processlist

Rows_sent: 0

Rows_examined: 0

Rows_read: 0

*************************** 2. row ***************************

Id: 5

User: root

Host: localhost

db: test

Command: Query

Time: 9

State: Copying to tmp table

Info: select count(*), name from test2 group by name order by cid

Rows_sent: 0

Rows_examined: 0

Rows_read: 0

*************************** 3. row ***************************

Id: 6

User: root

Host: localhost

db: test

Command: Query

Time: 5

State: Waiting for table metadata lock

Info: rename table test2 to test4

Rows_sent: 0

Rows_examined: 0

Rows_read: 0

3 rows in set (0.00 sec)

As we can see, CREATE TABLE AS SELECT can affect other queries. However, the problem here is not the metadata lock itself (the metadata lock is needed to preserve consistency). The problem is that the

***metadata lock will not be released until the statement is finished***.

我们可以看到，CREATE TABLE AS SELECT可以影响其他查询。但是，这里的问题不是元数据锁本身(需要元数据锁来保持一致性)。问题是

***在语句完成之前不会释放元数据锁***。

The fix is simple: copy the table structure first by doing “create table new_table like old_table”, then do “insert into new_table select …”. The metadata lock is still held for the create table part (very short), but isn’t for the “insert … select” part (the total time to hold the lock is much shorter). To illustrate the difference, let’s look at two cases:

1. With “create table table_new as select … from table1“, other application connections can’t read from the destination table (table_new) for the duration of the statement (even “show fields from table_new” will be blocked)

2. With “create table new_table like old_table” + “insert into new_table select …”, other application connections can’t read from the destination table during the “insert into new_table select …” part.

修复很简单：首先复制表结构，执行“ create table new_table like old_table”，然后执行“insert into new_table select ...”。元数据锁仍然在创建表部分(非常短)持有，但“insert … select”部分不会持有(保持锁定的总时间要短得多)。为了说明不同之处，让我们看看以下两种情况：

使用“create table table_new as select ... from table1 ”，其他应用程序连接 在语句的持续时间内 无法读取目标表(table_new)(甚至“show fields from table_new”将被阻塞)

使用“create table new_table like old_table”+“insert into new_table select ...”，在“insert into new_table select ...”这部分期间，其他应用程序连接无法读取目标表。

In some cases, however, the table structure is not known beforehand. For example, we may need to materialize the result set of a complex select statement, involving joins and/or group by. In this case, we can use this trick:

然而，在某些情况下，表结构事先是未知的。例如，我们可能需要物化复杂select语句的结果集，包括joins、and/or、group by。在这种情况下，我们可以使用这个技巧：

create table new_table as select ... join ... group by ... limit 0;

insert into new_table as select ... join ... group by ...

The first statement creates a table structure and doesn’t insert any rows (LIMIT 0). The first statement places a metadata lock. However, it is very quick. The second statement actually inserts rows into the table and doesn’t place a metadata lock.

第一个语句创建一个表结构，不插入任何行(LIMIT 0)。第一个语句持有元数据锁。但是，它非常快。第二个语句实际上是在表中插入行，而不持有元数据锁。

END

知

数

堂

这里有好课，为职场助攻

免费兑换知数堂的课程和周边，戳此了解

扫码加入MySQL技术Q群

(群号：529671799)返回搜狐，查看更多