mysql - 如何优化MySQL大表联合查询中的COUNT(DISTINCT ID)性能？

Question

真心求解! Mysql 两表联查,A表900w, B表1000w, 分页查询经过索引构建已经没什么问题, 但是count(id)的时候非常慢, 怎么优化

SELECT count(distinct A.id)
FROM A INNER JOIN B ON A.id = B.bus_id
WHERE A.trade_type in (0,1,2,4) and B.user_id IN (68305, 61831, 35444, 54923, 66298, 67438, 67440, 35427, 62213, 62900, 64550, 67658, 68103, 35441, 35466, 66801)
and B.bus_type = 2;
现有索引, A表id二级索引, B表有bus_id, user_id, 以及(bus_id, user_id, bus_type)联合索引, 现在分页查询获取A表数据ms没问题, 只有count(distinct A.id)很慢, 几十s, 不知道怎么处理了, 希望大家提点意见

这是explain得到的结果

ID	select_type	table	type	possible_key	key	key_len	ref	rows	extra
1	SIMPLE	B	index	idx_bus_id,idx_user_id,idx_bus_type_user_id	idx_bus_type_user_id	14		9962728	Using where; Using index
1	SIMPLE	A	eq_ref	PRIMARY	PRIMARY	4	B	1	Using index

Answer 1

要优化MySQL中涉及大表联合查询的COUNT(DISTINCT ID)操作的性能，你可以尝试以下几种方法：

1. 使用近似计数

对于非常大的数据集，精确计数可能非常耗时。如果业务场景允许，可以考虑使用近似计数方法，如MySQL的SQL_CALC_FOUND_ROWS（但注意，这在分页时可能不是最优选择，且对于COUNT(DISTINCT)不直接支持）或者使用第三方解决方案如Redis的HyperLogLog等。

2. 预先计算和缓存

• 定期计算并存储结果：如果查询条件（如trade_type和user_id列表）相对稳定，可以考虑定期（如每天或每小时）运行一个后台任务来计算这些COUNT(DISTINCT)值，并将结果存储在数据库的另一个表中或缓存系统（如Redis）中。
• 增量更新：当数据发生变化时，只更新受影响的计数值。

3. 优化查询和索引

• 检查并优化索引：确保你的索引是最优的。在你的场景中，你已经有了(bus_id, user_id, bus_type)的联合索引，这通常是一个好的开始。然而，对于COUNT(DISTINCT A.id)，如果A.id在A表中不是主键或唯一索引，确保它是索引的。
• 重写查询：尝试不同的查询写法，看看是否可以提高性能。虽然对于COUNT(DISTINCT)来说，重写空间可能有限，但值得一试。

4. 使用物化视图

如果你的MySQL版本支持（MySQL 5.7及以上版本对物化视图的支持有限，主要通过其他方式实现类似功能），可以考虑使用物化视图来存储计算后的COUNT(DISTINCT)值。然而，MySQL的物化视图功能不如一些其他数据库系统那样强大和直接。

5. 分析和调整配置

• 查询分析：使用EXPLAIN或EXPLAIN ANALYZE（MySQL 8.0+）来分析查询的执行计划，查看是否有可以优化的地方。
• 调整MySQL配置：考虑调整MySQL的配置参数，如buffer_pool_size、sort_buffer_size等，这些可以影响查询性能。

6. 分布式数据库或数据库分片

• 如果数据量继续增长，并且单个数据库实例已无法满足性能需求，考虑使用分布式数据库系统或数据库分片技术。这可以将数据分布在多个数据库实例上，从而提高查询性能。

结论

针对你的具体查询，最直接的改进可能是通过缓存或预先计算来避免实时计算COUNT(DISTINCT A.id)。同时，确保所有相关的列都已被正确索引，并尝试使用EXPLAIN来优化查询计划。如果这些方法仍不能满足性能要求，可能需要考虑更复杂的解决方案，如使用分布式数据库或近似计数方法。