页面底部的查询时间变长了要如何优化？另外看了下慢查询记录里有个表请求时间很久是缺少索引吗？

数码达人 · 发表于 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:09:56

0.07 已经很快了!不影响正常访问! 没必要费七八力的再去做优化!
缓存也要有访问过才有作用! 随着数据库的增大, 硬盘io的压力增大, 速度肯定会越来越慢的!

数码达人 · 发表于 2023-12-11 20:19:19

天外飘仙发表于 2023-12-11 20:09
0.07 已经很快了!不影响正常访问! 没必要费七八力的再去做优化!
缓存也要有访问过才有作用! 随着数据库 ...

因之前也是0.03秒左右，过几天就增加了，所以想知道是哪里问题，跟哪些设置有关系

天外飘仙 · 发表于 2023-12-11 20:42:18

数码达人发表于 2023-12-11 20:19
因之前也是0.03秒左右，过几天就增加了，所以想知道是哪里问题，跟哪些设置有关系 ...

你多刷新几次看看呢!
除非慢查询频繁出现, 偶尔出现是正常的! cpu爆表的时候很容易出现慢查询! 随着访问量的增大, cpu和硬盘io的可用性能肯定会越来越少!

dz的sql优化已经非常好了! 我 50G的数据库 ssd硬盘没啥压力! 如果机械硬盘肯定就不行了! 机械硬盘io不行

[求助] 页面底部的查询时间变长了要如何优化？另外看了下慢查询记录里有个表请求时间很久是缺少索引吗？

产品服务

开发者服务

使用教程