两张表分别 30w 和 40w 的数据量,请教下大神为啥下面的 sql 语句总是执行超过 1s 有时候甚至 3s 往上。
1.mysql 执行语句
EXPLAIN SELECT t . * , m.icon
FROM threads t
LEFT JOIN members m ON t.authorid = m.uid
WHERE t.fid = '1'
AND t.ifcheck = '1'
AND t.topped = '0'
ORDER BY lastpost DESC
LIMIT 0 , 40
2.EXPLAIN 分析结果
3.两张表的结构
threads 表大概 40w 数据
CREATE TABLE IF NOT EXISTS `threads` (
`tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fid` smallint(6) unsigned NOT NULL DEFAULT '0',
`icon` tinyint(3) NOT NULL DEFAULT '0',
`titlefont` varchar(15) NOT NULL DEFAULT '',
`author` varchar(15) NOT NULL DEFAULT '',
`authorid` int(10) unsigned NOT NULL DEFAULT '0',
`subject` varchar(100) NOT NULL DEFAULT '',
`toolinfo` varchar(16) NOT NULL DEFAULT '',
`toolfield` varchar(21) NOT NULL DEFAULT '',
`ifcheck` tinyint(3) NOT NULL DEFAULT '0',
`type` smallint(6) NOT NULL DEFAULT '0',
`postdate` int(10) unsigned NOT NULL DEFAULT '0',
`lastpost` int(10) unsigned NOT NULL DEFAULT '0',
`lastposter` varchar(15) NOT NULL DEFAULT '',
`hits` int(10) unsigned NOT NULL DEFAULT '0',
`replies` int(10) unsigned NOT NULL DEFAULT '0',
`favors` int(10) NOT NULL DEFAULT '0',
`modelid` smallint(6) unsigned NOT NULL DEFAULT '0',
`shares` mediumint(8) unsigned NOT NULL DEFAULT '0',
`topped` smallint(6) NOT NULL DEFAULT '0',
`locked` tinyint(3) NOT NULL DEFAULT '0',
`digest` tinyint(3) NOT NULL DEFAULT '0',
`special` tinyint(3) NOT NULL DEFAULT '0',
`state` tinyint(3) NOT NULL DEFAULT '0',
`ifupload` tinyint(3) NOT NULL DEFAULT '0',
`ifmail` tinyint(3) NOT NULL DEFAULT '0',
`ifmark` smallint(6) NOT NULL DEFAULT '0',
`ifshield` tinyint(3) NOT NULL DEFAULT '0',
`anonymous` tinyint(3) NOT NULL DEFAULT '0',
`dig` int(10) NOT NULL DEFAULT '0',
`fight` int(10) NOT NULL DEFAULT '0',
`ptable` tinyint(3) NOT NULL DEFAULT '0',
`ifmagic` tinyint(3) NOT NULL DEFAULT '0',
`ifhide` tinyint(3) NOT NULL DEFAULT '0',
`inspect` varchar(30) NOT NULL DEFAULT '',
`tpcstatus` int(10) unsigned NOT NULL DEFAULT '0',
`topreplays` smallint(6) NOT NULL DEFAULT '0',
`specialsort` tinyint(3) unsigned NOT NULL DEFAULT '0',
`frommob` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`tid`),
KEY `idx_authorid` (`authorid`),
KEY `idx_postdate` (`postdate`),
KEY `idx_digest` (`digest`),
KEY `idx_fid_type_ifcheck` (`fid`,`type`,`ifcheck`),
KEY `idx_special` (`special`),
KEY `idx_fid_ifcheck_specialsort_lastpost` (`fid`,`ifcheck`,`specialsort`,`lastpost`),
KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1;
members 表大概 30w 条数据
CREATE TABLE IF NOT EXISTS `members` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(40) NOT NULL DEFAULT '',
`safecv` varchar(10) NOT NULL DEFAULT '',
`email` varchar(60) NOT NULL DEFAULT '',
`groupid` tinyint(3) NOT NULL DEFAULT '-1',
`memberid` tinyint(3) NOT NULL DEFAULT '0',
`groups` varchar(255) NOT NULL DEFAULT '',
`icon` varchar(255) NOT NULL DEFAULT '',
`gender` tinyint(1) NOT NULL DEFAULT '0',
`regdate` int(10) unsigned NOT NULL DEFAULT '0',
`signature` text NOT NULL,
`introduce` text NOT NULL,
`oicq` varchar(12) NOT NULL DEFAULT '',
`aliww` varchar(30) NOT NULL,
`icq` varchar(12) NOT NULL DEFAULT '',
`msn` varchar(35) NOT NULL DEFAULT '',
`yahoo` varchar(35) NOT NULL DEFAULT '',
`site` varchar(75) NOT NULL DEFAULT '',
`location` varchar(36) NOT NULL DEFAULT '',
`honor` varchar(100) NOT NULL DEFAULT '',
`bday` date NOT NULL DEFAULT '0000-00-00',
`lastaddrst` varchar(255) NOT NULL DEFAULT '',
`yz` int(10) NOT NULL DEFAULT '1',
`timedf` varchar(5) NOT NULL DEFAULT '',
`style` varchar(12) NOT NULL DEFAULT '',
`datefm` varchar(15) NOT NULL DEFAULT '',
`t_num` tinyint(3) unsigned NOT NULL DEFAULT '0',
`p_num` tinyint(3) unsigned NOT NULL DEFAULT '0',
`attach` varchar(50) NOT NULL DEFAULT '',
`hack` varchar(255) NOT NULL DEFAULT '0',
`newpm` smallint(6) unsigned NOT NULL DEFAULT '0',
`banpm` text NOT NULL,
`msggroups` varchar(255) NOT NULL DEFAULT '',
`medals` varchar(255) NOT NULL DEFAULT '',
`userstatus` int(10) unsigned NOT NULL DEFAULT '0',
`shortcut` varchar(255) NOT NULL DEFAULT '',
`salt` char(6) NOT NULL DEFAULT '',
`authmobile` char(16) NOT NULL DEFAULT '',
`realname` varchar(16) NOT NULL DEFAULT '',
`apartment` int(10) unsigned NOT NULL DEFAULT '0',
`home` int(10) unsigned NOT NULL DEFAULT '0',
`previewimg_flag` int(1) NOT NULL DEFAULT '0',
`conisbind` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`),
UNIQUE KEY `idx_username` (`username`),
KEY `idx_groupid` (`groupid`),
KEY `idx_email` (`email`),
KEY `authmobile` (`authmobile`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=1 ;
1
scemsjyd 2022-03-18 08:54:49 +08:00 via iPhone
你把‘1’ 换成 1 ,注意类型
|
3
ccw 2022-03-18 09:08:08 +08:00
先查 threads ,根据分页后的结果 id 再查 members ,最后再组装数据。
|
4
justrand OP 知道原因了,ORDER BY lastpost 导致的,已经解决!
|
6
xiayushengfan 2022-03-18 11:36:20 +08:00
@justrand 怎么排查出来的??
|
7
justrand OP @xiayushengfan #6 explain 里面 Using filesort
|
8
justrand OP @xiayushengfan #6 Using filesort
|