->format_bytes(index_length)AS'indexSize',->format_bytes(data_length+index_length)AS'totalSize'->FROMinformation_schema.tables->WHEREtable_schema=DATABASE()->ANDtable_name='test';+------------------+------------
其中,username为你的mysql用户名,然后输入密码进行登录。 步骤2:查询information_schema表中的tables信息 接下来,我们需要查询information_schema表中的tables信息,找到数据长度和索引长度。在mysql中,可以使用以下sql语句查询: SELECTtable_name,table_rows,data_length,index_lengthFROMinformation_schema.tablesWHEREtable_sc...
'sys', 'mysql', 'performance_schema' ); PS: information_schema 中的数据默认不是实时的数据，如...
TABLE_ROWS AS '数据量', TRUNCATE(DATA_LENGTH/1024/1024, 2) AS '数据容量(MB)', TRUNCATE(INDEX_LENGTH/1024/1024, 2) AS '索引容量(MB)', CREATE_TIME AS '创建时间', UPDATE_TIME AS '更新时间', Table_comment AS '表注释' FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='数据库名' ...
-- 1. 进入information_schema 数据库(存放了其他的数据库的信息) use information_schema; -- 2. 查询所有数据的大小: select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables ; -- 3. 查看实例下所有数据库的空间占用情况 select table_schema ,concat(round(...
information_schema.tables存储了数据表的元数据信息,下面对常用的字段进行介绍: table_schema: 记录数据库名; table_name: 记录数据表名; engine : 存储引擎; table_rows: 关于表的粗略行估计; data_length : 记录表的大小(单位字节); index_length : 记录表的索引的大小; ...
information_schema.TABLES GROUP BY TABLE_SCHEMA ORDER BY data_length DESC; # 查看某个表占用空间 SELECT concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB, concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB FROM ...
GRANT SELECT ON information_schema.TABLES TO 'your_user'@'localhost'; 问题3:查询速度慢 原因:如果数据库表非常多,查询速度可能会变慢。 解决方法:可以限制查询的范围,或者优化查询语句。 代码语言:txt 复制 SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', ROUND(((DATA_LENGTH + INDEX_LENG...
TABLE_SCHEMA This is always NULL. TABLE_NAME This is always NULL. LOGFILE_GROUP_NAME For InnoDB: This is always NULL. For NDB: The name of the log file group to which the log file or data file belongs. LOGFILE_GROUP_NUMBER For InnoDB: This is always NULL. For NDB: For...
/1024/1024,2))as'数据容量(GB)',sum(truncate(index_length/1024/1024/1024,2))as'索引容量(GB)',sum(truncate(data_free/1024/1024/1024,2))as'碎片空间(GB)',sum(truncate((data_length+index_length+data_free)/1024/1024/1024,2))as'总容量(GB)'frominformation_schema.tablesgroupbytable_schema...