一、事务的基本性质

感谢 https://www.zhihu.com/question/458275373/answer/1873975439

1、ACID

数据库事务的几个特性：原子性（Atomicity）、一致性（Consistency）、隔离性或独立性（Isolation）和持久性（Durability），简称ACID；

  • 原子性：一系列的操作整体不可拆分，要么同时成功，要么同时失败
  • 一致性：数据在事务的前后，业务整体一致。
    • 转账：A:1000 B:1000；转 200 事务成功；A:800 B:1200
  • 隔离性：事务之间互相隔离。
  • 持久性：一旦事务成功，数据一定会落盘在数据库。

在以往的单体应用中，我们多个业务操作使用同一条连接操作不同的数据表，一旦有异常，我们可以很容易的整体回滚；

2、并发事务带来的问题

2.1 更新丢失

2.1.1 第一类更新丢失

事务A和事务B都对数据进行更新，但是事务A由于某种原因事务回滚了，把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。
image.png

2.1.2 第二类更新丢失

其实跟第一类更新丢失有点类似，也是两个事务同时对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。
image.png

2.2 脏读

1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改(update)后的数据。
2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。
这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。
image.png

2.2 不可重复读

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。
这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。
image.png

2.3 幻读

参考 https://blog.csdn.net/cb506852280/article/details/107645705 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

相关说明：
- 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
- 幻读仅专指“新插入的行”（即只针对insert）

快照读 select count() from user; 这个通过 隔离级别（可重复读）解决
*当前读 如下：（这种情况下，可重复读无法解决）
image.png
根据上面的结果我们期望的结果是这样的：

  1. id  name
  2. 1   财务部
  3. 2   研发部

但是实际上我们的结果是。。如下

  1. id  name
  2. 1   财务部
  3. 2   财务部

本来我们希望得到的结果只是第一条数据的部门改为财务，但是结果确实两条数据都被修改了。这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决时不彻底的。

读数据 其实就是 快照读，修改就是 当前读，参考 跳转。。。

在RR模式下，间隙锁可以解决幻读。请看。。。。。

client 1
image.png
client 2
image.png
阻止了insert，解决了幻读

这种情况 也是 https://www.yuque.com/wangchao-volk4/whmpo0/smu2c1#sOzqJ

2.4 总结

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
image.png
image.png
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

常看当前数据库的事务隔离级别：show variables like ‘tx_isolation’；

二、Mysql隔离级别

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交、读已提交、可重复读、串行化。事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。所以在设置数据库的事务隔离级别时需要做一下权衡，MySQL默认是可重复读的级别。

1、隔离级别设置

  1. SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
  3. -- 查询隔离级别
  4. SELECT @@session.tx_isolation;
  6. -- 设置隔离级别
  7. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2、读未提交

读未提交(Read Uncommitted)，是最低的隔离级别，所有的事务都可以看到其他未提交的事务的执行结果。只能防止第一类更新丢失，不能解决脏读，可重复读，幻读，所以很少应用于实际项目。

  1. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

当前隔离级别是读未提交
Mysql入门 - 图10
开启事务并插入一条数据
insert into user(username,password,name) values(‘king’,’111’,’王’);
Mysql入门 - 图11
当 A 中的事务没有提交时，去看下B的数据
Client B
Mysql入门 - 图12
结果是 Client B 可以看到 Client A中未提交的数据。
所谓 隔离级别“读未提交” 就是这样，可以看到其他事务未提交的信息
最后关闭AB的事务
commit;

3、读已提交

读已提交(Read Committed)， 在该隔离级别下，一个事务的更新操作结果只有在该事务提交之后，另一个事务才可能读取到同一笔数据更新后的结果。可以防止脏读和第一类更新丢失，但是不能解决可重复读和幻读的问题。

  1. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Mysql入门 - 图13
A 未提交，去B下面看数据
Client B
Mysql入门 - 图14
在隔离级别为“读已提交”，B并没有看到A提交的数据
Client A
接着在A中提交事务，然后去B看下数据
mysql> commit;
Client B
Mysql入门 - 图15
结果：B中能看到A中提交的数据

4、可重复读

可重复读(Repeatable Read)，MySQL默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失、第二类更新丢失的问题，不过还是会出现幻读。

MVCC 能解决 快照读的幻读，当前读的幻读 要靠间隙锁（next-key）解决。

AB事务不能同时更新同一条数据，包括where范围内的数据。 A 更新 where id范围内的数据时，B事务不能insert 这个Id范围内的数据，即间隙锁。

看完 “读已提交” 后会发现一个问题，Client B读了两次，但是前后两次得到的数据信息不一致。当设置为“可重复读”后，则能解决这个问题，Client B无论连续读取多少次，结果都是一致的。
Client A
设置隔离级别“可重复读”

  1. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Mysql入门 - 图16
Mysql入门 - 图17
此时A未提交，去看B的数据
Client B
Mysql入门 - 图18
B 没有读取到A未提交的数据，接着A提交一下
Client A
mysql> commit;
Client B
Mysql入门 - 图19
很明显，B中的数据并没有受到A事务提交的影响！
当B提交本次事务后，就能看到A中的内容了
Mysql入门 - 图20

5、串行化

串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。 1、A事务 不带条件查询时，B事务此时无法进行写操作。 2、A事务 带ID范围查询时，B事务无法对ID范围内的数据进行写操作，但是可以对范围之外的数据进行写操作。带ID之外的其他条件查询，还是会表锁。 3、A事务 带Id范围更新，B事务无法对这个范围内的数据进行读写操作，可以对Id范围以外的数据进行读写操作。 4、A事务新建时，B事务读写都不能操作。

Client A
设置隔离级别为串行化
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Mysql入门 - 图21
Mysql入门 - 图22
A 开启事务
Client B
Mysql入门 - 图23
因为Client A未提交事务，由于不同事务之间相互阻塞，所以出现了上述问题。
当Client A 提交事务之后。
mysql> insert into user(username,password,name) values(‘king9’,’111’,’王’);
Query OK, 1 row affected (0.00 sec)
这样B中就能成功创建了。

三、事务的传播行为

1、解释

  1. // 写法一
  2. class serviceImpl implements service{
  3.  @Autowired 
  4.  private service s;
  5. @Transaction()
  6. public void a(){  // a事务的所有设置就传播到了和他共用一个事务的方法
  7.   s.b();
  8.   s.c():   // 这有个坑，看下面注释  c如果设置的是required_new，那c就是独立的事务，a事务回滚和c没有任何关系
  9.   int i = 10/0;
  11. @Transaction()
  12. public void b(){
  14. }
  15. @Transaction()
  16. public void c(){
  17. }
  18. }
  21. // 写法二  使用了 @EnableAspectJAutoProxy(exposeProxy=true)
  22.   class serviceImpl implements service{
  23. @Transaction()
  24. public void a(){  // a事务的所有设置就传播到了和他共用一个事务的方法
  25.   serviceImpl service =(serviceImpl)AopContext.currentProxy();
  26.   service.b();
  27.   service.c():   // c如果设置的是required_new，那c就是独立的事务，a事务回滚和c没有任何关系
  28.   int i = 10/0;
  29. }
  31. @Transaction()
  32. public void b(){
  34. }
  36. @Transaction()
  37. public void c(){
  39. }
  40. }

b如果设置的传播是required，那b设置的timeout无任何意义，以a的timeout为准。

1、PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。
2、PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务方式执行。
3、PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
4、PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。
5、PROPAGATION_NOT_SUPPORT：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
7、PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
8、PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似。

2、本地事务失效

现象
当使用springboot的时候，同一个对象内事务方法互调失效问题，a方法里面的bc无论设置了何种事务都没有效果。

解决方式
1、必须重新使用一个service，例如bService.b()；cServce.c()，这样才有效果。因为事务是使用的代理对象来控制的，a事务的代理对象内的b,c方法就相当于复制粘贴进去的。
2、引入spring-boot-starter-aop模块，因为这个模块里面有aspectj；在启动器上开启@EnableAspectJAutoProxy(exposeProxy=true)，使用aspectj的动态代理功能并对外暴露代理对象，而不使用jdk自带的动态代理功能。可以参考 写法二

aspectj和jdk自带的动态代理功能的区别，jdk自带的动态代理功能必须要有接口才能实现，而aspectJ即使没有接口也可以实现动态代理功能。

四、分布式事务

1、事务保证

1.1 异常场景

1、订单服务异常，库存锁定不运行，全部回滚，撤销操作
2、库存服务事务自治，锁定失败全部回滚，订单感受到，继续回滚
3、库存服务锁定成功了，但是网络原因返回数据途中问题？
4、库存服务锁定成功了，库存服务下面的逻辑发生故障，订单回滚了，怎么处理？
image.png

利用消息队列实现最终一致 库存服务锁定成功后发送消息队列消息（当前库存工作单），等待时间自动解锁，解锁时先检查订单的支付状态。解锁成功修改库存工作单详情项状态为已解锁。

遇到的问题：

  • 远程服务假失败：
    • 远程服务其实成功了，由于网络故障等没有返回导致：订单回滚，库存却减少

  • 远程服务执行完成，下面的其他方法出现问题

    • 导致：已执行的远程请求，肯定不能回滚

      1.2 CAP 定理

  • 强一致性（Consistency）

    • 在分布式系统中的所有数据备份，在同一时刻是否同样的值。（等同于所有节点访问同一份最新的数据副本）。例如abc三台机器，在同一个时刻的值是一样的
  • 可用性（Available）
    • 在集群中一部分节点故障后，集群整体是否还能响应客户端的读写请求。（对数据更新具备高可用性）
  • 分区容错性（Partition tolerance）
    • 大多数分布式系统都分布在多个子网络。每个子网络叫做一个区(partition)。分区容错的意思是，区间通信可能失败。比如，一台服务器放在中国，另外一台服务器放在美国，这就是两个区，它们之间可能无法通信。

      CAP原则就是说这三个要素只能同时实现两点，不可能三者兼顾，在一般的系统中，都必须保证分区容错性，所以只能在可用性和一致性中二选一，所以我们的机器大都是CP机器，要么是AP机器。

AC是不可能并存的，因为C要求三台机器一样的值，A可以容忍三台中的两台挂掉，来保证可用性
分布式系统中实现一致性的raft算法(保证CP)、paxos算法。
http://thesecretlivesofdata.com/raft/
raft:在自旋时间内选出领导，在心跳时间内把日志发出去，心跳时间一般都是小于自旋时间的。

面临的问题
对于多数大型互联网应用场景，主机众多、部署分散，而且现在的集群规模越来越大，所以节点故障、网络故障是常态，而且要保证服务可用性达到99.99999(N个9)，既保证P和A，舍弃C。

1.3 BASE理论

既是无法做到强一致性（Strong consistency），但每个应用都可以根据自身的业务特点，采用适当的方式来使系统达到最终一致性（Eventual consistency）。

  • 基本可用（Basically Available）什么是基本可用呢？假设系统，出现了不可预知的故障，但还是能用，相比较正常的系统而言。
    • 响应时间上的损失：正常情况下的搜索引擎0.5秒即返回给用户结果，而基本可用的搜索引擎可以在2秒作用返回结果。
    • 功能上的损失：在一个电商网站上，正常情况下，用户可以顺利完成每一笔订单。但是到了大促期间，为了保护购物系统的稳定性，部分消费者可能会被引导到一个降级页面。
  • 软状态（Soft State）什么是软状态呢？相对于原子性而言，要求多个节点的数据副本都是一致的，这是一种“硬状态”。软状态指的是：允许系统中的数据存在中间状态，并认为该状态不影响系统的整体可用性，即允许系统在多个不同节点的数据副本存在数据延时。
  • 最终一致性（Eventually Consistent）上面说软状态，然后不可能一直是软状态，必须有个时间期限。在期限过后，应当保证所有副本保持数据一致性，从而达到数据的最终一致性。这个时间期限取决于网络延时、系统负载、数据复制方案设计等等因素。实际上，不只是分布式系统使用最终一致性，关系型数据库在某个功能上，也是使用最终一致性的。比如备份，数据库的复制过程是需要时间的，这个复制过程中，业务读取到的值就是旧的。当然，最终还是达成了数据一致性。这也算是一个最终一致性的经典案例。

总结：总体来说BASE理论面向的是大型高可用、可扩展的分布式系统。与传统ACID特性相反，不同于ACID的强一致性模型，BASE提出通过牺牲强一致性来获得可用性，并允许数据段时间内的不一致，但是最终达到一致状态。同时，在实际分布式场景中，不同业务对数据的一致性要求不一样。因此在设计中，ACID和BASE理论往往又会结合使用。

1.4 强一致性、弱一致性、最终一致性

从客户端角度看，多进程并发访问时，更新过的数据在不同的进程如何获取的不同策略，决定了不同的一致性。对于关系型数据库，要求更新过的数据能被后续的访问都能看到，这是强一致性。如果能容忍后续的部分或者全部访问不到，则是弱一致性，如果经过一段时间后要求能访问到更新后的数据，则是最终一致性。

2、分布式事务的集中解决方案

2.1 2PC模式

2PC即两阶段提交协议(2 phase commit)，是将整个事务流程分为两个阶段，准备阶段（Prepare phase）、提交阶段（commit phase）.2是指两个阶段，P是指准备阶段，C是指提交阶段。

数据库支持的2PC，又叫XA transaciton。XA是传统分布式事务解决方案,基于数据库必须要支持2PC协议,利用数据的2PC协议来实现分布式事务。
第一阶段：事务协调器要求每个涉及到事务的数据库预提交(precommit)操作,并反映是否可以提交。
第二阶段：事务协调器要求每个数据库提交数据。其中，如果有任何一个数据库否决此次提交，那么所有数据库都会被要求回滚它们在此事务中的那部分信息。
image.png

  • XA协议比较简单,而且一旦商业数据库实现了xA协议,使用分布式事务的成本也比较
    低。
  • XA性能不理想,特别是在交易下单链路,往往并发量很高,XA无法满足高并发场景
  • XA目前在商业数据库支持的比较理想,在 mysql数据库中支持的不太理想,mys的XA实现,没有记录 prepare阶段日志,主备切换会导致主库与备库数据不一致。
  • 许多 nosql也没有支持XA,这让XA的应用场景变得非常狭隘。
  • 也有3PC,引入了超时机制(无论协调者还是参与者,在向对方发送请求后,若长时间
    未收到回应则做出相应处理)

    2.2 柔性事务-TCC事务补偿型方案

    刚性事务：遵循ACID原则，强一致性 柔性事务：遵循BASE理论，最终一致性 与刚性事务不同的是，柔性事务允许在一定时间内，不同节点的数据不一致，但要求最终一致性

TCC（Try-Confirm-Cancel）的概念，最早是由 Pat Helland 于 2007 年发表的一篇名为《Life beyond Distributed Transactions:an Apostate’s Opinion》的论文提出。
TCC 是服务化的二阶段编程模型，其 Try、Confirm、Cancel 3 个方法均由业务编码实现：

  • Try 操作作为一阶段，负责资源的检查和预留。
  • Confirm 操作作为二阶段提交操作，执行真正的业务。
  • Cancel 是预留资源的取消。

TCC 事务的 Try、Confirm、Cancel 可以理解为 SQL 事务中的 Lock、Commit、Rollback。

2.3 柔性事务-最大努力通知型方案

按一定规律进行通知，不保证数据一定能通知成功，但会提供可查询操作接口进行核对。这种方案也会使用MQ进行实现，通知是否成功。案例，支付宝的支付成功会调。

2.4 柔性事务-可靠消息+最终一致性方案（异步确保型）

保证高并发的情况下，其他业务自己回滚，发消息通知。

2.5 SEATA

用法

详情参考官网文档：http://seata.io/zh-cn/docs/user/quickstart.html
备注：
1、每个微服务必须创建undo_log表
2、安装事务协调器：seata-server：https://github.com/seata/seata/releases
3、整合
1. 导入依赖：spring-cloud-starter-alibaba-seata seata-all-0.7.1
2. 解压并启动seata服务器，注意下载的版本和maven中jar包的版本
3. 修改registry.conf配置文件中的注册中心和地址，以及文件的配置
4. 所有想要用到分布式事务的微服务都需要使用seata datasourceProxy代理自己的数据源
5. 每个微服务都必须导入 registry.conf和file.conf
6. 给分布式大事务的入口标注@GlobalTransactionl，每个远程的小事务用@Transactional

整体机制

两阶段提交协议的演变：

  • 一阶段：业务数据和回滚日志记录在同一个本地事务中提交，释放本地锁和连接资源。
  • 二阶段：
    • 提交异步化，非常快速地完成。
    • 回滚通过一阶段的回滚日志进行反向补偿。

      五、易忘sql

      1、删除

      1.1 Drop

      主要删除表
      1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
      2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
      3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid 状态。

      1.2 truncate

      1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
      2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
      3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
      4、truncatetable不能用于参与了索引视图的表。

      1.3 delete

      1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
      2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
      3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

      1.4 总结

      1、在速度上，一般来说，drop> truncate > delete。
      2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
      3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
      如果想删除表，当然用drop；
      如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
      如果和事务有关，或者想触发trigger，还是用delete；
      如果是整理表内部的碎片，可以用truncate跟上 reuse stroage，再重新导入/插入数据。

      2、修改表结构

      2.1 加、删除列

      ``plsql -- 增加 ALTER TABLE xxxxx ADD COLUMNname` varchar(200) DEFAULT NULL COMMENT ‘名称’;

— 或者用以下方法 ALTER TABLE xxxx ADD COLUMN (xxx VARCHAR(32) DEFAULT NULL COMMENT ‘设备IP’, xxx VARCHAR(128) DEFAULT NULL COMMENT ‘设备名称’, xxx VARCHAR(32) DEFAULT NULL COMMENT ‘设备类型’);

—删除 格式： alter table 表名 drop column 列名; 示例： alter table test4 drop column addr;

  1. <a name="GS03u"></a>
  2. ### 2.2 加索引、删除索引
  3. <a name="JGDRT"></a>
  4. #### （1）ALTER TABLE
  5. ```plsql
  6. -- 增加普通索引
  7. ALTER TABLE table_name ADD INDEX index_name (column_list)
  8. -- 增加唯一索引
  9. ALTER TABLE table_name ADD UNIQUE (column_list)
  10. -- 增加主键索引
  11. ALTER TABLE table_name ADD PRIMARY KEY (column_list)

（2）CREATE INDEX

CREATE INDEX 无法创建主键索引

  1. -- 增加普通索引
  2. CREATE INDEX index_name ON table_name (column_list)
  3. -- 增加唯一索引
  4. CREATE UNIQUE INDEX index_name ON table_name (column_list)

（3）删除索引

  1. DROP INDEX index_name ON talbe_name
  2. ALTER TABLE table_name DROP INDEX index_name
  3. ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

（4）查看索引

  1. show index from tblname;

3、常用小技巧

3.1 结果集导出

问题：bigint类型导出到Excel后会丢失精度？
解决：结果集使用CONCAT转化后再导出

  1. SELECT
  2. t.NSRMC AS '企业名称',
  3. CONCAT(t.NSRSBH,'\t')  AS '纳税人识别号'
  4. FROM
  5. t_zc_bmd_copy t LIMIT 1000;

4、exists

select…..from table where exists (subquery)
该语法可以理解为：将主查询的数据拿到子查询中做验证，根据验证的结果（true/false）来决定主查询的数据结构是否可以保留
image.png

  1. SELECT * from smiler_user_2 WHERE id in (select id from smiler_user_1);
  3. SELECT * from smiler_user_2 WHERE exists (SELECT 1 from smiler_user_1 WHERE smiler_user_1.id = smiler_user_2.id)

5、left 中的 on 和 where 的区别

以left join 为例 ：
where 即对最终结果进行where条件的过滤
on：后面的条件与左连接本身无关，影响的是连接产生后的数据，所以一般写右边的条件。
image.png
inner join：WHERE和ON查询出来的结果是一样的，由此可看出，INNER JOIN 的ON条件和WHERE条件影响的都是一个效果，影响整体的查询结果。

6、union 和 union all

union all : 是直接连接，取到得是所有值，记录可能有重复
union : 是取唯一值，记录没有重复
1、UNION 的语法如下： [SQL 语句 1] UNION [SQL 语句 2]
2、UNION ALL 的语法如下： [SQL 语句 1] UNION ALL [SQL 语句 2]

效率： UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。 1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。
2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

六、innoDB、myISAM

  • InnoDB支持事务，MyISAM不支持
  • InnoDB支持外键，而MyISAM不支持
  • InnoDB主键是聚集索引，使用B+Tree作为索引结构，MyISAM是非聚集索引
  • InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）。

image.png

七、索引

关注索引专题
https://www.yuque.com/wangchao-volk4/whmpo0/qldrh6

八、慢查日志

默认：show variables like ‘’%slow_query_log%’’
1、开启 set global slow_query_log=1，不是永久生效的，重启后失效
设置慢的阈值：set global long_query_time=3
show global variables like ‘’%long_query_time%’（然后重新连接，或新开一个会话才能看到修改值）
2、my.cnf配置文件增加参数 slow_query_log = 1,
long_query_time=3
slow_query_log_file=/var/lib/mysql/xxxx-slow.log
log_output=FILE
3、mysqldumpslow

九、锁相关

关注：https://www.yuque.com/wangchao-volk4/whmpo0/smu2c1

十、主从复制

配置过程参考
https://www.cnblogs.com/NiceCui/p/8213723.html
image.png