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コード分析

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

推薦する

vue3 を使用してマテリアル ライブラリを構築する方法

目次なぜマテリアルライブラリが必要なのでしょうか?材質は何ですか?素材の種類fuep、vue3 ベー...

MySQL 並列レプリケーションの簡単な分析

01 並列レプリケーションの概念MySQL のマスター スレーブ レプリケーション アーキテクチャで...

MySQL 8.0.11 Mac 用インストール ガイド

MACはmysql8.0をインストールします。具体的な内容は次のとおりです。 1. ダウンロードアド...

mysql 5.6.21 のインストールと設定の詳細な手順

1. 概要MySQL バージョン: 5.6.21ダウンロードアドレス: https://dev.my...

JS はシンプルなカレンダー効果を実装します

この記事では、シンプルなカレンダー効果を実現するためのJSの具体的なコードを参考までに紹介します。具...

HTML+CSS3+JSで実装されたドロップダウンメニュー

成果を達成する html <div class="コンテナ"> &l...

HTML CSS3は画像表示効果を引き伸ばさない

1. transform 属性を使用して、画像を拡大せずに表示します (パスの問題は必要に応じて修正...

テーブルの作成、フィールドの追加、フィールドの変更、インデックスの追加によく使用される MySQL の SQL 文の概要

この記事では、テーブルの作成、フィールドの追加、フィールドの変更、インデックスの追加を行う一般的な ...

MySQL データベース監視ソフトウェア lepus の使用上の問題と解決策

lepus3.7 を使用して MySQL データベースを監視中に、次の問題が発生しました。このブログ...

MySQL インデックス プッシュダウンを 5 分で学ぶ

目次序文インデックス プッシュダウンとは何ですか?インデックスプッシュダウン最適化の原理インデックス...

CentOS8 システムをベースにした Gitlab を構築するために Docker を使用する詳細なチュートリアル

目次1. Dockerをインストールする2. GitLabをインストールする3. GitLabを初期...

MySQL の sql_mode モード例の詳細な説明

この記事では、MySQL の sql_mode モードについて例を挙げて説明します。ご参考までに、詳...

Docker が MySQL を作成する説明

1. MySQLイメージをダウンロードするコマンド: docker pull mysql 2. コン...

一般的なSQL削除ステートメントの原則の違いを理解するだけです

この記事では主に、SQL 削除ステートメント DROP、TRUNCATE、および DELETE の違...

分散監視システムにおけるZabbixのアクティブ、パッシブ、Web監視のプロセスの詳細な説明

前回の記事では、Zabbix のネットワーク検出機能について学習し、アクションと組み合わせてホストの...