MySQL日常监控及sys库的使用

# MySQL日常监控及sys库的使用

## 一、统计信息（SQL维度）

1、统计执行次数最多的SQL语句：

```sql
SELECT
DIGEST_TEXT,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
COUNT_STAR DESC;
```

2、查看平均响应时间最多的sql语句：

```sql
SELECT
DIGEST_TEXT,
AVG_TIMER_WAIT,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
AVG_TIMER_WAIT DESC;
```

3、查看排序记录数最多的sql

```sql
SELECT
DIGEST_TEXT,
SUM_SORT_ROWS,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_SORT_ROWS DESC;
```

4、扫描记录数最多的sql

```sql
SELECT
DIGEST_TEXT,
SUM_ROWS_EXAMINED,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_ROWS_EXAMINED DESC;
```

5、查看使用临时表最多的sql

```sql
SELECT
DIGEST_TEXT,
SUM_CREATED_TMP_TABLES,
SUM_CREATED_TMP_DISK_TABLES,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_CREATED_TMP_TABLES desc,SUM_CREATED_TMP_DISK_TABLES desc;
```

6、查看返回结果集最多的SQL

```sql
SELECT
DIGEST_TEXT,
SUM_ROWS_SENT,
COUNT_STAR,
FIRST_SEEN,
LAST_SEEN
FROM
`performance_schema`.events_statements_summary_by_digest
ORDER BY
SUM_ROWS_SENT desc;
```

## 二、统计信息(对象维度)

1、查看哪个表物理IO最多？

```sql
SELECT
file_name,
event_name,
SUM_NUMBER_OF_BYTES_READ,
SUM_NUMBER_OF_BYTES_WRITE
FROM
`performance_schema`.file_summary_by_instance
ORDER BY
SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;
```

2、查看哪个表逻辑IO最多?

```sql
SELECT
object_schema,
object_name,
COUNT_READ,
COUNT_WRITE,
COUNT_FETCH,
SUM_TIMER_WAIT
FROM
`performance_schema`.table_io_waits_summary_by_table
ORDER BY
sum_timer_wait DESC;
```

3、查看哪个索引访问最多？

```sql
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_FETCH,
COUNT_INSERT,
COUNT_UPDATE,
COUNT_DELETE
FROM
`performance_schema`.table_io_waits_summary_by_index_usage
ORDER BY
SUM_TIMER_WAIT DESC;
```

4、查看哪个索引从来没有使用过？

```sql
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM
`performance_schema`.table_io_waits_summary_by_index_usage
WHERE
INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'
ORDER BY
OBJECT_SCHEMA,
OBJECT_NAME;
```

## 三、统计信息(等待事件维度)

1、查看哪个等待事件消耗的时间最多？

```sql
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM
`performance_schema`.events_waits_summary_global_by_event_name
WHERE
event_name != 'idle'
ORDER BY
SUM_TIMER_WAIT DESC;
```

## 四、用户、连接类

1、查看每个客户端IP过来的连接消耗资源情况。

```sql
select * from sys.host_summary;
```

2、查看每个用户消耗资源情况

```sql
select * from sys.user_summary;
```

3、查看当前连接情况（有多少连接就应该有多少行）

```sql
select host,current_connections,statements from sys.host_summary;
```

4、查看当前正在执行的SQL和执行show full processlist的结果差不多

```sql
select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session;
```

5、查看总共分配了多少内存

```sql
select * from sys.memory_global_total;
select * from sys.memory_global_by_current_bytes;
```

6、每个库（database）占用多少buffer pool

```sql
select * from sys.innodb_buffer_stats_by_schema order by allocated desc;
```

7、统计每张表具体在InnoDB中具体的情况，比如占多少页？

```sql
select * from sys.innodb_buffer_stats_by_table;
```

8、查询每个连接分配了多少内存利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。

```sql
SELECT
b.USER,
current_count_used,
current_allocated,
current_avg_alloc,
current_max_alloc,
total_allocated,
current_statement
FROM
sys.memory_by_thread_by_current_bytes a,
sys.SESSION b
WHERE
a.thread_id = b.thd_id;
```

9、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考

```sql
select * from sys.schema_auto_increment_columns;
```

10、MySQL索引使用情况统计

```sql
select * from sys.schema_index_statistics order by rows_selected desc;
```

11、MySQL中有哪些冗余索引和无用索引若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。

```sql
select * from sys.schema_redundant_indexes;
```

12、查看库级别的锁信息，这个需要先打开MDL锁的监控：

```sql
--打开MDL锁监控
update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
select * from sys.schema_table_lock_waits;
```

13、MySQL内部有多个线程在运行，线程类型及数量

```sql
select user,count(*) from sys.`processlist` group by user;
```

14、查看MySQL自增id的使用情况

```sql
SELECT
table_schema,
table_name,
ENGINE,
Auto_increment
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA NOT IN ( INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, MYSQL, SYS );
```

15、找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 （此处 5分钟 可根据自己的需要调整SQL标红处）

​ 可复制查询结果到控制台，直接执行，杀死堵塞进程

```sql
select` `concat(``'kill '``, id, ``';'``) ``from` `information_schema.processlist ``where` `Command != ``'Sleep'` `and` `Time` `> 300 ``order` `by` `Time` `desc``;
```

16、按客户端 IP 分组，看哪个客户端的链接数最多

```sql
select` `client_ip,``count``(client_ip) ``as` `client_num ``from` `(``select` `substring_index(host,``':'` `,1) ``as` `client_ip ``from` `information_schema.processlist ) ``as` `connect_info ``group` `by` `client_ip ``order` `by` `client_num ``desc``;
```

17、查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

```sql
select` `* ``from` `information_schema.processlist ``where` `Command != ``'Sleep'` `order` `by` `Time` `desc``;
```