MySQLのINFORMATION_SCHEMA.TABLESにおけるテーブルの行数（レコード数）は、概算値だから正確とは限らない

SQLのパフォーマンスをテストするために、試験用にテーブルを作って大量のレコードを入れました。

その時に試験をやり直そうと思い、テーブルをリセットする局面があったのですが。ふと疑問に思い、「DELETE FROM テーブル名」と「TRANCATE TABLE テーブル名」の違いを検証していました。

その際にテーブルの集計で、「INFORMATION_SCHEMA.TABLES」で正しい値が取得できなかったので、そのことについて書きます。

動作確認のためのテストテーブル作成

今回は試験用なので、簡易的にdockerで立ち上げたMySQLを利用します。

# MySQLを立ち上げる
$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysql -d -p 3306:3306 mysql
# 接続確認をするために、MySQLの中に入ります
$ docker exec -it mysql bin/bash
# MYSQL_ROOT_PASSWORDをタイプします
mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.19 MySQL Community Server - GPL
...

お手軽にMySQLのコンテナーを立ち上げたいなら、@astrsk_horiさんの「dockerでmysqlを使う」が参考になります。

qiita.com

試験用に使うテーブル

CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT,
    status INT NOT NULL,
    txt TEXT,
    PRIMARY KEY(id),
    INDEX(status)
);

適当なシェルを作成して、テストデータを大量に入れます。私はローカルにMySQLクライアントを入れてなかったので、dockerのMySQLコンテナー内でシェルを実行しました。

#!/bin/sh

for i in `seq 1 10028`
do
    # パスワードをインラインで記述するべきではないという警告が出力されたますが、今回は試験用のため無視します。
    # mysql: [Warning] Using a password on the command line interface can be insecure.
    `mysql -pmysql -e "INSERT INTO test (status, txt) VALUES ( $i , 'abcdefg');" mysql`
done

シェルの実行後にテーブルを確認したところ、確かに10028件のレコードが挿入されました。

mysql> SELECT COUNT(*) FROM test;
+----------+
| COUNT(*) |
+----------+
|    10028 |
+----------+
1 row in set (0.00 sec)

テーブル情報の確認

@ikenjiさんの「MySQLでDBとテーブルのサイズを確認するSQL」が参考になります。 「INFORMATION_SCHEMA.TABLES」を参照すれば、テーブルの情報が確認できます。

qiita.com

mysql> SELECT
    ->    TABLE_NAME, ENGINE, TABLE_ROWS
    -> FROM
    ->     information_schema.tables
    -> WHERE
    ->     table_schema=database()
    ->     AND table_name = 'test';
+------------+--------+------------+
| TABLE_NAME | ENGINE | TABLE_ROWS |
+------------+--------+------------+
| test       | InnoDB |      10000 |
+------------+--------+------------+
1 row in set (0.00 sec)

SQLの実行結果を確認するとわかりますが、本来は10028件のはずが、10000件と表示されてしまっています。なおこの結果は不安定なようで、さまざまなパターンで何回かやり直してみたところ、正確な場合もあれば不正確な場合もありました。

InnoDBテーブルの場合、行カウントは概算を返却するらしい

理由が知りたかったので、リファレンスを確認してみました。 「InnoDBテーブルの場合、行カウントはSQL最適化で使用される単なる概算」と、書いてあります。

テーブルが INFORMATION_SCHEMA データベースにある場合、TABLE_ROWS カラムは NULL です。
InnoDB テーブルの場合、行カウントは SQL 最適化で使用される単なる概算です。(InnoDB テーブルがパーティション化されている場合も、これは当てはまります。)

https://dev.mysql.com/doc/refman/5.6/ja/tables-table.html より

つまり、正確な値ではないようです。

ちなみに、本来ならMySQL8系のリファレンスを見たかったのですが。なぜか5.6までのリファレンスしか見つからなかったため、5.6系のリファレンスをもとに書いてます。