MySQLクエリの冗長インデックスと未使用のインデックス操作

MySQL 5.7 以降のバージョンでは、冗長インデックス、重複インデックス、およびインデックスを使用していないビューを直接クエリできます。直接クエリできます。

冗長インデックスと重複インデックスをクエリする

schema_redundant_indexes から sys * を選択します。

未使用のインデックスをクエリする

sys.schema_unused_indexes から * を選択します。

バージョン5.6および5.5で使用する場合は、ビューをSQLステートメントクエリに変換するだけです。

冗長インデックスと重複インデックスをクエリする

Select A.`table_schema`、A.`table_name`、A.`index_name`、A.`index_columns`、b.`index_name`、b.`index_columns`、concat（ 'alter table `'、A .`table_schema`、 '` `。 （ `information_schema`.`Statistics`.`table_schema`を選択しますon_unique`）として「non_unique`」、max（isnull（ `information_schema`.`Statistics`.`sub_part`）、0,1）、0,1））として` subpart_exists`、 `information_schema`.`statistics` c separator '、'） `information_schema`.`Statistics`から「index_columns`（` information_schema`.`Statistics」 a '））） `information_schema`.`statistics`.`table_schema`、` information_schema`.`statistics`.`table_name`、 `information_schema`.`Statistics`.`index_name`） `Information_schema`.`statistics`.`table_name` as` table_name`.`Statistics`.`index_name` as `index_name`、max（` information_schema`.`statistics `.`non_unique`） `non_unique`（non_unique`） .`sub_part`）、0,1）） `subpart_exists`、group_concat（` information_schema`.`statistics`.`column_name` `statistics `.`seq_in_index` asc separator`） Schema`.`Statistics`.`index_type` = 'btree'）and（ `information_schema`.`Statistics`.`table_schema` in（ 'mysql'、 'sys'、 'Information_schema'、 'performance_schema'））））） a`.`statistics`.`table_name`、 `information_schema`.`Statistics`.`index_name`）b on（（（a.`table_schema」）and（a.`table_name` = b.`table_name`））））））） olumns` = b.`index_columns`）and（a.`non_unique`> b.`non_unique`）または（a.`non_unique` = b.`non_unique`）and（（a.`index_name` = 'primary'） （（locate（concat（a.`index_columns」、 '）、b.`index_columns`）= 1）および（a.`non_unique` = 1））または（（locate（concat（b.`index_columns」）、'）、a.`index_unique` = 0）;

未使用のインデックスをクエリする

`information_schema`.`statistics`.`table_schema`を選択します_unique`） `non_unique`、max（isnull（` information_schema`.`sub_part`）、0,1）） `subpart_exists`、` information_schema`.`Statistics ` 「Information_schema」から「index_columns」として（ `information_schema`.`statistics`.`index_type` = 'btree'）および（` information_schema`.`statistics ` '））） `Information_schema`.`statistics`.`table_schema`、` information_schema`.`statistics`.`table_name`、 `information_schema`.`sstatistics`.`index_name`

補足: mysql ID 残り index_mysql 重複インデックス、冗長インデックス、未使用インデックスの定義と検索

1. 冗長な重複インデックス

MySQL では、同じ列に複数のインデックスを作成できます。意図的であるかどうかにかかわらず、MySQL は重複したインデックスを個別に維持する必要があり、クエリを最適化するときにオプティマイザもそれらを 1 つずつ考慮する必要があるため、パフォーマンスに影響します。重複インデックスとは、同じ列に同じ順序で作成された同じタイプのインデックスを指します。このような重複の作成は避け、発見次第すぐに削除する必要があります。ただし、異なるクエリ要件を満たすために、同じ列に異なるタイプのインデックスを作成することは可能です。

冗長インデックスと重複インデックスにはいくつかの違いがあります。インデックス (a,b) を作成する場合、インデックス (a) は前のインデックスのプレフィックス インデックスにすぎないため、冗長インデックスになります。したがって、(a,b) は (a) としても使用できます。ただし、(b,a) は冗長インデックスではなく、インデックス (b) も冗長インデックスではありません。これは、b がインデックス (a,b) の左端のプレフィックス列ではないためです。さらに、同じ列に作成された他の異なるタイプのインデックス (ハッシュ インデックスやフルテキスト インデックスなど) は、btree インデックスの冗長インデックスにはなりません。

さらに、セカンダリ インデックス (a、id) の場合、id は主キーです。innodb の場合、主キー列はすでにセカンダリ インデックスに含まれているため、これも冗長インデックスです。ほとんどの場合、冗長インデックスは不要であり、新しいインデックスを作成するのではなく、既存のインデックスを拡張する必要があります。ただし、既存のインデックスを拡張するとサイズが大きくなりすぎて、そのインデックスを使用する他のクエリのパフォーマンスに影響するため、パフォーマンス上の理由から冗長インデックスが必要になる場合があります。たとえば、整数列にインデックスがあり、インデックスを拡張するために long varchar 列を追加する必要がある場合、特にこのインデックスをカバー インデックスとして使用するクエリがある場合、またはこれが MyISAM テーブルであり、範囲クエリが多数ある場合 (MyISAM プレフィックス圧縮のため)、パフォーマンスが大幅に低下する可能性があります。

たとえば、myisam エンジンのテーブル userinfo には 100 万行あり、各 state_id 値には約 20,000 行あり、state_id 列には次のクエリに役立つインデックスがあります。たとえば、select count(*) from userinfo where state_id=5; 1 秒あたり 115 QPS でテスト済み

state_id 列のインデックスは、QPS が 1 秒あたり 10 の次のクエリにはあまり役立ちません。

state_id=5 の場合、userinfo から state_id、city、address を選択します。

state_id インデックスを (state_id、city、address) に拡張すると、2 番目のクエリのパフォーマンスは速くなりますが、最初のクエリは遅くなります。両方のクエリを高速化するには、state_id 列のインデックスを冗長化する必要があります。ただし、InnoDB テーブルの場合、InnoDB はインデックス圧縮を使用しないため、最初のクエリに対する非冗長 state_id 列インデックスの効果は明ら​​かではありません。MyISAM テーブルと InnmodB テーブルに対して異なるインデックス戦略を使用した選択クエリの QPS テスト結果は次のとおりです (次のテスト データは参考用です)。

state_id 列のみにインデックスが付けられます。state_id_2 列のみにインデックスが付けられます。同時に 2 つのインデックスが存在します。

myisam、最初のクエリ 114.96 25.40 112.19

myisam、2番目のクエリ 9.97 16.34 16.37

innodb、最初のクエリ 108.55 100.33 107.97

innodb、2番目のクエリ 12.12 28.04 28.06

上図からわかるように、両方のインデックスを使用する場合、コストが高くなるというデメリットがあります。以下は、異なるインデックス戦略で InnoDB テーブルと MyISAM テーブルに 100 万行のデータを挿入する速度です (以下のテスト データは参考用です)。

state_id列インデックスのみが同時に2つのインデックスを持ちます

InnoDB、両方のインデックスに十分なコンテンツがある場合、80秒136秒

MyISAM、1つのインデックスのみに十分なコンテンツがある場合 72秒 470秒

ご覧のとおり、エンジンに関係なく、インデックスの数が増えるほど、挿入速度は遅くなります。特に、新しいインデックスを追加した後にメモリのボトルネックに達した場合は遅くなります。冗長インデックスと重複インデックスの解決方法は簡単です。削除するだけです。しかし、最初に行うべきことは、そのようなインデックスを見つけることです。information_schema テーブルにアクセスするための複雑なクエリをいくつか実行することで、それらを見つけることができます。ただし、より簡単な方法が 2 つあります。shlomi noach の common_schema のいくつかのビューを使用して、それらを見つけます。また、percona ツールキットの pt-dupulicate-key-checker ツールを使用することもできます。このツールは、テーブル構造を分析して冗長インデックスと重複インデックスを見つけます。大規模なサーバーの場合は、外部ツールを使用する方が適切です。サーバー上に大量のデータや多数のテーブルがある場合、information_schema テーブルをクエリすると、パフォーマンスの問題が発生する可能性があります。 pt-dupulicate-key-checker ツールを使用することをお勧めします。

インデックスを削除するときは十分注意してください。

InnoDB エンジン テーブルに where a=5 order by id のようなクエリがある場合、インデックス (a) が非常に役立ちます。インデックス (a,b) は、実際には (a,b,id) インデックスです。where a=5 order by id のようなクエリの場合、このインデックスはソートには使用できず、ファイル ソートのみに使用できます。したがって、計画されたインデックスの変更を再確認するには、Percona Toolbox の pt-upgrade ツールを使用してください。

2. 未使用のインデックス

冗長なインデックスや重複したインデックスに加えて、サーバーでまったく使用されないインデックスが存在する場合があります。このようなインデックスは完全に冗長であるため、削除することを検討することをお勧めします。未使用のインデックスを見つけるのに役立つツールが 2 つあります。

A: まず、Percona Server または MariaDB で userstat=ON サーバー変数をオンにします。デフォルトではオフになっています。次に、サーバーをしばらく実行し、information_schema.index_statistics をクエリして各インデックスの使用頻度を調べます。

B: Percona ツールキットの pt-index-usage ツールを使用します。このツールは、クエリ ログを読み取り、ログ内の各クエリを説明し、Guanyu インデックスとクエリに関するレポートを出力します。このツールは、使用されていないインデックスを見つけるだけでなく、クエリの実行プランを理解することもできます。たとえば、場合によっては、いくつかの類似したクエリが異なる方法で実行されるため、サーバーの品質が時々低下するクエリを見つけるのに役立ちます。このツールは、クエリ結果を容易にするために、結果を MySQL テーブルに書き込むこともできます。

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