mysqlデータベースのインデックスにはどのような種類がありますか?設置方法とメリット・デメリット

この記事では、4 種類の masql データベース インデックスの説明に焦点を当てます。データベース インデックスを作成するにはどうすればよいですか? WHERE および JOIN に出現する列にはインデックスを付ける必要がありますが、MySQL は <、<=、=、>、>=、BETWEEN、IN、および場合によっては LIKE のみを使用するため、完全にインデックスを付ける必要はありません。この記事が皆さんのお役に立てれば幸いです。まず、インデックスとは何かを理解しましょう。インデックスは高速検索の鍵です。

MySQL インデックスの確立は、MySQL を効率的に運用するために非常に重要です。以下では、いくつかの一般的な MySQL インデックス タイプを紹介します

データベース テーブルでは、フィールドにインデックスを付けると、クエリ速度が大幅に向上します。 mytable テーブルを作成するとします:

コードは次のとおりです:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );

10,000 レコードをランダムに挿入しました。そのうちの 1 つは 5555 です。管理者。

username="admin" SELECT * FROM mytable WHERE username='admin'; のレコードを検索するとき、username にインデックスが確立されていれば、MySQL はスキャンせずに正確にレコードを見つけることができます。逆に、MySQL はすべてのレコードをスキャンします。つまり、10,000 レコードがクエリされます。

インデックスは、単一列インデックスと結合インデックスに分かれています。単一列インデックスは、インデックスに 1 つの列のみが含まれることを意味します。テーブルには複数の単一列インデックスを含めることができますが、これは結合されたインデックスではありません。結合インデックス。つまり、1 つのインデックスに複数の列が含まれます。

MySQL インデックス タイプには次のものが含まれます:

1. 通常のインデックス

これは最も基本的なインデックスであり、制限はありません。以下の作成メソッドがあります:

1. インデックスを作成します

コードは次のとおりです:

CREATE INDEXindexName ON mytable(username(length));

CHAR、VARCHAR 型の場合、長さはそれより小さくてもかまいませんフィールドの実際の長さよりも長く、BLOB 型および TEXT 型の場合は長さを指定する必要があります。以下同様です。

2. テーブル構造を変更します

コードは次のとおりです:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- テーブル作成時に直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16 ) NOT NULL, INDEX [indexName] (username(length)) );

-- インデックスを削除する構文:

DROP INDEX [indexName] ON mytable; 2. 一意のインデックス

前の通常のインデックスと同じです。インデックス列の値が一意である必要がある点を除き、インデックスは似ていますが、NULL 値が許可されます。複合インデックスの場合、列値の組み合わせは一意である必要があります。次の作成メソッドがあります:

コードは次のとおりです:

CREATE UNIQUE INDEXindexName ON mytable(username(length))

-- テーブル構造を変更します

ALTER mytable ADD UNIQUE [indexName] ON (username(length) )

- - 直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );


3. 主キーインデックス

特別な一意null 値を許可しないインデックス。通常、主キー インデックスはテーブルの作成時に同時に作成されます:

コードは次のとおりです:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ) ;

もちろん、ALTER Order を使用することもできます。テーブルには主キーを 1 つだけ持つことができることに注意してください。

4. 複合インデックス

単一列インデックスと複合インデックスを明確に比較するには、テーブルに複数のフィールドを追加します:

コードは次のとおりです:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR (16) NOT NULL , city VARCHAR(50) NOT NULL, age INT NOT NULL ); MySQL の効率をさらに引き出すには、結合インデックスの確立を検討する必要があります。名前、都市、年齢をインデックスに構築するだけです:

コードは次のとおりです:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]

テーブルを作成するとき、ユーザー名は 16 です。ここでは 10 を使用します。これは、一般に名前の長さが 10 を超えないためです。これにより、インデックス クエリが高速化され、インデックス ファイルのサイズが削減され、INSERT の更新速度が向上します。

テーブルに 3 つの単一列インデックスがあるように、ユーザー名、都市、年齢にそれぞれ単一列インデックスを作成すると、クエリ効率は上記の結合インデックスとは大きく異なり、結合インデックスよりもはるかに低くなります。 。現時点では 3 つのインデックスがありますが、MySQL は最も効率的であると思われる単一列インデックスのみを使用できます。

このような結合インデックスを確立することは、実際には、次の 3 セットの結合インデックスを確立することと同じです:

usernname,city,age usernname,city usernname 都市と年齢のような組み合わせインデックスがないのはなぜですか?これは、MySQL 複合インデックスの「左端のプレフィックス」の結果です。簡単に理解すると、組み合わせは左端から開始するだけです。これら 3 つの列を含むクエリだけでなく、次の SQL もこの結合インデックスを使用します:
[code]
SELECT * FROM mytable WHREE username="admin" AND city="Zhengzhou" SELECT * FROM mytable WHREE username ="admin"

次のものは使用されません:

コードは次のとおりです:
SELECT * FROM mytable WHREE age=20 AND city="鄭州" SELECT * FROM mytable WHREE city=" 鄭州 "

5. インデックスの作成方法

これまでインデックスの作成方法を学習しましたが、どのような状況でインデックスを作成する必要があるのでしょうか?一般に、WHERE および JOIN に出現するカラムにはインデックスを付ける必要がありますが、MySQL では <、<=、=、>、>=、BETWEEN、IN のインデックスのみが使用され、場合によっては LIKE でインデックスが使用されるため、これが完全に当てはまるわけではありません。索引。例:

コードは次のとおりです:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州'

これでmytable テーブルの userame も JOIN 句に出現するため、これもインデックスする必要があります。

インデックスを作成する必要があるのは特定の LIKE のみであると先ほど述べました。 MySQL はワイルドカード文字 % と _ で始まるクエリを作成するときにインデックスを使用しないためです。たとえば、次の文はインデックスを使用します:

コードは次のとおりです:
SELECT * FROM mytable WHERE username like'admin%'
次の文はインデックスを使用しません:

コードは次のとおりです:

SELECT * FROM mytable WHEREt Name like'%admin'

したがって、LIKE を使用するときは、上記の違いに注意する必要があります。

6. インデックスの欠点

インデックスを使用する利点はすべて上で述べましたが、インデックスを過度に使用すると悪用が発生します。したがって、インデックスには欠点もあります:

1. インデックスはクエリ速度を大幅に向上させますが、テーブルの INSERT、UPDATE、DELETE などのテーブルの更新速度も低下します。テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるためです。

2. インデックスファイルを作成すると、ディスクスペースが占有されます。通常、この問題は深刻ではありませんが、大きなテーブルに複数の結合インデックスを作成すると、インデックス ファイルが急速に拡張します。

MySQL に大規模なデータ テーブルがある場合、インデックスは効率を向上させるための 1 つの要素にすぎません。最適なインデックスを構築したりクエリ ステートメントを最適化するために時間を費やす必要があります。

7. インデックスを使用する際の注意事項:

インデックスを使用する場合、いくつかのヒントと注意事項があります:

1. インデックスには NULL 値が含まれる列は含まれません

列に NULL が含まれている限り複合インデックス内の 1 つの列に NULL 値が含まれている限り、その列は複合インデックスに対して無効になります。したがって、データベースを設計するときは、フィールドのデフォルト値を NULL にしないでください。

2. 文字列のインデックス付けには短いインデックス

を使用し、可能であればプレフィックスの長さを指定する必要があります。たとえば、CHAR(255) 列がある場合、ほとんどの値が最初の 10 文字または 20 文字内で一意である場合は、列全体にインデックスを付けないでください。短いインデックスはクエリ速度を向上させるだけでなく、ディスク領域と I/O 操作を節約します。

3. インデックス列のソート

MySQL クエリはインデックスを 1 つだけ使用するため、インデックスが where 句で使用されている場合、order by の列はインデックスを使用しません。したがって、データベースのデフォルトの並べ替えで要件を満たすことができる場合は、並べ替え操作を使用しないでください。必要に応じて、これらの列に対して複合インデックスを作成することをお勧めします。

4. like ステートメントの操作

一般に、like 操作の使用は推奨されません。使用する必要がある場合は、その使用方法も問題になります。 「%aaa%」のようにインデックスは使用されませんが、「aaa%」のようにインデックスが使用されます。

5. 列に対して操作を実行しないでください。

コードは次のとおりです:
select * from users where YEAR(adddate)<2007;

は各行に対して操作を実行します。これにより、インデックスが失敗し、テーブルがいっぱいになります。 scan なので、次のように変更できます:

コードは次のとおりです:

select * from users where adddate<'2007-01-01';

6 NOT IN および <> 操作は使用しないでください。

上記では、MySQL インデックス タイプを紹介します。お役に立てれば幸いです。

2 つのインデックス作成方法:

B ツリー インデックス

注: 広い観点から見ると、これらはすべてバランス ツリーを使用しますが、具体的な実装に関しては、各ストレージ エンジンが若干異なります。たとえば、厳密に言えば、NDB エンジンは T ツリー

Myisam を使用します。innodb では、B ツリーの理論的なクエリ時間の計算量は O(log2 (N-1)) です。 , N データテーブル内の行数です

ハッシュインデックス

メモリストレージエンジンを使用するテーブルの場合、デフォルトはハッシュインデックスです。ハッシュの理論的なクエリ時間計算量はO(1)です

関連する推奨事項:

mysqlインデックス作成の使い方とメリット・デメリットを徹底解説

ページ 1/2 のインデックス作成の長所と短所

以上がmysqlデータベースのインデックスにはどのような種類がありますか?設置方法とメリット・デメリットの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。