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';
  ```

# 參考

![](http://img.blog.itpub.net/blog/2023/05/17/859380e52eee5ed3.png?x-oss-process=style/bb)

MySQL：慢SQL（slow_log）

相關文章