大規模な MySQL テーブルに対する count() の実装を最適化しました

以下は、B+ ツリーのデータ構造と実験結果からの推測に基づいた私の判断です。間違いがあればご指摘ください。

今日は、MySQL count() 操作の最適化を試しました。以下の説明は、MySQL 5.7 InnoDB ストレージ エンジンと x86 Windows オペレーティング システムに基づいています。

作成されたテーブルの構造は以下のとおりです（データ量は100万です）。

まず、MySQLのcount(*)、count(PK)、count(1)のうちどれが速いのかという疑問があります。
結果は次のとおりです。

違いはありません！ WHERE句を追加した後は、3つのクエリにかかる時間も同じなので、写真は掲載しません。

以前会社で働いていたとき、 select count(*) from tableという SQL 文を書いたのですが、データが多いと非常に遅くなりました。では、どのように最適化すればよいのでしょうか?

これは InnoDB インデックスから始まります。InnoDB インデックスは B+Tree です。

主キー インデックスの場合: リーフ ノードにのみデータを格納し、キーは主キー、値はデータ全体です。
補助インデックスの場合: キーはインデックスを作成する列であり、値は主キーです。

これにより、次の 2 つの情報が得られます。
1. 主キーに従ってデータ全体が検索されます
2. セカンダリ インデックスに基づいて見つけることができるのは主キーのみであり、残りの情報は主キーを通じて見つける必要があります。

したがって、count(*) 操作を最適化したい場合は、短い列を見つけて、その列のセカンダリ インデックスを作成する必要があります。
私の場合はstatusですが、「重大度」はほぼ 0 です。

まずインデックスを作成します: ALTER TABLE test1 ADD INDEX ( status );
次に、以下のようにクエリを実行します。

クエリ時間が 3.35 秒から 0.26 秒に短縮され、クエリ速度が13 倍近く向上したことがわかります。

インデックスがstr列の場合、結果はどうなりますか?
まずインデックスを作成します: alter table test1 add index (str)
結果は次のとおりです。

ご覧のとおり、時間は 0.422 秒で、これも非常に高速ですが、それでもstatus列より約 1.5 倍遅いです。

もっと大胆に、実験してみます。 status列のインデックスを削除し、 statusとleft(omdb,200)の結合インデックス (この列の平均文字数は 1000 文字) を作成して、クエリ時間をチェックします。
インデックスを作成します: alter table test1 add index ( status ,omdb(200))
結果は次のとおりです。

タイムは1.172秒

テーブル test1 を変更し、インデックス (status,imdbid) を追加します。

補充してください！ ！
インデックス障害に注意してください!
インデックスが作成されると、次のようになります。

key_len が 6 であり、Extra の説明でインデックスが使用されていることがわかります。

インデックスが失敗した場合:

関数の使用や != 演算など、インデックスが無効になる状況は多数あります。詳細については、公式ドキュメントを参照してください。

MySQL について深く勉強したわけではなく、上記は B+ ツリーデータ構造に基づく私の判断と実験結果の推測に基づいています。間違いがあればご指摘ください。

これで、大規模な MySQL テーブルに対する count() の最適化された実装に関するこの記事は終了です。大規模な MySQL テーブルに対する count() の最適化に関する関連コンテンツの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。