【MySQL】索引的创建、查看和删除

本文详细介绍了如何在SQL中创建索引,包括创建普通索引、唯一索引、主键索引、单列索引、联合索引、全文索引和空间索引。此外,还涵盖了如何在已有的表上添加索引,查看现有索引,以及删除索引的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、创建索引

1、在创建表的同时创建索引

使用create table创建表时,除了可以定义列的数据类型外,还可以定义主键约束 外键约束 或者唯一性约束,而不论创建那种约束,在定义约束的同时相当于在指定列上创建了一个索引。

① 创建普通索引

CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX idx_bname(book_name)
);

② 创建唯一性索引

声明有唯一索引的字段,在添加数据时,要保证唯一性,但是可以添加null
唯一性索引的关键字:unique

CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
);

③ 创建主键索引

设定为主键后数据库会自动建立索引,innodb为聚簇索引,语法:
关键字:PRIMARY KEY

#通过定义主键约束的方式定义主键索引
CREATE TABLE book2(
book_id INT PRIMARY KEY ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

注意:删除主键索引:通过删除主键约束的方式来删除主键索引

ALTER TABLE book2
DROP PRIMARY KEY;

修改主键索引:必须先删除掉(drop)原索引,再新建(add)索引

④创建单列索引

CREATE TABLE book3(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX idx_bname(book_name)
);

⑤ 创建联合索引

CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,info)
);

⑥ 创建全文索引

fulltext全文索引可以用于全文搜素,并且只为char varchar 和text列创建索引。
索引总是对整个列进行,不支持局部(前缀)索引。

CREATE TABLE test4(
id INT NOT NULL,
NAME CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info(50))
# info(50)属于局部(前缀)索引,当前不支持,只会对当前列进行全部搜索
)

全文索引可以创建多列

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  title VARCHAR (200),
  body TEXT, 
  FULLTEXT index (title, body) 
) ENGINE = INNODB ;

tips:用于模糊查询

like方式的查询:

SELECT * FROM papers WHERE content LIKE%查询字符串%;

全文索引用match+against方式查询:

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

注意点
1. 使用全文索引前,搞清楚版本支持情况;
2. 全文索引比 like + % 快 N 倍,但是可能存在精度问题;
3. 如果需要全文索引的是大量数据,建议先添加数据,再创建索引。

⑦ 创建空间索引

空间索引创建中,要求空间类型的字段必须为 非空 。
举例:创建表test5,在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下:

CREATE TABLE test5( 
  geo GEOMETRY NOT NULL, 
  SPATIAL INDEX spa_idx_geo(geo) 
) ENGINE=MyISAM;

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

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

① 使用alter table语句创建索引

基本语法:alter table …add…

#创建数据表
CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

# 创建索引 alter table ...add...
# 普通索引
ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);
# 唯一性索引
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);
# 联合索引
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

② 使用create index创建索引

基本语法:create index…on…

CREATE INDEX语句可以在已经存在的表上添加索引,在MySQL中, CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法结构为:

# 创建数据表
CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

# 创建索引 create index...on....
# 普通索引
CREATE INDEX idx_cmt ON book6(COMMENT);
# 唯一性索引
CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);
# 联合索引
CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

二、查看索引

通过命令查看索引
方式一:

SHOW CREATE TABLE book;

方式二:

SHOW INDEX FROM book;

三、删除索引

1、使用ALTER TABLE删除索引

ALTER TABLE删除索引的基本语法格式如下: ALTER TABLE … DROP INDEX …

ALTER TABLE book5 
DROP INDEX idx_cmt;

提示:添加Auto_increment(自增长)约束字段的唯一索引不能被删除

2、使用DROP INDEX语句删除索引

DROP INDEX删除索引的基本语法格式如下: DROP INDEX … ON …

DROP INDEX uk_idx_bname ON book5;

tips:删除联合索引中的相关字段(删除表中字段)

删除联合索引中的相关字段

ALTER TABLE book5
DROP COLUMN book_name;

提示:删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

### MySQL中添加索引SQL语句 在MySQL中,可以通过多种方式为表添加索引。以下是一些常见的添加索引SQL语句: #### 1. 添加主键索引(PRIMARY KEY) 主键索引用于唯一标识表中的每一行数据。可以使用以下语句为表添加主键索引: ```sql ALTER TABLE `table_name` ADD PRIMARY KEY (`column1`, `column2`); ``` 此语句将`column1``column2`作为联合主键[^1]。 #### 2. 添加唯一索引(UNIQUE) 唯一索引确保索引列中的所有值都是唯一的。可以使用以下语句为表添加唯一索引: ```sql ALTER TABLE `table_name` ADD UNIQUE index_name (`column1`, `column2`); ``` 此语句将为`column1``column2`创建一个名为`index_name`的唯一索引[^1]。 #### 3. 添加普通索引(INDEX) 普通索引是最基本的索引类型,它没有任何限制。可以使用以下语句为表添加普通索引: ```sql ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`); ``` 此语句将为`column1``column2`创建一个名为`index_name`的普通索引。 #### 4. 添加全文索引(FULLTEXT) 全文索引主要用于文本搜索,支持复杂的查询操作,例如模糊匹配。可以使用以下语句为表添加全文索引: ```sql ALTER TABLE `table_name` ADD FULLTEXT (`column1`, `column2`); ``` 需要注意的是,只有`CHAR`、`VARCHAR``TEXT`类型的列可以创建全文索引。此外,全文索引通常与`MATCH ... AGAINST`语法配合使用,而不是普通的`WHERE`语句[^2]。 #### 5. 使用`CREATE INDEX`语句添加索引 除了`ALTER TABLE`语句外,还可以使用`CREATE INDEX`语句为表添加索引: ```sql CREATE INDEX index_name ON table_name (column1, column2); ``` 此语句的效果与`ALTER TABLE ... ADD INDEX`类似,但语法略有不同。 ### 注意事项 - 在大数据量的情况下,建议先插入数据到无索引的表中,然后再创建索引,以提高性能[^2]。 - 删除索引时,可以使用以下语句: ```sql ALTER TABLE `table_name` DROP INDEX index_name; ``` 如果是主键索引,则可以直接使用: ```sql ALTER TABLE `table_name` DROP PRIMARY KEY; ``` 主键索引不需要指定索引名,因为每个表只能有一个主键[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

张_Laura

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值