select中的长文本使得查询非常慢，即使没有在where子句和空结果集(MySQL)中使用

Question

一旦我在select子句中包含了一个“长文本”类型，查询时间就从8s到3分钟(Amazon t2.mall)。where子句中不使用长文本，结果集为空。见下文：

mysql> select id from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (8.03 sec)

mysql> select id, stdoutContents from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (3 min 43.36 sec)

让我感到困惑的是，按主键请求长文本列是快速的：

select stdoutContents from mbp_process where id = 49213;
...
1 row in set (0.00 sec)

为什么会这样呢？在我的裸机服务器上，这种效果不太明显:查询速度从0.2s减慢到1:05m。

这是对"select id from.“的解释查询：

+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
| id | select_type | table       | type | possible_keys                                       | key                        | key_len | ref   | rows  | Extra                              |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
|  1 | SIMPLE      | mbp_process | ref  | idx_mbp_process_exitCode,idx_mbp_process_errorAcked | idx_mbp_process_errorAcked | 2       | const | 22551 | Using index condition; Using where |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+

这是"select id，stdoutContents from .“中的解释。查询：

+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
| id | select_type | table       | type | possible_keys                                       | key                        | key_len | ref   | rows  | Extra                              |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
|  1 | SIMPLE      | mbp_process | ref  | idx_mbp_process_exitCode,idx_mbp_process_errorAcked | idx_mbp_process_errorAcked | 2       | const | 22552 | Using index condition; Using where |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+

他们是一样的。

这是“显示create mbp_process”中的CREATE语句：

CREATE TABLE `mbp_process` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `command` varchar(1000) DEFAULT NULL,
  `pid` varchar(45) DEFAULT NULL,
  `state` varchar(45) NOT NULL,
  `exitCode` int(11) DEFAULT NULL,
  `stdoutContents` longtext,
  `stdoutTruncated` char(1) DEFAULT NULL,
  `stdoutFilename` varchar(200) DEFAULT NULL,
  `stderrContents` longtext,
  `stderrTruncated` char(1) DEFAULT NULL,
  `stderrFilename` varchar(200) DEFAULT NULL,
  `majorProgress` varchar(45) DEFAULT NULL,
  `minorProgress` varchar(45) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `endTime` datetime DEFAULT NULL,
  `errorAcknowledged` char(1) DEFAULT 'N',
  `errorComments` text,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mbp_process_command` (`command`(767)),
  KEY `idx_mbp_process_exitCode` (`exitCode`),
  KEY `idx_mbp_process_state` (`state`),
  KEY `idx_mbp_process_errorAcked` (`errorAcknowledged`)
) ENGINE=InnoDB AUTO_INCREMENT=50184 DEFAULT CHARSET=latin1

选择要包含的另一列不会降低查询速度：

mysql> select id, created from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (1.69 sec)

这很奇怪:如果我包含"stderrContents"，查询就会很快。这也是一个长文本列，尽管它中的数据通常要少得多。但是，我并没有要求MySQL检查列的内容，结果集是空的，那么为什么"stdoutContents“比较慢呢？

mysql> select id, stderrContents from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (0.57 sec)

Answer 1

PRIMARY KEY(id)说它是与数据聚集在一起的。这不是问题所在。使用二级索引的尝试也不是。下面是正在发生的事情。

在InnoDB中，通常所有列都在由PK索引的BTree中的主键旁边。然而，“大”栏目被放在其他地方。正如您所描述的，最多可以将大约8 8KB的行保持在一起。大柱子是在他们自己的16 in块(S)。这些列包括任何文本/BLOB，甚至是长的VARCHAR/VARBINARY列。(细节因innodb_file_format和其他几件事情而异。例如，大列的前767字节可能与短列一起保留。)

因此，当选择排除这些大列的列时，查询将避免获取这些额外的块，并且相对较快。听起来stdoutContents真的很大(需要多个16 is的块)？

亚马逊和裸金属:如果我没弄错的话，亚马逊把数据存储在一个类似SAN的系统上，而不是同一个盒子里。

我看到的另一件事..。EXPLAIN说它正在使用这个辅助密钥

KEY `idx_mbp_process_errorAcked` (`errorAcknowledged`)

每个次要密钥隐式地包括PK (id)。因此，处理过程类似于：

• 使用errorAcknowledged='N'钻入第一个条目的辅助键
• 在BTree中向前扫描。这是最有效的步骤。每16 per块可能有超过100‘行’。
• 对于其中的每一个，使用id进入“数据”BTree。(如果缓存得很好的话，每行1块，希望更少)
• 检查WHERE子句的其余部分：and (exitCode != 0 or exitCode is null)
• 如果该行仍感兴趣，则获取所需的本地列(id、created，可能还有stderrContents)，并且
• 把手伸进stdoutContents的“大”存储区(如果你想要的话)。这很可能不会被缓存，而且可能涉及到许多磁盘命中。

我希望这能解释一切。如果你需要进一步澄清，请告诉我。

Answer 2

由于I/O是主要的减速，而且您有两个长的(长的)字段，下面是另一种加快速度的方法。

在客户机中，压缩*Content字段并将它们放入MEDIUMBLOB中。普通文本压缩3:1；那些特定类型的文本可能压缩得更多。

类似地，在客户机中解压缩。这避免了网络开销，并卸载了服务器。

这种技术对于InnoDB或MyISAM非常有用。它与其它解是正交的。

另一个想法--为更多的IOPS买单。