MySQL(完结)

索引(index)

什么是索引?

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

MySQL在查询方面主要有两种方式:

  • 全表扫描

  • 根据索引检索

注意:在mysql数据库当中的索引需要排序(例如新华字典的目录,需要缩小扫描范围),并且这个索引的排序和TreeSet数据结构相同,TreeSet(TreeMap)底层是一个自平衡二叉树,在MySQL中索引是一个B-Tree数据结构(遵循左小右大原则存放,采用中序遍历方式遍历取数据)

索引的实现原理

1.在任何数据库当中主键上都会自动添加索引对象,在MySQL中,一个字段如果有unique约束的话,也会自动创建索引对象
2.在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号
在mysql当中,索引是单独的一个对象,不同的存储引擎以不同的形式存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中,在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中,在MEMORY存储引擎当中索引被存储在内存当中,不管索引存储在哪里,索引在mysql当中都是以树的形式存在

假设这个字段名为id,idIndex(字段的索引对象)

image

什么条件下会考虑给字段添加索引?
1.数据量庞大
2.该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描
3.该字段很少的DML操作(因为DML之后,索引需要重新排序)

建议不要随意添加索引,因为索引也是需要维护的,太多的话反而降低系统的性能
建议通过主键、unique约束的字段进行查询,效率是比较高的

索引的语法,创建、删除

#添加索引
#给emp表的ename字段添加索引,起名:emp_ename_index
create index emp_name_index on emp(ename);

#删除索引
#将emp表上的emp_ename_index索引对象删除
drop index emp_name_index on emp;

#查看SQL语句中是否使用了索引进行检索
explain select * from emp where ename = 'KING';

添加检索前:14条记录,全表扫描
image
添加检索后:1条记录,区间扫描
image
数组 + 树的结合体

索引的失效

失效的第一种情况:
	select * from emp where ename like '%T';
	ename上即使添加了索引,也不会走索引。
		因为模糊匹配中以"%"开头了
		尽量避免模糊查询时以"%"开始
		这是一种优化的手段/策略

失效的第二种情况:
	使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有
	索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个字段上
	的索引也会失效,这就是不建议使用or的原因

ename字段添加了索引,最后还是查找了十四次
explain select * from emp where ename = 'KING' or job = 'CLERK';

失效的第三种情况:
	使用复合索引的时候,没有使用左侧的列查找,索引失效
复合索引:两个字段,或更多的字段联合起来添加一个索引,叫复合索引
  create index emp_job_sal_index on emp(job,sal);
 
 	explain select * from emp where job = 'CLECK'; //只查询左边的job是没有问题的,一次就能查出
	 explain select * from emp where sal = 800;//只查右边索引失效,查询次数还是14

失效的第四种情况:
	在where当中的索引列参加了运算,索引失效
 explain select * from emp where sal+1 = 800; //工资列 +1,此时查询次数依然是14,索引失效
 
失效的第五种情况:
	在where当中索引列使用了函数
	 create index emp_ename_index on emp(ename);
 	explain select * from emp where lower(ename) = 'smith'; //这里使用了lower,索引失效,查询14次

索引是各种数据库进行优化的重要手段,优化的时候优先考虑的因素就是索引

索引分类:
单一索引:一个字段上添加索引
复合索引:两个字段或者更多的字段上添加索引

主键索引:主键上添加索引
唯一性索引:具有unique约束的字段上添加索引

注意:唯一性比较弱的字段上添加索引用处不大

视图(view)

什么是视图?

  • view:站在不同的角度去看待同一份数据

创建、删除视图

#表复制
create table dept2 as select * from dept;
select * from dept2;

#创建视图
create view dept2_view as select * from dept2;

#删除视图
drop view dept2_view;
/*
注意:只有DQL语句才能以view的形式创建
create view view_name as 这里的语句必须是DQL语句
*/

用视图做什么?

可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作(视图的特点:通过对视图的操作,会影响到原表数据)

#面向视图查询
select * from dept2_view;

#面向视图插入
insert into dept2_view(deptno,dname,loc) values (30,'SALES','HUNAN');

#查询原表数据
select * from dept2;
# 面向视图删除
delete from dept2_view;
#面向视图更新
update dept2_view set deptno=300 where loc = 'HU';


create view
	emp_dept_view
as
	select
		e.ename,e.sal,d.dname
	from
		emp e
	join 
		dept d
	on
		e.deptno = d.deptno;
视图的作用:
假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用
每一次使用这个SQL语句的时候都需要重新编写,很长很麻烦,怎么解决?
	可以把这条复杂的SQL语句的位置直接使用视图对象,可以大大简化开发
	并且利于后期的维护,因为修改的时候也只需要修改一个位置就行,只需要修改
	视图对象所映射的SQL语句

我们以后面向视图开发的时候,使用视图的时候可以像使用table一样。
可以对视图进行增删改查等操作,视图不是存储在内存当中,视图对象
也是存储在硬盘上的,不会消失

增删改查,又叫做:CRUD
C:Create(增)
R:Retrive(删)
U:Update(改)
D:Delete(查)

DBA常用命令

https://blog.csdn.net/wallimn/article/details/83632559

重点掌握:数据的导入和导出(数据的备份)

数据导出:
注意:在windows的dos命令窗口中:
mysqldump xiaohu>E:\xiaohu.sql -uroot -p11111
导出指定表:
mysqldump xiaohu 数据库下的表名>E:\xiaohu.sql -uroot -p11111
数据导入:
注意:需要先登录到mysql数据库服务器上
然后创建数据库:create database xiaohu;
使用数据库:use xiaohu
然后初始化数据库:source D:\xiaohu.sql

数据库设计三范式

什么是数据库设计范式?

数据库表的设计依据,教你怎么进行数据库表的设计

数据库范式

第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分

第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖

第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖

设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费

第一范式

最核心,最重要的范式,所有表的设计都需要满足
必须有主键,并且每一个字段都是原子性不可再分

第二范式

建立在第一范式的基础之上,
要求所有非主键字段必须完全依赖主键,不要产生部分依赖

image

image

多对多设计:多对多,三张表,关系表两个外键

第三范式

建立在第二范式的基础上
要求所有非主键字段必须直接依赖主键,不要产生传递依赖

image
image

一对多,两张表,多的表加外键

image

image
一对一:外键唯一

数据库设计三范式是理论上的

实践和理论有的时候有偏差

最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度

因为sql中,表和表之间的连接次数越多,效率越低(笛卡尔积)

有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,
并且对于开发人员来说,sql语句的编写难度也会降低
posted @ 2021-09-24 20:00  guided  阅读(253)  评论(0)    收藏  举报