关系型数据库Mysql

最新推荐文章于 2024-08-14 23:11:00 发布

yucan1234

最新推荐文章于 2024-08-14 23:11:00 发布

阅读量242

点赞数

分类专栏：面视文章标签：数据库 mysql

本文链接：https://blog.csdn.net/yucan1234/article/details/108929848

版权

面视专栏收录该内容

11 篇文章 0 订阅

订阅专栏

存储过程

存储过程是具有某种功能的函数，函数可以带参数，也可以不带参数，不带参数的存储过程

create procedure addresult()
	begin
	declare i int;
	declare sum int;
	declare n int;
	set i=1;
	set sum=0;
	set n=10;
	while i<=n do
		set sum=sum+i;
		set i=i+1;
	end while;
	select sum;
	end;
call addresult;

我们可以在navicat中看到我们创建的函数列表
在这里插入图片描述

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT：

IN：参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
　　OUT：该值可在存储过程内部被改变，并可返回
　　INOUT：调用时指定，并且可被改变和返回

drop procedure if exists addresult;			//如果存在addresult则删除
create procedure addresult(in n int)
	begin
	declare i int;
	declare sum int;
	
	set i=1;
	set sum=0;
	while i<=n do
		set sum=sum+i;
		set i=i+1;
	end while;
	select sum;
	end;

call addresult(3);

优缺点

优点

封装了内部函数，隐藏复杂的逻辑
存储过程可以使用in传参，out回传值

缺点

存储过程支持的编程语言不同，切换到其他厂商的数据库系统需要重新编写原来的存储过程
存储过程的性能，调优，撰写等受限于各种数据库系统

三大范式

1、1NF:字段不可分，每个字段是原子级别的，上节中看到第一个字段为ID，它就是ID不能在分成两个字段了，不能说我要把这个人的ID、名称、班级号都塞在一个字段里面，这个是不合适的，对以后的应用造成很大影响；
　　2、2NF:有主键，非主键字段依赖主键，ID字段就是主键，它能表示这一条数据是唯一的，有的读者朋友记性很好，“unique”表示唯一的、不允许重复的，确实它经常会修饰某个字段，保证该字段唯一性，然后再设置该字段为主键；
　　3、3NF:非主键字段不能相互依赖，这个怎么理解呢，比如student表，班级编号受人员编号的影响，如果在这个表中再插入班级的班主任、数学老师等信息，你们觉得这样合适吗？肯定不合适，因为学生有多个，这样就会造成班级有多个，那么每个班级的班主任、数学老师都会出现多条数据，而我们理想中的效果应该是一条班级信息对应一个班主任和数学老师，这样更易于我们理解，这样就形成class表，那么student表和class表中间靠哪个字段来关联呢，肯定是通过“classNo”，这个字段也叫做两个表的外键；

SQL 语句的执行过程

在这里插入图片描述

视图

什么是视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

使用场景

由于视图隐藏了原表的查询语句，所以可以在需要隐藏其他数据的时候使用
查询语句非常的复杂，或者很多地方都用到了该查询语句

创建语法
Create view 视图名 as 查询语句
显示视图创建情况
show create view 视图名;
查看视图
Show tables；
Show table status [ from db_name ] [ like ‘pattern’ ]；
SELECT * FROM information_schema.views where table_name = 'my_view';
删除视图
drop view 视图名[,视图名…];
重命名视图
Rename table 视图名 to 新视图名;;

优缺点：

优点：

安全性
简化数据操作，提高操作效率
可以合并分离的数据，创建分区视图

缺点：

性能差
修改限制

MySQL explain

语法 explain +完整的sql语句

查询的结果有12种字段
在这里插入图片描述

字段解释：

Id
Sql的执行顺序的优先级，是相同，则从上到下依次执行
如果不相同，则先执行id大的
有过一条语句有子查询，id的序号会递增，所以子查询会先执行，在执行外层语句

select_type
每个查询字句的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table
这一步访问的数据库中表的名称，有可能是简称

type
找到所需行的方式，又称访问类型
ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

Possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

Key
实际决定使用的键(索引)，包含于possible_keys

Key_len
索引中使用的字节数，可以通过该列计算查询中使用的索引的长度(key_len的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的)
在不损失精度的情况下，长度越短越好

ref
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra
不用读取表的信息，仅通过索引就能获取到的数据

事务（不同存储引擎下）

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性： 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
　　一致性： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
　　隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
　　持久性： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的隔离机制

数据库系统必须具有隔离并发运行各个事务的能力，使他们互不影响，避免各种并发问题。一个事务与其他事务的隔离程度称为隔离级别。SQL标准中规定了多种事务隔离级别，不同的隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性就越弱。

读未提交 READ UNCOMMITTED
允许一个事务读取另一个事务没有提交的数据

读已提交 READ COMMITTED
要求一个事务只能读取其他事务已经提交的修改

可重复读 REPEATABLE READ
确保一个事务可以多次从一个字段中读取到相同的值，即这个事务执行期间禁止其他事务对这个字段进行更新

串行化 SERILIZABLE
确保一个事务可以从一个表中读取到相同的行，在事务A执行期间，禁止其他事务对这个表进行添加，更新，删除操作，可以避免发生任何并发问题，但是性能十分低

脏读，幻读，不可重复读

脏读
事务A将某条记录修改了值i为20，但是还没有提交
事务B读取了被事务A修改后的值i=20
事务A出现了异常进行回滚，将值改回了原来的10
事务B读取到了20就是一个无效的值，俗称脏数据
不可重复读
事务A读取i值为10
事务B将i的值修改为20，已经提交
事务A再次读取i的值为30
事务A两次读取到的数据不一样
幻读
事务A读取了表中的一部分数据
事务B向表中插入了新的行
事务A再次读取表的数据时，发现出现了新的行

MySQL 隔离机制的实现 MVVC

锁

表锁

行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。对行进行加锁，按照使用方式分为共享锁和排他锁
共享锁（S锁）：事务T只能对当前行进行读操作，其他事务也可以加S锁，但是不能加X锁。直到事务T释放锁

select ... lock in share mode;

排他锁（X锁）：事务T能对当前行进行读写操作，但是事务不可以加任何锁

select ... for update

行锁

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁
共享锁：

LOCK TABLE table_name [ AS alias_name ] READ

排它锁：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

解锁：

unlock tables;

死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。（不过现在一般都是InnoDB引擎，关于MyISAM不做考虑）
在InnoDB中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

乐观锁/悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持悲观态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）
在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。
悲观锁的优点和不足：
悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。
乐观锁：

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

乐观锁（ Optimistic Locking ）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。
乐观锁的优点和不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

索引

索引定义

官⽅介绍索引是帮助MySQL⾼效获取数据的数据结构。更通俗的说，数据库索引好⽐是⼀本书前⾯的⽬录，能加快数据库的查询速度。
⽅便查找—检索
索引查询内容—覆盖索引
⼀般来说索引本身也很⼤，不可能全部存储在内存中，因此索引往往是存储在磁盘上的⽂件中的
（可能存储在单独的索引⽂件中，也可能和数据⼀起存储在数据⽂件中）。

索引创建原则

（1）为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
在这些字段上建立索引，可以有效地避免排序操作。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
（2）在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
（3）为经常用作查询选择的字段，建立索引。
（4）在经常用作表连接的属性上，建立索引。
（5）考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

1、对于查询频率高的字段创建索引。
      索引能够提高查询效率，但是会降低插入的效率，如果一个表经常进行更新，那么建立索引反而会拖慢速度
2、对排序、分组、联合查询频率高的字段创建索引
      在这些字段上建立索引，可以有效地避免排序操作。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
3、索引的数目不宜太多
      简历索引会占用磁盘空间，而且对于更新表的时候，也会消耗更多时间
4、若在实际中，需要将多个列设置索引时，可以采用多列索引
5、选择唯一性索引
      唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
6、尽量使用数据量少的索引
      如果索引的值很长，那么查询的速度会受到影响
7、尽量使用前缀来索引
8、删除不再使用或者很少使用的索引
      定时删除不再需要的索引

索引的存储为什么使用 B+ 树？跟 Hash 索引和数组索引的对比

索引的类型（主键索引和非主键索引 / 聚簇索引和非聚簇索引）

聚簇索引
　　聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
优点：
　　1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
　　2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
　　1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
　　
辅助索引（非聚簇索引）
聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

索引优化

索引失效

自适应索引

覆盖索引

当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

最左前缀

创建了a_b_c多列索引,相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引

索引下推

select * from tuser where name like '张 %' and age=10;

如果没有索引下推，则会先找出名字以张开头的人，然后将这些为张开头的所有人，回表查询他们的age是否等于10
索引下推会在查找到每一个张开头的人，判断他们的属性age是否等于10

普通索引和唯一索引

1、普通索引
　　即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。
2、唯一索引
　　使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一。主键是一种特殊唯一索引。

change buffer 和 redo log

JDBC 中防止 SQL 注入

利用PrepareStatement代替Statement
PreparedStatement主要有如下的三个优点：
1.可以防止sql注入
2.由于使用了预编译机制,执行的效率要高于Statement
3.sql语句使用?形式替代参数,然后再用方法设置?的值,比起拼接字符串,代码更加优雅.

Group by 的实现

删表的几个操作和区别

Truncate 用于删除数据表中的数据，仅数据表中的数据，不删除表（ truncate table 数据表名称），释放空间，不写日志。
delete 删除数据表中的行，可以删除某一行，也可以在不删除数据表的情况下删除所有行（删除某一行：Delete from 数据表名称 where 列名称=值），删表是逐行删除，速度慢，不释放空间，写日志
drop 删除数据库（drop database 数据库名称）、删除数据表（ use 数据库名称 drop table 数据表1名称，数据表2名）或删除数据表字段（use 数据库名称 alter table 数据表名称 drop column 字段名（列名称）），删除数据和结构，释放空间
将删除表的结构、被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.

varchar 和 char

varchar与char两种字符型数据类型相比，最大的差异就是前者是可变长度，而后者则是固定长度。
在存储时，前者会根据实际存储的数据来分配最终的存储空间。而后者则不管实际存储数据的长度，都是根据CHAR规定的长度来分配存储空间。

数据库优化

超键、候选键、主键、外键

键：在数据库中关键码（简称，键）由一个或多个属性组成。在实际应用中，有下列键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键
候选键：不含有多余属性的超键称为候选键
主键：用户选作元组标识的候选键称为主键
外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

关联查询

left join（左连接）

right join（右连接）

inner join（内连接）

outer join（外连接）

full join（全连接）

yucan1234

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关系型数据库Mysql

存储过程三大范式SQL 语句的执行过程视图什么是视图使用场景优缺点MySQL explain分析字段事务（不同存储引擎下）事务的隔离机制脏读，幻读，不可重复读MySQL 隔离机制的实现 MVVC锁表锁行锁死锁乐观锁/悲观锁索引索引定义索引创建原则索引的存储为什么使用 B+ 树？跟 Hash 索引和数组索引的对比索引的类型（主键索引和非主键索引 / 聚簇索引和非聚簇索引）索引优化索引失效自适..
复制链接

扫一扫