一、索引的分类
按照不同角度分类如下:
- 功能逻辑上分类:普通索引、唯一索引、主键索引、全文索引
- 物理实现上分类:聚簇索引、非聚簇索引
- 作用字段上分类:单列索引、联合索引
1、普通索引
创建普通索引时不附加任何限制条件,只是用于提高查询效率。这一类的索引可以创建在任何数据类型中。创建索引后可通过索引进行查询。
2、唯一性索引
使用 UNIQUE 关键字可以设置唯一性索引。创建唯一性索引时,该列的值必须是唯一的,但是可以为空值。在一张表中可以有多个唯一性索引。
3、主键索引
主键索引是一种特殊的唯一性索引,它在唯一性索引的基础上增加了不为空的约束。一张表中最多只能有一个主键索引。
4、单列索引
在表中的单个字段上创建的索引。单列索引可以是普通索引、唯一性索引、全文索引等,只要保证该索引对应一个字段即可。一个表可以有多个单列索引。
5、多列(组合、联合)索引
多列索引是在表的多个字段上创建的一个索引。可以通过这几个字段进行查询,但是只有查询条件使用了这些字段的第一个字段时,该索引才会生效,遵循最左前缀原则。
6、全文索引
使用分词技术等多种算法分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能的筛选出搜索结果。全文索引非常适合大型数据集。
使用 FULLTEXT 可以设置全文索引,允许空值和重复值,只能创建在CHAR、VARCHER、TEXT类型及其系列类型的字段上。查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。
二、创建索引
1、创建表时
(1)普通索引
CREATE TABLE student (
id INT,
name VARCHAR (100),
INDEX idx_name (name)
);
(2)唯一索引
CREATE TABLE student (
id INT,
name VARCHAR (100),
UNIQUE INDEX uk_idx_id(id)
);
(3)主键索引
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR (100)
);
(4)联合索引
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR (100),
age INT(11) NOT NULL,
INDEX multi_idx(id,name,age)
);
(5)全文索引
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR (100),
age INT(11) NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info)
);
2、创建表后
(1)使用ALTER TABLE语句创建索引
例如:
ALTER TABLE student ADD INDEX idx_name(name);
ALTER TABLE student ADD UNIQUE idx_age(age);
ALTER TABLE student ADD INDEX mul_name_age_info(name,age,info);
(2)使用CREATE INDEX创建索引
例如:
CREATE INDEX idx_name ON student(name);
CREATE UNIQUE INDEX uk_idx_age ON student(age);
CREATE INDEX mul_name_age ON student(name,age);
三、删除索引
(1)使用ALTER TABLE删除索引
ALTER TABLE student DROP INDEX idx_name;
(2)使用DROP INDEX语句删除索引
DROP INDEX mul_name_age ON student;
注意:
添加 AUTO_INCREMENT 约束字段的唯一索引不能被删除