dshimizu/blog

アルファ版

MySQLのInnoDBテーブルではinfomation_schema.tablesのtable_rowsが正確ではないことを今さら知った

MySQL Tech

いろいろネット上で調べていると既知のもののようで何を今さら...という感じではあるが、MySQLで全テーブルのレコード数を一括で取得するのに手軽にやれないかと思ってinfomation_schema.tablesのtable_rowsを参照したら、マスター/スレーブ間で件数が全然違って焦った。ちなみにテーブルの行数をcountで取得したら一致した。何故じゃ...と思ったが結論から言うとそういう仕様だった。

MySQLのInnoDBのテーブルで行数を数えるには

やってみたのは以下のような感じでinformation_schema.tablesのtable_rowsを参照してみたが、これだとInnoDBでは結構なブレが発生する。

> select
  table_name
  , table_rows
from
  information_schema.tables
where
  table_schema = 'sampledb';

ちなみにshowコマンドも内部ではinformation_schema.tablesを参照しているので同じく正確な値は取れない。

> show table status from sampledb;

ということで正確な件数を取得するには以下のようにselect count(*)を使うしかない、と思われる。

> select count(*) from sampledb.sampletable

全テーブルの件数を一括で取得するにはスクリプトを作成するしかなさそう。

ちなみに話は変わるけどMySQL5.6ならmysql_config_editorを使って暗号化された接続情報ファイルを作成できる。これを使うことでパスワードをスクリプト内に書く必要はなくなる。MySQL5.5ならスクリプトとかにパスワードとかの接続情報を書くしかないと思う。

$ mysql_config_editor set --login-path=test --host=localhost --user=test --password
Enter password:

$ mysql_config_editor print --all
[test]
user = test
password = *****
host = localhost

スクリプトは以下のような感じ。

  • get-table-count.sh
    • #!/bin/bash

# DB接続情報情報(～MySQL5.5)
DB_USER=
DB_PASSWORD=hogehoge
DB_NAME=sampledb
DB_HOST=localhost

# DB接続情報情報(MySQL5.6～)
MYSQL_LOGIN_PATH=test

# テーブル一覧取得(～MySQL5.5)
#TABLES=(`mysql -u ${DB_USER} -p${DB_PASSWORD} -D ${DB_NAME} -N -e "show tables"`)

# テーブル一覧取得(MySQL5.6～)
TABLES=(`mysql --login-path=${MYSQL_LOGIN_PATH} -N -e "show tables"`)

#echo ${TABLES}

# show tables に出力されるテーブルの件数を一括で取得するためのSQL文生成
for TABLE in ${TABLES[@]};
do
    # テーブル1個ずつ件数を取得して結合するSQL
    SQL="${SQL} SELECT '${TABLE}' AS table_name, COUNT(*) AS table_row_cnt FROM ${TABLE} UNION ALL"
done

# 上記の方法で最後に作成したSQL文にも UNION ALL が付与されてしまうのでそれだけ削除
SQL="$(echo $SQL | sed -e 's/ UNION ALL$//')"
#echo ${SQL}

# テーブルの件数を一括で取得するSQL 文実行(～MySQL5.5)
#mysql  -u ${DB_USER} -p${DB_PASSWORD} -D ${DB_NAME} -e "${SQL}"

# テーブルの件数を一括で取得するSQL 文実行(MySQL5.6～)
mysql --login-path=${MYSQL_LOGIN_PATH} -e "${SQL}"

実行してみる。

./get-table-count.sh

まとめ

少なくともMySQLの5.5,5.6,5.7ではInnoDBの各テーブルの件数を取得するときにinfomation_schema.tablesのtable_rowsの値は正確ではないので参照してはいけない旨を書いた。 一括で取得するには各テーブルに対してselect count(*)を実行するしかない模様。

参考