MySQL数据库 之 索引_mysql 空间索引

欢迎大家扫码关注我的微信公众号：

索引用于快速找出在某个列中有一特定值的行。如果不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出目标行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL 能快速到达某个位置去搜寻数据文件，而不必查看所有数据。

一、关于索引

索引是对数据表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

☆ 索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有 MySQL 列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

所有存储引擎支持每个表至少 16 个索引，总索引长度至少 256 字节。

MySQL 中索引的存储类型有两种：BTREE 和 HASH，具体和表的存储引擎相关：MyISAM 和 InnoDB存储引擎只支持 BTREE索引，而 MEMORY/HEAP可以两个都支持。

索引的 优点 主要有以下几点：

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的查询速度，这也是创建索引的最主要原因。
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
4. 使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的 缺点 主要有一下几点：

1. 创建和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2. 索引需要占磁盘空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果有大量索引，索引文件可能比数据文件更快达到最大文件尺寸。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

☆ 索引的分类

MySQL 的索引可以分为以下几类：

1. 普通索引和唯一索引

   ① 普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。

   ② 唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

2. 单列索引和组合索引

   ① 单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

   ② 组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

3. 全文索引

   全文索引类型为 fulltext，在定义索引的列上支持值的全文查找，允许在这些列上插入重复值和空值。全文索引可在 char、varchar或者 text 类型的列上创建。

   MySQL 中只有 MyISAM 存储引擎支持全文索引。

4. 空间索引

   空间索引是对空间数据类型的字段建立的索引，MySQL 中的空间数据类型有 4 种：geometry、point、linestring、polygon。MySQL 使用 spatial 关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为 not null。

   空间索引只能在存储引擎为 MyISAM 的表中创建。

☆ 索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，设计索引时应考虑以下准则：

○ 索引并非越多越好

由于当表中的数据更改的同时，索引也会进行调整和更新，所以索引并非越多越好。

○ 避免对经常更新的表进行过多的索引

避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对于经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

○ 数据量小的表最好不要使用索引

有可能造成相反的结果。

○ 在不同值较多的列上建立索引，不同值很少的列上不要建索引

如在 person 表中的 gender 字段中只有两个值 male 和 female，如果建立索引反而会严重降低数据更新速度。

○ 当唯一性是某种数据本身的特征时，指定唯一索引

使用唯一索引须能确保定义的列的数据完整性，以提高查询速度。

○ 在频繁进行排序或分组的列上建立索引

在频繁进行 group by 或 order by 操作的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

二、创建索引

☆ 创建表的时候创建索引

使用 create table 创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或唯一性约束，而无论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

create table tb_name [col_name data_type] [unique | fulltext | spatial] [index | key] [index_name] (col_name [length]) [asc | desc]
1

【参数说明】

unique、fulltext、spatial 为可选参数，分别表示唯一索引、全文索引、空间索引；

index 与 key 为同义词，两者作用相同，用来指定创建索引；

col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

index_name 指定索引的名称，为可选参数，如果不指定，MySQL 默认 col_name 为索引值；

length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

asc 和 desc 指定升序或降序的索引值存储。

1. 创建普通索引

最基本的索引类型，没有唯一性之类的限制。其作用只是加快对数据的访问速度。

创建 book 数据表，并在 year_publication 字段上建立普通索引，然后使用 show create table 查看表结构：

>>> 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.03 sec)

>>> show create table book \G;
******** 1. row ********
       Table: book
Create 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=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

索引创建成功，year_publication 字段的索引名与字段名相同，是 MySQL 自动添加的。

我们使用 explain 语句查看索引是否正在使用：

>>> 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: Using index condition
1 row in set, 0 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

【参数说明】

possible_keys： MySQL 在搜索数据记录时可选用的各个索引；

key： 表示 MySQL 实际选用的索引；

2. 创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建 t1 表，将其 id 字段创建唯一索引：

>>> create table t1 (
    id int not null,
    name char(30) not null,
    unique index Uniqidx(id)
    );
Query OK, 0 rows affected (0.03 sec)

>>> show create table t1 \G;
******** 1. row ********
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3. 创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。

创建一个 t2 表，在 name 字段上创建单列索引：

>>> create table t2 (
    id int not null,
    name char(50) not null,
    index SingleIdx(name(20))
    );
Query OK, 0 rows affected (0.02 sec)

>>> show create table t2 \G;
******** 1. row ********
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) NOT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

创建表时在 name 上创建了单列索引 SingleIdx，其长度为 20。

4. 创建组合索引

组合索引是在多个字段上创建一个索引。

创建 t3 表，并在 id、name、age 字段上建立组合索引：

>>> create table t3 ( 
    id int not null, 
    name char(30) not null, 
    age int not null, 
    info varchar(255), 
    index MultiIdx(id, name, age) 
    );
Query OK, 0 rows affected (0.03 sec)

>>> show create table t3 \G;
******** 1. row ********
       Table: t3
Create 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 `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

可以看到三个字段上的组合索引已经成功建立。

组合索引可起几个索引的作用，但是遵从‘最左前缀’：利用索引中最左边的列集来匹配行，这样的列集成为最左前缀。

例如本例中的组合索引，索引中按 id/name/age 的顺序存放，索引可以搜索包含 id 的任意组合，如：(id, name, age) 或 (id, name) 或 (id, age) 或 (id)，但是如果不构成索引最左面的前缀，即搜索中没有 id 字段，MySQL 不能使用局部索引。

查询 t3 表中的 id 和 name 字段，使用 explain 查看索引使用情况：

>>> explain select * from t3 where id = 1 and name = 'Lily' \G;
******** 1. row ********
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 94
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 0 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

我们可以看到 possible_keys 和 key 处都显示了组合索引 MultiIdx，而我们搜索一下 name 和 age 字段，并用 explain 查看一下：

>>> explain select * from t3 where name = 'Lily' and age = 20 \G;
******** 1. row ********
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 0 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

我们可以看到，当我们不构成索引最左面的前缀的时候，也就是没有搜索 id 字段的时候，possible_keys 和 key 处均显示为 NULL，即没有使用索引。

5. 创建全文索引

fulltext （全文索引）可以用于全文搜索。只有 MyISAM 存储引擎支持 fulltext 索引，并且只为 char、varchar 和 text 列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。

创建 t4 表，在 info 字段上建立全文索引：

>>> create table t4 (
    id int not null,
    name char(30) not null,
    age int not null,
    info varchar(255),
    fulltext index FullTextIdx(info)
    ) engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)

>>> show create table t4 \G;
******** 1. row ********
       Table: t4
Create 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 `FullTextIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

由于只有 MyISAM 存储引擎支持 fulltext 全文索引，所以在创建表时需要制定存储引擎为 MyISAM。

全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。

6. 创建空间索引

空间索引必须在 MyISAM 存储引擎类型的表中创建，且空间类型的字段必须为非空。

创建 t5 表，在空间类型为 geometry 的字段上创建空间索引：

>>> create table t5 (
    g geometry not null,
    spatial index SpatialIdx(g)
    ) engine = MyISAM;
Query OK, 0 rows affected (0.00 sec)

>>> show create table t5 \G;
******** 1. row ********
       Table: t5
Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `SpatialIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

有两个注意点：

1. 必须指定存储引擎为 MyISAM；
2. 必须指定空间约束的字段值非空；

☆ 在已经存在的表上创建索引

可以使用 alter table add 或 create table 语句来为已经存在的表创建索引。

1. 使用 alter table 语句创建索引

alter table tb_name add [unique | fulltext | spatial] [index | key] [index_name] (col_name[length], ...) [asc | desc]
1

首先我们使用 show index from tb_name 的查询索引语句来查询 book 表当前存在的索引：

>>> 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: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

① 在 book 表中的 bookname 字段上建立名为 BooknameIdx 的普通索引：

>>> alter table book add index BooknameIdx(bookname(30));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

查看一下当前索引：

>>> 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: 
******** 2. row ********
        Table: book
   Non_unique: 1
     Key_name: BooknameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

可以看到，我们成功的在 bookname 字段上创建了长度为 30 的非唯一索引—— BooknameIdx。

② 在 book 表的 bookid 字段上监理名称为 UniqIdx 的唯一索引：

>>> alter table book add unique index UniqIdx (bookid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;
******** 1. row ********
        Table: book
   Non_unique: 0
     Key_name: UniqIdx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 

...

3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

可以看到 Non_unique 处的值为 0，表示此索引为唯一索引。

③ 在 book 表的 comment 字段上建立单列索引：

>>> alter table book add index BookcommentIdx (comment(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;
******** 1. row ********
...

******** 4. row ********
        Table: book
   Non_unique: 1
     Key_name: BookcommentIdx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Sub_part 处的值为 50，说明我们成功建立了一个长度为 50 的非唯一索引，名字叫 BookcommentIdx。所以，在查询时，我们只要检索前 50 个字符即可。

④ 在 book 表的 authors 和 info 字段上建立组合索引：

>>> alter table book add index AuAndInIdx(authors(30), info(50));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;

...

******** 5. row ********
        Table: book
   Non_unique: 1
     Key_name: AuAndInIdx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
******** 6. row ********
        Table: book
   Non_unique: 1
     Key_name: AuAndInIdx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

可以看到，AuAndInIdx 索引由两个字段组成，分别为 authors 和 info，其中 authors 字段长度为 30，在组合索引中的序号为 1，不允许有空值，而 info 字段长度为 50，在组合索引中的序号为 2，且允许有空值。

⑤ 创建 t6 表，并创建全文索引：

创建 t6 表，注意修改存储引擎为 MyISAM：

>>> create table t6 (
    id int not null,
    info char(255)
    ) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5

在 info 字段上创建全文索引：

>>> alter table t6 add fulltext index infoFTIdx(info);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from t6 \G;
******** 1. row ********
        Table: t6
   Non_unique: 1
     Key_name: infoFTIdx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

⑥ 创建 t7 表，并创建空间索引：

创建 t7 表，注意修改 存储引擎为 MyISAM：

>>> create table t7 (
    a geometry not null
    ) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

>>> alter table t7 add spatial index SpatIdx (a);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from t7 \G;
******** 1. row ********
        Table: t7
   Non_unique: 1
     Key_name: SpatIdx
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

2. 使用 create index 语句创建索引

create index 语句可以在已经存在的表上添加索引，MySQL 中的 create index 语句被映射到一个 alter table 语句上，语法如下：

create [unique | fulltext | spatial] index index_name on tb_name (col_name [length], ...) [asc | desc]
1

重新建立一张没有索引的 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
    );
Query OK, 0 rows affected (0.02 sec)

>>> show index from book \G;
Empty set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

① 在 bookname 字段上创建 BooknameIdx 的普通索引：

>>> create index BooknameIdx on book(bookname);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;
******** 1. row ********
        Table: book
   Non_unique: 1
     Key_name: BooknameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

② 在 bookid 字段上建立 UniqIdx 的唯一索引：

>>> create unique index UniqIdx on book (bookid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;
******** 1. row ********
        Table: book
   Non_unique: 0
     Key_name: UniqIdx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 

...

2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

③ 在 comment 字段上建立单列索引：

>>> create index BookcommentIdx on book(comment(50));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;

...

******** 3. row ********
        Table: book
   Non_unique: 1
     Key_name: BookcommentIdx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

④ 在 authors 和 info 字段上建立组合索引：

>>> create index AuAndInIdx on book (authors(20), info(50));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from book \G;
******** 4. row ********
        Table: book
   Non_unique: 1
     Key_name: AuAndInIdx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 20
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
******** 5. row ********
        Table: book
   Non_unique: 1
     Key_name: AuAndInIdx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

⑤ 重新创建 t6 表，并在 info 字段上建立全文索引：

>>> create table t6 ( 
    id int not null, 
    info char(255) 
    ) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

>>> create fulltext index infoFTIdx on t6(info);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show index from t6 \G;
******** 1. row ********
        Table: t6
   Non_unique: 1
     Key_name: infoFTIdx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

⑥ 重新创建 t7 表，并在 g 字段上建立空间索引：

>>> create table t7 (
    g geometry not null
    ) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

>>> create spatial index SpatIdx on t7(g);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> 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: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

三、删除索引

MySQL 中删除索引使用 alter table 或者 drop index 语句，两者可实现相同的功能。 drop index 语句在内部被映射到一个 alter table 语句中。

☆ 使用 alter table 删除索引

语法如下：

alter table tb_name drop index index_name
1

查询 book 表的所有索引：

>>> show create table book \G;
******** 1. row ********
       Table: book
Create 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 `UniqIdx` (`bookid`),
  KEY `BooknameIdx` (`bookname`),
  KEY `BookcommentIdx` (`comment`(50)),
  KEY `AuAndInIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

现在我们删除 UniqIdx 的唯一索引：

>>> alter table book drop index UniqIdx;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3

再次查看：

>>> show create table book \G;
******** 1. row ********
       Table: book
Create 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 `BooknameIdx` (`bookname`),
  KEY `BookcommentIdx` (`comment`(50)),
  KEY `AuAndInIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

名为 UniqIdx 的索引已经被我们成功删除。

【注意】
当唯一索引指定了 auto_increment 约束时，该唯一索引不能被删除。

☆ 使用drop index 删除索引

语法如下：

drop index index_name on tb_name
1

删除 book 表中的 BookcommentIdx：

>>> drop index BookcommentIdx on book;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

>>> show create table book \G;
******** 1. row ********
       Table: book
Create 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 `BooknameIdx` (`bookname`),
  KEY `AuAndInIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

【注意】
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除