第五阶段 -- 数据库：day19_07/01/19【索引】

1. 索引的简介

1. 索引的概念：是数据库对象，实现数据库快速查询

2. 为什么使用索引：数据库快速查询，提高查询速度

3. 索引的分类
   a. 普通索引：
   最基本的索引，对字段数据的类型和值没有任何限制，数据类型可以任意,字段的值可以空也可以重复

   b. 主键索引：
   ​ 给主键字段添加的索引
   ​ 主键特点:非空且唯一

   c. 唯一索引：
   给唯一字段添加的索引
   唯一索引和主键索引的区别：
   唯一索引:只有唯一 可以有空值

   主键索引:非空且唯一

   d. 全文索引：
   ​ 适用于在一大串文本添加的索引，只可以给字符串数据类型添加
   字符串数据类型(char varchar text)

   e. 空间索引：
   给字段的数据类型只能是空间数据类型，且该字段的值必须是非空not null
   空间数据类型：geometry point linestring polygon

   f.复合索引：
   给多个字段添加的索引
   注意:如果添加了复合索引，查询条件中只有使用了第一个字段，该索引才会被触发

• 例如(id,name) 只有查询条件中使用了id字段，索引才会被使用
• 如果查询条件中只有name字段，则索引不会被触发

2. 创建索引

1. 自动创建索引：

如果在创建表时,给表添加了主键约束和唯一约束，MySQL数据库会自动为主键约束和唯一约束创建对应的主键索引和唯一索引

• 例如:创建表index_student,并为表添加主键和唯一约束

create table index_student(
	sno int(8) primary key auto_increment,
	sname varchar(20) unique,
	age int(2)
)

• 查询表中的索引
  语法:show index from 表名

  例如:查询表index_student中的索引

show index from index_student

2. 手动创建索引：

(1). 创建表时创建索引：

1. 创建普通索引：
   语法: create table 表名(
   ​ 字段名1 字段类型1,
   ​ 字段名2 字段类型2,
   ​ .....,
   ​ index|key [索引名][ 索引类型] (字段名[(长度)][asc | desc])
   ​ )

• 例如：创建表index_student2给sno添加索引，查看表中的索引，show index from 表名

-- 创建表index_student2给sno添加索引
create table index_student2(
    sno int(8),
    sname varchar(20),
    age int(2),
    index(sno)
)
-- 查看表中的索引
show index from index_student2

2. 唯一索引的创建：
   语法: create table 表名(
   ​ 字段名1 字段类型1,
   ​ 字段名2 字段类型2,
   ​ .....,
   ​ unique [index|key][索引名] [索引类型] (字段名[(长度)][asc | desc])
   ​ )

• 例如:创建表index_student3给sname添加唯一索引

create table index_student3(
	sno int(8),
    sname varchar(20),
    age int(2),
    unique index(sname)
)
-- 查看表中的索引
show index from index_student3

3. 主键索引的创建：
   语法: create table 表名(
   ​ 字段名1 字段类型1,
   ​ 字段名2 字段类型2,
   ​ .....,
   ​ primary key [index|key][索引名] [索引类型] (字段名[(长度)][asc | desc])
   ​ )

• 例如:创建表index_student4给sno添加主键索引

create table index_student4(
	sno int(8),
    sname varchar(20),
    sex varchar(1),
    primary key(sno)
)
-- 查看表index_student4中的索引
show index from index_student4;

4. 全文索引的创建：
   注意：只能给字符串数据类型添加
   语法: create table 表名(
   ​ 字段名1 字段类型1,
   ​ 字段名2 字段类型2,
   ​ .....,
   ​ fulltext [index|key][索引名] [索引类型] (字段名[(长度)][asc | desc])
   ​ )

• 例如：创建表index_student5，给sinfo添加全文索引

create table index_student5(
	sno int(8),
    sname varchar(20),
    sinfo varchar(100),
    fulltext index(sinfo)
)
-- 查看表index_student5中的索引
show index from index_student5

5. 空间索引的创建：
   注意：只能给空间数据类型添加，且该字段的值不能为空 not null

​ 语法: create table 表名(
​ 字段名1 字段类型1,
​ 字段名2 字段类型2,
​ .....,
​ spatial [index|key][索引名] [索引类型] (字段名[(长度)][asc | desc])
​ )

• 例如：创建表index_student6，给sloc 字段的类型是point添加空间索引

create table index_student6(
	sno int(8),
    sname varchar(20),
    age int(2),
    sloc point not null,
    spatial index(sloc)
)
-- 查看索引
show index from index_student6

6. 复合索引的创建：
   ​ 语法: create table 表名(
   ​ 字段名1 字段类型1,
   ​ 字段名2 字段类型2,
   ​ .....,
   ​ index|key [索引名][ 索引类型] (字段名1[(长度)][asc | desc],字段名2[(长度)][asc | desc]...)
   ​ )

• 例如:创建表index_student7,给sno和sname添加复合索引

create table index_student7(
	sno int(8),
    sname varchar(20),
    age int(2),
    index(sno,sname)
);
-- 查看表中的索引
show index from index_student7

(2). 创建表后使用"create index" 创建索引
​ 语法:create [unique|fulltext|spatial] index 索引名称 [索引的类型]
​ on 表名(字段名1[(长度)][asc|desc],字段名2[(长度)][asc|desc]....)

注意：使用create index这种创建索引的方式不能创建主键索引

1. 创建普通索引

• 例如：创建表index_student8，给表中sno添加普通索

create table index_student8(
	sno int(8),
    sname varchar(20),
    age int(2)
)
-- 给表中sno添加普通索
create index index_student8_sno on index_student8(sno)
-- 查看表中的索引
show index from index_student8;

2. 创建唯一索引：

• 例如： 给表index_student8中sname添加唯一索引

create unique index index_student8_sname on index_student8(sname)
-- 查看表中的索引
show index from index_student8

3. 创建全文索引 fulltext：

• 例如：创建表index_student9,其中给sinfo添加全文索引

create table index_student9(
	sno int(8),
    sname varchar(20),
    sinfo varchar(100)
)
create fulltext index_student9_sinfo on index_student9(sinfo)
-- 查看标的索引
show index from index_student9

4. 创建空间索引spatial：

• 例如创建表index_student10,其中给sloc添加空间索

create table index_student10(
	sno int(8),
    sname varchar(2),
    age int(2),
    sloc point not null
);
-- 添加索引
create spatial index index_student10_sloc on index_student10(sloc);
-- 查看索引
show index from index_student10;

5. 创建复合索引：

• 例如创建表index_student11,其中给sno和sname添加复合索引

create table index_student11(
	sno int(8),
    sname varchar(20),
    age int(2)
);
-- 添加符合索引
create index index_student11_sno_sname on index_student11(sno,sname);
-- 查看索引
show index from index_student11;

(3). 给已有表添加索引 “alter table”

1. 创建普通索引：
   ​ 语法: alter table 表名
   ​ add index|key [索引名][索引类型] (字段名[长度][asc|desc])

• 例如:创建表index_student12,其中给sno添加普通索引

create table index_student12(
	sno int(8),
    sname varchar(20),
    age int(2)
)
-- 给sno添加普通索引，使用alter table
alter table index_student12
	add index(sno)
-- 查看索引
show index from index_student12;

2. 创建唯一索引：
   ​ 语法: alter table 表名
   ​ add unique [index|key][索引名] [索引类型] (字段名[长度][asc|desc])

• 例如：创建表index_student13,其中给sname添加唯一索

create table index_student13(
	sno int(8),
    sname varchar(20),
    age int(2)
)
-- 给snamet添加唯一的索引
alter table index_student13
add unique index(sname)
-- 查看表中的索引
show index from index_student13

3. 创建主键索引：
   语法: alter table 表名
   ​ add primary key [index|key][索引名] [索引类型] (字段名[长度][asc|desc])

• 例如：创建表index_student14，其中给sno添加主键索引

create table index_student14(
	sno int(8),
    sname varchar(20)
)
-- 给sno添加主键索引
alter table index_student14
add primary key(sno)
-- 查看表中的索引
show index from index_student14

4. 创建全文索引：
   语法:alter table 表名
   ​ add fulltext [index|key][索引名] [索引类型] (字段名[长度][asc|desc])

• 例如：创建表index_student15，其中给sinfo添加全文索引

create table index _studentq15(
	sno int(8),
    sname varchar(20),
    sinfo varchar(100)
)
-- 给sinfo添加全文索引
alter table index_student15
add fulltext(sinfo)
-- 查看表中的索引
show index from index_student15

5. 创建空间索引：
   ​ 语法: alter table 表名
   ​ add spatial [index|key][索引名] [索引类型] (字段名[长度][asc|desc])

• 例如：创建表index_student16，其中给sloc添加空间索引

create table index_student16(
	sno int(8),
    sname varchar(20),
    sloc point not null
)
-- 给sloc添加空间索引
alter table index_student16
add spatial(sloc)
-- 查看表中的索引
show index from index_student16

6. 创建复合索引：
   语法: alter table 表名 ​ add index|key [索引名][索引类型] (字段名1[长度][asc|desc],字段名2[长度][asc|desc]...

• 例如创建表index_student17,其中给sno和sname添加复合索引

create table index_student17(
	sno int(8),
    sname varchar(20),
    age int(2)
)
-- 给sno和sname添加复合索引
alter table index_student17
add index(sno,sname)
-- 查看表中索引
show index from index_student17

3. 删除索引

1. 使用alter table删除：
   语法:alter table 表名 drop index|key 索引名称

• 例如:例如删除表index_student17中的索引

alter table index_student17 drop index sno

2. 使用drop index删除：
   语法:drop index 索引名称 on 表名

• 例如:删除表index_student16中的索引

show index from index_student16;
--- 删除表index_student16中的索引
drop index sloc on index_student16

注意：使用alter table方式删除索引，不能删除主键索引，删除主键索引可以使用：

1. alter table 表名 drop primary key
2. 使用drop index进行删除

-- alter table 表名 drop primary key
alter table index_student14 drop primary key;

4. 使用图形化界面操作索引