一个多表连接mysql 查询效率问题

surroad

数据库表：
forum:论坛主题表，20万记录
reforum：论坛回复表，46万记录
admin_read_rec：论坛阅读记录表,记录数：4万多

CREATE TABLE `forum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `forumid` int(11) NOT NULL DEFAULT '0',
  `topic` varchar(255) NOT NULL DEFAULT '',
  `username` varchar(50) NOT NULL DEFAULT '',
  `content` longtext,
  `posttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `postip` varchar(50) NOT NULL DEFAULT '',
  `replies` int(11) DEFAULT '0',
  `views` int(11) unsigned NOT NULL DEFAULT '0',
  `icon` int(11) DEFAULT '0',
  `goodtopic` int(11) NOT NULL DEFAULT '0' COMMENT '????',
  `toptopic` int(11) NOT NULL DEFAULT '0' COMMENT '????',
  `locktopic` int(11) NOT NULL DEFAULT '0' COMMENT '????',
  `deltopic` int(11) NOT NULL DEFAULT '0' COMMENT '????',
  `is_lock` set('0','1') NOT NULL DEFAULT '0' COMMENT '??',
  `lastname` varchar(255) DEFAULT '',
  `lasttime` datetime DEFAULT NULL,
  `polltopic` varchar(255) DEFAULT '',
  `pollresult` varchar(255) DEFAULT '',
  `multiplicity` int(11) DEFAULT '0',
  `reList` int(11) DEFAULT '0',
  `menberid` varchar(50) DEFAULT NULL,
  `rid` varchar(50) DEFAULT NULL,
  `mk` text,
  `is_deleted` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `forumid` (`forumid`),
  KEY `username` (`username`),
  KEY `posttime` (`posttime`)
) ENGINE=MyISAM AUTO_INCREMENT=263056 DEFAULT CHARSET=utf8

CREATE TABLE `reforum` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `topicid` int(11) unsigned NOT NULL,
  `username` varchar(50) NOT NULL,
  `content` text NOT NULL,
  `posttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `postip` varchar(32) NOT NULL,
  `is_deleted` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `topicid` (`topicid`),
  KEY `username` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=470222 DEFAULT CHARSET=utf8

CREATE TABLE `admin_read_rec` (
  `topicid` bigint(20) NOT NULL,
  `admin_uid` bigint(20) NOT NULL,
  `date_read` datetime NOT NULL,
  `reply_id` bigint(20) DEFAULT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `read_type` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `reply_id` (`reply_id`),
  KEY `topicid` (`topicid`),
  KEY `read_type` (`read_type`)
) ENGINE=MyISAM AUTO_INCREMENT=43527 DEFAULT CHARSET=utf8

查询运行需要差不多20秒：
mysql> select `f`.`id` AS `id` ,max(`re`.`id`)  as repMaxId   from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on   `f`.`id`     =`r`.`topicid`    group by `f`.`id`   limit 23;
+--------+----------+
| id     | repMaxId |
+--------+----------+
|     11 |   465659 |
|     58 |   467615 |
|  56231 |   462402 |
|  87837 |   469640 |
| 113242 |   469512 |
| 141179 |   241797 |
| 215054 |   464044 |
| 221558 |   465549 |
| 238942 |   458801 |
| 239357 |   465775 |
| 240434 |   466235 |
| 240530 |   469327 |
| 241587 |   461144 |
| 243230 |   464412 |
| 251952 |   461486 |
| 252933 |   460656 |
| 253691 |   463640 |
| 253781 |   463763 |
| 253864 |   458870 |
| 255115 |   464473 |
| 255132 |   454536 |
| 255200 |   460770 |
| 255453 |   455156 |
+--------+----------+
23 rows in set (19.65 sec)


mysql> explain select `f`.`id` AS `id` ,max(`re`.`id`)  as repMaxId   from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r`    on   `f`.`id`     =`r`.`topicid`    group by `f`.`id`   limit 25;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | r     | index  | topicid       | topicid | 8       | NULL               | 43526 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | bbs_test.r.topicid |     1 | Using where; Using index                     |
|  1 | SIMPLE      | re    | ref    | topicid       | topicid | 4       | bbs_test.f.id      |     3 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+

怎么会那么慢？

cenalulu

admin_read_rec的内容在结果集中并没有出现啊。
有什么作用？

Coolriver

我仔细分析了一下，你的SQL，想了一下你需求的功能：读取得到最新评论的Topid［被别人阅读过，有回复的］和最新的回复id.
这样也可以简单的理解为：
有回复的一定是被别人阅读过的。所以：  `f`.`id`     =`r`.`topicid` 这个是不是可以省略。
如果是这样，只需要得到topid,最新的回复。是不是可以用：
select topicid,max(id) from  reforum  order by topicid limit 23;
这样去显示呢？

我觉的在细分下去，表设计上还是有一定的问题。不太适合高并发的环境。不过，一般都是Cache比较多，程序做的好的DB也不受影响。

linxpeng

同意LS,不过我觉得SQL应该是

1. 
   
2. select `re`.`topicid` as `id`
   
3.        , `re`.`id`    as repMaxId
   
4. from   `reforum` `re`
   
5. order by `re`.`id` desc
   
6. limit  23;
   

复制代码

surroad

回复 3# Coolriver


谢谢楼上的回复。
   
admin_read_rec是提供给后台用户使用的，只有当用户进行了点击“已读取”按钮时，才会添加相应的帖子回复或主题的读取记录，会记录每个帖子主题、回复的读取记录。
其实最后要运行的查询是这样，获取最后回复的并操作为已读取的帖子：
select `f`.`id` AS `id`
from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`   
  group by `f`.`id`
having max(`r`.`reply_id`) = max(`re`.`id`)
limit 25

nonamexz

是什么版本的mysql,如果是mysql5.1或以上版本，可能和我一样遇到group by ** limit的bug了。
输出
explain select `f`.`id` AS `id`
from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`   
  group by `f`.`id`
having max(`r`.`reply_id`) = max(`re`.`id`);
和
explain select `f`.`id` AS `id`
from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`   
  group by `f`.`id`
having max(`r`.`reply_id`) = max(`re`.`id`) limit 25;
的结果，对比一下。

nonamexz

是什么版本的mysql,如果是mysql5.1或以上版本，可能和我一样遇到group by ** limit的bug了。
输出
explain select `f`.`id` AS `id`
from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`   
  group by `f`.`id`
having max(`r`.`reply_id`) = max(`re`.`id`);
和
explain select `f`.`id` AS `id`
from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`   
  group by `f`.`id`
having max(`r`.`reply_id`) = max(`re`.`id`) limit 25;
的结果，对比一下。

surroad

mysql> explain select `f`.`id` AS `id`
    -> from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`
    ->   group by `f`.`id`
    -> having max(`r`.`reply_id`) = max(`re`.`id`);
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows  | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+---------------------------------+
|  1 | SIMPLE      | r     | ALL    | topicid       | NULL    | NULL    | NULL               | 43526 | Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | bbs_test.r.topicid |     1 | Using where; Using index        |
|  1 | SIMPLE      | re    | ref    | topicid       | topicid | 4       | bbs_test.f.id      |     3 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+---------------------------------+
3 rows in set (0.00 sec)

mysql> explain select `f`.`id` AS `id`
    -> from `forum` `f` left join `reforum` `re` on`re`.`topicid` = `f`.`id`   join `admin_read_rec` `r` on `r`.`topicid` = `f`.`id`
    ->   group by `f`.`id`
    -> having max(`r`.`reply_id`) = max(`re`.`id`) limit 25;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows  | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+---------------------------------+
|  1 | SIMPLE      | r     | ALL    | topicid       | NULL    | NULL    | NULL               | 43526 | Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 4       | bbs_test.r.topicid |     1 | Using where; Using index        |
|  1 | SIMPLE      | re    | ref    | topicid       | topicid | 4       | bbs_test.f.id      |     3 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+---------------------------------+

surroad

Server version: 5.1.28-rc-log MySQL Community Server (GPL)

cenalulu

是什么版本的mysql,如果是mysql5.1或以上版本，可能和我一样遇到group by ** limit的bug了。
输出
explai ...
nonamexz 发表于 2010-03-08 22:55

    其实楼主的情况是 临时表的group by +limit本来就利用不到索引，跟那个bug应该没关系