MySQL索引的创建

创建日期：2022/04/21 修改日期：2023/05/31 MySQL

> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349)

# 索引的声明与使用

## 索引类型

> 分别库

- 按功能逻辑：
  - 普通索引
  - 唯一索引
  - 主键索引
  - 全文索引
  - 空间索引
- 按物理实现方式：
  - 聚簇索引
  - 非聚簇索引
- 按作用字段个数：
  - 单列索引
  - 联合索引

> 不同的存储引擎支持的索引类型也不一样

- InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引； 
- MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
- Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
- NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
- Archive ：不支持 B-tree、Hash、Full-text 等索引；

## 索引的创建

### 创建表时添加索引

#### 基本语法

> 方式1：隐式的方式创建索引。在声明有**主键约束**、**唯一性约束**、**外键约束**的字段上，会自动的添加相关的索引

```
mysql> CREATE TABLE dept ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR ( 20 ) );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE emp (
    -> emp_id INT PRIMARY KEY AUTO_INCREMENT,
    -> emp_name VARCHAR ( 20 ) UNIQUE,
    -> dept_id INT,
    -> CONSTRAINT emp_dept_id_fk FOREIGN KEY ( dept_id ) REFERENCES dept ( dept_id ) 
    -> );
Query OK, 0 rows affected (0.02 sec)

> 方式2：显式创建表时创建索引，基本语法格式如下：

```sql
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]
```

- `UNIQUE` 、 `FULLTEXT` 和 `SPATIAL` 为可选参数，分别表示唯一索引、全文索引和空间索引
- `INDEX` 与 `KEY` 为同义词，两者的作用相同，用来指定创建索引
- `index_name` 指定索引的名称，为可选参数，如果不指定，那么 `MySQL` 默认 `col_name` 为索引名
- `col_name` 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
- `length` 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
- `ASC` 或 `DESC` 指定升序或者降序的索引值存储。

#### 普通索引

```
mysql> CREATE TABLE user ( id INT, mobile INT, INDEX ( mobile ) );
Query OK, 0 rows affected (0.03 sec)

#### 唯一索引

> 显示

```
mysql> CREATE TABLE employee ( id INT, job_num INT, UNIQUE INDEX ( job_num ) );
Query OK, 0 rows affected (0.02 sec)

> 隐式

```
mysql> CREATE TABLE employee2 ( id INT, job_num INT UNIQUE );
Query OK, 0 rows affected (0.02 sec)

#### 主键索引

> 显示

```
mysql> CREATE TABLE student ( id INT, student_name VARCHAR ( 200 ), PRIMARY KEY ( id ) );
Query OK, 0 rows affected, 1 warning (0.01 sec)

> 隐式

```
mysql> CREATE TABLE student2 ( id INT PRIMARY KEY, student_name VARCHAR ( 100 ) );
Query OK, 0 rows affected (0.01 sec)

> 删除约束

```
mysql> ALTER TABLE student2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM student2;
Empty set (0.01 sec)

mysql> SHOW CREATE TABLE student2;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student2 | CREATE TABLE `student2` (
  `id` int NOT NULL,
  `student_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

#### 单列索引

作用在一个列上的索引均称为单列索引

#### 联合索引

在表中的 `name` 和 `age` 字段上建立组合索引

```
mysql> CREATE TABLE employee ( id INT NOT NULL, `name` CHAR ( 30 ) NOT NULL, age INT NOT NULL, INDEX multi_idx ( `name`, age ) );
Query OK, 0 rows affected (0.02 sec)

#### 全文索引

```
mysql> CREATE TABLE articles ( id INT, title VARCHAR ( 200 ), body TEXT, FULLTEXT INDEX ( body ) );
Query OK, 0 rows affected (0.09 sec)

不同于like方式的的查询：

```sql
SELECT * FROM articles WHERE title LIKE '%查询字符串%';
```

全文索引用match+against方式查询：

```sql
SELECT * FROM articles WHERE MATCH(body) AGAINST ('查询字符串');
```

> 全文搜索建议使用专业的搜索引擎，例如 `Elasticsearch`

#### 空间索引

注：仅了解

```
mysql> CREATE TABLE test_geo ( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo ( geo ) );
Query OK, 0 rows affected, 1 warning (0.02 sec)

### 在已经存在的表上创建索引

#### 使用 ALTER TABLE

> 语法：

```
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
```

> 示例

```
mysql> CREATE TABLE book ( id INT, `name` VARCHAR ( 100 ) );
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE book ADD INDEX idx_cmt ( `name` );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#### 使用 CREATE INDEX

> 在 `MySQL` 中，`CREATE INDEX 被映射到一个 `ALTER TABLE` 语句上。语法：

```
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
```

> 示例

```
mysql> CREATE TABLE book2 ( id INT, `name` VARCHAR ( 100 ) );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE INDEX idx_cmt ON book2 ( `name` );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

## 索引的删除

### 使用 ALTER TABLE

```sql
ALTER TABLE table_name DROP INDEX index_name;
```

### 使用 DROP INDEX

```sql
DROP INDEX index_name ON table_name;
```

# MySQL 8.0 新特性

## 降序索引

在索引的字段名称后面添加 `DESC` 关键字，默认情况下是 `ASC` ，一般用于倒序查找，如按创建时间倒序。

```
mysql> CREATE TABLE user(id INT,create_time datetime,INDEX (create_time DESC));
Query OK, 0 rows affected (0.01 sec)

## 隐藏索引

从 `MySQL 8.x` 开始支持**隐藏索引（invisible indexes）**，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。

> 创建表时直接创建

```
mysql> CREATE TABLE `user` ( id INT, `name` VARCHAR ( 100 ), INDEX idx_cmt ( `name` ) invisible );
Query OK, 0 rows affected (0.01 sec)

> 在已经存在的表上创建

```sql
CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE;
```

> 通过ALTER TABLE语句创建

```sql
ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE;
```

> 切换索引可见状态

```
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引
```

# 索引的设计原则

## 适合创建索引的11种情况

1. 字段的数值有唯一性的限制 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba） 说明：不要以为唯一索引影响了 `insert` 速度，这个速度损耗可以忽略，但提高查找速度是明显的。
2. 频繁作为 `WHERE` 查询条件的字段 某个字段在 `SELECT` 语句的 `WHERE` 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
3. 经常 `GROUP BY` 和 `ORDER BY` 的列 索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 `GROUP BY` 对数据进行分组查询，或者使用 `ORDER BY` 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立联合索引 。
4. `UPDATE` 、 `DELETE` 的 `WHERE` 条件列 对数据按照某个条件进行查询后再进行 `UPDATE` 或 `DELETE` 的操作，如果对 `WHERE` 字段创建了索引，就能大幅提升效率。原理是因为需要先根据 `WHERE` 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
5. `DISTINCT` 字段需要创建索引 有时候需要对某个字段进行去重，使用 `DISTINCT` ，那么对这个字段创建索引，也会提升查询效率。
6. 多表 `JOIN` 连接操作时，创建索引注意事项
 - 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
 - 对 `WHERE` 条件创建索引 ，因为 `WHERE` 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 `WHERE` 条件过滤是非常可怕的。
 - 对用于连接的字段创建索引 ，并且该字段在多张表中的类型必须一致 。
7. 使用列的类型小的创建索引
8. 使用字符串前缀创建索引 拓展：Alibaba《Java开发手册》 【 强制 】在 `varchar` 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上 ，可以使用 `count(distinct left(列名, 索引长度))/count(*)` 的区分度来确定。
9. 区分度高(散列性高)的列适合作为索引
10. 使用最频繁的列放到联合索引的左侧 这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

## 不适合创建索引的7种情况

1. 在 `where` 中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引
3. 有大量重复数据的列上不要建立索引
4. 避免对经常更新的表创建过多的索引
5. 不建议用无序的值作为索引 例如 `UUID` 、 `MD5` 、 `HASH` 、无序长字符串等。
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引

全部评论