php怎么解决慢查询,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;