【MySQL进阶之路 | 高级篇】索引的声明与使用

Vez'nan的幸福生活

已于 2024-06-26 10:50:32 修改

阅读量248

点赞数 11

文章标签： mongodb mysql hbase oracle hadoop storm zookeeper

于 2024-06-25 19:12:58 首次发布

本文链接：https://blog.csdn.net/2301_80912559/article/details/139967250

版权

1. 索引的分类

MySQL的索引包括普通索引，唯一性索引，全文索引，单列索引和空间索引.

从功能逻辑上说，索引主要分为普通索引，唯一索引，主键索引和全文索引.
按物理实现方式，索引可以分为聚簇索引和二级索引.
按作用字段个数进行划分，分为单列索引和联合索引.

(1). 普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询速度.这类索引可以创建在任何数据类型上，其值是否唯一或非空，要由字段本身的完整性约束条件决定.建立索引后，可以通过索引进行查询.

(2). 唯一索引

使用UNIQUE参数可以设置唯一索引，在建立唯一索引时，限制该索引的值必须是唯一的.但允许是空值.在一张表里可以有多个唯一索引.

(3). 主键索引

主键索引是一种特殊的唯一性索引.在唯一约束的基础上添加了非空的约束，一张表里只能有一个主键索引.这是由主键索引的物理实现方式决定的.因为数据存储在文件中只能按照一种顺序进行存储.

(4). 联合索引

联合索引是在表的多个字段上创建一个索引，该索引指向创建的多个字段.可以通过几个字段进行查询，但查询条件中使用这些字段的第一个字段时才会被使用.使用联合索引时遵循最左前缀原则.

(5). 全文索引

使用参数FULLTEXT可以设置索引为全文索引.在定义索引的列上支持值的全文查找，允许在这些索引列中插入空值和重复值.全文索引只能创建在CHAR, VARCHAR, TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度.

(6). 小结

InnoDB : 支持b+树，full-text等索引，不支持hash索引.
MyISAM : 支持b+树，full-text等索引，不支持hash索引.
Memory : 支持b+树，hash索引等，不支持b+树.

2. 创建索引

MySQL支持多种方法在单个或多个列上创建索引，在创建表的定义语句中CREATE TABLE中指定索引列，使用ALTER TABLE语句在已经创建的表中创建索引，或者使用CREATE INDEX语句在已经存在的表中添加索引.

(1). 创建表时添加索引

CREATE TABLE demo1(
demo1_id INT PRIMARY KEY AUTO_INCREMENT,
demo_fname VARCHAR(15) UNIQUE KEY,
demp_lname VARCHAR(20),
INDEX demo1_index_lname(demp_lname)
);

隐式的方式创建索引.在声明有主键约束，唯一性约束，外键约束的字段上，会自动的添加相关的索引.
显式的方式创建普通索引.
我们可以通过show index from 表名的方式来查看表中的索引.
我们只能在创建表时通过定义主键约束的方式隐式定义主键约束.

(2). 创建表后添加索引

1). ALTER TABLE ... ADD...

CREATE TABLE demo2(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
books VARCHAR(20)
);

ALTER TABLE demo2 ADD INDEX demo2_index_books(books);

ALTER TABLE demo2 ADD UNIQUE KEY demo2_uq_name(name);

show index from demo2;

2). CREATE INDEX ... ON ...

CREATE TABLE demo3(
id INT PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(20)
);

CREATE INDEX demo3_index_fname ON demo3(fname);

CREATE UNIQUE INDEX demo3_uq_lname ON demo3(lname);

show index from demo3;

3. 删除索引

(1). 方式1 : ALTER TABLE ... DROP

ALTER TABLE demo3 DROP INDEX demo3_index_fname;

show index from demo3;

(2). 方式2 : DROP INDEX ... ON ...

DROP INDEX demo3_uq_lname ON demo3;

注意 : 添加了AUTO_INCREMENT自增约束的列的索引是不可以被删除的.因为自增约束依赖于主键约束或唯一键约束.删除了列的索引，其约束也跟着被删除了.AUTO_INCREMENT失去了依赖的主键/唯一键约束，即会报错.

(3). 删除联合索引的列

删除联合索引的列.

ALTER TABLE ... DROP COLUMN ...

CREATE TABLE demo4(
column1 VARCHAR(20),
column2 VARCHAR(20),
column3 VARCHAR(20)
);
# 创建了一个联合索引
CREATE INDEX demo4_mul ON demo4(column1, column2, column3);

SHOW INDEX FROM demo4;
# 删除联合索引的列
ALTER TABLE demo4 
DROP COLUMN column1;

SHOW INDEX FROM demo4;