十分钟了结MySQL,MySQL怎么样辨别InnoDB表是独立表空间依旧共享表空间

 

 

十分钟了结MySQL information_schema,informationschema

information_schema数据库是MySQL系统自带的数据库,它提供了数据库元数据的走访方式。感觉information_schema就如MySQL实例的一个百科全书,记录了数据库当中超过半数我们必要收尾的新闻,比如字符集,权限相关,数据库实体对象新闻,外检约束,分区,压缩表,表新闻,索引音信,参数,优化,锁和东西等等。通过information_schema大家可以窥透整个MySQL实例的周转意况,可以了结MySQL实例的为主信息,甚至优化调优,维护数据库等,可以说是当真的一部百科全书啊哈哈。
下边就是基于自己攻读的局地细微经验将这一个表举行一个大约的归类,方便大家了结,若是有不足的地点请提出,我会尽快修改。
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授权的层系,SCHEMA,TABLE,COLUMN级别,当然这一个都是基于用户来予以的。可以看得到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_VARIABLES,SESSION_STATUS,SESSION_VARIABLES:那四张表分别记录了系统的变量,状态(全局和对话的音讯),作为DBA相信大家也都比较熟稔了,而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。
PARTITIONS
:MySQL分区表相关的音信,通过那张表大家得以查询到分区的有关音讯(数据库中已分区的表,以及分区表的分区和每个分区的数码新闻),分区相关详细音讯参见MySQL分区管理
PROCESSLIST:show
processlist其实就是从那几个表拉取多少,PROCESSLIST的多少是他的基本功。由于是一个内存表,所以大家一定于在内存中询问同一,这几个操作都是高效的。
INNODB_CMP_PER_INDEX,INNODB_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_LRU,INNODB_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_CMP,INNODB_CMP_RESET:存储的是关于压缩INNODB音讯表的时候的相关音讯,详细请见推荐笔记。
为啥把那一个表列为管理相关的表呢,因为我感到像连接,分区,压缩表,innodb
buffer
pool等表,大家透过这个表都能很清楚的来看自己数据库的相关职能的场馆,越发是我们通过有些变量更便于窥透MySQL的运行情形,方便大家实行管制。相关笔记有推荐innodb
buffer
pool小解,MySQL分区军事管制,information_schema体系十一。都是小编自己的笔记。
6:关于表信息和目录信息的局地表
TABLES,TABLESPACES,INNODB_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数据库的统计信息那几个表的记录是记录在内存当中的,是一个内存表,每一趟重启后就会再也记录,所以只好记录从上次重启后的数据库计算信息。有了那么些表,我们对于索引的吝惜就更为有益于了,大家得以查询索引的利用次数,方便清理删除不常用的目录,升高表的换代插入等成效,节省磁盘空间。
INNODB_SYS_FIELDS
:这么些表记录的是INNODB的表索引字段音讯,以及字段的排名INNODB_FT_CONFIG :那张表存的是全文索引的音信INNODB_FT_DEFAULT_STOPWORD:这么些表存放的是stopword 的信息,是和全文索引匹配起来使用的,和innodb的
INFORMATION_SCHEMA.INNODB_十分钟了结MySQL,MySQL怎么样辨别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追踪优化器小试
PROFILING:SHOW
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序列八(事物,锁)
 
最后只要大家有趣味的话,欢迎看我的小说information_schema系列,多多提提意见。如若感觉到值得鼓励,请点右下角推荐

information_schema,informationschema
information_schema数据库是MySQL系统自带的数据库,它提供了数据库元数据的走访方式。感觉info…

十分钟了结MySQL information_schema

 原文:

information_schema数据库是MySQL系统自带的数据库,它提供了数据库元数据的拜访情势。感觉information_schema就像MySQL实例的一个百科全书,记录了数据库当中半数以上大家须求收尾的新闻,比如字符集,权限相关,数据库实体对象消息,外检约束,分区,压缩表,表音信,索引新闻,参数,优化,锁和东西等等。通过information_schema大家得以窥透整个MySQL实例的运转处境,可以了结MySQL实例的为主音讯,甚至优化调优,维护数据库等,可以说是真正的一部百科全书啊哈哈。

下边就是按照自己上学的有的很小经验将那一个表展开一个大体的分类,方便我们了结,借使有欠缺的地点请提出,我会尽快修改。

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授权的层系,SCHEMA,TABLE,COLUMN级别,当然这个都是依照用户来予以的。可以看得到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_997755.com澳门葡京,SYS_FOREIGN :存储的INNODB关于外键的元数据音信和SYS_FOREIGN_COLS 存储的音信是一致的,只但是是独立对于INNODB来说的

KEY_COLUMN_USAGE:数据库中所有有约束的列都会存下下来,也会记录下约束的名字和体系

为啥要把外键和自律单列出来啊,因为感觉那是一块独立的东西,即使大家的生产环境大部分都不会使用外键,因为那会回落品质,不过合理的施用约束仍然一个不利的挑三拣四,比如唯一约束。

5:关于管理的部分的有些表:

GLOBAL_STATUS
,GLOBAL_VARIABLES,SESSION_STATUS,SESSION_VARIABLES:那四张表分别记录了系统的变量,状态(全局和对话的新闻),作为DBA相信大家也都比较熟知了,而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。

PARTITIONS :MySQL分区表相关的新闻,通过那张表大家可以查询到分区的连带新闻(数据库中已分区的表,以及分区表的分区和每个分区的多寡新闻),分区相关详细音讯参见MySQL分区军事管制

PROCESSLIST:show
processlist其实就是从那些表拉取多少,PROCESSLIST的数据是他的基础。由于是一个内存表,所以大家一定于在内存中询问同一,那个操作都是便捷的。

INNODB_CMP_PER_INDEX,INNODB_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_LRU,INNODB_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_CMP,INNODB_CMP_RESET:存储的是关于压缩INNODB音讯表的时候的相干音讯,详细请见推荐笔记。

怎么把那么些表列为管理相关的表呢,因为我感觉到像连接,分区,压缩表,innodb
buffer
pool等表,大家透过这几个表都能很清楚的来看自己数据库的相干作用的情形,越发是大家经过一些变量更便于窥透MySQL的运作情状,方便大家进行管制。相关笔记有推荐innodb
buffer
pool小解,MySQL分区管理,information_schema种类十一。都是小编自己的笔记。

6:关于表新闻和目录新闻的部分表

TABLES,TABLESPACES,INNODB_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追踪优化器小试

PROFILING:SHOW
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),而且蕴藏了事物是或不是被封堵或者请求锁。

 InnoDB选择按表空间(tablespace)的点子开展仓储数据,
默许配置情状下会有一个起来大小为10MB, 名字为ibdata1的文本,
该公文就是默许的表空间文件(tablespce
file),用户可以通过参数innodb_data_file_path对其举办安装,可以有四个数据文件,假使没有设置innodb_file_per_table的话,
那些Innodb存储类型的表的多寡都置身这几个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会时有发生一个独立表空间,独立表空间的命名规则为:表名.idb.
这一个单独的表空间文件仅存储该表的数量、索引和插入缓冲BITMAP等音讯,此外音讯或者存放在共享表空间中,那么如何分辨数据库中哪些表是独立表空间,哪些表是共享表空间吗?

今天赶上一个关于MySQL求助的难题,修改表结构时相遇“ERROR
1050 (42S01): table xxx already exits”

 

 

 

mysql>
ALTER TABLE DAY_BOOK_REPORT  ADD
UNIT_PRICE_PCS  
DOUBLE(12,2) DEFAULT NULL;

 

ERROR
1050 (42S01): TABLE ‘INVGSP/#SQL-IB379’ ALREADY EXISTS

主意1:通过ibd文件判别

mysql>

 

 

 

自我批评了后,发现表DAY_BOOK_REPORT确实不设有字段UNIT_PRICE_PCS,可是给表加字段时就报这些错误,遂咨询了刹那间他具体的操作进程,反馈是及时在做大量数码更新,然后给那个表扩展字段时,突然报“DB
connect fail”,
登录MySQL服务器检查发现MySQL服务一度挂了,MySQL版本为5.6.20-enterprise-commercial-advanced-log,检查错误日志,发现有上边错误消息:

设若表的储存引擎是InnoDB,而且表空间(tablespace)是共享表空间的话,那么数据库对应目录上边是从未有过”表名.ibd”文件的。独立表空间的表的话,则有”表名.ibd”文件。只是这些艺术很笨,对于生产条件,大批量的表经过那种艺术判别,确实不是一个好点子。

 

 

2018-03-31 23:29:16 7f09c1830700 InnoDB: Error: Write to file ./INVOICE/#sql-ib379.ibd failed at offset 600834048.

InnoDB: 1048576 bytes should have been written, only 446464 were written.

InnoDB: Operating system error number 0.

InnoDB: Check that your OS and file system support files of this size.

InnoDB: Check also that the disk is not full or a disk quota exceeded.

InnoDB: Error number 0 means 'Success'.

InnoDB: Some operating system error numbers are described at

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

15:29:16 UTC - mysqld got signal 11 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

 

key_buffer_size=8388608

read_buffer_size=131072

max_used_connections=120

max_threads=151

thread_count=6

connection_count=6

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68245 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

 

Thread pointer: 0x9ac95e0

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 7f09c182fe10 thread_stack 0x40000

/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x946155]

/usr/sbin/mysqld(handle_fatal_signal+0x3d8)[0x6a58c8]

/lib64/libpthread.so.0[0x3a6b60f710]

/usr/sbin/mysqld[0xa45a2b]

/usr/sbin/mysqld[0xa50f5a]

/usr/sbin/mysqld[0x9e1afd]

/usr/sbin/mysqld[0x9e55a5]

/usr/sbin/mysqld[0x96aec5]

/usr/sbin/mysqld[0x7790a5]

/usr/sbin/mysqld(_Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP10TABLE_LISTP10Alter_infojP8st_orderb+0x1e54)[0x77b204]

/usr/sbin/mysqld(_ZN19Sql_cmd_alter_table7executeEP3THD+0x4a5)[0x87fab5]

/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3d4f)[0x72aa4f]

/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x318)[0x72de48]

/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x11b6)[0x72f7f6]

/usr/sbin/mysqld(_Z10do_commandP3THD+0xd7)[0x7310a7]

/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x116)[0x6f8856]

/usr/sbin/mysqld(handle_one_connection+0x45)[0x6f8935]

/usr/sbin/mysqld(pfs_spawn_thread+0x126)[0xb153e6]

/lib64/libpthread.so.0[0x3a6b6079d1]

/lib64/libc.so.6(clone+0x6d)[0x3a6b2e89dd]

 

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (7f095e93b2e0): is an invalid pointer

Connection ID (thread ID): 4237691

Status: NOT_KILLED
mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.01 sec)

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table Independent_tablespace(name  varchar(64));

Query OK, 0 rows affected (0.03 sec)

 

mysql> exit

 

[root@DB-Server ~]# cd /data/mysql/MyDB/

[root@DB-Server MyDB]# ls -lrt Independent_tablespace*

-rw-rw---- 1 mysql mysql  8560 Aug 21 22:05 Independent_tablespace.frm

-rw-rw---- 1 mysql mysql 98304 Aug 21 22:05 Independent_tablespace.ibd

[root@DB-Server MyDB]# 

 

 

 

在安排文件my.cnf里面安装innodb_file_per_table=0,重启MySQL服务,创建表common_tablespace,你会在数量目录看到只有common_tablespace.frm文件。

从漏洞百出提醒看,MySQL在往./INVGSP/#sql-ib379.ibd文件写入数据时,碰到了错误,可是最终写入成功(InnoDB:
Operating system error number
0.),按错误日志里面的信息提醒排查难题:

 

 

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF   |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table common_tablespace(name varchar(64));

Query OK, 0 rows affected (0.02 sec)

 

mysql> exit

Bye

[root@DB-Server MyDB]# ls -lrt common_tablespace*

-rw-rw---- 1 mysql mysql 8560 Aug 21 22:08 common_tablespace.frm

[root@DB-Server MyDB]# 

InnoDB:
Check that your OS and file system support files of this size.

 

InnoDB:
Check also that the disk is not full or a disk quota exceeded.

 

 

方法2:使用INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES来判别。

末尾检查发现MySQL数据文件所在的分区已经爆了,看错误提示,很有可能是因为空间难题,导致MySQL进程Crash掉了,而MySQL在ALTER
TABLE操作进程中垮台,那么最后可能会在InnoDB表空间中生成一个孤立的中间表(orphaned
intermediate table)。
其实#sql-ib379.ibd就是在修改DAY_BOOK_REPORT时,由于MySQL进度Crash掉后转变的孤立中间表。检查如下所示:

 

 

 

mysql> show variables like '%innodb_file_per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

+----------+--------------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME               | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+--------------------+------+--------+-------+-------------+------------+---------------+

|      650 | INVOICE/#sql-ib379 |    1 |     65 |   636 | Antelope    | Compact    |             0 |

+----------+--------------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.04 sec)

 

mysql>

MySQL
5.6

 

 

997755.com澳门葡京 1

MySQL
5.6 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
关于那么些系统表提供了有关表格的格式和储存特性,包蕴行格式,压缩页面大小位级其余信息(如适用),INNODB的表空间音讯。

 

 

 

The
INNODB_SYS_TABLESPACES
table provides metadata about InnoDB tablespaces, equivalent to the
information in the SYS_TABLESPACES table in the InnoDB data
dictionary.

合法文档

 

 

  
花了点时间领悟了一晃INFORMATION_SCHEMA数据库上边的INNODB_SYS_TABLESPACES这么些表,遂写了一个SQL来判定那么些InnoDB引擎表是独立表空照旧共享表空间

Orphan
Intermediate Tables

 

 

共享表空间:

If
MySQL exits in the middle of an in-place ALTER TABLE operation
(ALGORITHM=INPLACE), you may be left with an orphan intermediate table
that takes up space on your system. This section describes how to
identify and remove orphan intermediate tables.

 

Intermediate
table names begin with an #sql-ib prefix (e.g., #sql-ib87-856498050).
The accompanying .frm file has an #sql-* prefix and is named
differently (e.g., #sql-36ab_2.frm).

 

SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_TYPE

    ,N'共享表空间' AS TABLE_SPACE

    ,ENGINE

    ,VERSION

    ,TABLE_ROWS

    ,AVG_ROW_LENGTH

    ,CREATE_TIME

    ,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES  T

LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='MyDB' AND T.ENGINE='InnoDB';

To
identify orphan intermediate tables on your system, you can view Table
Monitor output or query INFORMATION_SCHEMA.INNODB_SYS_TABLES.
Look for table names that begin with #sql. If the original table
resides in a file-per-table
tablespace, the tablespace file (the #sql-*.ibd file) for the orphan
intermediate table should be visible in the database directory.

 

 

997755.com澳门葡京 2

 

 

找到相应的frm文件(那里是#sql-71a_40a97b.frm
),然后将其取名为#sql-ib379.frm(数据文件为#sql-ib379.ibd),
然后删除表(对应的文本会去除)即可缓解地方这一个题材。

可是那些本子有个小小的bug,对于富含特殊字符的表名,有可能出现谬误景况,那么些是因为假诺表名包蕴特殊字符,那么文件名或INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中的NAME做了转义处理,如下所示

 

 

#
mv “#sql-71a_40a97b.frm” “#sql-ib379.frm”

997755.com澳门葡京 3

 

 

mysql>  DROP TABLE
`#mysql50##sql-ib379`

独立表空间

    -> ;

 

Query
OK, 0 rows affected (0.11 sec)

 

SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_TYPE

    ,N'独立表空间' AS TABLE_SPACE

    ,ENGINE

    ,VERSION

    ,TABLE_ROWS

    ,AVG_ROW_LENGTH

    ,CREATE_TIME

    ,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES  T

INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE T.TABLE_SCHEMA='MyDB'  AND T.ENGINE='InnoDB';

 

 

mysql>  SELECT * FROM
INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE
‘%#sql%’;

997755.com澳门葡京 4

Empty
set (0.01 sec)

 

 

 

mysql>

方法3 :INFORMATION_SCHEMA.INNODB_SYS_TABLES判别

 

 

 

 

私家还测试了网上此外一种艺术,就是首先先删除#sql伊始的这个文件,然后拷贝源表数据到备份表,接着删除原表,最终将备份表重命名为源表。添加相关索引。那种措施也能化解这么些难点。

MySQL
5.7

 

 

mysql> show index from DAY_BOOK_REPORT;

 

mysql> create table DAY_BOOK_REPORT_BK as select * from DAY_BOOK_REPORT;

 

mysql> drop table DAY_BOOK_REPORT;

 

mysql> rename table DAY_BOOK_REPORT_BK to DAY_BOOK_REPORT;

 

mysql>ALTER TABLE DAY_BOOK_REPORT ADD INDEX INDEX_NAME (column_list) --根据实际情况输入具体字段

 

mysql>ALTER TABLE DAY_BOOK_REPORT ADD UNIQUE (column_list) --根据实际情况输入具体字段

 

mysql>ALTER TABLE DAY_BOOK_REPORT ADD PRIMARY KEY (column_list) --根据实际情况输入具体字段

     假使是MySQL
5.7的话, 比MySQL 5.6有多了一种办法,MySQL 5.7
的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中多了SPACE_TYPE字段,不过其值全体为Single,而INFORMATION_SCHEMA.INNODB_SYS_TABLES中也多了字段SPACE_TYPE,
其值有Single与System 分别代表单独表空间和共享表空间。

 

 

参考资料:

 

 

#独自表空间

  

 

SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES

WHERE SPACE_TYPE='Single';

 

 

 

SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_TYPE

    ,N'独立表空间' AS TABLE_SPACE

    ,ENGINE

    ,VERSION

    ,TABLE_ROWS

    ,AVG_ROW_LENGTH

    ,CREATE_TIME

    ,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES  T

INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE T.TABLE_SCHEMA='YourDatabase'  AND T.ENGINE='InnoDB';

 

#共享表空间

 

SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES

WHERE SPACE_TYPE='System';

 

 

   

SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_TYPE

    ,N'共享表空间' AS TABLE_SPACE

    ,ENGINE

    ,VERSION

    ,TABLE_ROWS

    ,AVG_ROW_LENGTH

    ,CREATE_TIME

    ,UPDATE_TIME

FROM INFORMATION_SCHEMA.TABLES  T

LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME

WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='YourDatabase' AND T.ENGINE='InnoDB'

 

 

 

 

方法4:
INFORMATION_SCHEMA.INNODB_TABLES判别

 

 

MySQL
8.0

 

假定是MySLQ
8.0的话,还多了一种办法,
那就是透过INFORMATION_SCHEMA.INNODB_TABLES来判断,那些新增的系统表可以经过SPACE_TYPE字段来分别共享表空间与独立表空间

 

SELECT
* FROM 
INFORMATION_SCHEMA.INNODB_TABLES WHERE
SPACE_TYPE=’Single’;

 

 

 

参考资料

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website