2023年6月23日发(作者：)

为什么要避免使⽤“CREATETABLEASSELECT”语句导读作者：Alexander Rubin翻译团队：星耀队（芬达、刘莉、Ziroro、M哥、顺⼦）关键词：create table as select, metadata locks, MySQL, open source database, row locking, table locking适⽤范围：Insight for DBAs, MySQLIn this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement.在这篇博⽂中，我将解释为什么你应该避免使⽤CREATE TABLE AS SELECT语句。The SQL statement “create table as select …” is used to create a normal or temporary table andmaterialize the result of the select. Some applications use this construct to create a copy of the table. This is onestatement that will do all the work, so you do not need to create a table structure or use another statement to copy 语句“create table as select ...”⽤于创建普通表或临时表，并物化select的结果。某些应⽤程序使⽤这种结构来创建表的副本。⼀条语句完成所有⼯作，因此您⽆需创建表结构或使⽤其他语句来复制结构。At the same time there are a number of problems with this statement:1. You don’t create indexes for the new table2. You are mixing transactional and non-transactional statements in one transaction. As with any DDL, it will commitcurrent and unfinished transactions3. CREATE TABLE … SELECT is not supported when using GTID-based replication4. Metadata locks won’t release until the statement is finished与此同时，这种语句存在许多问题：1. 您不为新表创建索引2. 您在⼀个事务中混合了事务性和⾮事务性语句时，与任何DDL⼀样，它将提交当前和未完成的事务3. 使⽤基于GTID的复制时不⽀持 CREATE TABLE ... SELECT4. 在语句完成之前，元数据锁不会释放CREATE TABLE AS SELECT语句可以把事物变得很糟糕Let’s imagine we need to transfer money from one account to another (classic example). But in addition to justtransferring funds, we need to calculate fees. The developers decide to create a table to perform a complex the transaction looks like this:让我们想象⼀下，我们需要将钱从⼀个账户转移到另⼀个账户（经典⽰例）。但除了转移资⾦外，我们还需要计算费⽤。开发⼈员决定创建⼀个表来执⾏复杂的计算。然后事务看起来像这样：sqlbegin;update accounts set amount = amount - 100000 where account_id=123;-- now we calculate feescreate table as select ... join ...update accounts set amount = amount + 100000 where account_id=321;commit;The “create table as select … join … ” commits a transaction that is not safe. In case of an error, the second accountobviously will not be credited by the second account debit that has been already committed!Well, instead of “create table … “, we can use “create temporary table …” which fixes the issue, as temporary tablecreation is allowed."create table as select ... join ..."会提交⼀个事务，这是不安全的。如果出现错误，第⼆个帐户显然不会被已经提交的第⼆个帐户借记贷记！好吧，我们可以使⽤"create temporary table …"来修复问题，⽽不是"create table … "，因为允许临时表创建。GTID问题If you try to use CREATE TABLE AS SELECT when GTID is enabled (and ENFORCE_GTID_CONSISTENCY = 1)you get this error:sqlGeneral error: 1786 CREATE TABLE ... SELECT is forbidden when@@E_GTID_CONSISTENCY = application code may break.如果在启⽤GTID时尝试使⽤CREATE TABLE AS SELECT（并且ENFORCE_GTID_CONSISTENCY = 1），则会出现此错误：sqlGeneral error: 1786 CREATE TABLE ... SELECT is forbidden when@@E_GTID_CONSISTENCY = 1.应⽤程序代码可能会中断。元数据锁问题This quick simulation demonstrates metadata lock:以下速模拟演⽰了元数据锁定：以下速模拟演⽰了元数据锁定：**会话1:**•mysql> create table test2 asselect* fromtest1;**会话2:**••••••mysql> select * from test2 limit10; -- blocked statement 语句被阻塞 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 canprevent 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: 0Rows_examined: 0 Rows_read: 0 *************************** 2. row *************************** Id: 5 User: root Host: localhostdb: test Command: Query Time: 9 State: Copying to tmp table Info: select count(*), name from test2 group by nameorder 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 tabletest2 to test4 Rows_sent: 0 Rows_examined: 0 Rows_read: 0 3 rows in set (0.00 sec) As we can see, CREATETABLE AS SELECT can affect other queries. However, the problem here is not the metadata lock itself (the metadatalock is needed to preserve consistency). The problem is that the***metadata lock will not be released until thestatement is finished***.我们可以看到，CREATE TABLE AS SELECT可以影响其他查询。但是，这⾥的问题不是元数据锁本⾝（需要元数据锁来保持⼀致性）。问题是***在语句完成之前不会释放元数据锁***。The fix is simple: copy thetable structure first by doing “create table new_table like old_table”, then do “insert into new_table select …”. Themetadata lock is still held for the create table part (very short), but isn’t for the “insert … select” part (the total time tohold the lock is much shorter). To illustrate the difference, let’s look at two cases:1. With “create table table_new asselect … from table1“, other application connections can’t read from the destination table (table_new) for the durationof 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 “insertinto 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 fromtable_new”将被阻塞） 使⽤“create table new_table like old_table”+“insert into new_table select ...”，在“insert intonew_table select ...”这部分期间，其他应⽤程序连接⽆法读取⽬标表。 In some cases, however, the table structure isnot known beforehand. For example, we may need to materialize the result set of a complex select statement, involvingjoins and/or group by. In this case, we can use this trick:然⽽，在某些情况下，表结构事先是未知的。例如，我们可能需要物化复杂select语句的结果集，包括joins、and/or、group by。在这种情况下，我们可以使⽤这个技巧：createtable new_table as select ... join ... group by ... limit 0; insert into new_table as select ... join ... group by ... The firststatement creates a table structure and doesn’t insert any rows (LIMIT 0). The first statement places a metadata r, 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）