|
RT,Dz3.4,有时候cpu占用达到190%!导致论坛经常打不开!查询mysql慢日志代码如下! 这个具体怎么解决,有没有老哥指点一下!
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 68983
- # Query_time: 3.384782 Lock_time: 0.000089 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522728;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:38:49
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 68985
- # Query_time: 3.091555 Lock_time: 0.000102 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522729;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:38:56
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69008
- # Query_time: 3.325886 Lock_time: 0.000094 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522736;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69006
- # Query_time: 3.421492 Lock_time: 0.000097 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522736;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69015
- # Query_time: 3.344995 Lock_time: 0.000093 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522736;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:03
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69031
- # Query_time: 5.658589 Lock_time: 0.000089 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522743;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:04
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69032
- # Query_time: 6.080100 Lock_time: 0.000091 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522744;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:06
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69035
- # Query_time: 7.060208 Lock_time: 0.000113 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522746;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69039
- # Query_time: 7.022993 Lock_time: 0.000092 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522746;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69036
- # Query_time: 7.054990 Lock_time: 0.000096 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522746;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69037
- # Query_time: 7.103437 Lock_time: 0.000109 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522746;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69038
- # Query_time: 7.090650 Lock_time: 0.000093 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522746;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:07
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69043
- # Query_time: 7.351484 Lock_time: 0.000130 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522747;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:11
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69046
- # Query_time: 7.439842 Lock_time: 0.000093 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522751;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:13
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69051
- # Query_time: 7.495992 Lock_time: 0.000095 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522753;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:14
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69057
- # Query_time: 7.341683 Lock_time: 0.000129 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522754;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69056
- # Query_time: 7.348487 Lock_time: 0.000143 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522754;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69065
- # Query_time: 7.135357 Lock_time: 0.000110 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522754;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69063
- # Query_time: 7.170770 Lock_time: 0.000126 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522754;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69066
- # Query_time: 7.211434 Lock_time: 0.000121 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522754;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69067
- # Query_time: 7.217455 Lock_time: 0.000091 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522754;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
- # Time: 240221 21:39:15
- # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1] Id: 69073
- # Query_time: 3.805129 Lock_time: 0.000100 Rows_sent: 10 Rows_examined: 622055
- SET timestamp=1708522755;
- SELECT * FROM pre_forum_thread WHERE FROM_UNIXTIME(dateline, '%Y-%m-%d') = CURDATE() and fid in('2') AND displayorder >=0 ORDER BY views DESC LIMIT 10;
复制代码
gpt给的优化建议是下面这个,如果正确的话,具体优化哪个文件夹呢?
- 当CURDATE()转为索引字段时,MySQL无法直接利用dateline索引,而需要对全表的dateline列进行函数运算后才能做比较,查询效率低。
- 如果dateline是以时间戳存储的,可以把条件改为 dateline = UNIX_TIMESTAMP(CURDATE()),这样可以避免全表转换。
复制代码
我知道答案
回答被采纳将会获得 1 贡献 已有17人回答
|
|