MySQL（四）索引的设计与使用

目录

一、索引概述

二、索引类型

三、索引设计原则

四、创建索引

1、在创建表时创建索引

（1）创建普通索引

（2）创建唯一索引

（3）创建单列索引

（4）创建多列索引

（5）创建全文索引

2、在已创建的表上创建索引

（1）创建普通索引

（2）创建唯一索引

（4）创建多列索引

（5）创建全文索引

五、删除索引

---

一、索引概述

索引是一个常用的数据结构，又称为键。索引主要用于提高查找存储在数据库中记录的速度。

如果将MySQL比喻成一本书，那么索引就是目录。目录并不是越多越好。

索引可以在创建表时创建，也可根据需求随时创建新的索引。

二、索引类型

1. 普通索引，没有任何限制，允许在所有数据类型上创建普通索引。
2. 唯一索引，必须使用关键字unique约束索引值唯一。
3. 单列索引，一个索引只包含一个字段，一个表表中可以有多个单列多音。
4. 多列索引，一个多列索引包含多个字段。
5. 全文索引，使用关键字fulltext约束的索引。只能建立在char，varchar，text类型的字段上。

三、索引设计原则

索引并非越多越好。

1. 在关键字上建立索引。
2. 使用唯一索引。
3. 在进行排序或者分组的字段上创建索引。
4. 使用短索引。
5. 不要对规模小的数据表建立索引。
6. 索引并非越多越好。
7. 对于InnoDB，由于该引擎会对主键进行排序，因此在使用时尽可能在主键上建立索引，使得主键有序。

四、创建索引

1、在创建表时创建索引

（1）创建普通索引

①创建索引

 create table emp(id varchar(10),name varchar(20), index index_id(id));

②、验证索引是否成功创建

show create table emp;

③验证索引是否有用

mysql> explain select * from emp where id='110'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: index_id
          key: index_id
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

（2）创建唯一索引

①创建索引

 create table emp(id varchar(10),name varchar(20), unique index index_id(id));
insert into emp values('110','tian');

②、验证索引是否成功创建

③验证索引是否有用

（3）创建单列索引

普通索引和唯一索引都是单列索引，即旨在一个字段上创建索引。

（4）创建多列索引

 create table emp(id varchar(10),name varchar(20), index index_mutli(id,name));

（5）创建全文索引

只能建立在char，varchar和text字段上。

 create table emp(id varchar(10),name varchar(20), fulltext index_full(id));

2、在已创建的表上创建索引

（1）创建普通索引

①创建索引

create index index_id on emp(id);

②、验证索引是否成功创建

show create table emp;

③验证索引是否有用，可以使用explain查看

mysql> explain select * from emp where id='110'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: index_id
          key: index_id
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

（2）创建唯一索引

①创建索引

create  unique index_uique on emp(id);

（3）创建单列索引

普通索引和唯一索引都是单列索引，即旨在一个字段上创建索引。

（4）创建多列索引

create index index_mutli on emp(id ,name);

（5）创建全文索引

create fulltext index index_full on emp(id);

五、删除索引

格式：drop index indexName on tableName;

drop index index_full on emp;