首页 / MYSQL / MySQL--查询表统计信息
MySQL--查询表统计信息
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL--查询表统计信息,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2774字,纯文字阅读大概需要4分钟。
内容图文
![MySQL--查询表统计信息](/upload/InfoBanner/zyjiaocheng/917/d12529048dfb49389f02f08a585d2aee.jpg)
=============================================================
可以用show table status 来查看表的信息,如:show table status like '%waybill5%' \G 但使用information_schema.`TABLES`更方便查看。 ? ? ============================================================== 查看非InnoDB引擎表
## 查看非InnoDB引擎表 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.`ENGINE` NOT IN ('innodb');
==============================================================
查看数据表较大的表
## 查看数据表较大的表 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') ORDER BY T1.`TABLE_ROWS` DESC LIMIT 10;
==============================================================
查看碎片较多的表
## 查看碎片较多的表 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, ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2)AS Free_Percent FROM information_schema.`TABLES` AS T1 WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema') AND ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2) >10 AND ROUND((DATA_FREE)/1024.0/1024, 2)>100 ORDER BY ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2) DESC LIMIT 10;
==============================================================
查看表当前自增值
## 查看表自增值 SELECT T2.TABLE_SCHEMA, T2.TABLE_NAME, T1.COLUMN_NAME, T1.COLUMN_TYPE, T2.AUTO_INCREMENT FROM information_schema.columns AS T1 INNER JOIN information_schema.tables AS T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE T1.EXTRA='auto_increment' AND T1.DATA_TYPE NOT LIKE '%bigint%' ORDER BY T2.AUTO_INCREMENT DESC LIMIT 100;
==============================================================
内容总结
以上是互联网集市为您收集整理的MySQL--查询表统计信息全部内容,希望文章能够帮你解决MySQL--查询表统计信息所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。