Es gibt einige SQL Queries mit dem man sich Informationen über die Tabellen und deren Zustand holen kann. Die Informationen sind immer wieder mal nützlich.
|
1 2 3 |
SELECT table_schema, table_name, engine,row_format, table_rows FROM information_schema.TABLES WHERE table_schema = <span class="crayon-s">'table_name'</span>; |
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Informationen über die Größe der Tabellen (maschinell weiter verarbeitbar) SELECT table_schema AS "database", table_name AS "tablename", round(((data_length + index_length) / 1024 / 1024), 2) AS total_size, round(((data_length) / 1024 / 1024), 2) AS data_size, round(((index_length) / 1024 / 1024), 2) AS index_size , round(index_length/data_length,2) AS data_index_ratio, table_rows, engine, row_format, create_options, update_time, check_time, table_collation, table_comment FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- Informationen über die Größe der Tabellen (Gut lesbar und Größenangabe automatisch in GB, MB, KB, Byte) SELECT table_schema AS "Database", table_name AS "Tables", CASE WHEN (data_length+index_length) < 1024 THEN concat((data_length+index_length),'Bytes') WHEN (data_length+index_length) < (1024*1024) THEN concat(round((data_length+index_length)/(1024),2),' KB') WHEN (data_length+index_length) < (1024*1024*1024) THEN concat(round((data_length+index_length)/(1024*1024),2),' MB') WHEN (data_length+index_length) < (1024*1024*1024*1024) THEN concat(round((data_length+index_length)/(1024*1024*1024),2),' GB') ELSE 0 END AS total_size, CASE WHEN data_length < 1024 THEN concat(data_length,'Bytes') WHEN data_length < (1024*1024) THEN concat(round(data_length/(1024),2),' KB') WHEN data_length < (1024*1024*1024) THEN concat(round(data_length/(1024*1024),2),' MB') WHEN data_length < (1024*1024*1024*1024) THEN concat(round(data_length/(1024*1024*1024),2),' GB') ELSE 0 END AS data_size, CASE WHEN index_length < 1024 THEN concat(index_length,'Bytes') WHEN index_length < (1024*1024) THEN concat(round(index_length/(1024),2),' KB') WHEN index_length < (1024*1024*1024) THEN concat(round(index_length/(1024*1024),2),' MB') WHEN index_length < (1024*1024*1024*1024) THEN concat(round(index_length/(1024*1024*1024),2),' GB') ELSE 0 END AS index_size, round(index_length/data_length,2) AS data_index_ratio, table_rows AS "Zeilen", engine, row_format, create_options, update_time, check_time, table_collation, table_comment FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; |
MySQL bietet auch noch weitere Befehle um Informationen zu den Tabellen zu bekommen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Tabellen SHOW TABLE STATUS SHOW OPEN TABLES -- Infos über die Volltextsuche SHOW GLOBAL VARIABLES LIKE 'ft_%'; -- temporäre Tabellen SHOW GLOBAL VARIABLES LIKE '%table_size'; SHOW GLOBAL STATUS LIKE 'Created_tmp_%'; -- Infos zu innoDB SHOW ENGINE INNODB STATUS; SHOW GLOBAL STATUS LIKE 'inno%'; |