select中的longtext使查询速度极慢,即使没有在where子句和空结果集中使用(MySQL)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了select中的longtext使查询速度极慢,即使没有在where子句和空结果集中使用(MySQL),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5302字,纯文字阅读大概需要8分钟。
内容图文
只要在select子句中包含“longtext”类型,查询时间就会从8s到3min(Amazon RDS t2.small). long子文件未在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)
令我难以置信的是,通过主键请求longtext列很快:
select stdoutContents from mbp_process where id = 49213;
...
1 row in set (0.00 sec)
为什么是这样?在我的裸机服务器上效果不太明显:查询从那里减慢0.2s到1:05m.
这是关于“select id from …”查询的EXPLAIN:
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
| 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 …”查询的EXPLAIN:
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
| 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 |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
它们完全相同.
这是“SHOW CREATE TABLE mbp_process”中的create table语句:
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”,查询速度很快.这也是一个longtext专栏,尽管它的数据通常少得多.但是,我不是要求MySQL检查列的内容,结果集是空的,为什么“stdoutContents”的速度慢?
mysql> select id, stderrContents from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (0.57 sec)
解决方法:
PRIMARY KEY(id)表示它与数据聚集在一起.这不是问题.也没有尝试使用二级指数.这是正在发生的事情.
在InnoDB中,通常所有列都在BTree中由PRI索引的PRIMARY KEY旁边.然而,“大”专栏放在其他地方.如您所述,最多约8KB的行保持在一起.大列在他们自己的16KB块中.这些列包括任何TEXT / BLOB,甚至是长VARCHAR / VARBINARY列. (详细信息因innodb_file_format和其他一些内容而异.例如,大列的前767个字节可能会留下短列.)
因此,在选择排除这些大列的列时,查询将避免获取这些额外的块并且相对较快.听起来stdoutContents真的很大(需要多个16KB块)?
亚马逊与裸机:如果我没弄错的话,亚马逊将数据存储在类似SAN的系统上,而不是同一个盒子.
我看到的另一件事…… EXPLAIN说它正在使用这个二级密钥
KEY `idx_mbp_process_errorAcked` (`errorAcknowledged`)
每个二级密钥隐含地包括PK(id).所以,处理类似于:
>使用errorAcknowledged =’N’钻取第一个条目的辅助密钥
>在该BTree中向前扫描.这是最有效的一步.每个16KB块可能有超过100个’行’.
>对于其中每一个,使用id进入“数据”BTree. (每行1个块,希望更少,如果它们被很好地缓存)
>检查WHERE子句的其余部分:和(exitCode!= 0或exitCode为null)
>如果行仍然有趣,请获取所需的本地列(id,created,and stderrContents),以及
>到达stdoutContents的“大”存储区(如果你要求的话).这可能不会被缓存,并且可能涉及许多磁盘命中.
我希望这能解释一切.如果您需要进一步澄清,请与我们联系.
内容总结
以上是互联网集市为您收集整理的select中的longtext使查询速度极慢,即使没有在where子句和空结果集中使用(MySQL)全部内容,希望文章能够帮你解决select中的longtext使查询速度极慢,即使没有在where子句和空结果集中使用(MySQL)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。