主页 / dba / 问题 / 264153
Accepted
a1an
Asked: 2020-04-03 05:37:36 +0800 CST

为什么查询变得非常慢，与数据量无关？

  • 772

我有一个查询通常在几秒钟/几分钟内运行，一段时间（大约一周）后变得非常慢，然后几天！执行。查询只停留在“发送数据”中，CPU 使用率为 100%。服务器是 Mariadb 10.4，系统正在执行许多其他复杂的查询而没有问题，只有这个特定的查询似乎遇到了一些服务器限制或性能错误。

数据量似乎并不相关，因为查询在不同的数据库上运行，这些数据库是为具有不同记录量的每个特定项目创建和删除的，但即使是较小的项目也会出现问题。

重新启动服务器会使查询再次快速运行一段时间，但问题会一遍又一遍地出现。在服务器达到其允许的最大 RAM 量之前，该问题似乎不会发生，即使服务器上仍有可用 RAM 可供使用（我专门减小了缓冲区大小以对其进行测试）。一旦问题出现，InnoDB 和 MyISAM 引擎都会发生这种情况。由于在服务器重新启动后查询运行得非常快，因此它似乎不是缺少索引等问题。有什么提示会导致该行为以及如何调查/解决？

以下是查询：

CREATE TABLE counts_otus (
    _sample_id INT,
    _region_sample_id INT,
    sequencesPerOtu INT,
    PRIMARY KEY (_region_sample_id),
    INDEX (_sample_id)
) ENGINE=InnoDB AS 
    SELECT _sample_map._sample_id, _sample_map._region_sample_id, (
            SELECT COUNT(*) 
              FROM cluster AS otu 
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + (
            SELECT count(*) 
              FROM cluster AS otu 
        INNER JOIN cluster AS mem 
                ON otu._region_sample_id = mem._cluster_sample_id
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + 1 AS sequencesPerOtu
      FROM Region
INNER JOIN _sample_map USING (primaryAccession)
INNER JOIN sample USING (_sample_id)
     WHERE regionTag is NULL
       AND sampleTag is NULL
       AND sample_type <> 'otumap'
;

查询计划确实不同，这可能是解决问题的决定因素：快速运行时的计划是

+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
| id   | select_type        | table       | type | possible_keys                                   | key            | key_len | ref                                                                    | rows  | Extra                    |
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+
|    1 | PRIMARY            | sample      | ALL  | PRIMARY,id_sample_type                          | NULL           | NULL    | NULL                                                                   | 10    | Using where              |
|    1 | PRIMARY            | _sample_map | ref  | fk_sset_seqent,fk_sset_sample,fk_sset_smapleTag | fk_sset_sample | 4       | silvangs_slv_main_pid23875_rid26315.sample._sample_id                  | 52186 | Using where              |
|    1 | PRIMARY            | Region      | ref  | PRIMARY,fk_rgnTag                               | fk_rgnTag      | 100     | const,silvangs_slv_main_pid23875_rid26315._sample_map.primaryAccession | 1     | Using where; Using index |
|    3 | DEPENDENT SUBQUERY | otu         | ref  | PRIMARY,id_cluster                              | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id      | 1     | Using index              |
|    3 | DEPENDENT SUBQUERY | mem         | ref  | id_cluster                                      | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315.otu._region_sample_id              | 1     | Using index              |
|    2 | DEPENDENT SUBQUERY | otu         | ref  | id_cluster                                      | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id      | 1     | Using index              |
+------+--------------------+-------------+------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+-------+--------------------------+

运行速度极慢时的计划（终止正在运行的查询并随后对其选择进行解释：

+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
| id   | select_type        | table       | type   | possible_keys                                   | key            | key_len | ref                                                                    | rows   | Extra                    |
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+
|    1 | PRIMARY            | sample      | ALL    | PRIMARY,id_sample_type                          | NULL           | NULL    | NULL                                                                   | 10     | Using where              |
|    1 | PRIMARY            | _sample_map | ref    | fk_sset_seqent,fk_sset_sample,fk_sset_smapleTag | fk_sset_sample | 4       | silvangs_slv_main_pid23875_rid26315.sample._sample_id                  | 41361  | Using where              |
|    1 | PRIMARY            | Region      | ref    | PRIMARY,fk_rgnTag                               | fk_rgnTag      | 100     | const,silvangs_slv_main_pid23875_rid26315._sample_map.primaryAccession | 1      | Using where; Using index |
|    3 | DEPENDENT SUBQUERY | mem         | index  | id_cluster                                      | id_cluster     | 4       | NULL                                                                   | 738041 | Using index              |
|    3 | DEPENDENT SUBQUERY | otu         | eq_ref | PRIMARY,id_cluster                              | PRIMARY        | 4       | silvangs_slv_main_pid23875_rid26315.mem._cluster_sample_id             | 1      | Using where              |
|    2 | DEPENDENT SUBQUERY | otu         | ref    | id_cluster                                      | id_cluster     | 4       | silvangs_slv_main_pid23875_rid26315._sample_map._region_sample_id      | 57226  | Using index              |
+------+--------------------+-------------+--------+-------------------------------------------------+----------------+---------+------------------------------------------------------------------------+--------+--------------------------+

因此，运行缓慢时不仅有“ref”连接类型，还有“index”和“eq_ref”，据我所知，它们应该会更好，但最终会卡住几天。

该问题最初发布到stackoverflow，在那里我得到建议它更适合在dba，这里是该问题的链接：https ://stackoverflow.com/questions/60952661/why-does-a-query-becomes-与数据量无关的极慢速度

mysql query-performance

2 个回答

  1. Best Answer

    根据 mysql 文档（https://dev.mysql.com/doc/refman/5.7/en/controlling-query-plan-evaluation.html），错误的查询计划似乎确实可以产生如此数量级的差异作为秒与天，所以我认为问题在于优化器选择了错误的查询计划。为什么在服务器运行一段时间后经常发生这种情况（并且其缓冲区的可用内存已完全分配）仍然是一个谜，但解决方案似乎在于向优化器提供提示以避免错误的连接顺序并使用引用的索引在好的查询中。这是通过如下更改查询获得的：

    DROP TABLE IF EXISTS test_counts_otus;
CREATE TABLE test_counts_otus (
    _sample_id INT,
    _region_sample_id INT,
    sequencesPerOtu INT,
    PRIMARY KEY (_region_sample_id),
    INDEX (_sample_id)
) ENGINE=InnoDB AS 
    SELECT _sample_map._sample_id, _sample_map._region_sample_id, (
            SELECT COUNT(*) 
              FROM cluster AS otu FORCE INDEX ( id_cluster )
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + (
            SELECT count(*) 
              FROM cluster AS otu FORCE INDEX ( id_cluster )
     STRAIGHT_JOIN cluster AS mem FORCE INDEX ( id_cluster )
                ON otu._region_sample_id = mem._cluster_sample_id
             WHERE otu._cluster_sample_id = _sample_map._region_sample_id
    ) + 1 AS sequencesPerOtu
      FROM Region
INNER JOIN _sample_map USING (primaryAccession)
INNER JOIN sample USING (_sample_id)
     WHERE regionTag is NULL
       AND sampleTag is NULL
       AND sample_type <> 'otumap'
;

    固定查询使用强制索引和 STRAIGHT_JOIN，如https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/中所述

    • 0

  2. otu 上的这个“覆盖”索引可能会起作用：

    INDEX(_cluster_sample_id, _region_sample_id)  -- in this order
    • 0

相关问题