其他
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 字符集）
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（下）》将继续为大家介绍。
| 作者简介
罗小波·沃趣科技高级数据库技术专家
IT从业多年，主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写，熟悉MySQL体系结构，Innodb存储引擎，喜好专研开源技术，多次在公开场合做过线下线上数据库专题分享，发表过多篇数据库相关的研究文章。
点击查看招聘信息
相关链接
更多干货，欢迎来撩~