Note/大学笔记/数据库导论/笔记/数据库笔记07——索引——2022.04.07.md

150 lines
3.0 KiB
Markdown
Raw Permalink Normal View History

2023-08-31 11:30:31 +08:00
# 索引
## 一、创建索引
### 1、创建表的同时创建索引
```mysql
create table 表名(
列名 数据类型,
列名 数据类型,
......
[unique/fulltext/spatial] + [index/key] (索引名) (列名[长度]) [ASC/DESC]
)
```
#### (1)普通索引
```mysql
create table book(
bookid int not null,
bookname varchar(255) not null,
author varchar(255) not null,
info varchar(255),
comment varchar(255),
year_publication year not null,
index (year_publication)
//index/key(列名)
);
```
```mysql
show create table book;
```
2023-08-31 12:43:36 +08:00
![image-20220407081946290](https://lsky.hhdxw.top/imghub/img/image-20220407081946290.png)
2023-08-31 11:30:31 +08:00
```mysql
explain select * from book where year_publication=1990;
//使用explain查看索引是否正在使用重点观察结果中的possible keys和key的值此处都为year_publication说明执行此查询语句时使用了索引
```
2023-08-31 12:43:36 +08:00
![image-20220407082155889](https://lsky.hhdxw.top/imghub/img/image-20220407082155889.png)
2023-08-31 11:30:31 +08:00
#### (2)唯一索引
```mysql
create table t1(
id int not null,
name char(30) not null,
unique index udid(id)
//unique index 索引名(列名)
//索引名可省略默认名可show create table 展示
);
```
```mysql
show create table t1;
```
2023-08-31 12:43:36 +08:00
![image-20220407082821945](https://lsky.hhdxw.top/imghub/img/image-20220407082821945.png)
2023-08-31 11:30:31 +08:00
#### (3)组合索引
```mysql
create table t3(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index (id,name,age)
);
```
```mysql
explain select * from t3 where id=1 and name='joy';
explain select * from t3 where name='joy';
//查询时,必须遵从最左索引前缀原则
```
2023-08-31 12:43:36 +08:00
![image-20220407084307684](https://lsky.hhdxw.top/imghub/img/image-20220407084307684.png)
2023-08-31 11:30:31 +08:00
#### (4)全文索引 只为字符charvarchartext索引
```mysql
create table t4(
id int not null,
name char(30),
age int not null,
info varchar(255),
fulltext index (info)
);
```
```mysql
show create table t4;
```
2023-08-31 12:43:36 +08:00
![image-20220407091100882](https://lsky.hhdxw.top/imghub/img/image-20220407091100882.png)
2023-08-31 11:30:31 +08:00
#### (5)空间索引ENGIN=MyISAM
```mysql
create table t5(
g geometry not null,
spatial index(g)
//只能对一些图形等使用
);
```
### 2、在已经存在的表上创建索引
#### (1)使用alter table
```mysql
alter table 表名 add index【索引名】(列名);
//索引名可以省略,默认为列名
alter table book add index(bookname);
//查询索引
show index from 表名;
```
2023-08-31 12:43:36 +08:00
![image-20220407091751882](https://lsky.hhdxw.top/imghub/img/image-20220407091751882.png)
2023-08-31 11:30:31 +08:00
#### (2)使用create index
```mysql
create index [索引名] on 表名(列名);
```
## 二、删除索引
### 1、使用alter table
```mysql
alter table 表名 drop index 索引名;
alter table book drop index id;
```
2023-08-31 12:43:36 +08:00
![image-20220407093356011](https://lsky.hhdxw.top/imghub/img/image-20220407093356011.png)
2023-08-31 11:30:31 +08:00
### 2、使用drop index
```mysql
drop index 索引名 on 表名;
drop index bookname on book;
```
2023-08-31 12:43:36 +08:00
![image-20220407093437374](https://lsky.hhdxw.top/imghub/img/image-20220407093437374.png)