mysql creat table as_CREATE TABLE AS SELECT杀死MySQL

我们在功能强大的硬件(具有8个Xeon内核,8Gb RAM和RAID10的HP DL360)上运行具有中等负载(200-300 QPS)的MySQL服务器.所有表都是innodb,活动数据集适合分配的innodb_buffer_pool_size.

我们的数据库已规范化,为了减少联接的数量,我们使用实例化视图来展平数据集.由于一天要分几次添加数据,因此将使用CREATE TABLE AS SELECT而不是使用复杂的触发器来动态更新MV：s.

问题是,有时在运行这些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会使我们的服务器完全过载,又如何防止它过载？

问候,