事务&MySQL相关

1、事务

事务是一组完整的逻辑操作

事务可以使一组逻辑操作要么全部成功，要么全部失败，保证数据的一致性

1、作用

事务能保证我们规定的那组逻辑操作要么全部执行成功，要么全部执行失败，回滚到事务执行之前的状态，能保证数据的一致性。就是事务如果没有执行成功，就不会修改数据库的数据，还有事务执行成功后，对数据库的更新是永久性的。

1. 提高性能：通过使用事务，可以避免一些不必要的锁操作，提高数据库的性能。

1、基本特性、三个问题

事务有ACID四大特性

原子性是指当前事务的操作要么全部成功，要么全部失败，回滚到事务开始之前的状态。

一致性指事务执行前后数据库的完整性没有被破坏，就是写入的数据必须完全符合所有的预设约束、触发器、级联回滚等。

隔离性是指并发环境中多个事务可以同时操作同样的数据，且互不干扰，但会产生脏读、不可重复读和幻读的问题，所以又设置了四种隔离级别，由低到高分别是读未提交，读已提交，可重复读，串行化，但也不是越高越好，比如串行化相当单线程，一个事务执行期间，禁止其他事务对这个表进行增删改操作，虽然解决任何并发问题，但性能十分低下，mysql的默认隔离级别是可重复读，oracle默认是读已提交

最后一个特性是持久性，就是事务执行结束后，对数据库中数据的修改时永久的

1、 脏读就是一个事物读到了其他事物更新但是还没有提交的数据就是脏读）

2、不可重复读就是事务a第一次读取了一个数据后其他事务在此期间修改了这个数据，事务a再次读取发现跟第一次读取到的不一样。

3、幻读就是事务在查询同一范围的数据时，会有不一样的结果。比如事务a对某张表的所有数据进行了某一字段的修改，同时又有其他事务往这个表添加了新的数据，事务a回过头来看发现还有一行数据没有修改，就跟幻觉一样。一般解决幻读的方法是增加范围锁RangeS，锁定检索范围为只读，这样就避免了幻读。或者提高隔离级别、事务控制

2、事务注解参数、失效场景

【事务】@Transactional 注解参数详解-CSDN博客

1.1、isolation（事务隔离级别）1.2、propagation（事务传播机制）1.3、readOnly（事务读写性）

（那两个关于回滚的）

1.4、noRollbackFor 和 noRollbackForClassName（遇到时不回滚）1.5、rollbackFor 和 rollbackForClassName（遇到时回滚）1.6、timeout（超时时间）1.7、value（指定使用的事务管理器）

注解失效场景：

将异常使用catch捕获时失效，因为会使程序继续向下执行，回滚失败。第二是同一类中未添加注解的方法调用添加了注解的方法会导致事务失效，因为Spring事务是基于aop的，只要使用代理对象，去调用某个方法的时候呢，Spring事务它才会生效，而在一个方法中，直接去调用，其实是相当于是用this对象去调用的，并不是用那个代理对象去调用它，解决方法是注入一个Service，用这个service去调用。第三种是使用在非public方法上，因为不会获取注解的属性配置信息，也是跟那个动态代理有关（这个好像还挺复杂的，我没仔细研究过）。第四种是属性设置错误，第五种是数据库引擎本身不支持事务

@Transactional 应用在非 public 修饰的方法上，不支持回滚；@Transactional 注解属性 propagation 设置错误；@Transactional 注解属性 rollbackFor 设置错误；在同一个类中方法调用，导致 @Transactional 失效；异常被你的 catch 处理了，导致 @Transactional 没办法回滚而失效；数据库配置了不支持事务的引擎，或者数据库本身就不支持事务。

3、如何开启事务？

在配置类中添加@EnableTransactionalManagement开启springboot环境下的事务管理，然后在有需要下的方法前加上@Transactional注解就行。

4、事务传播机制

propagation

需要保证数据一致性的场景下可以选择required，在需要高性能的场景下可以选择supports或requiresNew。

1. required：第一种是如果当前（正在被调用的）方法有事务，那就用这个事务，没有就创建（用于方法里有更新操作的 ）不管嵌套的service层方法是否有事务（嵌套的方法发生的运行时异常，也属于当前方法的一部分，所以当前方法的sql全部回滚）。

2. supports：第二种跟第一种的差别就是如果没有事务就算了，也不创建事务（查询操作）

3. mandatory：第三种也是用当前的事务，如果没有，就直接抛出异常。

4. requiresNew：第四种如果当前方法有事务，但我们不去用这个事务，而是新建一个事务。

5. 第五种是不管有没有事务，我都要以非事务的形式执行，有事务就都挂起来

6. 第六种就是有事务直接抛出异常

7. 第七种是以嵌套的形式。

5、隔离级别

由低到高分别是，呃

第一个是读未提交，就是事务可以读取其他事务未提交的数据修改。什么问题都解决不了

第二是读已提交，事务只能读取已经提交的数据修改。能解决脏读

第三是可重复读，这个能确保事务每次读取到的同一个数据的值是相同的，就是能够在事务执行时禁止其他事务对数据进行修改。能解决脏读和不可重复读，其实在刚学的时候我是这么认为的，但是最近看了那个MVCC后，发现他也能解决幻读的问题。（他分两种，一种快照读和当前读，快照读能解决幻读的问题，能理解成就是在事务启动的时候对数据库拍了个“快照”，它保留了那个时刻数据库的数据状态，然后这个事务以后再次读取数据的时候就从这个“快照”中获取，哪怕其他事务新加了数据，也不会影响到“快照”中的数据，也就不会出现幻读了。）

最后一个串行化，这个级别能避免任何并发问题（禁止其他事务在当前事务执行期间对这张表进行增删改操作），不过性能十分低下，mysql默认使用可重复读，orcal是读已提交。

2、MySQL相关

1、存储过程和视图

存储过程是一段预先编写好的SQL代码的集合，用来提高性能的，因为他可以减少网络传输，降低网络负载，只在创建时进行编译，以后就不会再重新编译了，而SQL一般都是每执行一次就编译一次，所以，可以提高数据库执行速度

而视图就是一个虚拟表而已，就是没有真的去存到磁盘上，表就是真的存在磁盘上。视图作为查询的结果展示

2、sql注入及#{}、${}

sql注入是一种常见的web安全漏洞，攻击者可以构造恶意的sql语句来对数据库进行非法操作或者获取敏感信息。

我最常见的是是使用单引号和or关键字，加一个必定成立的条件拼接到sql中，这样就能强迫该sql语句永远是正确的，也不能说是正确，反正就是能达到一种目的，比如条件查询，硬生生变成了相当于没有条件。本来只能查一条数据，现在全部都查出来了。

如何避免？使用预编译语句，在编写sql语句的时候尽量不使用$符，$符（$()）为字符串替换，相当于sql拼接；而#为参数占位符，就是sql预编译。还有在代码开发过程中要对用户输入的数据进行严格的校验和过滤，确保符合预期格式和类型。

#{} 为参数占位符 ?，即sql 预编译，#{} 能防止sql 注入，变量替换后，#{} 字符串对应的变量自动加上单引号’’

$为字符串替换，即sql拼接，${} 不能防止sql 注入，变量替换后，${} 字符串的变量不会加上单引号’’

3、模糊查询

1、LIKE’Mc%’ 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。2、LIKE’%inger’ 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。3、LIKE’%en%’ 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。4、LIKE’_heryl’ 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。

4、数据库表连接

表连接分为内连接和外连接，内连接返回两个表中共同满足连接条件的行，也就是两张表的交集。左连接从左表中选取所有数据，然后与右表中匹配的数据进行连接，如果右表中没有匹配数据，则结果中的右边部分为空。右连接则相反。

5、select,from,where,group by,having,order by执行顺序

首先是from,选择是哪些表，然后用where进行初步条件筛选，再对筛选结果进行分组，having就是对分组的结果再次进行筛选，都筛选完了就可以用Select获取数据了，最后再对数据用order by进行排序。排序完后才到limit限制结果集

6、数据库存储引擎

InnoDB支持事务，也能对表中的某一行进行锁定，还有外键约束。MyISAM除了不支持事务，还是表级锁。不过InnoDB占用的内存比MYISQM大。一般来说，innodb适合处理有很多事务和高并发的场景，myisam更适合多度的场景。

1. 索引：InnoDB 支持 B-Tree 索引和哈希索引，不支持全文索引。MyISAM 支持 B-Tree 索引、全文索引，但不支持哈希索引。
2. 崩溃恢复：InnoDB 具有自动崩溃恢复的功能，这意味着在 MySQL 崩溃后，它可以自动恢复数据。而 MyISAM 不具备这种能力。
3. 内存使用：InnoDB 占用空间是 MyISAM 的 2.5 倍。

7、增删改sql

– 1)新添加16号员工的信息，其它字段值自定INSERT into t_employee (emp_id,name,sex) values (16,‘孙小美’,‘女’)

– 2)删除1950年以前的员工信息DELETE FROM t_employee WHERE YEAR(birthday) < 1950

– 3)工资的调整： – a)1 ~ 5号员工，基本工资增加200UPDATE t_salary SET base_salary = base_salary + 200WHERE emp_id >=1 and emp_id <= 5

8、插入一条数据如何返回这条数据的主键？

将useGeneratedKeys设为true，keyProperty指定将该主键赋值到实体类的哪个字段中。表示使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的模型属性中。

9、索引层级

1. 表级索引：也称为全局索引，它覆盖整个表，可以被所有查询访问。
   

2. 分区级索引：也称为局部索引，它覆盖一个或多个分区，只被某些查询访问。
   

3. 列级索引：它覆盖一个列或多个列，可以被所有查询访问。
   

4. 行级索引：它覆盖一行或多行，只被某些查询访问。
   

5. 键级索引：也称为唯一索引，它覆盖一个键或多个键，可以被所有查询访问。
   

6. 组合索引：它覆盖多个列，并且可以被所有查询访问。
   

10、最左匹配原则

在联合索引中，如果SQL语句中用到了联合索引中的最左边的索引，那就可以用这个联合索引去进行匹配。换句话说，最左匹配原则是指，在查询中，只要使用了索引的最左边一列，那么就可以利用该索引进行匹配，而不必关心索引中其他列的顺序。

举个例子，假设有一个联合索引(a,b,c)，如果SQL语句是“SELECT * FROM table WHERE a = 1 AND b = 2”，那么这个SQL语句可以利用索引(a,b,c)进行匹配，而不必关心索引中c列的顺序。同时遇到范围查询(>、<、between、like)就会停止匹配。

优化SQL查询的性能，提高查询效率。

11、怎么发现索引失效

如果索引失效，结果不会显示索引信息

1. 使用EXPLAIN命令
2. 使用SHOW TABLE STATUS命令

12、索引回表

就是将索引中的数据记录下来

就是将索引中的数据按照某种规则进行分组，并将符合条件的数据记录下来，以方便查询。

13、聚簇索引和非聚簇索引

聚簇索引就是以主键创建的索引，每张表只能有一个，它是按照索引列的值进行物理存储的，也就是说，数据是按照索引列的值进行排序的，在查找时可以直接通过索引找到对应的数据，速度非常快。对应的插入和更新速度就比较慢了。

非聚簇索引就是以非主键创建的索引，每张表可以有多个，查询速度比聚簇索引慢，但插入和更新的时候更快

14、什么是B+树索引、好处？

B+树是一种平衡树，每个节点的左右子树的高度差不超过1，保证了整棵树的平衡性。非叶子节点存储的是键，叶子结点存储对应的值。

好处就是使得查询操作的时间复杂度保持在O(logn)，跟二叉树或平衡二叉树相比B+树的高度更低，提高数据检索的效率。而且他的叶子节点形成有序链表，就是当查找到叶子节点时发现不是想要的数据，可以通过这个链表直接遍历到其他的叶子节点，就不用再从根节点寻找。

支持范围查询和按顺序遍历。插入和删除操作也不需要重新构建整棵树。通过修改叶子节点的指针和分隔关键字，可以实现平衡树的调整，保持树的平衡性，提高数据的维护效率

B树（B-Tree）和B+树（B+ Tree）是常见的平衡查找树

• B树：B树的节点中既包含键值，也包含对应的数据，就是在叶子节点上同时存储键值和数据。

• B+树：B+树的节点中只包含键值，对应的数据存储在叶子节点上，所有叶子节点之间都有一个链指针。

1. 应用场景：

• B树适用于磁盘存储，能够快速定位到具体的数据。因此，在 索引比较短（比如整型）的情况下，B树的高度会相对较低，可以减少磁盘IO。
• B+树适用于内存存储和数据库索引。由于B+树的叶子节点之间使用链表连接，在范围查询和顺序遍历时具有较好的性能。

15、MySQL锁

\1. 乐观锁、悲观锁

\3. 全局锁：对整个数据库实例加锁，限制除了超级用户外的所有查询和修改操作。一般用于备份、恢复等操作。

\4. 表级锁：对整个表加锁，其他连接无法修改或者读取这个表的数据，还是可以对其他表进行操作的，没有全局锁那么狠

\5. 页级锁：对数据页（通常是连续的几个行）加锁，比表级锁更宽松一点，控制并发事务对该页的访问。适用于数据较大且并发量较高的场景。

\6. 行级锁：对单个行加锁，只锁定需要修改的数据行，其他行可以被同时修改或读取。并发性高，但锁管理较复杂。

\7. 共享锁：也称为读锁，多个事务可以同时持有共享锁并读取数据，但不能修改数据。适用于同时读取同一数据的场景。

\8. 排它锁：也称为写锁，事务持有排它锁时，其他事务无法同时持有共享锁或排它锁，用于保护数据的写操作。

\9. 意向共享锁：表级锁的辅助锁，表示事务要在某个表或页级锁上获取共享锁。

\10. 意向排它锁：表级锁的辅助锁，表示事务要在某个表或页级锁上获取排它锁。

间隙锁、临键锁和记录锁都能解决幻读的问题。

\11. 间隙锁可以锁定一个范围，在执行查询时可以防止其他事务插入或修改该范围内的数据

\12. 临建锁可以锁定一个记录的前一个键值

\13. 记录锁以锁定一个记录

16、数据库三范式

数据库三范式就是关系型数据库在设计表的时候要遵循的三个规范

第一范式具有原子性，指数据库的每一列都是不可分割的基本数据项。

第二范式具有唯一性，在第一范式的基础上，消除非主键依赖联合主键中的部分字段。

第三范式具有独立性，在第二范式的基础上，消除传递依赖，非主键不依赖于非主键，都应该依赖于主键。

但是有时候为了效率或者某些情况没法遵守，也可以违反三范式。

在第一个项目中，存储订单项的时候，如何遵守三范式的话，应该只需要存储订单id和商品id和一些必要的字段，但是商品的信息不是一成不变的，下单的时候可能是99块，明天可能就变成199了，下次查订单的时候就会发现数据不对，所以不得不违反三范式，把商品的当前信息都存入表中。查询的时候不去关联查询，直接从本表中提取就行