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

我通过 Amazon 服务使用 MySQL 服务器，使用默认设置。涉及到的表mytable is of InnoDB类型，大约有 10 亿行。 查询是：

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

执行起来大约需要 10 分钟。我有一个索引date. The 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；其他的计数类似dates.

我没有索引01。这是一个问题吗？为什么AVG()需要这么长时间来计算？一定是我有什么地方没做对。

任何建议表示赞赏！

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

为了计算未包含在索引中的列的平均值，MySQL 将再次使用索引来查找该日期的所有行（与之前相同）。但此外，对于它找到的每一行，它都必须读取该行的实际表数据，这意味着使用主键来定位该行，读取一些字节，并重复此 700k 次。这“随机访问” https://www.percona.com/blog/2008/04/28/the-mysql-optimizer-the-os-cache-and-sequential-versus-random-io/ is a lot比第一种情况下的顺序读取慢。 （由于“一些字节”是innodb_page_size https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size（默认情况下为 16KB），因此与“一些 MB”相比，您可能需要读取最多 700k * 16KB = 11GBcount(*);并且根据您的内存配置，其中一些数据可能不会被缓存，并且必须从磁盘读取。）

解决方案是在索引中包含所有使用的列（“覆盖索引”），例如创建索引date, 01。那么MySQL不需要访问表本身，只需读取索引即可继续，与第一种方法类似。索引的大小会增加一点，因此 MySQL 将需要读取“更多 MB”（并执行avg-操作），但它仍然应该是几秒钟的事情。

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

为了避免MySQL-每个索引 16 列的限制 https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html，您可以将其拆分为两个索引（和两个查询）。创建例如索引date, 01, .., 12 and date, 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，...），虽然总的来说，它们需要更多的空间，但可能会快一点（因为它们各自较小）。但缓冲池可能仍然倾向于完整索引，具体取决于使用模式和内存配置等因素，因此您可能必须对其进行测试。

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

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