mysql - 优化MySQL的聚合性能

Question

我正在尝试优化报告查询，因为大多数报告查询都包含聚合。由于表的大小相当大并且还在不断增长，我需要关注它的性能。

例如，我有一个包含三列的表:id、name、action。我想计算每个名字完成的 Action 数:

SELECT name, COUNT(id) AS count
FROM tbl
GROUP BY name;

虽然很简单，但我无法在可接受的时间内运行它。这可能需要 30 秒，并且没有任何索引，我可以添加已考虑在内的索引，但会对其进行改进。

当我在上述查询上运行 EXPLAIN 时，它从不使用表的任何索引，即 name 上的索引。

有没有办法提高聚合的性能？为什么没有使用索引？

[更新]

这是 EXPLAIN 的输出:

+----+-------------+-------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+-----------------+
|  1 | SIMPLE      | tbl   | ALL  | NULL          | NULL | NULL    | NULL | 4025567 |   100.00 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------+-----------------+

这是表的架构:

CREATE TABLE `tbl` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name` varchar(1000) NOT NULL,
  `action` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `inx` (`name`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

您的查询和索引使用的问题在于您在 SELECT 语句中引用了两个不同的列，但在您的索引中只有一列，并且在索引上使用了前缀。

试试这个(仅引用名称列):

SELECT name, COUNT(*) AS count
FROM tbl
GROUP BY name;

具有以下索引(无前缀):

tbl (name)

不要在该查询的索引上使用前缀，因为如果这样做，MySQL 将无法将其用作覆盖索引(仍然需要访问表)。

如果您使用上面的方法，MySQL 将扫描name 列上的索引，但不必扫描实际的表数据。您应该在解释结果中看到 USING INDEX。

这是 MySQL 能够完成此类任务的最快速度。另一种方法是单独存储聚合结果，并随着数据的变化保持更新。

此外，请考虑减小 name 列的大小，尤其是当您达到索引大小限制时，这很可能就是您使用前缀的原因。如果不需要，请不使用 UTF8 以节省一些空间(UTF8 是每个字符 3 个字节用于索引)。