MySQL关联大表执行COUNT统计查询过慢问题优化请求

描述 #

我们有两张表：

• table_1（question主表）
• table_2（question_attempted关联表）

情况说明 #

• table_2包含is_correct字段，取值为1或0，用于标记答案正确/错误
• table_1存储100万条记录，table_2存储1000万条记录

统计需求 #

需要按以下维度对数据排序展示：

• 问题被尝试的总次数
• 问题被答对的总次数
• 问题的答对率（由前两个值计算得出）

当前问题 #

关联两张表统计上述指标时，查询耗时约8-10分钟，请求优化方案。

表结构 #

question表结构 #

CREATE TABLE IF NOT EXISTS `question` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` bigint(20) NOT NULL DEFAULT 0,
  `parent` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `name` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `questiontext` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `questiontextformat` tinyint(4) NOT NULL DEFAULT 0,
  `generalfeedback` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `generalfeedbackformat` tinyint(4) NOT NULL DEFAULT 0,
  `defaultmark` decimal(12,7) NOT NULL DEFAULT 1.0000000,
  `penalty` decimal(12,7) NOT NULL DEFAULT 0.3333333,
  `qtype` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '''1''',
  `length` bigint(20) UNSIGNED NOT NULL DEFAULT 1,
  `stamp` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `version` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `hidden` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `timecreated` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `timemodified` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `createdby` bigint(20) UNSIGNED DEFAULT NULL,
  `modifiedby` bigint(20) UNSIGNED DEFAULT NULL,
  `type_data_id` bigint(20) NOT NULL,
  `img_id` bigint(20) DEFAULT NULL,
  `qimg_gallary_text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qrimg_gallary_text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qimg_gallary_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qrimg_gallary_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_id` bigint(20) NOT NULL DEFAULT 0,
  `ques_type_id` bigint(20) DEFAULT NULL,
  `year` bigint(20) DEFAULT NULL,
  `spec` bigint(20) DEFAULT NULL,
  `sub_speciality_id` int(11) DEFAULT NULL,
  `sub_sub_speciality_id` int(11) DEFAULT NULL,
  `spec_level` bigint(20) DEFAULT 1,
  `is_deleted` int(11) NOT NULL DEFAULT 0,
  `sequence` int(11) NOT NULL DEFAULT 0,
  `sort_order` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Question order in list',
  `idnumber` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `addendum` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text_for_search` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'this is for the text based searching, this will store the text of the question without html tags',
  `text_for_search_ans` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `type_data_id` (`type_data_id`),
  UNIQUE KEY `mdl_ques_catidn_uix` (`category`,`idnumber`),
  KEY `mdl_ques_cat_ix` (`category`),
  KEY `mdl_ques_par_ix` (`parent`),
  KEY `mdl_ques_cre_ix` (`createdby`),
  KEY `mdl_ques_mod_ix` (`modifiedby`),
  KEY `id` (`id`),
  KEY `mq_spec_ix` (`spec`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='The questions themselves';

question_attempted表结构 #

CREATE TABLE IF NOT EXISTS `question_attempted` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `questionusageid` bigint(20) UNSIGNED NOT NULL,
  `slot` bigint(20) UNSIGNED NOT NULL,
  `behaviour` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `questionid` bigint(20) UNSIGNED NOT NULL,
  `variant` bigint(20) UNSIGNED NOT NULL DEFAULT 1,
  `maxmark` decimal(12,7) NOT NULL,
  `minfraction` decimal(12,7) NOT NULL,
  `flagged` tinyint(3) UNSIGNED NOT NULL DEFAULT 2,
  `questionsummary` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rightanswer` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responsesummary` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timemodified` bigint(20) UNSIGNED NOT NULL,
  `maxfraction` decimal(12,7) DEFAULT 1.0000000,
  `in_remind_state` int(11) NOT NULL DEFAULT 0,
  `is_correct` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mdl_quesatte_queslo_uix` (`questionusageid`,`slot`),
  KEY `mdl_quesatte_que_ix` (`questionid`),
  KEY `mdl_quesatte_que2_ix` (`questionusageid`),
  KEY `mdl_quesatte_beh_ix` (`behaviour`),
  KEY `questionid` (`questionid`),
  KEY `is_correct` (`is_correct`)
) ENGINE=InnoDB AUTO_INCREMENT=151176 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Each row here corresponds to  an attempt at one question, as ';   

尝试过的查询语句 #

SELECT mq.id, mq.name, COUNT(is_correct) 
FROM mdl_question_attempts as mqa 
LEFT JOIN mdl_question mq on mq.id = mqa.questionid where mq.id IS NOT NULL and mq.is_deleted = '0' 
GROUP by mqa.questionid 
ORDER by mq.sort_order desc, mq.id DESC 
LIMIT 50

优化方案 #

1. 先聚合关联表，再关联主表 #

原查询先关联再聚合，会生成大量中间数据。先对question_attempted按questionid聚合统计，再关联主表，大幅减少关联数据量：

SELECT 
    mq.id, 
    mq.name, 
    agg.total_attempts, 
    agg.correct_attempts,
    ROUND(agg.correct_attempts / agg.total_attempts, 4) AS correct_rate
FROM (
    SELECT 
        questionid,
        COUNT(*) AS total_attempts,
        SUM(is_correct) AS correct_attempts
    FROM question_attempted
    GROUP BY questionid
) AS agg
JOIN question mq 
    ON mq.id = agg.questionid 
    AND mq.is_deleted = 0
ORDER BY 
    mq.sort_order DESC, 
    mq.id DESC
LIMIT 50;

2. 优化索引 #

• 给question_attempted创建复合索引：CREATE INDEX idx_qa_qid_correct ON question_attempted(questionid, is_correct);，聚合时可直接通过索引获取数据，避免全表扫描
• 给question表创建复合索引：CREATE INDEX idx_q_deleted_sort ON question(is_deleted, sort_order, id, name);，关联和排序时直接命中索引，避免回表查询

3. 修正连接逻辑 #

原查询用LEFT JOIN却添加mq.id IS NOT NULL，等价于INNER JOIN，直接用内连接减少无效数据处理。

4. 预计算统计值（适合非实时场景） #

如果对数据实时性要求不高，可创建统计表定时同步聚合结果，查询速度会大幅提升：

-- 创建统计表
CREATE TABLE question_stats (
    questionid BIGINT UNSIGNED PRIMARY KEY,
    total_attempts BIGINT UNSIGNED NOT NULL DEFAULT 0,
    correct_attempts BIGINT UNSIGNED NOT NULL DEFAULT 0,
    correct_rate DECIMAL(5,4) NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (questionid) REFERENCES question(id)
);

-- 定时更新脚本（可通过数据库事件或Cron执行）
REPLACE INTO question_stats (questionid, total_attempts, correct_attempts, correct_rate)
SELECT 
    questionid,
    COUNT(*) AS total_attempts,
    SUM(is_correct) AS correct_attempts,
    ROUND(SUM(is_correct)/COUNT(*),4) AS correct_rate
FROM question_attempted
GROUP BY questionid;

查询时直接关联统计表：

SELECT 
    mq.id,
    mq.name,
    qs.total_attempts,
    qs.correct_attempts,
    qs.correct_rate
FROM question mq
JOIN question_stats qs ON mq.id = qs.questionid
WHERE mq.is_deleted = 0
ORDER BY mq.sort_order DESC, mq.id DESC
LIMIT 50;

5. 细节优化 #

• is_deleted是数值类型，原查询用字符串'0'匹配，改成0避免隐式类型转换
• 只查询需要的字段，避免SELECT *，减少数据传输和内存占用

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