首页 > 代码库 > information_schema系列七

information_schema系列七

这个系列的文章主要是为了能够让自己了解MySQL5.7的一些系统表,统一做一下备注和使用,也希望分享出来让大家能够有一点点的受益。
第七篇主要看一下一下几系统表:PARAMETERS (定义参数),PARTITIONS(分区相关),PLUGINS(插件),PROCESSLIST(链接信息),PROFILING(优化相关,可以查看具体运行时间等信息) ,REFERENTIAL_CONSTRAINTS (外键约束相关信息),SCHEMATA(SCHEMA信息) ,SCHEMA_PRIVILEGES (mysql.db)
1:PARAMETERS
参数表存储了一些存储过程和方法的参数,以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。
INFORMATION_SCHEMA NameRemarks
SPECIFIC_CATALOGdef
SPECIFIC_SCHEMAroutine database
SPECIFIC_NAMEroutine name
ORDINAL_POSITION1, 2, 3, ... for parameters, 0 for function RETURNS clause
PARAMETER_MODEIN, OUT, INOUT (NULL for RETURNS)
PARAMETER_NAMEparameter name (NULL for RETURNS)
DATA_TYPEsame as for COLUMNS table
CHARACTER_MAXIMUM_LENGTHsame as for COLUMNS table
CHARACTER_OCTET_LENGTHsame as for COLUMNS table
NUMERIC_PRECISIONsame as for COLUMNS table
NUMERIC_SCALEsame as for COLUMNS table
DATETIME_PRECISIONsame as for COLUMNS table
CHARACTER_SET_NAMEsame as for COLUMNS table
COLLATION_NAMEsame as for COLUMNS table
DTD_IDENTIFIERsame as for COLUMNS table
ROUTINE_TYPEsame as for ROUTINES table
具体的列大部分和ROUTINES里面一致,,前面的系列已经讲过了,所以不过多介绍了。
2: PARTITIONS
顾名思义存储了MySQL分区表相关的信息,通过这张表我们可以查询到分区的相关信息,如下:
查看数据库中已分区的表:
select distinct TABLE_NAME from information_schema.PARTITIONS where TABLE_SCHEMA=qiandai and PARTITION_NAME is not null ;

查看每个分区表有多少个分区以及每个分区的行数:

select TABLE_SCHEMA, TABLE_NAME,PARTITION_NAME,TABLE_ROWS,PARTITION_EXPRESSION,PARTITION_METHOD,PARTITION_DESCRIPTION 

from information_schema.PARTITIONS where TABLE_SCHEMA=qiandai and TABLE_NAME=employees ;

3:PLUGINS

这个表存放的是系统相关插件信息,可以通过以下的语句查看
SHOW PLUGINS
技术分享
看到基本上是MySQL的插件信息,是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取道德数据。
4: PROCESSLIST
我们可以看到PROCESSLIST和show processlist里面的信息是一致的,显示当前的连接数
技术分享
show processlist其实就是从这个表拉取数据,PROCESSLIST的数据是他的基础。所以我们相当于在内存中查询一样,这些操作都是很快的。
5:PROFILING
这个表提供报表分析信息,可以通过SHOW PROFILES ;show PROFILE都可以提供相同的信息。
INFORMATION_SCHEMA NameSHOW Name
QUERY_IDQuery_ID
SEQ 
STATEStatus
DURATIONDuration
CPU_USERCPU_user
CPU_SYSTEMCPU_system
CONTEXT_VOLUNTARYContext_voluntary
CONTEXT_INVOLUNTARYContext_involuntary
BLOCK_OPS_INBlock_ops_in
BLOCK_OPS_OUTBlock_ops_out
MESSAGES_SENTMessages_sent
MESSAGES_RECEIVEDMessages_received
PAGE_FAULTS_MAJORPage_faults_major
PAGE_FAULTS_MINORPage_faults_minor
SWAPSSwaps
SOURCE_FUNCTIONSource_function
SOURCE_FILESource_file
SOURCE_LINESource_line
看一下列基本信息。
SHOW PROFILES和SHOW PROFILE的一些记录。
默认这个功能是关闭的,可以使用以下语句打开这个功能:
SET profiling = 1;

然后就可以看到这个会话记录的信息。

我们运行一个查询信息,
select * from qiandai.t1;

然后进行查询:

SHOW PROFILES;

就能够查看记录的信息了:

root@localhost [(none)]>SHOW PROFILES;+----------+------------+--------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------+| 1 | 0.00101925 | select * from qiandai.t1 |+----------+------------+--------------------------+1 row in set, 1 warning (0.00 sec)

能看到执行信息,还有使用的时间。

然后使用SHOW PROFILE可以看到具体的耗时,从这个查询开始到结束:
root@localhost [(none)]>SHOW PROFILE;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000219 || checking permissions | 0.000035 || checking permissions | 0.000016 || Opening tables | 0.000047 || init | 0.000101 || System lock | 0.000040 || optimizing | 0.000054 || statistics | 0.000083 || preparing | 0.000063 || executing | 0.000019 || Sending data | 0.000187 || end | 0.000021 || query end | 0.000023 || closing tables | 0.000023 || freeing items | 0.000040 || cleaning up | 0.000051 |+----------------------+----------+16 rows in set, 1 warning (0.00 sec)

SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能,不能查看和剖析其他连接的语句,以及剖析时所引起的消耗。

SHOW PROFILES显示最近发给服务器的多条语句,条数根据会话变量profiling_history_size定义,默认是15,最大值为100。设为0等价于关闭分析功能。
SHOW PROFILE FOR QUERY n,这里的n就是对应SHOW PROFILES输出中的Query_ID。
SHOW PROFILE CPU FOR query 1;其中类型是可以选择的,我们可以根据自己的需要进行查询。
1)ALL 显示所有性能信息(2)LOCK IO 显示块IO操作的次数(3)CONTEXT SWITCHES 显示上下文切换次数,不管是主动还是被动(4)CPU 显示用户CPU时间、系统CPU时间(5)IPC 显示发送和接收的消息数量(6)MEMORY [暂未实现](7)PAGE FAULTS 显示页错误数量(8)SOURCE 显示源码中的函数名称与位置(9)SWAPS 显示SWAP的次数

6:REFERENTIAL_CONSTRAINTS

这个表提供的外键相关的信息,而且只提供外键相关信息;可以通过TABLE_NAME进行筛选CONSTRAINT_SCHEMA 这个是外键约束名字
技术分享
7:SCHEMATA
这个表提供了实例下有多少个数据库,而且还有数据库默认的字符集:
技术分享
8:SCHEMA_PRIVILEGES
这个提供了数据库的相关权限,这个表是内存表是从mysql.db中拉去出来的。
select * from information_schema.SCHEMA_PRIVILEGES ;

技术分享

 

information_schema系列七