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

原标题：Server 层混杂信息字典表 | 全方位认识 information_schema（中）

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

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

该表提供查询关于语句性能分析的信息。其记录内容对应于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, 0rows affected, 1warning ( 0. 00sec)

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

Database changed

root@localhost : sbtest 04:47:21> select min(id) from sbtest1;

+———+

| min(id) |

+———+

| 1 |

+———+

1row inset ( 0. 00sec)

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. 000008 | 0.000000 |0. 000000| 0 |0| 0 |0| 0 |0| 0 |0| 0 |dispatch_command | sql_parse.cc |1902|

+———-+—–+———————-+———-+———-+————+——————-+———————+————–+—————+—————+——————-+——————-+——————-+——-+———————–+———————-+————-+

17 rows inset, 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, 0rows affected, 1warning ( 0. 00sec)

## 列出查询语句的query id和语句文本等信息

root@localhost : sbtest 04:33:47> show profiles;

+———-+————+—————————–+

| Query_ID |Duration | Query |

+———-+————+—————————–+

| 1 |0. 00005125| show warnings |

| 2 |0. 0007395 0| select * from profiling |

| 3 |0. 00007150| show warnings |

| 4 |0. 00021025| set profiling=1 |

| 5 |0. 00003250| show warnings |

| 6 |0. 0003830 0| select * from profiling |

| 7 |0. 00003625| show warnings |

| 8 |0. 0001825 0| SELECT DATABASE |

| 9 |0.08272525 | select max(id) from sbtest1 |

+———-+————+—————————–+

9rows inset, 1warning ( 0. 00sec)

## 找到query id为9，查询该ID的性能分析数据

root@localhost : sbtest 04:33:53> show profile all forquery 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. 000079 | 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. 000008 | 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 inset, 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 | DEION |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- 8Unicode | 3 |

…………

| gb18030 |gb18030_chinese_ci | China National Standard GB18030 |4|

+——————–+———————-+———————————+——–+

41 rows inset (0.00 sec)

字段含义如下：

  • CHARACTER_SET_NAME：字符集名称
  • DEFAULT_COLLATE_NAME：字符集对应的默认校对规则
  • DEION：字符集描述信息，该字段为 “MySQL extension” 列
  • MAXLEN：字符集单个字符占用的最大字节数，该字段为 “MySQL extension” 列

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

root@localhost : (none) 12:02:04> SHOW CHARACTER SET like ‘utf8%’;

+———+—————+——————–+——–+

| Charset |Deion | Default collation |Maxlen |

+———+—————+——————–+——–+

| utf8 | UTF-8 Unicode |utf8_general_ci | 3 |

| utf8mb4 |UTF- 8Unicode | utf8mb4_general_ci |4|

+———+—————+——————–+——–+

2 rows inset (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 |

+——————-+——————–+—-+————+————-+———+

20rows inset ( 0. 01sec)

字段含义如下：

  • 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 |

+—————–+———+—-+———+———-+———+

1row inset ( 0. 00sec)

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 |

+——————-+——————–+

20rows inset ( 0. 00sec)

字段含义如下：

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

该表提供查询关于列(字段)的权限信息，表中的内容来自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, 0rows affected, 1warning ( 0. 00sec)

# 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|

+———-+—————+————–+————+————-+—————-+————–+

3rows inset ( 0. 00sec)

字段含义如下：

  • 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 FORCURRENT_USER;

SHOW GRANTS FORCURRENT_USER;

# 示例

root@localhost : information_schema 09: 39: 10> show grants for‘xx’@’%’;

+——————————————————————————-+

| Grants forxx@% |

+——————————————————————————-+

| GRANT USAGE ON*.* TO‘xx’@’%’ |

| GRANT SELECT(id), INSERT (id), UPDATE (id) ON`sbtest`.`sbtest1` TO‘xx’@’%’ |

+——————————————————————————-+

2rows inset( 0.00sec)

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

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

Enjoy MySQL 🙂

责任编辑：

Thenews.cc