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

18\MySQL查看正在运行的SQL

完整SQL
SELECT a.*, c.thread_id, c.sql_text from information_schema.processlist a
LEFT JOIN performance_schema.threads b on a.id = b.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current c on c.THREAD_ID = b.THREAD_ID;

 19\mysql 查询没有主键的表

SELECT a.TABLE_SCHEMA, a.TABLE_NAME FROM ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS a LEFT JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME WHERE b.TABLE_NAME IS NULL;

1、查看数据库中不为 InnoDB 引擎的表

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE

TABLE_SCHEMANOT IN ('sys', 'mysql', 'performance_schema', 'information_schema',

'test') AND ENGINE != 'InnoDB';

2、查看数据库中表的大小及数据量

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, CONCAT(round((DATA_LENGTH +

INDEX_LENGTH) /1024 / 1024, 2), 'MB') as data FROM information_schema.TABLES

WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema',

'information_schema', 'test') ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

3、查找数据库中无显式主键索引的表

SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.TABLES t WHERE

(t.TABLE_SCHEMA, t.TABLE_NAME)NOT IN (SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME

FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI') AND t.TABLE_SCHEMA NOT

IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test');

4、查找数据库中主键为联合主键的表

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY

SEQ_IN_INDEX SEPARATOR',') cols, MAX(SEQ_IN_INDEX) len FROM

information_schema.STATISTICSWHERE INDEX_NAME = 'PRIMARY' AND TABLE_SCHEMA NOT

IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY

TABLE_SCHEMA, TABLE_NAMEHAVING len > 1;

5、查找数据库中不为自增主键的表

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE

TABLE_SCHEMANOT IN ('sys', 'mysql', 'performance_schema', 'information_schema',

'test') AND (TABLE_SCHEMA,TABLE_NAME) NOT IN (SELECT TABLE_SCHEMA, TABLE_NAME

FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',

'performance_schema', 'information_schema', 'test') AND IS_NULLABLE = 'NO' AND

COLUMN_TYPELIKE '%int%' AND COLUMN_KEY = 'PRI' AND EXTRA = 'auto_increment');

6、查看数据库中存在外键约束的表

SELECT c.TABLE_SCHEMA, c.REFERENCED_TABLE_NAME, c.REFERENCED_COLUMN_NAME, c

.TABLE_NAME, c.COLUMN_NAME, c.CONSTRAINT_NAME, t.TABLE_COMMENT, r.UPDATE_RULE, r

.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE c JOIN information_schema

.TABLES t ON t.TABLE_NAME = c.TABLE_NAME JOIN information_schema.REFERENTIAL

_CONSTRAINTS r ON r.TABLE_NAME = c.TABLE_NAME AND r.CONSTRAINT_NAME = c

.CONSTRAINT_NAME AND r.REFERENCED_TABLE_NAME = c.REFERENCED_TABLE_NAME WHERE c

.REFERENCED_TABLE_NAME IS NOT NULL;

7、查找数据库中低区分度索引(区分度小于0.1)

SELECT p.TABLE_SCHEMA, p.TABLE_NAME, c.INDEX_NAME, c.car, p.car total FROM (

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(CARDINALITY) car FROM

information_schema.STATISTICSWHERE INDEX_NAME != 'PRIMARY' AND TABLE_SCHEMA NOT

IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY

TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) cINNER JOIN (SELECT TABLE_SCHEMA,

TABLE_NAME,MAX(CARDINALITY) car from information_schema.STATISTICS WHERE

INDEX_NAME ='PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql',

'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA,

TABLE_NAME) pON c.TABLE_NAME = p.TABLE_NAME AND c.TABLE_SCHEMA = p.TABLE_SCHEMA

WHERE p.car > 0 AND c.car / p.car < 0.1;

8、查找数据库中重复索引前缀的索引

SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, a.cols, b.INDEX_NAME, b.cols

FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ',

GROUP_CONCAT(COLUMN_NAMEORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols

FROM information_schema.STATISTICS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',

'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY'

GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a INNER JOIN (SELECT

TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME

ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols FROM

information_schema.STATISTICSWHERE TABLE_SCHEMA NOT IN ('sys', 'mysql',

'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY'

GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) b ON a.TABLE_NAME = b.TABLE_NAME

AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.cols LIKE CONCAT(b.cols, '%') AND

a.INDEX_NAME != b.INDEX_NAME;

9、查找数据库中包索引重复包含主键列的索引

SELECT a.*, b.pk FROM (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| '

, GROUP_CONCAT(COLUMN_NAMEORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') cols

FROM information_schema.STATISTICS WHERE INDEX_NAME != 'PRIMARY' AND

TABLE_SCHEMANOT IN ('sys', 'mysql', 'performance_schema', 'information_schema',

'test') GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) a INNER JOIN (SELECT

TABLE_SCHEMA, TABLE_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY

SEQ_IN_INDEX SEPARATOR' | '), ' |') pk FROM information_schema.STATISTICS WHERE

INDEX_NAME ='PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql',

'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA,

TABLE_NAME) bON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA

AND a.cols LIKE CONCAT('%', b.pk, '%');

10、查找数据库中没有被使用的索引

SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS FROM

performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE aINNER JOIN

information_schema.TABLES bON a.OBJECT_SCHEMA = b.TABLE_SCHEMA AND

a.OBJECT_NAME = b.TABLE_SCHEMAWHERE a.INDEX_NAME IS NOT NULL AND a.INDEX_NAME !=

'PRIMARY' AND a.COUNT_STAR = 0 AND OBJECT_SCHEMA NOT IN ('sys', 'mysql',

'performance_schema', 'information_schema') ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

11、查看数据库中的锁请求信息

SELECT r.TRX_ISOLATION_LEVEL, r.TRX_ID WAITING_TRX_ID, r.TRX_MYSQL_THREAD_ID

WAITING_TRX_THREAD, r.TRX_STATE WAITING_TRX_STATE, lr.LOCK_MODE

WAITING_TRX_LOCK_MODE, lr.LOCK_TYPE WAITING_TRX_LOCK_TYPE, lr.LOCK_TABLE

WAITING_TRX_LOCK_TABLE, lr.LOCK_INDEX WAITING_TRX_LOCK_INDEX, r.TRX_QUERY

WAITING_TRX_QUERY, b.TRX_ID BLOCKING_TRX_ID, b.TRX_MYSQL_THREAD_ID

BLOCKING_TRX_THREAD, b.TRX_STATE BLOCKING_TRX_STATE, lb.LOCK_MODE

BLOCKING_TRX_LOCK_MODE, lb.LOCK_TYPE BLOCKING_TRX_LOCK_TYPE, lb.LOCK_TABLE

BLOCKING_TRX_LOCK_TABLE, lb.LOCK_INDEX BLOCKING_TRX_LOCK_INDEX, b.TRX_QUERY

BLOCKING_QUERY FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN

INFORMATION_SCHEMA.INNODB_TRX b ON b.TRX_ID = W.BLOCKING_TRX_ID INNER JOIN

information_schema.INNODB_TRX R ON r.TRX_ID = W.REQUESTING_TRX_ID INNER JOIN

information_schema.INNODB_LOCKS lb ON lb.LOCK_TRX_ID = W.BLOCKING_TRX_ID INNER

JOIN information_schema.INNODB_LOCKS lr ON lr.LOCK_TRX_ID = W.REQUESTING_TRX_ID;

注：本文提供的 SQL 脚本只适应特定的数据库版本，不代表所有数据库版本。
 

1.数据库大表信息查看

统计某库下各表大小 不要存在过大的表信息。本身分配内存有限，过大的表会不停地刷新新旧数据，IO交付频繁，导致性能衰减。

SELECT TABLE_SCHEMA,
       TABLE_NAME TABLE_NAME, TABLE_ROWS,
                  CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,
                  CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,
                  CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,
                  engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'performance_schema',
                           'sys',
                           'mysql') 
ORDER BY (data_length + index_length) DESC LIMIT 10;
 
 
+--------------+-----------------------+------------+-------------+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME            | TABLE_ROWS | data_length | index_length | total_size | ENGINE |
+--------------+-----------------------+------------+-------------+--------------+------------+--------+
| employees    | salaries              |    1910497 | 64.59M      | 0.00M        | 64.59M     | InnoDB |
| employees    | employees             |     299556 | 14.52M      | 10.03M       | 24.55M     | InnoDB |
| employees    | employees01           |     101881 | 5.52M       | 8.55M        | 14.06M     | InnoDB |
| employees    | t_temp                |      95374 | 5.52M       | 5.52M        | 11.03M     | InnoDB |
| db3          | t_temp                |       1000 | 0.08M       | 0.13M        | 0.20M      | InnoDB |
| db3          | transportorder        |          3 | 0.02M       | 0.06M        | 0.08M      | InnoDB |
| db3          | transportorderwaybill |          3 | 0.02M       | 0.05M        | 0.06M      | InnoDB |
| db1          | pt1                   |         10 | 0.06M       | 0.00M        | 0.06M      | InnoDB |
| db1          | city                  |          2 | 0.02M       | 0.03M        | 0.05M      | InnoDB |
| db2          | tabname               |         30 | 0.02M       | 0.03M        | 0.05M      | InnoDB |
+--------------+-----------------------+------------+-------------+--------------+------------+--------+
10 rows in set (0.20 sec)
2.存储引擎

存储引擎分布，innodb引擎最适合因为支持事务，行锁级别。

SELECT TABLE_SCHEMA,
       ENGINE,
       COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'PERFORMANCE_SCHEMA',
                           'SYS',
                           'MYSQL')
  AND TABLE_TYPE='BASE TABLE'
GROUP BY TABLE_SCHEMA,
         ENGINE;
非 INNODB 存储引擎表
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_COLLATION,
       ENGINE,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'SYS',
                           'MYSQL',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
  AND ENGINE NOT IN ('INNODB')
ORDER BY TABLE_ROWS DESC ;
3.主键

无主键、无唯一键表。复制主键最重要，数据操作主键效率高。

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
AND   T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';
4.not utf8 table

偏生字成乱码，表情符失效问题。

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
 from information_schema.TABLES 
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
 AND table_schema NOT IN ('information_schema' ,'mysql','performance_schema', 'sys');
5.字符集验证

表之间Join字符集不对称，导致索引失效。

参看系统字符集：
mysql> show global variables like 'collation%';
跟系统字符集不一样的数据库：
SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME  
from information_schema.SCHEMATA  b 
 WHERE  b.SCHEMA_NAME not in ('information_schema' ,'mysql','performance_schema', 'sys')  
AND b.DEFAULT_COLLATION_NAME<>@@collation_server  ; 
跟系统字符集不一样的表和字段:
 select distinct tschema,tname,tcoll
 from 
 (
  select  a.TABLE_SCHEMA as tschema , a.TABLE_NAME as tname,a.TABLE_COLLATION  as tcoll 
 from information_schema.TABLES a 
 WHERE  a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema', 'sys') 
and a.TABLE_COLLATION<>@@collation_server 
union 
 select a.TABLE_SCHEMA as tschema, TABLE_NAME as tname, a.COLLATION_NAME  as tcoll  
 from information_schema.COLUMNS a
  WHERE  a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema', 'sys') 
and a.COLLATION_NAME<>@@collation_server ) as aa ;
6.存储过程&函数

存储过程 函数查看，确实影响mysql 处理能力，后期也不好维护。

##MySQL5.7
SELECT db,type,count(*) 
FROM mysql.proc
WHERE db not in ('mysql','information_schema','performance_schema','sys')
AND type='PROCEDURE'
GROUP BY db, type;
##MySQL8.0
SELECT  Routine_schema, Routine_type 
FROM information_schema.Routines
WHERE  Routine_schema not in ('mysql','information_schema','performance_schema','sys') 
AND ROUTINE_TYPE='PROCEDURE'
GROUP BY Routine_schema, Routine_type;
7.统计视图

确实影响mysql 处理能力，后期也不好维护。特别是ddl变更要注意

SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) 
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA ;
8.自增主键查看

主要考虑自增键超出范围，需要检查一下

SELECT  infotb.TABLE_SCHEMA  ,
infotb.TABLE_NAME,
infotb.AUTO_INCREMENT,
infocl.COLUMN_TYPE  ,
infocl.COLUMN_NAME
FROM information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA 
AND infotb.TABLE_NAME = infocl.TABLE_NAME 
AND infocl.EXTRA='auto_increment';
自增主键使用情况统计：

SELECT  infotb.TABLE_SCHEMA  , infotb.TABLE_NAME, infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE 
FROM  information_schema.TABLES  as infotb INNER JOIN information_schema.COLUMNS infocl 
ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA   
AND infotb.TABLE_NAME = infocl.TABLE_NAME 
AND infocl.EXTRA='auto_increment';
9.分区表

尽量避免分区表，分区表性能问题：体现在分区锁，初期访问加载所有分区

查看实例中的分区表相关信息

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       count(PARTITION_NAME) AS PARTITION_COUNT,
       sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS,
       CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA,
         TABLE_NAME
ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;
 
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME       | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| db           | t1               |             365 |                0 | 5.70M       | 17.11M       | 22.81M     |
| db           | t2               |             391 |                0 | 6.11M       | 0.00M        | 6.11M      |
| db           | t3               |               4 |            32556 | 2.28M       | 0.69M        | 2.97M      |
| db           | t4               |              26 |                0 | 0.41M       | 2.44M        | 2.84M      |
| db           | t5               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |
| db           | t6               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
6 rows in set (1.04 sec)
查看某分区表具体信息，此处以库名为 db、表名为 e 的分区表为例
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       PARTITION_NAME,
       PARTITION_EXPRESSION,
       PARTITION_METHOD,
       PARTITION_DESCRIPTION,
       TABLE_ROWS,
       CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
  AND TABLE_SCHEMA='db'
  AND TABLE_NAME='e';
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| db           | e          | p0             | id                   | RANGE            | 50                    |       4096 | 0.20M       | 0.09M        | 0.30M      |
| db           | e          | p1             | id                   | RANGE            | 100                   |       6144 | 0.28M       | 0.13M        | 0.41M      |
| db           | e          | p2             | id                   | RANGE            | 150                   |       6144 | 0.28M       | 0.13M        | 0.41M      |
| db           | e          | p3             | id                   | RANGE            | MAXVALUE              |      16172 | 1.52M       | 0.34M        | 1.86M      |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
4 rows in set (0.00 sec)
10.计划任务

在不自觉中，自动执行。确认无法维护。

SELECT EVENT_SCHEMA,EVENT_NAME
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA not in ('mysql','information_schema','performance_schema','sys') ;
总结

以上合规检查已经为后期排除了很多隐患。做好上线数据库设计方面合规检查，必不可少的流程。
 

优化、分析Mysql表读写、索引等操作的sql语句效率优化问题

为什么要优化：

随着实际项目的启动，数据库经过一段时间的运行，最初的数据库设置，会与实际数据库运行性能会有一些差异，这时我们 就需要做一个优化调整。

数据库优化这个课题较大，可分为四大类：

》主机性能

》内存使用性能

》网络传输性能

》SQL语句执行性能【软件工程师】

下面列出一些数据库SQL优化方案：

（01）选择最有效率的表名顺序（笔试常考）

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。

例如：查询员工的编号，姓名，工资，工资等级，部门名

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

1)如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推

2)如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推

（02）WHERE子句中的连接顺序（笔试常考）

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

例如：查询员工的编号，姓名，工资，部门名

select emp.empno,emp.ename,emp.sal,dept.dname
from emp,dept
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

（03）SELECT子句中避免使用*号

数据库在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间

select empno,ename from emp;

（04）用TRUNCATE替代DELETE

（05）尽量多使用COMMIT

因为COMMIT会释放回滚点

（06）用WHERE子句替换HAVING子句

WHERE先执行，HAVING后执行

（07）多使用内部函数提高SQL效率

（08）使用表的别名

salgrade s

（09）使用列的别名

ename e

直接上代码：

反映表的读写压力

SELECT file_name AS file,
count_read,
sum_number_of_bytes_read AS total_read,
count_write,
sum_number_of_bytes_write AS total_written,
(sum_number_of_bytes_read + sum_number_of_bytes_write) AS total
FROM performance_schema.file_summary_by_instance
ORDER BY sum_number_of_bytes_read+ sum_number_of_bytes_write DESC;

反映文件的延迟

SELECT (file_name) AS file,
count_star AS total,
CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') AS total_latency,
count_read,
CONCAT(ROUND(sum_timer_read / 1000000000000, 2), 's') AS read_latency,
count_write,
CONCAT(ROUND(sum_timer_write / 3600000000000000, 2), 'h')AS write_latency
FROM performance_schema.file_summary_by_instance
ORDER BY sum_timer_wait DESC;

table 的读写延迟

SELECT object_schema AS table_schema,
object_name AS table_name,
count_star AS total,
CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') as total_latency,
CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency,
CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency
FROM performance_schema.objects_summary_global_by_type
ORDER BY sum_timer_wait DESC;

查看表操作频度

SELECT object_schema AS table_schema,
object_name AS table_name,
count_star AS rows_io_total,
count_read AS rows_read,
count_write AS rows_write,
count_fetch AS rows_fetchs,
count_insert AS rows_inserts,
count_update AS rows_updates,
count_delete AS rows_deletes,
CONCAT(ROUND(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency,
CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency,
CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency,
CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY sum_timer_wait DESC ;

索引状况

SELECT OBJECT_SCHEMA AS table_schema,
OBJECT_NAME AS table_name,
INDEX_NAME as index_name,
COUNT_FETCH AS rows_fetched,
CONCAT(ROUND(SUM_TIMER_FETCH / 3600000000000000, 2), 'h') AS select_latency,
COUNT_INSERT AS rows_inserted,
CONCAT(ROUND(SUM_TIMER_INSERT / 3600000000000000, 2), 'h') AS insert_latency,
COUNT_UPDATE AS rows_updated,
CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency,
COUNT_DELETE AS rows_deleted,
CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h')AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY sum_timer_wait DESC;

全表扫描情况

SELECT object_schema,
object_name,
count_read AS rows_full_scanned
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL
AND count_read > 0
ORDER BY count_read DESC;
没有使用的index

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 not in ('mysql','v_monitor')
AND index_name <> 'PRIMARY'
ORDER BY object_schema, object_name;

糟糕的sql问题摘要

SELECT (DIGEST_TEXT) AS query,
SCHEMA_NAME AS db,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
COUNT_STAR AS exec_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
(SUM_TIMER_WAIT) AS total_latency,
(MAX_TIMER_WAIT) AS max_latency,
(AVG_TIMER_WAIT) AS avg_latency,
(SUM_LOCK_TIME) AS lock_latency,
format(SUM_ROWS_SENT,0) AS rows_sent,
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
SUM_ROWS_EXAMINED AS rows_examined,
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
SUM_CREATED_TMP_TABLES AS tmp_tables,
SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
SUM_SORT_ROWS AS rows_sorted,
SUM_SORT_MERGE_PASSES AS sort_merge_passes,
DIGEST AS digest,
FIRST_SEEN AS first_seen,
LAST_SEEN as last_seen
FROM performance_schema.events_statements_summary_by_digest d
where d
ORDER BY SUM_TIMER_WAIT DESC
limit 20;