MySQL における冗長インデックスと重複インデックスの違い

MySQL における冗長インデックスと重複インデックスの違い

MySQL では、1 つの列に複数のインデックスを作成できます。意図的であるかどうかにかかわらず、MySQL はこれらの重複したインデックスを個別に管理する必要があり、クエリを最適化するときにオプティマイザもそれらを 1 つずつ考慮する必要があるため、MySQL のパフォーマンスに影響します。

コンセプトの説明

重複インデックス: 同じ列に同じ順序で作成された同じタイプのインデックス。このような重複インデックスの作成は避け、発見次第すぐに削除する必要があります。

冗長インデックス: 2 つのインデックスが同じ列を同じ順序でカバーします。

創設の理由

一般的に、次の例のように、誤って重複したインデックスが作成されることがあります。

CREATE TABLEテスト(
 ID INT NOT NULL 主キー、
 INT NOT NULL、
 B INT NULLではない、
 ユニーク(ID)
 インデックス(ID)
)ENGINE=InnoDB;

MySQL の一意の制限と主キーの制限はインデックスを通じて実装されるため、実際には、上記のコードを使用して作成されたテーブルでは、ID 列に 3 つのインデックスが作成されます。通常、異なるクエリ要件を満たすために同じ列に異なるタイプのインデックスを作成する場合を除き、これを行う理由はありません。たとえば、KEY(col) と FULLTEXT KEY(col) という 2 つのインデックスがあります。

冗長インデックスは重複インデックスとは多少異なります。インデックス (A、B) を作成する場合、インデックス (A) は前のインデックスのプレフィックス インデックスにすぎないため、作成すると冗長なインデックスになります。ただし、インデックス (B,A) が作成された場合、それは冗長インデックスではなく、インデックス (B) はインデックス (A,B) の左端のプレフィックス列ではないため冗長インデックスではありません。さらに、カバーされるインデックス列に関係なく、異なるタイプのインデックスは B ツリー インデックスでは冗長になりません。

ほとんどの場合、冗長なインデックスは必要なく、新しいインデックスを作成するのではなく、既存のインデックスを可能な限り拡張する必要があります。しかし、既存のインデックスを拡張するなど、パフォーマンス上の理由によりインデックスが大きくなりすぎて、そのインデックスを使用する他のクエリのパフォーマンスに影響する場合があります。

影響

カバー インデックスとして冗長インデックスを作成すると、一部のクエリの QPS が向上しますが、インデックスが 2 つあるとインデックス コストが高くなるという欠点もあります。

テーブルにインデックスが追加されるにつれて、テーブルへの挿入速度は低下します。一般的に、新しいインデックスを追加すると、特に新しいインデックスがメモリのボトルネックを引き起こす場合、INSERT、UPDATE、DELETE などの操作が遅くなります。

冗長なインデックスや重複したインデックスの解決方法は簡単です。単に削除するだけです。ただし、最初に行うべきことは、そのようなインデックスを見つけることです。

補足: MySQL で重複インデックスと重複外部キーをクリーンアップする

MySQL では同じ列に重複したインデックスを作成できますが、これを行うとデータベースにメリットよりもデメリットが生じます。データベースのパフォーマンスを向上させるには、このような重複したインデックスを定期的にチェックする必要があります。

ディスク領域の使用量を削減し、ディスク IO を削減し、オプティマイザがクエリを最適化するときに比較する必要があるインデックスの数を削減し、データベース内の冗長インデックスを維持するためのさまざまなオーバーヘッドを削減し、データベースのパフォーマンス (挿入、更新、削除) を向上させることができます。

重複インデックス検出

pt-duplicate-key-checker: SHOW CREATE TABLE によって出力されたテーブル定義を使用して、MySQL テーブル内の重複または冗長なインデックスまたは外部キーを検出します。

検出可能な冗長/重複インデックスの種類: インデックスに別のインデックスと同じ順序で同じ列が含まれている場合、またはインデックスに含まれる列が別のインデックスの左端のプレフィックス列である場合、重複/冗長インデックスと見なされます。

デフォルトでは、同じタイプのインデックス (BTREE インデックスなど) のみが比較されます。異なるタイプのインデックスは、上記の説明を満たしていても重複/冗長とは見なされませんが、この動作はパラメータによって変更できます。

さらに、重複する外部キー、つまり同じテーブルと列を参照する外部キーを検出することもできます。クラスター化インデックスを持つテーブルの場合、セカンダリ インデックスの後にプライマリ キー列のインデックスを追加することも冗長であると見なされます。この場合、セカンダリ インデックスの末尾自体にプライマリ キー情報が含まれているためです。

基本的な使い方とサンプル出力は以下のとおりです。

[root@VM_8_180_centos パッケージ]# pt-duplicate-key-checker A=utf8、F=/etc/my.cnf、h=localhost、u=root、P=3306 –ask-pass

サンプル出力:

# ########################################################################
# dcf.権限                              
# ########################################################################
# PRIMARY は重複制約であるため、UQI_IDX_1 の一意性は無視されます
# UQI_IDX_1 は PRIMARY の複製です
# 主な定義:
# ユニークキー `UQI_IDX_1` (`privilege_id`),
# 主キー (`privilege_id`)、
# 列の種類:
# `privilege_id` varchar(50) collat​​e utf8_bin not null コメント '権限ID'
# この重複インデックスを削除するには、次を実行します。
テーブル `dcf`.`privilege` を変更し、インデックス `UQI_IDX_1` を削除します。
# ########################################################################
# dcf.t_game_config                            
# ########################################################################
# PRIMARY は重複制約であるため、pkey の一意性は無視されます
# pkeyはPRIMARYの複製です
# 主な定義:
# ユニークキー `pkey` (`pkey`)
# 主キー (`pkey`)、
# 列の種類:
# `pkey` bigint(20) は null ではない auto_increment
# この重複インデックスを削除するには、次を実行します。
テーブル `dcf`.`t_game_config` を変更し、インデックス `pkey` を削除します。
# ########################################################################
#dcf.t_プロジェクト機関                        
# ########################################################################
# index_1はindex_2の左接頭辞です
# 主な定義:
# キー `index_1` (`project_id`),
# キー `index_2` (`project_id`,`institution_id`,`delete_flag`)
# 列の種類:
# `project_id` bigint(20) not null コメント 'プロジェクト ID'
# `institution_id` varchar(20) 非 null コメント '機関 ID'
# `delete_flag` tinyint(4) が null ではない
# この重複インデックスを削除するには、次を実行します。
テーブル `dcf`.`t_project_institution` を変更し、インデックス `index_1` を削除します。
# ########################################################################
# dcf_commons.bank_cnaps                         
# ########################################################################
# idxはPRIMARYの重複です
# 主な定義:
# キー `idx` (`cnaps`)
# 主キー (`cnaps`)、
# 列の種類:
# `cnaps` varchar(255) not null コメント '電子銀行番号'
# この重複インデックスを削除するには、次を実行します。
テーブル `dcf_commons`.`bank_cnaps` を変更し、インデックス `idx` を削除します。
# ########################################################################
# dcf_contract.顧客銀行口座                   
# ########################################################################
# IDX_CUSTOMER_IDはUQI_IDX_1の左接頭辞です
# 主な定義:
# キー `IDX_CUSTOMER_ID` (`customer_id`)
# ユニークキー `UQI_IDX_1` (`customer_id`,`account_no`,`branch_bank`,`account_type`,`account_name`) BTREE を使用、
# 列の種類:
# `customer_id` varchar(20) collat​​e utf8_bin not null コメント '顧客ID'
# `account_no` varchar(40) collat​​e utf8_bin デフォルト null コメント '銀行口座番号'
# `branch_bank` varchar(100) collat​​e utf8_bin デフォルト null コメント 'ブランチを開く'
# `account_type` tinyint(4) デフォルト null コメント '口座タイプ: 受取口座、返済口座など。\n0-受取口座\n1-返済口座'
# `account_name` varchar(100) collat​​e utf8_bin default null comment '銀行口座名'
# この重複インデックスを削除するには、次を実行します。
テーブル `dcf_contract`.`customer_bank_account` を変更し、インデックス `IDX_CUSTOMER_ID` を削除します。
# ########################################################################
# dcf_contract.t_contract_account                     
# ########################################################################
# IDX_CONTRACT_IDはt_contract_account_uq1の左接頭辞です
# 主な定義:
# キー `IDX_CONTRACT_ID` (`contract_id`)
# ユニークキー `t_contract_account_uq1` (`contract_id`,`account_type`),
# 列の種類:
# `contract_id` bigint(20) 非 null コメント '契約ID'
# `account_type` tinyint(4) not null コメント '口座タイプ: globalconstant.bankaccounttypec 定数\n0-受取口座\n1-返済口座など。'
# この重複インデックスを削除するには、次を実行します。
テーブル `dcf_contract`.`t_contract_account` を変更し、インデックス `IDX_CONTRACT_ID` を削除します。
......
......
# ########################################################################
# インデックスの概要                           
# ########################################################################
# サイズ重複インデックス 173317386
重複インデックスの合計数 18
# 総インデックス数 562

重複/冗長タイプ、インデックス/外部キー定義、インデックスに含まれる列タイプ、重複/冗長インデックス/外部キーを削除する SQL、最後にインデックスに関する統計が提供されます。

重複インデックスの削除

ツールの出力結果で ALTER TABLE ステートメントを直接実行できますが、実行する前に起こり得る影響を慎重に評価してください。例えば、テーブルが非常に大きい場合、マスタースレーブレプリケーションの遅延が発生する可能性があります。SQL にインデックスヒントが含まれている場合、インデックスを直接削除すると SQL 構文エラーが発生する可能性があります。そのような SQL が含まれていないか事前に確認することをお勧めします (一般的なログや tcpdump ツールで SQL を取得して分析できます)

上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。間違いや不備な点がありましたら、遠慮なくご指摘ください。

以下もご興味があるかもしれません:
  • MySQLクエリの冗長インデックスと未使用のインデックス操作
  • MySQL 重複インデックスと冗長インデックスの例の分析
  • MySQL の冗長インデックスと重複インデックスの詳細な説明

<<:  左右移動時のテキスト変換効果のHTMLコード分析

>>:  ウェブサイト構築経験概要

推薦する

JavaScript の new 演算子の原理と例の詳細な説明

新しい用途new の機能は、コンストラクターを通じてインスタンス オブジェクトを作成することです。イ...

VMware ESXi CLI の一般的なコマンドを調べる

目次【共通コマンド】 [一般的な esxi コマンドの概要] [esxcli コマンドの調査] ES...

Nginx転送マッチングルールの実装

1. 正規表現マッチング大文字と小文字を区別するマッチングの場合 ~ ~*は大文字と小文字を区別しな...

MySQLデータベースエンジンをInnoDBに変更する

PS: ここではPHPStudy2016を使用しています1. 変更中にMySQLを停止する2. my...

さまざまな Tomcat ログと catalina.out ファイルのセグメンテーションの関係についての簡単な分析

Tomcatログの関係一枚の写真は千の言葉に値する! localhost.{yyyy-MM-dd}....

現在使用されている設定ファイル my.cnf を表示する mysql メソッド (推奨)

my.cnfは、MySQL の起動時に読み込まれる設定ファイルです。通常は MySQL インストー...

Node+Express テストサーバーのパフォーマンス

目次1 テスト環境1.1 サーバーハードウェア1.1.1 t2.マイクロ1.1.2 c5.large...

CentOS8.0ネットワーク設定の実装

1. CentOS 7 と CentOS 8 のネットワーク構成の違い: VMware Workst...

インタビューの質問: ホーリー グレイル レイアウトとダブル ウィング レイアウトの違い

序文今日は、聖杯レイアウトとダブルウィングレイアウト、そしてそれらの違いについてお話しします。この2...

Dockerコンテナの紹介

Dockerの概要Docker はオープンソースのソフトウェア展開ソリューションです。 Docker...

MySQL 5.7.18 Green Edition のダウンロードとインストールのチュートリアル

この記事では、MySQL 5.7.18のグリーンバージョンをダウンロードしてインストールする詳細な手...

開発効率の向上に役立つ 56 個の実用的な JavaScript ツール関数

目次1. デジタルオペレーション(1)指定された範囲内で乱数を生成する2. 配列操作(1)配列の順序...

jsは配列の平坦化を実装します

目次配列をフラット化する方法1.flat() の使用2. 正規表現を使用する3.reduce()+c...

全体的なユーザーエクスペリエンスを確保する方法

関連記事:ユーザーエクスペリエンスのためのウェブサイトデザイン今朝、GMail がまた不調になり、接...

画像のシームレスなスクロールを実現する JavaScript タイマー

この記事では、画像のシームレスなスクロールを実現するためのJavaScriptの具体的なコードを参考...