mysql 慢查询的几种优化方式

 Mysql   高蒙   阅读(1124)   评论(1)   2017-12-18 17:18:23    mysql 慢查询 


以下仅供参考

数据表120多万条记录。

数据表500M多。

统计查询 

#查询花费 1.0717 秒 

SELECT count(id) FROM table WHERE is_del=0 AND wantshow=1 AND is_encrypt=0 AND name like '%装饰%' 


#查询花费 0.0065 秒  这个查询出来 count下数组即可。 

SELECT count(1) FROM table WHERE is_del=0 AND wantshow=1 AND is_encrypt=0 AND name like '%装饰%' GROUP BY id


列表查询

#查询花费 2.8622 秒  这是基础语句 

SELECT view_num,id,preview,name,uid,like_num,username FROM table WHERE is_del = 0 AND wantshow = 1 AND is_encrypt = 0 AND bigcate = 606 AND is_recommend = 1 ORDER BY id DESC LIMIT 1000,30;     


#查询花费 2.6788 秒 

SELECT view_num,id,preview,name,uid,like_num,username FROM table WHERE is_del = 0 AND wantshow = 1 AND is_encrypt = 0 AND bigcate = 606 AND is_recommend = 1 GROUP by id ORDER BY id DESC LIMIT 1000,30;   

#查询花费 2.1573 秒 SELECT a.view_num,a.id,a.preview,a.name,a.uid,a.like_num,a.username FROM table a RIGHT JOIN (SELECT id FROM table WHERE is_del = 0 AND wantshow = 1 AND is_encrypt = 0 AND bigcate = 606 AND is_recommend = 1 ORDER BY id DESC LIMIT 1000,30) b ON b.id = a.id; 

    

#查询花费 2.0922 秒 

SELECT a.view_num,a.id,a.preview,a.name,a.uid,a.like_num,a.username FROM table a RIGHT JOIN (SELECT id FROM table WHERE is_del = 0 AND wantshow = 1 AND is_encrypt = 0 AND bigcate = 606 AND is_recommend = 1 GROUP by id ORDER BY id DESC LIMIT 1000,30) b ON b.id = a.id;     

#查询花费 1.8811 秒 

SELECT a.view_num,a.id,a.preview,a.name,a.uid,a.like_num,a.username FROM table a RIGHT JOIN (SELECT id FROM table WHERE id>1 AND is_del = 0 AND wantshow = 1 AND is_encrypt = 0 AND bigcate = 606 AND is_recommend = 1 ORDER BY id DESC LIMIT 1000,30) b ON b.id = a.id; 


#查询花费 1.8519 秒 

SELECT a.view_num,a.id,a.preview,a.name,a.uid,a.like_num,a.username FROM table a RIGHT JOIN (SELECT id FROM table WHERE id>1 AND is_del = 0 AND wantshow = 1 AND is_encrypt = 0 AND bigcate = 606 AND is_recommend = 1 GROUP by id ORDER BY id DESC LIMIT 1000,30) b ON b.id = a.id;





相关文章



我要评论


站长昵称:(*)

输入内容:


评论列表


高蒙

男,程序猿一枚

 

人生要是没有理想, 那跟一条咸鱼有什么分别。


关于我

  http://www.shuchengxian.com

高蒙个人博客是以PHP技术为主的程序员个人博客。博客主要发布php开发中遇到的问题以及解决办法,同时个人博客也分享网站模板素材,jquery插件等方面素材。


站点声明:相关侵权、举报、投诉及建议等,请发E-mail:936594075#qq.com(#替换成@)。

Copyright © 2018, www.shuchengxian.com, All rights reserved. 个人博客皖公网安备 34152302000022号 皖ICP备15015490号

关键词:个人博客,PHP博客,PHP博客程序,高蒙博客,高蒙个人博客,php程序员博客,程序员个人博客