MySQL入门教程（23）MySQL中默认系统库的说明与实际运用
一、MySQL默认系统库
在MySQL初始化完成后会创建information_schema、performance_schema、mysql、sys这4个默认的系统库。这些系统库主要记录了MySQL服务自身的信息，比如MySQL所创建的用户信息、库表信息等，对数据库的运维管理有非常大的用处。下面是每个系统库的介绍与运用
1、mysql库
该库主要存放了每个用户及其访问权限信息，主要包含表如下：
· user表：用户全局权限表，记录了每个用户的权限信息，如果该表中有字段值为Y就代表该用户拥有全局权限
select * from mysql.user limit 1\G; *************************** 1. row *************************** Host: % #权限生效的地址范围 User: tanglu #用户名 Select_priv: Y #拥有全局读权限 Insert_priv: N #没有写权限 ...省略部分... max_questions: 0 #资源控制相关字段，比如用户每小时最大并发查询数 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password #密码认证插件 authentication_string: *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F password_expired: N #密码是否过期 password_last_changed: 2017-07-01 14:37:32 #最近修改密码的时间 password_lifetime: NULL #密码过期剩余时间 account_locked: N #用户锁定状态
· db表：库级别权限表信息，字段含义同user表，但粒度更细
· tables_priv：表级别权限信息，字段含义同user表，但粒度更细
· columns_priv：字段级别权限信息，字段含义同user表，但粒度更细
· procs_priv：存储过程与函数权限
· proxies_priv：代理用户的权限
· event：事件与任务调度表
· gtid：与GTID主从复制有关的表
· innodb_index_stats：innodb索引统计信息
· innodb_table_stats：innodb表统计信息
· plugin：插件表
2、performance_schema库
该系统库在5.7开始默认开启，5.6或之前版本需要显式开启。收集数据库服务器性能相关数据，覆盖IO、Memory、lock等多个维度。由于该库的结构较为复杂，大多数时候不会直接使用该库来查询性能相关数据，而是使用sys库
[mysqld]
performance_schema = ON
setup_%：和PS库自身设置相关的表
memory_summary_%：内存监视相关的表‘
events_statements_%：事件相关表，最近执行过的语句的各种统计
events_waits_%：事件等待相关
file_%：文件系统调用相关
3、information_schema库
这个库也被称为MySQL的数据字典，存放了所有的元数据信息，比如所有的数据库、表、索引，甚至每个会话信息也在该库中记录。通过这个库可以进行一些数据资产统计，比如有多少个库、多少表、占用了多大的硬盘空间等。需要注意的是这个库并没有将数据持久化到硬盘中，所以并不会生成这个库的数据目录，也不能进行DML操作对数据进行修改
· TABLES表：保存了所有表的数据字典信息，比如表名、表引擎、表大小、表行数等等有用信息。TABLES表关键字段：
TABLE_ROWS：表的行数统计
AVG_ROW_LENGTH：平均行的大小
DATA_LENGTH：表数据的大小
INDEX_LENGTH：表索引的大小
· STATISTICS：保存了索引信息。STATISTICS表关键字段：
SEQ_IN_INDEX字段：字段在索引中的序列
CARDINALITY字段：字段中唯一值的估算，该值越接近表的总行数，说明区分度越高，索引效果越好
· PROCESSLIST表：记录了会话详细信息，执行show processlist命令其实就是在查询该表
· INNODB_TRX表：记录当前正在执行的每个事务（不包含只读事务）的信息，包括事务开始时间、是否正在等待锁等
· INNODB_LOCKS：记录当前正在被阻塞的事务，但是产生阻塞的事务是看不到的，因为它自身没有被阻塞
· INNODB_LOCK_WAITS：记录事务的锁等待信息，如果该表查询为空则表示无锁等待
4、sys库
由于performance_schema中的信息较为复杂，官方为了让用户能更快速了解数据库运行情况，以视图的形式将一些查询存放在了sys库下，也就是说sys库的数据也都来自performance_schema。在sys库中以x$开头的表是一些原始数据，更适合程序或者工具使用，不带x$前缀的视图显示的数据都是经过单位换算后给人阅读的数据，比如单位是秒
statements_with_temp_tables：使用到内部临时表的语句，也就是说这些语句有创建临时表的行为。关键字段：
memory_tmp_tables：SQL语句建立的内部内存临时表的总数
disk_tmp_tables：SQL语句建立的内部磁盘临时表的总数
二、MySQL默认系统库的实际运用
· 通过information.processlist查看每个用户所建立的连接数
SELECT USER , COUNT(*) FROM information_schema.processlist GROUP BY USER;
· 通过information_schema.KEY_COLUMN_USAGE查看指定库是否有表使用外键（通常在开发规范中禁止使用外键，尽量使用程序逻辑来做数据上的约束）
SELECT * FROM information_schema.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA='linuxe' and REFEREBCED_TABLE_SCHEMA is not null \G
· 通过information.STATISTICS表查看索引是否合理，基数值越大、越接近表的总行数，那么索引效果越好
SELECT TABLE_NAME,CARDINALITY FROM information.`STATISTICS` WHERE TABLE_SCHEMA='wp_ark' ORDER BY CARDINALITY DESC #假设wp_ark.ark_express_order CARDINALITY为1000W
SELECT COUNT(*) FROM wp_ark.ark_express_order #该值越接近1000W，说明索引效果越好，如果低于10%都是不合理的索引
· 查看MySQL实例下每个库中包含了哪些表以及对表的数量进行统计
SELECT table_schema,COUNT(table_name),GROUP_CONCAT(table_name) FROM `information_schema`.`tables` GROUP BY table_schema;
· 统计MySQL实例下每个库所占用的磁盘空间总量，以MB为单位显示
SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 FROM `information_schema`.`TABLES` GROUP BY table_schema;
· 统计数据库实例下每个库占用的磁盘空间
SELECT table_schema AS 数据库名, CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024/1024),2),'GB') AS 数据大小, CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024/1024),2),'GB') AS 索引大小, CONCAT(ROUND(SUM((DATA_LENGTH + INDEX_LENGTH)/1024/1024/1024),2),'GB') AS 总大小 FROM information_schema.TABLES GROUP BY table_schema ORDER BY SUM(DATA_LENGTH + INDEX_LENGTH) DESC ;
· 统计数据库实例下每个库下每张表的行数、大小和索引大小
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 ;
· 找出造成阻塞的process id，方便用kill命令断开（sql_kill_blocking_query一栏）
select * from sys.schema_table_lock_waits
· 统计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');
· 统计出MySQL实例下所有用户
SELECT CONCAT(USER,'@',HOST) FROM mysql.user;
· 查询当前MySQL中会话信息，相比使用show processlist命令直接查看，用SQL的形式能灵活的进行过滤
select * from information_schema.processlist where time > 50;
select * from information_schema.processlist where info like 'my query%';
· 批量杀死某个用户的进程
select concat('KILL ',id,';') from information_schema.processlist where user='your_user_name';
· 查看当前正在运行的SQL
SELECT conn_id,USER,current_statement,last_statement FROM sys.session
· 查找没有被使用过的索引，统计方式是从实例启动后到当前期间没有被使用过的索引，对这些索引进行清理可以避免无效索引占用空间和影响数据插入、更新的性能
SELECT * FROM sys.schema_unused_indexes
· 列出指定用户的权限
select * from mysql.user where user='tanglu' \G
· 统计实例下不包含主键索引的表
SELECT tmp.table_name, tmp.table_schema, SUM(tmp.pr) FROM (
SELECT
table_schema,
table_name,
index_name,
CASE index_name
WHEN 'PRIMARY' THEN 1
ELSE 0 END AS pr
FROM information_schema.`STATISTICS`) AS tmp
GROUP BY table_schema, table_name HAVING SUM(pr) = 0;
· 找出非InnoDB引擎的表，并且统计出表的大小（也可以不用format_bytes函数，直接对data_length做计算来计算表大小）
SELECT table_schema,table_name,`engine`,sys.format_bytes(data_length) AS data_size FROM information_schema.tables WHERE `engine` <> 'InnoDB' AND table_schema NOT IN ('mysql','performance_schema','information_schema');
#统计出每个InnoDB表的大小
SELECT table_schema,table_name,`engine`,sys.format_bytes(data_length) AS data_size FROM information_schema.tables WHERE `engine` = 'InnoDB' AND table_schema NOT IN ('mysql','performance_schema','information_schema');
· 查找持续时间超过 60s的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
· 统计有全表扫描的SQL
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10
评论