创建表时使用SELECT语句，如何在MySQL中实现？

Question

我们在相当强大的硬件上（HP DL360，8个Xeon核心，8GB RAM和RAID10）运行一个MySQL服务器，负载适中（200-300 QPS）。所有表都是InnoDB，并且活动数据集适合于分配的innodb_buffer_pool_size。

我们的数据库已规范化，为了减少连接次数，我们使用物化视图来展开数据集。由于数据每天批量添加几次，因此使用CREATE TABLE AS SELECT重新生成MV：而不是使用复杂触发器动态更新它们。

问题在于，有时在运行这些CREATE查询（每个查询需要5到50秒不等）时，其他与服务器无关的查询似乎被排队在CREATE查询后面，导致数据库无响应。

为了重新生成MV：，我们使用类似以下的方法：

BEGIN TRANSACTION;
DROP TABLE IF EXISTS TableName_TMP;
CREATE TABLE TableName_TMP ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci AS 
    SELECT about100columns, and10Expressions 
    FROM Table1 
    JOIN Table2 ON Table1.fk = Table2.pk 
    /* join up to 13 other tables */
    WHERE ((removed IS NULL OR removed = 0)) 
    ORDER BY created DESC, id ASC;
ALTER TABLE TableName_TMP ADD PRIMARY KEY(id), INDEX(created);
DROP TABLE IF EXISTS TableName;
ALTER TABLE TableName_TMP RENAME TO TableName;
COMMIT;

SELECT语句的EXPLAIN结果大致如下：

+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+
| id | select_type | table            | type        | possible_keys | key        | key_len | ref                          | rows  | Extra                       |
+----+-------------+------------------+-------------+---------------+------------+---------+    ------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | Table1           | ref_or_null | removed       | removed    | 5       | const                        | 76093 | Using where; Using filesort | 
|  1 | SIMPLE      | Table2           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk1                   |     1 |                             | 
|  1 | SIMPLE      | Table3           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk2                   |     1 |                             | 
/* More of the same */
|  1 | SIMPLE      | TableN           | eq_ref      | PRIMARY       | PRIMARY    | 4        | TableM.fk                    |     1 | Using index                 | 
|  1 | SIMPLE      | TableX           | eq_ref      | PRIMARY       | PRIMARY    | 4       | TableY.fk                    |     1 |                             | 
/* More of the same */    
+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+

有什么想法，为什么CREATE TABLE AS 会完全超载我们的服务器，我该如何防止呢？

谢谢！

- Paso

---

在 SQL Server 中，执行 SELECT ... INTO（与 CREATE TABLE ... SELECT 概念相同）会锁定整个源表，导致其他查询排队等待。这是设计上的考虑。MySQL 可能存在类似的行为。由于我不太了解 MySQL，所以没有在正式答案中提及。 - user114600

看起来这可能是它 - http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/。我打算在周一进行一些测试。 - Paso

如果您希望您的评论被接受为正确答案，请将其重新发布为答案。 - Paso

Answer 1

我们通过转换为SELECT INTO和LOAD DATA INFILE来解决此问题，参考http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/。非常感谢Randolph Potter为我们指明了正确的方向。

- Paso

Answer 2

这可能是原因吗？

注意：DROP TABLE 命令会自动提交当前活动的事务，除非您使用 TEMPORARY 关键字。

(http://dev.mysql.com/doc/refman/5.1/en/drop-table.html)

- Zed

---

谢谢，我会将事务更改为仅包括CREATE TABLE和ALTER TABLE语句。然而，我看不出这会是我的问题源头，因为仅有CREATE TABLE语句就会使服务器超载，而单独一个语句在性能方面不应该从事务中获益，对吧？ - Paso