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

數碼達人 · 發表于 2023-12-11 19:12:56

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

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

# Time: 2023-12-11T10:52:54.556741Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 364698
# Query_time: 4.171622 Lock_time: 0.000000 Rows_sent: 6 Rows_examined: 629667
SET timestamp=1702291974;
SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
FROM `pre_forum_thread` t
INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
AND t.fid IN ('37','36','2') AND t.isgroup='0'
AND t.displayorder>='0'
ORDER BY t.dateline DESC
LIMIT 0,6;
# Time: 2023-12-11T10:52:55.744347Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 364782
# Query_time: 1.078406 Lock_time: 0.000000 Rows_sent: 6 Rows_examined: 156282
SET timestamp=1702291975;
SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
FROM `pre_forum_thread` t
INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
AND t.fid IN ('37') AND t.isgroup='0'
AND t.displayorder>='0'
ORDER BY t.dateline DESC
LIMIT 0,6;
# Time: 2023-12-11T10:54:16.983359Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 366522
# Query_time: 1.254606 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 397061
SET timestamp=1702292056;
SELECT DISTINCT t.*
FROM `pre_forum_thread` t
WHERE t.readperm='0'
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'
AND t.displayorder>='0'
ORDER BY t.dateline DESC
LIMIT 0,10;
# Time: 2023-12-11T10:54:18.474966Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 366548
# Query_time: 1.226406 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 397061
SET timestamp=1702292058;
SELECT DISTINCT t.*
FROM `pre_forum_thread` t
WHERE t.readperm='0'
AND t.fid IN ('37','36','2') AND t.special IN ('0') AND t.isgroup='0'
AND t.displayorder>='0'
ORDER BY t.dateline DESC
LIMIT 0,10;
# Time: 2023-12-11T10:56:56.033320Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 369432
# Query_time: 1.996411 Lock_time: 0.001000 Rows_sent: 3 Rows_examined: 397108
SET timestamp=1702292216;
SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
FROM `pre_forum_thread` t
INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
AND t.fid IN ('48') AND t.isgroup='0' AND t.dateline >= '1701687413'
AND t.displayorder>='0'
ORDER BY t.recommends DESC
LIMIT 0,3;
# Time: 2023-12-11T10:57:06.324541Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 369624
# Query_time: 2.121604 Lock_time: 0.000000 Rows_sent: 3 Rows_examined: 397222
SET timestamp=1702292226;
SELECT DISTINCT t.*, ti.attachment as attachmenturl, ti.remote
FROM `pre_forum_thread` t
INNER JOIN `pre_forum_threadimage` ti ON t.tid=ti.tid WHERE t.readperm='0'
AND t.fid IN ('51','52','88') AND t.isgroup='0' AND t.dateline >= '1701687424'
AND t.displayorder>='0'
ORDER BY t.recommends DESC
LIMIT 0,3;

復制代碼

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

天外飄仙 · 發表于 2023-12-11 20:42:18

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

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

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

數碼達人 · 發表于 2023-12-11 20:19:19

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

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

天外飄仙 · 發表于 2023-12-11 20:09:56

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

久久久久av_欧美日韩一区二区在线_国产精品三区四区_日韩中字在线

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

產品服務

開發者服務

使用教程

久久久久av_欧美日韩一区二区在线_国产精品三区四区_日韩中字在线

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

產品服務

開發者服務

使用教程

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