04 数据库及其事务

1. 事务的四个特性

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

2. 四个隔离等级及其可能引起的问题

2.1 四个隔离等级

1. 读未提交：可能导致脏读
2. 读已提交：导致不可重复读；Oracle、Sql Server默认
3. 可重复读：导致幻读；MySql默认
4. 序列化：效率很低，但不会出错

2.2 脏读、不可重复读、幻读

1. 脏读：假如事务A开始执行更新，同时B也开始执行查询，在A更新之前，B读到了还没有更新的数据，此时就是脏读。由于A还没有提交，B读到的属于脏数据，此时就是脏读
2. 不可重复读：事务A中要进行两次读取同一条数据的操作，A执行完第一次查询后，B开启事务，开始更新这条数据，导致A第二次读到的数据跟第一次读到的数据不一样，这就是不可重复读。不可重复读对应的是更新update的操作。读已提交是只能读到提交后的事务，A的第二次查询必须要等到B更新的事务提交后才能执行
3. 幻读：事务A要读取两次同一范围内的数据，A读完第一次之后，B往里插入或者删除了几条数据，导致A读到的记录数与第一次不一样，这就导致了幻读。幻读对应的是插入insert或者删除delete的操作，多出来或者少的那些行记录叫做幻行

3. 事务的七种传播行为

1. REQUIRED ：如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务；这个是Spring默认的。
2. SUPPORTS：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。
3. MANDATORY ：如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。
4. REQUIRES_NEW ：创建一个新的事务，如果当前存在事务，则把当前事务挂起。
5. NOT_SUPPORTED ：以非事务方式运行，如果当前存在事务，则把当前事务挂起。
6. NEVER ：以非事务方式运行，如果当前存在事务，则抛出异常。
7. NESTED ：如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于 REQUIRED

4. 数据库设计三大范式和六大约束

4.1 三大范式

1. 第一范式：确保每列保持原子性，即列不可再分
2. 第二范式：确保表中的每列都和主键相关，即每列完全依赖主键而不是依赖主键的一部分，主要针对联合主键的情况
3. 第三范式：确保表中的每列都和主键直接相关，不能存在依赖传递，表中的某一列依赖主键，同时又有别的几列依赖该列

4.2 六大约束

1. 主键约束：PAIMARY KEY
2. 外键约束：FOREIGN KEY
3. 唯一约束：UNIQUE
4. 非空约束：NOT_NULL
5. 默认约束：DEFAULT
6. 检查约束：CHECK

以上部分详情可见自己整理的：sql专栏中的内容

https://blog.csdn.net/m0_46836425/article/details/121851867

5. Sql优化的具体操作

1. 查询SQL尽量不要使用select *，而是具体字段

2. 避免在where子句中使用or来连接条件：可能会使索引失效，从而全表扫描

   # 反例
   SELECT * FROM student WHERE id=1 OR salary=30000
   # 正例 使用UNION ALL
   SELECT * FROM student WHERE id=1
   UNION ALL
   SELECT * FROM student WHERE salary=30000
   

3. 优化like语句：如果在字段开头使用模糊查询可能会导致所以失效，应该在字段后进行模糊查询

   # 反例
   SELECT id,NAME FROM student WHERE NAME LIKE '%1'
   # 正例
   SELECT id,NAME FROM student WHERE NAME LIKE '1%'
   

4. 查询字符串要加’'：这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

   # 反例
   SELECT * FROM student WHERE NAME=123 # 没有走索引
   # 正例
   SELECT * FROM student WHERE NAME='123'
   

5. 避免使用in和not in：可能会导致索引失效；如果是连续数值可以用between、如果是子查询可以使用exists代替

6. 避免进行null值的判断：索引可能会失效，可以给字段添加默认值0

6. 数据库乐观锁、悲观锁的区别

1. 悲观锁：每次拿数据觉得有人会修改，事务直接在操作数据时抢占锁，其他的事务在进行时就会等待，直到占有锁的事务释放锁为止。

   能保证数据的最大一致性，但是容易导致锁超时、并发程度低等问题

2. 乐观锁：乐观锁认为数据一般情况下不会造成冲突，只有当数据去进行写操作时，才会针对数据冲突做处理，实现方式一般都是加version或者时间戳字段

   比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增加，需要重复进行操作，反而不如悲观锁

   实现原理：

   1. 事务A读取数据的时候，获取到版本号为1
   2. 事务B进行了一次修改，版本号+1变成2
   3. 此时事务A进行修改，发现版本号对应不上，事务执行失败

总结：

写操作比较频繁使用悲观锁、读取操作比较频繁使用乐观锁

实现方式：

1. 使用悲观锁只需要在Sql语句后加上for update即可
2. 使用乐观锁只需要通过代码层面即可

7. count(*)、count(1)、count(column)的区别

1. count(*)：对行的数目进行计算，包含null值
2. count(1)：与count（*）相同
3. count(column)：对单个列中的函数进行计算，排除null值

8. 什么是索引以及索引的作用

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容

索引是为了能够快速对表中数据行进行查询的一种数据结构。

索引中存储着表的指定列的数据值的指针，然后根据用户指定的排序方式对指针进行排序；数据库可以通过索引找到特定值，然后获取到该值的指针，通过指针找到包含该值的行

如果表中有大量数据，查询方式有两种：一种是普通全表检索，这样很慢且浪费IO；第二种就是建立索引通过索引来操作，在索引中找到符合要求的索引值，通过索引获取到保存在索引中的ROWID，来找到符合要求的数据行

优点

1. 大大加快数据检索效率
2. 连接查询时加速表和表之间的关联

缺点

1. 索引也需要占据物理空间
2. 索引也需要维护

9. Hash和B+树索引的区别、为什么要使用B+树作为索引

这两种索引的数据结构不同，顾名思义，一个类似HashMap的哈希表，一个是B+Tree

区别

1. 在查询速度上，如果是等值查询，那么Hash索引有绝对优势，因为只需要经过一次hash算法就可以找到对应的数据；前提是这个键值是唯一的，如果不唯一，那么就需要先找到这个键，然后根据链表向后扫描，这时候就比较慢了，所以Hash索引通常不会用到重复值多的列上，比如年龄、性别（当然B+树也不适合）
2. Hash索引是无序的，如果是范围查询，索引无法起到作用
3. Hash索引不支持多列联合索引

使用场景

大多数场景下，都会有组合查询，范围查询、排序、分组、模糊查询等查询特征，建议使用B+索引

数据离散程度高且等值查询时，Hash索引有优势

以下是两种索引的结构示意图

10. B树和B+树的区别

B树所有节点都存储data，B+树只有叶子节点存储data

1. 因此由于B+树内节点不存储data，所以节点可以放下更多的key；
2. 这样子的话，整个树层次就少了，树就变得矮胖；

11. sql条件语句执行顺序以及Mysql左侧原则

条件语句执行顺序

Mysql执行顺序：从左往右，从上到下；虽然Mysql可以优化查询语句，但是数据量大的话最好手写
Oracle和Sql Server：从右往左，从下到上，所以注意书写顺序，将过滤掉最多的条件放到最后面

Mysql左侧原则

从最左边开始匹配的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like、or)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，其针对的是组合索引（又名联合索引）
对于复合索引 idx_A_B_C:
有A、A and B、B and A、C and A、A and C、A and B and C、B and A and C、C and B and A 会走索引;
注意: or 不走索引 C and B or A 或者 A and B or C 或者 A and (B or C) 不走索引.

12. Mysql的索引类型与存储引擎的关系

在Mysql中，索引是与存储引擎相关

常用的两种引擎：InnoDB（默认）和MyISAM:

1. InnoDB：支持事务；只支持树索引，不支持哈希索引；适合要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制
2. MyISAM：不支持事务；只支持树索引，不支持哈希索引；适合数据表主要用来插入和查询记录的