Day123 MySQL事务

MySQL高级

深入MySQL的底层原理

体系结构

• 连接层：主要完成一些类似于连接处理、授权认证、及相关的安全方案。
• 服务层：完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。
• 引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，可以根据自己的需要，来选取合适的存储引擎。
• 存储层：将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式针对不同的存储需求可以选择最优的存储引擎。

• InnoDB：MySQL5.5之后默认引擎。支持事务，提供了行级锁和外键的约束；适合高并发的业务或对数据一致性要高的业务；对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

  • 外键：外键会严重影响数据库读写的效率；数据删除时会比较麻烦。在电商行业，性能是非常重要的。我们宁可在代码中通过逻辑来维护表关系，也不设置外键。
  • 索引实现原理：B+Tree，聚簇索引。
  • 聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。主键索引就是一种聚簇索引。
    • 优点：.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快；聚簇索引对于主键的排序查找和范围查找速度非常快。
    • 缺点：插入速度严重依赖于插入顺序；更新主键的代价很高，因为将会导致被更新的行移动；二级索引访问需要两次索引查找。

• MyISAM：不提供事务的支持，也不支持行级锁和外键，其优势是访问的速度快，适合读数据多的网站。

  • 索引实现原理：MyISAM索引文件和数据文件是分离的，索引文件的叶结点仅保存数据记录的地址。
  • 非聚簇索引，都是辅助索引，像复合索引、前缀索引、唯一索引。

• 引擎区别：

  • 事务安全：MyISAM不支持事务而 InnoDB 支持
  • 查询和添加速度：MyISAM不用支持事务就不用考虑同步锁，查找和添加和添加的速度快
  • 支持全文索引：MyISAM 支持，InnoDB 不支持
  • 锁机制：MyISAM 支持表锁，InnoDB 支持行锁
  • 外键：MyISAM 不支持外键， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)

• 索引区别：

  • InnoDB 主键索引是聚簇索引，MyISAM 主键索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

• MySQL中的B+树：

• 在MySQL中我们的InnoDB页（叶子结点）的大小默认是16k，当然也可以通过参数设置。
  
  资料：
  https://www.cnblogs.com/jiawen010/p/11805241.html
  https://blog.youkuaiyun.com/luoyang_java/article/details/92781164

事务

一套操作叫事务，那么这些操作要么同时成功，要么同时失败。事务的最终目的就是为了保证数据的可靠性和一致性，不能因为突发的异常使数据出现问题，并且在多线程下要保证数据的安全。

• 事物实现原理：即如何实现事务的ACID，通过三个技术实现，分别是日志文件(redo log 和 undo log)，锁技术以及MVCC
• 日志文件：
  • redo log是用来恢复数据的 用于保障，已提交事务的持久化特性
  • undo log是用来回滚数据的用于保障 未提交事务的原子性
• mysql锁技术：
• 读写锁：通过读写锁，可以做到读读可以并行，但是不能做到写读，写写并行。
  • 读锁：共享锁(shared lock),又叫做"读锁"，读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。
  • 写锁：排他锁(exclusive lock),又叫做"写锁"，写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。
• 行锁和表锁：MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
  • 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
  • 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。
• InnoDB中的锁
  • 记录锁：封锁索引记录，它会在索引记录上加锁，以阻止其他事务插入，更新，删除这一行。
  • 间隙锁：封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
  • 临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
• MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制：指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB 的并发度，并且不用加锁就能让多个事务并发读写。
  • 基本思想：每次事务生成一个新版本的数据，在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取。
  • MVCC 的实现依赖：隐藏字段、Read View、Undo log。
  • 隐藏字段记录版本号（创建版本、删除版本）

https://www.bilibili.com/video/BV1Vk4y1k7KQ
https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961471&idx=1&sn=da257b4f77ac464d5119b915b409ba9c&chksm=bd2d0da38a5a84b5fc1417667fe123f2fbd2d7610b89ace8e97e3b9f28b794ad147c1290ceea&scene=21#wechat_redirect

• 事务隔离级别

  • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
  • READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
    • 实现原理：select语句不加锁。
  • READ-COMMITTED （Oracle默认）：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
    • 实现原理：锁select /update /delete 会使用记录锁
  • REPEATABLE-READ （MySQL默认）：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
    • 实现原理：普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；（快照读：读的是历史数据的快照，这种一致性不加锁的读就是InnoDB并发如此之高的核心原因之一）
  • SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

• 事务的四大特征（ACID）

  • Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部失败，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 实现：事务的原子性是通过 undo log 来实现的
  • Consistency（一致性）：事务前后数据的完整性必须保持一致。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
    • 实现：而事务的终极大 boss 一致性是通过原子性，持久性，隔离性来实现的
  • Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 实现：事务的隔离性是通过 (读写锁+MVCC)来实现的
  • Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
  • 实现：事务的持久性性是通过 redo log 来实现的

• 脏读、幻影读、不可重复读的理解与解决方案

  • 脏读：一个事务，读取到另一个事务中没有提交的数据
  • 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
  • 幻读：一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。
  • 解决方案：隔离级别

https://blog.youkuaiyun.com/Waves___/article/details/105295060
https://www.cnblogs.com/wyc1994666/p/11367051.html

同步

    4、主从同步实现方案
        1、优缺点

联系到多线程中锁的概念，多个事务读同一批数据时也容易出问题，所以也有锁的概念。

不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)

一般也就听过常说的乐观锁和悲观锁，了解过基本的含义之后就没了~~~

定心丸：即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预
只会在某些特定的场景下才需要手动加锁，学习数据库锁知识就是为了:

能让我们在特定的场景下派得上用场
更好把控自己写的程序
在跟别人聊数据库技术的时候可以搭上几句话
构建自己的知识库体系！在面试的时候不虚

175. 说一下 mysql 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

索引

    7、索引
        1、为什么要建索引
        2、什么样的字段需要建索引
        3、建索引的时候一般考虑什么
        4、索引会不会使插入、删除作效率变低，怎么解决