mysql 8 EXPLAIN查询性能优化

最新推荐文章于 2023-12-27 12:36:50 发布

weixin_34265814

最新推荐文章于 2023-12-27 12:36:50 发布

阅读量815

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/michaelshu/blog/1927909

版权

2019独角兽企业重金招聘Python工程师标准>>>

1. 进入MySQL数据库中，查看：

MySQL的变量分为以下两种：

系统变量：配置MySQL服务器的运行环境，可以用show variables查看

SHOW VARIABLES；

状态变量：监控MySQL服务器的运行状态，可以用show status查看

SHOW STATUS;

show global status like '%log%';

2. 打开MySQL的配置文件,一般是/etc/my.conf

查找

slow_query_log_file =

后面的就是慢查询日志文件

打开慢查询日志文件查看，可以看到查询时间，行检查条数：

复制上面的慢查询语句，并在查询语句前加上EXPLAIN，

EXPLAIN SELECT
   `u`.*,
   `us`.`share_img_url`,
   `up`.`promoter_id`,
   ( SELECT SUM( o.money ) FROM pm_orders o WHERE o.user_id = u.id GROUP BY o.user_id ) money_total,
   ( SELECT SUM( usc.promote_people_total ) FROM p_users_statistics usc WHERE usc.user_id = u.id GROUP BY usc.user_id ) people_total,
   ( SELECT nickname FROM p_users u WHERE up.promoter_id = u.id ) promoter_name,
   (
   SELECT
       SUM( awr.award_num )
   FROM
       pm_award_records awr
   WHERE
       awr.award_type = 5
       AND u.id = awr.praise_invitor_user_id
   GROUP BY
       awr.praise_invitor_user_id
   ) get_cash_num,
   `ccc`.`thread_total`,
   ad.nickname AS agent_name
FROM
   `p_users` `u`
   LEFT JOIN `p_users_shares` `us` ON `us`.`user_id` = `u`.`id`
   LEFT JOIN `p_users_promoter` `up` ON `up`.`user_id` = `u`.`id`
   LEFT JOIN (
   SELECT
       tmp.*,
       count( * ) AS thread_total
   FROM
       (
       SELECT
           uuu.id,
           pup.promoter_id
       FROM
           p_users AS uuu
           INNER JOIN p_users_promoter AS pup ON uuu.id = pup.user_id
       WHERE
           uuu.mobile != ''
       ) AS tmp
       LEFT JOIN p_users_promoter AS pup1 ON pup1.user_id = tmp.id
   GROUP BY
       pup1.promoter_id
   ) AS ccc ON `ccc`.`promoter_id` = `u`.`id`
   LEFT JOIN `pm_admin` `ad` ON `ad`.`id` = `u`.`agent_person_id`
WHERE
   ( `u`.`is_star` IN ( 0, 1 ) )
GROUP BY
   `u`.`id`
ORDER BY
   thread_total DESC
   LIMIT 0,
   20;