MySQLのcount(*)を解析してみると、count(1)よりも本当に速いのでしょうか？

MySQL の count(*) は本当に count(1) より速いのでしょうか?次の記事では、MySQL の count(*) と count(1) を比較して、パフォーマンスの違いを確認します。

今日誰かが、MySQL では count(1) の方が count(*) よりも速いと言いました。これを許容できますか? ?彼とは休憩を取らなければなりません。

ステートメント: 以下の説明は InnoDB ストレージ エンジンに基づいていますが、特殊な状況のため、MyISAM については記事の最後で個別に説明します。 [関連する推奨事項: mysql ビデオ チュートリアル ]

結論から先に話させてください: 両者のパフォーマンスには大きな違いはありません。

1. 実践

100 万個のデータを含むテーブルを用意しました。テーブル構造は次のとおりです。主キーインデックス。

次のように 2 つの方法でテーブル内のレコード数を数えてみましょう。

2 つの SQL の実行効率は次のとおりであることがわかります。実際にはほぼ同じで、どちらも 0.14 秒です。

他の 2 つの統計を見てみましょう。

id ​​は主キー、ユーザー名とアドレスは通常のフィールドです。

統計に ID を使用すると、いくつかの利点があることがわかります。ソング兄弟、テスト データのサンプルが比較的少ないため、効果は明ら​​かではありませんが、友達がテスト データの量を増やすと、違いがより明確になります。

では、この違いの正確な理由は何でしょうか? 次に簡単に分析してみましょう。

2. Explain 分析

まず、Explain を使用して、これらの SQL のさまざまな実行プランを見てみましょう:

次のことがわかります。最初の 3 つの統計手法の実行計画は同じであり、最後の 2 つは同じです。

ここでは、説明内のさまざまな項目を比較します。

type: 最初の 3 つの type 値は、index です。これは、完全なインデックス スキャン、つまり、インデックス全体を調べてください (

インデックスはテーブル全体ではないことに注意してください
• ); 後の 2 つの型の値は all で、これは 全テーブル スキャン を意味します。インデックスは使用されません。 key: これは、MySQL がテーブルへのアクセスを最適化するためにどのインデックスを使用するかを決定することを意味します。PRIMARY は主キー インデックスを使用することを意味し、NULL はインデックスが使用されないことを意味します。
• key_len: MySQL で使用されるキーの長さを表します。主キーのタイプは INT で null ではないため、値は 4 です。
• 補足: ここでインデックスを使用すると、オプティマイザは必要なデータを取得するためにインデックスにアクセスするだけで済みます (テーブルを返す必要はありません)。

説明を通じて、最初の 3 つの統計手法の実行効率が (インデックスを使用しているため) より高く、後の 2 つの統計手法の実行効率が高いことが実際に大まかにわかります。比較的低い値であり、これより低いと言われています (インデックスは使用されず、テーブル全体のスキャンが必要です)。

上記の分析だけでは不十分です。原理的な観点から分析してみましょう。

3. 原理分析

3.1 主キーインデックスと通常インデックス原理分析を始める前に、最初にリードする B ツリーを見てみましょう。これは、次の内容を理解する上で重要な役割を果たします。

ご存知のとおり、InnoDB のインデックスのストレージ構造は B ツリーです (B ツリーとは何か、また B ツリーとの違いについては、この記事では説明しません)これは、両方を個別に組み合わせることができます。記事)、主キー インデックスと通常のインデックスの格納方法が異なります。次の図は、主キー インデックスを示しています。主キー インデックスでは、リーフ ノードに各行のデータが格納されていることがわかります。

通常のインデックスでは、リーフ ノードに主キーの値が格納されます。通常のインデックスを使用してデータを検索するときは、まずリーフ ノードで主キーを見つけてから、その主キーを主キー インデックス。データの検索は 2 つの検索を実行するのと同じであり、これを通常

table return

操作と呼びます。

3.2 原理分析

私たちが MySQL を学習していたときに、count 関数が次のように分類されていたことに友人が気づいたかどうかはわかりません。 count 関数はこれらとグループ化されており、これが集計関数でもあることを示しています。

集合関数なので、返された結果セットを一行ずつ判断する必要があり、何が返ってきたのかという疑問が生じます。これを個別に見てみましょう: クエリ select count(1) from user;

の場合、InnoDB エンジンは走査する最小のインデックス ツリー (主キー インデックスとは限りません) を見つけます。ただし、データは読み取られませんが、リーフノードが読み取られ、1が返され、結果が蓄積されます。

クエリ select count(id) from user; の場合、InnoDB エンジンは主キー インデックス全体を走査し、ID を読み取って返しますが、ID は主キーであるため、はノード上の B ツリーのリーフ上にあるため、このプロセスにはランダム IO が含まれず (データ ページからデータを取得するためにテーブルに戻るなどの操作は必要ありません)、パフォーマンスも問題ありません。

クエリ select count(username) from user; の場合、InnoDB エンジンはテーブル全体を走査してテーブル全体のスキャンを実行し、各行のユーザー名フィールドを読み取り、それを返します。定義中にユーザー名が非 null に設定されている場合は、ユーザー名の数が直接カウントされます。定義時にユーザー名が非ヌルとして設定されていない場合は、最初にユーザー名が空かどうかを判断してからカウントされます。

最後に、select count(*) from user; について話しましょう。この SQL の特別な点は、MySQL によって最適化されていることです。MySQL が count(* を認識したとき) ) レコードの合計数をカウントしたい場合は、走査する最小のインデックス ツリーを見つけて、レコード数をカウントします。

主キー インデックス (クラスター化インデックス) のリーフ ノードはデータですが、通常のインデックスのリーフ ノードは主キーの値であるため、通常のインデックスのインデックス ツリーは小さくなります。ただし、上記の場合は主キー インデックスしかないため、最終的には主キー インデックスが使用されます。

ここで、上記のテーブルを変更してユーザー名フィールドにインデックスを追加すると、explain select count(*) from user; の実行プランを確認します。 :

ご覧のとおり、この時点で使用されるインデックスはユーザー名インデックスであり、以前の分析結果と一致しています。

上記の説明から、最初のクエリのパフォーマンスが最も高く、2 番目が 2 番目 (ID を読み取って返す必要があるため)、3 番目が最悪 (完全なクエリが必要なため) であることがわかります。 table scan) )、4 番目のクエリ パフォーマンスは最初のクエリ パフォーマンスに近いです。

4.MyISAM についてはどうですか?

一部の友人は、MyISAM エンジンの select count(*) from user; 操作が非常に高速であることを知っているかもしれません。これは、MyISAM がテーブル内の行数を直接保存するためです。ディスク内にあるので、必要なときに直接読み取ることができるため、非常に高速です。

MyISAM エンジンがこれを行う理由は、主にトランザクションをサポートしていないためです。そのため、その統計は実際には非常に簡単で、レコードの行を追加するだけです。

しかし、一般的に使用されている InnoDB ではこれを行うことができません。なぜ？なぜなら、InnoDB はトランザクションをサポートしているからです。トランザクションをサポートするために、InnoDB では MVCC マルチバージョン同時実行制御が導入されているため、データ読み取り時にダーティ リード、ファントム リード、反復不能読み取りなどの問題が発生する可能性があります。参照: https://www.bilibili.com/video/BV14L4y1B7mB

したがって、InnoDB はデータの各行を取り出し、データの行が現在のセッションに表示されるかどうかを判断する必要があります。表示されている場合はデータ行をカウントしますが、表示されていない場合はカウントされません。

もちろん、MySQL の MVCC は実際には非常に壮大なトピックなので、ソング兄弟が将来暇なときに MVCC について詳しく紹介します。

わかりました、友達の皆さん、理解できましたか?ご質問がございましたら、ディスカッションのためにメッセージを残してください。

プログラミング関連の知識について詳しくは、

プログラミング ビデオ

をご覧ください。 ！

以上がMySQLのcount(*)を解析してみると、count(1)よりも本当に速いのでしょうか？の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。