MySQLの行数カウントに関する簡単な説明

各テーブルの行数をカウントするために使用される MySQL count() 関数は、誰もがよく知っています。しかし、テーブルがどんどん大きくなり、InnoDB エンジンを使用すると、計算速度がどんどん遅くなることがわかります。この記事では、まず count() を実装する原則と理由を紹介し、次に count のさまざまな使用法のパフォーマンスを分析し、最後に頻繁に変更する必要があり、行数をカウントする必要があるテーブルに対するソリューションを提供します。

Count() の実装

InnoDB と MyISAM は、MySQL でよく使用されるデータ エンジンです。実装の違いにより、count() 操作の効率も異なります。

MyISAM の場合、各テーブルの合計行数をディスク上に保存するため、count(*) 計算を使用すると効率が非常に高く、結果が直接返されます。ただし、where 条件を追加すると、検索は実行されるため、効率は高くありません。

InnoDB の場合、count(*) 操作を行う際に、エンジンからデータを 1 行ずつ読み込み、カウントを蓄積していきます。当然、テーブルが大きくなるほど効率は悪くなります。

では、なぜ InnoDB は MyISAM のようにテーブルに記録できないのでしょうか?その理由は、InnoDB には MyISAM よりも多くのトランザクション サポート機能があるものの、一定のトレードオフも必要となるためです。 MVCC の制御により、MySQL は並行処理の機能を備えています。つまり、同時に、InnoDB によって返されるテーブルの行数は固定ではありません。トランザクションが参照する行数は、有効にした後の一貫性ビューに関係します。つまり、各トランザクションが参照できるデータ バージョンは異なり、行ごとにしか判断できません。

次のトランザクションのように、テーブル t に 10,000 件のレコードがあると仮定します。

セッション A の場合、セッション B は送信されていないため表示されません。セッション C は送信されていますが、セッション A の開始後に送信されているため、これも表示されません。つまり10000です。

セッション B に関しては、セッション C が開始される前にコミットされ、別のエントリが挿入されたため、結果は 10002 になります。

実際、InnoDB は count(*) 操作を実行する際に最適化を行っています。count(*) 操作を実行すると、通常のインデックスが主キーの ID 値を保存するため、主キー インデックス ツリーをトラバースする代わりに、検索用の最小の通常のインデックス ツリーが見つかります。

論理的な正確性を確保しながら、スキャンされるデータの量を減らすことは、データベース システム設計における一般的なルールです。

また、show table status を使用すると、行数を非常に迅速に照会することもできますが、このコマンドはサンプリングと推定にインデックス統計の値を使用することに注意してください。公式文書によれば、誤差は40%～50%になる可能性があるとのことです。

しかし、テーブル内の行数をリアルタイムで取得する必要がある場合はどうすればよいのでしょうか?

テーブルの数を手動で保存する

キャッシュシステムを使用してカウントを保存する

更新されるテーブルの場合は、キャッシュ システムを使用してサポートすることをお勧めします。たとえば、Redis はテーブル内の行の合計数を保存するために使用されます。

エントリがデータベースに挿入されるたびに、Redis のカウントは 1 つ増加し、1 つ減少します。これにより、読み取りおよび書き込み操作は高速に見えますが、いくつか問題があります。

キャッシュ システムの更新が失われます:

Redis メモリ内のデータは定期的にディスクに同期する必要がありますが、Redis の異常な再起動を防ぐ方法はありません。たとえば、Redis にデータを挿入した後、Redis が再起動し、データはハードディスクに保存されません。この時、Redis を再起動した後、データベースから count(*) 操作を実行し、Redis に更新することができます。テーブル全体のスキャンは引き続き可能です。

論理は正確ではありません:

ページにテーブル内の行数と各データを表示する必要があるとします。実装する場合、まず Redis から数量を取得し、次にデータベースからレコードを取得します。

しかし、次のようなことが起こる可能性があります:

1. データベースでは、最後に挿入されたレコードが 100 行内にあることがわかりますが、Redis のカウントは 1 少なくなっています。
2. データベースでは 100 行が見つかり、最新のレコードはありませんでしたが、Redis のカウントは 1 増加しました。

セッション B の場合、時刻 T2 で、Redis エントリの数がデータベースの数より 1 つ少ないことがわかります。

セッション B の場合、時刻 T2 で、Redis レコードの数がデータベースの数より 1 つ多いことがわかります。

実際、この問題は、Redis とデータベース レコード クエリが同じトランザクション内にないために発生します。

データベースに保存

MySQL 自体は InnoDB エンジンのサポートによりトランザクションをサポートしています。そのため、Redis の挿入操作をデータベース内の更新操作に置き換えることで、RR レベルでのトランザクション機能を活用し、データの正確性を確保できます。

もう 1 つのポイントは、REDO ログのサポートにより、MySQL で例外が発生した場合でもクラッシュセーフが保証されることです。

異なるカウント使用法の実行効率

count() 自体は集計関数であり、返された結果セットを行ごとに評価します。パラメータが NULL でない場合は、累積を続け、最終的に結果を返します。

したがって、count(*)、count(id)、count(1) はすべて、条件を満たす結果セット内の行の合計数を返すことを意味します。

count(field) は、条件を満たすデータ行内の NULL ではないフィールドを示します。

count(id) の場合、InnoDB はテーブル全体を走査し、各行 ID を取得して、それをサーバー層に渡します。サーバーは ID が空かどうかを判断し、それを蓄積します。

count(1)の場合、InnoDBはテーブル全体を走査しますが、値を取得しません。サーバー層は自身に 1 を入れてそれを蓄積します。

したがって、count(1) はデータ行の解析やフィールド値のコピーを必要としないため、count(*) よりも高速です。

count(field)の場合、フィールドがnull以外として定義されている場合は、行ごとに読み取られ、nullではないことが判断され、累積されます。定義時に null になる可能性がある場合は、実行中に値を削除し、null でない場合にのみ蓄積する必要があります。

例外は count(*) で、これは特別に最適化されています。値を取得せず、行ごとに直接累積し、計算用の最小のインデックス ツリーを見つけます。

要約する

MySQL count() 関数の実行効率は、基盤となるデータ エンジンに関連しています。 MyISAM は where 条件を追加しないため、クエリは非常に高速になりますが、トランザクションはサポートされません。 InnoDB はトランザクションをサポートしています。ただし、MVCC の実装により、各クエリで行を 1 つずつスキャンする必要があり、非効率的です。

解決策は、レコードを保存するための Redis などの外部キャッシュを設計することです。ただし、異常な再起動や不正確なデータが発生する場合があります。解決策としては、InnoDB に新しいテーブルを作成してレコードを保存することです。

最後に、InnoDB は count(*) に対して独立した最適化を行いますが、他の count 操作には追加の操作が必要になります。

上記は行数をカウントするMySQL countの詳細について簡単に説明しました。MySQL countの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。