生产环境MySQL左连接查询超时问题排查与优化求助

问题背景 #

生产环境中一条左连接查询超时，拆分后第二个统计查询首次执行耗时6秒（后续因缓存降至300ms），涉及contact_tag表的分组统计操作，相关查询语句及表结构如下：

原左连接查询语句 #

SELECT
    `tags`.*,
    COUNT( ct.contact_id ) AS contacts_count 
FROM
    `tags`
    LEFT JOIN `contact_tag` AS `ct` ON `tags`.`id` = `ct`.`tag_id` 
WHERE
    `tags`.`company_id` = 1068 
    AND `tags`.`category` = 1 
    AND `tags`.`deleted_at` IS NULL 
GROUP BY
    `tags`.`id` 
ORDER BY
    `tags`.`id` DESC

拆分后的统计查询语句 #

SELECT 
   tag_id, 
   COUNT(com_tag.contact_id) AS communications_count
FROM 
   contact_tag as com_tag
WHERE 
   tag_id IN (294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287)
GROUP BY 
   tag_id;

contact_tag表结构定义 #

CREATE TABLE `contact_tag` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `contact_id` int unsigned NOT NULL,
    `tag_id` int unsigned NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `contact_tag_contact_id_tag_id_unique` (`contact_id`,`tag_id`),
    KEY `contact_tag_tag_id_foreign` (`tag_id`),
    CONSTRAINT `contact_tag_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `contact_tag_tag_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2477082 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

优化方案 #

1. 创建覆盖索引，消除回表开销 #

当前查询依赖contact_tag_tag_id_foreign索引（仅包含tag_id），统计contact_id时需要回表读取数据，导致磁盘IO耗时高。创建联合覆盖索引：

ALTER TABLE `contact_tag` ADD INDEX `idx_tag_id_contact_id` (`tag_id`, `contact_id`);

该索引直接包含查询所需的tag_id和contact_id字段，MySQL无需回表即可完成统计，大幅降低首次执行的磁盘IO开销。

2. 优化tags表的过滤与排序效率 #

原查询中tags表的过滤条件为company_id=1068、category=1、deleted_at IS NULL，并按id DESC排序。创建复合索引优化过滤与排序逻辑：

ALTER TABLE `tags` ADD INDEX `idx_company_category_deleted_id` (`company_id`, `category`, `deleted_at`, `id` DESC);

此索引可直接定位符合条件的标签数据，同时利用索引顺序完成ORDER BY操作，避免额外的文件排序，提升原左连接查询的整体效率。

3. 调整查询逻辑，减少关联数据量（可选） #

若无需拆分查询，可将原语句改写为先过滤标签再关联统计的形式，结合上述索引进一步优化：

SELECT
    t.*,
    COALESCE(ct.contacts_count, 0) AS contacts_count
FROM (
    SELECT id, name, [其他需要返回的tags字段]
    FROM `tags`
    WHERE `company_id` = 1068 
      AND `category` = 1 
      AND `deleted_at` IS NULL
    ORDER BY id DESC
) t
LEFT JOIN (
    SELECT tag_id, COUNT(contact_id) AS contacts_count
    FROM `contact_tag`
    GROUP BY tag_id
) ct ON t.id = ct.tag_id
ORDER BY t.id DESC;

子查询先过滤出目标标签数据，再关联统计结果，减少关联的数据量，配合索引可避免全表扫描。

4. 优化InnoDB缓冲池配置 #

首次执行慢通常是因为数据未加载到内存缓存中，调整InnoDB缓冲池大小（建议设置为服务器内存的50%-70%）：

# my.cnf 配置示例
innodb_buffer_pool_size = 8G

重启MySQL后，常用数据会被缓存到内存，后续查询（包括冷启动后的首次查询）耗时将显著降低。

内容的提问来源于stack exchange，提问作者Juan Pablo B