MySQL 索引（简解）

---

一，索引简介

① 索引概念

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据 库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数 据结构上实现高级查找算法，这种数据结构就是索引。

② 使用索引的原因

优点

• 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
• 可以加快数据的检索速度。
• 可以加速表与表之间的连接。
• 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间。

缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占用物理空间，数据量越大，占用空间越大。
• 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护。

③ 索引的分类（逻辑）

1- 普通索引

基本的索引类型，值可以为空，没有唯一性的限制。

2- 唯一索引

索引列的所有值都只能出现一次，即必须唯一，值可以为空。

3- 主键索引

主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。

4- 全文索引

全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。

全文索引不支持中文需要借sphinx(coreseek)或迅搜技术处理中文。

5- 空间索引

空间索引是指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构 ，其中包含空间对象的概要信息，如对象的标识、外接矩形及指向空间对象实体的指针。

6- 复合索引

复合索引，用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引; 设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。

二，创建索引

① 自动创建索引

当在表中定义一个primary key 或者 unique 约束条件时，MySQL 数据库会自动创建一个对应的主键索引或者唯一索引。

【例1】创建带主键和唯一约束的表

create table student1(
    stu_id int(10) primary key,
	stu_name varchar(3) unique,
	stu_sex varchar(1)
);

【例2】查看student1表的索引

show index 
from student1;

② 手动创建索引

用户可以在创建表时创建索引，也可以为已经存在的表添加索引。

③ 在创建表时创建索引

在创建表时可以直接手动创建不同类型的索引，下面详细的讲解手动创建普通索引、唯一索引、主键索引、全文索引、空间索引和复合索引。

1，创建普通索引

【语法格式】

create table table_name(
   columu_name1 date_type,
   columu_name2 date_type,
   ...
   index | key [index_name] [index_type] 
   (column_name [(length)] [asc | desc])
);

【例3】在创建表时创建普通索引

create table student2(
   stu_id int(10),
   stu_name varchar(3),
   index(stu_id)
);

2，创建唯一索引

【语法格式】

create table table_name(
   columu_name1 date_type,
   columu_name2 date_type,
   ...
   unique [index | key] [index_name] [index_type] 
   (column_name [(length)] [asc | desc])
);

【例4】在创建表时创建唯一索引

create table student3(
   stu_id int(10);
   stu_name varchar(3),
   unique index(stu_id)
);

3，创建主键索引

【语法格式】

create table table_name(
   columu_name1 date_type,
   columu_name2 date_type,
   ...
   primary key [index | key] [index_name] [index_type] 
   (column_name [(length)] [asc | desc])
);

【例5】在创建表时创建主索引

create table student4(
   stu_id int(10);
   stu_name varchar(3);
   primary key using hash(stu_id)
);

4，创建全文索引

【语法格式】

create table table_name(
   columu_name1 date_type,
   columu_name2 date_type,
   ...
   fulltext [index | key] [index_name] [index_type] 
   (column_name [(length)] [asc | desc])
);

【例6】在创建表时创建全文索引

create table student5(
   stu_id int(10),
   stu_info varchar(100),
   fulltext index(stu_info)
);

5，创建空间索引

【语法格式】

create table table_name(
   columu_name1 date_type,
   columu_name2 date_type,
   ...
   spatial [index | key] [index_name] [index_type] 
   (column_name [(length)] [asc | desc])
);

【例7】在创建表时创建空间索引

create table student6(
   stu_id int(10),
   stu_loc point not null,
   spatial index(stu_loc)
);

6，创建复合索引

【语法格式】

create table table_name(
   columu_name1 date_type,
   columu_name2 date_type,
   ...
   index | key [index_name] [index_type] 
   (column_name [(length)] [asc | desc], 
   column_name2 [(length)] [asc | desc], ...)
);

【例8】在创建表时创建复合索引

create table student7(
   stu_id int(10),
   stu_name varchar(3),
   index(stu_id, stu_name)
);

④ 为已存在的表创建索引

➀ 使用create index 创建索引

【语法格式】

create [unique(|fulltext|spatial)] index index_name [index_type] on table_name 
(column_name1 [(length)] [asc|desc], column_name2 [(length)] [asc|desc], ...);

【例9】使用create index为已存在的表创建普通索引

create index index_id on student10(stu_id);

➁ 使用alter table 创建索引

【语法格式】

alter table table_name
add index | key [index_name] [index_type] (column_name1 [(length)] [asc | desc], column_name2 [(length)] [asc | desc], ...);

【例10】使用alter table为已存在的表创建主键索引

alter table student11
add primary key (stu_id);

三，删除索引

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

① 使用alter table删除语句索引

【语法格式】

alter table table_name drop index | key index_name;

【例11】

alter table student12 drop index index_loc; 

② 使用drop table删除语句索引

【语法格式】

drop index index_name on table_name;

【例12】

drop index index_id on student13;