MariaDB中COUNT DISTINCT与GROUP BY性能及索引问题问询

MariaDB版本 #

select version();
version()                                |
-----------------------------------------+
10.4.24-MariaDB-1:10.4.24+maria~focal-log|

表结构与索引 #

CREATE TABLE tt (a int, b varchar(32), c varchar(64), d varchar(64), e varchar(64), f int);
CREATE INDEX tt_a_IDX USING BTREE ON tt (a,b,c,d,e);

表数据量与分布 #

共510114行，数据分布如下：

select count(distinct a) from tt;
count(distinct a)|
-----------------+
                1|
-- 注：列a仅存在值1

select count(distinct a,b) from tt;
count(distinct a,b)|
-------------------+
                 16|

select count(distinct a,b,c) from tt;
count(distinct a,b,c)|
---------------------+
                   28|

select count(distinct a,b,c,d) from tt;
count(distinct a,b,c,d)|
-----------------------+
                   2652|

select count(distinct a,b,c,d,e) from tt;
count(distinct a,b,c,d,e)|
-------------------------+
                   510071|

select count(distinct a,b,c,d,e,f) from tt;
count(distinct a,b,c,d,e,f)|
---------------------------+
                      49680|

1. SQL性能差异问题 #

对比两条统计e列相关计数的SQL：

SQL语句 #

-- sql1
select count(DISTINCT c,d,e),b from tt where a = 1 group by b;

-- sql2
select sum(count), b from (
                select b,COUNT(DISTINCT e) as count from tt 
                where a = 1
                GROUP BY  b,c,d) tt group by b;

执行计划 #

sql1执行计划

id|select_type|table|type|possible_keys|key     |key_len|ref  |rows  |Extra                   |
--+-----------+-----+----+-------------+--------+-------+-----+------+------------------------+
 1|SIMPLE     |tt   |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using index|

sql2执行计划

id|select_type|table     |type|possible_keys|key     |key_len|ref  |rows  |Extra                          |
--+-----------+----------+----+-------------+--------+-------+-----+------+-------------------------------+
 1|PRIMARY    |<derived2>|ALL |             |        |       |     |253768|Using temporary; Using filesort|
 2|DERIVED    |tt        |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using index       |

性能表现 #

sql1平均耗时4秒，sql2仅需数百毫秒。

问题1解答：为何第二条SQL更快？是否可用"分组后求和"替代多列COUNT(DISTINCT)以提升性能？

核心原因是多列COUNT(DISTINCT)的计算成本远高于单列COUNT(DISTINCT)+分组求和：

• sql1需要对(c,d,e)三列组合做去重计数，由于(a,b,c,d,e)的唯一值接近全表行数（510071），数据库要在全表数据中维护一个极大的哈希表存储所有(c,d,e)的唯一组合，内存占用高且计算耗时久。
• sql2先按b,c,d分组，每组内对e做去重计数，再对分组结果求和。从数据分布看，(a,b,c,d)的唯一值仅2652个，远小于(c,d,e)的唯一值数量，子查询阶段维护的哈希表规模极小，计算成本大幅降低；后续对2652条结果求和的开销可以忽略不计。

在业务逻辑等价的前提下，完全可以用"分组后求和"替代多列COUNT(DISTINCT)来提升性能，尤其是当多列组合的唯一值数量远大于拆分后分组的唯一值数量时，效果会非常明显。

2. GROUP BY添加列后的性能问题 #

在sql2的子查询GROUP BY中添加列a后得到sql3：

SQL语句 #

-- sql3
select sum(count), b from (
                select b,COUNT(DISTINCT e) as count from tt 
                where a = 1
                GROUP BY  a,b,c,d) temp group by b;

执行计划 #

id|select_type|table     |type |possible_keys|key     |key_len|ref|rows  |Extra                                           |
--+-----------+----------+-----+-------------+--------+-------+---+------+------------------------------------------------+
 1|PRIMARY    |<derived2>|ALL  |             |        |       |   |253768|Using temporary; Using filesort                 |
 2|DERIVED    |tt        |range|tt_a_IDX     |tt_a_IDX|689    |   |253768|Using where; Using index for group-by (scanning)|

性能表现 #

实际平均耗时约3秒，比sql2慢很多。

问题2解答：此现象原因是什么？

这是MariaDB 10.4版本中索引分组优化的特性差异导致的：

• sql2的GROUP BY是b,c,d，结合过滤条件a=1，数据库可以利用复合索引(a,b,c,d,e)的前缀顺序，直接按索引顺序扫描并分组，无需额外排序或创建临时表，属于高效的索引覆盖扫描。
• sql3的GROUP BY添加了a后，虽然a的过滤条件是固定值1，但MariaDB优化器在处理GROUP BY a,b,c,d时，错误选择了范围扫描（range）+索引分组扫描的策略，而非利用索引前缀的有序性直接分组。从执行计划的Extra字段Using index for group-by (scanning)可以看出，数据库需要扫描整个索引范围并逐行处理分组，而非按索引顺序快速聚合，导致性能下降。

MySQL 8对这种场景做了优化，使用Covering index skip scan高效处理分组，因此性能没有明显下降，也佐证了这是MariaDB特定版本的优化器问题。

3. 索引实际有效性问题 #

删除复合索引(a,b,c,d,e)，创建仅包含列a的索引后，两条SQL的执行计划差异极小，但平均耗时分别增至5秒、4秒：

执行计划 #

sql1执行计划

id|select_type|table|type|possible_keys|key     |key_len|ref  |rows  |Extra                      |
--+-----------+-----+----+-------------+--------+-------+-----+------+---------------------------+
 1|SIMPLE     |tt   |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using filesort|

sql2执行计划

id|select_type|table     |type|possible_keys|key     |key_len|ref  |rows  |Extra                          |
--+-----------+----------+----+-------------+--------+-------+-----+------+-------------------------------+
 1|PRIMARY    |<derived2>|ALL |             |        |       |     |253768|Using temporary; Using filesort|
 2|DERIVED    |tt        |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using filesort    |

问题3解答：是否说明复合索引(a,b,c,d,e)实际生效，但执行计划未体现？

是的，复合索引确实生效了，只是执行计划没有完整体现其覆盖扫描和分组优化的细节：

• 使用复合索引时，sql1和sql2都能通过索引直接获取所需的a,b,c,d,e列数据，无需回表查询（执行计划中的Using index字段已说明），这是性能提升的核心原因。
• 删除复合索引后，仅靠a的索引，数据库需要回表获取b,c,d,e列数据，同时分组时无法利用索引的有序性，必须通过Using filesort排序，导致耗时大幅增加。
• MariaDB的执行计划在展示索引使用细节时不够细致，没有体现出复合索引在分组、去重时的有序性优化，但实际执行时确实利用了索引的结构来降低计算成本。

将数据导入MySQL 8后，三条SQL性能无明显差异，其EXPLAIN ANALYZE结果如下：

EXPLAIN ANALYZE select count(DISTINCT c,d,e),b 
from tt where a = 1 group by b; 
-> Group aggregate: count(distinct tt.c,tt.d,tt.e)  (cost=71893.42 rows=253768) (actual time=0.882..3453.691 rows=16 loops=1)
    -> Covering index lookup on tt using tt_a_IDX (a=1)  (cost=46516.62 rows=253768) (actual time=0.030..411.063 rows=510114 loops=1)

EXPLAIN ANALYZE select sum(count), b
from
    (
    select b, COUNT(DISTINCT e) as count
    from tt where a = 1 GROUP BY b, c, d
    ) temp
group by b;
-> Table scan on <temporary>  (actual time=1113.183..1113.185 rows=16 loops=1)
    -> Aggregate using temporary table  (actual time=1113.182..1113.182 rows=16 loops=1)
        -> Table scan on temp  (cost=97270.23..100444.82 rows=253768) (actual time=1111.180..1111.563 rows=2652 loops=1)
            -> Materialize  (cost=97270.22..97270.22 rows=253768) (actual time=1111.177..1111.177 rows=2652 loops=1)
                -> Group aggregate: count(distinct tt.e)  (cost=71893.42 rows=253768) (actual time=0.153..1109.965 rows=2652 loops=1)
                    -> Covering index lookup on tt using tt_a_IDX (a=1)  (cost=46516.62 rows=253768) (actual time=0.043..398.208 rows=510114 loops=1)

EXPLAIN ANALYZE select sum(count), b
from
    (
    select b, COUNT(DISTINCT e) as count
    from tt where a = 1 GROUP BY a,b, c, d
    ) temp
group by b;
-> Table scan on <temporary>  (actual time=4762.307..4762.309 rows=16 loops=1)
    -> Aggregate using temporary table  (actual time=4762.306..4762.306 rows=16 loops=1)
        -> Table scan on temp  (cost=76130.41..79305.00 rows=253768) (actual time=4760.310..4760.708 rows=2652 loops=1)
            -> Materialize  (cost=76130.40..76130.40 rows=253768) (actual time=4760.308..4760.308 rows=2652 loops=1)
                -> Group aggregate: count(distinct tt.e)  (cost=50753.60 rows=253768) (actual time=0.388..4757.575 rows=2652 loops=1)
                    -> Filter: (tt.a = 1)  (cost=25376.80 rows=253768) (actual time=0.055..4593.786 rows=510071 loops=1)
                        -> Covering index skip scan for deduplication on tt using tt_a_IDX over (a = 1)  (cost=25376.80 rows=253768) (actual time=0.051..4535.554 rows=510071 loops=1)

内容的提问来源于stack exchange，提问作者Chovy Chu