mysql单次查询数据不超过多少条 mysql查询出现次数最多的

# 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;
 没有使用的indexSELECT 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;