mysql8 查询性能_mysql 8 EXPLAIN查询性能优化

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;

这里可以看到查询类型，查询可能的键，检查行数，过滤行数等信息，可以通过分析针对哪些复杂查询SQL语句进行优化