如何优化MariaDB千万级contacts表关联的慢查询性能

性能瓶颈定位 #

当前查询的核心性能问题出在以下三点：

• 执行计划先取group_id=6的1133个业主，再按owner_id关联contacts表，每次关联后都要回表校验checkin时间范围，累计需要扫描百万级行数据
• 分组、排序时触发了临时表（Using temporary）和文件排序（Using filesort），大数据量下这两个操作会严重拖慢执行效率
• 现有索引都是单列索引，无法覆盖查询需要的所有字段，产生大量无用的回表IO开销

优化方案 #

1. 新增contacts表联合覆盖索引（优先级最高） #

按照等值匹配→范围过滤→需要返回的字段的索引设计规则，新建如下联合索引：

CREATE INDEX `idx_owner_checkin_unit` ON `contacts` (`owner_id`, `checkin`, `unit_id`);

该索引可以完全消除回表开销：

• 直接通过owner_id快速匹配对应业主的contacts记录
• 在索引层直接过滤checkin时间范围，不需要访问整行数据
• 分组需要的unit_id直接从索引获取，无需额外IO

2. 改写SQL语句，降低关联开销 #

当前语句写的LEFT JOIN owners但WHERE条件过滤了owners.group_id=6，本质等价于INNER JOIN，可以调整为先聚合取前20条再关联维度表，大幅减少需要关联的数据量：

SELECT 
  c.unit_id, 
  c.owner_id,
  u.description,
  u.address,
  o.name,
  o.email,
  c.contact_count
FROM (
  -- 先做聚合计算，仅保留前20条需要的unit数据
  SELECT 
    unit_id, 
    owner_id,
    COUNT(*) AS contact_count
  FROM contacts
  WHERE 
    checkin BETWEEN '2021-10-01 00:00:00' AND '2021-10-31 23:59:59'
    AND EXISTS (SELECT 1 FROM owners WHERE id = contacts.owner_id AND group_id = 6)
  GROUP BY unit_id
  ORDER BY contact_count DESC
  LIMIT 20
) AS c
INNER JOIN units u ON c.unit_id = u.id
INNER JOIN owners o ON c.owner_id = o.id;

3. 高频查询场景新增预聚合表 #

如果该类时间范围的top单元统计是高频业务查询，可新建月度汇总表提前计算结果，避免每次查询都扫描全量数据：

CREATE TABLE `unit_contact_monthly` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int unsigned NOT NULL,
  `year_month` char(7) NOT NULL COMMENT '格式如2021-10',
  `unit_id` int unsigned NOT NULL,
  `owner_id` int unsigned NOT NULL,
  `contact_count` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_group_month_unit` (`group_id`,`year_month`,`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过定时任务每天增量更新该表数据，查询时直接从汇总表取数，耗时可降到10ms以内。

优化效果说明 #

完成前两项优化后，查询耗时可从4分钟降到1秒以内，当前远没有触碰到硬件性能上限，不需要升级硬件配置。

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