MySQL Library and table-occupied spatial queries

1. View all library sizes under the DB instance, and the result is in megabytes

SelectTable_schema,sum(data_length)/1024x768/1024x768  asData_length,sum(index_length)/1024x768/1024x768 asIndex_length,sum(Data_length+Index_length)/1024x768/1024x768  as sum  fromInformation_schema.tables;

2. View the size of each library under this instance

SelectTable_schema,sum(Data_length+Index_length)/1024x768/1024x768  asTOTAL_MB,sum(data_length)/1024x768/1024x768  asDATA_MB,sum(index_length)/1024x768/1024x768  asINDEX_MB,Count(*) asTables, Curdate () asToday fromInformation_schema.tablesGroup  byTable_schemaOrder  by 2 desc;

3. View the size of a single library

SelectConcattruncate(sum(data_length)/1024x768/1024x768,2),'MB') asData_size,concat (truncate(sum(max_data_length)/1024x768/1024x768,2),'MB') asMax_data_size,concat (truncate(sum(Data_free)/1024x768/1024x768,2),'MB') asData_free,concat (truncate(sum(index_length)/1024x768/1024x768,2),'MB') asindex_size fromInformation_schema.tableswhereTable_schema= 'jwtnew2017';

4. View the status of a single table

Table  from where = ' T_user '

5. View the status of all tables under the library

 UseInfomation_schema;SelectTABLE_NAME, (data_length/1024x768/1024x768) asDATA_MB, (index_length/1024x768/1024x768)  asINDEX_MB, ((data_length+Index_length)/1024x768/1024x768) asALL_MB, Table_rows fromTableswhereTable_schema= 'MyDB'

MySQL Library and table-occupied spatial queries