我面临一个奇怪的错误。我在本地Mac(Brew)上安装了5.5.5-10.1.20-MariaDB,在我的Prod Server上安装了5.5.52-MariaDB(CentOS7)。我本地的数据库内容是我服务器DB的副本。我已经在本地执行了此查询:
## CREATE DIRECT RELATION BETWEEN JOURNAL AND PUBLICATION
INSERT INTO journal_publication (journal_id, `publication_id`) (
select issues.journal_id as journal_id, publications.id as publication_id from issues
join publications on issues.id = publications.`issue_id`
where publications.id Not In (select distinct publication_id from journal_publication)
);
它可以正常工作,只需不到一秒钟即可执行。现在,当我在产品服务器上尝试完全相同的查询时,查询永远不会结束,并且需要所有CPU。而且,我试图解释查询,它在我的本地上正常工作:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY issues index PRIMARY issues_journal_id_foreign 5 NULL 70993 Using index; Using temporary
1 PRIMARY publications ref publications_issue_id_foreign publications_issue_id_foreign 5 pubpeer.issues.id 1 Using where; Using index
2 MATERIALIZED journal_publication index NULL PRIMARY 8 NULL 143926 Using index
虽然我的产品上的相同查询返回错误:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO journal_publication (journal_id, `publication_id`)
(select issues.j' at line 2
再次,两个DBS的内容是相同的,主键和索引的设置平均设置。为了记录,当我尝试执行此查询时:
select issues.journal_id as journal_id, publications.id as publication_id from issues
join publications on issues.id = publications.`issue_id`
where publications.id Not In (select distinct publication_id from journal_publication;
在本地或产品上只需要一秒钟。
您是否有任何线索或过程,可以帮助我了解这些差异?
谢谢。
Xavier
-
Mariadb服务器版本<10.0仅支持
EXPLAIN SELECT
-
Mariadb服务器版本> = 10.0支持另外
EXPLAIN UPDATE
,EXPLAIN INSERT
和EXPLAIN DELETE
请注意,版本字符串5.5.5-10.1.20-mariaDB表示Mariadb 10.1.20,由于MySQL复制会破裂,因此需要5.5.5前缀,因为它仅支持主要版本的1位数字。<<<<<<<<<<<。/p>
另请参见MySQL和Mariadb中的udate/insert/delete