因为最近研究了代码自动生成框架，其中可以将数据库中所有的表自动生成JAVA、HTML、JS等文件，在熟悉的过程中发现在数据表操作上用到了INFORMATION_SCHEMA，这个在此之前并不是很熟悉。当我们安装好 MySQL 数据库后，会发现数据库实例自带有 information_schema 系统库，你是否有去关注过这个系统库呢？是否有查询过此库中的表数据呢？又是否清楚此库存在的具体作用呢？带着这些疑问，我们一起来看本篇文章。
一、概述
INFORMATION_SCHEMA 是 MySQL 自带的信息数据库，其保存了MySQL服务器所有数据库的信息，例如数据库名、数据库的表、表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在 INFORMATION_SCHEMA 表里面。
对于 INFORMATION_SCHEMA 中的大多数信息，每位MySQL用户均有权访问这些表，但仅限于表中与用户具有适当访问权限的对象相对应的行。虽然可以INFORMATION_SCHEMA通过语句选择默认数据库USE ，但只能读取表的内容，不能对它们执行 INSERT、 UPDATE或 DELETE操作。
二、表分类
我们来具体看下 information_schema 下的表，根据MySQL版本的不同，表的个数和存放是有所不同的。在MySQL5.6 版本中总共有59个表，在MySQL5.7.23版本中，该schema下总共有61个表，而到了MySQL5.7.32该schema下总共有69个表，在MySQL 8.0版本中，该schema下的数据字典表都迁移到了mysql schema下，且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问。这里以 5.7.32 版本为例，说明information_schema需要了解的表。
information_schema下的所有表使用的都是Memory存储引擎，且都是临时表，不是持久表，在数据库重启之后这些数据会丢失。在MySQL的4个系统库中，information_schema也是唯一一个在文件系统上没有对应库表的目录和文件的系统库。
2.1 统计信息类
2.1.1 schemata
该表提供了当前mysql实例中所有数据库的信息，一个schema就代表一个数据库。show databases 的结果取之此表。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| catalog_name | varchar(512) | NO | |
| schema_name | varchar(64) | NO | 数据库名 |
| default_character_set_name | varchar(32) | NO | 数据库默认编码 |
| default_collation_name | varchar(32) | NO | |
| sql_path | varchar(512) | YES |
2.1.2 tables
tables表提供了关于数据库中的表的基本信息，详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息，show tables from schemaname 的结果取之此表。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| table_catalog | varchar(512) | no | |
| table_schema | varchar(64) | no | 数据表所属的数据库名 |
| table_name | varchar(64) | no | 表名称 |
| table_type | varchar(64) | no | 表类型[system view、base table] |
| engine | varchar(64) | yes | 使用的数据库引擎[MyISAM、CSV、InnoDB] |
| version | bigint(21) | yes | 版本，默认值10 |
| row_format | varchar(10) | yes | 行格式[Compact、Dynamic、Fixed] |
| table_rows | bigint(21) | yes | 表里所存多少行数据 |
| avg_row_length | bigint(21) | yes | 平均行长度 |
| data_length | bigint(21) | yes | 数据长度 |
| max_data_length | bigint(21) | yes | 最大数据长度 |
| index_length | bigint(21) | yes | 索引长度 |
| data_free | bigint(21) | yes | 空间碎片 |
| auto_increment | bigint(21) | yes | 做自增主键的自动增量当前值 |
| create_time | datetime | yes | 表的创建时间 |
| update_time | datetime | yes | 表的更新时间 |
| check_time | datetime | yes | 表的检查时间 |
| table_collation | varchar(32) | yes | 表的字符校验编码集 |
| checksum | bigint(21) | yes | 校验和 |
| create_options | varchar(255) | yes | 创建选项 |
| table_comment | varchar(2048) | no | 表的注释、备注 |
2.1.3 columns
columns表提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息，show tables from schemaname.tablename 的结果取之此表。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| table_catalog | varchar(512) | no | 包含该列的表所属的目录的名称，该值始终为def |
| table_schema | varchar(64) | no | 数据库名 |
| table_name | varchar(64) | no | 表名 |
| column_name | varchar(64) | no | 列名 |
| ordinal_position | bigint(21) | no | 表中字段的位置 |
| column_default | longtext | yes | 字段的默认值 |
| is_nullable | varchar(3) | no | 是否可以取空值 |
| data_type | varchar(64) | no | 字段数据类型 |
| character_maximum_length | bigint(21) | yes | 对于字符串列，最大长度(以字符为单位) |
| character_octet_length | bigint(21) | yes | 对于字符串列，最大长度(以字节为单位) |
| numeric_precision | bigint(21) | yes | 对于数字字段，数字精度 |
| numeric_scale | bigint(21) | yes | 对于数字字段，数字刻度 |
| datetime_precision | bigint(21) | yes | 对于时间字段，小数秒精度 |
| character_set_name | varchar(32) | yes | 对于字符串字段，字符集名称 |
| collation_name | varchar(32) | yes | 对于字符串字段，排序规则名称 |
| column_type | longtext | no | 字段数据类型 |
| column_key | varchar(3) | no | 索引类型。主键:PRI、唯一索引:UNI、一般索引:MUL |
| extra | varchar(30) | no | 有关给定字段的任何其他可用信息 |
| privileges | varchar(80) | no | 该列的权限 |
| column_comment | varchar(1024) | no | 字段定义中包含的任何注释 |
| generation_expression | longtext | no | 对于生成的字段，显示用于计算列值的表达式 |
2.1.4 statistics
该表提供了关于表索引的信息，一个索引对应一行记录，show index from schemaname.tablename 的结果取之此表。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| table_catalog | varchar(512) | no | 所属的目录的名称。该值始终为def |
| table_schema | varchar(64) | no | 表所属数据库的名称 |
| table_name | varchar(64) | no | 表的名称 |
| non_unique | bigint(1) | no | 不能包含重复项，则为 0，否则为 1 |
| index_schema | varchar(64) | no | 索引所属数据库的名称 |
| index_name | varchar(64) | no | 索引的名称 |
| seq_in_index | bigint(2) | no | 索引中的列序号，从 1 开始 |
| column_name | varchar(64) | no | 列名称 |
| collation | varchar(1) | yes | 排序方式。 A（升序）、D （降序）或NULL（未排序） |
| cardinality | bigint(21) | yes | |
| sub_part | bigint(3) | yes | 索引前缀 |
| packed | varchar(10) | yes | |
| nullable | varchar(3) | no | 是否可能包含 NULL值，是为YES，否则为"" |
| index_type | varchar(16) | no | 使用的索引方法 ( BTREE, FULLTEXT, HASH, RTREE) |
| comment | varchar(16) | yes | 描述 |
| index_comment | varchar(1024) | no |
2.2 表级别对象类
2.2.2 routines
该表提供了关于存储过程和存储函数的信息，但不包括用户自定义函数。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| specific_name | varchar(64) | no | 名称 |
| routine_catalog | varchar(512) | no | 所属的目录的名称。该值始终为def |
| routine_schema | varchar(64) | no | 数据库的名称 |
| routine_name | varchar(64) | no | |
| routine_type | varchar(9) | no | PROCEDURE：存储过程， FUNCTION：存储函数 |
| data_type | varchar(64) | no | |
| character_maximum_length | int(21) | yes | |
| character_octet_length | int(21) | yes | |
| numeric_precision | bigint(21) | yes | |
| numeric_scale | int(21) | yes | |
| datetime_precision | bigint(21) | yes | |
| character_set_name | varchar(64) | yes | |
| collation_name | varchar(64) | yes | |
| dtd_identifier | longtext | yes | |
| routine_body | varchar(8) | no | |
| routine_definition | longtext | yes | 执行的 SQL 语句 |
| external_name | varchar(64) | yes | |
| external_language | varchar(64) | yes | |
| parameter_style | varchar(8) | no | |
| is_deterministic | varchar(3) | no | |
| sql_data_access | varchar(64) | no | |
| sql_path | varchar(64) | yes | |
| security_type | varchar(7) | no | |
| created | datetime | no | |
| last_altered | datetime | no | |
| sql_mode | varchar(8192) | no | |
| routine_comment | longtext | no | |
| definer | varchar(93) | no | |
| character_set_client | varchar(32) | no | |
| collation_connection | varchar(32) | no | |
| database_collation | varchar(32) | no |
2.2.3 triggers
该表提供了关于某个数据库下的触发器相关信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| trigger_catalog | varchar(512) | no | 触发器所属目录，该值始终为def |
| trigger_schema | varchar(64) | no | 触发器所属的数据库 |
| trigger_name | varchar(64) | no | 触发器的名称 |
| event_manipulation | varchar(6) | no | 触发事件 |
| event_object_catalog | varchar(512) | no | |
| event_object_schema | varchar(64) | no | |
| event_object_table | varchar(64) | no | |
| action_order | bigint(4) | no | |
| action_condition | longtext | yes | |
| action_statement | longtext | no | |
| action_orientation | varchar(9) | no | |
| action_timing | varchar(6) | no | |
| action_reference_old_table | varchar(64) | yes | |
| action_reference_new_table | varchar(64) | yes | |
| action_reference_old_row | varchar(3) | no | |
| action_reference_new_row | varchar(3) | no | |
| created | datetime(2) | yes | 触发器的创建时间 |
| sql_mode | varchar(8192) | no | |
| definer | varchar(93) | no | |
| character_set_client | varchar(32) | no | |
| collation_connection | varchar(32) | no | |
| database_collation | varchar(32) | no |
2.2.4 views
该表给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| table_schema | varchar(64) | no | 所属的数据库的名称 |
| table_name | varchar(64) | no | 视图的名称 |
| view_definition | longtext | no | 视图定义的语句 |
| check_option | varchar(8) | no | |
| is_updatable | varchar(3) | no | 视图可更新标志 |
| definer | varchar(93) | no | 创建视图的用户 |
| security_type | varchar(7) | no | |
| character_set_client | varchar(32) | no | |
| collation_connection | varchar(32) | no |
2.2.5 events
该表提供查询与计划任务事件相关的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| event_catalog | varchar(64) | no | 所属目录的名称。该值始终为def |
| event_schema | varchar(64) | no | 所属数据库的名称 |
| event_name | varchar(64) | no | 事件的名称 |
| definer | varchar(93) | no | 创建事件的用户 |
| time_zone | varchar(64) | no | 事件时区，默认值为SYSTEM |
| event_body | varchar(8) | no | 用于事件语句的语言。该值始终为 SQL |
| event_definition | longtext | no | 事件执行的语句 |
| event_type | varchar(9) | no | 事件重复类型，ONE TIME：一次性、RECURRING：重复 |
| execute_at | datetime | yes | |
| interval_value | varchar(256) | yes | 事件重复执行之间的间隔数。对于一次性事件，该值始终为 NULL |
| interval_field | varchar(18) | yes | 事件在重复之前等待间隔的时间单位。对于一次性事件，该值为 NULL |
| sql_mode | varchar(8192) | no | |
| starts | datetime | yes | 重复事件的开始时间 |
| ends | datetime | yes | 重复事件的结束时间 |
| status | varchar(18) | no | 事件状态 |
| on_completion | varchar(12) | no | |
| created | datetime | no | 创建时间 |
| last_altered | datetime | no | 修改时间。如果事件未曾修改，则此值与该CREATED值相同。 |
| last_executed | datetime | yes | 事件上次执行的时间。如果事件从未执行，则此列是NULL |
| event_comment | varchar(64) | no | 创建事件的 MySQL 服务器 ID |
| originator | bigint(10) | no | |
| character_set_client | varchar(32) | no | |
| collation_connection | varchar(32) | no | |
| database_collation | varchar(32) | no |
2.3 权限、约束类
2.3.1 user_privileges
user_privileges(用户权限)表给出了关于全程权限的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| grantee | varchar(81) | no | 授予权限的帐户的名称 |
| table_catalog | varchar(512) | no | 目录的名称。该值始终为 def |
| privilege_type | varchar(64) | no | 权限类型 |
| is_grantable | varchar(3) | no | 是否授权 |
2.3.2 schema_privileges
schema_privileges表给出了关于数据库权限的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| grantee | varchar(81) | no | |
| table_catalog | varchar(512) | no | |
| table_schema | varchar(64) | no | 数据库名 |
| privilege_type | varchar(64) | no | 权限类型 |
| is_grantable | varchar(3) | no | 是否授权 |
2.3.3 table_privileges
该表给出了关于表权限的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| grantee | varchar(81) | no | 授予权限的帐户名称 |
| table_catalog | varchar(512) | no | 表所属目录的名称。该值始终为def |
| table_schema | varchar(64) | no | 表所属的数据库的名称 |
| table_name | varchar(64) | no | 表的名称 |
| privilege_type | varchar(64) | no | 权限类型 |
| is_grantable | varchar(3) | no |
2.1.5 table_constraints
该表描述了存在约束的表以及表的约束类型。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| constraint_catalog | varchar(512) | no | 约束所属的目录的名称。该值始终为def |
| constraint_schema | varchar(64) | no | 约束所属的数据库 |
| constraint_name | varchar(64) | no | |
| table_schema | varchar(64) | no | 表所属的数据库 |
| table_name | varchar(64) | no | 表的名称 |
| constraint_type | varchar(64) | no | 约束的类型。该值是 UNIQUE、PRIMARY KEY、 FOREIGN KEY或CHECK |
2.3.4 column_privileges
column_privileges(列权限)表给出了关于列权限的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| grantee | varchar(81) | no | 授予权限的帐户的名称 |
| table_catalog | varchar(512) | no | 该列的表所属的目录的名称。该值始终为def |
| table_schema | varchar(64) | no | 该列的表所属数据库的名称 |
| table_name | varchar(64) | no | 表的名称 |
| column_name | varchar(64) | no | 列的名称 |
| privilege_type | varchar(64) | no | 授予的权限 |
| is_grantable | varchar(3) | no |
2.1.3 key_column_usage
该表描述了具有约束的键列，包含主键、唯一索引、外键等约束信息。例如：所在的库表列名、引用的库表列名等。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| constraint_catalog | varchar(512) | no | 约束所属的目录的名称。该值始终为def |
| constraint_schema | varchar(64) | no | 约束所属的数据库的名称 |
| constraint_name | varchar(64) | no | 约束的名称 |
| table_catalog | varchar(512) | no | 表所属目录的名称。该值始终为def |
| table_schema | varchar(64) | no | 表所属的架构（数据库）的名称 |
| table_name | varchar(64) | no | 具有约束的表的名称 |
| column_name | varchar(64) | no | 具有约束的列的名称 |
| ordinal_position | bigint(10) | no | 列在约束中的位置，从 1 开始编号 |
| position_in_unique_constraint | bigint(10) | yes | |
| referenced_table_schema | varchar(64) | yes | 约束引用的数据库的名称 |
| referenced_table_name | varchar(64) | yes | 约束引用的表的名称 |
| referenced_column_name | varchar(64) | yes | 约束引用的列的名称 |
2.3.5 character_sets
该character_sets表提供了mysql实例可用字符集的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| character_set_name | varchar(32) | no | 字符集名称 |
| default_collate_name | varchar(32) | no | 字符集的默认排序规则 |
| description | varchar(60) | no | 字符集的描述 |
| maxlen | bigint(3) | no | 存储一个字符所需的最大字节数 |
2.3.6 collations
collations表提供了关于各字符的排序规则的信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| collation_name | varchar(32) | no | 排序规则名称 |
| character_set_name | varchar(32) | no | 关联的字符集的名称 |
| id | bigint(11) | no | 排序规则 ID |
| is_default | varchar(3) | no | 排序规则是否为其字符集的默认值 |
| is_compiled | varchar(3) | no | 字符集是否编译到服务器中 |
| sortlen | bigint(3) | no |
2.4 InnoDB 表
2.4.1 innodb_trx
该表提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁、事务什么时间点开始、以及事务正在执行的SQL语句文本信息等（如果有SQL的话）。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| trx_id | varchar(18) | no | 唯一事务ID号。只读和非锁定事务不记录 |
| trx_state | varchar(13) | no | 事务执行状态。有效值为：running、lock wait、rolling back、committing |
| trx_started | datetime | no | 事务开始时间 |
| trx_requested_lock_id | varchar(81) | yes | 事务当前正在等待锁的标识 |
| trx_wait_started | datetime | yes | 事务开始等待的时间 |
| trx_weight | bigint(21) | no | 事务的权重，默认是0 |
| trx_mysql_thread_id | bigint(21) | no | 事务线程id |
| trx_query | varchar(1024) | yes | 事务正在执行的 sql 语句 |
| trx_operation_state | varchar(64) | yes | 事务当前操作状态 |
| trx_tables_in_use | bigint(21) | no | 当前事务执行的 sql 中使用的表的个数 |
| trx_tables_locked | bigint(21) | no | 当前执行 sql 的行锁数量 |
| trx_lock_structs | bigint(21) | no | 事务保留的锁数量 |
| trx_lock_memory_bytes | bigint(21) | no | 事务锁住的内存大小，单位为 bytes。 |
| trx_rows_locked | bigint(21) | no | 事务锁住的记录数 |
| trx_rows_modified | bigint(21) | no | 事务更改的行数 |
| trx_concurrency_tickets | bigint(21) | no | 事务并发票数 |
| trx_isolation_level | varchar(16) | no | 当前事务的隔离级别 |
| trx_unique_checks | int(1) | no | 是否唯一性检查 |
| trx_foreign_key_checks | int(1) | no | 是否外键检查 |
| trx_last_foreign_key_error | varchar(256) | yes | 最后的外键错误 |
| trx_adaptive_hash_latched | int(1) | no | |
| trx_adaptive_hash_timeout | bigint(21) | no | |
| trx_is_read_only | int(1) | no | 是否是只读事务，1表示只读 |
| trx_autocommit_non_locking | int(1) | no |
这个表对于排查因为事务未提交引起的锁问题可以说是举足轻重。当我们有事务长时间未提交导致锁住数据库，其他程序拿不到锁的时候，因为对这张表进行排查。
2.4.2 innodb_locks
该表提供查询innodb引擎事务中正在请求的且并未获得的且同时阻塞了其他事务的锁信息(即没有发生不同事务之间的锁等待的锁信息，在这里是查看不到的，例如，只有一个事务时，该事务所加的锁信息无法查看到)。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| lock_id | varchar(81) | no | 唯一锁ID号，随时可能会发生变化 |
| lock_trx_id | varchar(18) | no | 持有该锁的事务ID |
| lock_mode | varchar(32) | no | 锁模式(如何请求锁) |
| lock_type | varchar(32) | no | 锁的类型。record 代表行级锁，table 代表表级锁 |
| lock_table | varchar(1024) | no | 锁定记录相关的表名称 |
| lock_index | varchar(1024) | yes | 当 lock_type=’record’ 时，表示索引的名称；否则为 null |
| lock_space | bigint(21) unsigned | yes | 当 lock_type=’record’ 时，表示锁定行的表空间 id；否则为 null |
| lock_page | bigint(21) unsigned | yes | 当 lock_type=’record’ 时，表示锁定行的页号；否则为 null |
| lock_rec | bigint(21) unsigned | yes | 当 lock_type=’record’ 时，表示被锁定的记录号；否则为 null |
| lock_data | varchar(8192) | yes | 当 lock_type=’record’ 时，表示锁定行的主键；否则为null |
- 锁的模式有如下：
- 行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。
- 表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
2.4.3 innodb_lock_waits
锁等待的对应关系。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| requesting_trx_id | varchar(18) | NO | 正在请求锁的事务ID |
| requested_lock_id | varchar(81) | NO | 正在请求的锁ID |
| blocking_trx_id | varchar(18) | NO | 当前拥有锁的事务ID |
| blocking_lock_id | varchar(81) | NO | 当前拥有锁的锁ID |
2.5 进程类
2.5.1 processlist
MySQL进程列表指示服务器中执行的线程集当前正在执行的操作，PROCESSLIST表是进程信息的一个来源，其记录正在运行的线程的各种信息。该表对应的列说明如下：
| 列名 | 类型 | 是否为空 | 说明 |
|---|---|---|---|
| id | bigint(21) | no | |
| user | varchar(32) | no | 执行语句的 MySQL 用户 |
| host | varchar(64) | no | 执行语句的客户端的主机名 |
| db | varchar(64) | yes | 线程的默认数据库 |
| command | varchar(16) | no | 执行的命令类型 |
| time | int(7) | no | 线程处于当前状态的时间，以秒为单位 |
| state | varchar(64) | yes | 线程状态 |
| info | longtext | yes | 线程正在执行的语句，如果不执行任何语句，其值为NULL |
三、基本使用
3.1 查询MySQL某个数据库下所有的表信息
SELECT
TABLE_NAME,-- 表名称
TABLE_TYPE,-- 表类型
AUTO_INCREMENT,-- 自增主键值
TABLE_SCHEMA,-- 数据库
ENGINE,-- 存储引擎
TABLE_ROWS,-- 数据行数
CREATE_TIME,-- 创建时间
update_time,-- 更新时间
IFNULL( table_comment, table_name ) AS table_comment -- 表注释
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = '数据库名称'
ORDER BY table_name
如果不想指定具体的数据库而是当前登录数据库可以通过下面方式解决：
SELECT
TABLE_NAME,-- 表名称
TABLE_TYPE,-- 表类型
AUTO_INCREMENT,-- 自增主键值
TABLE_SCHEMA,-- 数据库
ENGINE,-- 存储引擎
TABLE_ROWS,-- 数据行数
CREATE_TIME,-- 创建时间
update_time,-- 更新时间
IFNULL( table_comment, table_name ) AS table_comment -- 表注释
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA =(SELECT DATABASE())
ORDER BY table_name;
3.2 查看整个实例占用空间
SELECT
concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB
FROM
information_schema.TABLES;
3.3 查看各个库占用空间
SELECT
TABLE_SCHEMA,
concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size
FROM
information_schema.TABLES
GROUP BY
TABLE_SCHEMA
ORDER BY
data_length DESC;
3.4 查看某个表占用空间
SELECT
concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB
FROM
information_schema.TABLES
WHERE
table_schema = (SELECT DATABASE())
-- TABLE_SCHEMA = '数据库名称'
AND table_name = '表名'
3.5 查询某个数据表字段列和属性信息
select
column_name,-- 列名称
column_type, -- 列类型
ifnull(column_default,'') AS column_default, -- 字段默认值
if(is_nullable = 'no','0','1') AS is_nullable, -- 是否空
if(is_nullable = 'no' AND column_key != 'PRI','1','0') AS is_required, -- 是否必须
if(column_key = 'pri','1','0') AS is_pk, -- 是否主键
if(extra = 'auto_increment','1','0') AS is_increment, -- 是否自增
data_type, -- 数据类型
ifnull(column_comment,'') as '字段注释'
from
information_schema.columns
where
table_schema = (select database()) and table_name = 'sys_upms_user'
ORDER BY ordinal_position;
3.6 查询产生锁的具体sql
SELECT
a.trx_id 事务 id,a.trx_mysql_thread_id 事务线程 id,a.trx_query 事务 SQL
FROM
INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a
WHERE
b.lock_trx_id = a.trx_id;
3.7 查看所有线程信息
SELECT * FROM information_schema.PROCESSLIST;
3.8 查看非睡眠线程信息
SELECT * FROM information_schema.PROCESSLIST WHERE command != 'sleep';
