$request_time request processing time in seconds with a milliseconds resolution (1.3.9, 1.2.6); time elapsed since the first bytes were read from the client
从接收到第一个字节到发送回最后一个字节所经历的时间
$upstream_response_time keeps time spent on receiving the response from the upstream server; the time is kept in seconds with millisecond resolution. Times of several responses are separated by commas and colons like addresses in the $upstream_addr variable.
[15-Feb-2016 00:22:17] WARNING: [pool www] server reached pm.max_children setting (20), consider raising it [15-Feb-2016 02:06:25] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 8 children, there are 0 idle, and 9 total children [15-Feb-2016 02:06:26] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 16 children, there are 0 idle, and 10 total children [15-Feb-2016 02:06:27] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 11 total children [15-Feb-2016 02:06:28] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 12 total children [15-Feb-2016 02:06:29] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 13 total children [15-Feb-2016 02:06:30] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 14 total children [15-Feb-2016 02:06:31] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 15 total children [15-Feb-2016 02:06:32] WARNING: [pool www] seems busy (you may need to increase pm.start_servers, or pm.min/max_spare_servers), spawning 32 children, there are 0 idle, and 16 total children
# User@Host: server[server] @ localhost [] # Query_time: 4.025911 Lock_time: 0.000016 Rows_sent: 32 Rows_examined: 65284 SET timestamp=1455591112; select * from `photo` where `status` = '1' and `desc` is not null order by rand() limit 32;
一次查询消耗了4秒多,稍微有点并发请求肯定得死。
问题
检查SQL是如何执行查询操作的
1
select * from `photo` where `status` = '1' and `desc` is not null order by rand() limit 32;
为什么上面的这条SQL会这么消耗时间呢?我们explain一下
1 2 3 4 5 6 7
mysql> explain select * from `photo` where `status` = '1' and `desc` is not null order by rand() limit 32; +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ | 1 | SIMPLE | photo | ALL | NULL | NULL | NULL | NULL | 47606 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+ 1 row inset (0.00 sec)
可以看到photo表内总共47606条记录,使用 order by rand() 使用到了临时表,随着表记录的逐渐增加性能会迅速的下降,导致查询变慢。