MySQL中索引的创建及删除方法！

admin • 2024年9月30日 20:54 • SQL技巧

MySQL中索引的创建及删除方法！

MySQL中的索引是一种特殊的数据结构,它的主要目的是为了加快数据的检索速度,下面这篇文章主要给大家介绍了关于MySQL中索引的创建及删除的相关资料,文中通过代码介绍的非常详细,需要的朋友可以参考下。

1.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑按照上说，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成单列索引和联合索引。

注意：不同的存储引擎支持的索引类型也不一样

InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive：不支持 B-tree、Hash、Full-text 等索引;

1.2 创建索引

1、创建表的时候创建索引

隐式的索引创建：

# 1.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引)
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,# 创建主键索引
dept_name VARCHAR(20)
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,# 主键索引
emp_name VARCHAR(20) UNIQUE,# 唯一索引
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
); # 外键索引

显式的索引创建：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

UNIQUE、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引;
INDEX与KEY 为同义词，两者的作用相同，用来指定创建索引;
index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名;
col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择;
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度;
ASC 或 DESC 指定升序或者降序的索引值存储。
特例：主键索引使用主键约束的方式来创建。

1、创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下:

# ①创建普通的索引
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)
);

通过命令查看索引有没有创建成功

# 方式1：
SHOW CREATE TABLE book; # Linux下添加\G参数

# 方式2：
SHOW INDEX FROM book;

#性能分析工具：EXPLAIN，查看索引是否正在使用
EXPLAIN SELECT * from book where book_name = 'mysql高级';

EXPLAIN语句输出结果的各个行我们在下一章讲解，这里主要关注两个字段

possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引
key行时MySQL实际选用的索引

可以看到，possible_keys和key值都为idx_bname，查询时使用了索引

2、创建唯一索引

# ②创建唯一索引
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)
);

show INDEX from book1;# 查看索引

3、主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法:

随表一起建索引:

# ③主键索引
# 通过定义主键约束的方式定义主键索引
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）索引

4、创建单列索引

#④ 创建单列索引
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)
);
show index from book3;

5、创建组合索引

举例：创建表 book4，在表中的 book_id、book_name和 info字段上建立组合索引，SQL 语句如下：

# ⑤ 创建联合索引
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)    
)
SHOW INDEX FROM book4;

注意上面三行依次是book_id,book_name,info，与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则，先进行book_id条件的比较，然后再进行book_name比较，最后才是info。因此注意把最常用的查询字段放在索引的最左边。

# 分析
explain select * from book4 where book_id = 1001 and book_name = 'mysql'; # 会使用到mul_bid_bname_info索引
explain select * from book4 where book_name = 'mysql';# 不会使用到mul_bid_bname_info索引

6、创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。

举例1：创建表test4，在表中的 info 字段上建立全文索引，SQL 语句如下:

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)
) ENGINE=MyISAM;

在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了，因为在此版本中 InnoDB 支持全文索引。

语句执行完毕后，用SHOW CREATE TABLE查看表结构：

SHOW INDEX FROM test4\G;

由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。

举例2：

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

创建了一个给 title 和 body 字段添加全文索引的表。

举例3：

CREATE TABLE `papers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于 like 方式的的查询:

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

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

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

明显的提高查询效率

注意点

使用全文索引前，搞清楚版本支持情况；

全文索引比like + % 快 N倍，但是可能存在精度问题

如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

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

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

1. 使用 ALTER TABLE 语句创建索引

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

2. 使用 CREATE INDEX 创建索引

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

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

举例1：在book表的comment字段上建立名为的普通索引

create index idx_cmt on book(comment);

举例2：在book表中的book_id字段上建立名为uk_idx_bid的唯一索引，SQL语句如下：

CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);

举例3：在book表的book_id、book_name、info字段上建立联合索引，SQL语句如下：

CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);

1.3 删除索引

MySQL中删除索引使用ALTER TABLE或DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中

1. 使用 ALTER TABLE 删除索引：

ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

练习：删除book表中名称为idx_bk_id的唯一索引

首先查看book表中是否名称为idx_bk_id的索引，输入SHOW语句如下：

SHOW INDEX FROM book\G;

下面删除该索引，输入删除语句如下：

ALTER TABLE book DROP INDEX idx_bk_id;

提示

添加AUTO_INCREMENT约束字段的唯一索引不能被删除（）

2. 使用 DROP INDEX 语句删除索引：

DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

练习：删除book表中名称为idx_aut_info的组合索引，SQL语句如下：

DROP INDEX idx_aut_info ON book;

语句执行完毕，使用SHOW查看索引是否删除：

SHOW CREATE TABLE book\G;

可以看到，book表中已经没有名称为idx_aut_info的组合索引，删除索引成功。

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

总结

到此这篇关于MySQL中索引创建及删除的文章就介绍到这了。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！

更多技巧， www.excelbook.cn

欢迎加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END

二维码

Python人脸识别检测！

< <上一篇

小众软件，FnSync实现安卓手机消息同步到电脑神器，还支持剪贴板同步！

下一篇>>

搜索内容

MySQL中索引的创建及删除方法！

MySQL中索引的创建及删除方法！

1.1 索引的分类

1.2 创建索引

1、创建表的时候创建索引

1、创建普通索引

2、创建唯一索引

3、主键索引

4、创建单列索引

5、创建组合索引

6、创建全文索引

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

1. 使用 ALTER TABLE 语句创建索引

2. 使用 CREATE INDEX 创建索引

1.3 删除索引

1. 使用 ALTER TABLE 删除索引：

2. 使用 DROP INDEX 语句删除索引：

总结

取消回复

共有 0 条评论

MySQL中索引的创建及删除方法！

MySQL中索引的创建及删除方法！

1.1 索引的分类

1.2 创建索引

1、创建表的时候创建索引

1、创建普通索引

2、创建唯一索引

3、主键索引

4、创建单列索引

5、创建组合索引

6、 创建全文索引

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

1. 使用 ALTER TABLE 语句创建索引

2. 使用 CREATE INDEX 创建索引

1.3 删除索引

1. 使用 ALTER TABLE 删除索引：

2. 使用 DROP INDEX 语句删除索引：

总结

取消回复

共有 0 条评论

6、创建全文索引