MySQL データベースのインデックス作成の原理と最適化戦略は何ですか?

1 インデックス

インデックスの概念

インデックスは、データ テーブル内のすべてのレコードへの参照ポインターを含む特別なファイルです。テーブル内の 1 つ以上の列にインデックスを作成し、インデックスのタイプを指定できます。各タイプのインデックスには独自のデータ構造実装があります。

インデックスの役割

データベース内のテーブル、データ、インデックス間の関係は、本、本の内容、本棚の書籍カタログ間の関係に似ています。インデックスは書籍カタログに似ており、データをすばやく検索して取得するために使用できます。インデックスによりデータベースのパフォーマンスが大幅に向上します。

インデックスの使用シナリオ

データベース テーブルの特定の列にインデックスを作成することを検討するには、次の点を考慮する必要があります。

• #Data ボリュームが大きいため、これらの列に対して条件付きクエリが実行されることがよくあります。

• このデータベース テーブルのこれらの列に対する挿入操作と変更操作の頻度は低いです。

• インデックスは追加のディスク領域を占有します。

2 インデックスの分類

• インデックスの保存構造から分割: BTree インデックス、ハッシュ インデックス、FULLTEXT フルテキスト インデックス、RTree インデックス

• アプリケーションレベルから分割：通常のインデックス、ユニークインデックス、主キーインデックス、複合インデックス

• インデックスのキー値の型、主キーから分割インデックス、補助インデックス (セカンダリ インデックス インデックス)

• データ ストレージとインデックス キー値の論理関係から分割: クラスター化インデックス (クラスター化インデックス) 非クラスター化インデックス (非クラスター化インデックス)

• インデックスの列数による分割: 単一列インデックス、複合インデックス

B-tree インデックスと B-tree インデックスの違い

相違点:

データは別の場所に保存されます: B ツリーはリーフ ノードに保存され、B -tree はすべてのノードに保存されます。

B ツリーの利点を反映しています。ノードはデータを保存しないため、1 つのノードにより多くのキーを保存できます。これによりツリーが短くなり、IO 操作の数が減ります。クエリのパフォーマンスは安定しています。各クエリはルート ノードからリーフ ノードまで移動し、クエリ パスの長さは同じです。つまり、各クエリの効率は同等で、時間計算量は O(log(n))## に固定されています。

#リーフ ノード ポインティング: B ツリーの隣接するリーフ ノードは、ポインタを介して接続されます。B ツリーは、B ツリーの利点を反映しません。

#すべてのリーフ ノードは、順序付けされたリンクを形成します。範囲検索を容易にするリスト

#3 インデックス操作

主キー インデックスの作成

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
-- 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

主キー インデックスの特性:

テーブルには最大 1 つの主キー インデックスを含めることができます。もちろん、主キーと一致するように作成することもできます

• 主キー インデックスは非常に効率的です (主キーを繰り返すことはできません) )

• 主キー インデックスの作成に使用される列は null にすることはできず、繰り返すこともできません

• 主キー インデックスの列は基本的に次のとおりです。 int

• 一意のインデックスの作成

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
-- 创建表以后再添加unique
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

一意のインデックスの機能:

テーブルには複数の一意のインデックスを持つことができます

• 高いクエリ効率

• 特定の列に一意のインデックスが作成されている場合は、この列に重複データが存在しないようにする必要があります

• 一意インデックスに null 以外が指定されている場合、主キー インデックスと同等になります。

• 通常のインデックス Create

--在表的定义最后，指定某列为索引
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name)
);
--创建完表以后指定某列为普通索引
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); 
-- 创建一个索引名为 idx_name 的索引
create table user10(id int primary key, name varchar(20), email varchar(30));
create index idx_name on user10(name)；

Characteristics通常のインデックスの:

テーブルには複数の通常のインデックスが存在する可能性があります。実際の開発では通常のインデックスがより一般的に使用されます

• 列の場合インデックスを作成する必要がありますが、列に重複した値がある場合は、通常のインデックスを使用する必要があります

• #クエリ インデックス

テーブル名からキーを表示します

• ##mysql> Goods\G

  ************ のキーを表示 1. row * **********
Table: Goods Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1
Column_name: Goods_id < ;= インデックスはどの列にありますか?
Collat​​ion: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE コメント:
セット内の 1 行 (0.00 秒)




テーブル名のインデックスを表示;
#desc テーブル名
インデックスの削除
主キー インデックスの削除: テーブル テーブル名を変更して主キーを削除;
他のインデックスを削除します: テーブル テーブル名を変更します。インデックス インデックス名を削除します。インデックス名は、テーブル名からのキーを表示するの Key_name フィールドです。

• mysql> alter table user10 drop index idx_name;

• インデックス インデックス名をテーブル名にドロップします

mysql> drop index name on user8

インデックス作成の原則
クエリ条件として頻繁に使用されるフィールドにはインデックスを作成する必要があります
一意性の低いフィールドは、クエリ条件として頻繁に使用される場合でも、単独でインデックスを作成するのには適していません。
非常に頻繁に更新されるフィールドインデックスの作成には適していません
where 句に現れないフィールドにはインデックスを作成しないでください

以上がMySQL データベースのインデックス作成の原理と最適化戦略は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。