一句Sql语句的优化（带子查询）

全部话题移民创业 iOS Mac Objective-C Swift Android 招聘求职

由 tinyfool 发布于 2013年11月20日
无人欣赏。

SELECT * FROM `threads` where `createbyid` <> 24016 AND `id` in (SELECT `threadid` FROM `thread_replys` WHERE `userid` = 24016 GROUP BY `threadid`);

这么一句sql，要想高效，应该怎么写？

共24条回复

回复楼长

coredump 回复于 2013年11月20日

试试用join: SELECT t.* FROM threads t inner join thread_replys r ON t.id = r.threadid where t.createbyid <> 24016 AND r.userid = 24016;

回复 2楼

tinyfool 回复于 2013年11月20日

1楼 @coredump 快一些不过还是需要10s多。。

回复 3楼

fanngyuan 回复于 2013年11月20日

2楼 @tinyfool 你的表结构贴出来看看，show create table threads

回复 4楼

terryso 回复于 2013年11月20日

2楼 @tinyfool 我觉得首先要看看执行计划，找出查询慢的瓶颈。另外检查看看有没有针对相关字段做索引。。。

回复 5楼

贵人回复于 2013年11月20日

用exist替换in试试

回复 6楼

tinyfool 回复于 2013年11月20日

3楼 @fanngyuan

CREATE TABLE `threads` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `content` blob NOT NULL,
  `createby` varchar(50) NOT NULL DEFAULT '',
  `createbyid` int(11) NOT NULL,
  `lastreply` varchar(50) NOT NULL DEFAULT '',
  `lastreplyid` int(11) NOT NULL DEFAULT '0',
  `createdate` int(11) NOT NULL,
  `updatedate` int(11) NOT NULL,
  `replys` int(11) NOT NULL,
  `modifydate` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1540 DEFAULT CHARSET=utf8;

回复 7楼

coredump 回复于 2013年11月20日

2楼 @tinyfool 看看你的SQL运行的执行计划，index是不是使用正常(特别是那个createdbyid有没有索引？)。一般来说用IN都会较慢，因为内部查询不结束外部查询无法开始。

回复 8楼

贵人回复于 2013年11月20日

4楼 @terryso 对，支持楼上，最好先看看执行计划，如果有必要，可以在id上加索引，并且将索引的查询条件放在where条件的最前面

回复 9楼

coredump 回复于 2013年11月20日

如果记录数很大MyISAM engine也应该考虑换成其它的

回复 10楼

tinyfool 回复于 2013年11月20日

createbyid也加上索引了，没有发现明显变化

回复 11楼

coredump 回复于 2013年11月20日

话说，你的数据库的确有点点点点慢，发表一个回复要等等等等等等等等等好久久久久

回复 12楼

tinyfool 回复于 2013年11月20日

11楼 @coredump 就是这句话闹的，锁表了

# Query_time: 114.958171  Lock_time: 0.000077 Rows_sent: 1  Rows_examined: 27389215
SET timestamp=1384932679;
SELECT * FROM `threads` where `createbyid` <> 22734 AND `id` in (SELECT `threadid` FROM `thread_replys` WHERE `userid` = 22734 GROUP BY `threadid`);

回复 13楼

fanngyuan 回复于 2013年11月20日

createbyid-id的联合索引就好了

回复 14楼

tinyfool 回复于 2013年11月20日

EXPALIN的结果

mysql> explain
    -> SELECT * FROM `threads` where `createbyid` <> 24016 AND `id` in (SELECT `threadid` FROM `thread_replys` WHERE `userid` = 24016 GROUP BY `threadid`); 
+----+--------------------+---------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type        | table         | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+--------------------+---------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | PRIMARY            | threads       | ALL   | createbyid    | NULL     | NULL    | NULL | 1505 | Using where |
|  2 | DEPENDENT SUBQUERY | thread_replys | index | NULL          | threadid | 5       | NULL |    6 | Using where |
+----+--------------------+---------------+-------+---------------+----------+---------+------+------+-------------+
2 rows in set (0.00 sec)

回复 15楼

tinyfool 回复于 2013年11月20日

13楼 @fanngyuan 建立联合索引以后，explain未发生任何改变

回复 16楼

coredump 回复于 2013年11月20日

threads表的问题： 1. content不应该嵌入在threads表里，单独成content表，thread里放content_id 2. createdby和replyedby用户名其实也不应该存在threads表里

threads和reply表记录数无疑会很多，这就要求这两个表的设计应该尽可能轻量。

如果不重新改表结构的话，你先试试把SELECT t.*改成SELECT t.id, 然后用另一组SQL再单独取出thread的具体内容。

回复 17楼

tinyfool 回复于 2013年11月20日

给thread_replys的userid加上索引后，正常了

回复 18楼