select * from schema_object_overview where db='sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | VIEW          |   100 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    21 |
| sys | PROCEDURE     |    26 |
+-----+---------------+-------+
6 rows in set (0.10 sec)

查看Innodb_locks
select *from information_schema.innodb_locks;
show engine innodb status;

查看innodb_trx
select *from information_schema.innodb_trx;

查看innodb_lock_waits, kill 产生死锁的会话:
select * from innodb_lock_waits;

查看列值溢出的情况，比如是否列的自增值会超出数据类型的限制:
select * from schema_auto_increment_columns;

查看表没有使用到的索引:
select * from schema_unused_indexes;

查看全表扫描:
select * from schema_tables_with_full_table_scans;

查看冗余索引:
select * from schema_redundant_indexes;

查看语句的排序情况:
select *from statements_with_sorting;   

查看临时表情况:
select * from statements_with_temp_tables;

-- information_schema:

查看当前数据库运行最耗时的SQL语句TOP 10
select * from information_schema.processlist
where db is not null
and command!='Sleep'
order by time desc
limit 10;

查询各表的大小（排除基础库表)
SELECT
table_schema,
table_name, ROUND((data_length + index_length) / 1024 / 1024,
2) AS 'table_size(M)', ROUND(data_length / 1024 / 1024, 2) AS 'data_length(M)',
ROUND(index_length / 1024 / 1024, 2) AS 'index_length(M)',
table_rows,avg_row_length,ENGINE
FROM
information_schema.tables
WHERE
table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY table_schema, table_rows DESC;

SELECT TABLE_SCHEMA, table_name, ENGINE, TABLE_ROWS,
IF(TABLE_ROWS<=0,'Small',if(TABLE_ROWS>0 and
table_rows<=1000000,'Medieum',if(table_rows>1000000,'Big',ifnull(TABLE_ROWS,
0)))) as table_size FROM information_schema.TABLES order by table_rows desc limit
10;

查看各表空间占用(排除基础数据库)
SELECT ROUND(SUM((data_length+index_length))/1024/1024,2) AS
"engine_total_size(M)", ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema',
'mysql', 'sys')
GROUP BY ENGINE;

查询那些表没主键
select table_schema,table_name
from information_schema.tables
where table_schema='demo' and table_name
not in (select table_name
from information_schema.STATISTICS
where INDEX_NAME='PRIMARY'
and table_schema='demo')

查看各个库大小:
select table_schema as
'Database',round(sum(data_length+index_length)/1024/102
4,2) as "size(M)" from information_schema.tables group by
table_schema;

// performance_schema

查看等待时间最长的前5个事件
select event_name,count_star,sum_timer_wait from
events_waits_summary_global_by_event_name order by SUM_TIMER_WAIT desc limit 5;

查看读写次数最多的前5个文件
select * from file_summary_by_event_name order by count_read+count_write desc limit 5;

查看线程ID为13的最近发生的事件
SELECT event_id,event_name,timer_wait FROM events_waits_history where thread_id=13 order by event_id;

查看当前阶段语句执行的信息
select * from events_stages_current;

查看当前数据库都在做什么
select * from events_waits_current;

获取事件对应磁盘文件的记录
// cond_instances （条件记录表） file_instances （文件记录表） mutex_instances （互斥记录表）rwlock_instances（独占锁记录表）

select * from file_instances;




每秒查看一次当前数据库进程状态
mysqladmin -S /data/mysql/data/mysql_3306.sock -p -r -i 1 processlist

每秒查看数据库的qps
mysqladmin -S /data/mysql/data/mysql_3306.sock -p -r -i 1 extended-status |grep -Ewi 'Questions'

每秒查看数据库状态
mysqladmin -S / data /mysql/data/mysql_3306.sock -p -r -i 1 status



使用sys.diagnostics()存储过程生成性能报告:

此存储过程是也是利用snapshot快照前的性能视图增量值，生成全局性能报告。

该存储过程有三个重要参数，in_max_runtime、in_interval、in_auto_config

in_max_runtime： 总共最大收集时间，单位秒，null 为默认值60秒；
in_interval：快照间的间隔时间，单位秒，null为默认30秒；
in_auto_config： Performance Schema的选项分析current\medium\full,  启的选项指标越全，对MySQL服务的性能影响越大，FULL的影响最大。

收集2分钟的一个性能报告，每次间隔30秒，生成本本报告:

mysql> tee diag.out;
mysql> CALL sys.diagnostics(120, 30, 'current');
mysql> notee;

生成html格式报告:

mysql -u root -p -H -e"CALL sys.diagnostics(120, 30, 'current');" > ./current_instance_report.html