ホームページ > 記事 > データベース > MySQL インデックスに関する知識

MySQL インデックスに関する知識

coldplay.xixi転載: 2021-03-23 09:50:502289ブラウズ

MySQL インデックスに関する知識

1. インデックスの概要
(1)インデックスの意味と具体性
(2)インデックスの分類
(3)インデックスの設計原則

2. インデックスを作成します
(1) テーブル作成時にインデックスを作成します
(2) 既存のテーブルにインデックスを作成します
(3) インデックスを削除します

1. インデックスの概要

インデックスは、列内の特定の値を持つ行をすばやく検索するために使用されます。。インデックスを使用しない場合、MySQL は最初のレコードから始めて、関連する行が見つかるまでテーブル全体を読み取る必要があります。テーブルが大きくなるほど、データのクエリにかかる時間が長くなります。テーブル内のクエリされたカラムにインデックスがある場合、MySQL はすべてのデータを確認することなく、データファイルを検索する場所にすぐにアクセスできます。

(1)インデックスの意味と具体的な

意味: インデックスはディスクに保存される別個のデータベース構造であり、データテーブル内のすべてのレコードへの参照ポインターが含まれています。 1 つ以上の列で特定の値を持つ行をすばやく検索するために使用されます。

インデックスはストレージエンジンに実装されるため、各ストレージエンジンのインデックスは必ずしも完全に同じであるとは限りません。また、各ストレージエンジンがすべてのインデックスタイプをサポートするとは限りません。ストレージエンジンに応じて、各テーブルのインデックスの最大数と最大インデックス長を定義します。すべてのストレージエンジンは、テーブルごとに少なくとも 16 個のインデックスをサポートし、インデックスの合計長は少なくとも 256 バイトです。ほとんどのストレージエンジンには、より高い制限があります。

MySQL には、BTREE と HASH という 2 つのストレージタイプのインデックスがあり、これらは特にテーブルのストレージエンジン (MyISAM および InnoDB ストレージエンジンのみ) に関連しています。 BTREE インデックスのサポート; MEMORY/HEAL ストレージエンジンは、HASH インデックスと BTREE インデックスをサポートできます。

インデックスの利点:
1. 一意のインデックスを作成すると、データベーステーブル内のデータの各行の一意性が保証されます。
2. データクエリの速度を大幅に高速化できます。 (インデックスを作成する主な理由)
3. データの参照整合性を実現するという点で、テーブルとテーブル間の接続を高速化できます。
4. データクエリにグループ化句と並べ替え句を使用すると、クエリ内のグループ化と並べ替えの時間も大幅に短縮できます。

インデックスを追加するデメリット:
1. インデックスの作成と維持には時間がかかり、データ量が増えると費やす時間も増加します。
2. インデックスはディスク領域を占有します。データテーブルが占有するデータ領域に加えて、各インデックスは一定量の物理領域も占有します。インデックスの数が多い場合、インデックスファイルが最大ファイルに達する可能性があります。データファイルよりも速いサイズになります。
3. テーブル内のデータを追加、削除、変更する場合、インデックスも動的に維持する必要があるため、データの維持速度が低下します。

(2) インデックスの分類

1. オーディナリーインデックスとユニークインデックス(unique)

オーディナリーインデックスはMySQLの基本的なインデックスタイプで定義が可能です。重複した値と null 値をインデックス付き列に挿入します。
一意のインデックス。インデックス列の値は一意である必要がありますが、NULL 値も許可されます。複合インデックスの場合、列値の組み合わせは一意である必要があります。
主キーインデックスは、NULL 値を許可しない特別な一意のインデックスです。

2. 単一列インデックスと結合インデックス

単一列とは、インデックスに 1 つの列のみが含まれることを意味し、テーブルには複数の単一列インデックスを含めることができます。
結合インデックスとは、テーブル内の複数のフィールドを組み合わせて作成されるインデックスのことで、これらのフィールドの左側のフィールドがクエリ条件で使用される場合にのみ使用されます。
複合インデックスを使用する場合は、左端のプレフィックスセットに従います。

3. フルテキストインデックス (フルテキスト)

フルテキストインデックスのタイプは FULLTEXT で、次の列の値の全文検索をサポートします。インデックスが定義されており、これらのインデックス列への挿入が可能です。重複値と null 値、フルテキストインデックスは、char、varchar、または text 型の列に作成できます。 MySQL の MyISAM ストレージエンジンのみがフルテキストインデックス作成をサポートします。

4. 空間インデックス (spatial)

空間インデックスは、空間データ型のフィールドに対して確立されるインデックスであり、MySQL には 4 種類の空間データ型があります。つまり、ジオメトリ、ポイント、ラインストリング、ポリゴンです。 MySQL は spatial キーワードで拡張され、通常のインデックスの作成に使用される構文と同様の構文で空間インデックスを作成できるようになりました。空間インデックスの作成に使用される列は、null ではないと宣言する必要があります。空間インデックスは、ストレージエンジンが MySQL であるテーブルでのみ作成できます。

(3) インデックス設計の原則

無理なインデックス設計やインデックスの不足は、データベースやアプリケーションのパフォーマンスに障害を引き起こす可能性があります。良好なパフォーマンスを得るには、効率的なインデックスが非常に重要です。インデックスを設計するときは、次のガイドラインを考慮する必要があります:

1. インデックスが多ければ多いほど良いというわけではありません。
2. 頻繁に更新されるテーブルにインデックスを作成しすぎないようにし、インデックスを作成する列の数をできるだけ少なくします。
3. データ量が少ないテーブルにはインデックスを使用しないことをお勧めします。
4. 条件式でよく使用される、さまざまな値が含まれる列にインデックスを作成します。異なる値がほとんどない列にはインデックスを作成しないでください。
5. 一意性がデータ自体の特性である場合は、一意のインデックスを指定します。
6. 頻繁に並べ替えまたはグループ化される列にインデックスを作成します (グループ化または並べ替え操作)。並べ替える列が複数ある場合は、これらの列に結合インデックスを作成できます。

2. インデックスの作成

構文形式:

create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc]

unique、fulltext、spatial はオプションのパラメーターで、それぞれ一意のインデックス、フルテキストインデックス、空間インデックスを表します。
インデックスとキーは同義語であり、同じ機能を持ち、インデックスの作成を指定するために使用されます。
col_name は、インデックスを作成する必要があるフィールド列です。この列は、データテーブルで定義されている複数の列から選択する必要があります。
index_name はインデックスの名前を指定します。これはオプションのパラメータです。指定しない場合、MySQL はデフォルトでインデックス値としてcol_name を使用します。
length はインデックスの長さを示すオプションのパラメータです。インデックスの長さを指定できるのは文字列タイプのフィールドのみです。
asc または desc は、インデックス値の格納を昇順または降順で指定します。

(1) テーブル作成時にインデックスを作成します

①通常のインデックスを作成します

通常のインデックスは、一意性などの制限がなく、最も基本的なインデックスの種類です。その機能はデータへのアクセスを高速化することだけです。

[例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文の出力結果の各行の説明

select_type 行は、使用される選択クエリのタイプを指定します。ここでの値は simple であり、ユニオンやサブクエリを使用しない単純な選択を意味します。他の可能な値には、プライマリ、ユニオン、サブクエリなどが含まれます。
テーブル行は、データベースによって読み取られるデータテーブルの名前を指定し、読み取られた順序で並べられます。
type 行は、このデータベーステーブルと他のデータベーステーブル間の関係を指定します。指定可能な値には、system、const、eq_ref、ref、range、index、および all が含まれます。
possible_keys 行: MySQL がデータレコードを検索するときに使用できるさまざまなインデックスを提供します。
キー行は、MySQL によって実際に選択されるインデックスです。
key_len 行はインデックスの長さをバイト単位で示します。key_len 値が小さいほど高速になります。
ref 行は、リレーションシップ内の別のデータテーブルのデータ列の名前を示します。
rows rows は、このクエリの実行時に MySQL がこのデータテーブルから読み取ると想定されるデータ行の数です。
余分な行は、関連付け操作に関する情報を提供します。

ご覧のとおり、 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 です。

(2) 既存のテーブルにインデックスを作成する

既存のテーブルにインデックスを作成するには、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)

其中，各个主要参数的含义为;

table表示创建索引的表。
Non_unique表示索引非唯一，1表示非唯一，0表示唯一。
Key_name表示索引的名称。
Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。
Column_name表示定义索引的列字段。
Sub_part表示索引的长度。
Null表示该字段是否能为空值。
Index_type表示索引类型。

可以看到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

(3)删除索引

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 サイトの他の関連記事を参照してください。

声明：

この記事はcsdn.netで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。

前の記事：MySQLデータベースにビューを作成する方法次の記事：MySQLデータベースにビューを作成する方法

続きを見る