212 - 218. MySQL索引的基本用法

1.索引的简介

1.1 索引的概念

是数据库对象，实现数据库快速查询

1.2 为什么使用索引

实现数据库快速查询，提高查询速度

1.3 索引的分类

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

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

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

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

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

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

2.创建索引

2.1 自动创建索引

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

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

CREATE TABLE index_student(
	sno int(8) PRIMARY KEY auto_increment,
	sname varchar(20) UNIQUE,
	age int(2)
);
SHOW INDEX FROM index_student;
1
2
3
4
5
6

2.2 手动创建索引

a.创建表时创建索引

1. 创建普通索引

语法：creat table 表名(字段名1 字段类型1，字段名2 字段类型2，…，
index | key [索引名] [索引类型] (字段名[(长度)] [ asc | desc ])

CREATE TABLE index_student2(
	sno int(8),
	sname VARCHAR(20),
	age int(2),
	index(sno)
);
SHOW INDEX FROM index_student2;

1
2
3
4
5
6
7
8

2. 唯一索引的创建

语法：creat table 表名(字段名1 字段类型1，字段名2 字段类型2，…，
unique [ index | key ] [索引名] [索引类型] (字段名[(长度)] [ asc | desc ])

CREATE TABLE index_student3(
	sno int(8),
	sname VARCHAR(20),
	age int(2),
	unique index(sname)
);
1
2
3
4
5
6

3. 主键索引的创建

语法：creat table 表名(字段名1 字段类型1，字段名2 字段类型2，…，
primary key [ index | key ] [索引名] [索引类型] (字段名[(长度)] [ asc | desc ])

CREATE TABLE index_student4(
	sno INT(8),
	sname VARCHAR(20),
	sex VARCHAR(1),
	PRIMARY KEY(sno)
);
SHOW INDEX FROM index_student4;
1
2
3
4
5
6
7

4. 全文索引的创建

注意：只能给字符串数据类型添加

语法：creat table 表名(字段名1 字段类型1，字段名2 字段类型2，…，
fulltext [ index | key ] [索引名] [索引类型] (字段名[(长度)] [ asc | desc ])

CREATE TABLE index_student5(
	sno INT(8),
	sname VARCHAR(20),
	sinfo VARCHAR(100),
	FULLTEXT index(sinfo)
);
SHOW INDEX FROM index_student5;
1
2
3
4
5
6
7

5. 空间索引的创建

注意：只能给空间数据类型添加 且该字段的值不能为空 not null

语法：creat table 表名(字段名1 字段类型1，字段名2 字段类型2，…，
spatial [ index | key ] [索引名] [索引类型] (字段名[(长度)] [ asc | desc ])

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;
1
2
3
4
5
6
7
8

6. 复合索引的创建

语法：creat table 表名(字段名1 字段类型1，字段名2 字段类型2，…，
index | key [索引名] [索引类型] ( 字段名1 [ (长度) ] [ asc | desc ] ，
( 字段名2 [ (长度) ] [ asc | desc ] ，…)

CREATE TABLE index_student7(
	sno int(8),
	sname VARCHAR(20),
	age int(2),
	INDEX(sno,sname)
);
SHOW INDEX FROM index_student7;
1
2
3
4
5
6
7

b.创建表后使用"create index"创建索引

create [ unique | fulltext | spatial ] index 索引名称 [ 索引的类型 ]
on 表名 ( 字段名1 [(长度)] [ asc | desc ] , 字段名2 [(长度)] [ asc | desc ] , … )

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

1. 创建普通索引

CREATE TABLE index_student8(
	sno int(8),
	sname VARCHAR(20),
	age int(2)
);
CREATE INDEX index_student8_sno on index_student8(sno);
SHOW INDEX FROM index_student8;
1
2
3
4
5
6
7

2.创建唯一索引

CREATE UNIQUE INDEX index_student8_sname on index_student8(sname);
1

3.创建全文索引

CREATE TABLE index_student9(
	sno int(8),
	sname VARCHAR(20),
	sinfo VARCHAR(100)
);
CREATE FULLTEXT INDEX index_student9_sinfo on index_student9(sinfo);
SHOW INDEX FROM index_student9;
1
2
3
4
5
6
7

4.创建空间索引

CREATE TABLE index_student10(
	sno INT(8),
	sname VARCHAR(20),
	age INT(2),
	sloc point NOT NULL
);
CREATE SPATIAL INDEX index_student10_sloc ON index_student10(sloc);
SHOW INDEX FROM index_student10;
1
2
3
4
5
6
7
8

5.创建复合索引

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;
1
2
3
4
5
6
7

c.给已有表添加索引"alter table"

1.创建普通索引

语法：alter table 表名
add index | key [ 索引名 ] [ 索引类型 ] ( 字段名[ 长度 ] [ asc | desc ])

CREATE TABLE index_student12(
	sno int(8),
	sname VARCHAR(20),
	age INT(2)
);
ALTER TABLE index_student12 ADD INDEX(sno);
SHOW INDEX FROM index_student12;
1
2
3
4
5
6
7

2.创建唯一索引

语法：alter table 表名
add unique [ index | key ] [ 索引名 ] [ 索引类型 ] ( 字段名[ 长度 ] [ asc | desc ])

CREATE TABLE index_student13(
	id int(8),
	sname VARCHAR(20),
	age INT(2)
);
ALTER TABLE index_student13 ADD UNIQUE INDEX(sname);
SHOW INDEX FROM index_student13;
1
2
3
4
5
6
7

3.创建主键索引

语法：alter table 表名
add primary key [ index | key ] [ 索引名 ] [ 索引类型 ] ( 字段名[ 长度 ] [ asc | desc ])

CREATE TABLE index_student14(
	sno int(8),
	sname VARCHAR(20)
);
ALTER TABLE index_student14 ADD PRIMARY KEY(sno);
SHOW INDEX FROM index_student14;
1
2
3
4
5
6

4.创建全文索引

语法：alter table 表名
add fulltext [ index | key ] [ 索引名 ] [ 索引类型 ] ( 字段名[ 长度 ] [ asc | desc ])

CREATE TABLE index_student15(
	sno int(8),
	sname VARCHAR(20),
	sinfo VARCHAR(100)
);
ALTER TABLE index_student15 ADD FULLTEXT(sinfo);
SHOW INDEX FROM index_student15;
1
2
3
4
5
6
7

5.创建空间索引

语法：alter table 表名
add spatial [ index | key ] [ 索引名 ] [ 索引类型 ] ( 字段名[ 长度 ] [ asc | desc ])

CREATE TABLE index_student16(
	sno int(8),
	sname VARCHAR(20),
	sloc point NOT NULL
);
ALTER TABLE index_student16 add SPATIAL(sloc);
SHOW INDEX FROM index_student16;
1
2
3
4
5
6
7

6.创建复合索引

语法：alter table 表名
add index | key [ 索引名 ] [ 索引类型 ] ( 字段名1[ 长度 ] [ asc | desc ]，字段名2[ 长度 ] [ asc | desc ]，…)

CREATE TABLE index_student17(
	sno INT(8),
	sname VARCHAR(20),
	age INT(2)
);
ALTER TABLE index_student17 ADD INDEX(sno,sname);
SHOW INDEX FROM index_student17;
1
2
3
4
5
6
7

3.删除索引

3.1 使用alter table 删除

语法：alter table 表名 drop index | key 索引名称

ALTER TABLE index_student17 DROP INDEX sno;
1

3.2 使用 drop index 删除

语法：drop index 索引名称 on 表名

DROP INDEX sloc ON index_student16;
1

3.3 注意

使用alter table 方式删除索引不能删除主键索引
不能删除主键索引 ALTER TABLE index_student14 DROP PRIMARY KEY;
删除主键索引：
1.ALTER TABLE index_student14 DROP PRIMARY KEY;
2.使用 DROP INDEX 进行删除