MySQL查询性能和Codeigniter

Question

我有一个MySQL查询语句，我加载到我的主页控制器中，在运行Codeigniter的 $this->output->enable_profiler(TRUE); 后，我得到了一个执行时间为5.3044

在我的模型中的查询：

class Post extends CI_Model {

    function stream($uid, $updated, $limit) {
        $now = microtime(true);
        $sql = "
            SELECT 
                * 
            FROM 
                vPAS_Posts_Users_Temp 
            WHERE 
                post_user_id = ? 
                AND post_type !=4 
                AND post_updated > ?
                AND post_updated < ? 
            UNION 

            SELECT 
                u.* 
            FROM 
                vPAS_Posts_Users_Temp u 
            JOIN 
                PAS_Follow f 
                ON f.folw_followed_user_id = u.post_dynamic_pid 
                WHERE u.post_updated > ?
                AND post_updated < ? 
                AND (( f.folw_follower_user_id = ? AND f.folw_deleted = 0 ) 
                OR ( u.post_passed_on_by = f.folw_follower_user_id OR u.post_passed_on_by = ? AND u.post_user_id != ? AND u.post_type =4 )) 
            ORDER BY 
                post_posted_date DESC 
            LIMIT ?
        "; 

        $query = $this->db->query($sql, array($uid, $updated, $now, $updated, $now, $uid, $uid, $uid, $limit));

        return $query->result();
    }

}

有什么我可以在这里做的来提高执行时间，从而增加我的页面载入速度吗？

编辑

解释结果

MySQL Workbench可视化解释

- Justin Erswell

---

你是否在这两个字段上设置了索引：f.folw_followed_user_id 和 u.post_dynamic_pid？ - user2417483

@saurabh2836，我不知道该怎么做，请问你能指点我一下吗？ - Justin Erswell

表有多大？ - Kao

你的联合是没有用的，你没有对它做任何操作（除非我在这里漏掉了什么）。如果你提供了表结构并解释你想要做什么，那会更好。 - ahmad

您需要返回所有字段吗？如果不需要，请避免使用SELECT * FROM，而是指定您需要的列。此外，我可以假设vPAS_Posts_Users_Temp是一个视图吗？如果是这样，请提供该视图的定义。 - deroby

Answer 1

也许你不会相信，但是在SQL中不要检索SELECT *。只需编写您想要检索的字段，我认为它会加快很多速度。

当仅使用所需字段更改*时，查询执行速度可以增加20倍以上（从0.4秒到0.02秒）。

另一件事：如果您在表中插入auto_increment id，请勿使用post_posted_date作为ORDER字段。按DATETIME字段排序很慢，如果您可以使用INT id（希望您将其作为索引），则可以更快地实现相同的结果。

更新：

如问题所要求的，技术原因：

• 不使用 SELECT *：哪个更快/更好？SELECT *或SELECT column1，colum2，column3等。这是关于SQL的，但是对于MySQL（不如之前的问题完整）mySQL查询-选择字段

• 按日期时间排序：SQL，SQL Server 2008：按日期时间排序太慢了，还有与MySQL相关的内容：MySQL性能优化：按日期时间字段排序

奖励：学习如何设置索引：http://ronaldbradford.com/blog/tag/covering-index/

- Federico J.

Answer 2

我会在post_user_id，post_updated和folw_follower_user_id上添加索引。

在这种情况下，最好不使用union并将查询分成两个单独的查询，然后使用PHP组合两个结果集。

如果您切换到使用Active Record，还可以查看caching以获得更好的性能。

- Pattle

Answer 3

尝试使用左连接查询，因为您正在尝试在同一张表上进行联合操作

"SELECT 
u.* 
        FROM 
            vPAS_Posts_Users_Temp u
            LEFT JOIN PAS_Follow f ON f.folw_followed_user_id = u.post_dynamic_pid 
        WHERE (
        u.post_user_id = ? 
                AND u.post_type !=4 
                AND u.post_updated > ?
                AND u.post_updated < ?
    ) 
    OR 
     (
    u.post_updated > ?
        AND post_updated < ? 
        AND (( f.folw_follower_user_id = ? AND f.folw_deleted = 0 ) 
                OR ( u.post_passed_on_by = f.folw_follower_user_id OR u.post_passed_on_by = ? AND u.post_user_id != ? AND u.post_type =4 )) 
    )
        ORDER BY 
            u.post_posted_date DESC 
            LIMIT ?"

- Minhaz

---

我没有考虑到你的所有条件，我只是展示了如何使用左连接来实现这个。 - Minhaz

Answer 4

我认为你可以从查询中删除UNION，改用左连接，并避免不必要的条件：

SELECT U.* 
FROM vPAS_Posts_Users_Temp AS U 
LEFT JOIN PAS_Follow AS F ON F.folw_followed_user_id = U.post_dynamic_pid 
WHERE U.post_updated > ?
AND U.post_updated < ?
AND ( 
      ( 
        F.folw_follower_user_id = ? AND F.folw_deleted = 0 
      ) 
      OR 
      ( 
        U.post_passed_on_by = F.folw_follower_user_id OR U.post_passed_on_by = ? 
      )
    ) 
ORDER BY 
U.post_posted_date DESC 
LIMIT ?

同时在你的表中识别并设置适当的索引。

- Nouphal.M

Answer 5

行列显示了需要检查的行数的估计值，就我所知，在您的情况下，它必须扫描 72*1*2627*1*2 行，这相当多。

现在，技巧是减少此数字，其中一种方法是添加索引。在您的情况下，我建议添加一个包含以下内容的索引： post_user_id、post_type、post_updated、post_updated。
这应该可以减少第一个结果集的 72 行。

现在对于 UNION，请尝试改用 UNION ALL，因为它被认为更快。
如果那不能解决问题，我建议重写查询以完全不使用 UNION 调用。

- Kao

---

72 * 1 * 2627 * 1 * 2 = 378288 - Ronni Skansing