MySQL的information_schema库下的常用sql

select * from CHARACTER_SETS;

select * from COLLATIONS;

select * from `COLUMNS`;

select * from `TABLES`;

select * from USER_PRIVILEGES;

select * from `PARTITIONS`;

select * from INNODB_INDEXES;

information_schema.TABLES

统计数据库和表的大小，主要是借助 information_schema 库的 TABLES 表:

SELECT
	* 
FROM
	information_schema.TABLES;

主要字段分别是：

TABLE_SCHEMA :数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小，以字节为单位
INDEX_LENGTH：索引大小，以字节为单位

查看该数据库实例下所有库大小(MB为单位)

SELECT
	sum( data_length )/ 1024 / 1024 AS data_length,
	sum( index_length )/ 1024 / 1024 AS index_length,
	sum( data_length + index_length )/ 1024 / 1024 AS sum 
FROM
	information_schema.TABLES;

查看该实例下各个库大小(MB为单位)

SELECT
	table_schema,
	sum( data_length + index_length )/ 1024 / 1024 AS total_mb,
	sum( data_length )/ 1024 / 1024 AS data_mb,
	sum( index_length )/ 1024 / 1024 AS index_mb,
	count(*) AS TABLES,
	curdate() AS today 
FROM
	information_schema.TABLES 
GROUP BY
	table_schema 
ORDER BY
	2 DESC;

查看表大小(MB为单位)

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 = '数据库名' 
	AND table_name = '表名';