MySQL インデックスの使用方法 (単一列インデックスと複数列インデックス)

MySQL インデックスの使用方法 (単一列インデックスと複数列インデックス)

1. 単一列インデックス

どの列にインデックスを作成するかを選択することは、パフォーマンス最適化プロセスにおける最も重要なステップの 1 つです。インデックス作成に考慮できる列には、WHERE 句に表示される列と JOIN 句に表示される列の 2 つの主な種類があります。次のクエリを考えてみましょう。

年齢を選択 ## 人からのインデックスは使用しない where firstname='Mike' ## インデックスの使用を検討 AND lastname='Sullivan ## インデックスの使用を検討

このクエリは前のクエリとは少し異なりますが、それでも単純です。 age は Select 部分で参照されるため、MySQL は列選択操作を制限するために age を使用しません。したがって、このクエリでは、age 列にインデックスを作成する必要はありません。

より複雑な例を次に示します。

people.age を選択します。## インデックス town.name は使用しないでください。## インデックスは使用しないでください。FROM people LEFT JOIN town ON people.townid=town.townid ## インデックスの使用を検討してください。Where firstname='Mike' ## インデックスの使用を検討してください。AND lastname='Sullivan' ## インデックスの使用を検討してください。

前の例と同様に、firstname と lastname は Where 句に出現するため、これら 2 つの列にインデックスを作成する必要があります。さらに、town テーブルの townid 列が結合句に出現するため、この列にインデックスを作成することを検討する必要があります。

では、Where 句と Join 句に現れるすべての列にインデックスを付ける必要があると単純に考えればよいのでしょうか?ほぼそうですが、まだ完全ではありません。列を比較するために使用される演算子の種類も考慮する必要があります。 MySQL は、<、<=、=、>、>=、BETWEEN、IN、および場合によっては LIKE の演算子に対してのみインデックスを使用します。

他のオペランドがワイルドカード文字 (% または _) で始まらない場合、LIKE 操作でインデックスを使用できます。

例えば:

people から peopleid を選択 where firstname LIKE 'Mich%'

このクエリはインデックスを使用しますが、次のクエリはインデックスを使用しません。

people FROM people、firstname LIKE '%ike' の peopleid を選択します。

2. 複数列インデックス

インデックスは、単一列インデックスまたは複数列インデックスにすることができます。以下では、具体的な例を使用して、これら 2 つのインデックスの違いを説明します。次のような people テーブルがあるとします。

テーブル人を作成する(  
peopleid SMALLINT NOT NULL AUTO_INCREMENT、  
名 CHAR(50) NOT NULL,  
姓 CHAR(50) NOT NULL,  
年齢 SMALLINT NOT NULL、  
townid SMALLINT NOT NULL、  
主キー (peopleid) );

people テーブルに挿入したデータは次のとおりです。

このデータ スニペットには、「Mikes」という名前の人が 4 人 (姓が Sullivans が 2 人、姓が McConnells が 2 人)、年齢が 17 歳の人が 2 人、珍しい名前が Joe Smith の人が 1 人います。

このテーブルの主な目的は、指定されたユーザーの名、姓、年齢に基づいて、対応する peopleid を返すことです。たとえば、17 歳の Mike Sullivan というユーザーの peopleid を見つけたいとします。

人物IDを選択 
人々から  
firstname='Mike'の場合  
   AND 姓='Sullivan' AND 年齢=17;

クエリを実行するたびに MySQL がテーブル全体をスキャンしないようにするため、インデックスの使用を検討する必要があります。

まず、firstname、lastname、age などの単一の列にインデックスを作成することを検討できます。 firstname 列にインデックスを作成すると (Alter TABLE people ADD INDEX firstname (firstname);)、MySQL はすぐにこのインデックスを使用して、firstname='Mike' のレコードに検索範囲を制限し、次にこの「中間結果セット」に対して他の検索条件を実行します。まず、lastname が "Sullivan" と等しくないレコードを除外し、次に age が 17 と等しくないレコードを除外します。レコードがすべての検索条件を満たすと、MySQL は最終的な検索結果を返します。

firstname 列にはインデックスが付けられているため、MySQL はテーブル全体をスキャンするよりもはるかに効率的ですが、それでも MySQL では実際に必要な数よりもはるかに多くのレコードをスキャンする必要があります。 firstname 列のインデックスを削除して lastname 列または age 列のインデックスを作成することもできますが、全体的には、どの列にインデックスを作成しても検索効率は同様です。

検索効率を向上させるには、複数列のインデックスの使用を検討する必要があります。 firstname、lastname、age の複数列インデックスを作成すると、MySQL は 1 回の検索で正しい結果を見つけることができます。この複数列インデックスを作成するための SQL コマンドは次のとおりです。

テーブルを変える人々  
インデックス fname_lname_age (firstname,lastname,age); を追加します。

インデックス ファイルは B ツリー形式で保存されるため、MySQL はすぐに適切な名、適切な姓、最後に適切な年齢に移動できます。データ ファイル内のレコードをスキャンしなくても、MySQL はターゲット レコードを正しく検出しました。

では、firstname、lastname、age の 3 つの列にそれぞれ単一列のインデックスを作成した場合、firstname、lastname、age に複数列のインデックスを作成した場合と同じ効果が得られるでしょうか。

答えは「いいえ」です。この2つはまったく異なります。クエリを実行する場合、MySQL は 1 つのインデックスのみを使用できます。単一列インデックスが 3 つある場合、MySQL は最も制限の厳しいインデックスを選択しようとします。ただし、最も制限の厳しい単一列インデックスであっても、firstname、lastname、age 列の複数列インデックスよりははるかに制限が緩くなります。

3. 複数列インデックスの左端プレフィックス

複数列インデックスには、左端プレフィックスと呼ばれる概念に反映される別の利点もあります。前の例を引き続き検討すると、firstname、lastname、age 列に複数列のインデックスが作成され、このインデックスを fname_lname_age と呼びます。 MySQL は、検索条件が次の列の組み合わせである場合に fname_lname_age インデックスを使用します。

名、姓、年齢
名、姓
ファーストネーム

別の観点から見ると、これは (firstname, lastname, age)、(firstname, lastname)、(firstname) の組み合わせでインデックスを作成することと同じです。次のクエリはすべて fname_lname_age インデックスを使用できます。

peopleからpeopleidを選択  
firstname='Mike' AND lastname='Sullivan' AND age='17'; の場合、
peopleからpeopleidを選択  
firstname='Mike' かつ lastname='Sullivan' の場合、
peopleからpeopleidを選択  
firstname='Mike'; の場合

次のクエリではfname_lname_ageインデックスを使用できません。

peopleからpeopleidを選択  
lastname='Sullivan'; の場合
peopleからpeopleidを選択  
age='17'の場合
peopleからpeopleidを選択  
lastname='Sullivan' AND age='17'; の場合

この記事はこれで終わりです。後ほど、123WORDPRESS.COM の編集者が MySQL インデックスに関する記事をさらに紹介する予定です。

以下もご興味があるかもしれません:
  • MySQL インデックスタイプの概要と使用上のヒントと注意事項
  • MySQLのINサブクエリによってインデックスが使用できなくなる問題を解決する
  • MySql インデックスの詳細な紹介と正しい使用方法
  • MySQLはインデックスを使用してクエリを最適化します
  • Mysql テーブル作成とインデックス使用仕様の詳細な説明
  • MySQL インデックスの正しい使い方とインデックスの原理の詳細な説明
  • MySQL インデックスの使用戦略と最適化 (高パフォーマンス インデックス戦略)
  • MySQLでインデックスを使用する方法を簡単に紹介します
  • MySQL でインプレースおよびオンライン メソッドを使用してインデックスを作成するチュートリアル
  • MySQL インデックス使用状況監視スキル (収集する価値あり!)

<<:  知っておくべき 7 つのネイティブ JS エラーの種類

>>:  docker 環境でのデータベース バックアップ (postgresql、mysql) のサンプル コード

推薦する

mysql8.0.11をインストールしてrootパスワードを変更し、navicat for mysqlに接続するアイデアの詳細な説明

1.1. ダウンロード:公式ウェブサイトから zip パッケージをダウンロードします。私は 64 ビ...

Docker での WSL の構成と変更の問題について

https://docs.microsoft.com/ja-jp/windows/wsl/wsl-...

require loaderの実装原理の深い理解

序文Node は新しいプログラミング言語ではなく、JavaScript のランタイムに過ぎないとよく...

インタラクションデザインと心理学の驚くべきつながり18選

デザイナーは心理学を理解する必要があるデザイナーが知るべき心理学という本は非常に興味深いです。まず、...

portainer を使用してリモート docker に接続するチュートリアル

Portainer は、Docker ホストと Docker Swarm クラスターの管理に使用でき...

Vueの7つの値転送メソッドの詳細な説明

1. 父から息子へ子コンポーネントにpropsフィールドを定義し、その型は配列です (フィールド値の...

MySQLデータベースに他のIPアドレスからアクセスできない問題の解決策

序文先ほどのプロジェクトを参考にすると、環境は整いました。プロジェクトの準備と検証の段階で、問題が発...

Docker イメージを Docker Hub にプッシュする実装

イメージが正常にビルドされると、Docker 環境があれば使用できますが、イメージを Docker ...

grpc のリバース プロキシとして nginx を使用する場合の落とし穴の概要

背景ご存知のとおり、nginx は高性能な Web サーバーであり、負荷分散やリバース プロキシによ...

ノードにおけるhttpモジュールの使用と実行プロセス

ノードにおけるhttpの役割は何ですか? httpモジュールの役割は、サーバーの作成と記述を支援する...

MySQLがフルテーブルスキャンを実行するいくつかの状況

目次ケース1:ケース2:ケース3:簡単にまとめると:過去 2 日間で、完全なテーブル スキャンを引き...

テーブルパーティションとパーティション分割とは何ですか?MySqlデータベースパーティションとテーブルパーティション分割方法

1. テーブルとパーティションを分割する必要があるのはなぜですか?日常の開発では、大きなテーブルに遭...

mysql 複数テーブル接続削除関数の削除

単一のテーブルを削除する: tableName から columnName = value を削除し...

統計量に対するmySql SQLクエリ操作

余計なことは言わないで、コードだけ見てみましょう〜 プロジェクト番号を選択、 sum(case wh...

Spring Boot 2.4 の新機能、ワンクリックビルド、Docker イメージプロセスの詳細説明

背景開発プロセス中に Docker コンテナ化をサポートするために、通常は Maven を使用してコ...