mysqlインデックスの基本操作まとめ(4)_MySQL

1. インデックスを使用する理由:
データベース オブジェクトのインデックスは、主にテーブルからデータを取得する速度を向上させるために、実際には本の目次に似ています。データはデータベース テーブルに格納されるため、インデックスはデータベース テーブル オブジェクト上に作成され、テーブル内の 1 つまたは複数のフィールドによって生成されたキーで構成されます。これらのキーはデータ構造 (B ツリーまたはハッシュ テーブル) に格納されます。 MySQL を使用すると、キーに関連付けられたフィールドを迅速かつ効率的に検索できます。インデックスの格納タイプに応じて、インデックスは B ツリー インデックス (BTREE) とハッシュ インデックス (HASH) に分類できます。注: InnoDB および MyISAM ストレージ エンジンは BTREE タイプのインデックスをサポートし、MEMORY ストレージ エンジンは HASH タイプのインデックスをサポートします。デフォルトでは、前者のインデックスが使用されます。
MySQL は、通常のインデックス、一意のインデックス、全文インデックス、単一列インデックス、複数列インデックス、空間インデックスの 6 種類のインデックスをサポートしています。
次の状況がインデックスの作成に適しています:
1. 頻繁にクエリされるフィールド。つまり、WHERE 句に表示されるフィールドです。
2. グループ内のフィールド、つまり GROUP BY 句に表示されるフィールド。
3. 依存関係がある子テーブルと親テーブル、つまり主キーまたは外部キー フィールド間の結合クエリ。
4. 一意の完全制約フィールドを設定します。

2. インデックスを作成して表示します:
インデックス操作には、インデックスの作成、インデックスの表示、インデックスの削除が含まれます。いわゆるインデックスの作成とは、テーブルの 1 つまたは複数のフィールドにインデックスを作成することです。MySQL では、通常、テーブルの作成時にインデックスを作成する、既存のテーブルにインデックスを作成する、の 3 つの方法があります。 SQL ステートメント ALTER TABLE を渡すと、インデックスが作成されます。
2.1 通常のインデックスを作成して表示する:
いわゆる通常のインデックスとは、インデックスを作成するときに制限 (一意である、空でないなど) が適用されていないことを意味します。このタイプのインデックスは、任意のタイプのフィールドに作成できます。
2.1.1 テーブル作成時に通常のインデックスを作成します:

構文形式は次のとおりです:

リーリー

注: INDEX または KEY パラメータはフィールドをインデックスとして指定するために使用され、「インデックス名」パラメータは作成されたインデックスの名前を指定するために使用され、「属性名 1」パラメータは作成されたインデックスの名前を指定するために使用されます。インデックスの長さを指定するには、「ASC|DESC」パラメータを使用して昇順または降順を指定します。
注: インデックスを作成するときに、インデックスの長さを指定できます。これは、テーブルのインデックスの最大数とインデックスの最大長が異なるストレージ エンジンで定義されるためです。 MySQL でサポートされるストレージ エンジンは、テーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。

例:

リーリー

説明:
SHOW CREATE TABLE t_dept G; でインデックスが正常に作成されたかどうかを確認できます。 EXPLAIN SELECT * FROM t_dept WHERE deptno=1G; を使用して、データベース テーブルのインデックスが使用されているかどうかを確認できます。実行結果の possible_keys フィールドと key フィールドの値が両方とも作成されたインデックス名index_deptno である場合、インデックスはすでに存在し、有効化されていることを意味します。

2.1.2 既存のテーブルに通常のインデックスを作成します:

SQL ステートメント CREATE INDEX によって実現され、その構文は次のとおりです:

CREATE INDEX インデックス名 ON テーブル名 (属性名 [(長さ)] [ADC|DESC])

2.1.3 SQL ステートメント ALTER TABLE を使用して通常のインデックスを作成します:

文法形式は次のとおりです:

ALTER TABLE テーブル名 ADD INDEX|KEY インデックス名 (属性名 [(長さ)] [ASC|DESC]);

2.2 一意のインデックスを作成して表示する:
いわゆる一意のインデックスは、インデックスを作成するときに、制限されたインデックスの値が一意である必要があることを意味します。このタイプのインデックスは、レコードのクエリをより迅速に行うのに役立ちます。 MySQLではインデックスの作成方法により、自動インデックス作成と手動インデックス作成の2種類に分けられます。 いわゆる自動インデックス作成とは、データベース テーブルに整合性制約が設定されている場合、システムによってテーブルに自動的にインデックスが作成されることを意味します。いわゆる手動インデックスとは、テーブルにインデックスを手動で作成することを指します。テーブル内のフィールドが主キーまたは一意の整合性制約として設定されると、システムはフィールドに関連付けられた一意のインデックスを自動的に作成します。

2.2.1 テーブル作成時に一意のインデックスを作成します:

文法形式は次のとおりです:

リーリー

説明: UNIQUE INDEX または UNIQUE KEY は、一意のインデックスを作成することを意味します。

2.2.2 既存のテーブルに一意のインデックスを作成します:

SQL ステートメント CREATE UNIQUE INDEX によって実現され、構文は次のとおりです:

CREATE UNIQUE INDEX インデックス名 ON テーブル名 (属性名 [(長さ)] [ASC|DESC]);

2.2.3 SQL ステートメント ALTER TABLE を使用して一意のインデックスを作成します:

文法形式は次のとおりです:

ALTER TABLE table_name ADD UNIQUE INDEX|KEY インデックス名 (属性名 [(長さ)] [ASC|DESC]);

2.3 创建和查看全文索引： 
    全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能更加快速的查询数据量较大的字符串类型的字段。MySQL只能在存储引擎为MyISAM的数据库表上创建全文引擎。默认情况下，全文引擎的搜索执行方式为不区分大小写，如果全文引擎所关联的字段为二进制数据类型，则以区分大小写的方式执行。 

2.3.1 创建表时创建全文索引：

语法形式为：

CREATE TABLE table_name (
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 FULLTEXT INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC])
);

2.3.2 在已经存在的表上创建全文索引：

语法形式为：
CREATE FULLTEXT INDEX 索引名
ON 表名 (属性名 [(长度)] [ASC|DESC]);

2.3.3 通过SQL语句ALTER TABLE 创建全文索引：

语法形式为：
ALTER TABLE table_name
ADD FULLTEXT INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

2.4 创建和查看多列索引：
所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
2.4.1 创建表时创建多列索引：

语法形式如下：

CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 INDEX|KEY [索引名] 
 (属性名1 [(长度)] [ASC|DESC]),
 ......
 (属性名1 [(长度)] [ASC|DESC])
);

上述语句创建索引时，所关联的字段至少大于一个字段。

2.4.2 在已经存在的表上创建多列索引：

语法形式为：

CREATE INDEX 索引名 ON 表名 (
 属性名 [(长度)] [ASC|DESC],
 ......
 属性名n [(长度)] [ASC|DESC]
); 

2.4.3 通过SQL语句ALTRE TABLE 创建多列索引：

语法形式为：
ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名 [(长度)] [ASC|DESC],属性名n [(长度)] [ASC|DESC]);

3. 删除索引：

删除索引的语法形式：
DROP INDEX index_name ON table_name

4. 查看索引：

查看索引的语法形式：
SHOW INDEX FROM table_name

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。