[数据库]索引（创建索引、查询索引、删除索引、索引创建规则）

1.索引

  索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。但是查询速度的提高是以插入，更新，删除的速度为代价的，所以索引的作用在于提高一个海量数据的检索速度。
常见索引分为：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)

1.1 原理

假设数据库中有上亿的数据，我们执行select操作，就是将这上亿个数据按着遍历一遍，性能会非常的差，耗时巨大。
但是建立索引之后搜索的性能将大大提高

索引的缺点：

• 占用磁盘空间
• 当添加一条记录，除了添加到表中，还要维护二叉树，速度有影响但不大
• 当我们添加一条索引，不能够解决所有的查询问题，需要分别给字段建立索引
• 索引是以空间换取时间

1.2 创建索引

1.2.1 创建主键索引

创建表时，直接在字段名后指定primary key

create table user1(
	id primary key,
	name varchar(32)
);

创建表时，指定某列或某几列为主键索引

create table user2(
	id int,
	name varchar(32),
	primary key(id)
);

创建表后添加主键

create table user3(
	id int,
	name varchar(32)
);
alter table user3 add primary key(id);

主键索引的特点：

• 一个表中最多有一个主键索引
• 主键索引的效率高
• 创建主键索引的列，它的值不能为null、且不能重复
• 主键索引的值一般都是int

1.2.2 唯一索引的创建

直接在字段名后指定unique唯一属性

create table user4(
	id int primary key,
	name varchar(32) unique
);

创建表时，指定某列或某几列为unique

create table user5(
	id int primary key,
	name varchar(32),
	unique(name)
);

创建表后添加唯一索引

create table user6(
	id int primary key,
	name varchar(32)
);
alter table user6 add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

1.2.3 普通索引的创建

在表的定义后，指定某列为索引

create table user8(
	name varchar(20),
	email varchar(30)
	index(name)
);

创建表后，指定某列为索引

create table user9(
	id primary key,
	name varchar(20),
	email varchar(30)
);
alter table
 user9  add index(name);

直接创建索引

create table user10(
	id int primary key, 
	name varchar(32),
	email varchar(30)
);
create index idx_name on user10(name);

1.2.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引，MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文搜索支持英文，不支持中文。

1.3 查询索引

show keys from 表名\G;

show index from 表名；
desc 表名; //比较简略

1.4 删除索引

删除主键索引

alter table 表名 drop primary key;

其他索引的删除

alter table 表名 drop index 索引名;

drop index 索引名 on 表名

1.5 索引创建的规则

• 比较频繁作为查询字段的字段应该建立索引
• 唯一性太差的字段不适合单独创建索引
• 更新十分频繁的字段不适合创建索引
• 不会出现在where语句中的字段不该创建索引