狂人日记day11

狂人日记day11

事务

业务逻辑

假设从a到b转账1000

a减去1000

b加上1000

完整的业务

要么同时成功 要么同时失败

dml语句

• insert
• delete
• update

要考虑安全问题

多个事务共同完成

• 提交事务

成功的事务

• 回滚事务

失败的事务

提交：Transaction

回滚: rollback

回滚只能回到上次提交点

Start transaction;

Rollback;//回滚事务

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yOJcwwdt-1621656868173)(/Users/suannai/Library/Application Support/typora-user-images/image-20210521145155890.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3RC9Apjv-1621656868174)(/Users/suannai/Library/Application Support/typora-user-images/image-20210521145228999.png)]

commit//提交事务

事务特性

• 原子性

  原子最小不能再分

• 一致性

同时成功，同时失败

• 隔离性

a与b有一定的隔离

• 持久性

  事务提交的保障

事务的隔离性

• 读未提交 read uncommitted

  set global transaction isolation level read uncommitted;

  事务a可以读取到事务b未提交的数据

• 读已提交 read committed

set global transaction isolation level read committed;

• 可重复读 repeatable read

set global transaction isolation level repeatable read;

• 序列化读 serializable

set global transaction isolation level serializable;

查看当前会话隔离级别

SELECT @@tx_isolation

索引

索引在数据库的字段上添加，为了提高查询效率

目的：缩小扫描范围

索引需要排序

mysql 中索引是一个（自平衡二叉树：B-tree）

任何数据库 主键都自动添加索引

任何数据库的任何表的任何记录 在硬盘上都有物理存储编号

主键和unique 都会自动添加索引

条件：

• 数据庞大
• 条件经常出现在where后面
• 改字段很少出现dml操作

不要随意添加索引

创建索引：

create index emp_ename_index on emp(ename);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NryURTfG-1621656868175)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522104621361.png)]

给 emp的name 起名 叫 emp_ename_index

删除索引：

drop index emp_ename_index on emp;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BgKfxqlE-1621656868175)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522104825844.png)]

查看是否sql是否使用了索引

explain select * from emp where ename = ‘KING’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a5bVZVLW-1621656868175)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522105255900.png)]

扫描14条 说明没使用索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YotZgHIo-1621656868176)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522105304594.png)]

扫描1条，加上了索引

索引的失效：

select * from emp where ename like ‘%T’

即使添加索引也不走索引，因为用%开头了，尽量模糊查询时用%开始

explain select * from emp where ename like ‘%G’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MnMgYiEu-1621656868176)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522105845453.png)]

失效的第二种情况：

使用or的情况 如果or两端都有索引才会走索引

建议 ：少用or

explain select * from emp where ename = 'KING ’ or job = ‘MANAGER’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IyQa554i-1621656868177)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522110049845.png)]

失效第三种情况：

使用复合索引时 没有使用左侧的列查找 索引失效

复合索引：

​ 两个字段或者更多字段联合起来添加一个索引，叫做复合索引

create index index_job_sal on emp(job,sal);

explain select * from emp where job =‘MANAGER’

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QMPwBRmc-1621656868177)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522110348110.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Tohg5grc-1621656868177)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522110426564.png)]

索引失效

第四种情况：

在where中参与了运算，索引失效

create index index_sal on emp(sal);

explain select * from emp where sal = 800;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gLLkBCqg-1621656868177)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522110703236.png)]

explain select * from emp where sal +1 =800;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zSs2H3HY-1621656868178)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522110631066.png)]

失效的第五种情况：

在where中 索引列使用了函数

explain select * from emp where lower(ename) = ‘jack’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fg0LlR4n-1621656868178)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522110854530.png)]

第六种情况：

类型转换

索引分类

进行优化的重要手段，优化的时候优先考虑的因素就是索引

试图：

站在不同的角度 去看同一份数据

创建视图

create table dept2 as select * from dept;

create view emp_view as select * from dept2;

Drop view emp_view;

只有dql语句才能以view创建

试图作用 ：

对试图对象的增删改查，会导致原表被操作

insert into emp_view(deptno,dname,loc) values (60,‘sales’,‘beijing’);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cickT5NQ-1621656868179)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522112042242.png)]

实际开发中的作用：

• 简化sql语句

视图的语句只能dql

但视图创建完成后可以对视图进行增删改查操作

增删改查：crud

DBA 常用命令

重点掌握数据导入导出（数据的备份）

数据导出

在terminal中

alias mysql=/usr/local/mysql/bin/mysqldump - uroot -proot123 bjpowernode >suannai:bjpowernode.sql

可以导出指定的表

alias mysql=/usr/local/mysql/bin/mysqldump - uroot -proot123 bjpowernode emp >suannai:bjpowernode.sql

注意这里面设置了连接 同时解答了 开始mysql启动不了的问题 应该为 目录设置问题 用连接启动

导入先登陆mysql后

Source + 文件

数据库设计三范式

第一范式：

• 任何一张表必须有主键，每个字段原子性不可再分

第二范式：

• 所有非主键字段完全依赖主键，不产生部分以来，在第一范式之上

第三范式

• 非主键字段直接依赖主键，不产生传递依赖，在第二范式之上

作用： 避免空间浪费

第一范式：

核心

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fw9hPrpb-1621656868179)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522114547125.png)]

第二范式：

建立在第一范式的基础之上

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JodjJPAw-1621656868179)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522114933519.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fmTz6OF4-1621656868180)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522115322945.png)]

多对多怎么设计：

​ 多对多 三张表 关键表两个外键

第三范式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Uh33F7dK-1621656868180)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522115851680.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VhT8ittY-1621656868180)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522115936300.png)]

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

一对一 如果表过于庞大 建议拆分表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mucjvuTn-1621656868180)(/Users/suannai/Library/Application Support/typora-user-images/image-20210522120725109.png)]

总结：

• 多对多 三张表 关键表两个外键

• 一对多，两张表，多的表加外键

• 一对一 拆分表 外键唯一

实际为了满足客户需求 有时会用冗余换速度

连接次数越多 效率越低

对于开发人员 sql编写难度也会降低

剩下就是作业题