首页 文章

我如何优化我的mysql查询？

提问于
浏览 次
1
SELECT a.*, u.avatar, u.name, u.surname, u.username, COUNT(a.user1) AS cnt  
FROM user_actions a,users u,following f   
WHERE a.user1=u.user_id AND
a.user1=f.follower_id AND 
f.user_id=159 
GROUP BY a.user1, a.action, day(a.dt) 
ORDER BY a.id DESC 
LIMIT 7;

查询耗时4.4909秒

表user_actions的索引：

Action  Keyname     Type   Unique Packed Column    Cardinality Collation
Edit    PRIMARY     BTREE  Yes    No     id        516094      A        
Edit    user1       BTREE  No     No     user1     15639       A        
Edit    user2       BTREE  No     No     user2     36863       A        
Edit    action      BTREE  No     No     action    16          A        
Edit    dt          BTREE  No     No     dt        516094      A        
Edit    group_index BTREE  No     No     user1     20643       A        


EXPLAIN SELECT a . * , u.avatar, u.name, u.surname, u.username, COUNT( a.user1 ) AS cnt
FROM user_actions a, users u, following f
WHERE a.user1 = u.user_id
AND a.user1 = f.follower_id
AND f.user_id =159
GROUP BY a.user1, a.action, day( a.dt )
ORDER BY a.id DESC
LIMIT 7

id select_type表类型possible_keys键key_len ref rows额外1 SIMPLE f ref user_id，follower_id，for_actions for_actions 4 const 242使用索引;使用临时;使用filesort 1 SIMPLE a ref user1，group_index group_index 4 pictify_main.f.follower_id 25 1 SIMPLE u eq_ref PRIMARY PRIMARY 4 pictify_main.a.user1 1使用where

mysql sql

4 回答

  • 0

    由于格式化，无法理解ur索引

    但是以下索引可以帮助..

    user_actions(user1)
following (user_id)
users(user_id)

    如建议的那样，请发布解释计划 .

    回复于
  • 0

    Non-deterministic GROUP BY: SQL检索既不在聚合函数中也不在GROUP BY表达式中的列，因此这些值在结果中将是不确定的 .

    SELECT : 使用通配符选择所有列将导致查询's meaning and behavior to change if the table'架构更改，并可能导致查询检索过多数据 .

    Aliasing without the AS keyword 在列或表别名中明确使用AS关键字，例如"tbl AS alias,"比隐式别名（如"tbl alias"）更具可读性 .

    回复于
  • 0

    我会反过来一些，因为你正在寻找一个特定的“跟随”用户ID ...然后添加“STRAIGHT_JOIN”的子句告诉MySQL按顺序列出的顺序

    确保表索引的索引（User_ID，Follower_ID）用户（User_ID）User_Actions（User1，Action，Dt，ID）

    select STRAIGHT_JOIN
      UA.*,
      U.Avatar,
      U.Name,
      U.SurName,
      U.UserName,
      count(*) as UserActionsCount
   from
      Following F
         JOIN User_Actions UA
            on F.Follower_ID = UA.User1
            Join Users U
               on UA.User1 = U.User_ID
   where
      F.User_ID = 159
   group by
      F.Follower_ID,
      UA.Action,
      Day( UA.Dt )
   order by
      UA.ID DESC
   limit 
      7
    回复于
  • 0

    使用连接而不是以逗号分隔的 FROM cluase：

    SELECT a.*, u.avatar, u.name, u.surname, u.username, COUNT(a.user1) AS cnt  
FROM following f
LEFT JOIN user_actions a ON a.user1 = f.follower_id
LEFT JOIN users u ON u.user_id = a.user1
WHERE f.user_id = 159 
GROUP BY a.user1, a.action, day(a.dt) 
ORDER BY a.id DESC 
LIMIT 7;

    我选择在 FROM 子句中使用 following 表，因为搜索的关键部分基于 following.user_id . 这应该减少在早期尽可能缩小结果集时获取的行数 .

    我推荐一个 LEFT JOIN ，因为这将返回 following 中的行，即使他们不想在这种查询中使用't match any entries in the other two tables, which is usually the result you' . 如果您只想要在所有3个表中包含数据的行，请尝试使用 INNER JOIN .

    回复于

相关问题