数据库索引的基本操作（sql语句）

表索引的增删改查（sql语句）

概念：

• 索引由表中的一列或多列组合而成，目的是优化数据库的查询速度。
• 向索引表中插入数据时，数据库系统需要按照索引进行排序。有时，可以先将索引删除，然后向表插入数据，最后重新创建索引。
• Mysql索引包括普通索引、唯一性索引、全文索引和空间索引等。（唯一性索引：使用UNIQUE参数可以设置唯一索引，主键 设置一种特殊的唯一索引。）

分类：

• 全文索引：
• 适用于数据量大的字符串类型字段的查询；
• 使用FULLTEXT参数设置全文索引；
• 全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上；
• 默认情况，全文搜索不区分大小写，如果索引列使用二进制排序，那么全文搜索区分大小写。
• 空间索引：
• 只能建立在空间数据类型上；
• 使用SPATIAL参数设置空间索引；
• 只有MYISAM存储引擎支持空间索引，并且索引的字段不能为空值。

环境：centos7、mysql版本如下图：

一、建表时候创建索引
基本语法格式：
create table table_name(
属性名 数据类型[约束条件],
……
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [别名] (属性名1，…属性名n [(长度)] [ASC | DESC])
);
注：
INDEX | KEY：指定索引，两者选择一个即可。
别名：为索引命名。
属性名：索引对应的字段名称，必须被预先定义。
长度：索引的长度，字符串类型才能使用。
ASC/DESC：前者是升序排列，后者是降序排列。

• 创建单列普通索引

创建表telephone，以及创建长度为20的单列普通索引tel：
 create table telephone(
    ->   id int(11) primary key,
    ->   name varchar(50) not null,
    ->   tel varchar(50) not null,
    ->   index (tel(20))
    -> );
Query OK, 0 rows affected (0.03 sec)

1
2
3
4
5
6
7
8
9

• 创建多列普通索引

创建表information，以及为name，sex列创建索引info：
mysql> create table information(
    ->   id int(11)  primary key,
    ->   name varchar(50) not null,
    ->   sex varchar(5) not null,
    ->   index info(name,sex)
    -> );
Query OK, 0 rows affected (0.03 sec)

1
2
3
4
5
6
7
8
9

• 创建唯一性索引

创建表address的同时为列id以升序的方式创建唯一索引：
mysql> create table address(
    ->   id int(11)  primary key,
    ->   address varchar(200),
    ->   unique index address(id ASC)
    -> );
Query OK, 0 rows affected (0.02 sec)

1
2
3
4
5
6
7
8

• 创建全文索引

创建表cards时为表cards的info列创建全文索引cards_info：
mysql> create table cards(
    ->   id int(11) primary key,
    ->   name varchar(50),
    ->   info varchar(50),
    ->   fulltext index cards_info(info)
    -> );
Query OK, 0 rows affected (0.05 sec)

1
2
3
4
5
6
7
8
9

• 创建空间索引

创建表list的同时为列goods创建空间索引listinfo，并且指定该表的存储引擎为MYISAM类型：
mysql> create table list(
    ->   id int(11) primary key,
    ->   goods geometry not null,
    ->   spatial index listinfo(goods)
    -> )
    -> engine=MYISAM;
Query OK, 0 rows affected (0.06 sec)

1
2
3
4
5
6
7
8
9

二、在已有的表里进行索引操作

以下的实验例子都是基于表worklnfo，以下是表结构：

• 在已建立的表中创建索引
  语句格式：
  CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name ON table_name(属性 [(length)] [ ASC | DESC]);

为name字段创建长度为10的索引index_name：
mysql> create index index_name on worklnfo(name(10));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

1
2
3
4
5

• 修改表结构时添加索引
  语句格式：
  ALTER TABLE table_name ADD [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name (属性名 [(length)] [ ASC | DESC]);

在表workInfo里的type和address列上创建名为index_t的索引：
mysql> alter table worklnfo add index index_t(Type,Address);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

在extra字段上创建名为index_ext的全文索引。
mysql> alter table worklnfo add fulltext index index_ext(extra);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9

三、查看表索引
基本的语句格式
SHOW INDEX FROM <表名> [ FROM <数据库名>]

查看表workInfo里的所有索引：
mysql> show index from worklnfo;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| worklnfo |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| worklnfo |          0 | id         |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| worklnfo |          1 | index_name |            1 | Name        | A         |           0 |       10 | NULL   |      | BTREE      |         |               |
| worklnfo |          1 | index_t    |            1 | Type        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| worklnfo |          1 | index_t    |            2 | Address     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

四、删除索引
语句格式
DROP INDEX index_name ON table_name;

使用 DROP 语句删除workinfo表的惟一性索引id：
mysql> drop index id on worklnfo;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
1
2
3
4