mysql表结构表空间和索引的查询

mysql表结构表空间和索引的查询

1.查看索引

mysql 表空间及索引的查看方法,mysql索引

1.查看索引  (1)单位是GB 
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ‘ 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), 2), ‘ 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), 2), ‘ 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’, 
CONCAT(ROUND(table_rows/1000000,2),’M’) AS ‘Number of Rows’, 
CONCAT(ROUND(data_length/(1024*1024*1024),2),’G’) AS ‘Data Size’, 
CONCAT(ROUND(index_length/(1024*1024*1024),2),’G’) AS ‘Index Size’ , 
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),’G’)
AS’Total’FROM information_schema.TABLES WHERE table_schema LIKE
‘database’;

表空间及索引的查看方法,mysql索引
1.查看索引(1)单位是GB SELECT
CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ‘ GB’) AS ‘Total
Index Size’ FROM…

 

(1)单位是GB

1.查询表的结构信息

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ‘ GB’) AS
‘Total Index Size’ FROM information_schema.TABLES WHERE table_schema
LIKE ‘database’;
+——————+
| Total Index Size |
+——————+
| 1.70 GB |
+——————+

Sql代码  

(2)单位是MB

desc tableName;  

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ‘ MB’) AS
‘Total Index Size’ FROM information_schema.TABLES WHERE table_schema
LIKE ‘database’;

  

其中“database”为你所要查看的数据库

show columns from tableName;  

2.查看表空间

  

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ‘ GB’) AS
‘Total Data Size’
FROM information_schema.TABLES WHERE table_schema LIKE ‘database’;
+—————–+
| Total Data Size |
+—————–+
| 3.01 GB |
+—————–+

describe tableName  

3.查看数据库中所有表的信息

  上面的结果返回的结果是一样的。

SELECT CONCAT(table_schema,’.’,table_name) AS ‘Table Name’,
CONCAT(ROUND(table_rows/1000000,2),’M’) AS ‘Number of Rows’,
CONCAT(ROUND(data_length/(1024*1024*1024),2),’G’) AS ‘Data Size’,
CONCAT(ROUND(index_length/(1024*1024*1024),2),’G’) AS ‘Index Size’
,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),’G’)
AS’Total’FROM information_schema.TABLES WHERE table_schema LIKE
‘database’;

2 查询表的列信息。

(1)单位是GB SELECT
CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ‘ GB’) AS ‘Total
Index Size’ FROM information_schema.TABLES WHERE table_schema LIKE
‘da…

Sql代码  

select * from   

information_schema.columns   

where table_name=’tableName’;  

 3 查看库中所有的库

Sql代码  

SELECT LOWER(schema_name) schema_name  

FROM  

 information_schema.schemata  

WHERE  

 schema_name NOT IN (  

 ‘mysql’,  

 ‘information_schema’,  

 ‘test’  

)  

 4 查询某个库中所有的表

Sql代码  

SELECT table_name, create_time updated_at, table_type, ENGINE,
table_rows num_rows, table_comment, CEIL(data_length / 1024 / 1024)
store_capacity  

FROM  

 information_schema.TABLES  

WHERE table_schema = ‘schema_name’ AND table_name NOT LIKE ‘tmp#_%’
ESCAPE ‘#’  

 5 查看某一个库下某一个表的所有字段

Sql代码  

SELECT  

    lower(column_name) column_name,