记一次order by desc limit导致的查询慢

    昨天接到一个客户的问题，电脑上可以打开网站，在手机上确不能打开报500的错。首先登陆上客户的服务器查看环境apache+mysql+php，php和mysql的占用都比较高，按经验来说那就是mysql的问题了，登陆mysql用show processlist查看进程，发现一条查询一直在sending date

mysql> show processlist;

+------+------+-----------------+--------+---------+------+--------------+------

--------------------------------------------------------------------------------

----------------+

| Id   | User | Host            | db     | Command | Time | State        | Info

                |

+------+------+-----------------+--------+---------+------+--------------+------

--------------------------------------------------------------------------------

----------------+

| 1832 | root | localhost:53490 | NULL   | Query   |    0 | NULL         | show

processlist

                |

| 1842 | root | localhost:53508 | yungou | Query   |    4 | Sending data | selec

t a.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,announced_type,q_end_

time ,(SELECT ` |

+------+------+-----------------+--------+---------+------+--------------+------

--------------------------------------------------------------------------------

----------------+

2 rows in set (0.00 sec)

明显不对啊，把这条语句单独拿出来执行也是慢得要死48s。

explain分析一下这条语句：

mysql> explain select a.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,a

nnounced_type,q_end_time ,(SELECT `time` FROM `go_member_go_record` WHERE shopid

 = a.id ORDER BY `time` DESC LIMIT 1 ) as gm_time from `go_shoplist` as a where

`shenyurenshu` <=0 ORDER BY `gm_time` DESC LIMIT 4\G;

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: a

         type: range

possible_keys: shenyurenshu

          key: shenyurenshu

      key_len: 4

          ref: NULL

         rows: 945

        Extra: Using where; Using filesort

*************************** 2. row ***************************

           id: 2

  select_type: DEPENDENT SUBQUERY

        table: go_member_go_record

         type: index

possible_keys: shopid

          key: time

      key_len: 63

          ref: NULL

         rows: 1

        Extra: Using where

2 rows in set (0.00 sec)

一看type是range就悲剧了，慢是肯定的了，但是945条记录也不至于这么慢啊！！

没办法用简化法来定位错误，先去掉子查询，直接

select a.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,announced_type,q_end_time from `go_shoplist` as a where `shenyurenshu` <=0 ORDER BY `gm_time` DESC LIMIT 4;

没有问题，速度杠杠的~~

那就是子查询的问题了哦，从哪里动刀呢？order by desc limit

首先去掉排序，查询杠杠的~~

然后想想order by desc limit 1怎么替代呢，这句的意思是查询最大的值，那我直接用max可以吗？马上修改

select a.id,a.q_user,a.q_showtime,a.thumb,a.title,a.q_uid,qishu,announced_type,q_end_time ,(SELECT MAX(`time`) FROM `go_member_go_record` WHERE shopid = a.id ) as gm_time from `go_shoplist` as a where `shenyurenshu` <=0 ORDER BY `gm_time` DESC LIMIT 4;

打开网站，哈哈，速度杠杠的~~

©著作权归作者所有：来自51CTO博客作者sheva的原创作品，谢绝转载，否则将追究法律责任

mysql优化web