请教一条 sql 语句执行完超过一秒的概率很高，该如何优化？

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 772 天前的主题，其中的信息可能已经有所发展或是发生改变。

两张表分别 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 ;

8 条回复