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) のサンプル コード

推薦する

WeChatアプレットがスネークゲームを実装

この記事では、参考までに、スネークゲームを実装するためのWeChatアプレットの具体的なコードを紹介...

Linux で boost.python を使用して C++ 動的ライブラリを呼び出す方法

序文最近、C++ 動的ライブラリをテストするためにロボット フレームワークを使い始めました。ロボット...

HTMLタグオーバーフロー処理アプリケーション

CSSを使用してスクロールバーを変更する1.コンテンツがオーバーフローした場合のオーバーフロー設定水...

Vue は QR コード スキャン機能を実装します (スタイル付き)

必要: vue を使用して QR コードのスキャンを実現します。プラグイン: QRコードリーダー;プ...

Ubuntu 18.04 は mysql 5.7.23 をインストールします

以前、Ubuntu 16.04 に MySQL をスムーズにインストールしました。今回、Ubuntu...

JavaScript ループトラバーサルの 24 種類のメソッドをすべてご存知ですか?

目次序文1. 配列走査法1. 各() 2. マップ() 3. 〜のために4. フィルター() 5. ...

CSSクラス名の問題の詳細な説明

数字で始まる次の CSS クラス名は有効になりません。 .1番目{ 色: 赤; }有効な CSS ク...

LinuxでPythonの組み込みバージョンを削除する手順の詳細な説明

大きな落とし穴、Linuxシステムに付属するPythonのバージョンを簡単に削除しないでください1....

Nginx で複数のドメイン名がドメインをまたいでアクセスできるようにマップを使用する方法

一般的なNginx構成ではクロスドメインが可能 サーバー{ 11111 を聞いてください。 serv...

CSS のサイズと幅と高さのブラウザ解釈の違いに対する解決策

まずは例を見てみましょうコードをコピーコードは次のとおりです。 <!DOCTYPE html ...

UniappはBaidu Voiceを使用して録音をテキストに変換する機能を実現

3日間さまざまな困難に遭遇した後、ようやくこの機能を実現しました。正常に実装できる方法を見つける前に...

MySQL の制限パフォーマンス分析と最適化

1. 結論構文: 制限オフセット、行結論: 同じ行条件では、オフセット値が大きいほど、limitステ...

WeChatミニプログラムマップの使い方を詳しく解説

この記事の例では、WeChatアプレットマップで使用される具体的な実装コードを参考までに共有していま...

MySQL複合クエリの詳細な説明

UNIONの使用ほとんどの SQL クエリは、1 つ以上のテーブルからデータを返す単一の SELEC...

Vue はデータの変更をどのように追跡しますか?

目次背景例誤解 - コールスタックを表示するためにウォッチでブレークポイントを設定する正しいアプロー...