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

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

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

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

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

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

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

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

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

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

 

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

 

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

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

 

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

  • レコード数が大量ある場合(数千件~)
  • 条件とかを一切指定しない場合

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

 

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

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

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

追記 : 2020/08/30

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

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

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

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

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

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

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

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

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

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

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

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

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

Shareこの記事をシェアしよう!

Commentsこの記事についたコメント

1件のコメント
  • k

    AUTO_INCREMENTは常にインクリメントされるので、DELETEされたレコードがあったら合わないし、色々な事情で1から始まらないことも多いし、インクリメント値を取得したけど色々あってinsertが正しく完了しなかった場合にも値は戻らないので、AUTO_INCREMENTの値が実際の行数と一致すると判断するのは何の信頼性もなくて非常に危険、業務だと絶対に採用できない

    6月 21, 2022 5:13 pm

コメントを残す

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

CAPTCHA


このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください