数据库原理以及Mysql高级

最新推荐文章于 2022-10-03 08:00:00 发布

danyinhao

最新推荐文章于 2022-10-03 08:00:00 发布

阅读量656

点赞数

本文链接：https://blog.csdn.net/weixin_44717965/article/details/120252534

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库原理以及Mysql

一、数据库的三范式（摘自gitee-JavaGuide）
二、变量
三、数据库的视图
四、事务
八、JOIN连接种类
九、索引优化
十、慢查询日志
- 1、开启慢查询日志
- 2、分析和诊断sql
十一、锁
- 1、介绍
- 2、锁的优化

一、数据库的三范式（摘自gitee-JavaGuide）

1NF(第一范式)

属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求，也就是说关系型数据库中创建的表一定满足第一范式。

2NF(第二范式)

2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。

3NF(第三范式)

3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 ,姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求。

一些重要的概念

函数依赖（functional dependency）：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
部分函数依赖（partial functional dependency）：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；
完全函数依赖(Full functional dependency) ：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；
传递函数依赖：在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X。传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系 R(学号 ,姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

二、变量

1、系统变量

会话变量：session，仅仅针对当前连接有效
全局变量：global，服务器每次重启将会赋值，可以跨连接，不会跨重启
SHOW GLOBAL||SESSION VARIABLES;
查看某部分的变量 SHOW GLOBAL VARIABLES LIKE ‘%char%’;
查看具体的变量的值 SELECT @@global||session.系统变量名;
对某一个系统变量赋值 SET @@global||session 系统变量名 = 值; ser global||session 系统变量名 = 值; 如果是全局，需要加global,如果是会话，可省略。

2、自定义变量

用户变量：针对当前会话连接有效,可用于任何地方
声明并初始化：SET @用户变量名=值; SELECT @用户变量名=值;
赋值：SET @用户变量名=值; SELECT @用户变量名=值; SELECT 字段 INTO @变量名 FROM 表;
查看变量值：SELECT @变量名

3、局部变量：仅仅在局部有小，定义在begin end中有效,只能在begin end中第一句声明比初始化

declare 变量名类型;
declare 变量名类型 default 值;
赋值：SET 局部变量名=值; SET 局部变量名:=值; SELECT @局部变量名=值; SELECT 字段 INTO 变量名
查看：select 局部变量名

三、数据库的视图

1、视图的创建和修改

create or replace view 视图
as 
查询语句
[with check option]; #with check option 表示对视图进行更新、插入、删除时必须进行合法性检查
alter view 视图
as 查询语句
[with|cascaded|local|check option];

2、删除视图

drop view 视图名,视图名....

3、视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的

包含以下关键字的sql语句：分组函数、distinct、group by、 having、join、union或者union all；
常量视图；
Select中包含子查询；
from一个不能更新的视图；
where子句的子查询引用了from子句中的表；

4、视图的作用

安全作用，可以隐藏数据。
可是复杂的查询易于理解和使用。
使sql语句得到重用。

5、视图和表的区别和联系（摘自其他博客，出处找不到了）

视图是已经编译好的sql语句。而表不是
视图没有实际的物理记录。而表是内容，视图是窗口
表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
表是内模式，视图是外模式
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用接触数据表，从而不知道表结构
表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表
视图的建立和删除只影响视图本身，不影响对应的基本表。

四、事务

1、事务的特性

原子性：一个事务不可再分割
一致性：一个事务可以从一个一致状态切换到另一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事物一旦提交，就会永久改变

2、事务的创建

隐式事务：事务的开启和结束没有明显的标记。例如：insert, update, delete 语句
显示事务：必须先设置自动启动事务功能为禁用
查询事务是否开启自动提交：SHOW VARIABLES LIKE ‘autocommit’
关闭事务的自动提交：SET autocommit = 0
创建事务

# 开启事务
SET autocommit = 0;
START TRANSACTION;
UPDATE account SET balance = 5500 WHERE username = '张无忌';
UPDATE account SET balance = 15500 WHERE username = '赵敏';
ROLLBACK;# 回滚
COMMIT;# 提交

3、事务并发的问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段，之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

4、事务的隔离级别

一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
数据库提供的 4 种事务隔离级别
Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
查看当前的隔离级别: mysql8以上的版本使用 SELECT @@TRANSACTION_ISOLATION ，mysql8以下版本使用 SELECT @@tx_isolation
设置当前 mysql 连接的隔离级别: set transaction isolation level read committed
设置数据库系统的全局的隔离级别: set global transaction isolation level read committed
mysql默认的隔离级别为REPEATABLE READ，可重复读，会出现幻读
使用隔离级别read uncommitted，出现脏读、不可重复读、幻读
隔离级别为read committed，事务之间能够读取其他事务已经提交的数据，解决了脏读，但会出现不可重复读
隔离级别repeatable read，解决了不可重复读，但还是会出现幻读
隔离级别设置serializable，解决了脏读、不可重复读、幻读，但是效率低

八、JOIN连接种类

在这里插入图片描述

A、B两表共有

select * from t_emp a inner join t_dept b on a.deptId = b.id;

A、B两表共有+A的独有

 select * from t_emp a left join t_dept b on a.deptId = b.id;

A、B两表共有+B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id;

A的独有

select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;

B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;

AB全有

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
# 这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。

A的独有+B的独有

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

九、索引优化

1、简介

索引可以理解为——排好序的快速查找的数据结构
索引一般来说也是一种数据，不可能全部存在内存中，因此往往以索引文件的形式存储在硬盘上
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索(hashindex)等。
通过建立索引，可以降低排序的成本，提高检索的效率
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

2、简单介绍几种索引

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引，修改逐渐索引前必须先删除原索引，在建立新的索引
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：建立唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。
复合索引：即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

3、以下情况适合建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中与其它表关联的字段，外键关系建立索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
单键/组合索引的选择问题，(在高并发下倾向创建组合索引)

4、以下情况不适合建立索引

表记录太少
经常增删改的表
Where条件里用不到的字段不创建索引
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

5、sql语句分析explain

在这里插入图片描述

id取值
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
type取值
- system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
- const 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量
- eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体，这里与eq_ref区分开，eq_ref条件匹配的行只有一条数据
- range 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
- index index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
- all 将遍历全表以找到匹配的行
key：实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows：rows列显示MySQL认为它执行查询时必须检查的行数，行书越少越好
Extra常见取值
- Using filesort ：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”，会拖垮系统，应当尽量避免
- Using temporary ：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by，会拖垮系统，应当尽量避免
- USING index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，**效率不错！**如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
- Using where：表明使用了where过滤

6、单表条件查询(where)如何避免索引失效

尽量使用全局匹配，顺序使用索引建立的列
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
is not null 也无法使用索引,但是is null是可以使用索引的
like以通配符开头(’%abc…’，%abc%)mysql索引失效会变成全表扫描的操作，但是abc%可以使用索引
字符串不加单引号索引失效
少用or,用它来连接时会索引失效
总结: 假设index(a,b,c)，以下where条件使用哪一列的索引

Where语句	索引是否被使用
where a = 3	使用到a
where a = 3 and b = 5	使用到a，b
where a = 3 and b = 5 and c = 4	使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	第一个索引 a 列不能丢，索引失效
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b后断了
where a = 3 and b like ‘kk%’ and c = 4	使用到a,b,c，由于开头是定值kk，因此kk%不算范围
where a = 3 and b like ‘%kk’ and c = 4	只用到a
where a = 3 and b like ‘%kk%’ and c = 4	只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	使用到a,b,c ，由于开头是定值k，因此k%kk%不算范围

7、多表连接查询如何避免索引失效

保证被驱动表的join字段，也就是连接字段，已经被索引
left join 时，选择小表作为驱动表建立索引，大表作为被驱动表
inner join 时，mysql会自己帮你把小结果集的表选为驱动表
子查询尽量不要放在被驱动表，有可能使用不到索引

8、order by如何避免索引失效

ORDER BY 语句使用索引最左前列
使用Where子句与Order BY子句条件列组合满足索引最左前列
where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效
另外，group by 避免索引失效的方式跟 order by 相似

十、慢查询日志

1、开启慢查询日志

# 查询慢查询日志是否已开启
SHOW VARIABLES LIKE '%slow_query_log%';
# 打开慢查询日志
set global slow_query_log=1;
# 查询当前的查询时间多少算慢
SHOW VARIABLES LIKE 'long_query_time%';
# 设置查询时间为1s，修改后可能没有改变，这时可以重新打开一个会话来修改值，或者使用以下命令：set session long_query_time=1
set global long_query_time=1
# 查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%';