MySQLテーブルのデータ件数はinformation_schema.tablesから取得できる
前提
- MySQL 5.7.11
やりたいこと
MySQL上のテーブルに COUNT(*) することなく、テーブルの行数を知りたい。
解決法
information_schema.tables テーブルから、テーブルの名前やデータ件数などのメタデータが取得可能。
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec) mysql> select * from tables where table_schema = 'information_schema' and table_name = 'tables'; +---------------+--------------------+------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------------+------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | def | information_schema | TABLES | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 9441 | 0 | 16757775 | 0 | 0 | NULL | 2017-07-09 23:15:45 | NULL | NULL | utf8_general_ci | NULL | max_rows=1777 | | +---------------+--------------------+------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ 1 row in set (0.01 sec)
table_row カラムにデータ件数が格納されている。このテーブルを利用すれば、任意のテーブルの行数を COUNT(*) することなく取得が可能。
mysql> select * from tables where table_schema = 'foo_db' and table_name = 'bar_table'; +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------+ | def | foo_db | bar_table | BASE TABLE | InnoDB | 10 | Dynamic | 19303 | 1336 | 25804800 | 0 | 4767744 | 4194304 | NULL | 2017-06-23 00:25:12 | 2017-07-03 11:36:54 | NULL | utf8mb4_bin | NULL | | | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------+ 1 row in set (0.00 sec)
このテーブルを利用することで、 データ件数が一定数以上のテーブルを抽出する みたいなこともできる。ただし、このテーブルには mysql スキーマや information_schema スキーマなどの管理データやメタデータスキーマも含まれるのでそれを必ず除外する必要がある。対象のスキーマを条件に入れて検索すること。
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema in ('foo_db', 'bar_db')
AND table_rows > 1000;
information_schema 以下には他にも面白そうなテーブルがたくさんあるのでもうちょっと深く掘りたい。
（2017/07/11 13:13 追記）コメント欄で id:n-3104さんから指摘頂きましたが、 table_rows で取得できる行数は概算の値で、その瞬間での正確な値ではありません。概算量が取得できれば問題ないような要件であれば活用できますが、正確な量が知りたい場合は各TABLEに COUNT(*) をかける必要があります。
また、 ANALYZE TABLE を実行することで、テーブル単位での同期をかけることが可能です。
(追記ここまで)
