有时候会遇到某些需求,比如要查看统计数据库中特定条件的字段等,之前会傻傻的一张张表来分析统计,其实information schema提供了非常强大的功能,可以研究一下。
参考资料,官网文档:
MySQL :: MySQL 5.7 Reference Manual :: 24 INFORMATION_SCHEMA Tableshttps://dev.mysql.com/doc/refman/5.7/en/information-schema.htmlinformation schema里面有啥,看一下文档说明:
INFORMATION_SCHEMA
提供对数据库 元数据、有关 MySQL 服务器的信息(例如数据库或表的名称、列的数据类型或访问权限)的访问。information里面的表是视图,没有数据文件:INFORMATION_SCHEMA
是每个 MySQL 实例中的一个数据库,该位置存储有关 MySQL 服务器维护的所有其他数据库的信息。该 INFORMATION_SCHEMA
数据库包含几个只读表。它们实际上是视图,而不是基表,因此没有与它们关联的文件,并且不能在它们上设置触发器。此外,没有具有该名称的数据库目录。
大致看一下schema下有哪些表:
可知主要是数据库元数据权限,变量以及很大一部分INNODB存储引擎有关的表。
主要学习一下常用的表
1. TABLES
TABLES | 表信息 |
tables表主要描述了数据库表的元数据,可以获取有关表的一些信息,例如行数,占用空间等。
主要字段如下:
-
TABLE_CATALOG
表所属目录的名称。该值始终为def
。 -
TABLE_SCHEMA
表所属的架构(数据库)的名称。 -
TABLE_NAME
表的名称。 -
TABLE_TYPE
BASE TABLE/SYSTEM VIEW
-
ENGINE
表的存储引擎。对于分区表,ENGINE
显示所有分区使用的存储引擎的名称。 -
VERSION
.frm
表文件 的版本号。 -
ROW_FORMAT
行存储格式 (Fixed
,Dynamic
,Compressed
,Redundant
,Compact
)。 -
TABLE_ROWS
行数。一些存储引擎,例如MyISAM
,存储确切的计数。对于其他存储引擎,例如InnoDB
,这个值是一个近似值,可能与实际值相差 40% 到 50%。在这种情况下,请使用SELECT COUNT(*)
以获得准确的计数。 -
AVG_ROW_LENGTH
平均行长。 -
DATA_LENGTH
对于MyISAM
,DATA_LENGTH
是数据文件的长度,以字节为单位。对于
InnoDB
,DATA_LENGTH
是为聚集索引分配的近似空间量,以字节为单位。具体来说,它是聚集索引大小(以页面为单位)乘以InnoDB
页面大小。有关其他存储引擎的信息,请参阅本节末尾的注释。
-
MAX_DATA_LENGTH
对于MyISAM
,MAX_DATA_LENGTH
是数据文件的最大长度。给定使用的数据指针大小,这是可以存储在表中的数据总字节数。 -
INDEX_LENGTH
对于MyISAM
,INDEX_LENGTH
是索引文件的长度,以字节为单位。对于
InnoDB
,INDEX_LENGTH
是为非聚集索引分配的近似空间量,以字节为单位。具体来说,它是非聚集索引大小的总和(以页面为单位)乘以InnoDB
页面大小 -
AUTO_INCREMENT
下一个AUTO_INCREMENT
值 -
CREATE_TIME
创建表的时间 -
TABLE_COMMENT
创建表时使用的注释
根据data_length+index_length可以大致统计一下表占用空间的大小。
例如统计所有表的空间大小
SELECT TABLE_NAME, sum(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 as size FROM information_schema.`TABLES` GROUP BY TABLE_NAME ORDER BY size desc;
2.COLUMNS
COLUMNS | 每个表中的列 |
提供有关表中列的信息。具有以下列:
-
TABLE_CATALOG
包含该列的表所属的目录的名称。该值始终为def
。 -
TABLE_SCHEMA
包含该列的表所属的架构(数据库)的名称。 -
TABLE_NAME
包含列的表的名称。 -
COLUMN_NAME
列的名称。 -
ORDINAL_POSITION
列在表中的位置。 -
COLUMN_DEFAULT
列的默认值。这是NULL
如果列具有显式默认值NULL
,或者列定义不包含DEFAULT
子句。 -
IS_NULLABLE
列可空性。该值是YES
是否NULL
可以将值存储在列中,NO
如果不是。 -
DATA_TYPE
列数据类型。 -
CHARACTER_MAXIMUM_LENGTH
对于字符串列,以字符为单位的最大长度。 -
CHARACTER_OCTET_LENGTH
对于字符串列,最大长度(以字节为单位)。 -
NUMERIC_PRECISION
对于数值列,数值精度。 -
NUMERIC_SCALE
对于数字列,数字刻度。 -
DATETIME_PRECISION
对于时间列,小数秒精度。 -
CHARACTER_SET_NAME
对于字符串列,字符集名称。 -
COLLATION_NAME
对于字符串列,排序规则名称。 -
COLUMN_TYPE
COLUMN_TYPE
值包含类型名称和可能的其他信息,例如精度或长度。 -
COLUMN_KEY
列是否被索引:-
如果
COLUMN_KEY
为空,则该列要么不被索引,要么仅作为多列、非唯一索引中的辅助列被索引。 -
如果
COLUMN_KEY
是PRI
,则该列是PRIMARY KEY
或 是多列中的列之一PRIMARY KEY
。 -
如果
COLUMN_KEY
是UNI
,则该列是UNIQUE
索引的第一列。(UNIQUE
索引允许多个NULL
值,但您可以NULL
通过检查Null
列来判断该列是否允许。) -
如果
COLUMN_KEY
是MUL
,则该列是非唯一索引的第一列,其中允许在该列中多次出现给定值。
-
-
PRIVILEGES
您对该列的权限。 -
COLUMN_COMMENT
列定义中包含的任何注释。
通过columns表可以批量生成一些DDL语句等。例如要将数据库下面所有表的字符串字段长度40扩展为60 则可以查询column_type=varchar(40)有哪些列,然后批量生成ddl sql。
3.PARTITIONS
PARTITIONS | 表分区信息 |
存储于分区表有关的数据,主要字段:
-
PARTITION_NAME
分区的名称。 -
PARTITION_METHOD
值RANGE
,LIST
,HASH
,LINEAR HASH
,KEY
, 或LINEAR KEY
; -
TABLE_ROWS
分区中的表行数。可以大致统计分区表的行数
4.PROCESSLIST
PROCESSLIST | 当前执行线程的信息 |
show processlist; 可以查看当前数据库活跃的连接,线程等,常用来查看数据库当前运行状态。
MySQL 进程列表指示当前由服务器内执行的线程集执行的操作。
核心列:
-
ID
连接标识符。 可以使用KILL 语句杀死线程。 -
USER
发出语句的 MySQL 用户。 -
HOST
发出语句的客户端的主机名 -
DB
线程的默认数据库,或者NULL
如果没有选择。 -
COMMAND
线程代表客户端执行的命令类型,或者Sleep
会话是否空闲。 -
TIME
线程处于当前状态的时间(以秒为单位)。 -
STATE
指示线程正在做什么的动作、事件或状态。大多数状态对应于非常快速的操作。如果一个线程在给定状态下停留数秒,则可能存在需要调查的问题。 -
INFO
线程正在执行的语句,或者NULL
如果它不执行任何语句。如果该语句执行其他语句,则该语句可能是发送到服务器的语句,或者是最内层的语句。例如,如果CALL
语句执行正在执行 SELECT语句的存储过程,则该INFO
值显示该 SELECT语句。
啥也不说了,这里面每个字段都经常使用,这里学习一下command和STATE状态,
COMMAND取值主要如下:
线程可以具有以下任何 Command
值:
STATE状态主要如下:
-
After create
当线程在创建表的函数结束时创建表(包括内部临时表)时,就会发生这种情况。即使由于某些错误而无法创建表,也会使用此状态。 -
altering table
服务器正在执行就地 ALTER TABLE. - checking permissions 该线程正在检查服务器是否具有执行语句所需的权限。
-
closing tables
该线程正在将更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有,请确认您没有完整的磁盘并且该磁盘的使用量不是很大。 -
copy to tmp table
线程正在处理一条ALTER TABLE语句。此状态发生在创建具有新结构的表之后但在将行复制到其中之前。对于处于这种状态的线程,可以使用 Performance Schema 来获取有关复制操作的进度。 -
Copying to group table
如果语句具有不同ORDER BY
的GROUP BY
条件,则将按组对行进行排序并复制到临时表中。 -
Copying to tmp table on disk
服务器正在复制到磁盘上的临时表。临时结果集变得太大.因此,线程将临时表从内存中更改为基于磁盘的格式以节省内存。 -
Creating sort index
线程正在处理 SELECT使用内部临时表解析的 a。 -
Creating tmp table
该线程正在内存或磁盘上创建一个临时表。如果表是在内存中创建的,但后来转换为磁盘表,则该操作期间的状态为Copying to tmp table on disk
. -
committing alter table to storage engine
服务器已就地完成 ALTER TABLE并正在提交结果。 -
deleting from main table
服务器正在执行多表删除的第一部分。它仅从第一个表中删除,并保存用于从其他(参考)表中删除的列和偏移量。 -
deleting from reference tables
服务器正在执行多表删除的第二部分,并从其他表中删除匹配的行。 -
discard_or_import_tablespace
线程正在处理ALTER TABLE ... DISCARD TABLESPACE
orALTER TABLE ... IMPORT TABLESPACE
语句。 -
end
这发生在最后,但在清理 ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE语句之前。对于end
状态,可能会发生以下操作:-
更改表中的数据后删除查询缓存条目
-
将事件写入二进制日志
-
释放内存缓冲区,包括 blob
-
-
freeing items
线程已执行命令。在此状态期间完成的某些项目释放涉及查询缓存。此状态通常后跟cleaning up
. -
init
这发生在 ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE语句的初始化之前。服务器在此状态下采取的动作包括刷新二进制日志、InnoDB
日志和一些查询缓存清理操作。 -
Killed
有人KILL 向线程发送了一条语句,它应该在下次检查 kill 标志时中止。在 MySQL 的每个主要循环中都会检查该标志,但在某些情况下,线程可能仍需要很短的时间才能结束。如果线程被其他线程锁定,则在其他线程释放其锁定后立即终止。 -
Opening tables
该线程正在尝试打开一个表。这应该是一个非常快的过程,除非有东西阻止打开。例如,一个ALTER TABLEor LOCK TABLE语句可以阻止打开表,直到语句完成。检查您的table_open_cache值是否足够大也是值得的。 -
Receiving from client
服务器正在从客户端读取数据包。此状态Reading from net
在 MySQL 5.7.8 之前被调用。 -
Removing duplicates
查询的使用 SELECT DISTINCT方式使得 MySQL 无法在早期阶段优化掉不同的操作。因此,MySQL 需要一个额外的阶段来删除所有重复的行,然后再将结果发送到客户端。 -
SELECT 线程在处理语句 后正在删除内部临时表。如果没有创建临时表,则不使用此状态。
-
rename result table
线程正在处理一条ALTER TABLE语句,已创建新表,并正在重命名它以替换原始表。 -
Reopen tables
线程获得了表的锁,但在获得锁后注意到底层表结构发生了变化。它释放了锁,关闭了表,并试图重新打开它。 -
preparing for alter table
服务器正准备执行就地 ALTER TABLE. -
Saving state
对于MyISAM
修复或分析等表操作,线程正在将新表状态保存到.MYI
文件头。状态包括行数、AUTO_INCREMENT
计数器和键分布等信息。 -
Searching rows for update
该线程正在执行第一阶段以在更新它们之前找到所有匹配的行。如果 UPDATE正在更改用于查找相关行的索引,则必须这样做。 -
Sending data
该线程正在读取和处理 SELECT语句的行,并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问(读取),因此它通常是给定查询生命周期内运行时间最长的状态。 -
Sending to client
服务器正在向客户端写入数据包。此状态Writing to net
在 MySQL 5.7.8 之前被调用。 -
setup
线程正在开始一个ALTER TABLE操作。 -
Sorting result
对于SELECT语句,这类似于Creating sort index
,但对于非临时表。 -
statistics
服务器正在计算统计信息以制定查询执行计划。如果一个线程长时间处于这种状态,服务器可能正在磁盘绑定执行其他工作。 -
System lock
线程已调用mysql_lock_tables()
且线程状态自此未更新。这是一种非常普遍的状态,可能由于多种原因而发生。 例如,线程将要请求或正在等待表的内部或外部系统锁。在InnoDB执行 LOCK TABLES. 如果此状态是由外部锁请求引起的,并且您没有使用多个访问相同表的mysqld服务器,则可以使用该 选项MyISAM 禁用外部系统锁 。--skip-external-locking但是,默认情况下外部锁定是禁用的,因此该选项很可能没有效果。对于 SHOW PROFILE,此状态意味着线程正在请求锁(不等待它)。 -
updating main table
服务器正在执行多表更新的第一部分。它只更新第一个表,并保存用于更新其他(参考)表的列和偏移量。 -
User lock
线程将要请求或正在等待调用请求的咨询锁 GET_LOCK()。对于 SHOW PROFILE,此状态意味着线程正在请求锁(不等待它)。 -
User sleep
线程调用了一个 SLEEP()调用。 -
FLUSH TABLES WITH READ LOCK 正在等待提交锁。
-
FLUSH TABLES WITH READ LOCK 正在等待全局读锁或 read_only正在设置全局系统变量。
-
Waiting for tables
线程收到一个表的基础结构已更改的通知,它需要重新打开表以获取新结构。但是,要重新打开表,它必须等到所有其他线程都关闭了有问题的表。 -
Waiting for table flush
线程正在执行FLUSH TABLES并正在等待所有线程关闭其表,或者线程收到表的底层结构已更改的通知,它需要重新打开表以获取新结构。但是,要重新打开表,它必须等到所有其他线程都关闭了有问题的表。 -
Waiting for
服务器正在等待从元数据锁定子系统 获取 锁或锁,其中lock_type
lock THR_LOCKlock_type
指示锁的类型。此状态表示等待 a
THR_LOCK
:-
Waiting for table level lock
这些状态表示等待元数据锁:
-
Waiting for event metadata lock
-
Waiting for global read lock
-
Waiting for schema metadata lock
-
Waiting for stored function metadata lock
-
Waiting for stored procedure metadata lock
-
Waiting for table metadata lock
-
Waiting for trigger metadata lock
可以获取到当前线程状态正在等待锁,特别是执行DDL时,需要关注,很容易因为获取不到元数据锁而导致链接不释放,数据库崩溃。
-
-
Writing to net
服务器正在向网络写入数据包。从 MySQL 5.7.8 开始,这种状态被称为Sending to client
。