sql - CREATE TABLE AS SELECT 杀死 MySQL

Question

我们在相当强大的硬件(具有 8 个 Xeon 内核、8Gb RAM 和 RAID10 的 HP DL360)上运行中等负载(200-300 QPS)的 MySQL 服务器。所有表都是 innodb 并且事件数据集适合分配的 innodb_buffer_pool_size。

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

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

为了(重新)生成 MV:s，我们使用这样的东西:

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 会完全重载我们的服务器吗？我该如何防止它？

问候，

Answer 1

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