AWS RDS 預設記錄對數據庫表的慢查詢。

有沒有辦法針對該表執行 mysqldumpslow 而不先將其導出到文件？或者有沒有更好的方法來獲取慢查詢的報告？

mysqldumpslow僅適用於文本文件，因此您需要忘記使用它。

該表mysql.slow_log預設為 CSV 表。

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
      Table: slow_log
Create Table: CREATE TABLE `slow_log` (
 `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `user_host` mediumtext NOT NULL,
 `query_time` time NOT NULL,
 `lock_time` time NOT NULL,
 `rows_sent` int(11) NOT NULL,
 `rows_examined` int(11) NOT NULL,
 `db` varchar(512) NOT NULL,
 `last_insert_id` int(11) NOT NULL,
 `insert_id` int(11) NOT NULL,
 `server_id` int(10) unsigned NOT NULL,
 `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

您不想查詢 CSV 表，因為它每次都會進行全表掃描。

您可以做的是將表的副本創建到另一個數據庫和索引中start_time

DROP DATABASE IF EXISTS my_slow_log;
CREATE DATABASE my_slow_log;
CREATE TABLE my_slow_log.slow_log LIKE mysql.slow_log;
ALTER TABLE my_slow_log.slow_log ADD INDEX (start_time);
ALTER TABLE my_slow_log.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
   SELECT * FROM mysql.slow_log
;
ALTER TABLE my_slow_log.slow_log ENABLE KEYS;

然後，您可以my_slow_log.slow_log按日期時間值查詢

我以前討論過這個

• Nov 18, 2011:在 MySQL 中創建自定義日誌表
• Mar 30, 2012: MySQL慢日誌配置

試一試 ！！！

警告：每次需要最新的慢日誌時，您都必須重新載入數據。

在這種情況下，如果您只想從 3 天前的午夜開始緩慢記錄日誌，請執行以下操作：

TRUNCATE TABLE my_slow_log.slow_log;
ALTER TABLE my_slow_log.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
   SELECT * FROM mysql.slow_log
   WHERE start_time >= DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
;
ALTER TABLE my_slow_log.slow_log ENABLE KEYS;

或僅在過去 3 小時內

TRUNCATE TABLE my_slow_log.slow_log;
ALTER TABLE mysql.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
   SELECT * FROM mysql.slow_log
   WHERE start_time >= (NOW() - INTERVAL 3 HOUR);
;
ALTER TABLE my_slow_log.slow_log; ENABLE KEYS;

更新 2014-12-11 14:50 EST

你的評論

這使得查詢數據更快一些，但我對如何提供慢查詢的摘要感興趣，然後我可以將其傳遞給我們的開發人員

您可以通過請求慢速日誌條目來向您的開發人員展示此內容，如下所示：

今日 20 個表現最差的查詢

SELECT query_time,sql_text
FROM my_slow_log.slow_log
WHERE start_time >= DATE(NOW()) + INTERVAL 0 SECOND
ORDER BY query_time DESC
LIMIT 20;

過去 2 週內每小時的慢查詢計數

SELECT hr,COUNT(1) SlowQueries FROM
(
   SELECT (unix_timestamp(start_time) - MOD(unix_timestamp(start_time),3600)) hr
   FROM my_slow_log.slow_log
   WHERE start_time >= DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND
) A GROUP BY hr;

過去 6 小時內每分鐘的慢查詢計數

SELECT mn,COUNT(1) SlowQueries FROM
(
   SELECT (unix_timestamp(start_time) - MOD(unix_timestamp(starttime),60)) mn
   FROM my_slow_log.slow_log
   WHERE start_time >= ( NOW() - INTERVAL 6 HOUR )
) A GROUP BY mn;

像這樣的查詢可以在慢查詢執行時為您提供統計資訊以及實際查詢是什麼。

您可以將這些查詢發送到文本文件並將它們發送給開發人員

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT query_time,sql_text FROM my_slow_log.slow_log"
SQL="${SQL} WHERE start_time >= DATE(NOW()) + INTERVAL 0 SECOND"
SQL="${SQL} ORDER BY query_time DESC LIMIT 20"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Slow_Queries_Report.txt

引用自：https://dba.stackexchange.com/questions/85951