MySQLCOUNT(Joiningtable)獲取大表中的資料花費的時間太長-有解無憂

描述：

我們有如下兩個表：

table_1（“問題”主表）
table_2（“question_attempted”連接表）

案例：

在“table_2”中，我們有一個列，其中有一列“is_correct”（包含 1,0）用于正確或錯誤的答案。
在“table_1”中我們有 1m 條記錄，在“table_2”中我們有 10m 條記錄

我們想按以下列/值對串列資料進行排序：

嘗試問題的總次數
正確回答問題的總次數
正確回答了百分比問題（基于以上兩個值）

問題：

一旦我們加入 table_1 和 table_2 以獲得 total_questions_attempted、total_questiones_give_correct_answer、perntage_corrected_given_answers 的計數。查詢開始運行大約需要 8-10 分鐘。表結構如下。提前致謝。

表結構：

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';

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

https://i.stack.imgur.com/mHK6W.png

uj5u.com熱心網友回復：

正確的查詢是

SELECT mq.id, mq.name, COUNT(mqa.questionid)
FROM mdl_question mq
LEFT JOIN mdl_question_attempts mqa ON mq.id = mqa.questionid AND mqa.is_correct
WHERE NOT mq.is_deleted
GROUP by mq.id
ORDER by mq.sort_order DESC, mq.id DESC 
LIMIT 50;

現在讓我們看看，這能達到多快。問題表上只有一個標準（WHERE NOT mq.is_deleted）。我們可以假設即使不是大多數問題也有很多沒有被洗掉，所以這里使用索引乍一看沒有意義；閱讀完整的表格似乎更快。

然后我們在問題 ID 和 is_correct 標志上加入答案。這意味著我們至少應該在 ID 上有一個索引，甚至在 ID 和標志上更好：

CREATE INDEX idx1 ON mdl_question_attempts (questionid, is_correct);

現在我們必須按問題的 sort_order 和 ID 對所有行進行排序以獲得前 50 行。有一個已經排序的索引會很棒，這樣我們就可以從那里取出前 50 個條目。但是，我們只查看匹配的行NOT mq.is_deleted，因此索引必須包含該標志：

CREATE INDEX idx2 ON mdl_question (is_deleted, sort_order DESC, id DESC);

我們甚至可以包括名稱，因此所有資料都可以從索引中獲得，并且不能再讀取表（覆寫索引）。

CREATE INDEX idx2 ON mdl_question (is_deleted, sort_order DESC, id DESC, name);

是否使用這些索引仍然取決于 DBMS。我們提供它們只是為了給 DBMS 選擇。對于這個查詢，它取決于 MySQL 優化器的作業情況。它是否看到它可以只從問題索引中讀取前 50 個條目，然后使用答案索引進行簡單計數？

uj5u.com熱心網友回復：

在表中添加索引

在表名（列名）上創建索引索引名；

參考資料：https ://www.w3schools.com/sql/sql_create_index.asp

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/536562.html

標籤：数据库数据库加入

上一篇：Mysql查詢根據列值選擇行

下一篇：如何加入fillnullbypreviousvalue