Mysql查询优化

Question

Mysql查询需要15+秒来获取数据。我的查询如下:

供参考:消息表5L(50万)行数据，耗时较长。

select  distinct
ur.id, ur.user_name as name, ur.online, ur.chat_status,
ur.updated_at, ur.profile_image, ur.role_id, 
( SELECT  created_at
from  messages
where  from_role_user_id = ur.id
OR  to_role_user_id = ur.id
Order by  created_at DESC
LIMIT  1
) as message_at, 
( SELECT  count(is_read)
from  messages
where  from_role_user_id = ur.id
AND  to_role_user_id = 1
AND  is_read = 0
) as count,
r.name as role
from  role_users ur
left join  roles r  ON r.id = ur.role_id
where  ur.id != 1
AND  r.name IN ('superadmin', 'candidate', 'admin', 'manager',
'business_unit','client')
AND  ur.chat_status != 'offline'
AND  ur.is_deleted = 0
AND  ur.user_name IS NOT NULL
order by  message_at DESC
LIMIT  10;

谁能帮我优化一下这个查询?

Answer 1

• 第一个子查询可能会更快:

  ( SELECT  MAX(created_at)
  from  messages
  where  from_role_user_id = ur.id
  OR  to_role_user_id = ur.id
  ) as message_at, 
  

• 这可能更快:

  SELECT GREATEST(
  ( SELECT MAX(created_at)  FROM messages
  WHERE from_role_user_id = ur.id ),
  ( SELECT MAX(created_at)  FROM messages
  WHERE to_role_user_id = ur.id ) )
  

• 有两个索引:

  指数(from_role_user_id created_at),指数(to_role_user_id created_at)

• COUNT(x)计算x IS NOT NULL有多少行。SUM(y)把y加起来，忽略NULLs。通常计算行数的方法是COUNT(*)。

  ( SELECT  count(*)
  from  messages
  where  from_role_user_id = ur.id
  AND  to_role_user_id = 1
  AND  is_read = 0
  ) as count,
  

• 有' INDEX(from_role_user_id, to_role_user_id, is_read)

• 如果真的不需要DISTINCT，它将避免额外的数据传递，因此更快。

• 请提供EXPLAIN SELECT ...

• 它可能最好先找到10条消息的id，然后再去运行这些子查询10次。