曹耘豪的博客

MySQL之获取所有表的行数并过滤和排序

  1. 一、通过information_schema获取行数
  2. 二、通过生成SQL执行
  3. 三、优化SQL生成并保存到中间表
  4. InnoDB里的count(*)

一、通过information_schema获取行数

对于MyISAM引擎，这个没有问题，但对于InnoDB，这个值只是估计值，和实际差距较大

1
2
3
SELECT TABLE_SCHEMA, table_name, table_rows from information_schema.`TABLES`
where table_schema NOT IN ('information_schema','performance_schema','mysql', 'sys') and table_rows > 0
ORDER BY TABLE_SCHEMA, table_rows desc;

问题：

二、通过生成SQL执行

1
2
3
SELECT CONCAT('SELECT "', TABLE_SCHEMA, '.', TABLE_NAME, '", COUNT(*) FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` UNION ALL' ) EXEC_SQL 
FROM INFORMATION_SCHEMA.TABLES
where table_schema NOT IN ('information_schema','performance_schema','mysql', 'sys')

先生成SQL，然后将生成的SQL语句再次执行，执行前需去掉最后一个UNION ALL

问题：

三、优化SQL生成并保存到中间表

1
2
3
4
5
6
7
8
9
10
11
12
13
SET group_concat_max_len = 1048576; -- 如果表很多，不加这个会有问题

SELECT CONCAT("select * from (", 
              GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
                                  ' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION '), 
              ") A where table_rows > 0 order by table_schema, table_rows desc")

INTO @CountSQL -- 将生成的SQL插入到临时表

FROM (SELECT table_schema db,table_name tb FROM information_schema.tables 
      WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A; 

PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

参考：

InnoDB里的count(*)

由于MVCC，count(*)只能计算当前事务可见的行数，所以需要逐行扫描，慢但准确

  技术 / 数据库