元数据查询

information_schema 库中保存了大量元数据查询的视图

tables 视图简介

保存了整个数据库中所有表的元数据

USE information_schema;

DESC tables;

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

查询常用字段

字段	说明
TABLE_SCHEMA	表所在的库名
TABLE_NAME	表名
ENGINE	存储引擎
TABLE_ROWS	数据行
AVG_ROW_LENGTH	平均行长度
INDEX_LENGTH	索引长度

实战操作

查询所有库和表的信息

SELECT TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME) FROM TABLES GROUP BY TABLE_SCHEMA;

查询所有 innodb 引擎的表

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM TABLES WHERE ENGINE='innodb';

查询world库下city表的占用空间大小

# 表大小 = 平均行长度 * 行数 + 索引长度
# 单位为字节
SELECT TABLE_NAME,AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH FROM information_schema.tables WHERE TABLE_SCHEMA='world' AND TABLE_NAME='city';

# 可将结果除以1024转换成KB
SELECT TABLE_NAME,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.tables WHERE TABLE_SCHEMA='world' AND TABLE_NAME='city';

统计所有库的大小，并按从大到小的顺序排列显示

SELECT TABLE_SCHEMA,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024) AS TOTAL_KB FROM information_schema.tables GROUP BY TABLE_SCHEMA ORDER BY TOTAL_KB DESC;

配合CONCAT()函数拼接语句或命令

# 模仿以下语句，进行数据库的分库分表备份
mysqldump -uroot -p123 world city > /bak/world_city.sql

SELECT
CONCAT("mysqldump -uroot -p123 ",TABLE_SCHEMA," ",TABLE_NAME," > /bak/",TABLE_SCHEMA,"_",TABLE_NAME,".sql")
FROM information_schema.tables;

# 模仿以下语句，进行批量生产对world库下所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE;

SELECT
CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE;")
FROM information_schema.tables;
WHERE TABLE_SCHEMA=''world;

SHOW 命令

# http://dev.mysql.com/doc/refman/5.7/en/show.html

show  databases;                          #查看所有数据库
show tables;                             #查看当前库的所有表
SHOW TABLES FROM;                        #查看某个指定库下的表
show create database world;                #查看建库语句
show create table world.city;            #查看建表语句
show grants for root@'localhost';   #查看用户的权限信息
show charset；                       #查看字符集
show collation                        #查看校对规则
show processlist;                     #查看数据库连接情况
show index from                       #表的索引情况
show status                            #数据库状态查看
SHOW STATUS LIKE '%lock%';             #模糊查询数据库某些状态
SHOW VARIABLES                         #查看所有配置信息
SHOW variables LIKE '%lock%';          #查看部分配置信息
show engines;                        #查看支持的所有的存储引擎
show engine innodb status\G;        #查看InnoDB引擎相关的状态信息
show binary logs;                    #列举所有的二进制日志
show master status;                    #查看数据库的日志位置信息
show binlog evnets in                  #查看二进制日志事件
show slave status \G;                 #查看从库状态
SHOW RELAYLOG EVENTS;                #查看从库relaylog事件信息
desc  (show colums from city);        #查看表的列定义信息