查看MySql数据库物理文件存放位置
show variables
like
'datadir'
查所有数据库占用空间大小
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;
查指定数据库占用空间大小
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 = ‘im_db’
group by TABLE_NAME
order by data_length desc;
MYSQL:查看的数据库表空间
-
/*1.查看索引
-
-
（1）单位是GB*/
-
-
SELECT
CONCAT(ROUND(
SUM
(index_length)/(1024*1024*1024), 6),
‘ GB’
)
AS
‘Total Index Size’
-
FROM
information_schema.TABLES
WHERE
table_schema
LIKE
‘database’
;
-
/*
-
+
——————+
-
| Total
Index
Size
|
-
+
——————+
-
| 1.70 GB |
-
+
——————+
-
*/
-
/*
-
（2）单位是MB
-
*/
-
SELECT
CONCAT(ROUND(
SUM
(index_length)/(1024*1024), 6),
‘ MB’
)
AS
‘Total Index Size’
-
FROM
information_schema.TABLES
WHERE
table_schema
LIKE
‘database’
;
-
/*
-
其中“
database
”为你所要查看的数据库
-
*/
-
-
/*
-
2.查看表空间
-
*/
-
SELECT
CONCAT(ROUND(
SUM
(data_length)/(1024*1024*1024), 6),
‘ GB’
)
AS
‘Total Data Size’
-
FROM
information_schema.TABLES
WHERE
table_schema
LIKE
‘database’
;
-
/*
-
+
—————–+
-
| Total Data
Size
|
-
+
—————–+
-
| 3.01 GB |
-
+
—————–+
-
*/
-
/*
-
3.查看数据库中所有表的信息
-
*/
-
SELECT
CONCAT(table_schema,
‘.’
,table_name)
AS
‘Table Name’
,
-
table_rows
AS
‘Number of Rows’
,
-
CONCAT(ROUND(data_length/(1024*1024*1024),6),
‘ G’
)
AS
‘Data Size’
,
-
CONCAT(ROUND(index_length/(1024*1024*1024),6),
‘ G’
)
AS
‘Index Size’
,
-
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),
‘ G’
)
AS
‘Total’
-
FROM
information_schema.TABLES
-
WHERE
table_schema
LIKE
‘database’
;