MySql第三篇---索引的创建与设计原则

文章目录

MySql第三篇---索引的创建与设计原则
- 索引的声明与使用
- - 索引的分类
  - 创建索引
  - 在已经存在的表上创建索引
  - 删除索引
- 索引的设计原则
- - 哪些情况适合创建索引？
  - 限制索引的数目
  - 哪些情况不适合创建索引？
- 小结

MySql第三篇—索引的创建与设计原则

索引的声明与使用

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
1.从功能逻辑上来说，索引主要有4种，分别是普通索引，唯一索引，主键索引，全文索引。
2.按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
3.按照作用字段个数进行划分，分成单列索引和联合索引。

在这里插入图片描述

创建索引

1.创建表的时候创建索引

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
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)
);

在这里插入图片描述

1. 创建普通索引
在这里插入图片描述

2. 创建唯一索引
在这里插入图片描述

3. 主键索引

4.创建单列索引

在这里插入图片描述

5.创建组合索引
在这里插入图片描述

6.创建全文索引
在这里插入图片描述

7. 创建空间索引
在这里插入图片描述

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

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。如下图：
在这里插入图片描述
案例如下图：

删除索引

在这里插入图片描述
案例如下图：

索引的设计原则

哪些情况适合创建索引？

1. 字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2. 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4. UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引
在这里插入图片描述

6. 在多个字段都要创建索引的情况下，联合索引优于单值索引

7.使用最频繁的列放到联合索引的左侧
因为索引匹配的时候有一个最左匹配原则。

限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:
① 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
② 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
③优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。