Mysql慢日志时使用UNION ALL？

Question

我在mysql中有下面的查询--缓慢的日志，但是我不知道如何优化这个查询并使它更快。对此有什么想法吗？我已经在用户表和其他表中使用了索引。然而，这仍然是不对的。我的所有表都是InnoDB表类型的。

SELECT  count(*) as total
    FROM  users_interest
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL 
SELECT  count(*)
    FROM  users_view
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL 
SELECT  count(*)
    FROM  mail_msg
    WHERE  user_to='64500'
      AND  folder='1'
      AND  new='Y'
    UNION  ALL 
SELECT  count(*)
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden' )
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  online=1
    UNION  ALL 
SELECT  count(*) as total_recent
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden')
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  last_visit >= DATE_SUB(NOW(), INTERVAL 60 MINUTE);

# Time: 160124 22:46:38
# User@Host: xyz_user[xyz_user] @ localhost []  Id: 194710
# Schema: xyz_dev  Last_errno: 0  Killed: 0
# Query_time: 10.199606  Lock_time: 0.000048  Rows_sent: 81  Rows_examined: 2358518  Rows_affected: 0
# Bytes_sent: 4158
SET timestamp=1453655798;

Answer 1

请提供SHOW CREATE TABLE。

所需索引：

users_interest:  (user_to, new)
users_view:  (user_to, new)
mail_msg:  (user_to, folder, new)
users_block:  (user_from) and (user_to, user_from)
user:  (activated, online) and (activated, last_visit)

构造NOT IN ( SELECT ... )性能不佳；将其更改为LEFT JOIN ... WHERE ... IS NULL。例如：

SELECT ... FROM user
    WHERE user_id NOT IN ( SELECT user_from
                         FROM users_block WHERE user_to=1 )
      AND other_stuff

-->

SELECT ... FROM user AS u
    LEFT JOIN user_from AS ub  ON u.user_id = ub.user_from
    WHERE ub.user_from IS NULL
      AND other_stuff

或者(我不知道这是否更好)：

SELECT ... FROM user AS u
    WHERE NOT EXISTS ( SELECT * FROM users_block
                     WHERE user_to=1 AND users_from = u.user_id )
      AND other_stuff

建议:与其拥有5行未标记的计数，不如执行以下操作

( SELECT 'hidden count' AS total, COUNT(*) AS ct ... )
UNION ALL
( SELECT 'blocked to  ' AS total, COUNT(*) AS ct ... )
...

所以每个计数都有标记。