php - 使用 join 进行查询比使用 php 和 MySQL 进行循环查询慢 20 倍

Question

我正在尝试对我的网站的查询进行一些优化。我一直认为，使用一个带有“JOIN”的查询比在 php 循环中放置多个查询更好，但是当我这样做时，它真的很慢。

带有循环查询的原始方法需要 0.055 秒，而带有“join”的新方法需要 1.084 秒。我应该使用哪种解决方案？有没有办法让新查询更快？也许另一个想法是在 mysql 中使用循环创建一个过程？

情况是这样的:我有一个论坛，论坛中有主题，主题中有消息。为了获取用户名，我在 msgs 表中获取用户的 id，如果这是肯定的，则它是注册用户，否则它是 guest 表中的访客。不知道这是否是一个好的结构，但我不能将客人和注册成员(member)放在同一张 table 上。

CREATE TABLE `topic` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `post_date` int(14) NOT NULL DEFAULT '0',
  `last_answere_date` int(14) NOT NULL,
  `author_name` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `msgs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL DEFAULT '0',
  `post_date` int(14) unsigned DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `topic_id` (`topic_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `guests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

我的两种情况:

//Goal : get the list of 5 new topics or topic with new messages
//Methode query in loop
    $timestart=microtime(true);
    echo '<h3>Query in loop</h3>';
    //Get the list of new topics
    $reqTopic = $bdd->query('SELECT id,title,author_name,post_date FROM topic ORDER BY last_answere_date DESC LIMIT 0,5');
    While($topic = $reqTopic->fetch())
    {
        //Get the last message
            $reqMsgs = $bdd->prepare('SELECT count(*) as msgCount, user_id,post_date FROM msgs WHERE topic_id=? ORDER BY post_date DESC');
            $reqMsgs->bindparam(1,$topic['id'],PDO::PARAM_INT);
            $reqMsgs->execute();
            $msg = $reqMsgs->fetch();
        //IF the id is >0 then it's a registered member, else it's a guest
            if($msg['user_id'] > 0)
                $reqAuthor = $bdd->prepare('SELECT nom as name FROM users WHERE id=? LIMIT 0,1');
            else
                $reqAuthor = $bdd->prepare('SELECT name FROM guests WHERE id=? LIMIT 0,1');

            $reqAuthor->bindparam(1,$msg['user_id'],PDO::PARAM_INT);
            $reqAuthor->execute();
            $author = $reqAuthor->fetch();
        //Output
        echo '<strong>'.$topic['title']. '</strong><br/>'
            .'Author : '. $topic['author_name'] . ' | Date : '. date('d/m/Y H:i',$topic['post_date']) .'<br/>';
            if($msg['msgCount'] > 0)
            {
                echo 'Last msg author : '. $author['name'] . ' | Date : '. date('d/m/Y  H:i',$msg['post_date']) .'<br/>'
                .'Nbr msgs : '.$msg['msgCount'].'<br/>';
            }
            echo '<br/>';

    }
    //End of script : get time
    $timeend=microtime(true);
    $time=$timeend-$timestart;
    echo '<strong>'.number_format($time, 10) . ' sec</strong>';

//Methode Left join
    $timestart=microtime(true);
    echo '<h3>Left Join</h3>';
    //The query
        $reqTopic = $bdd->query('SELECT t.id,title,t.author_name,t.post_date, m.*, (SELECT COUNT(*) FROM msgs WHERE t.id=topic_id) as msgCount
        FROM topic t
        LEFT JOIN (
            SELECT m.id,topic_id,post_date as mdate,user_id, IFNULL(u.nom,i.name) as name
            FROM msgs m
            LEFT JOIN users u ON u.id=m.user_id
            LEFT JOIN guests i ON i.id=-m.user_id
            GROUP BY topic_id
            ORDER BY post_date DESC
            ) m
            ON t.id=m.topic_id
        GROUP BY t.id
        ORDER BY last_answere_date DESC LIMIT 0,5');
    While($topic = $reqTopic->fetch())
    {

        //Output
        echo '<strong>'.$topic['title']. '</strong><br/>'
            .'Author : '. $topic['author_name'] . ' | Date : '. date('d/m/Y H:i',$topic['post_date']) .'<br/>';
            if($topic['msgCount'] > 0)
            {
                echo 'Last msg author : '. $topic['name'] . ' | Date : '. date('d/m/Y  H:i',$topic['mdate']) .'<br/>'
                .'Nbr msgs : '.$topic['msgCount'].'<br/>';
            }
            echo '<br/>';

    }
    //End of script : get time
    $timeend=microtime(true);
    $time=$timeend-$timestart;
    echo '<strong>'.number_format($time, 10) . ' sec</strong>';

Answer 1

一般来说，处理这类事情的最佳方法是分两步进行:

1. 获取您需要的行
2. 获取与这些行相关的数据

根据您的情况，首先获取您的主题:

SELECT id,title,author_name,post_date
FROM topic
ORDER BY last_answere_date DESC LIMIT 0,5

使用获取的主题 id，然后获取相关数据:

SELECT topic_id, user_id, post_date
FROM msgs
WHERE topic_id IN (…)
ORDER BY topic_id, post_date DESC

SELECT id, nom as name FROM users WHERE id IN (…)

等等

在您的特定情况下，如果您确实希望 SQL 返回计数，则相关子查询可以廉价地生成计数:

SELECT t.id,title,t.author_name,t.post_date,
       (SELECT COUNT(*) FROM msgs WHERE t.id=topic_id) as msgCount
FROM topic t
ORDER BY t.last_answere_date DESC LIMIT 0,5

(在上面，规划器应该首先获取行，然后每行运行一次子查询。)

像您一样使用左连接一次性检索所有内容的问题是:

1. 您可能会不必要地加入因在子查询中使用 group by 语句而产生的巨大集合，因为后者会阻止使用索引；
2. 由于您加入的子查询中存在 order by 子句，您可能会失去执行 where ... order by ... limit ... 时可能发挥的索引的优势；和
3. 将发送数据的次数乘以相关行数 - 例如在左连接查询中，每条消息都会发送一次与主题相关的数据。 (在您的特定情况下，这可能可以忽略不计，但在其他情况下可能会增加。)