含外键的MySQL慢查询性能优化求助

问题说明 #

关联两个外键并带ID过滤的查询运行极慢，移除a.company_id IN(...)后查询耗时仅3秒，但移除a.audit_template_id IN(...)后仍需250+秒。

原始查询 #

SELECT `a`.`id` AS `_audit_id` 
FROM `audits` AS `a` 
INNER JOIN `audit_templates` AS `aat` ON `aat`.`id` = `a`.`audit_template_id` 
INNER JOIN `companies` AS `c` ON `c`.`id` = `a`.`company_id` 
WHERE `a`.`organization_id` = 484 
  AND `a`.`date` >= '2025-01-01' 
  AND `a`.`date` <= '2025-01-31' 
  AND `a`.`company_id` IN (11802,26551, ...297_ids_total) 
  AND `a`.`audit_template_id` IN (37,49,85,97,378,498,542,664,..3632_items_total);

audits表结构 #

CREATE TABLE `audits` (    
    `id` int unsigned NOT NULL AUTO_INCREMENT,    
    `corporation_id` int unsigned DEFAULT NULL,    
    `organization_id` int unsigned DEFAULT NULL,    
    `department_id` int unsigned DEFAULT NULL,    
    `audit_template_id` int unsigned NOT NULL,    
    `company_id` int unsigned DEFAULT NULL,    
    `user_id` int unsigned NOT NULL,    
    `group` varchar(255) DEFAULT NULL,    
    `guid` varchar(255) DEFAULT NULL,    
    `date` date NOT NULL,    
    `time` time NOT NULL,    
    `updated_at` timestamp NULL DEFAULT NULL,    
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,    
    `deleted_at` timestamp NULL DEFAULT NULL,    
    `deleted_user_id` int unsigned DEFAULT NULL,    
    PRIMARY KEY (`id`),    
    KEY `fk-audit_template-audits` (`audit_template_id`),    
    KEY `fk-company-audits` (`company_id`),    
    KEY `fk-user-audits` (`user_id`),    
    KEY `unq-guid` (`guid`),    
    KEY `idx-guid` (`guid`),    
    KEY `fk-corporation-audits` (`corporation_id`),    
    KEY `fk-organization-audits` (`organization_id`),    
    KEY `date` (`date`),    
    KEY `idx-audit-unq_id` (`unq_id`),    
    KEY `idx-audit-date` (`date`),    
    KEY `idx-audit-time` (`time`),    
    KEY `idx-audit-group` (`group`) USING BTREE,    
    KEY `idx-audit-date-time` (`date`,`time`) USING BTREE,    
    KEY `audits_deleted_user_id_foreign` (`deleted_user_id`),    
    CONSTRAINT `audits_ibfk_1` FOREIGN KEY (`audit_template_id`) REFERENCES `audit_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,    
    CONSTRAINT `audits_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,    
    CONSTRAINT `audits_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,    
    CONSTRAINT `fk-corporation-audits` FOREIGN KEY (`corporation_id`) REFERENCES `corporations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,    
    CONSTRAINT `fk-department-audits` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,    
    CONSTRAINT `fk-organization-audits` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20968336 DEFAULT CHARSET=utf8mb3;

EXPLAIN执行计划 #

[  
    {    
        "id": 1,    
        "select_type": "SIMPLE",    
        "table": "c",    
        "partitions": null,    
        "type": "range",    
        "possible_keys": "PRIMARY",    
        "key": "PRIMARY",    
        "key_len": "4",    
        "ref": null,    
        "rows": 298,    
        "filtered": 100.00,    
        "Extra": "Using where; Using index"  
    },  
    {    
        "id": 1,    
        "select_type": "SIMPLE",    
        "table": "a",    
        "partitions": null,    
        "type": "ref",    
        "possible_keys": "fk-audit_template-audits,fk-company-audits,fk-organization-audits,date,idx-audit-date,idx-audit-date-time,fk-audit-organization-department,audits_company_id_organization_id_department_id_index",    
        "key": "audits_company_id_organization_id_department_id_index",    
        "key_len": "10",    
        "ref": "freshability.c.id,const",    
        "rows": 403,    
        "filtered": 3.21,    
        "Extra": "Using where"  
    },  
    {    
        "id": 1,    
        "select_type": "SIMPLE",    
        "table": "aat",    
        "partitions": null,    
        "type": "eq_ref",    
        "possible_keys": "PRIMARY",    
        "key": "PRIMARY",    
        "key_len": "4",    
        "ref": "freshability.a.audit_template_id",    
        "rows": 1,    
        "filtered": 100.00,    
        "Extra": "Using index"  
    }
]

优化方向 #

1. 创建针对性复合覆盖索引 #

当前audits表缺少覆盖所有过滤条件的复合索引，这是性能瓶颈核心。推荐创建以下索引：

-- 优先尝试：覆盖所有过滤条件+目标字段，避免回表
CREATE INDEX idx_audits_org_date_company_template ON audits(organization_id, date, company_id, audit_template_id, id);

• 逻辑：等值过滤字段organization_id放最左，范围过滤字段date紧随其后，再放两个IN过滤字段，最后包含查询目标id，让索引成为覆盖索引。

如果移除company_id过滤后仍慢，补充：

CREATE INDEX idx_audits_org_date_template ON audits(organization_id, date, audit_template_id, id);

2. 优化超长IN列表的处理 #

audit_template_id的IN列表包含3632个值，MySQL对过长IN列表优化效率低，可改用临时表关联：

CREATE TEMPORARY TABLE temp_templates (id INT UNSIGNED PRIMARY KEY);
INSERT INTO temp_templates VALUES (37),(49),...,(3632); -- 替换为实际ID列表

SELECT a.id AS _audit_id 
FROM audits a
JOIN temp_templates tt ON a.audit_template_id = tt.id
JOIN companies c ON a.company_id = c.id
WHERE a.organization_id = 484 
  AND a.date BETWEEN '2025-01-01' AND '2025-01-31'
  AND a.company_id IN (11802,26551,...);

3. 调整JOIN顺序 #

当前优化器选择先扫描companies表，若organization_id + date的过滤能筛选更少数据，可强制先扫描audits表：

SELECT STRAIGHT_JOIN a.id AS _audit_id 
FROM audits a
INNER JOIN audit_templates aat ON aat.id = a.audit_template_id 
INNER JOIN companies c ON c.id = a.company_id 
WHERE a.organization_id = 484 
  AND a.date >= '2025-01-01' 
  AND a.date <= '2025-01-31' 
  AND a.company_id IN (...) 
  AND a.audit_template_id IN (...);

4. 移除冗余关联 #

由于外键约束已保证audit_template_id和company_id对应记录存在，若业务无需额外验证，可直接移除两个JOIN：

SELECT a.id AS _audit_id 
FROM audits a
WHERE a.organization_id = 484 
  AND a.date BETWEEN '2025-01-01' AND '2025-01-31' 
  AND a.company_id IN (...) 
  AND a.audit_template_id IN (...);

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