dz数据库占用满载，慢日志显示dateline代码问题，求方法！ New

xiao9469 · 发表于 2024-2-21 21:32:35

马上注册，免费下载更多dz插件网资源。

您需要登录才可以下载或查看，没有账号？立即注册

×

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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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: 622055SET 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 贡献已有1人回答

©dz插件网所发布的一切资源仅限用于学习和研究目的；不得将上述内容用于商业或者非法用途，否则，一切后果请用户自负。
本站信息来自网络，版权争议与本站无关。您必须在下载后的24个小时之内，从您的电脑中彻底删除上述内容。如果您喜欢该程序，请支持正版软件，得到更好的正版服务。
如有侵权请邮件与我们联系处理:discuz-x@qq.com（手机随时接收邮件）并出示相关证明以便删除。敬请谅解！

TyCoding · 发表于 2024-2-21 21:33:34

原贴不能编辑了，补充一下gpt的优化方案！这个dateline优化代码在哪个文件了呢？

不要在WHERE条件中对字段进行函数操作。在你的SQL语句中，你使用了FROM_UNIXTIME(dateline, ＇%Y-%m-%d＇) = CURDATE()进行比较，由于需要对每一行的dateline执行FROM_UNIXTIME函数，这将导致索引无法使用，并需要全表扫描。建议将这部分修改为dateline >= UNIX_TIMESTAMP(CURDATE()) AND dateline < UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY))。

复制代码