Mysql

INNER JOIN 在某些行上非常慢並創建臨時表

  • March 24, 2020

MySQL 查詢

SELECT `p`.*,
      `p.Author`.*
FROM   `post` AS `p`
      INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid`
WHERE  ( ( `p`.`threadid` = 29780 )
        AND ( `p`.`parentid` <> 0 ) )
      AND `p`.`visible` IN ( 1, 2 )
ORDER  BY `p`.`dateline`,
         `p`.`postid`
LIMIT  10 offset 0

大約需要 800 毫秒到 1000 毫秒，並創建一個包含表中所有行的臨時user表（大約 17k）

解釋結果

奇怪的是，這不會出現在其他執行緒上。讓我們將threadid過濾器更改為 2978 1

在此處輸入圖像描述

在threadid = 29781PK上按預期使用。所以這個查詢要快得多，並且在我的測試伺服器上只用了大約 0.000056 毫秒，threadid = 29780執行了將近一秒鐘。無法解釋為什麼第一種情況會創建一個臨時表：user.userid是 PK。

在 Docker 上執行的 MariaDB 10.2 和 10.3 上進行了測試。

編輯

可以將其分解為以下查詢：

select post.*, user.*
from post
inner join user on user.userid=post.userid
where threadid=29780
order by post.dateline
limit 10

編輯#2

試圖強制使用索引

select post.*, user.*
from post use index(PRIMARY)
inner join user use index(PRIMARY) on user.userid=post.userid 
where threadid=29780
order by post.dateline
limit 10

上的索引post使它更快，但對於 sql (~100ms) 仍然很慢。EXPLAIN假設沒有post使用表上的鍵，因此掃描了整個表（~350k 行）。強制索引user似乎沒有任何效果。

ORDER BY似乎是問題所在。刪除它時，執行速度很好。儘管它似乎dateline有一個索引。通過（PK）訂購postid也需要很長時間。

背景

我正在使用 Entity Framework 核心來查詢現有的 vBulletin 數據庫。上面的查詢是 EF 生成的查詢的一部分，它應該獲取執行緒的回复。在測試中，我發現對一個測試執行緒的查詢非常慢。所以我把它分解成上面的查詢。

這裡的表定義：

郵政

CREATE TABLE `post` (
   `postid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `threadid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `parentid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `username` VARCHAR(100) NOT NULL DEFAULT '',
   `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `title` VARCHAR(250) NOT NULL DEFAULT '',
   `dateline` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `pagetext` MEDIUMTEXT NULL DEFAULT NULL,
   `allowsmilie` SMALLINT(6) NOT NULL DEFAULT '0',
   `showsignature` SMALLINT(6) NOT NULL DEFAULT '0',
   `ipaddress` VARCHAR(45) NOT NULL DEFAULT '',
   `iconid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `visible` SMALLINT(6) NOT NULL DEFAULT '0',
   `attach` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `infraction` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `reportthreadid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `post_thanks_amount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `htmlstate` ENUM('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
   `isForeign` INT(1) NULL DEFAULT '0',
   PRIMARY KEY (`postid`),
   INDEX `threadid` (`threadid`, `userid`),
   INDEX `threadid_visible_dateline` (`threadid`, `visible`, `dateline`, `userid`, `postid`),
   INDEX `ipaddress` (`ipaddress`),
   INDEX `dateline` (`dateline`),
   INDEX `userid` (`userid`, `parentid`),
   INDEX `user_date` (`userid`, `dateline`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

使用者

CREATE TABLE `user` (
   `userid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `usergroupid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `membergroupids` CHAR(250) NOT NULL DEFAULT '',
   `displaygroupid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `username` VARCHAR(100) NOT NULL DEFAULT '',
   `password` CHAR(32) NOT NULL DEFAULT '',
   `passworddate` DATE NOT NULL DEFAULT '1000-01-01',
   `email` CHAR(100) NOT NULL DEFAULT '',
   `styleid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `parentemail` CHAR(50) NOT NULL DEFAULT '',
   `homepage` CHAR(100) NOT NULL DEFAULT '',
   `icq` CHAR(20) NOT NULL DEFAULT '',
   `aim` CHAR(20) NOT NULL DEFAULT '',
   `yahoo` CHAR(32) NOT NULL DEFAULT '',
   `msn` CHAR(100) NOT NULL DEFAULT '',
   `skype` CHAR(32) NOT NULL DEFAULT '',
   `showvbcode` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `showbirthday` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '2',
   `usertitle` CHAR(250) NOT NULL DEFAULT '',
   `customtitle` SMALLINT(6) NOT NULL DEFAULT '0',
   `joindate` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `daysprune` SMALLINT(6) NOT NULL DEFAULT '0',
   `lastvisit` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `lastactivity` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `lastpost` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `lastpostid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `posts` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `reputation` INT(11) NOT NULL DEFAULT '10',
   `reputationlevelid` INT(10) UNSIGNED NOT NULL DEFAULT '1',
   `timezoneoffset` CHAR(4) NOT NULL DEFAULT '',
   `pmpopup` SMALLINT(6) NOT NULL DEFAULT '0',
   `avatarid` SMALLINT(6) NOT NULL DEFAULT '0',
   `avatarrevision` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `profilepicrevision` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `sigpicrevision` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `options` INT(10) UNSIGNED NOT NULL DEFAULT '33570831',
   `birthday` CHAR(10) NOT NULL DEFAULT '',
   `birthday_search` DATE NOT NULL DEFAULT '1000-01-01',
   `maxposts` SMALLINT(6) NOT NULL DEFAULT '-1',
   `startofweek` SMALLINT(6) NOT NULL DEFAULT '1',
   `ipaddress` VARCHAR(45) NOT NULL DEFAULT '',
   `referrerid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `languageid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `emailstamp` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `threadedmode` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `autosubscribe` SMALLINT(6) NOT NULL DEFAULT '-1',
   `pmtotal` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `pmunread` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `salt` CHAR(30) NOT NULL DEFAULT '',
   `ipoints` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `infractions` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `warnings` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `infractiongroupids` VARCHAR(255) NOT NULL DEFAULT '',
   `infractiongroupid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `adminoptions` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `profilevisits` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `friendcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `friendreqcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `vmunreadcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `vmmoderatedcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `socgroupinvitecount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `socgroupreqcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `pcunreadcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `pcmoderatedcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `gmmoderatedcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `post_thanks_user_amount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `post_thanks_thanked_posts` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `post_thanks_thanked_times` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `xperience` BIGINT(20) NULL DEFAULT '1',
   `xperience_done` SMALLINT(6) NULL DEFAULT '0',
   `xperience_level` SMALLINT(6) NULL DEFAULT '1',
   `xperience_levelp` SMALLINT(6) NULL DEFAULT '1',
   `xperience_next_level` BIGINT(20) NULL DEFAULT '0',
   `xperience_next_level_points` BIGINT(20) NULL DEFAULT '1',
   `xperience_ppd` FLOAT NULL DEFAULT '0',
   `xperience_awards` MEDIUMTEXT NULL DEFAULT NULL,
   `xperience_shopitems` MEDIUMTEXT NULL DEFAULT NULL,
   `xperience_achievements` MEDIUMTEXT NULL DEFAULT NULL,
   `xperience_lastupdate` INT(10) NULL DEFAULT '0',
   `xperience_awardcount` SMALLINT(6) NULL DEFAULT '0',
   `xperience_promotioncount` SMALLINT(6) NULL DEFAULT '0',
   `xperience_achievementcount` SMALLINT(6) NULL DEFAULT '0',
   `ncode_imageresizer_mode` ENUM('none','enlarge','samewindow','newwindow') NULL DEFAULT NULL,
   `ncode_imageresizer_maxwidth` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
   `ncode_imageresizer_maxheight` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
   `vbseo_likes_in` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `vbseo_likes_out` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `vbseo_likes_unread` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `itrader_total` INT(10) NOT NULL DEFAULT '0',
   `itrader_pcnt` INT(10) NOT NULL DEFAULT '0',
   `itrader_buy` VARCHAR(255) NOT NULL DEFAULT '',
   `itrader_sell` VARCHAR(255) NOT NULL DEFAULT '',
   `assetposthash` VARCHAR(32) NOT NULL DEFAULT '',
   `fbuserid` VARCHAR(255) NOT NULL,
   `fbjoindate` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `fbname` VARCHAR(255) NOT NULL,
   `logintype` ENUM('vb','fb') NOT NULL DEFAULT 'vb',
   `fbaccesstoken` VARCHAR(255) NOT NULL,
   `newrepcount` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
   `bloggroupreqcount` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `showblogcss` INT(11) NOT NULL DEFAULT '1',
   `vsafrules_date` INT(10) NOT NULL DEFAULT '0',
   `vsafrules_sets` VARCHAR(40) NOT NULL DEFAULT '0',
   `showvsastats` TINYINT(1) NOT NULL DEFAULT '1',
   `ars_count` INT(10) UNSIGNED NOT NULL DEFAULT '0',
   `indexpage` INT(11) NULL DEFAULT NULL,
   `isForeign` INT(1) NULL DEFAULT '0',
   `stylechoosen` INT(1) UNSIGNED NULL DEFAULT '0',
   `recent_thankcnt` INT(3) NOT NULL DEFAULT '0',
   `recent_thankact` TINYINT(1) NOT NULL DEFAULT '1',
   `uloShowDesktopNotifications` INT(1) UNSIGNED NOT NULL DEFAULT '1',
   `source` VARCHAR(50) NULL DEFAULT NULL,
   PRIMARY KEY (`userid`),
   INDEX `usergroupid` (`usergroupid`),
   INDEX `username` (`username`),
   INDEX `birthday` (`birthday`, `showbirthday`),
   INDEX `birthday_search` (`birthday_search`),
   INDEX `referrerid` (`referrerid`),
   INDEX `post_thanks_thanked_times` (`post_thanks_thanked_times`),
   INDEX `fbuserid` (`fbuserid`),
   INDEX `email` (`email`),
   INDEX `lastactivity` (`lastactivity`),
   INDEX `post_thanks_thanked_times_2` (`post_thanks_thanked_times`),
   INDEX `itrader_total` (`itrader_total`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;

為了這

   WHERE  `p`.`threadid` = 29780
     AND  `p`.`parentid` <> 0
     AND  `p`.`visible` IN ( 1, 2 )

以下索引是最佳的：

INDEX(threadid, visible)

在其上添加更多列（您已經完成）可能無濟於事。

WHERE由於. _ _ _ ORDER BY_ <>所以你被一個排序和隨之而來的臨時表困住了。此外，由於您無法INDEX同時處理 theWHERE和 the ORDER BY，因此您無法及時到達LIMIT以防止獲取可能的大量行。

即使您擺脫了p.parentid <> 0，IN也會阻止使用單個索引來完成任務。

STRAIGHT_JOIN可能是一個解決方案。這應該強制它首先引用p。

這非常令人困惑：p.Author 表示數據庫 p，表 Author。然而，您似乎有一個名為 p.Author 的表？！

不要使用 MyISAM，使用 InnoDB。

不要使用CHAR除了真正恆定大小的列；使用VARCHAR.

user表應該同時具有(id, userid)和列的多(userid, id)列索引，而post表應該具有索引(postid, threadid, parentid, userid, visible, dateline)出現在後一個索引中的順序列也可以更改，但我估計建議的結果可以承受。

另外我的建議是從即將過時的 MyISAM 引擎遷移到 InnoDB。

引用自：https://dba.stackexchange.com/questions/226113

相關問答

Mysql

使用映射表時獲取未映射記錄的計數

  • March 3, 2022
檢視問答
Mysql

通過自我 JOIN 和 GROUP BY 更新表

  • December 29, 2021
檢視問答
Mysql

改進查詢（使用 ctes 在範圍上進行令人討厭的自連接）

  • October 7, 2021
檢視問答
Mysql

查找總計不匹配的行

  • June 17, 2021
檢視問答
Mysql

LEFT JOIN 不加入第二個表

  • May 16, 2021
檢視問答
Mysql

根據兩個條件 SQL 獲取某行上方和下方（相鄰行）的行

  • January 17, 2021
檢視問答