目录
一、索引(Index)
1.1定义:
绑定在某些上字段使用,提高查询效率,可以绑定在一个或多个上,缩小扫描范围。
理解:
对于一本字典来说,查找某个汉字有两种方式:
第一种方式:一页一页挨着找,直到找到为止,这种查找方式属于全字典扫描。
效率比较低。
第二种方式:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个
位置,做局域性扫描,缩小扫描的范围,快速的查找。这种查找方式属于通过
索引检索,效率较高。
书籍内容查找方式:
逐页
目录
mysql查找的方式:
全表
根据索引检索
1.2 根据索引扫描首先需要排序:
排序结构与TreeSet数据结构相同,TreeSet底层是一个自平衡的二叉树、在mysql当中索引是一个B-Tree数据结构。
遵循左小右大原则存放,采用中序遍历方式存放方式遍历数据。
注释:
1.2数据的实现原理
注释1:主键、unique约束上自动添加索引,mysql数据库中.
注释2:任何数据库中,任何一张表上的每一条记录在硬盘有哦一个硬盘的物理存储编号
注释3:需要不断维护。
每一条记录都会生成一个存储编号,根据添加了索引的此条记录的字段会直接对应到该物理存储的编号从而查询到整条记录。
1.3考虑添加索引的场所
场景1:数据量庞大(需要测试,根据不同硬件环境不同分别处理)
场景2:被添加的字段经常当作限制条件添加到where后面,需要经常被扫描。
场景3:该字段很少的DML(insert delete update)操作,原因:增删改之后需要重新排序。
建议:不要添加过多索引,过多反而降低系统的性能,建议通过primary key 以及 unique使用
1.4索引的创建与删除: index
创建:
语法:create index ___(索引名) on ___ (___) 表名(字段名)
案例:mysql> create index emp_ename_index on emp(ename);
删除:
语法:drop index ____索引名 on ___表名;
案例:mysql> drop index emp_ename_index on emp;
1.5查看数据库中是否使用了索引检索表格
explain
案例:explain select * from emp where ename = 'king';
结果1:
type---ALL (未加索引,检索了全部)
type---ref (加了索引,检索一条)
1.6索引的失效
情景1:like '%T',模糊匹配中以%开头了,原因不知道第一个字符是什么。
优化,尽量避免模糊查询当中以%开头。
情景2:or,使用or的情况时,两边的字段都添加了索引才会通过索引查询,否则失效。
优化,尽量少用or比如union
情景2:使用复合索引时,没有使用左侧列查找,索引失效。
复合索引:两个或多个索引联合添加一个索引,叫做复合索引;
情景3:复合索引
情景4:where列当中参加了运算
注意是字段参加了运算而不是其中的值;
情景5:在where当中索引列使用函数;(单行处理函数)
情景6:。。。。。
1.7索引是各种数据库进行优化的的重要手段,优化时优先考虑的因素就是索引;
单一索引;一个字段上添加索引
复合索引;多个字段联合添加索引
主键索引;主键上添加索引
唯一性索引;unique字段上添加索引
注意:唯一性比较弱的字段上添加索引用处不大
二、视图view
2.1定义:不同角度看同一份数据
2.2如何创建,删除
create view (视图的名字) as select * from emp;
cerate view view_emp as select * from emp;
案例:复制表,创建视图,删除视图;
create table dept2 as select * from dept;
create view dept2_view as select * from dept;
drop view dept2_view;
注释:创建视图的语句当中后面跟的一定时dql语句;
2.3用途
特点:面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作
不同于表复制,操作视图其关联的原表会被改动
2.3.1操作
//面向视图查询
select * from dept_view;
//面向视图插入
insert into dept2_view ()value();
//面向视图删除
delete from dept_view;
//查询原表数据
select * from dept2;
//创建视图对象
错误操作:
create view
emp_view,dept_view
as
select
*
from
emp
join
dept
on
emp.deptno=dept.deptno
正确操作:
create view
emp_dept_view
as
select
*
from
emp e
join
dept d
on
e.deptno=d.deptno;
//面向视图更新
update emp_dept_view set sal =1000 where dname ='king';
注释:原表数据被更新
2.3.2用途:简化sql语句
sql的查询可能很长且复杂,需要多次使用,利用命令一个新的对象使用视图对象,且便于修改,修改视图对象中所反映的sql语句
注释:视图存储在硬盘而非内存,关机重启后不会消失
视图对应的只能是sql语句,但可以对视图后期进行增删改查等操作
插曲:术语CRUD,程序员当中沟通的术语,代表增删改查。
C:create(增)
R:retrive(查)
U:update(改)
D:delete(删)
三、DBA常见操作(未完待补充)
3.1导入导出(数据的备份)
导出数据库:
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p13008172713Z.R
导出数据库指定的表:
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p13008172713Z.R
数据的导入:
source D:\bjpowernode.sql
四、数据库设计三范式(面试)
4.1定义:数据表的设计依据,设计表的基础
第一范式:每一张表都有主键,且每一个字段都呈现原子性不可再分。
第二范式:建立在第一范式的基础之上,要求每一个非主键字段都完全抵赖于主键,不要产生部分依赖;
第三范式:建立在第二范式的基础之上,要求每一个非主键字段都直接依赖于主键,不要产生传递性依赖;
注释:所设计的数据表出现数据冗余,造成空间的浪费;
4.2第一范式:
最核心重要的范式;
案例学生表;
t_student
----------------------------------------------------------------------------
编号(PK) name 联系信息
01 张三 zhangsan@123 13300921911
02 李四 lisi@12332 15213234421
03 王五 wangwu@123 16332343434
更改后
t_student
编号(PK) name 邮箱 电话
----------------------------------------------------------------------------
01 张三 zhangsan@123 13300921911
02 李四 lisi@12332 15213234421
03 王五 wangwu@123 16332343434
不符合原子性不可再分
4.3第二范式:
建立在第一范式的基础之上,要求所有非主键字段完全依赖,不产生部分依赖;
案例学生老师表:
学生编号 学生 老师编号 老师
-------------------------------------------------------
01 张三 101 赵老师
02 李四 102 王老师
03 王五 101 赵老师
04 赵六 102 王老师
修改满足范式1后
给学生编号号和老师编号添加复合主键
(学生编号 老师编号)pk 学生 老师
---------------------------------------------------------------
01 101 张三 赵老师
02 102 李四 王老师
03 101 王五 赵老师
04 102 赵六 王老师
修改满足范式2后
范例2:
学生与老师字段只依赖于复合编号中的一部分,依次为学生编号与老师编号,未产生完全依赖;
修改:
学生编号PK 学生
--------------------------
01 张三
02 李四
03 王五
04 赵六
老师编号PK 老师
---------------------------
101 赵老师
102 王老师
id号 学生编号FK 老师编号FK
----------------------------
1 01 101
2 02 102
3 03 101
4 04 102
口诀:多对多表的设计:多对多,三张表,两个外键
4.4第三范式:
学生班级表
学生编号pk 学生姓名 班级编号 班级
------------------------------------------------------
01 张三 101 1班
02 李四 102 2班
03 王五 101 1班
04 赵六 102 2班
解析:
符合第一范式,有主键,且完全依赖于主键原子性不可再分
符合第二范式,完全依赖于主键,不产生部份依赖
不符合第三范式,非主键字段具有传递性,
如:班级依赖于班级编号,传递到学生编号
修改满足范例3后:
学生编号pk 学生姓名fk
------------------------------
01 张三
02 李四
03 王五
04 赵六
班级编号 班级fk
------------------------
101 1班
102 2班
关系
口诀:一对多,两张表,多的表加外键
4.5总结表的设计:
口诀1:多对多,三张表,两个外键
口诀2:一对多,两张表,多的表加外键
口诀3:一对一,外键唯一
场景:一张表字段太多,太庞大,需要拆分
案例:用户信息表;
4.6最后嘱咐:
数据表的设计三范式是理论上的;
时间和理论有一些偏差。
最终的目的都是为了满足客户的需求,有时候会拿冗余换执行速度。
原因:sql语句中,表和表的连接次数越多,效率越低(笛卡尔积)
面试的时候回到现实,回归到客户
有的时候会拿数据冗余,来减少表的连接次数。这样做也是合理的,并且会降低开发人员的编写难度;
面试的时候可以带一些这样的话语,更容易打动面试官