mysql.slow_log 表是 csv 表，没有索引。 ```sql CREATE TABLE `slow_log` ( `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, `query_time` time(6) NOT NULL, `lock_time` time(6) NOT NULL, `rows_sent` int NOT NULL, `rows_examined` int NOT NULL, `db` varchar(512) NOT NULL, `last_insert_id` int NOT NULL, `insert_id` int NOT NULL, `server_id` int unsigned NOT NULL, `sql_text` mediumblob NOT NULL, `thread_id` bigint unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='Slow log' ``` # slow_log 改造 获取慢查询日志很慢，想添加索引来加速访问，而 csv 引擎不能添加索引(csv 引擎存储是以逗号分割的文本来存储的)，只能改存储引擎来添加索引了 ```sql mysql> set global slow_query_log=off; Query OK, 0 rows affected (0.00 sec) mysql> alter table mysql.slow_log engine=innodb; ERROR 1579 (HY000): This storage engine cannot be used for log tables" mysql> alter table mysql.slow_log engine=myisam; Query OK, 45 rows affected (1.37 sec) Records: 45 Duplicates: 0 Warnings: 0 ``` # 慢 SQL 清理 - TABLE ```sql SET GLOBAL slow_query_log = 'OFF'; ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop; CREATE TABLE mysql.slow_log LIKE mysql.slow_log_drop; SET GLOBAL slow_query_log = 'ON'; DROP TABLE mysql.slow_log_drop; ``` 直接删除数据，会报如下异常： ```sql mysql> delete from mysql.slow_log; ERROR 1556 (HY000): You can't use locks with log tables. ``` - FILE ```sql mysql> SET GLOBAL slow_query_log = 'OFF'; [root@model log]# cp slow.log slow_drop.log [root@model log]# echo '' > slow.log mysql> SET GLOBAL slow_query_log = 'ON'; ``` # 参考