[mysql]查看数据库状态data的大小和index大小的语句

最新推荐文章于 2023-04-18 14:21:55 发布

pmdream

最新推荐文章于 2023-04-18 14:21:55 发布

阅读量370

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/pmdream/article/details/119033414

版权

sql 专栏收录该内容

39 篇文章

订阅专栏

本文探讨了数据库中发现的异常现象——某些表的数据量小于其索引大小，针对此现象，提出潜在的优化建议，分享作为开发者如何从DBA经验中学到的数据管理技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
	TABLE_SCHEMA AS database_name,
	TABLE_NAME AS table_name,
	TABLE_ROWS AS table_rows,
	ENGINE AS table_engine,
	ROUND(( DATA_LENGTH )/ 1024.0 / 1024, 2 ) AS Data_MB,
	ROUND(( INDEX_LENGTH )/ 1024.0 / 1024, 2 ) AS Index_MB,
	ROUND(( DATA_LENGTH + INDEX_LENGTH )/ 1024.0 / 1024, 2 ) AS Total_MB,
	ROUND(( DATA_FREE )/ 1024.0 / 1024, 2 ) AS Free_MB 
FROM
	information_schema.`TABLES` AS T1 
WHERE
	T1.`TABLE_SCHEMA` NOT IN ( 'performance_schema', 'mysql', 'information_schema', 'sys' ) 
	AND T1.TABLE_NAME is not null
ORDER BY
	T1.`TABLE_ROWS` DESC 
	LIMIT 200;