Myqlのインデックスを作成する

Mysql のパフォーマンスを向上させるために、Mysql の検索速度を向上させ、Mysql データベースへの負担を軽減するためのインデックスを作成できます。Mysql インデックスと高度な使用法について話しましょう。

すべての MySQL 列タイプにインデックスを付けることができます。ストレージ エンジンに応じて、各テーブルのインデックスの最大数と最大インデックス長を定義します。
すべてのストレージ エンジンは、テーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。ほとんどのストレージ エンジンには、より高い制限があります。

現在、ストレージ エンジン モードに特に関連するストレージ タイプのインデックスは 2 つだけです (btree とハッシュ)。 . ハッシュインデックスを使用します

MySQLのbtreeインデックスとハッシュインデックスの違い
ハッシュインデックス構造の特殊性、その検索効率は非常に高く、必要なbtree(B-Tree)インデックスとは異なり、一度にインデックスの検索を行うことができます。ルート ノードからブランチ ノードに移動し、最終的にページ ノードに複数の IO アクセスを通じてアクセスできるため、ハッシュ インデックスのクエリ効率は btree (B-Tree) インデックスのクエリ効率よりもはるかに高くなります。

ハッシュインデックスは非常に効率的ですが、ハッシュインデックス自体はその特殊性により、主に以下のような多くの制限や欠点ももたらします。
(1) ハッシュ インデックスは =、<=>、IN、IS NULL、または IS NOT NULL クエリのみを満たすことができ、範囲クエリは使用できません。
ハッシュインデックスはハッシュ演算後のハッシュ値を比較するため、対応するハッシュアルゴリズムによる処理後のハッシュ値の大小関係により、等しい値のフィルタリングにのみ使用でき、範囲ベースのフィルタリングには使用できません。ハッシュ操作前とまったく同じであるという保証はありません。

(2) データの並べ替え操作を回避するためにハッシュ インデックスを使用することはできません。
ハッシュインデックスにはハッシュ計算後のハッシュ値が格納され、ハッシュ値の大小関係がハッシュ演算前のキー値と完全に同じであるとは限らないため、データベースはソート演算を回避するためにインデックスデータを使用できません。 ;

(3) 部分インデックス キーを使用してハッシュ インデックスをクエリすることはできません。
結合インデックスの場合、ハッシュ インデックスのハッシュ値を計算するときに、最初の 1 つまたは複数のハッシュ値を個別に計算するのではなく、結合されたインデックス キーがマージされてからハッシュ値が一緒に計算されます。結合インデックスのインデックス キー、ハッシュ インデックスも利用できません。

(4) ハッシュインデックスはいつでもテーブルスキャンを回避できません。
前に知ったように、ハッシュインデックスは、インデックスキーをハッシュ化した後、ハッシュ演算結果のハッシュ値と対応する行ポインタ情報をハッシュテーブルに格納することです。異なるインデックスキーは同じハッシュ値を持つため、たとえ特定のハッシュ キー値を満たすレコードの数をハッシュ インデックスから直接クエリすることはできません。代わりに、テーブル内の実際のデータにアクセスして対応する比較を実行し、対応する結果を取得する必要があります。

(5) ハッシュ インデックスが多数の等しいハッシュ値に遭遇した場合、そのパフォーマンスは必ずしも B ツリー インデックスのパフォーマンスよりも高いとは限りません。
選択性の低いインデックスキーの場合、ハッシュインデックスを作成すると、同じハッシュ値に関連付けられたレコードポインタ情報が多数存在します。この方法では、特定のレコードを見つけるのが非常に面倒になり、テーブル データへの複数回のアクセスが無駄になり、結果として全体的なパフォーマンスが低下します。B ツリー インデックスは、MySQL データベースで最も頻繁に使用されるインデックス タイプです。アーカイブ ストレージ エンジン。他のすべてのストレージ エンジンは B ツリー インデックスをサポートします。これは MySQL に限ったことではありません。実際、他の多くのデータベース管理システムでも、B ツリー インデックスが主要なインデックス タイプです。これは主に、B ツリー インデックスのストレージ構造がデータベース データの検索において特定の機能を備えているためです。とても良いパフォーマンスです。
一般的に言えば、MySQL の B ツリー インデックスの物理ファイルのほとんどはバランス ツリー構造に保存されます。つまり、実際に必要なデータはすべてツリーのリーフ ノードに保存され、任意のリーフへの最短パスになります。ノードはパスの長さがまったく同じであるため、これを B ツリー インデックスと呼びます。 もちろん、さまざまなデータベース (または MySQL のさまざまなストレージ エンジン) は、独自の B ツリー インデックスを保存するときにストレージ構造をわずかに変更する場合があります。 . 変換を行います。
たとえば、Innodb ストレージ エンジンの B ツリー インデックスで使用される実際のストレージ構造は実際には B+Tree です。これは、B ツリー データ構造に基づいて小さな変更が行われ、ストレージ インデックスが配置されることを意味します。各リーフ ノードには、キー関連の情報に加えて、そのリーフ ノードに隣接する次のリーフ ノードを指すポインタ情報も格納されます。これは主に、複数の隣接するリーフ ノードの取得を高速化するためです。
Innodb ストレージ エンジンには 2 つの異なる形式のインデックスがあり、1 つはクラスター形式の主キー インデックス (Primary Key) で、もう 1 つは他のストレージ エンジンと基本的に同じストレージ形式である通常の B です ( MyISAM ストレージ エンジンなど) - ツリー インデックス。このインデックスは Innodb ストレージ エンジンではセカンダリ インデックスと呼ばれます。
Innodb では、主キーを介してデータにアクセスするのが非常に効率的ですが、セカンダリ インデックスを介してデータにアクセスする場合、Innodb はまずセカンダリ インデックスの関連情報と対応するインデックス キーを介してリーフ ノードを取得し、次に必要になります。次に、リーフ ノードに格納されている主キー値を使用して、主キー インデックスを通じて対応するデータ行が取得されます。
MyISAM ストレージ エンジンの主キー インデックスと非主キー インデックスの違いは、主キー インデックスのインデックス キーが一意で空ではないことを除いて、非常にわずかです。さらに、MyISAM ストレージ エンジンのインデックスのストレージ構造は、基本的に Innodb のセカンダリ インデックスのストレージ構造と同じです。主な違いは、MyISAM ストレージ エンジンがインデックス キー情報をリーフ ノードに保存し、対応するストレージを直接保存できることです。データ行情報 (行番号など) は MyISAM データ ファイルに格納されますが、主キーのキー値情報は格納されません。

インデックスは、単一列インデックスと結合インデックスに分かれています。単一列インデックスは、インデックスに 1 つの列のみが含まれることを意味します。テーブルには複数の単一列インデックスを含めることができますが、これは結合されたインデックスではありません。結合インデックス。つまり、1 つのインデックスに複数の列が含まれます。
MySQL インデックス タイプには次のものが含まれます:
(1) 通常のインデックス。これは最も基本的なインデックスであり、制限はありません。次の作成メソッドがあります:

-- インデックスの作成

CREATE INDEX IndexName ON mytable(username(10)) ,city(10)); --結合インデックス

--indexName はインデックスです。 name、mytable テーブル名、username、city は列名、10 はプレフィックスの長さ、つまり列の左端の文字から始まるインデックスによって格納される情報の長さ、単位はバイト

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

--

テーブル構造を変更してインデックスを作成します

ALTER TABLE mytable ADD INDEX IndexName (username(10));

-- ALTER TABLE mytable ADD INDEX IndexName (username(10),city(10)) ;

-- ここでのインデックス名は記述する必要はありません。システムは username、username_2、username_3、... という名前を自動的に割り当てます。

--

テーブルを作成するときは、直接

CREATE TABLE mytable(

) と指定します。

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

INDEX IndexName (username(10))-- INDEX IndexName (username(10),city(10))

);

- - ここで、indexName インデックス名は省略することもできます

(2) 一意のインデックス。インデックス列の値が一意である必要があり、null 値である必要がある点を除いて、前の通常のインデックスと似ています。許可されています。複合インデックスの場合、列値の組み合わせは一意である必要があります。次の作成方法があります (通常のインデックスを作成する場合は、キーワード INDEX の前に UNIQUE を追加するだけです):

--

インデックスの作成

CREATE UNIQUE INDEXindexName ON mytable(username(10));

- -

テーブル構造を変更してインデックスを作成します

ALTER TABLE mytable ADD UNIQUE INDEXindexName (username(10));-- ALTER TABLE mytable ADD UNIQUE IndexName (username(10));と省略することもできます

--

作成 直接指定

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

UNIQUE INDEXindexName(username(10)) - - UNIQUE IndexName (username(10))

);

と省略することもできます (3) 主キー インデックス。NULL 値を許可しない特別な一意のインデックスです。テーブルの作成時に同時に作成される主キーは主キー インデックスです。テーブルには主キーを 1 つだけ指定する必要はありません。主キー インデックスは同時に一意のインデックスまたはフルテキスト インデックスにすることができますが、一意のインデックスまたはフルテキスト インデックスを同じインデックス内に共存させることはできません:

-- テーブル構造を変更して、 Index ALTER TABLE mytable ADD PRIMARY KEY (id);

-- テーブルの作成 CREATE TABLE を直接指定する場合 mytable(

id INT,

username VARCHAR(16),

age INT,PRIMARY KEY( id)
);



(4) フルテキスト インデックス、InnoDB ストレージ エンジンはフルテキスト インデックス作成をサポートしていません:

-- インデックスの作成 CREATE FULLTEXT INDEX IndexName ON mytable(username(10));
-- テーブル構造を変更してインデックスを作成します ALTER TABLE mytable ADD FULLTEXT INDEX IndexName (username(10)) ;

-- ALTER TABLE mytable ADD FULLTEXT IndexName (username(10));

と省略することもできます。

-- テーブル作成時にCREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR)(16),

age INT,
FULLTEXT INDEXindexName(username(10))を直接指定

-- FULLTEXT IndexName (username(10)))ENGINE=MYISAM;

-- テーブルの作成時にフルテキスト インデックスを作成します。テーブルのストレージ エンジンを、デフォルトの InnoDB ストレージ エンジンである MYISAM に設定します。 mysql の新しいバージョンはフルテキスト インデックスをサポートしていません

-- インデックスを削除します。 DROP INDEXindexName ON mytable;

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

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

以上、Mysql でのインデックス作成についてまとめましたので、今後皆様のお役に立てれば幸いです。

関連記事:

PHPでmyqlにデータを挿入すると文字化けが表示される

myql5.7.7の最適化された構成パラメータ_MySQL

以上がMyqlのインデックスを作成するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。