mysql统计存储空间_MySQL状态（存储空间和数据量）查询命令

本文记录一些检测MySQL性能，配置的命令

status命令

status命令可以列出MySQL数据库的基本参数。比如编码信息，MySQL版本，查询次数等等。

mysql> status

--------------

mysql Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 790

Current database: snapchat

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.1.61 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 1 day 4 hours 19 min 19 sec

Threads: 1 Questions: 5796 Slow queries: 0 Opens: 282 Flush tables: 1 Open tables: 50 Queries per second avg: 0.56

show status命令

该语句相对于status列出了更多的数据库配置信息。

查询本次MySQL启动后执行的SELECT语句的次数

show status like 'com_select';

查看MySQL服务器的线程信息

show status like 'Thread_%';

show variables命令

show variables命令用于列出MySQL的所有配置信息，按照字母升序排列。可以使用模糊匹配进行查询，如下语句查询编码信息：

show variables like 'char%';

查看端口

show global variables like 'port';

MySQL系统表information_schema

information_schema库是MySQL的系统库，里面的表都是只读表，这些表实际是视图，MySQL自己进行维护，该数据库中存放了其他数据库的相关信息。

在使用下面的sql语句之前，需要切换数据库：

use information_schema;

information_schema系统表的基本字段如下：

desc information_schema.tables;

+-----------------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------------+---------------------+------+-----+---------+-------+

| TABLE_CATALOG | varchar(512) | YES | | NULL | |

| 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(80) | NO | | | |

+-----------------+---------------------+------+-----+---------+-------+

查询总体信息

# 查询所有数据的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data

from tables;

# 查看指定数据库的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data

from tables

where table_schema='databse_name';

# 查看指定数据库的某个表的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data

from tables

where table_schema='databse_name' and table_name='table_name';

查询各表存储空间大小

倒序显示某个数据库中所有表数据和索引所占空间大小，sql如下，其中database_name为查询的数据库：

select

TABLE_NAME,

concat(truncate(data_length/1024/1024,2),' MB') as data_size,

concat(truncate(index_length/1024/1024,2),' MB') as index_size

from information_schema.tables

where TABLE_SCHEMA = 'databse_name'

group by TABLE_NAME

order by data_length desc;

查看数据库中所有表的记录数

查看数据库中所有表的记录数

select table_name,table_rows

from information_schema.tables

where TABLE_SCHEMA = 'snapchat'

order by table_rows desc;

同时查询存储和数据量

select

table_name,

concat(truncate(data_length/1024/1024,2),' MB') as data_size,

concat(truncate(index_length/1024/1024,2),' MB') as index_size,

table_rows

from information_schema.tables

where table_schema= 'snapchat'

group by table_name

order by data_length desc;

查询某个表的详细信息

show table status

from snapchat

where name = 'income_detail_account' \G;