对200m表的慢插入查询

Question

我们有一个包含约2亿条记录的表格：

CREATE TABLE IF NOT EXISTS `history` (
  `airline` char(2) NOT NULL,
  `org` char(3) NOT NULL,
  `dst` char(3) NOT NULL,
  `departat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `arriveat` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `validon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `price` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(org)
(PARTITION p0 VALUES LESS THAN ('AHI') ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN ('ARO') ENGINE = MyISAM,
 ...
 PARTITION p39 VALUES LESS THAN ('WMA') ENGINE = MyISAM,
 PARTITION p40 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */;

--
-- Indexes for table `history`
--
ALTER TABLE `history`
 ADD KEY `tail` (`org`,`dst`,`departat`);

我们经常批量插入一些VALUES，通常在简单的INSERT查询中最多可插入1000条记录，而不需要任何修饰，比如ON DUPLICATE KEY (索引本来就不是唯一的)。

有时，当我在phpMyAdmin中进入服务器状态时，会看到一堆INSERT语句相互等待，有时长达300-400秒。在特定的时间，服务器上似乎没有其他东西。我们有32 GB的内存和其他出色的性能。

如何解决此问题？谢谢你的帮助。

Answer 1

可能第一步是在profiling打开的情况下进行几次测试运行。

通常你会这样做：

SET LOCAL PROFILING=ON;
-- run your INSERT, like:
INSERT INTO yourtable (id) VALUES (1),(2),(3);

SHOW PROFILES;
+----------+------------+------------------------------------------------+
| Query_ID | Duration   | Query                                          |
+----------+------------+------------------------------------------------+
|     1012 | 6.25220000 | INSERT INTO yourtable (id) VALUES (1),(2),(3); |
+----------+------------+------------------------------------------------+

这将告诉您非常基本的信息，例如查询的持续时间(在本例中为6.25秒)。要获得实际的详细信息，您需要获取所述查询的配置文件：

SHOW PROFILE FOR QUERY 1025; 
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.004356 |
| checking permissions         | 0.000015 |
| Opening tables               | 6.202999 |
| System lock                  | 0.000017 |
| init                         | 0.000342 |
| update                       | 0.023951 |
| Waiting for query cache lock | 0.000008 |
| update                       | 0.000007 |
| end                          | 0.000011 |
| query end                    | 0.019984 |
| closing tables               | 0.000019 |
| freeing items                | 0.000304 |
| logging slow query           | 0.000006 |
| cleaning up                  | 0.000181 |
+------------------------------+----------+

你可能会注意到“打开桌子”花了很长时间。在本例中，查询执行被另一个进程锁定表(LOCK TABLES)以延迟执行。有关这些状态的更多信息，请参阅manual。

Answer 2

将时间戳字段设置为默认值0，然后尝试

例如：

departat timestamp NOT NULL DEFAULT 0,
arriveat timestamp NOT NULL DEFAULT 0,

Timestamp将存储一个类似integer (经过时间的平均时间戳)的值，它不会像datetime那样保存记录。

在本例中，您已在timestap字段类型中将缺省值设置为日期时间格式

Answer 3

有几件事可以用来优化批量插入。如果您确定您的数据不包含重复项，其中一件事是设置这些变量(不要忘记在上传完成后将它们设置为1)：

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;

此外，您还需要检查是否没有其他用户正在访问表。您也可以尝试使用Innodb，因为据说它比MyISAM更好地处理已有数据的批量插入。

此外，您还可以检查表上的碎片，有时操作系统在碎片驱动器上分配空闲空间时提供的开销是延迟的原因。