# MySQL 慢查询分析

---

## 查看慢查询配置

```sql
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
```

---

## 查询慢日志（RDS/MySQL）

```sql
-- 最近24小时慢查询
SELECT
    db,
    LEFT(REPLACE(REPLACE(sql_text, '\n', ' '), '  ', ' '), 200) as sql_preview,
    query_time,
    rows_examined,
    rows_sent,
    start_time
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY query_time DESC
LIMIT 30;

-- 查询所有慢记录
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 20;
```

---

## 查看当前运行的长查询

```sql
-- 查看所有查询
SHOW FULL PROCESSLIST;

-- 筛选长查询（超过10秒）
SELECT
    id, User, Host, db, Time, State,
    LEFT(Info, 150) as sql_preview
FROM information_schema.processlist
WHERE Command = 'Query' AND Time > 10
ORDER BY Time DESC;
```

---

## 终止查询

```sql
-- 单个终止
KILL <process_id>;

-- 批量生成终止语句（按来源IP）
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE Host LIKE '39.99.243.191%' AND Command = 'Query';
```

---

## performance_schema 分析

```sql
-- 按总耗时排序
SELECT
    SCHEMA_NAME,
    LEFT(DIGEST_TEXT, 150) as sql_pattern,
    COUNT_STAR as exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) as total_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms,
    ROUND(MAX_TIMER_WAIT/1000000000000, 2) as max_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 按平均耗时排序
SELECT
    SCHEMA_NAME,
    LEFT(DIGEST_TEXT, 150) as sql_pattern,
    COUNT_STAR as exec_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms,
    ROUND(MAX_TIMER_WAIT/1000000000000, 2) as max_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 15;
```

---

## 查看表大小

```sql
SELECT
    table_schema,
    table_name,
    ROUND(data_length/1024/1024, 2) as data_MB,
    ROUND(index_length/1024/1024, 2) as index_MB,
    table_rows
FROM information_schema.tables
WHERE table_schema IN ('db1', 'db2')
ORDER BY data_length DESC
LIMIT 30;
```

---

## 常用连接

| 库 | 连接命令 |
|----|----------|
| suke 生产库（阿里云RDS） | `mysql -hrm-8vbrd2wil14hclop3vm.mysql.zhangbei.rds.aliyuncs.com -uroot -pWFFGwffg233` |
| 二号机 | `mysql -h47.92.117.13 -uroot -px251119!` |

---

*创建时间：2026-03-04*