mysql查询超时

rwqw0loc  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(302)

innodb MySQL5.7版
大家好,正如标题所说,我在mysql工作台上使用的查询超时了。我使用的查询和一周前一样,但是不是0.5秒,而是15-25秒。。示例如下:

SELECT * FROM <table> ORDER BY <time column> DESC;

另一方面,我正在使用一些相当长的mysql查询,以便从数据中收集一些粗略的统计数据,我预计这些查询需要更长的时间,但我不确定是否有更好的方法来完成。我还应该提到,我目前只有 SELECT/SHOW 权限。。下面是超时的较长查询:

SELECT DATE(<time_sent_column>) as Update_Date, 
ABS(AVG(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) AS AVG_Latency,
AVG((SELECT 
    ABS(AVG(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) 
    FROM <table_name> 
    WHERE ((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>) < 10800))))
AS Total_Average_Latency,
STDDEV(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)) as STDDEV_Latency,
AVG((SELECT 
    ABS(STDDEV(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) 
    FROM <table_name> 
    WHERE ((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>) < 10800))))
AS Total_STDDEV_Latency,
VAR_SAMP(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(create_date)) as Variance_Latency,
AVG((SELECT 
    ABS(VAR_SAMP(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) 
    FROM <table_name>
    WHERE ((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>) < 10800))))
AS Average_Variance_Latency,
MIN(ABS((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)))) AS Min_Latency,
MAX(ABS((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)))) AS Max_Latency
FROM <table_name>
WHERE ((ABS(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)) < 10800) AND created_at > '2018-05-01')
GROUP BY DATE(<time_created_column>)
ORDER BY DATE(<time_created_column>) DESC;

我可以分解所有这些,但是如果可能的话,我更希望在查询之后将它们放在一个表中。。我是mysql的新手,所以我不确定我是否能使用它 JOINs 或是那种只有 SELECT/SHOW 权限。。
数据库有50%的连接,最小流量,98.7%的密钥效率,87.5%的innodb缓冲区使用率,每秒0次读/写。。任何建议都太好了!谢谢!

f1tvaqid

f1tvaqid1#

(在下文中,我假设 time_created 小于 time_sent ; 如果不是给定的,请调整代码。)
我会从

CREATE TEMPORARY TABLE t
    SELECT DATE(time_sent)    AS Update_Date,
           DATE(time_created) AS Created_Date,
           UNIX_TIMESTAMP(time_sent) - UNIX_TIMESTAMP(time_created)) AS Latency
        FROM table_name
        WHERE UNIX_TIMESTAMP(time_sent) - UNIX_TIMESTAMP(time_created)) < 10800
          AND created_at > '2018-05-01';

然后从临时表生成查询。下一个查询将需要更少的击键。它不需要任何子查询。
(我想 create_date 是打字错误吗?)
(也许你想 >=created_at > '2018-05-01'; ?)
注意,测试 < 10800 在除 WHERE .

相关问题