经过测试网站打开缓慢与php无直接关系

大胡子

1. /usr/sbin/mysqld, Version: 5.1.66-0+squeeze1-log ((Debian)). started with:
   
2. Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
   
3. Time                 Id Command    Argument
   
4. # Time: 130313 21:38:12
   
5. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
   
6. # Query_time: 0.000497  Lock_time: 0.000163 Rows_sent: 1  Rows_examined: 5
   
7. SET timestamp=1363181892;
   
8. SELECT count(*) FROM mysql.user WHERE user='root' and password='';
   
9. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
   
10. # Query_time: 0.015748  Lock_time: 0.000267 Rows_sent: 36  Rows_examined: 58
    
11. SET timestamp=1363181892;
    
12. select concat('select count(*) into @discard from `',
    
13.                     TABLE_SCHEMA, '`.`', TABLE_NAME, '`')
    
14.       from information_schema.TABLES where ENGINE='MyISAM';
    
15. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
16. # Query_time: 0.025962  Lock_time: 0.000291 Rows_sent: 0  Rows_examined: 590
    
17. SET timestamp=1363181892;
    
18. select count(*) into @discard from `information_schema`.`COLUMNS`;
    
19. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
20. # Query_time: 0.000553  Lock_time: 0.000426 Rows_sent: 0  Rows_examined: 0
    
21. SET timestamp=1363181892;
    
22. select count(*) into @discard from `information_schema`.`EVENTS`;
    
23. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
24. # Query_time: 0.028746  Lock_time: 0.000403 Rows_sent: 0  Rows_examined: 58
    
25. SET timestamp=1363181892;
    
26. select count(*) into @discard from `information_schema`.`PARTITIONS`;
    
27. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
28. # Query_time: 0.000478  Lock_time: 0.000212 Rows_sent: 0  Rows_examined: 10
    
29. SET timestamp=1363181892;
    
30. select count(*) into @discard from `information_schema`.`PLUGINS`;
    
31. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
32. # Query_time: 0.000345  Lock_time: 0.000194 Rows_sent: 0  Rows_examined: 1
    
33. SET timestamp=1363181892;
    
34. select count(*) into @discard from `information_schema`.`PROCESSLIST`;
    
35. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
36. # Query_time: 0.000396  Lock_time: 0.000293 Rows_sent: 0  Rows_examined: 0
    
37. SET timestamp=1363181892;
    
38. select count(*) into @discard from `information_schema`.`ROUTINES`;
    
39. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
40. # Query_time: 0.002947  Lock_time: 0.000247 Rows_sent: 0  Rows_examined: 0
    
41. SET timestamp=1363181892;
    
42. select count(*) into @discard from `information_schema`.`TRIGGERS`;
    
43. # User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
    
44. # Query_time: 0.002985  Lock_time: 0.000246 Rows_sent: 0  Rows_examined: 0
    
45. SET timestamp=1363181892;
    
46. select count(*) into @discard from `information_schema`.`VIEWS`;
    
47. # Time: 130313 21:39:24
    
48. # User@Host: aabbcc[aabbcc] @ localhost []
    
49. # Query_time: 0.001011  Lock_time: 0.000059 Rows_sent: 52  Rows_examined: 52
    
50. use aabbcc;
    
51. SET timestamp=1363181964;
    
52. SELECT *  FROM typecho_options WHERE  (`user` = 0 );
    
53. # User@Host: aabbcc[aabbcc] @ localhost []
    
54. # Query_time: 0.000126  Lock_time: 0.000031 Rows_sent: 0  Rows_examined: 52
    
55. SET timestamp=1363181964;
    
56. SELECT *  FROM typecho_options WHERE  (`user` = '1' );
    
57. # Time: 130313 21:39:28
    
58. # User@Host: aabbcc[aabbcc] @ localhost []
    
59. # Query_time: 0.001375  Lock_time: 0.000151 Rows_sent: 2  Rows_examined: 4
    
60. SET timestamp=1363181968;
    
61. SELECT typecho_comments.`coid` , typecho_comments.`cid` , typecho_comments.`author` , typecho_comments.`mail` , typecho_comments.`url` , typecho_comments.`ip` , typecho_comments.`authorId` , typecho_comments.`ownerId` , typecho_comments.`agent` , typecho_comments.`text` , typecho_comments.`type` , typecho_comments.`status` , typecho_comments.`parent` , typecho_comments.`created`  FROM typecho_comments WHERE  (typecho_comments.`status` = 'approved' ) AND (`ownerId` <> `authorId` ) ORDER BY typecho_comments.`coid`  DESC LIMIT 10;
    
62. # Time: 130313 21:39:41
    
63. # User@Host: aabbcc[aabbcc] @ localhost []
    
64. # Query_time: 12.555035  Lock_time: 0.000053 Rows_sent: 30  Rows_examined: 15135
    
65. SET timestamp=1363181981;
    
66. SELECT *  FROM typecho_contents WHERE  (`status` = 'publish' ) AND (`type` = 'post' ) ORDER BY RAND()  ASC LIMIT 30;
    
67. # User@Host: aabbcc[aabbcc] @ localhost []
    
68. # Query_time: 0.000330  Lock_time: 0.000065 Rows_sent: 24  Rows_examined: 58
    
69. SET timestamp=1363181981;
    
70. SELECT *  FROM typecho_metas WHERE  (`type` = 'tag' ) AND (`count` > 0 ) ORDER BY `count`  DESC LIMIT 28;
    
71. # User@Host: aabbcc[aabbcc] @ localhost []
    
72. # Query_time: 0.000295  Lock_time: 0.000087 Rows_sent: 10  Rows_examined: 44
    
73. SET timestamp=1363181981;
    
74. SELECT *  FROM typecho_metas WHERE  (`type` = 'category' ) ORDER BY typecho_metas.`order`  ASC;
    
75. # Time: 130313 21:40:20
    
76. # User@Host: aabbcc[aabbcc] @ localhost []
    
77. # Query_time: 9.610633  Lock_time: 0.000123 Rows_sent: 30  Rows_examined: 15135
    
78. SET timestamp=1363182020;
    
79. SELECT *  FROM typecho_contents WHERE  (`status` = 'publish' ) AND (`type` = 'post' ) ORDER BY RAND()  ASC LIMIT 30;
    
80. # Time: 130313 21:40:56
    
81. # User@Host: aabbcc[aabbcc] @ localhost []
    
82. # Query_time: 7.751408  Lock_time: 0.000074 Rows_sent: 30  Rows_examined: 15135
    
83. SET timestamp=1363182056;
    
84. SELECT *  FROM typecho_contents WHERE  (`status` = 'publish' ) AND (`type` = 'post' ) ORDER BY RAND()  ASC LIMIT 30;
    
85. # Time: 130313 21:41:09
    
86. # User@Host: aabbcc[aabbcc] @ localhost []
    
87. # Query_time: 8.684856  Lock_time: 0.000103 Rows_sent: 30  Rows_examined: 15135
    
88. SET timestamp=1363182069;
    
89. SELECT *  FROM typecho_contents WHERE  (`status` = 'publish' ) AND (`type` = 'post' ) ORDER BY RAND()  ASC LIMIT 30;
    

复制代码

求带 每次查询时间基本7 -15秒 这Y怎么受得了   机子内存不够？


my.rar (2.14 KB, 下载次数: 399)

2013-3-13 21:37 上传

点击文件名下载附件

大胡子

wdlth 发表于 2013-3-13 23:18
难道是没索引的……

高级点的我就不会了 现在丢其他机子上秒开。。。

imtu

帮顶

wdlth

大胡子 发表于 2013-3-13 23:05
没有 我没开InnoDB

难道是没索引的……

大胡子

wdlth 发表于 2013-3-13 22:57
SHOW INDEX FROM typecho_contents，不过我认为key_buffer_size那个设得好像太大了，还有Key_reads和Key_ ...

没有 我没开InnoDB

大胡子

smyz 发表于 2013-3-13 22:53
有些东西并非都要塞给mysql处理啊。
有内存的话利用内存，php来处理很快很快就能响应了。

太难了 搞不定。。。

wdlth

大胡子 发表于 2013-3-13 22:41
上面的复制不到。。。

SHOW INDEX FROM typecho_contents，不过我认为key_buffer_size那个设得好像太大了，还有Key_reads和Key_read_requests怎么都是0，你用InnoDB？
当然最好还是有KVDB，加个memcached的吧。

ohmyga

1. SELECT * FROM `typecho_contents`
   
2. WHERE status ='publish' AND type='post' AND id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `typecho_contents`)))
   
3. ORDER BY id LIMIT 30;

复制代码

smyz

大胡子 发表于 2013-3-13 22:42
应该怎么写 我汗 求爆照 姐姐？

有些东西并非都要塞给mysql处理啊。
有内存的话利用内存，php来处理很快很快就能响应了。

查id与标题出来用php随机，不用order by。
或是php先随机30id数再去查mysql也不用order by。

估计速度会提高N倍……

大胡子

smyz 发表于 2013-3-13 22:30
大胡子。。。优化下php的SQL语句。。。

应该怎么写 我汗 求爆照 姐姐？