大規模な 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 開発チュートリアル クロスドメイン ソリューションの詳細な説明

推薦する

React Router 5.1.0 はページジャンプナビゲーションを実装するために useHistory を使用します

目次1. withRouterコンポーネントを使用する2. ルートタグを使用するReactRoute...

CSSスタイルシートとフォーマットレイアウトの詳細な説明

スタイルシートCSS (カスケーディング スタイル シート) は、HTML Web ページを美しくす...

VMware Workstation 仮想マシンのインストール操作方法

仮想マシンは非常に便利なテストソフトウェアです。ハードウェアに損傷を与えることなく、さまざまなテスト...

Ubuntu システムにおける Mysql ERROR 1045 (28000): ユーザー root@localhost へのアクセスが拒否される問題の解決方法

最初の方法: skip-grant-tables: 非常に便利なmysql起動パラメータ非常に便利な...

PostgreSQL データベースにおける varchar、char、text の比較に関する簡単な説明

以下のように表示されます。名前説明する文字可変(n)、varchar(n)長さ制限あり、可変長文字(...

Linux でのインストール中にソフトウェア パッケージの依存関係レポートに関連する問題の解決策

目次背景1) yumのkeepchche機能を有効にする: 方法1 2) yum-utils ソフト...

インデックススキャンを使用したMySQLソート

目次sakilaをインストールするインデックススキャンソートテーブル構造インデックススキャンをソート...

Vue でカスタムパスのエイリアスを設定する方法

Vue でカスタム パス エイリアスを設定する方法日常の開発では、モジュールやコンポーネントをインポ...

MySQLは適切なストレージエンジンを選択します

データベースに関して最もよく聞かれる質問の 1 つは、現在のビジネス ニーズを満たす MySQL の...

Nodeはバックエンドの実装手順を素早く構築します

1. まず、node、express、express-generator をインストールします (4...

HTML 代替カラーコードを実現する n 通りの方法 サンプルコード

この記事では、主に HTML のサンプル コードを紹介し、次のように交互に色を変更する方法を共有しま...

ダウンロードにおすすめの氷と雪のフォント 33 種類 (個人用および商用)

01 ウィンターフレーク(個人のみ) 02 スノートップキャップ(業務用) 03 モディウス「フリ...

MySQL ロック(テーブルロック、行ロック、共有ロック、排他ロック、ギャップロック)の詳細な説明

現実世界では、鍵は外の世界から身を隠したいときに使用するツールです。コンピュータでは、複数のプロセス...

Dockerを使用してRedisクラスターを構築する方法

目次1. Redis Dockerベースイメージを作成する2. Redisノードイメージを作成する3...

JavaScript のマイクロタスクとマクロタスクの説明

序文: js はシングルスレッド言語なので、非同期にすることは不可能です。しかし、js のホスト環境...