Mysql系统库作用

MySQL初始化完成后会创建出4个系统库，分别是information_schema、performance_schema、mysql、sys

• mysql库 核心数据库
  用户、权限 user表、慢日志和通用日志转化成表形式、索引信息、表信息
  user表：用户全局权限表，记录了每个用户权限
  db表：库级别权限表
  tables_priv：表级权限
  columns_priv：列级别全新表
  procs_priv：存储过程与函数权限
  proxies_priv：代理用户的权限
  event：事件与任务调度表
  gtid：与GTID主从复制有关的表
  innodb_index_stats：innodb索引统计信息
  innodb_table_stats：innodb表统计信息

• performance_schema库 性能表现
  据库服务器性能数据。哪个SQL执行次数最多、耗时最长、哪个SQL被锁等有用的信息

• sys库 performance_schema的简化，大量视图
  performance_schema中的信息较为复杂，官方将performance_schema做了简化形成了一个sys库。sys库的所有数据都是来自performancce_schema，这些视图简化了对performancce_schema的查询，便于更快速了解数据库运行情况。

• information_schema库 元数据信息，MySQL的数据字典
  这个库也被称为MySQL的数据字典，存放了所有的元数据信息，比如所有的数据库、表、索引，会话信息。通过这个库可以进行一些数据资产统计，比如有多少个库、多少表、占用了多大的硬盘空间等。information_schema库没有将数据持久化到硬盘中，所以并不会生成这个库的数据目录，也不能进行DML操作对数据进行修改。
  TABLES表：保存了所有表的数据字典信息，比如表名、表引擎、表大小、表行数等等有用信息
  PROCESSLIST表：记录了会话详细信息，执行show processlist命令其实就是在查询该表
  INNODB_TRX表：记录了所有事务，包括事务是否被锁
  INNODB_LOCK_WAITS：记录了事务正在等待的锁的信息

1、统计MySQL实例下每个库中所有表的表名以及个数

SELECT table_schema,COUNT(table_name),GROUP_CONCAT(table_name) FROM `information_schema`.`tables` GROUP BY table_schema;

2、统计MySQL实例下每个库占用空间总大小，并以MB为单位显示

SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 FROM `information_schema`.`TABLES` GROUP BY table_schema;

3、查找出MySQL实例下非InnoDB的表

SELECT table_schema,table_name FROM information_schema.tables WHERE ENGINE !='InnoDB' AND table_schema NOT IN ('sys','performance_schema','information_schema','mysql');

4、统计出MySQL实例下所有用户

SELECT CONCAT(USER,'@',HOST) FROM mysql.user;

5、查询当前MySQL中会话信息，相比使用show processlist命令直接查看，用SQL的形式能灵活的进行过滤

select * from information_schema.processlist where time > 50;
select * from information_schema.processlist where info like 'my query%';

6、批量杀死某个用户的进程

select concat('KILL ',id,';') from information_schema.processlist where user='your_user_name';

7、查看当前正在运行的SQL

SELECT conn_id,USER,current_statement,last_statement FROM sys.session

8、统计有全表扫描的SQL

SELECT * FROM sys.statements_with_full_table_scans LIMIT 10

http://www.linuxe.cn/post-558.html