MySQL中的索引、原理以及常见索引的添加、删除

索引：提高数据库的性能。

本质就是对添加索引的字段进行拷贝，并把它放进B+树（MySQL）中，消耗空间来获取时间。
所以说每给表增加一个索引，就会多一份拷贝，也就浪费一部分空间。
添加索引也要选择合适的字段：

• 经常查找的字段（where）
• 唯一的字段
• 不为null

优点：查找效率高
缺点：创建索引耗时、占用物理空间、维护索引（树）浪费资源（添加、删除等操作）

索引失效的几个例子：

• 用or查询
• 不用索引字段查询
• 模糊查询（like %）
• MySQL预计全表查找优于索引的时候

---

常见的索引：

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

---

1. 主键索引(primary key)

一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

1.1 创建

• 在创建表的时候，直接在字段名后指定 primary key
  create table user1(id int primary key, name varchar(30));

• 在创建表的最后，指定某列或某几列为主键索引
  create table user2(id int, name varchar(30), primary key(id));

• 创建表以后再添加主键
  alter table user3 add primary key(id);

1.2 删除主键索引

alter table 表名 drop primary key;

---

2. 唯一索引(unique)

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

2.1 创建

• 在表定义时，在某列后直接指定unique唯一属性。
  create table user4(id int primary key, name varchar(30) unique);

• 创建表时，在表的后面指定某列或某几列为unique
  create table user5(id int primary key, name varchar(30), unique(name))；

• alter table user6 add unique(name);

---

3. 普通索引(index)

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

3.1 创建

• 创建一个索引名为 idx_name 的索引
  create index idx_name on user10(name);

• 创建表时，在表的后面指定某列或某几列为unique
  create table user5(id int primary key, name varchar(30), index(name))；

• alter table user6 add index(name);

---

4. 全文索引(fulltext)

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引.

4.1 创建

• 创建表时，在表的后面指定某列或某几列为unique
  create table user5(id int primary key, name varchar(30), fulltext(name))；

---

删除索引（通用）

• alter table 表名 drop index 索引名；
• drop index 索引名 on 表名

查看索引

• show keys from 表名
• show index from 表名;
• desc 表名；