一人一花一凋零 发表于 2017-3-13 11:10:00

2秒以上的MYSQL慢查询出来的一些数据，高手帮看一下怎么优化

Count: 6Time=14.91s (89s)Lock=0.00s (0s)Rows=4155435.2 (24932611), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_ucenter_members`

Count: 6Time=3.69s (22s)Lock=0.00s (0s)Rows=4154733.5 (24928401), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_common_member_field_forum`

Count: 6Time=6.12s (36s)Lock=0.00s (0s)Rows=4154733.5 (24928401), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_common_member_field_home`

Count: 6Time=21.72s (130s)Lock=0.00s (0s)Rows=4154733.5 (24928401), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_common_member_profile`

Count: 6Time=8.18s (49s)Lock=0.00s (0s)Rows=4154733.5 (24928401), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_common_member_status`

Count: 6Time=9.77s (58s)Lock=0.00s (0s)Rows=4154733.5 (24928401), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_common_member_count`

Count: 6Time=19.13s (114s)Lock=0.00s (0s)Rows=4152272.5 (24913635), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_common_member`

Count: 6Time=2.46s (14s)Lock=0.00s (0s)Rows=3130240.8 (18781445), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_forum_attachment`

Count: 6Time=11.12s (66s)Lock=0.00s (0s)Rows=1703289.0 (10219734), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_forum_post_tmp`

Count: 2Time=2.26s (4s)Lock=0.00s (0s)Rows=4157801.5 (8315603), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_ucenter_memberfields`

Count: 6Time=9.96s (59s)Lock=0.00s (0s)Rows=749347.3 (4496084), root@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mb_forum_thread`

Count: 1483Time=2.33s (3462s)Lock=0.00s (0s)Rows=21.0 (31143), mbbbs_f@localhost
SELECT DISTINCT t.*
FROM `mb_forum_thread` t
WHERE t.readperm='0'
AND t.special IN ('0') AND t.displayorder IN ('3') AND t.tid NOT IN ('8138820','13155402','10094767','8283673','10025650') AND t.isgroup='0'
AND t.displayorder>='0'
ORDER BY t.lastpost DESC
LIMIT 0,40

Count: 129Time=70.93s (9150s)Lock=0.00s (0s)Rows=8.0 (1032), mbbbs_f@localhost
SELECT * FROM mb_forum_post WHERE tid=7049623 AND `invisible`='0' AND `authorid`='1369075' ORDER BY datelineLIMIT 15

Count: 326Time=78.01s (25432s)Lock=0.00s (0s)Rows=2.0 (652), mbbbs_f@localhost
SELECT * FROM mb_forum_post WHERE tid=174051 AND `invisible`='0' AND `authorid`='9016' ORDER BY datelineLIMIT 15

一周的完整的TXT文件，见附件。
那位大神帮看看，如何优化配置。谢谢。

草木沁清 发表于 2017-3-13 11:10:00

把 localhost 改成127.0.0.1 可能会好些

WZX习惯性潜水 发表于 2017-3-13 11:10:00

这些会员数据、帖子信息的查询，都是正常查询，不应该很慢。是不是你该优化一下服务器？

蓝蓝蓝1112015 发表于 2017-3-13 11:10:00

格东站长网插件发表于 2017-1-9 06:06
这些会员数据、帖子信息的查询，都是正常查询，不应该很慢。是不是你该优化一下服务器？
怎么优化服务器呢？

孩子_爸爸对你 发表于 2017-3-13 11:10:00

和信立-建站发表于 2017-1-9 11:03
怎么优化服务器呢？
不知道啊，你都没说你用的是哪种

缘来缘去谁的错 发表于 2017-3-13 11:10:00

格东站长网插件发表于 2017-1-9 11:41
不知道啊，你都没说你用的是哪种
WINDOW2008+phpstudy 用的是PHP5.3+Apache+mysql

ouhjiao 发表于 2017-3-13 11:10:00

和信立-建站发表于 2017-1-9 11:57
WINDOW2008+phpstudy 用的是PHP5.3+Apache+mysql
我不知道WINDOWS如何，我用的linux才512的内存都感觉很流畅
也许你在衡量服务器的时候，应该考虑内存、磁盘空间、网站访问量、DZ后台优化这些因素，而不是光把程序名字列出来

页: [1]