Mysql Information Schema 学习(一)--通用表

暗夜猎手-大魔王

已于 2022-07-03 17:01:39 修改

阅读量1.2k

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 information

于 2022-07-03 16:27:15 首次发布

本文链接：https://blog.csdn.net/u014106644/article/details/125584978

版权

数据库专栏收录该内容

41 篇文章 6 订阅

订阅专栏

有时候会遇到某些需求，比如要查看统计数据库中特定条件的字段等，之前会傻傻的一张张表来分析统计，其实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值：

Binlog Dump 这是复制源上的一个线程，用于将二进制日志内容发送到副本。
Change user 线程正在执行更改用户操作。
Close stmt 线程正在关闭准备好的语句。
Connect 副本连接到其源。
Connect Out 副本正在连接到其源。
Create DB 线程正在执行创建数据库操作。
Daemon 该线程在服务器内部，而不是为客户端连接提供服务的线程。
Debug 线程正在生成调试信息。
Delayed insert 该线程是一个延迟插入处理程序。
Drop DB 线程正在执行删除数据库操作。
Error
Execute 线程正在执行准备好的语句。
Fetch 线程正在从执行准备好的语句中获取结果。
Field List 该线程正在检索表列的信息。
Init DB 该线程正在选择一个默认数据库。
Kill 该线程正在杀死另一个线程。
Long Data 线程在执行准备好的语句的结果中检索长数据。
Ping 该线程正在处理服务器 ping 请求。
Prepare 该线程正在准备一个准备好的语句。
Processlist 该线程正在生成有关服务器线程的信息。
Query 线程正在执行一条语句。
Quit 线程正在终止。
Refresh 线程正在刷新表、日志或缓存，或重置状态变量或复制服务器信息。
Register Slave 该线程正在注册副本服务器。
Reset stmt 该线程正在重置准备好的语句。
Set option 线程正在设置或重置客户端语句执行选项。
Shutdown 线程正在关闭服务器。
Sleep 该线程正在等待客户端向其发送新语句。
Statistics 该线程正在生成服务器状态信息。

STATE状态主要如下：

After create 当线程在创建表的函数结束时创建表（包括内部临时表）时，就会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态。
altering table 服务器正在执行就地 ALTER TABLE.
checking permissions 该线程正在检查服务器是否具有执行语句所需的权限。
Checking table 线程正在执行表检查操作。
cleaning up 线程已经处理了一个命令并准备释放内存并重置某些状态变量。
closing tables 该线程正在将更改的表数据刷新到磁盘并关闭已使用的表。这应该是一个快速的操作。如果没有，请确认您没有完整的磁盘并且该磁盘的使用量不是很大。
converting HEAP to ondisk 该线程正在将内部临时表从 MEMORY表转换为磁盘表。
copy to tmp table 线程正在处理一条ALTER TABLE语句。此状态发生在创建具有新结构的表之后但在将行复制到其中之前。对于处于这种状态的线程，可以使用 Performance Schema 来获取有关复制操作的进度。
Copying to group table 如果语句具有不同ORDER BY的 GROUP BY条件，则将按组对行进行排序并复制到临时表中。
Copying to tmp table 服务器正在复制到内存中的临时表。
Copying to tmp table on disk 服务器正在复制到磁盘上的临时表。临时结果集变得太大.因此，线程将临时表从内存中更改为基于磁盘的格式以节省内存。
Creating index 线程正在处理ALTER TABLE ... ENABLE KEYS一个MyISAM表。
Creating sort index 线程正在处理 SELECT使用内部临时表解析的 a。
creating table 该线程正在创建一个表。这包括创建临时表。
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 TABLESPACEorALTER TABLE ... IMPORT TABLESPACE语句。
end 这发生在最后，但在清理 ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE语句之前。对于end状态，可能会发生以下操作：
- 更改表中的数据后删除查询缓存条目
- 将事件写入二进制日志
- 释放内存缓冲区，包括 blob
executing 线程已开始执行语句。
Execution of init_command 线程正在执行 init_command系统变量值中的语句。
freeing items 线程已执行命令。在此状态期间完成的某些项目释放涉及查询缓存。此状态通常后跟cleaning up.
FULLTEXT initialization 服务器正准备执行自然语言全文搜索。
init 这发生在 ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE语句的初始化之前。服务器在此状态下采取的动作包括刷新二进制日志、InnoDB日志和一些查询缓存清理操作。
Killed 有人KILL 向线程发送了一条语句，它应该在下次检查 kill 标志时中止。在 MySQL 的每个主要循环中都会检查该标志，但在某些情况下，线程可能仍需要很短的时间才能结束。如果线程被其他线程锁定，则在其他线程释放其锁定后立即终止。
logging slow query 该线程正在向慢查询日志写入一条语句。
login 连接线程的初始状态，直到客户端成功通过身份验证。
manage keys 服务器正在启用或禁用表索引。
Opening tables 该线程正在尝试打开一个表。这应该是一个非常快的过程，除非有东西阻止打开。例如，一个ALTER TABLEor LOCK TABLE语句可以阻止打开表，直到语句完成。检查您的table_open_cache值是否足够大也是值得的。
optimizing 服务器正在为查询执行初始优化。
preparing 此状态发生在查询优化期间。
Purging old relay logs 该线程正在删除不需要的中继日志文件。
query end 此状态发生在处理查询之后但在 freeing items状态之前。
Receiving from client 服务器正在从客户端读取数据包。此状态Reading from net在 MySQL 5.7.8 之前被调用。
Removing duplicates 查询的使用 SELECT DISTINCT方式使得 MySQL 无法在早期阶段优化掉不同的操作。因此，MySQL 需要一个额外的阶段来删除所有重复的行，然后再将结果发送到客户端。
removing tmp table

SELECT 线程在处理语句后正在删除内部临时表。如果没有创建临时表，则不使用此状态。
rename 该线程正在重命名一个表。
rename result table 线程正在处理一条ALTER TABLE语句，已创建新表，并正在重命名它以替换原始表。
Reopen tables 线程获得了表的锁，但在获得锁后注意到底层表结构发生了变化。它释放了锁，关闭了表，并试图重新打开它。
Repair by sorting 修复代码使用排序来创建索引。
preparing for alter table 服务器正准备执行就地 ALTER TABLE.
Rolling back 线程正在回滚事务。
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 for group 线程正在执行排序以满足 a GROUP BY。
Sorting for order 该线程正在执行排序以满足ORDER BY.
Sorting index MyISAM该线程正在对索引页进行排序，以便在表优化操作期间进行更有效的访问。
Sorting result 对于SELECT语句，这类似于Creating sort index，但对于非临时表。
starting 语句执行开始的第一阶段。
statistics 服务器正在计算统计信息以制定查询执行计划。如果一个线程长时间处于这种状态，服务器可能正在磁盘绑定执行其他工作。
System lock 线程已调用mysql_lock_tables() 且线程状态自此未更新。这是一种非常普遍的状态，可能由于多种原因而发生。例如，线程将要请求或正在等待表的内部或外部系统锁。在InnoDB执行 LOCK TABLES. 如果此状态是由外部锁请求引起的，并且您没有使用多个访问相同表的mysqld服务器，则可以使用该选项MyISAM 禁用外部系统锁。--skip-external-locking但是，默认情况下外部锁定是禁用的，因此该选项很可能没有效果。对于 SHOW PROFILE，此状态意味着线程正在请求锁（不等待它）。
update 线程正准备开始更新表。
Updating 该线程正在搜索要更新的行并正在更新它们。
updating main table 服务器正在执行多表更新的第一部分。它只更新第一个表，并保存用于更新其他（参考）表的列和偏移量。
updating reference tables 服务器正在执行多表更新的第二部分，并从其他表中更新匹配的行。
User lock 线程将要请求或正在等待调用请求的咨询锁 GET_LOCK()。对于 SHOW PROFILE，此状态意味着线程正在请求锁（不等待它）。
User sleep 线程调用了一个 SLEEP()调用。
Waiting for commit lock

FLUSH TABLES WITH READ LOCK 正在等待提交锁。
Waiting for global read lock

FLUSH TABLES WITH READ LOCK 正在等待全局读锁或 read_only正在设置全局系统变量。
Waiting for tables 线程收到一个表的基础结构已更改的通知，它需要重新打开表以获取新结构。但是，要重新打开表，它必须等到所有其他线程都关闭了有问题的表。
Waiting for table flush 线程正在执行FLUSH TABLES并正在等待所有线程关闭其表，或者线程收到表的底层结构已更改的通知，它需要重新打开表以获取新结构。但是，要重新打开表，它必须等到所有其他线程都关闭了有问题的表。
Waiting for lock_type lock THR_LOCK服务器正在等待从元数据锁定子系统获取锁或锁，其中lock_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时，需要关注，很容易因为获取不到元数据锁而导致链接不释放，数据库崩溃。
Waiting on cond 线程正在等待条件变为真的一般状态。没有具体的状态信息可用。
Writing to net 服务器正在向网络写入数据包。从 MySQL 5.7.8 开始，这种状态被称为Sending to client。