MySQL统计信息收集

分类 Mysql 标签 Mysql 评论:
  1. MySQL统计信息收集
    1. 统计每个库大小
    2. 统计库里每个表的大小
    3. 统计所有数据库的大小
    4. 统计客户端连接数
    5. MySQL在什么状态收集统计信息

MySQL统计信息收集

1、重启MySQL数据库
2、遍历 information_schema.tables表
这个库里面的表只支持select查询

每天读取一下information_schema.tables的信息,读这个表:相当于完成了一次表信息的收集

统计每个库大小

mysql> use information_schema ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT TABLE_SCHEMA,SUM(DATA_LENGTH)/1024/1024/1024 as DATA_LENGTH,SUM(INDEX_LENGTH)/1024/1024/1024
    as INDEX_LENGTH,SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as SUM_DATA_INDEX FROM information_schema.TABLES
    WHERE TABLE_SCHEMA!='information_schema' AND TABLE_SCHEMA!='mysql' GROUP BY TABLE_SCHEMA;

+--------------------+----------------+----------------+----------------+
| TABLE_SCHEMA       | DATA_LENGTH    | INDEX_LENGTH   | SUM_DATA_INDEX |
+--------------------+----------------+----------------+----------------+
| performance_schema | 0.000000000000 | 0.000000000000 | 0.000000000000 |
| sys                | 0.000015258789 | 0.000000000000 | 0.000015258789 |
| test               | 0.000015258789 | 0.000000000000 | 0.000015258789 |
+--------------------+----------------+----------------+----------------+
2 rows in set (0.08 sec)

mysql>

统计库里每个表的大小

mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,SUM(DATA_LENGTH+INDEX_LENGTH) AS TOTAL_SIZE
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='test' GROUP BY TABLE_NAME;
+------------+-------------+--------------+------------+
| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+------------+-------------+--------------+------------+
| t1         |       16384 |            0 |      16384 |
+------------+-------------+--------------+------------+
1 row in set (0.00 sec)

统计所有数据库的大小

mysql> select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;
+----------------------------------------------+
| sum(data_length+index_length)/1024/1024/1024 |
+----------------------------------------------+
|                               0.002552489750 |
+----------------------------------------------+
1 row in set (0.04 sec)

index_length 都为0, 表示只有主键,或是没索引

统计客户端连接数

[root@lyucan ~]# netstat -anlp|grep 3306|grep tcp|awk '{print $5}'|awk -F: '{print $1}'|sort|uniq -c|sort -nr|head -n20
      3 119.123.76.61
      1
[root@lyucan ~]# mysql -uroot -p -e "show processlist"|awk '{print $3}' |awk -F: '{print $1}' |sort |uniq -c |sort -nr
Enter password:
      3 119.123.76.61
      2 localhost
      1 Host
[root@lyucan ~]#

MySQL在什么状态收集统计信息

  1. 访问information_schema.tables 会收集
  2. 数据库重启
  3. show table status;
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com

©2016-2023 Daryl