数据库（事务，锁，SQL优化索引）

最新推荐文章于 2024-07-05 17:52:41 发布

miroCyber

最新推荐文章于 2024-07-05 17:52:41 发布

阅读量503

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/miroCyber/article/details/105127320

版权

事务的概念

什么是事务？
一个数据库事务通常包含了一个序列的对数据库的读/写操作。
（数据库事务，是指对数据库的相关增删改查的操作，要么完全地执行，要么完全地不执行。）

事务的作用：
为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务四大特性
原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

当事务完成时,数据必须处于一致状态。比如银行转账，A账户转到B账户，不管转几次，A和B账户的总额不能变。

隔离性（Isolation）

是指多个用户同时请求数据库，开启多个事务同时处理某个数据库，隔离性保证了各个事务之间均不受干扰，每个事务都感觉不到其他事务的存在。

持久性（durability）

事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，就算数据库系统出现故障，这种修改也不会丢失。

事务的隔离级别

为什么要隔离？
存在大量并发操作数据库时候防止操作互相干扰，所以要隔离

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

丢失更新：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。

数据库锁

为什么要有数据库锁？
当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

1、乐观锁
乐观锁(Optimistic Lock), 顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制.乐观锁适用于多读的应用类型,这样可以提高吞吐量。
在这里插入图片描述

2、悲观锁
悲观锁(Pessimistic Lock), 顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会 block 直到它拿到锁.传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁.

在这里插入图片描述
总结：
两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量.但如果经常产生冲突,上层应用会不断的进行重试,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适.

SQL优化

MYSQL索引
索引是帮助MySQL高效获取数据的数据结构
（可以理解为排好序的快速查找数据结构）

作用：提高数据检索的效率，降低数据排序的成本

图解原理
在这里插入图片描述
如果要找到29.首先把磁盘块1加载到内存，在内存中用二分查找确定29 在17到35之间，找到指针P2。P2的引用指向磁块3，
因为29在26到30之间，指针P2指向磁块8，同时用二分查找找到29。

索引分类：
单值索引：即一个索引只包含单个字段
唯一索引：索引字段的值必须唯一（主键）
复合索引：一个索引包含多个字段

添加索引的方法：
在这里插入图片描述

MySQL Explain详解

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

那些情况适合创建索引？
1.主键自动建立唯一索引
2.频繁作为查询条件的字段
3.查询中与其他表关联的字段，外键建立索引

不适合创建索引的情况
1.频繁更新的字段
2.where条件里用不到的字段

explain包含的字段

id
//这是SELECT的查询序列号我的理解是SQL执行的顺序的标识，SQL从大到小的执行
select_type
//查询中每个select子句的类型
table
//显示这一行的数据是关于哪张表的
type
//访问的类型。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好，注意:一般保证查询至少达到range级别，最好能达到ref。）
possible_keys
//指出MySQL能使用哪个索引在该表中找到行（不是实际的索引）
key
//实际使用到的索引，必然包含在possible_keys中
key_len
//表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref
//显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows
//根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
Extra
//该列包含MySQL解决查询的详细信息

注意： extended关键字：仅对select语句有效

案例一

题目
在这里插入图片描述

在这里插入图片描述
查询优化出现ALL全表扫描出现filesort 文件内排序需要优化

在这里插入图片描述
where category_id AND comments > 1 ORDER BY views DESC LIMIT 1
WHERE后面的三个字段(category_id ,comments , views ) 建立一个复合索引 CCV

查询优化 TYPE出现了range范围出现filesort 文件内排序需要优化

删除掉索引
删除上一个复合索引，重新建立一个索引CV
在这里插入图片描述
查询优化 TYPE为ref，（possible keys）与实际（key）相统一用到了索引，没有内排序，还挂了一个常量（const）。
这样就优化好了！！！

补充：一般问到 SQL优化先说缓存再说索引
缓存有mybatis自带的缓存还有redis缓存。
redis缓存这篇文章有讲到https://blog.csdn.net/miroCyber/article/details/105119862

说一下mybatis缓存

mybatis相关配置

mybatis:
  configuration:
      #开启MyBatis的二级缓存
      cache-enabled: true

Mapper文件上加注解@CacheNamespace注解：

@Mapper
@Repository
@CacheNamespace
public interface UserMapper {
 
    @Select("SELECT ID,NAME,AGE,PASSWORD FROM USER WHERE ID = #{id}")
    User selectById(String id);
 
}

注意：
使用mybatis二级缓存的缺陷：（一般情况下不使用mybatis的二级缓存，使用redis等替代）
分布式环境下必然会出现脏数据；
多表联合查询的情况下极大可能会出现脏数据；

miroCyber

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
数据库（事务，锁，SQL优化索引）

事务的概念什么是事务？一个数据库事务通常包含了一个序列的对数据库的读/写操作。（数据库事务，是指对数据库的相关增删改查的操作，要么完全地执行，要么完全地不执行。）事务的作用：为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。事...
复制链接

扫一扫