gpt4 book ai didi

mysql - MySQL 服务器上非常简单的 AVG() 聚合查询需要很长时间

转载 作者:行者123 更新时间:2023-11-29 05:05:21 29 4
gpt4 key购买 nike

我通过 Amazon could service 使用默认设置的 MySQL 服务器。 mytable涉及的表是InnoDB类型,大约有10亿行。查询是:

select count(*), avg(`01`) from mytable where `date` = "2017-11-01";

执行需要将近 10 分钟。我有一个关于 date 的索引。此查询的 EXPLAIN 是:

+----+-------------+---------------+------+---------------+------+---------+-------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+-------+---------+-------+
| 1 | SIMPLE | mytable | ref | date | date | 3 | const | 1411576 | NULL |
+----+-------------+---------------+------+---------------+------+---------+-------+---------+-------+

这个表的索引是:

+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytable | 0 | PRIMARY | 1 | ESI | A | 60398679 | NULL | NULL | | BTREE | | |
| mytable | 0 | PRIMARY | 2 | date | A | 1026777555 | NULL | NULL | | BTREE | | |
| mytable | 1 | lse_cd | 1 | lse_cd | A | 1919210 | NULL | NULL | YES | BTREE | | |
| mytable | 1 | zone | 1 | zone | A | 732366 | NULL | NULL | YES | BTREE | | |
| mytable | 1 | date | 1 | date | A | 85564796 | NULL | NULL | | BTREE | | |
| mytable | 1 | ESI_index | 1 | ESI | A | 6937686 | NULL | NULL | | BTREE | | |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

如果我删除 AVG():

select count(*) from mytable where `date` = "2017-11-01";

返回计数只需要 0.15 秒。这个特定查询的计数是692792;其他 date 的计数类似。

我没有关于 01 的索引。这是一个问题吗?为什么 AVG() 需要这么长时间来计算?一定是我哪里做的不对。

欢迎任何建议!

最佳答案

要计算具有特定日期的行数,MySQL 必须在索引中找到该值(这非常快,毕竟这是创建索引的目的),然后读取后续条目 index 直到找到下一个日期。根据 esi 的数据类型,这将总结为读取一些 MB 的数据来计算您的 700k 行。读取一些 MB 不会花费太多时间(并且该数据甚至可能已经缓存在缓冲池中,具体取决于您使用索引的频率)。

要计算未包含在索引中的列的平均值,MySQL 将再次使用索引查找该日期的所有行(与之前相同)。但是另外,对于它找到的每一行,它必须读取该行的实际表数据,这意味着使用主键来定位该行,读取一些字节,并重复这个 700k 次。这"random access"比第一种情况下的顺序读取慢很多。 (由于“一些字节”是 innodb_page_size(默认为 16KB)的问题变得更糟,因此与 count(* );并且根据您的内存配置,其中一些数据可能不会被缓存,必须从磁盘读取。)

一个解决方案是在索引中包含所有使用的列(“覆盖索引”),例如在 date, 01 上创建索引。那么 MySQL 就不需要访问表本身,可以像第一种方法一样,只读索引就可以继续。索引的大小会增加一点,因此 MySQL 将需要读取“更多 MB”(并执行 avg 操作),但这仍然是几秒钟的事情。

在评论中,您提到您需要计算 24 列的平均值。如果你想同时计算多个列的 avg,你需要对所有列都使用一个覆盖索引,例如date, 01, 02, ..., 24 以防止访问表。请注意,包含所有列的索引需要与表本身一样多的存储空间(并且创建这样的索引需要很长时间),因此它可能取决于此查询的重要性是否值得这些资源。

为了避免MySQL-limit of 16 columns per index ,您可以将其拆分为两个索引(和两个查询)。创建例如索引 date, 01, .., 12date, 13, .., 24,然后使用

select * from (select `date`, avg(`01`), ..., avg(`12`) 
from mytable where `date` = ...) as part1
cross join (select avg(`13`), ..., avg(`24`)
from mytable where `date` = ...) as part2;

确保记录好这一点,因为没有明显的理由以这种方式编写查询,但它可能是值得的。

如果您只对单个列进行平均,则可以添加 24 个单独的索引(在 date, 01, date, 02, ...),尽管在总的来说,它们将需要更多的空间,但可能会快一点(因为它们单独较小)。但是缓冲池可能仍然倾向于完整索引,具体取决于使用模式和内存配置等因素,因此您可能需要对其进行测试。

由于 date 是主键的一部分,您还可以考虑将主键更改为 date, esi。如果您通过主键查找日期,则不需要额外的步骤来访问表数据(因为您已经访问了表),因此行为类似于覆盖索引。但这是对您的表的重大更改,可能会影响所有其他查询(例如使用 esi 来定位行),因此必须仔细考虑。

正如您所提到的,另一种选择是构建一个汇总表,您可以在其中存储预先计算的值,特别是如果您不添加或修改过去日期的行(或者可以使用触发器使它们保持最新)。

关于mysql - MySQL 服务器上非常简单的 AVG() 聚合查询需要很长时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49397333/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com