Server 层混杂信息字典表 | 全方位认识 information_schema(中)

kindule 2019-02-19

数据存储与数据库 mysql Server 字符集 存储 information_schema 字典表

上期《Server 层混杂信息字典表 | 全方位认识 information_schema(上)》为大家介绍了部分关于Server层混杂信息字典表的知识,本期“Server层混杂信息字典表(中)”将继续为大家介绍。


5、PROFILING

该表提供查询关于语句性能分析的信息。其记录内容对应于SHOW PROFILES和SHOW PROFILE语句产生的信息

  • 该表需要在会话变量 profiling=1时才会记录语句性能分析信息,否则该表不记录

  • 该表为Memory引擎临时表,注意:从MySQL 5.7.2开始,此表不再推荐使用,在未来的MySQL版本中删除。改用Performance Schema;代替

下面是该表中存储的信息内容

root@localhost : sbtest 04:47:17> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost : (none) 04:47:10> use sbtest
Database changed

root@localhost : sbtest 04:47:21> select min(id) from sbtest1;
+---------+
| min(id) |
+---------+
|      1 |
+---------+
1 row in set (0.00 sec)

root@localhost : sbtest 04:47:33> select * from information_schema.profiling;
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE                | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION      | SOURCE_FILE          | SOURCE_LINE |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
|
        2 |  2 | starting            | 0.000107 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | NULL                  | NULL                |        NULL |
|        2 |  3 | checking permissions | 0.000022 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | check_access          | sql_authorization.cc |        810 |
|
        2 |  4 | Opening tables      | 0.000080 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | open_tables          | sql_base.cc          |        5650 |
|        2 |  5 | init                | 0.000056 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | handle_query          | sql_select.cc        |        121 |
|
        2 |  6 | System lock          | 0.000021 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_lock_tables    | lock.cc              |        323 |
|        2 |  7 | optimizing          | 0.000057 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | optimize              | sql_optimizer.cc    |        151 |
|
        2 |  8 | executing            | 0.000007 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | exec                  | sql_executor.cc      |        119 |
|        2 |  9 end                  | 0.000003 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | handle_query          | sql_select.cc        |        199 |
|
        2 |  10 | query end            | 0.000023 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_execute_command | sql_parse.cc        |        4968 |
|        2 |  11 | closing tables      | 0.000016 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_execute_command | sql_parse.cc        |        5020 |
|
        2 |  12 | freeing items        | 0.000012 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_parse          | sql_parse.cc        |        5596 |
|        2 |  13 | cleaning up          | 0.00000| 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | dispatch_command      | sql_parse.cc        |        1902 |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
17 rows in set, 1 warning (0.00 sec)


字段含义如下:

  • QUERY_ID:执行的语句的数字标识符

  • SEQ:一个序列号,用于表示具有相同QUERY_ID值的语句记录行的显示顺序,

  • STATE:表示语句性能分析的状态(语句阶段)

  • DURATION:表示语句在性能分析中的某个给定状态下持续的时长(单位为秒)

  • CPU_USER和CPU_SYSTEM:表示用户CPU的us%和sy%的时间开销(单位为秒)

  • CONTEXT_VOLUNTARY和CONTEXT_INVOLUNTARY:表示发生了多少次自主或非自主的上下文切换

  • BLOCK_OPS_IN和BLOCK_OPS_OUT:表示块输入和输出的操作数量

  • MESSAGES_SENT和MESSAGES_RECEIVED:表示发送和接收的通信消息的数量

  • PAGE_FAULTS_MAJOR和PAGE_FAULTS_MINOR:表示主要和次要页面错误的数量

  • SWAPS:表示发生了多少次swap

  • SOURCE_FUNCTION、SOURCE_FILE和SOURCE_LINE:提供关于在源代码中执行的性能状态和位置信息

该表中的信息还可以使用show语句查询,如下:

# 需要使用set profiling=1;语句开启,该功能即将废弃,使用performance_schema中的阶段事件代替
## 启用
root@localhost : information_schema 02:30:20> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

## 列出查询语句的query id和语句文本等信息
root@localhost : sbtest 04:33:47> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration  | Query                      |
+----------+------------+-----------------------------+
|        1 | 0.00005125 | show warnings              |
|        2 | 0.00073950 | select * from profiling    |
|        3 | 0.00007150 | show warnings              |
|        4 | 0.00021025 | set profiling=1            |
|        5 | 0.00003250 | show warnings              |
|        6 | 0.00038300 | select * from profiling    |
|        7 | 0.00003625 | show warnings              |
|        8 | 0.00018250 | SELECT DATABASE()          |
|        9 | 0.08272525 | select max(id) from sbtest1 |
+----------+------------+-----------------------------+
9 rows in set, 1 warning (0.00 sec)

## 找到query id为9,查询该ID的性能分析数据
root@localhost : sbtest 04:33:53> show profile all for query 9;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status              | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function      | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
|
 starting            | 0.000089 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                1 |    0 | NULL                  | NULL                |        NULL |
| checking permissions | 0.000011 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | check_access          | sql_authorization.cc |        810 |
|
 Opening tables      | 0.082168 | 0.000000 |  0.081988 |                25 |                  2 |          128 |            0 |            0 |                0 |                0 |              800 |    0 | open_tables          | sql_base.cc          |        5650 |
| init                | 0.000173 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                10 |    0 | handle_query          | sql_select.cc        |        121 |
|
 System lock          | 0.000012 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_lock_tables    | lock.cc              |        323 |
| optimizing          | 0.00007| 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                2 |    0 | optimize              | sql_optimizer.cc    |        151 |
|
 executing            | 0.000010 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | exec                  | sql_executor.cc      |        119 |
end                  | 0.000003 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | handle_query          | sql_select.cc        |        199 |
|
 query end            | 0.000009 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_execute_command | sql_parse.cc        |        4968 |
| closing tables      | 0.00000| 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | mysql_execute_command | sql_parse.cc        |        5020 |
|
 freeing items        | 0.000150 | 0.000000 |  0.000000 |                0 |                  1 |            0 |            0 |            0 |                0 |                0 |                3 |    0 | mysql_parse          | sql_parse.cc        |        5596 |
| cleaning up          | 0.000013 | 0.000000 |  0.000000 |                0 |                  0 |            0 |            0 |            0 |                0 |                0 |                0 |    0 | dispatch_command      | sql_parse.cc        |        1902 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
12 rows in set, 1 warning (0.00 sec)


PS:关于show profiling语句,详见链接:https://dev.mysql.com/doc/refman/5.7/en/show-profile.html


6、CHARACTER_SETS

该表提供查询MySQL Server支持的可用字符集有哪些

  • 该表是Memory引擎临时表

下面是该表中存储的信息内容(MySQL 5.6版本支持40种字符集,5.7开始增加了gb18030 字符集)

admin@localhost : information_schema 03:31:08> select * from CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                    | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
............
|
 latin1            | latin1_swedish_ci    | cp1252 West European            |      1 |
| latin2            | latin2_general_ci    | ISO 8859-2 Central European    |      1 |
|
 swe7              | swe7_swedish_ci      | 7bit Swedish                    |      1 |
| ascii              | ascii_general_ci    | US ASCII                        |      1 |
............
|
 utf8              | utf8_general_ci      | UTF-8 Unicode                  |      3 |
............
| gb18030            | gb18030_chinese_ci  | China National Standard GB18030 |      4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)


字段含义如下:

  • CHARACTER_SET_NAME:字符集名称

  • DEFAULT_COLLATE_NAME:字符集对应的默认校对规则

  • DESCRIPTION:字符集描述信息,该字段为 "MySQL extension" 列

  • MAXLEN:字符集单个字符占用的最大字节数,该字段为 "MySQL extension" 列

该表中的信息还可以使用show语句查询,如下:

root@localhost : (none) 12:02:04> SHOW CHARACTER SET like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description  | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
|
 utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)


7、COLLATIONS

该表提供查询MySQL Server支持的可用校对规则有哪些

  • 该表是Memory引擎临时表

下面是该表中存储的信息内容(MySQL 5.6版本共219种校对规则,MySQL 5.7版本共222种,MySQL 8.0共270种)

admin@localhost : information_schema 03:31:58> select * from COLLATIONS limit 20;
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
|
 big5_chinese_ci  | big5              |  1 | Yes        | Yes        |      1 |
| big5_bin          | big5              | 84 |            | Yes        |      1 |
............
|
 koi8r_bin        | koi8r              | 74 |            | Yes        |      1 |
+-------------------+--------------------+----+------------+-------------+---------+
20 rows in set (0.01 sec)


字段含义如下:

  • COLLATION_NAME:校对规则名称

  • CHARACTER_SET_NAME:校对规则对应的字符集名称,该字段为 "MySQL extension" 列

  • ID:校对规则的ID号,该字段为 "MySQL extension" 列

  • IS_DEFAULT:是否是字符集的默认校对规则,该字段为 "MySQL extension" 列

  • IS_COMPILED:校对规则是否被编译进Server中,如果不为Yes,则表示并没有被编译到Server中,校对规则处于不可用状态,该字段为 "MySQL extension" 列

  • SORTLEN:最大排序字节长度,与字符集对应的字符串在排序时所占用的内存大小有关,该字段为 "MySQL extension" 列

该表中的内容还可以使用show语句查询,如下:

root@localhost : information_schema 12:26:23> show collation where COLLATION='utf8_general_ci';
+-----------------+---------+----+---------+----------+---------+
| Collation      | Charset | Id | Default | Compiled | Sortlen |
+-----------------+---------+----+---------+----------+---------+
|
 utf8_general_ci | utf8    | 33 | Yes    | Yes      |      1 |
+-----------------+---------+----+---------+----------+---------+
1 row in set (0.00 sec)


8、COLLATION_CHARACTER_SET_APPLICABILITY

该表提供查询MySQL Server中哪种字符集适用于什么校对规则。查询结果集相当于从SHOW COLLATION获得的结果集中的前两个字段值。该表其实并没有太大作用

  • 该表是Memory引擎临时表

下面是该表中存储的信息内容

admin@localhost : information_schema 03:32:28> select * from COLLATION_CHARACTER_SET_APPLICABILITY limit 20;
+-------------------+--------------------+
| COLLATION_NAME    | CHARACTER_SET_NAME |
+-------------------+--------------------+
............
|
 latin1_swedish_ci | latin1            |
| latin1_danish_ci  | latin1            |
|
 latin1_german2_ci | latin1            |
| latin1_bin        | latin1            |
|
 latin1_general_ci | latin1            |
| latin1_general_cs | latin1            |
|
 latin1_spanish_ci | latin1            |
| latin2_czech_cs  | latin2            |
|
 latin2_general_ci | latin2            |
+-------------------+--------------------+
20 rows in set (0.00 sec)


字段含义如下:

  • COLLATION_NAME:校对规则名称

  • CHARACTER_SET_NAME:校对规则对应的字符集名称


9、COLUMN_PRIVILEGES

该表提供查询关于列(字段)的权限信息,表中的内容来自mysql.column_priv列权限表(需要针对一个表的列单独授权之后才会有内容)

  • 该表是Memory引擎临时表

下面是该表中存储的信息内容

# 针对某个用户授予某表某列的SELECT,INSERT,UPDATE权限
root@localhost : information_schema 09:37:43> grant select(id),insert(id),update(id) on sbtest.sbtest1 to xx@'%' identified by 'xx';
Query OK, 0 rows affected, 1 warning (0.00 sec)

# select语句方式查询该表中的权限信息
root@localhost : information_schema 09:38:32> select * from COLUMN_PRIVILEGES;
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE  | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| 'xx'@'%' | def          | sbtest      | sbtest1    | id          | SELECT        | NO          |
| 'xx'@'%' | def          | sbtest      | sbtest1    | id          | INSERT        | NO          |
| 'xx'@'%' | def          | sbtest      | sbtest1    | id          | UPDATE        | NO          |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
3 rows in set (0.00 sec)


字段含义如下:

  • GRANTEE:PRIVILEGE_TYPE 列值的权限对应的授予者(账户名)

  • TABLE_CATALOG:该字段总是为def

  • TABLE_SCHEMA:PRIVILEGE_TYPE 列值的权限关联的表对应的库名

  • TABLE_NAME:PRIVILEGE_TYPE 列值的权限关联的表名

  • COLUMN_NAME:PRIVILEGE_TYPE 列值的权限关联的字段名

  • PRIVILEGE_TYPE:具体的列权限名称,注意:该字段值只显示一个权限名称,即,如果一个字段拥有多个可授予的列权限值,则在该表中会记录多行记录,每行PRIVILEGE_TYPE列值仅对应一个权限名称

  • IS_GRANTABLE:如果GRANTEE列值表示的授予者还同时拥有grant option权限,则该列值为YES,否则为NO

PS:该表中的信息还可以通过show语句方式查询(select和show方式虽然都能查询该表中的列权限信息,但是查询的结果展示方式有所不同)

# 语法
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

# 示例
root@localhost : information_schema 09:39:10> show grants for 'xx'@'%';
+-------------------------------------------------------------------------------+
| Grants for xx@%                                                              |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xx'@'%'                                                |
| GRANT SELECT (id), INSERT (id), UPDATE (id) ON `sbtest`.`sbtest1` TO 'xx'@'%' |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


限于篇幅,本期就先为大家介绍到这,下期《Server 层混杂信息字典表 | 全方位认识 information_schema(下)》将继续为大家介绍。

登录 后评论
下一篇
corcosa
16449人浏览
2019-10-08
相关推荐
MySQL8.0之数据字典
3621人浏览
2018-05-17 11:33:00
重新认识mysql基本知识
735人浏览
2018-07-06 10:15:58
重新认识mysql基本知识
729人浏览
2018-04-20 11:01:50
MySQL 8.0的新特性(二)
3961人浏览
2018-05-30 11:18:33
MySQL · myrocks · myrocks统计信息
1221人浏览
2017-03-08 10:50:24
MySQL 5.7 SYS SCHEMA
804人浏览
2017-08-23 09:44:11
了解一下SQL Server
619人浏览
2017-09-07 23:54:02
MySQL8.0之快速加列
2223人浏览
2019-08-23 17:29:24
0
0
0
1995