MySQL学习笔记汇总（五）——索引、视图、数据库设计三范式

r囧r小猫 2023-10-05 08:07 8阅读 0赞

## 一、索引 ##

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的 行。表越大，花费时间越多。  
**添加索引是给某一个字段，或者说某些字段添加索引。**

*  **什么时候考虑给字段添加索引**

> 数据量庞大。（根据客户的需求，根据线上的环境）  
> 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）  
> 该字段经常出现在where子句中。（经常根据哪个字段查询）

主键和具有unique约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。

*  **创建索引对象：**

create index 索引名称 on 表名(字段名);

*  **删除索引对象：**

drop index 索引名称 on 表名;

*  **索引的实现原理：**

> 索引底层采用的数据结构是：B + Tree。

> 通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

*  **索引的分类:**

> 单一索引：给单个字段添加索引  
> 复合索引: 给多个字段联合起来添加1个索引  
> 主键索引：主键上会自动添加索引  
> 唯一索引：有unique约束的字段上会自动添加索引  
> …

*  索引什么时候失效?

> 例如：进行如下查询  
> select ename from emp where ename like ‘%A%’;  
> 模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

案例：给薪资sal字段添加索引。  
添加索引前：sal语句的执行计划  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70]  
添加索引：

create index sal_emp_index on emp(sal);

添加索引后：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 1]

## 二、视图（view） ##

*  站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。
 *  创建视图，删除视图

> create view 视图名 as select …  
> drop view 视图名;

*  对视图进行增删改查，会影响到原表数据。
 *  视图的作用

> 视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图,只对视图对象进行CRUD。

## 三、数据库设计的三范式 ##

*  什么是设计范式？  
    **设计表的依据。按照这个三范式设计的表不会出现数据冗余。**
 *  三范式都是哪些？  
    第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。  
    第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。  
    第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

**多对多设计方案：三张表，关系表两个外键。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 2]  
**一对多设计方案：两张表，多的表加外键。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 3]  
**一对一设计方案：**  
1.主键共享  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 4]  
2.外键唯一  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 5]

**今天的分享就到这里啦！！~感谢大家的观看，希望对大家有帮助的话麻烦给个丝滑三连击。（点赞＋转发+关注）  
一起加油，一起努力，一起秃见成效**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200811163216743.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200811163328364.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200811164943680.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200811165129570.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200811165916362.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200811170053481.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pwX2luc2lzdA==,size_16,color_FFFFFF,t_70