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

       有时候会遇到某些需求，比如要查看统计数据库中特定条件的字段等，之前会傻傻的一张张表来分析统计，其实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表主要描述了数据库表的元数据，可以获取有关表的一些信息，例如行数，占用空间等。

主要字段如下：

根据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表可以批量生成一些DDL语句等。例如要将数据库下面所有表的字符串字段长度40扩展为60 则可以查询column_type=varchar(40)有哪些列，然后批量生成ddl sql。

3.PARTITIONS

存储于分区表有关的数据，主要字段：

• PARTITION_NAME  分区的名称。

• PARTITION_METHOD  值RANGE, LIST, HASH, LINEAR HASH, KEY, 或 LINEAR KEY;

• TABLE_ROWS 分区中的表行数。可以大致统计分区表的行数

4.PROCESSLIST

show processlist; 可以查看当前数据库活跃的连接，线程等，常用来查看数据库当前运行状态。

MySQL 进程列表指示当前由服务器内执行的线程集执行的操作。

核心列：

啥也不说了，这里面每个字段都经常使用，这里学习一下command和STATE状态，

COMMAND取值主要如下：

线程可以具有以下任何 Command值：

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。