摘要:本文主要向大家介绍了MySQL数据库之记一次order by desc limit导致的查询慢 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之记一次order by desc limit导致的查询慢 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
昨天接到一个客户的问题,电脑上可以打开网站,在手机上确不能打开报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;
打开网站,哈哈,速度杠杠的~~
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号