MySQL INFORMATION_SCHEMA

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

常用SQL
查看一个库的所有表

SELECT Concat('select * from ', table_name, ';')
FROM INFORMATION_SCHEMA.tables
WHERE table_schema = 'hive121'
ORDER BY table_name;

查看表占用的存储空间

SELECT
  Concat(table_schema, '.', table_name)                                      AS "Name",
  Concat(Round(table_rows / 1000000, 2), 'M')                                AS "Rows",
  Concat(Round(data_length / (1024 * 1024 * 1024), 2), 'G')                  AS "Row 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",
  Round(index_length / data_length, 2)                                          "Row / Index Ratio"
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;

ref
https://dev.mysql.com/doc/refman/5.7/en/information-schema.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注