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