MySQL索引和视图的使用(包含三大范式)

索引

什么是索引?

  • 索引是各种数据库进行优化的重要手段!
  • 索引是在数据库表的字段上添加的,是为了提高查询效率而存在的一种机制。
  • 一张表的一个字段可以添加一个索引,多个字段联合起来也可以添加索引。
  • 索引就相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。

例子:对于一本字典来说,查找某个汉字有两种方式:

第一种:一页一页挨着找,知道找到为止,这种查找方式属于全字典扫描,效率较低。

第二种:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个位置,做局域性扫描,缩小扫描的范围,快速的查找,这种查找方式属于通过索引检索,效率较高。

注意:在实际开发中,汉语字典前面的目录是排序的,按照 a、b、c… 排序,为什么排序?因为只有排序了才会有区间查找这一说法(缩小扫描范围其实就是扫描某个区间罢了)

  • 在mysql 数据库当中,索引也是需要排序的,并且这个索引的排序和 TreeSet 数据结构相同,TreeSet 底层是一个自平衡的二叉树!在 mysql 中索引是一个 B-Tree 数据结构。
  • 在mysql中,主键字段和unique字段上都会自动添加索引

索引实现原理

假设现在有一张 t_user 表:
id(PK)		name			每一个行记录在硬盘上都有物理存储编号
--------------------------------------------------------------
100			zhangsan		0x1111
120			lisi			0x2222
99			wangwu			0x3333
88			zhaoliu			0x6666
101			houqi			0x2332
55			wuba			0x9999
100			xiaojiu			0x6954

说明一:
1、在任何数据库表当中,主键都会自动添加索引,所以这里的 id 字段上自动会有索引。
2、在 mysql 当中,一个字段上如果有 unique 约束的话,也会自动创建索引对象。

说明二:
1、在任何数据库当中,任何一张表的任何一条记录,在硬盘的存储上都有一个硬盘的物理存储编号。

说明三:
1、在 mysql 中,索引是一个单独的对象,不同的存储引擎以不同的形式存储:
	在 MyISAM 存储引擎中,索引存储在一个 .MYI 文件中;
	在 InnoDB 存储引擎中,索引存储在一个逻辑名称叫做 tablespace 的文件中;
	在 MEMORY 存储引擎中,索引被存储在内存中
2、不管索引存储在哪里,索引在 mysql 中都是一个树的形式存在(自平衡二叉树,B-Tree)	

在这里插入图片描述

这个图应该不是那么的准确,大家大概了解一下就好了

什么条件下,我们会考虑给字段添加索引?

条件一:数据量庞大(多少数据才算庞大?这个不能确定,需要测试,因为每一个硬件环境不同)

条件二:该字段经常出现在 where 的后面,以条件的方式存在,也就是说这个字段总是被扫描。

条件三:该字段很少的 MDL(insert、update、delete)操作(因为 DML 之后,索引需要重新进行排序,如果频繁执行 DML,那么索引就需要频繁的排序,这样会拖慢查询速度)

建议:不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能;建议查询的时候通过主键或unique约束的子弹进行查询,这样的效率是比较高的。

索引的创建与删除

语法案例

-- 查看索引
show index from emp;

-- 创建索引
-- 给 emp 表的 ename 字段添加索引,起名为:emp_ename_index
create index emp_ename_index on emp(ename);

-- 删除索引
-- 删除 emp 表中的 emp_ename_index 索引
drop index emp_ename_index on emp;

在 mysql 中,怎么查看一个 SQL 语句是否使用了索引进行检索?

未添加索引的效果:

-- 我现在的 emp 表中有 14 行数据
explain select * from emp where ename = 'KING';

在这里插入图片描述

根据上图,我们可以看到 rows = 14、type = ALL 也就是说这条 SQL 语句是全表检索。

添加索引之后的效果:

-- 添加索引
create index emp_ename_index on emp(ename);

-- 查看结果
explain select * from emp where ename = 'KING';

在这里插入图片描述

根据上图,我们可以看到 rows = 1,也就是说这条 SQL 只检索了一条数据。

索引的失效

索引说明时候会失效呢?

情况一
  • 以百分号(%)开头就会导致索引失效。
select * from emp where ename like "%T";

问:上述语句中,ename 上即使添加了索引,也不会走索引,为什么呢?
解:原因是因为模糊查询当中以 “%” 开头了(百分号开头表示不知道第一个字符是什么,所以每次都需要先去获取到对应的字符,然后才开始塞选)
> 所以在使用模糊查询的时候,需要尽量避免使用 “%” 开头(这是一种优化的手段/策略),如果一定要使用的话,那么就只能全表检索了。
情况二
  • 在使用 or 的时候会失效,如果使用 or,那么要求 or 两边的字段都要有索引,如果其中一边的一个字段没有索引,那么另一个字段上的索引也会失效,所以这就是不建议使用 or 的原因。

    union 不会使索引失效。

explain select * from emp where ename = 'KING' or job = 'MANAGER';
情况三
  • 使用复合索引的时候,没有使用左侧的列进行查找,索引就会失效!
  • 复合索引:两个字段或更多字段联合起来添加一个索引,叫做复合索引。
-- 创建复合索引
create index emp_job_sal_index on emp(job, sal);

-- 使用了索引(复合索引左边的列):
explain select * from emp where job = 'MANAGER';

explain select * from emp where job = 'MANGER' and sal = 800;

-- 未使用索引(复合索引右边的列):
explain select * from emp where sal = 800;

-- or 如果想要使用索引,就必须保证两边的字段都是有索引的,而这里的复合索引中,右边的索引字段是不能单独使用的,如果单独使用,则不会具有索引
explain select * from emp where job = 'MANGER' or sal = 800;
情况四
  • 在 where 当中索引列字段参加了运算,索引就会失效
-- 删除刚才创建的复合索引
drop index emp_job_sal_index on emp;

-- 给 sal 列添加索引
create index emp_sal_index on emp(sal);

-- 使用了索引:
explain select * from emp where sal = 800;

-- 没有使用索引:
explain select * from emp where sal + 1 = 800;

-- 如果是值进行了运算,则不会导致索引失效,比如:
explain select * from emp where sal = 800 + 1;
情况五
  • 在 where 当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';

索引的分类

  1. 单一索引:一个字段上添加索引。
  2. 复合索引:两个字段或更多字段添加索引。
  3. 主键索引:主键上添加索引。
  4. 唯一性索引:具有 unique 约束的字段上添加约束。

注意:唯一性比较弱的字段上添加索引用处不大(简单理解:在一列中如果有很多重复值,那么此时索引的作用并不大;所以索引添加的字段越唯一,效率就越高!)。

视图(view)

什么是视图?

  • view:站在不同的角度去看待同一份数据。
  • 视图对象也是一个文件,在数据库中也是以文件的形式存在的,并不会因为重启 MySQL 而消失。

如何创建视图对象?

  • 语法create view 视图名称 as select * from 表名;
create view dept_bak_view as select * from dept_bak;

注意:只有 DQL 语句才能以 view 的形式创建视图,也就是说 as 后面的语句必须是 DQL 语句(查询语句)

如何删除视图对象?

  • 语法drop view 视图名;
drop view dept_bak_view;

有了视图之后,可以干什么?

  • 我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作。

  • 视图的特点:通过对视图的操作,会影响到原表数据。

    注意:视图可以影响到原表数据,原表也可以影响视图中的数据。

  • 作用:提高检索效率,隐藏表的实现细节。

---------------------------单表---------------------------
-- 面向视图查询
select * from dept_bak_view;

-- 面向视图插入
insert into dept_bak_view(deptno, dname, loc)
values(50, 'SALES', 'BEIJING');

-- 查询原表数据
select * from dept_bak;

-- 面向视图删除
delete from dept_bak_view;

-- 查询原表数据
select * from dept_bak;
---------------------------多表---------------------------
-- 创建视图对象
create view emp_dept_view as
select e.ename, e.sal, d.dname from emp e
inner join dept d
on e.deptno = d.deptno;

-- 查询视图对象
select * from emp_dept_view;

-- 更新视图对象
update emp_dept_view set sal = 1000 where ename = 'TEST_name';

-- 查询原表数据
select e.ename, e.sal, d.dname from emp e
inner join dept d
on e.deptno = d.deptno;

看了以上案例,视图对象在实际开发中到底有什么用呢?

  • 假设有一条非常复杂的 SQL,而这条 SQL 语句需要在不同的位置上反复使用吗,每一次使用这个 SQL 语句的时候都需要重新编写,非常的麻烦,这个时候就可以使用视图。

    将这条复杂的 SQL 语句以视图对象的形式新建,在需要编写这条 SQL 语句的位置直接使用视图对象,可以大大的简化开发,并且利于后期的维护,因为修改的时候也只需要修改一个位置就行,只需要修改视图对象所映射的 SQL 语句。

说明

  • 以后再面向视图开发的时候,使用视图的时候就可以像使用表一样;
  • 可以对视图进行增删改查等操作,视图不是在内存中的,而是存储在硬盘中的,不会消失。

什么是CRUD?

  • 增删改查又被称为:CRUD
  • CRUD 是在公司中程序员之间沟通的术语,一般我们很少说增删改查,一般都是说 CRUD
  • C:Create(增)
  • R:Retrive(查 / 检索)
  • U:Update(改)
  • D:Delete(删)

DBA 常用命令(了解)

在这里插入图片描述

1、创建数据库账号密码

语法:CREATE USER 用户名 IDENTIFIED BY '密码';

create user heweibin identified by '1234';

2、数据的导出

语法:mysqldump 数据库名>路径:\文件名.sql -u 用户名 -p 密码

-- 导出指定数据库
mysqldump test_sql>D:\test.sql -u root -p 1234

-- 导出指定数据库下指定的表
mysqldump test_sql emp>D:\emp.sql -u root -p 1234

3、数据的导入

步骤:
1. 先登录到 mysql 数据库服务器上
2. 然后创建数据库:create database test_sql
3. 使用数据库:use test_sql
4. 初始化数据库:source D:\test.sql

数据库设计的三大范式

什么是数据库设计范式?

  • 数据库表的设计依据,教我们怎么进行数据库表的设计。

说明

  • 第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分!
  • 第二范式:建立在第一范式的基础之上,要求所有非主键字段,完全依赖主键,不要产生部分依赖!
  • 第三范式:建立在第二范式的基础之上,要求所有非主键字段,直接依赖主键,不要产生传递依赖!
  • 设计数据库表的时候,按照上面的三大范式进行设计,可以避免表中数据的冗余,空间的浪费。

第一范式

  • 最核心,最重要的范式,所有表的设计都需要满足。
  • 必须要有主键,并且每一个字段都是原子性不可再分。
学生编号		学生姓名		联系方式
----------------------------------------------------
1001			张三			zs@123.com, 13912345678
1002			李四			ls@123.com, 16612345678
1003			王五			ww@123.com, 19912345678

问:以上为学生表,满足第一范式吗?
解:不满足
	1、没有主键
	2、联系方式可以分为邮箱地址和电话
	
将上面的表修改为满足第一范式的:	
学生编号(PK)		学生姓名		邮箱地址		联系电话
------------------------------------------------------------
1001				张三			zs@123.com		13912345678
1002				李四			ls@123.com 		16612345678
1003				王五			ww@123.com 		19912345678	

第二范式

  • 建立在第一范式的基础之上,要求所有非主键字段必须完全依赖主键,不能产生部分依赖。
学生编号 	学生姓名 	教师编号 	教师姓名
------------------------------------------
1001 		张三 		 001		王老师
1002		李四		 002 		赵老师
1003 		王五 		 001 		王老师
1001	 	张三		 002 		赵老师

这张表描述了学生和老师的关系:一个学生可能有多个老师,一个老师可能有多个学生(这是典型的多对多关系)

问:以上为学生表,满足第一范式吗?
解:不满足
	1、没有主键
	2、联系方式可以分为邮箱地址和电话
	
将上面的表修改为满足第一范式的:		
学生编号 + 教师编号(PK)		学生姓名 	   教师姓名
-----------------------------------------------------
1001 		001				张三 		 	王老师
1002		002				李四		  	赵老师
1003 		001				王五 		  	王老师
1001		002				张三		  	赵老师

》说明:将学生编号和教师编号联合做主键:复合主键,此时就是满足第一方式的

问:修改后的表结构是否满足第二范式?
解:不满足
   原因:张三依赖 1001,王老师依赖 001,显然产生了部分依赖(产生部分依赖的缺点:数据冗余了,空间浪费了)

修改为满足第二范式的表:
使用三张表来表示多对多的关系,分别为:学生表、教师表、学生教室关系表
学生表:
学生编号(PK)	 学生姓名
-------------------------
1001			  张三
1002			  李四
1003			  王五

教师表:
教师编号(PK)	   教师姓名
---------------------------
001					王老师
002					赵老师

学生教师关系表:
id(PK)		教师编号(FK)		学生编号(FK)
-------------------------------------------------
1			 001				  1001
2			 002				  1002
3			 001				  1001
4			 002				  1002

多对多口诀:多对多,三张表,关系表两个外键!

第三范式

  • 第三范式建立在第二范式的基础之上,要求所有非主键字段必须直接依赖主键,不能产生传递依赖
学生编号(PK)   学生姓名 	班级编号	 班级名称
------------------------------------------------------
1001 			张三 			01 		  一年一班
1002 			李四			02		  一年二班
1003 			王五 			03 		  一年三班
1004 			赵六 			03 		  一年三班

问:以上设计的表,满足第一范式吗?
解:满足

问:以上设计的表,是否满足第二范式?
解:满足,因为主键不是复合主键,没有产生部分依赖,主键是单一主键

问:以上设计的表,是否满足第三范式?
解:不满足
   原因:一年一班依赖 01, 01 依赖 1001,产生了传递依赖,不符合第三范式的要求,产生了数据的冗余。
   
将上面的表修改为满足第三范式的:
班级表:
班级编号(PK)    班级名称
----------------------------
01 				一年一班
02 				一年二班
03 				一年三班

学生表:
学生编号(PK)   学生姓名 	班级编号(FK)	 
--------------------------------------
1001 			张三 			01 		
1002 			李四			02	
1003 			王五 			03 		
1004 			赵六 			03 	

学生表和班级表的关系是:一个班级对应多个学生,一个学生对应一个班级(是典型的一对多关系)

一对多口诀:一对多,两张表,多的表加外键

总结

  • 一对多:一对多,两张表,多的表加外键!

  • 多对多:多对多,三张表,关系表加两个外键!

  • 一对一:一对一,外键唯一!

    一对一放在一张表中不就好了吗?为什么还要拆分表?

    在实际开发中,可能存在一张表字段太多,太庞大了,这个时候就需要拆分表。

其它说明

  • 数据库设计的三大范式是理论上的。
  • 实践和理论有的时候有偏差。
  • 最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度,因为在 SQL 中,表和表之间连接次数越多,效率越低(笛卡尔积)。
  • 有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,并且对于开发人员来说, SQL 语句的编写难度也会降低。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值