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

r囧r小猫 2022-12-23 00:39 93阅读 0赞

> **上期[《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.000008 | 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.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 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 字符集）

字段含义如下：

*  CHARACTER\_SET\_NAME：字符集名称
 *  DEFAULT\_COLLATE\_NAME：字符集对应的默认校对规则
 *  DESCRIPTION：字符集描述信息，该字段为 "MySQL extension" 列
 *  MAXLEN：字符集单个字符占用的最大字节数，该字段为 "MySQL extension" 列

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

### **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引擎临时表

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

字段含义如下：

*  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（下）》将继续为大家介绍。**

**| 作者简介**

### **罗小波·数据库技术专家** ###

《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。

全文完。

Enjoy MySQL :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png][]

[Server _ _ _ information_schema]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA%3D%3D&chksm=fb242dabcc53a4bd75a78353af50861dff389f33cd69ee04b97b66abd965fe80b7885ec1b9e4&idx=1&mid=2247484582&scene=21&sn=e5409a791ed247828cf4c51e137cd3f5#wechat_redirect
[format_png]: /images/20221120/e7e76201eff5491285d3aa8c2824a64b.png