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

はじめに

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

5.7でも同様になる模様。

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

やってみたのは以下のような感じでinformation_schema.tablestable_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の各テーブルの件数を取得するときにschema_infomation.tablesのtable_rowsの値は正確ではないので参照してはいけない旨を書いた。
一括で取得するにはselect count(*)を実行するしかない模様。

参考

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です