1. インデックスの概要
(1)インデックスの意味と具体性
(2)インデックスの分類
(3)インデックスの設計原則
2. インデックスを作成します
(1) テーブル作成時にインデックスを作成します
(2) 既存のテーブルにインデックスを作成します
(3) インデックスを削除します
(無料学習の推奨事項 :mysql ビデオ チュートリアル )
1. インデックスの概要
インデックスは、列内の特定の値を持つ行をすばやく検索するために使用されます。 。インデックスを使用しない場合、MySQL は最初のレコードから始めて、関連する行が見つかるまでテーブル全体を読み取る必要があります。テーブルが大きくなるほど、データのクエリにかかる時間が長くなります。テーブル内のクエリされたカラムにインデックスがある場合、MySQL はすべてのデータを確認することなく、データ ファイルを検索する場所にすぐにアクセスできます。
意味: インデックスはディスクに保存される別個のデータベース構造であり、データ テーブル内のすべてのレコードへの参照ポインターが含まれています。 1 つ以上の列で特定の値を持つ行をすばやく検索するために使用されます。
インデックスはストレージ エンジンに実装されるため、各ストレージ エンジンのインデックスは必ずしも完全に同じであるとは限りません。また、各ストレージ エンジンがすべてのインデックス タイプをサポートするとは限りません。ストレージ エンジンに応じて、各テーブルのインデックスの最大数と最大インデックス長を定義します。すべてのストレージ エンジンは、テーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。ほとんどのストレージ エンジンには、より高い制限があります。
MySQL には、BTREE と HASH という 2 つのストレージ タイプのインデックスがあり、これらは特にテーブルのストレージ エンジン (MyISAM および InnoDB ストレージ エンジンのみ) に関連しています。 BTREE インデックスのサポート; MEMORY/HEAL ストレージ エンジンは、HASH インデックスと BTREE インデックスをサポートできます。
インデックスの利点:
1. 一意のインデックスを作成すると、データベース テーブル内のデータの各行の一意性が保証されます。
2. データクエリの速度を大幅に高速化できます。 (インデックスを作成する主な理由)
3. データの参照整合性を実現するという点で、テーブルとテーブル間の接続を高速化できます。
4. データ クエリにグループ化句と並べ替え句を使用すると、クエリ内のグループ化と並べ替えの時間も大幅に短縮できます。
インデックスを追加するデメリット:
1. インデックスの作成と維持には時間がかかり、データ量が増えると費やす時間も増加します。
2. インデックスはディスク領域を占有します。データ テーブルが占有するデータ領域に加えて、各インデックスは一定量の物理領域も占有します。インデックスの数が多い場合、インデックス ファイルが最大ファイルに達する可能性があります。データファイルよりも速いサイズになります。
3. テーブル内のデータを追加、削除、変更する場合、インデックスも動的に維持する必要があるため、データの維持速度が低下します。
1. オーディナリーインデックスとユニークインデックス(unique)
2. 単一列インデックスと結合インデックス
3. フルテキスト インデックス (フルテキスト)
4. 空間インデックス (spatial)
無理なインデックス設計やインデックスの不足は、データベースやアプリケーションのパフォーマンスに障害を引き起こす可能性があります。良好なパフォーマンスを得るには、効率的なインデックスが非常に重要です。インデックスを設計するときは、次のガイドラインを考慮する必要があります:
2. インデックスの作成
構文形式:
create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc]
①通常のインデックスを作成します
通常のインデックスは、一意性などの制限がなく、最も基本的なインデックスの種類です。その機能はデータへのアクセスを高速化することだけです。
[例1] bookテーブルのyear_publicationフィールドに共通インデックスを作成する SQL文は以下のとおりです:
mysql> create table book -> ( -> bookid int not null, -> bookname varchar(255) not null, -> authors varchar(255) not null, -> info varchar(255) null, -> comment varchar(255) null, -> year_publication year not null, -> index(year_publication) -> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: book partitions: NULL type: ref possible_keys: year_publication key: year_publication key_len: 1 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
explan文の出力結果の各行の説明
ご覧のとおり、 possible_key と key の値は year_publication であり、インデックスがクエリで使用されます。
②一意のインデックスを作成する
一意のインデックスを作成する主な理由は、特に比較的大きなデータ テーブルの場合、インデックス列のクエリ操作の実行時間を短縮することです。これは前の通常のインデックスと似ていますが、インデックス列の値が一意である必要がある点と、NULL 値が許可される点が異なります。複合インデックスの場合、列値の組み合わせは一意である必要があります。
[例 2] テーブル t1 を作成し、unique キーワードを使用してテーブル内の id フィールドに一意のインデックスを作成します。
mysql> create table t1 -> ( -> id int not null -> ,name char(30) not null, -> unique index uniqidx(id) -> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 \G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
③単一列インデックスの作成
単一列インデックスは、データ テーブルの特定のフィールドに対して作成されるインデックスであり、テーブル内に複数の単一列インデックスを作成できます。
[例 3] テーブル t2 を作成し、テーブル内の名前フィールドに単一列のインデックスを作成します。
mysql> create table t2 -> ( -> id int not null, -> name char(50) null, -> index singleidx(name(20)) -> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 \G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` char(50) DEFAULT NULL, KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)
結果からわかるように、SingleIdx という名前の単一列インデックスが、インデックス長 20 で id フィールドに正常に確立されました。
④複合インデックスの作成
複合インデックスとは、複数のフィールドに対してインデックスを作成することです。
[例 4] テーブル t3 を作成し、テーブル内の id、name、age フィールドに複合インデックスを作成します。SQL ステートメントは次のとおりです:
mysql> create table t3 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> index mulitiidx(id,name,age) -> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 \G*************************** 1. row *************************** Table: t3Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: mulitiidx key: mulitiidx key_len: 124 ref: const,const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.06 sec)
複合インデックスの役割は次のとおりです。ただし、これを使用する場合、インデックスを使用してフィールドをクエリすることはできませんが、「左端のプレフィックス」に従う必要があります。つまり、インデックスの左端の列セットを使用して行を照合し、そのような列セットは呼ばれます。一番左のプレフィックス。
たとえば、次のインデックスは、id、name、age の 3 つのフィールドで構成されています。インデックス行は、id/nam/age の順に並べ替えられます。インデックスはフィールドの組み合わせを検索できます: (id 、名前、年齢)、( id、名前) または id。カラムがインデックスの左端のプレフィックスを構成していない場合、MySQL はローカル インデックスを使用できません。たとえば、(age) または (name, age) の組み合わせではインデックス クエリを使用できません。 id フィールドと name フィールドをクエリする場合、multiidx インデックスが使用されます。(name, age) の組み合わせがクエリされる場合、または name フィールドと age フィールドが個別にクエリされる場合、インデックスは null になります。
⑤全文インデックスの作成
全文検索には全文インデックスを使用できます。 MyISAM ストレージ エンジンのみがフルテキスト インデックスをサポートし、char、varchar、および text 列のインデックスのみを作成します。インデックス付けは常に列全体に対して実行され、ローカル (プレフィックス) インデックスはサポートされません。
[例 5] テーブル t4 を作成し、テーブル内の情報フィールドにフルテキスト インデックスを作成します。SQL ステートメントは次のとおりです:
mysql> create table t4 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> fulltext index fulltxtidx(info) -> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 \G*************************** 1. row *************************** Table: t4Create Table: CREATE TABLE `t4` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
フルテキスト インデックスは、次のような場合に最適です。大きなデータ セットですが、小さなデータ セットの場合、その有用性は比較的小さくなります。
6. 空間インデックスの作成
空間インデックスは MyISAM タイプのテーブルに作成する必要があり、空間タイプ フィールドは null 以外である必要があります。
[例 6] テーブル t5 を作成し、空間タイプ ジオメトリを使用してフィールドに空間インデックスを作成します。SQL ステートメントは次のとおりです:
mysql> create table t5 -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 \G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `g` geometry NOT NULL, SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec)
名前がテーブル t5 の g フィールドは spatIdx の空間インデックスです。作成時に空間タイプのフィールド値の非 null 制約を指定し、テーブルのストレージ エンジンは MyISAM です。
既存のテーブルにインデックスを作成するには、alter table ステートメントまたは createindex ステートメントを使用します。
1. alter table ステートメントを使用してインデックスを作成します
基本構文:
alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec]
[例 7] bookname フィールドに名前を作成しますbook テーブル内の BkNameIdx の通常のインデックスです。
インデックスを追加する前に、showindex ステートメントを使用して、指定されたテーブルに作成されたインデックスを表示します。
mysql> show index from book \G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.10 sec)
其中，各个主要参数的含义为;
可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下：
mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL2 rows in set (0.05 sec)
可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。
【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下：
mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...*************************** 3. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL3 rows in set (0.01 sec)
可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。
【例9】在book表的comment字段上建立单列索引，SQL语句如下：
mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...*************************** 4. row *************************** Table: book Non_unique: 1 Key_name: bkcmtidx Seq_in_index: 1 Column_name: comment Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL4 rows in set (0.01 sec)
可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。
【例10】在book表的authors和info字段上建立组合索引，SQL语句如下：
mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...4...*************************** 5. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 1 Column_name: authors Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 6. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 2 Column_name: info Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL6 rows in set (0.06 sec)
可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。
【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下：
mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from t6 \G*************************** 1. row *************************** Table: t6 Non_unique: 1 Key_name: infofiidx Seq_in_index: 1 Column_name: info Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.05 sec)
可以看到，t6表中已经创建了名称为infoftidx的索引，该索引在info字段上创建，类型为fulltext，允许空值。
【例12】创建表t7，t7的空间类型字段g上创建名称为spatidx的空间索引，SQL语句如下：
mysql> create table t7(g geometry not null)ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t7 add spatial index spatidx(g);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show index from t7 \G*************************** 1. row *************************** Table: t7 Non_unique: 1 Key_name: spatidx Seq_in_index: 1 Column_name: g Collation: A Cardinality: NULL Sub_part: 32 Packed: NULL Null: Index_type: SPATIAL Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.01 sec)
可以看到，t7表的g字段上创建了名为spatidx的空间索引。
2.使用create index 创建索引
create index 语句可以在已经存在的表上添加索引，MySQL中create index被映射到一个alter table语句上，基本语法为：
create [unique|fulltext|spatial] index index_nameon table_name (col_name[length],...) [asc|desc]
可以看到create index语句和alter index语句的语法基本一样，只是关键字不同，使用相同的表book，假设该表中没有任何索引值，创建book表语句如下：
create table book(bookid int not null,bookname varchar(255) not null,authors varchar(255) not null,info varchar(255) null,comment varchar(255) null,year_publication year not null);
【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：
mysql> create index BkNameOdx on book(bookname);Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0
【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引，SQL语句如下：
mysql> create unique index UniqiiIdx on book(bookid);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
【例15】在book表的comment字段上建立单列索引，SQL语句如下：
mysql> create index BkcmtIdx on book(bookid);Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0
【例16】在book表的authors和info字段上建立组合索引，SQL语句如下：
mysql> create index BkAuAndInfoIdx on book (authors(20),info(50));Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0
【例17】删除表t6，重新建立表t6，在t6表中使用create index 语句，在char类型的info字段上创建全文索引。
mysql> drop table t6;Query OK, 0 rows affected (0.02 sec)mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create fulltext index infoftidx on t6(info);Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0
【例18】删除表t7，重新创建表t7，在t7表中使用create index语句，在空间数据类型字段g上创建名称为spatIdx的空间索引。
mysql> drop table t7;Query OK, 0 rows affected (0.06 sec)mysql> create table t7 (g geometry not null )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create spatial index spatIdx on t7 (g);Query OK, 0 rows affected, 1 warning (0.07 sec)Records: 0 Duplicates: 0 Warnings: 1
MySQL中删除索引使用alter table或者drop index 语句，两者可实现相同的功能，drop index 语句在内部被映射到一个alter table语句中。
1.使用alter table删除索引
alter table 删除索引的基本语法格式:
alter table table_name drop index index_name
【例1】删除book表中的名称为UniqidIdx的唯一索引。
mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, UNIQUE KEY `UniqiIdx` (`bookid`), KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`), KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> alter table book drop index UniqiIdx;Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`), KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到，book表中已经没有名称为UniqidIdx的唯一索引，删除索引成功。
注意：添加auto_increment约束字段的唯一索引不能被删除。
2.使用drop index 语句删除索引
drop index语句删除索引的基本语法格式：
drop index inde _name on table_name
【例2】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：
mysql> drop index BkAuAndInfoIdx on book;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。
注意：删除表中的列时，如果要删除的列为索引的组成部分，则该部分也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。
相关免费学习推荐：mysql数据库(视频)
以上がMySQL インデックスに関する知識の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
