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

大規模な 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列のインデックスを削除し、 statusleft(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 をよろしくお願いいたします。

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

<<:  nuxt.js 複数の環境変数の設定

>>:  Web 開発チュートリアル クロスドメイン ソリューションの詳細な説明

推薦する

ローカルで起動したときに Vue プロジェクトがクッキーを保持できない問題を解決する

vueプロジェクトをパッケージ化してサーバーにデプロイし、正常にログインできるが、ローカルで起動する...

MySQL NULLデータ変換方法(必読)

MySQL を使用してデータベースをクエリし、左結合を実行すると、関連付けられたフィールドの一部に...

CocosCreatorオブジェクトプールの使い方

目次序文:特定の操作ステップ1: プレハブを準備するステップ2: オブジェクトプールを初期化するステ...

MySQLのネストされたトランザクションで発生する問題

MySQL はネストされたトランザクションをサポートしていますが、それを実行する人は多くありません....

Windows での MySQL 5.7.20 のインストールと設定方法のグラフィック チュートリアル

参考までにWindowsにMySQLをインストールします。具体的な内容は次のとおりです。 1.まずM...

CocosCreator 学習モジュールスクリプト

Cocos Creator モジュラースクリプトCocos Creator を使用すると、コードを複...

Idea で Docker を使用して SpringBoot プロジェクトをデプロイする詳細な手順

序文プロジェクト要件: Dockeridea に Docker プラグインをインストールし、Dock...

Vueのprovideとinjectの使い方と原則を分析する

まず、provide/inject を使用する理由について説明しましょう。祖父コンポーネントと孫コン...

Linux の chown コマンドと chmod コマンドの違いの詳細な説明

Linux システムでは、chmod コマンドと chown コマンドの両方を使用して権限を設定でき...

MySQLトランザクションの特徴と分離レベルについてお話ししましょう

インターネットにはすでにこの種の記事が溢れていますが、私がこれをまだ書いている理由は単純です。それは...

MySQL イベント スケジューラに関するよくある話 (必読)

概要MySQL には独自のイベント スケジューラもあり、これは Linux の crontab ジョ...

MySQL で重複を削除するには、distinct または group by を使用する必要がありますか?

序文group by と distinctive のパフォーマンス比較について: インターネット上の...

MySQLのスペースをクリーンアップするいくつかの具体的な方法

目次序文1. ファイルのディスク使用量を確認する1.1 ディスク容量の使用状況を確認する1.2 ディ...

CentOS8 jdk8 / java8 のインストールチュートリアル(推奨)

序文最初はCentOS8でwgetを使ってダウンロードし、解凍して環境変数を設定するつもりだったので...

Docker: /etc/default/docker の DOCKER_OPTS パラメータを変更しても反映されない

デフォルトでは、 /etc/default/docker 設定は有効になりません。docker 環境...