Server 层混杂信息字典表 | 全方位认识 information

2020-11-20 12:00:28 作者:浅色系遗忘  阅读:159 次  点赞:0 次  鄙视:0 次  收藏:0 次  由 www.3ctuan.cn 收集整理
听新闻 - Server 层混杂信息字典表 | 全方位认识 information
00:00 / 00:00

+

-

自动播放×

御姐音

大叔音

萝莉音

型男音

温馨提示:
是否自动播放下一条内容?
立即播放当前内容?
确定
确定
取消

上期 《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, 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:校对规则对应的字符集名称
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, 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(下)》将继续为大家介绍。

Enjoy MySQL :)返回搜狐,查看更多

本文关键词:字段 , 语句 , 字符集

相关文章

X

3C团-上所有的内容均由网友收集整理,纯属个人爱好并供广大网友交流学习之用,作品版权均为原版权人所有。
如果版权所有人认为在本站放置您的作品会损害您的利益,请指出,本站在核实之后会立即删除。QQ:22359512
Copyright 2006-2019 3ctuan.cn Inc. all Rights Reserved