MySQL 学习笔记（18）— 索引的分类、创建、查看、删除等

1. 索引类型

1.1 唯一索引与非唯一索引

唯一索引（ UNIQUE ）中的索引值必须唯一，可以确保被索引的数据不会重复，从而实现数据的唯一性约束。

非唯一索引允许被索引的字段存在重复值，仅仅用于提高查询的性能。

1.2 单列索引与多列索引

单列索引是基于单个字段创建的索引。例如，员工表的主键使用 emp_id 字段创建，就是一个单列索引。

多列索引是基于多个字段创建的索引，也叫复合索引。创建多列索引的时候需要注意字段的顺序，查询条件中最常出现的字段放在最前面，这样可以最大限度地利用索引优化查询的性能。

1.3 全文索引与函数索引

全文索引（Full-text），用于支持全文搜索，类似于 Google 和百度这种搜索引擎。

函数索引，基于函数或者表达式的值创建的索引。例如，员工的 email 不区分大小写并且唯一，可以基于 UPPER(email) 创建一个唯一的函数索引。

2. 创建索引

使用 CREATE INDEX 语句创建索引，默认情况下创建的是 B+ 树索引：

CREATE [UNIQUE] INDEX index_name 
ON table_name(col1 [ASC | DESC], ...);

其中， UNIQUE 表示创建唯一索引； ASC 表示索引按照升序排列， DESC 表示索引按照降序排列，默认为 ASC 。以下语句为表 emp_devp 的员工姓名字段创建一个普通索引：

CREATE INDEX idx_emp_devp_name ON emp_devp(emp_name);

定义主键和唯一约束时，数据库自动创建相应的索引。 MySQL InnoDB 存储引擎也会自动为外键约束创建索引。

3. 查看索引

MySQL 提供了 SHOW INDEXES 命令查看表的索引：

其中 PRIMARY 是系统自动为主键创建的索引。

4. 删除索引

DROP INDEX 语句用于删除一个索引：

-- Oracle 和 PostgreSQL 实现
DROP INDEX idx_emp_devp_name;

对于 MySQL 和 SQL Server 而言，删除索引时需要指定表名：

-- MySQL 和 SQL Server 实现
DROP INDEX idx_emp_devp_name ON emp_devp;

5. 索引缺点

既然索引可以优化查询的性能，那么我们是不是应该将所有字段都进行索引？显然并非如此，因为索引在提高查询速度的同时也需要付出一定的代价。

首先，索引需要占用磁盘空间。索引独立于数据而存在，过多的索引会导致占用大量的空间，甚至超过数据文件的大小。

其次，对数据进行 DML 操作时，同时也需要对索引进行维护；维护索引有时候比修改数据更加耗时。