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

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
tからcount(*)を選択します。
t() に挿入します。
始める;
t() に挿入します。
tからcount(*)を選択します。 tからcount(*)を選択します。 tからcount(*)を選択します。
10000;結果は10002です結果は10001です

セッション 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 増加しました。
セッションAセッションB
データを挿入します。 T1
Redis カウントを読み取ります。 T2
データベースからのレコードを確認します。
Redis のカウントが 1 増加します。 T3

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

セッションAセッションB
Redis のカウントが 1 増加します。 T1
Redis カウントを読み取ります。 T2
データベースからのレコードを確認します。
データを挿入します。 T3

セッション 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の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • 大規模な MySQL テーブルに対する count() の実装を最適化しました
  • MySQL の集計関数 count の使用法とパフォーマンスの最適化テクニック
  • MySQL の InnoDB におけるカウント最適化の問題の共有
  • MySQLのCOUNT(*)のパフォーマンスについてお話しましょう
  • MySQL の count 関数の正しい使い方の詳細な説明
  • MySQL カウントを向上させる方法のまとめ
  • MySQL でのフィルター条件なしのカウントの詳細な説明
  • MySQL における count(*)、count(1)、count(col) の違いのまとめ
  • 複数のテーブルでの MySQL カウント データ例の詳細な説明
  • MySQL COUNT関数の使用と最適化

<<:  Vue.js パフォーマンス最適化 N 個のヒント (収集する価値あり)

>>:  Linux ダイナミックライブラリの生成と使用ガイドの詳細な説明

推薦する

Docker を使用して開発環境を構築する方法 (Windows および Mac)

目次1. Dockerを使用する利点2. Dockerをインストールする1) LinuxにDocke...

HTML thead タグの定義と使用法の詳細な紹介

コードをコピーコードは次のとおりです。 <thead> <!– 最初の 2 行をヘ...

Nginx サーバーが Systemd カスタム サービス プロセス分析を追加

1. nginxを例に挙げるyumコマンドを使用してNginxをインストールしましたSystemd ...

Zabbixで電子メールアラートを実装する方法

オンラインチュートリアルに従って実装しました。 zabbix3.4、スクリプトとsendEmailを...

vue3 キャッシュページキープアライブと統合ルーティング処理の詳細な説明

目次1. はじめに2. 使用1. vue2とvue3の違い2. ページ上の一部のデータはキャッシュす...

CSS 疑似クラス: 空っぽだと光る (サンプルコード)

最近私の記事を読んだ人なら誰でも、私が現在WeChatミニプログラムプロジェクトを担当しており、その...

Ubuntu に MySQL 5.7 をインストールし、データ ストレージ パスを構成する方法

1. MySQLをインストールするこの記事はAPT経由でインストールされており、インストールされてい...

JavaScript タイマーの種類の概要

目次1.setInterval() 2.タイムアウトを設定する() 1.setInterval()指...

Linux でパスワードを入力せずに sudo コマンドを実行する方法

sudo コマンドを使用すると、信頼できるユーザーは別のユーザー (デフォルトでは root ユーザ...

CSS コード省略 div+css レイアウト コード省略仕様

略語を使用すると、CSS ファイルのサイズが小さくなり、読みやすくなります。 CSS 省略形の主なル...

VueはEchartsを使用して3次元棒グラフを実装します

この記事では、Echartsを使用して3次元棒グラフを実装するVueの具体的なコードを参考までに共有...

MYSQL サブクエリとネストされたクエリの最適化例の分析

ゲーム史上最高スコアトップ100をチェックSQLコード cdb_playsgame ps から ps...

MySQL 中断された接続警告ログの分析

序文:場合によっては、MySQL に接続されたセッションが異常終了することが多く、エラー ログに「通...

使用場所によって混乱しやすいXHTMLタグ

<br />jb51.net では、常に記事のセマンティクスを重視してきましたが、HTM...

JavaScript ベースのランダム点呼システムの実装

この記事では、ランダムロールコーラーを実装するためのJavaScriptの具体的なコードを参考までに...