MySQLでの全レコード取得で一番パフォーマンスがいい方法

MySQLでテーブルレコード数を取得する場合、
普通は COUNT(*) とか使うのが定番だと思います。

ですが、全レコード数を取得したいだけなら、もっといい方法があるんです。

実を言うと、僕自身もそんないい方法があると知りませんでした。

そこで自分の記憶の整理も兼ね、
MySQLで全レコード数を取得するハイパフォーマンスな方法を紹介します。

このページの目次

全レコード件数取得に COUNT(*) を使うのは悪手＆最悪
COUNT(*) よりパフォーマンスがいい方法とは・・・
訂正 - TABLE_ROWSより正確な方法があった
全レコード取得は "一番" 効率のいい方法を使おう

全レコード件数取得に COUNT(*) を使うのは悪手＆最悪

もし条件とかを一切指定せず、全レコード数を取得したい場合・・・

そういうときに COUNT(*) を使っちゃうのは最悪

なぜならこの関数は、カウントという動作を伴うものだからです。

だから全レコード数が多ければ多いほど、速度に影響が出てくるわけです。

例えば次みたいなテーブルとデータを用意したとしましょう。

CREATE TABLE players (
  id    INT KEY AUTO_INCREMENT,
  name  VARCHAR(255) NOT NULL
);

INSERT INTO players (name)
VALUES ('hoge'), ('fuga'), ('piyo');

CREATE TABLE players (

id INT KEY AUTO_INCREMENT,

name VARCHAR(255) NOT NULL

);

INSERT INTO players (name)

VALUES ('hoge'), ('fuga'), ('piyo');

そしてこのテーブルの全件を知りたいなら、
次みたいに COUNT(*) を使う人がそれなりに多いはず

SELECT COUNT(*) AS cnt FROM players;
-- => 3

1 2	SELECT COUNT(*) AS cnt FROM players; -- => 3

もちろん COUNT(*) でもやりたいことは実現できます。

でも次みたいな場合だと、この方法は超効率が悪いんです。

レコード数が大量ある場合（数千件～）
条件とかを一切指定しない場合

もし "ただ単純に全レコード件数取得" したいだけなら、もっといい方法があります。

COUNT(*) よりパフォーマンスがいい方法とは・・・

その方法とは infomatio_scheme データベースから取得すること

以下はリファレンスでの information_scheme の説明です。

INFORMATION_SCHEMA は、各 MySQL インスタンス内のデータベースであり、MySQL Server が保持するほかのすべてのデータベースに関する情報を格納する場所です。INFORMATION_SCHEMA データベースには複数の読み取り専用テーブルが含まれます。これらには実際にはビューがあるので、関連付けられたファイルはなく、トリガーは設定できません。また、その名前を持つデータベースディレクトリもありません。

引用元 : MySQL リファレンスマニュアル INFORMATION_SCHEMA テーブル

簡単に言うなら、MySQL全体のメタ情報を記録しておくデータベースということ

例えばこんな情報がアレコレ記録されてるみたいです。

サーバーのプラグインの情報
テーブルとかカラムのメタ情報
ストレージエンジンの情報

こういう風にあらゆるメタ情報が記録されてます。

もちろん各DBの各テーブルのレコード数も "レコードとして" 記録されてます。

なので全レコード数取得なら、それを調べるのが手っ取り早いですね。

例えば my_app というデータベース内に、 hoge テーブルがあるとしましょう。

その場合、 hoge テーブルの全レコード数取得は次SQLを書けばＯＫ

SELECT table_rows FROM information_schema.tables
WHERE
  table_schema = 'my_app' AND
  table_name = 'hoge';

SELECT table_rows FROM information_schema.tables

WHERE

table_schema = 'my_app' AND

table_name = 'hoge';

このSQL例の table_name = 'hoge' みたいに、
データベース名とテーブル名を指定しなくちゃいけないのが少し面倒かも

でも件数はレコードとして記録されてるから、
実行速度も COUNT(*) なんかを使うより何倍・何十倍も高速です。

もちろん数百件とか千件とか・・・
そういう小さなテーブルなら COUNT(*) でも問題ないかもしれません。

でも数千件、数万件を超えるなら間違いなくこっちを使うのがベスト！

訂正 - TABLE_ROWSより正確な方法があった

追記　：　2020/08/30

上記の方法ですが、どうやら不完全でした。

数十万レコードあるテーブルで試したのですが、 COUNT(*) だと160694件返ってくるのに、TABLE_ROWSだと148412件しか返ってこないという現象に遭遇しました。

恐らく TABLE_ROWSはテーブル全体のレコード数を表しているのではなく、何か別の意味があるのかもしれません。（もし分かる人がいたら教えてください）

ですが、代わりの方法はちゃんとあります。

▼ このようにAUTO_INCREMENTから取得すればOK

SELECT AUTO_INCREMENT-1 
FROM information_schema.tables
WHERE
  table_schema = 'app' AND
  table_name = 'hogehoge';

SELECT AUTO_INCREMENT-1

FROM information_schema.tables

WHERE

table_schema = 'app' AND

table_name = 'hogehoge';

一般的なテーブルではIDに連番を使用してるはずです。

その連番の最新値＋１を記録しているのがAUTO_INCREMENTカラムです。この値から全レコード数を取得できました。TABLE_ROWSの代わりにこちらを使う方が確実です。

全レコード取得は "一番" 効率のいい方法を使おう

ということで、簡単にまとめ

全件取得に COUNT(*) は悪手！
条件とか一切指定しないなら、 COUNT(*) を使うのは最悪の悪手。もし数万レコードとかあるテーブルだと速度にかなり悪影響を与えそう...
メタ情報から取得するのがベスト
具体的には information_scheme.tables テーブルの TABLE_ROWS から取得するという方法。具体的には information_scheme.tablesテーブルの AUTO_INCREMENT から取得する方法。速度を重視するなら絶対にコッチヲ使うべし！

僕自身、こんな便利な方法があるとは知らなかったですね。

これからは COUNT(*) じゃなくて、こっちを使ってこうと思います。

以上、 MySQLで全レコード数を取得する方法＆SQL例についてでした。ではではﾊﾞｲﾊﾞｲ(^_^)/~