How to View mysql tablespaces and indexes _ MySQL

How to View mysql tablespaces and indexes: bitsCN. com1. view indexes

(1) unit: 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) The unit is 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" indicates the database you want to view.



2. View tablespaces

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. view information about all tables in the database

Select concat (table_schema, '.', table_name) AS 'table name ',
CONCAT (ROUND (table_rows/Rows), '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'; bitsCN.com