免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1242 | 回复: 0

统计mysql每一个库的相关信息 [复制链接]

论坛徽章:
0
发表于 2011-12-23 03:56 |显示全部楼层

 

sql代码:perschema.sql

作用:

    1、统计库的大小,数据大小,索引大小,表的数量。

    2、按库,引擎,校验字符集分类并统计各种类型含有的表的数量

    3、统计指定库每个表大小,数据大小,索引大小,引擎,数据行的格式,表的行数,平均每行的长   度。

     4、统计含有text,blog大字段的表。

     5、统计每个表含有的大于255长度的varchar大字段,字段的最大长度。

     6、统计库中含有bigint字段且为auto_increment的表名,字段名字。

     7、统计库中含有的存储过程总数,视图总数,触发器总数。

  1. SELECT NOW(), VERSION();

  2. # Per Schema Queries

  3. SET @schema = IFNULL(@schema,DATABASE());

  4. # One Line Schema Summary
  5. SELECT table_schema,
  6.          SUM(data_length+index_length)/1024/1024 AS total_mb,
  7.          SUM(data_length)/1024/1024 AS data_mb,
  8.          SUM(index_length)/1024/1024 AS index_mb,
  9.          COUNT(*) AS tables,
  10.          CURDATE() AS today
  11. FROM information_schema.tables
  12. WHERE table_schema=@schema
  13. GROUP BY table_schema;

  14. # Schema Engine/Collation Summary
  15. SELECT table_schema,engine,table_collation,
  16.          COUNT(*) AS tables
  17. FROM information_schema.tables
  18. WHERE table_schema=@schema
  19. GROUP BY table_schema,engine,table_collation;


  20. # Schema Table Usage
  21. SELECT @schema as table_schema, CURDATE() AS today;
  22. SELECT if(length(table_name)>20,concat(left(table_name,18),'..'),table_name) AS table_name,
  23.          engine,row_format as format, table_rows, avg_row_length as avg_row,
  24.          round((data_length+index_length)/1024/1024,2) as total_mb,
  25.          round((data_length)/1024/1024,2) as data_mb,
  26.          round((index_length)/1024/1024,2) as index_mb
  27. FROM information_schema.tables
  28. WHERE table_schema=@schema
  29. ORDER BY 6 DESC;

  30. # Schema Table BLOB/TEXT Usage
  31. select table_schema,table_name,column_name,data_type
  32. from information_schema.columns
  33. where table_schema= @schema
  34. and ( data_type LIKE '%TEXT' OR data_type like '%BLOB');

  35. # Large varchars
  36. select table_schema,table_name,column_name,character_maximum_length from information_schema.columns where data_type='varchar' and character_maximum_length > 255 and table_schema = @schema;

  37. select table_schema,table_name,column_name,data_type,extra from information_schema.columns where data_type='bigint' and extra like '%auto_increment%' and table_schema = @schema;

  38. select 'routines',count(*) from information_schema.routines where routine_schema= @schema
  39. union
  40. select 'views',count(*) from information_schema.views where table_schema= @schema
  41. union
  42. select 'triggers',count(*) from information_schema.triggers where trigger_schema= @schema;

  43. set @schema = NULL;
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP