dz數據庫占用滿載，慢日志顯示dateline代碼問題，求方法！

達達崩吧

   RT,Dz3.4,有時候cpu占用達到190%！導致論壇經常打不開!查詢mysql慢日志代碼如下！ 這個具體怎么解決，有沒有老哥指點一下！

1. 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;
   
2. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 68983
   
3. # Query_time: 3.384782  Lock_time: 0.000089 Rows_sent: 10  Rows_examined: 622055
   
4. SET timestamp=1708522728;
   
5. 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;
   
6. # Time: 240221 21:38:49
   
7. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 68985
   
8. # Query_time: 3.091555  Lock_time: 0.000102 Rows_sent: 10  Rows_examined: 622055
   
9. SET timestamp=1708522729;
   
10. 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;
    
11. # Time: 240221 21:38:56
    
12. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69008
    
13. # Query_time: 3.325886  Lock_time: 0.000094 Rows_sent: 10  Rows_examined: 622055
    
14. SET timestamp=1708522736;
    
15. 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;
    
16. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69006
    
17. # Query_time: 3.421492  Lock_time: 0.000097 Rows_sent: 10  Rows_examined: 622055
    
18. SET timestamp=1708522736;
    
19. 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;
    
20. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69015
    
21. # Query_time: 3.344995  Lock_time: 0.000093 Rows_sent: 10  Rows_examined: 622055
    
22. SET timestamp=1708522736;
    
23. 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;
    
24. # Time: 240221 21:39:03
    
25. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69031
    
26. # Query_time: 5.658589  Lock_time: 0.000089 Rows_sent: 10  Rows_examined: 622055
    
27. SET timestamp=1708522743;
    
28. 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;
    
29. # Time: 240221 21:39:04
    
30. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69032
    
31. # Query_time: 6.080100  Lock_time: 0.000091 Rows_sent: 10  Rows_examined: 622055
    
32. SET timestamp=1708522744;
    
33. 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;
    
34. # Time: 240221 21:39:06
    
35. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69035
    
36. # Query_time: 7.060208  Lock_time: 0.000113 Rows_sent: 10  Rows_examined: 622055
    
37. SET timestamp=1708522746;
    
38. 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;
    
39. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69039
    
40. # Query_time: 7.022993  Lock_time: 0.000092 Rows_sent: 10  Rows_examined: 622055
    
41. SET timestamp=1708522746;
    
42. 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;
    
43. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69036
    
44. # Query_time: 7.054990  Lock_time: 0.000096 Rows_sent: 10  Rows_examined: 622055
    
45. SET timestamp=1708522746;
    
46. 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;
    
47. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69037
    
48. # Query_time: 7.103437  Lock_time: 0.000109 Rows_sent: 10  Rows_examined: 622055
    
49. SET timestamp=1708522746;
    
50. 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;
    
51. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69038
    
52. # Query_time: 7.090650  Lock_time: 0.000093 Rows_sent: 10  Rows_examined: 622055
    
53. SET timestamp=1708522746;
    
54. 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;
    
55. # Time: 240221 21:39:07
    
56. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69043
    
57. # Query_time: 7.351484  Lock_time: 0.000130 Rows_sent: 10  Rows_examined: 622055
    
58. SET timestamp=1708522747;
    
59. 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;
    
60. # Time: 240221 21:39:11
    
61. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69046
    
62. # Query_time: 7.439842  Lock_time: 0.000093 Rows_sent: 10  Rows_examined: 622055
    
63. SET timestamp=1708522751;
    
64. 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;
    
65. # Time: 240221 21:39:13
    
66. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69051
    
67. # Query_time: 7.495992  Lock_time: 0.000095 Rows_sent: 10  Rows_examined: 622055
    
68. SET timestamp=1708522753;
    
69. 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;
    
70. # Time: 240221 21:39:14
    
71. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69057
    
72. # Query_time: 7.341683  Lock_time: 0.000129 Rows_sent: 10  Rows_examined: 622055
    
73. SET timestamp=1708522754;
    
74. 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;
    
75. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69056
    
76. # Query_time: 7.348487  Lock_time: 0.000143 Rows_sent: 10  Rows_examined: 622055
    
77. SET timestamp=1708522754;
    
78. 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;
    
79. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69065
    
80. # Query_time: 7.135357  Lock_time: 0.000110 Rows_sent: 10  Rows_examined: 622055
    
81. SET timestamp=1708522754;
    
82. 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;
    
83. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69063
    
84. # Query_time: 7.170770  Lock_time: 0.000126 Rows_sent: 10  Rows_examined: 622055
    
85. SET timestamp=1708522754;
    
86. 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;
    
87. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69066
    
88. # Query_time: 7.211434  Lock_time: 0.000121 Rows_sent: 10  Rows_examined: 622055
    
89. SET timestamp=1708522754;
    
90. 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;
    
91. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69067
    
92. # Query_time: 7.217455  Lock_time: 0.000091 Rows_sent: 10  Rows_examined: 622055
    
93. SET timestamp=1708522754;
    
94. 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;
    
95. # Time: 240221 21:39:15
    
96. # User@Host: www_xxxxx_co[www_xxxxx_co] @ localhost [127.0.0.1]  Id: 69073
    
97. # Query_time: 3.805129  Lock_time: 0.000100 Rows_sent: 10  Rows_examined: 622055
    
98. SET timestamp=1708522755;
    
99. 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給的優(yōu)化建議是下面這個，如果正確的話，具體優(yōu)化哪個文件夾呢?

1. 當CURDATE()轉為索引字段時，MySQL無法直接利用dateline索引，而需要對全表的dateline列進行函數運算后才能做比較，查詢效率低。
   
2. 
   
3. 如果dateline是以時間戳存儲的，可以把條件改為 dateline = UNIX_TIMESTAMP(CURDATE())，這樣可以避免全表轉換。

復制代碼

我知道答案 回答被采納將會獲得1 貢獻 已有17人回答

達達崩吧

原貼不能編輯了，補充一下gpt的優(yōu)化方案！這個dateline優(yōu)化代碼在哪個文件了呢？

1. 不要在WHERE條件中對字段進行函數操作。在你的SQL語句中，你使用了FROM_UNIXTIME(dateline, ＇%Y-%m-%d＇) = CURDATE()進行比較，由于需要對每一行的dateline執(zhí)行FROM_UNIXTIME函數，這將導致索引無法使用，并需要全表掃描。建議將這部分修改為dateline >= UNIX_TIMESTAMP(CURDATE()) AND dateline < UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 DAY))。

復制代碼

mingkong

看看對應時間節(jié)點，是哪些腳本在運行。
或者通過慢查詢的關鍵詞來搜索源碼
通過此類方法先找到慢查詢的實際PHP腳本位置，再談怎么優(yōu)化和改造

達達崩吧

mingkong 發(fā)表于 2024-2-21 23:13
看看對應時間節(jié)點，是哪些腳本在運行。
或者通過慢查詢的關鍵詞來搜索源碼
通過此類方法先找到慢查詢的實際 ...

應該不是插件問題，應該還是源碼問題，嘗試過關掉所有插件，確實沒有問題了，但是只要開啟的插件調用這個數據表，就會出現慢日志記錄！

mingkong

達達崩吧 發(fā)表于 2024-2-22 12:16
應該不是插件問題，應該還是源碼問題，嘗試過關掉所有插件，確實沒有問題了，但是只要開啟的插件調用這個 ...

如果關閉某個插件就正常了，那就從這個插件的代碼入手。看看他如何查詢這個數據表的。

達達崩吧

mingkong 發(fā)表于 2024-2-22 12:22
如果關閉某個插件就正常了，那就從這個插件的代碼入手。看看他如何查詢這個數據表的。 ...

好多插件涉及 pre_forum_thread 這個數據表都會出現在這個問題！連官方的插件也是。所以我推斷不是插件代碼的問題！

科站網

達達崩吧 發(fā)表于 2024-2-23 12:31
好多插件涉及 pre_forum_thread 這個數據表都會出現在這個問題！連官方的插件也是。所以我推斷不是插件代 ...

你不能說插件有問題或者代碼有問題，畢竟這是一個正常的sql，但是考慮使用場景
不是牽涉到pre_forum_thread就有問題，而是這個sql是否在合適的場景使用，比如你慢日志的sql，如果頻繁的調用，那么就可能造成數據庫的服務壓力

打個通俗的比方：圖書館有10000本數（你的數據條目數），10個管理員（性能上限），每個人進圖書館都要查詢下去年2月份買的書單，如果管理員沒有每個月購書的目錄，而書上面貼了購書時間，是不是得一本本看，100個人進來，是不是得排隊

科站網

所以最好的是找到這段sql調用的地方，優(yōu)化下，可以做緩存
繼續(xù)用上面的比方，如果第一個人進來，查到書的目錄，緩存下來，第二個人只要看緩存數據就行了，而不用從頭查一次

達達崩吧

科站網 發(fā)表于 2024-2-23 13:25
所以最好的是找到這段sql調用的地方，優(yōu)化下，可以做緩存
繼續(xù)用上面的比方，如果第一個人進來，查到書的目 ...

緩存已經弄了，沒啥效果，這段sql調用的我感覺就是dateline這里！但是不知道是哪個文件！

ink

pre_forum_thread 這個表是主題表，你后臺暫時關閉了搜索功能試試。