mysql - 带有外键的 MariaDB INNER JOIN 比没有外键慢得多

Question

请帮助我，我被 MariaDB 服务器的奇怪行为困住了。我有 3 张 table 。

CREATE TABLE `default_work` (
  `add_date` datetime(6) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `short_text` longtext DEFAULT NULL,
  `downloads` int(10) unsigned NOT NULL,
  `published` tinyint(1) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `work_type_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `default_work_subject_id_IDX` (`subject_id`) USING BTREE,
  KEY `default_work_work_type_id_IDX` (`work_type_id`) USING BTREE,
  CONSTRAINT `default_work_FK` FOREIGN KEY (`subject_id`) REFERENCES `default_subject` (`id`),
  CONSTRAINT `default_work_FK_1` FOREIGN KEY (`work_type_id`) REFERENCES `default_worktype` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=210673 DEFAULT CHARSET=utf8

CREATE TABLE `default_subject` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) NOT NULL,
  `old_id` int(10) unsigned NOT NULL,
  `subject_literal` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8

CREATE TABLE `default_worktype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `work_type` varchar(250) NOT NULL,
  `description` longtext DEFAULT NULL,
  `old_id` int(10) unsigned NOT NULL,
  `work_type_literal` varchar(250) NOT NULL,
  `title` varchar(255) NOT NULL,
  `multiple` varchar(255) NOT NULL,
  `keywords` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `default_worktype_old_id_a8b508fe_uniq` (`old_id`),
  UNIQUE KEY `default_worktype_work_type_literal_1e609434_uniq` (`work_type_literal`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

这些表是由 Django ORM 创建的，但似乎没问题。default_work 表有大约 200,000 条记录，default_subject - 42，default_worktype - 12。

在 Django admin 中通过这些表之间的简单连接发出请求后，我得到了大约 9 秒的查询时间。查看 SQL 日志我发现了一个原始查询:

SELECT `default_work`.`id`, `default_work`.`title`, `default_worktype`.`work_type`,`default_subject`.`subject`
FROM `default_work`
INNER JOIN `default_subject` ON (`default_work`.`subject_id` = `default_subject`.`id`)
INNER JOIN `default_worktype` ON (`default_work`.`work_type_id` = `default_worktype`.`id`) 
ORDER BY `default_work`.`id` DESC LIMIT 100

解释显示: Explain result of the query with indexes

这有点令人困惑，因为当我删除表 default_work 上除主键之外的所有索引时，结果完全不同。请求时间约为 3.4 毫秒，说明显示所有主键均已正确使用。 Explain result of the query without indexes

附言。我试图在 PostgreSQL 上重现这种情况，并得到了 1.3 毫秒的索引和外键请求。

Answer 1

查看您的 EXPLAIN 结果，您可以看到当外键打开时，系统在连接中使用该键，而不是选择使用目标表中的主键。 (第 2 行)

由于会有许多具有相同值的记录，因此它会大量增加正在评估的记录。

我不知道它为什么选择这样做。您可能会发现，以不同的顺序重写 select 语句会改变它选择索引的方式。如果在 ON 子句中先指定目标表，然后指定源表 (default_subject.id = default_work.subject_id)