一台虚拟主机能建设几个网站,怎样设计网站主页,乐陵市seo关键词优化,专业的营销型网站企业文化一、information_schema简介
在MySQL中#xff0c;把 information_schema 看作是一个数据库#xff0c;确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名#xff0c;数据库的表#xff0c;表栏的数据类型与访问权 限等。在INFORMA…一、information_schema简介
在MySQL中把 information_schema 看作是一个数据库确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名数据库的表表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中有数个只读表。它们实际上是视图而不是基本表因此你将无法看到与之相关的任何文件。
mysql use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
---------------------------------------
| Tables_in_information_schema |
---------------------------------------
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
---------------------------------------
61 rows in set (0.00 sec)二、information_schema数据库表说明:
SCHEMATA表提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。TABLES表提供了关于数据库中的表的信息包括视图。详细表述了某个表属于哪个schema表类型表引擎创建时间等信息。是show tables from schemaname的结果取之此表。COLUMNS表提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。STATISTICS表提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。USER_PRIVILEGES用户权限表给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。SCHEMA_PRIVILEGES方案权限表给出了关于方案数据库权限的信息。该信息来自mysql.db授权表。是非标准表。TABLE_PRIVILEGES表权限表给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。COLUMN_PRIVILEGES列权限表给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。CHARACTER_SETS字符集表提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。COLLATIONS表提供了关于各字符集的对照信息。COLLATION_CHARACTER_SET_APPLICABILITY表指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。TABLE_CONSTRAINTS表描述了存在约束的表。以及表的约束类型。KEY_COLUMN_USAGE表描述了具有约束的键列。ROUTINES表提供了关于存储子程序存储程序和函数的信息。此时ROUTINES表不包含自定义函数UDF。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。VIEWS表给出了关于数据库中的视图的信息。需要有show views权限否则无法查看视图信息。TRIGGERS表提供了关于触发程序的信息。必须有super权限才能查看该表
三、按类别划分
1关于字符集和排序规则相关的系统表
CHARACTER_SETS 存储数据库相关字符集信息memory存储引擎
COLLATIONS 字符集对应的排序规则
COLLATION_CHARACTER_SET_APPLICABILITY就是一个字符集和连线校对的一个对应关系而已
下面我们说一下character sets和collations的区别
字符集character sets存储字符串是指人类语言中最小的表义符号。例如’A、’B等
排序规则collations规则比较字符串collations是指在同一字符集内字符之间的比较规则
每个字符序唯一对应一种字符集但一个字符集可以对应多种字符序其中有一个是默认字符序(Default Collation) MySQL中的字符序名称遵从命名惯例以字符序对应的字符集名称开头以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如在字符序“utf8_general_ci”下字符“a”和“A”是等价的
看一下有关于字符集和校对相关的MySQL变量
character_set_server默认的内部操作字符集
character_set_client客户端来源数据使用的字符集
character_set_connection连接层字符集
character_set_results查询结果字符集
character_set_database当前选中数据库的默认字符集
character_set_system系统元数据(字段名等)字符集
再看一下MySQL中的字符集转换过程
1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection
2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集其确定方法如下
使用每个数据字段的CHARACTER SET设定值
若上述值不存在则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展非SQL标准)
若上述值不存在则使用对应数据库的DEFAULT CHARACTER SET设定值
若上述值不存在则使用character_set_server设定值。
3. 将操作结果从内部操作字符集转换为character_set_results。
2权限相关的一些表
SCHEMA_PRIVILEGES提供了数据库的相关权限这个表是内存表是从mysql.db中拉去出来的。
TABLE_PRIVILEGES:提供的是表权限相关信息信息是从 mysql.tables_priv 表中加载的
COLUMN_PRIVILEGES 这个表可以清楚就能看到表授权的用户的对象那张表那个库以及授予的是什么权限如果授权的时候加上with grant option的话我们可以看得到PRIVILEGE_TYPE这个值必须是YES。
USER_PRIVILEGES:提供的是表权限相关信息信息是从 mysql.user 表中加载的
通过表我们可以很清晰看得到MySQL授权的层次SCHEMATABLECOLUMN级别当然这些都是基于用户来授予的。可以看得到MySQL的授权也是相当的细密的可以具体到列这在某一些应用场景下还是很有用的比如审计等。
3存储数据库系统的实体对象的一些表
COLUMNS存储表的字段信息所有的存储引擎
INNODB_SYS_COLUMNS 存放的是INNODB的元数据 他是依赖于SYS_COLUMNS这个统计表而存在的。
ENGINES 引擎类型是否支持这个引擎描述是否支持事物是否支持分布式事务是否能够支持事物的回滚点
EVENTS 记录MySQL中的事件类似于定时作业
FILES 这张表提供了有关在MySQL的表空间中的数据存储的文件的信息文件存储的位置这个表的数据是从InnoDB in-memory中拉取出来的所以说这张表本身也是一个内存表每次重启重新进行拉取。也就是我们下面要说的INNODB_SYS_DATAFILES这张表。还要注意一点的是这张表包含有临时表的信息所以说和SYS_DATAFILES 这张表是不能够对等的还是要从INNODB_SYS_DATAFILES看。如果undo表空间也配置是InnoDB 的话那么也是会被记录下来的。
PARAMETERS 参数表存储了一些存储过程和方法的参数以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。
PLUGINS 基本上是MySQL的插件信息是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取道德数据
ROUTINES关于存储过程和方法function的一些信息不过这个信息是不包括用户自定义的只是系统的一些信息。
SCHEMATA这个表提供了实例下有多少个数据库而且还有数据库默认的字符集
TRIGGERS :这个表记录的就是触发器的信息包括所有的相关的信息。系统的和自己用户创建的触发器。
VIEWS :视图的信息也是系统的和用户的基本视图信息。
这些表存储的都是一些数据库的实体对象方便我们进行查询和管理对于一个DBA来说这些表能够大大方便我们的工作更快更方便的了结和查询数据库的相关信息。
4约束外键等相关的一些表
REFERENTIAL_CONSTRAINTS这个表提供的外键相关的信息而且只提供外键相关信息
TABLE_CONSTRAINTS 这个表提供的是 相关的约束信息
INNODB_SYS_FOREIGN_COLS 这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的
INNODB_SYS_FOREIGN 存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的只不过是单独对于INNODB来说的
KEY_COLUMN_USAGE数据库中所有有约束的列都会存下下来也会记录下约束的名字和类别
为什么要把外键和约束单列出来呢因为感觉这是一块独立的东西虽然我们的生产环境大部分都不会使用外键因为这会降低性能但是合理的利用约束还是一个不错的选择比如唯一约束。
5关于管理的一些的一些表
GLOBAL_STATUS GLOBAL_VARIABLESSESSION_STATUSSESSION_VARIABLES这四张表分别记录了系统的变量状态全局和会话的信息作为DBA相信大家也都比较熟悉了而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。
PARTITIONS MySQL分区表相关的信息通过这张表我们可以查询到分区的相关信息数据库中已分区的表以及分区表的分区和每个分区的数据信息分区相关详细信息参见MySQL分区管理
PROCESSLISTshow processlist其实就是从这个表拉取数据PROCESSLIST的数据是他的基础。由于是一个内存表所以我们相当于在内存中查询一样这些操作都是很快的。
INNODB_CMP_PER_INDEXINNODB_CMP_PER_INDEX_RESET这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.我们知道对于一个INNODB压缩表来说,不管是数据还是二级索引都是会被压缩的,因为数据本身也可以看作是一个聚集索引。关于压缩表在information_schema系列十一有些许简单的介绍。
INNODB_CMPMEM INNODB_CMPMEM_RESET这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。如果要打开这个功能的话我们要设置innodb_cmp_per_index_enabled参数为ON状态。
INNODB_BUFFER_POOL_STATS 表提供有关INNODB 的buffer pool相关信息和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
INNODB_BUFFER_PAGE_LRUINNODB_BUFFER_PAGE :维护了INNODB LRU LIST的相关信息详细请见小编笔记innodb buffer pool小解
INNODB_BUFFER_PAGE 这个表就比较屌了存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响千万不要再我们自己的生产库上面执行这个语句除非你能接受服务短暂的停顿详细请见小编笔记innodb buffer pool小解
INNODB_SYS_DATAFILES 这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)
INNODB_TEMP_TABLE_INFO 这个表惠记录所有的INNODB的所有用户使用到的信息但是只能记录在内存中和没有持久化的信息。
INNODB_METRICS 提供INNODB的各种的性能指数是对INFORMATION_SCHEMA的补充收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
INNODB_SYS_VIRTUAL :表存储的是INNODB表的虚拟列的信息当然这个还是比较简单的在MySQL 5.7中支持两种Generated Column即Virtual Generated Column和Stored Generated Column前者只将Generated Column保存在数据字典中表的元数据并不会将这一列数据持久化到磁盘上后者会将Generated Column持久化到磁盘上而不是每次读取的时候计算所得。很明显后者存放了可以通过已有数据计算而得的数据需要更多的磁盘空间与实际存储一列数据相比并没有优势因此MySQL 5.7中不指定Generated Column的类型默认是Virtual Column。
INNODB_CMPINNODB_CMP_RESET存储的是关于压缩INNODB信息表的时候的相关信息详细请见推荐笔记。
为什么把这些表列为管理相关的表呢因为我感觉像连接分区压缩表innodb buffer pool等表我们通过这些表都能很清晰的看到自己数据库的相关功能的状态特别是我们通过一些变量更容易窥透MySQL的运行状态方便我们进行管理。相关笔记有推荐innodb buffer pool小解MySQL分区管理information_schema系列十一。都是小编自己的笔记。
6关于表信息和索引信息的一些表
TABLESTABLESPACESINNODB_SYS_TABLES INNODB_SYS_TABLESPACES
TABLES这张表毫无疑问了就是记录的数据库中表的信息其中包括系统数据库和用户创建的数据库。show table status like test1\G的来源就是这个表
TABLESPACES 却是标注的活跃表空间。 这个表是不提供关于innodb的表空间信息的对于我们来说并没有太大作用因为我们生产库是强制INNODB的
INNODB_SYS_TABLES 这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性包括行格式压缩页面大小位级别的信息如适用
提供的是关于INNODB的表空间信息其实和SYS_TABLESPACES 中的INNODB信息是一致的。
STATISTICS这个表提供的是关于表的索引信息所有索引的相关信息。
INNODB_SYS_INDEXES提供相关INNODB表的索引的相关信息和SYS_INDEXES 这个表存储的信息基本是一样的只不过后者提供的是所有存储引擎的索引信息后者只提供INNODB表的索引信息。
INNODB_SYS_TABLESTATS
这个表就比较重要了记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息其实就是MySQL数据库的统计信息
这个表的记录是记录在内存当中的是一个内存表每次重启后就会重新记录所以只能记录从上次重启后的数据库统计信息。有了这个表我们对于索引的维护就更加方便了我们可以查询索引的使用次数方便清理删除不常用的索引提高表的更新插入等效率节省磁盘空间。
INNODB_SYS_FIELDS 这个表记录的是INNODB的表索引字段信息以及字段的排名
INNODB_FT_CONFIG :这张表存的是全文索引的信息
INNODB_FT_DEFAULT_STOPWORD这个表存放的是stopword 的信息,是和全文索引匹配起来使用的和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的这个STOPWORD必须是在创建索引之前创建而且必须指定字段为varchar。stopword 也就是我们所说的停止词全文检索时停止词列表将会被读取和检索在不同的字符集和排序方式下会造成命中失败或者找不到此数据这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。
INNODB_FT_INDEX_TABLE这个表存储的是关于INNODB表有全文索引的索引使用信息的同样这个表也是要设置innodb_ft_aux_table以后才能够使用的一般情况下是空的
INNODB_FT_INDEX_CACHE 这张表存放的是插入前的记录信息也是为了避免DML时候昂贵的索引重组
7关于MySQL优化相关的一些表
OPTIMIZER_TRACE 提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试MySQL追踪优化器小试
PROFILINGSHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能不能查看和剖析其他连接的语句以及剖析时所引起的消耗。
SHOW PROFILES显示最近发给服务器的多条语句条数根据会话变量profiling_history_size定义默认是15最大值为100。设为0等价于关闭分析功能。详细信息请见MySQL profile
INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。详细信息详见我的OPTIMIZE TABLE 小解
8关于MySQL事物和锁的相关的一些表
INNODB_LOCKS:现在获取的锁但是不含没有获取的锁而且只是针对INNODB的。
INNODB_LOCK_WAITS系统锁等待相关信息包含了阻塞的一行或者多行的记录而且还有锁请求和被阻塞改请求的锁信息等。
INNODB_TRX包含了所有正在执行的的事物相关信息INNODB而且包含了事物是否被阻塞或者请求锁。
我们通过这些表就能够很方便的查询出来未结束的事物和被阻塞的进程这是不是更方便了详细可见information_schema系列八(事物锁)
