主页 / dba / 问题 / 98970
Accepted
Pedro Werneck
Asked: 2015-04-28 11:01:40 +0800 CST

查询“优化”状态花费的时间太长

  • 772

我在 Amazon RDS 上有一个 MySQL 5.6，用于测试一些数据归档脚本。我正在根据“updated_date”列和索引删除最旧的数据。奇怪的是，在删除了几百万行之后，我的脚本卡在了它为确定数据边界所做的初始查询上。

我运行这样的查询：

SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

此查询的explain命令显示：

'1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'

因此，它应该几乎立即命中索引并运行，并且在测试开始时确实如此，但是现在，在删除数百万行之后，它会在“优化”状态中停留几分钟。

脚本是唯一在数据库上运行的东西。

关于它有什么问题的任何想法？删除大量这样的行时，我应该做些什么吗？optimize table即使我不使用，我是否必须跑步delete quick？

更新#1

结果来自show table status like 'order'：

Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment
order,InnoDB,10,Compact,568037197,280,159252496384,0,180806041600,37692112896,4052226884,"2015-01-26 17:27:20",NULL,NULL,utf8_general_ci,NULL,,

结果select count(*) from order是 618376777 行。

不幸的是，我不能在这里发布整个架构，但它与问题有关，结果show create table order是：

CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  // 31 data columns here
  `updated_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),       
  KEY `ix_order_updated_date` (`updated_date`),
  // 9 indexes here
) ENGINE=InnoDB AUTO_INCREMENT=4052226884 DEFAULT CHARSET=utf8;

更新#2

通过在两个查询中分离 min() 和 max() 调用，我注意到只有 min() 查询受到影响。max() 几乎立即返回，因此看起来 min() 正在遍历所有存在但现在为空的索引条目的索引。除了重建索引之外，还有什么方法可以防止这种情况发生吗？

更新#3

RickJames 通过有关更改缓冲的提示解决了这个问题，但是完全禁用它会损害所有插入、删除和更新的性能。最终，我发现在生产服务器上刷新更改缓冲区所需的时间是合理的，所以问题对我来说解决了，但如果你在带有磁性存储的低端服务器上遇到同样的问题，祝你好运。

mysql mysql-5.6

3 个回答

  1. Best Answer
    SELECT min(updated_date) as oldest, max(updated_date) AS newest FROM `order`;

    如果你有INDEX(updated_date)（你这样做），那么优化得很好。它将进行两次探测。不需要两个查询等。

    所说的EXPLAIN“优化掉”是因为它可以看到它不需要做任何工作。

    我检查了一个类似的表，然后检查了Handler%STATUS 值——每个值Handler_read_first都Handler_read_last增加了 1。这表明优化MIN和MAX。

    不使用OPTIMIZE TABLE它（通常）会浪费很长时间。

    这是一张巨大的桌子。

    删除一百万行会排队很多东西，尤其是因为有 10 个二级索引。我敢打赌，当您要求 MAX 和 MIN 时，它必须完成所有未决的索引更新。

    如果是这样的话，任何建议都不能真正解决问题。分块删除（无论如何这是一个好主意）可能会通过减慢删除任务的速度来“隐藏”问题。正如@eroomydna 所说，撤消日志一定很大。

    你删除的依据是什么？如果它是“清除所有早于 X 的记录”，那么这是一种治疗方法： PARTITION BY RANGE(...)在日期（用于清除）并DROP PARTITION抛弃旧行。这也比删除更干净（在优化碎片整理的意义上）。

    Rolando，ANALYZE TABLE将“立即”重新计算统计数据。 OPTIMIZE TABLE重建（和分析）表，但永远在这个大小的表上。

    可能有效的是复制有用的行，如罗兰多的#4。但前提是你要抛弃“大部分”桌子。我建议这里不是这种情况——从 618M 中“删除几百万行”。

    10个辅助键很多。你愿意给他们看吗？我们可能对修剪列表有建议。对于这种大小的桌子，维护这么多桌子的成本很高。

    编辑——“更改缓冲”

    我相信我所描述的称为 DELETE 的更改缓冲。更多讨论： http: //dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance-change_buffering.html http://dev.mysql.com/doc/refman/5.6/en/innodb-性能-change_buffering.html

    你可以试试innodb_change_buffering = none。

    • 2

  2. 删除大量数据的最佳执行方式是分块您的活动。有一些工具可以完成此操作，例如 pt-archive 和 Oak-chunk-update。这避免了撤销和性能问题的累积。

    • 1

  3. 建议 #1

    也许您可以将最小和最大日期作为单独的查询来检索

    SELECT updated_date INTO @oldest_updated_date
FROM `order` ORDER BY updated_date LIMIT 1;
SELECT updated_date INTO @newest_updated_date
FROM `order` ORDER BY updated_date DESC LIMIT 1;

    这将遍历索引每个查询一个键

    建议 #2

    批量删除后，您应该缩小表。

    OPTIMIZE TABLE `order`;

    建议#3

    执行批量删除后，需要重新计算索引统计信息。OPTIMIZE TABLE为你做，但你可以做一个单独的步骤，像这样：

    ANALYZE TABLE `order`;

    建议 #4

    也许从开始日期重新创建表。例如，要保留最近 30 天，请执行以下操作：

    SET @TimeGapToKeep = NOW() - INTERVAL 30 DAY;
ALTER TABLE `order` RENAME `old_order`;
CREATE TABLE `new_order` LIKE `old_order`;
INSERT INTO `new_order` SELECT * FROM `old_order`
WHERE updated_date >= @TimeGapToKeep;
ALTER TABLE `new_order` RENAME `order`;
ANALYZE TABLE `order`;
    • 1

相关问题