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
を実行することで、テーブル単位での同期をかけることが可能です。
(追記ここまで)