頁面底部的查詢時間變長了要如何優化？另外看了下慢查詢記錄里有個表請求時間很久是缺少索引嗎？

數碼達人

比如Dismall網站底部的處理時間是0.03秒左右，我們之前最快也差不多，現在變成了0.07秒左右，不知道是哪里設置沒優化好還是數據庫問題，已經啟用Redis

登錄/注冊后可看大圖

另外慢查詢里發現兩個表的時間比較久，表是innodb引擎，請問是那里問題？

1. # Time: 2023-12-11T10:52:54.556741Z
   
2. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 364698
   
3. # Query_time: 4.171622  Lock_time: 0.000000 Rows_sent: 6  Rows_examined: 629667
   
4. SET timestamp=1702291974;
   
5. SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
   
6.                         FROM `pre_forum_thread` t
   
7.                          INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
   
8.                          AND t.fid IN ('37','36','2') AND t.isgroup='0'
   
9.                         AND t.displayorder>='0'
   
10.                         ORDER BY t.dateline DESC
    
11.                         LIMIT 0,6;
    
12. # Time: 2023-12-11T10:52:55.744347Z
    
13. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 364782
    
14. # Query_time: 1.078406  Lock_time: 0.000000 Rows_sent: 6  Rows_examined: 156282
    
15. SET timestamp=1702291975;
    
16. SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
    
17.                         FROM `pre_forum_thread` t
    
18.                          INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
    
19.                          AND t.fid IN ('37') AND t.isgroup='0'
    
20.                         AND t.displayorder>='0'
    
21.                         ORDER BY t.dateline DESC
    
22.                         LIMIT 0,6;
    
23. # Time: 2023-12-11T10:54:16.983359Z
    
24. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 366522
    
25. # Query_time: 1.254606  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 397061
    
26. SET timestamp=1702292056;
    
27. SELECT DISTINCT t.*
    
28.                         FROM `pre_forum_thread` t
    
29.                          WHERE t.readperm='0'
    
30.                          AND t.fid IN ('82','40','41','44','43','45','63','39','87','64','51','52','88','56','48','57','59','61','60','66','67','68','70','71','72','74','76','93','79') AND t.isgroup='0'
    
31.                         AND t.displayorder>='0'
    
32.                         ORDER BY t.dateline DESC
    
33.                         LIMIT 0,10;
    
34. # Time: 2023-12-11T10:54:18.474966Z
    
35. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 366548
    
36. # Query_time: 1.226406  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 397061
    
37. SET timestamp=1702292058;
    
38. SELECT DISTINCT t.*
    
39.                         FROM `pre_forum_thread` t
    
40.                          WHERE t.readperm='0'
    
41.                          AND t.fid IN ('37','36','2') AND t.special IN ('0') AND t.isgroup='0'
    
42.                         AND t.displayorder>='0'
    
43.                         ORDER BY t.dateline DESC
    
44.                         LIMIT 0,10;
    
45. # Time: 2023-12-11T10:56:56.033320Z
    
46. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 369432
    
47. # Query_time: 1.996411  Lock_time: 0.001000 Rows_sent: 3  Rows_examined: 397108
    
48. SET timestamp=1702292216;
    
49. SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
    
50.                         FROM `pre_forum_thread` t
    
51.                          INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
    
52.                          AND t.fid IN ('48') AND t.isgroup='0' AND t.dateline >= '1701687413'
    
53.                         AND t.displayorder>='0'
    
54.                         ORDER BY t.recommends DESC
    
55.                         LIMIT 0,3;
    
56. # Time: 2023-12-11T10:57:06.324541Z
    
57. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 369624
    
58. # Query_time: 2.121604  Lock_time: 0.000000 Rows_sent: 3  Rows_examined: 397222
    
59. SET timestamp=1702292226;
    
60. SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
    
61.                         FROM `pre_forum_thread` t
    
62.                          INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
    
63.                          AND t.fid IN ('51','52','88') AND t.isgroup='0' AND t.dateline >= '1701687424'
    
64.                         AND t.displayorder>='0'
    
65.                         ORDER BY t.recommends DESC
    
66.                         LIMIT 0,3;
    

復制代碼

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

天外飄仙

0.07 已經很快了!不影響正常訪問!  沒必要費七八力的再去做優化!
緩存 也要有訪問過 才有作用! 隨著數據庫的增大, 硬盤io的壓力增大, 速度肯定會越來越慢的!

數碼達人

天外飄仙 發表于 2023-12-11 20:09
0.07 已經很快了!不影響正常訪問!  沒必要費七八力的再去做優化!
緩存 也要有訪問過 才有作用! 隨著數據庫 ...

因之前也是0.03秒左右，過幾天就增加了，所以想知道是哪里問題，跟哪些設置有關系

天外飄仙

數碼達人 發表于 2023-12-11 20:19
因之前也是0.03秒左右，過幾天就增加了，所以想知道是哪里問題，跟哪些設置有關系 ...

你多刷新幾次看看呢!
除非慢查詢 頻繁出現, 偶爾出現是正常的! cpu爆表的時候  很容易出現慢查詢! 隨著訪問量的增大, cpu和硬盤io的 可用性能肯定會越來越少!

dz的sql優化 已經非常好了!  我 50G的數據庫 ssd硬盤 沒啥壓力! 如果機械硬盤肯定就不行了! 機械硬盤io不行