mysql - mysql中统计查询优化

Question

users 表中共有 1629664 条记录。

如果我使用SELECT count(u.id) FROM users，它会在0.00053秒内返回结果，这很好!!!

如果我要使用 SELECT count(u.id) FROM users u INNER JOIN user_profile p ON p.user_id = u.id and p.is_delete = 0 进行 JOIN，它将返回结果在 5.67 秒或更多秒内，这对我来说不好，我们如何优化这个？

此外，如果我使用查询在 where 中添加更多列

SELECT  count(u.id)
    FROM  `users` `u`
    INNER JOIN  `user_profile` `p`  ON `p`.`user_id` = `u`.`id`
      and  `p`.`is_delete` = 0
    WHERE  `u`.`gender` in (1,0)
      AND  `u`.`status` = 1
      AND  `u`.`is_delete` =0
      AND  `u`.`user_role` = 2
      AND  `u`.`id` <> 5720962
      AND  `u`.`search_n_dashboard` = 'visible' 

它在 12.45 秒或更长时间内返回结果，我需要优化这个。

我已经使用了索引CREATE INDEX users_index ON users (id);
创建索引 user_profile_index ON user_profile (user_id);
请建议我，我该如何优化更多？

提前致谢!!!

Answer 1

寻找可以使用复合索引的条件和位置

  On table  users   ( gender , id , status, is_delete , user_role ,id , search_n_dashboard)

和

 On table user_profile ( is_delete ,user_id ) 

根据这些索引，您可以尝试反转表(更改引导表)并且还可以使用 INNER ON subselect 避免 IN 子句

SELECT count(u.id) 
FROM `user_profile` `p`
INNER JOIN  `users` `u`  ON `p`.`user_id` = `u`.`id` and `p`.`is_delete` = 0 
INNER JOIN ( select 1  as gender union select 2  ) T on T.gender = u.gender
WHERE `u`.`status` = 1 
      AND `u`.`is_delete` =0 
        AND `u`.`user_role` = 2 
          AND `u`.`id` <> 5720962 
            AND `u`.`search_n_dashboard` = 'visible'

您也可以使用 FORCE 索引进行连接

SELECT count(u.id) 
FROM `user_profile` `p`
INNER JOIN  `users` `u` FORCE INDEX FOR JOIN (`your_index_for_join`)  ON `p`.`user_id` = `u`.`id` and `p`.`is_delete` = 0 
INNER JOIN ( select 1  as gender union select 2  ) T on T.gender = u.gender
WHERE `u`.`status` = 1 
      AND `u`.`is_delete` =0 
        AND `u`.`user_role` = 2 
          AND `u`.`id` <> 5720962 
            AND `u`.`search_n_dashboard` = 'visible'