优化MySQL分页的复杂查询

最新推荐文章于 2024-07-30 21:39:44 发布

weixin_34259559

最新推荐文章于 2024-07-30 21:39:44 发布

阅读量287

点赞数

文章标签：数据库 php python

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

版权

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

前情背景：

开发过程中会遇到很多查询MySQL复杂查询语句，涉及到连接表，聚合计算，排序，分组，分页等。分页用的是最多的，这种情况下，MySQL数据库可能会遇到性能瓶颈，导致这样的查询时间长或者超时，最可怕的还有可能导致没法操作MySQL数据库。

在遇到这样的查询的时候，算是发现问题了。但要怎么解决呢？

分析其原因，查询SQL语句也没有写的冗余，但是很多产品，什么都想要，需求不断的变，导致表的结构有些不合理。优化表有时候可以，不仅麻烦，而且还不一定可行，时间也不够。有没有更好的办法解决呢？

通过打开MySQL的配置文件,一般是/etc/my.conf，查找

slow_query_log_file =

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

可以看到查询的重要信息（查询时间，行检查条数）。

也可以用在MySQL中将执行的SQL语句前加上EXPLAIN，查看其详细信息。可以看到查询的重要信息，其中有个type列，此列说明查询的类型，一般慢查询都是All：表示全查询，是最慢的。const是带索引的查询最快。还有其他的类型（ref,eq_ref等）。如果能改成const的或者eq_ref类型的查询，性能就会成十成百倍的提高。如何做到呢？

laravel5.5框架用有自带的分页，用到group/order by，变成全查询，所以慢。所以使用自定义分页，如下SQL查询：

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;