阅读目录
说明- TABLE_NAME ：表名字；
- DATA_LENGTH ： 数据大小；
- INDEX_LENGTH ：索引大小；
- TABLE_ROWS ： 记录数量；
- TABLE_SCHEMA ： 数据库名字；
- ENGINE：所使用的存储引擎；
information_schema ：
是mysql自带的，它提供了访问数据库元数据的方式，元数据是关于数据的数据，
如数据库名或表名，列的数据类型，或访问权限等。
有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
一个表占用空间的大小，相当于是 数据大小 + 索引大小；
SELECT
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH,
(DATA_LENGTH + INDEX_LENGTH) AS length,
TABLE_ROWS,
concat(
round(
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
3
),
'MB'
) AS total_size
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA = 'database_name'
ORDER BY
length DESC;
mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='text' order by length desc;
+------------------------------+-------------+--------------+--------+------------+------------+
| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | length | TABLE_ROWS | total_size |
+------------------------------+-------------+--------------+--------+------------+------------+
| new_comments | 16384 | 81920 | 98304 | 6 | 0.094MB |
| admin_operation_log | 81920 | 16384 | 98304 | 195 | 0.094MB |
| admin_permissions | 16384 | 16384 | 32768 | 6 | 0.031MB |
| system_attribute | 16384 | 16384 | 32768 | 0 | 0.031MB |
| cate | 16384 | 16384 | 32768 | 18 | 0.031MB |
| product_sku | 16384 | 16384 | 32768 | 0 | 0.031MB |
| admin_users | 16384 | 16384 | 32768 | 0 | 0.031MB |
| product_attribute_option | 16384 | 16384 | 32768 | 2 | 0.031MB |
| admin_roles | 16384 | 16384 | 32768 | 0 | 0.031MB |
| product_attribute_and_option | 16384 | 16384 | 32768 | 0 | 0.031MB |
| product_attribute | 16384 | 16384 | 32768 | 2 | 0.031MB |
| users | 16384 | 0 | 16384 | 0 | 0.016MB |
| orders | 16384 | 0 | 16384 | 5 | 0.016MB |
| admin_permission_menu | 16384 | 0 | 16384 | 0 | 0.016MB |
| tb_score | 16384 | 0 | 16384 | 10 | 0.016MB |
| migrations | 16384 | 0 | 16384 | 4 | 0.016MB |
| likecate | 16384 | 0 | 16384 | 6 | 0.016MB |
| admin_menu | 16384 | 0 | 16384 | 12 | 0.016MB |
| sign_in_reward | 16384 | 0 | 16384 | 7 | 0.016MB |
| deepcate | 16384 | 0 | 16384 | 11 | 0.016MB |
| sign_in | 16384 | 0 | 16384 | 4 | 0.016MB |
| admin_role_users | 16384 | 0 | 16384 | 0 | 0.016MB |
| admin_role_permissions | 16384 | 0 | 16384 | 0 | 0.016MB |
| product | 16384 | 0 | 16384 | 0 | 0.016MB |
| admin_role_menu | 16384 | 0 | 16384 | 0 | 0.016MB |
| password_resets | 16384 | 0 | 16384 | 0 | 0.016MB |
+------------------------------+-------------+--------------+--------+------------+------------+
26 rows in set (0.00 sec)
mysql>