MySQL 在 JOIN 查询中不使用索引

Question

我有一个包含 500000 多行的主表。

CREATE TABLE `esc_questions`(
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `esc_id` INT(11) NOT NULL,
    `question_text` LONGTEXT COLLATE utf8_unicode_ci NOT NULL,
    `answer_1` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `answer_2` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `answer_3` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `answer_4` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `answer_5` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `right_answer` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
    `disciplinas_id` INT(11) UNSIGNED NOT NULL,
    `assunto_id` INT(11) UNSIGNED NOT NULL,
    `orgao_id` INT(11) UNSIGNED NOT NULL,
    `cargo_id` INT(11) UNSIGNED NOT NULL,
    `ano` INT(11) NOT NULL,
    `banca_id` INT(11) UNSIGNED NOT NULL,
    `question_type` TINYINT(4) NOT NULL,
    `url` TEXT COLLATE utf8_unicode_ci NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY(`id`),
    KEY `idx_ano`(`ano`) USING BTREE,
    KEY `idx_question_type`(`question_type`) USING BTREE,
    KEY `idx_cargo_id`(`cargo_id`) USING BTREE,
    KEY `idx_orgao_id`(`orgao_id`) USING BTREE,
    KEY `idx_banca_id`(`banca_id`) USING BTREE,
    KEY `idx_question_id`(`id`) USING BTREE,
    KEY `idx_assunto_id`(`assunto_id`) USING BTREE,
    KEY `idx_disciplinas_id`(`disciplinas_id`) USING BTREE,
    CONSTRAINT `fk_assunto_id` FOREIGN KEY(`assunto_id`) REFERENCES `esc_assunto`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_banca_id` FOREIGN KEY(`banca_id`) REFERENCES `esc_bancas`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_cargo_id` FOREIGN KEY(`cargo_id`) REFERENCES `esc_cargo`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_disciplinas_id` FOREIGN KEY(`disciplinas_id`) REFERENCES `esc_disciplinas`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_orgao_id` FOREIGN KEY(`orgao_id`) REFERENCES `esc_orgao`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB AUTO_INCREMENT = 516157 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci

相关数据存储到另外五个表中，与此表非常相似:

CREATE TABLE `esc_assunto`(
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `idx_assunto_id`(`id`) USING BTREE,
KEY `idx_assunto_name`(`name`(30)),
CONSTRAINT `fk_assunto` FOREIGN KEY(`id`) REFERENCES `esc_questions`(`assunto_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = INNODB AUTO_INCREMENT = 3618 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci

我的网站上有分页。当我尝试获取最新页面时，数据请求所花费的时间正在增加。这是我对此任务的选择:

SELECT
    f.*,
    d.name disciplinas,
    o.name orgao,
    c.name cargo,
    b.name banca,
    a.name assunto
FROM
    `esc_questions` f
INNER JOIN
    `esc_bancas` b
ON
    f.banca_id = b.id
INNER JOIN
    `esc_disciplinas` d
ON
    f.disciplinas_id = d.id
INNER JOIN
    `esc_assunto` a
ON
    f.assunto_id = a.id
INNER JOIN
    `esc_orgao` o
ON
    f.orgao_id = o.id
INNER JOIN
    `esc_cargo` c
ON
    f.cargo_id = c.id
LIMIT 400020, 20

此查询在查询分析器中显示的发送数据阶段需要很长时间。
发送数据 17.6 秒 99.99% 1 17.6 秒

EXPLAIN 显示以下内容:
1 个简单 d 所有小学，idx_disciplinas_id 247

1 简单引用 idx_cargo_id、idx_orgao_id、idx_banca_id、idx_assunto_id、idx_disciplinas_id idx_disciplinas_id 4 concursos.d.id 1116

1 简单 o eq_ref PRIMARY,idx_orgao_id PRIMARY 4 concursos.f.orgao_id 1

1 简单 c eq_ref PRIMARY,idx_cargo_id PRIMARY 4 concursos.f.cargo_id 1

1 简单 a eq_ref PRIMARY,idx_assunto_id PRIMARY 4 concursos.f.assunto_id 1

1 SIMPLE b eq_ref PRIMARY,idx_bancas_id PRIMARY 4 concursos.f.banca_id 1


我花了一整天的时间来快速完成这项工作，但没有成功。

有人可以告诉我我的选择查询有什么问题或者为什么 MySQL 不使用索引吗？

感谢任何帮助。

Answer 1

你有几个说法是错误的。首先，您的查询没有 order by 子句。不保证查询在多次执行时以相同的顺序返回结果(尽管在实践中查询确实如此，但调试此类问题可能非常困难)。

因此，您应该添加一个 order by，可能在 esc_questions 的主键上以及任何必要的辅助键上。

其次，400020的偏移量较大。 MySQL 将生成 400,020 行并在找到第 400,021 行之前丢弃它们。

我的建议是找到排序中使用的“id”，然后包含 where 子句:

where ?? > $last_id
. . .
order by ??
limit 20

这可能不会(或可能)加快第一次加载速度，但它应该会加快后续加载速度。