事务的隔离级别

image.png

InnoDB默认是可重复读级别的
① 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
② 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
③ 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样，幻读是数据行记录变多了或者少了。
简单点总结下他们的区别：脏读是指读取了未修改完的记录，不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致，而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题。

锁机制

共享锁:又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到最新数据。

• 多个事务的查询语句可以共用一把共享锁；
• 如果只有一个事务拿到了共享锁，则该事务可以对数据进行 UPDATE DETELE 等操作；
• 如果有多个事务拿到了共享锁，则所有事务都不能对数据进行 UPDATE DETELE 等操作。
  使用场景：
• 确保某个事务查到最新的数据；
• 这个事务不需要对数据进行修改、删除等操作；
• 也不允许其它事务对数据进行修改、删除等操作；
• 其它事务也能确保查到最新的数据。

排它锁:又称为写锁，简称X锁，顾名思义，排它锁不能与其它锁并存，而且只有一个事务能拿到某一数据行的排它锁，其余事务不能再获取该数据行的所有锁。

• 只有一个事务能获取该数据的排它锁；
• 一旦有一个事务获取了该数据的排它锁之后，其余事务对于该数据的操作将会被阻塞，直至锁释放。
  使用场景：
• 确保某个事务查到最新的数据；
• 并且只有该事务能对数据进行修改、删除等操作。

几种排它锁

-记录锁(Record Locks)
记录锁是 封锁记录，记录锁也叫行锁；

• 间隙锁(Gap Locks)
  唯一锁引产生间隙锁
  • 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE id = 5 FOR UPDATE;
  • 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;
    普通索引产生 间隙锁
  • 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
    -在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。
• 临键锁(Next-key Locks)
  临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
  注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

RR隔离级别下，是如何解决幻读问题的？

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。
可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是启动事务后，在执行第一个查询语句后，会创建一个视图，然后后续的查询语句都用这个视图，「快照读」读的就是这个视图的数据，视图你可以理解为版本数据，这样就使得每次查询的数据都是一样的。
MySQL 里除了普通查询是快照度，其他都是当前读，比如update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且 提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。因此，要讨论「可重复读」隔离级别的幻读现象，是要建立在「当前读」的情况下。
Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了 next-key 锁，就是记录锁和间隙锁的组合。

RR彻底解决了幻读吗？

MySQL没有完全解决快照读下的幻读问题。

可以做这个实验：
当前DB已有id 5, 10, 15三条数据。
事务A查询id < 10的数据，可以查出一行记录id = 5
事务B插入id = 6的数据
事务A再查询id < 10的数据，可以查出一行记录id = 5，查不出id = 6的数据（读场景，解决了幻读）
事务A可以更新/删除id = 6的数据，不能插入id = 6的数据（写场景，幻读不彻底）

这个很好理解，MySQL虽然通过MVCC的版本号来解决了读场景下的幻读，但对于上面第5步那种写场景的情况，其实是无能为力的，因为MVCC毕竟是无锁实现。

所以如果后续要对数据进行写操作，还是「通过for update语句上锁」比较稳妥，不然就可能会出现上面第5步那样的问题。

深度分页查询慢

为什么先查询Id再连表查询会这么快？？？
因为mysql默认搜索引擎是innodb，innoDb是聚簇索引,底层索引是B+tree，id为我们的主键，利用了主键索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了。然后拿我们得到的主键id再去连表。这样就节省了很多查询时间

分库分表 (sharding)

数据库水平扩展的核心是把数据拆分成不同的单元并放在不同的独立的实例上，这样就做到了负载均衡。拆分分为逻辑和物理拆分，逻辑拆分是对物理上不可分割的实例进行逻辑上的分割，物理拆分是拆分成多个独立的实例：
逻辑拆分
-分区（Partition）
-分表
物理拆分
-读写分离
-垂直拆分（分库）
-水平拆分（分表）

物理拆分带来好处的同时也带来的一些问题：
跨库事务

• 通过分布式事务 或 最终一致解决
  跨库 Join
• 把 Join 操作拆分成多次查询并在应用中做聚合
  -使用搜索引擎做数据聚合和查询
  -使用 CQRS 做数据聚合
  跨表分页和排序：
• 由中间件去所有分片聚合数据，再做分页和排序

MySQL:互联网公司常用分库分表方案汇总

MySQL 分库分表与分区的区别和思考

B-树

B-树（Balance Tree），一个m阶的B树具有如下几个特征：
1.根结点至少有两个子女。
2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m
3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
4.所有的叶子结点都位于同一层。
5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

索引数据结构

什么情况下会使用索引呢？

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中于其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合建立索引，因为每次更新不单单时更新了记录还会更新索引
5. where 条件里用不到的字段不创建索引
6. 查询中排序的字段，排序的字段若通过索引去访问将会大大提高排序速度
7. 查询中统计或者分组的字段
   哪些情况不需要创建索引
   1.表记录太少
   2.经常增删改的表
   3.如果某个数据列包含许多重复的内容，为它建立索引
   就没有太多太大实际效果

B+树

B+树的特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优势：
1.单一节点存储更多的元素，使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。

索引失效的几种情况

索引失效的几种情况
　　1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
　　2.对于多列索引，不是使用的第一部分，则不会使用索引
　　3.like查询以%开头
　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

MVCC

InnoDB 是如何存储记录多个版本的？这些数据是 事务版本号，行记录中的隐藏列和Undo Log。
事务版本号
每开启一个日志，都会从数据库中获得一个事务ID（也称为事务版本号），这个事务 ID 是自增的，通过 ID 大小，可以判断事务的时间顺序。
行记录的隐藏列
1.row_id :隐藏的行 ID ,用来生成默认的聚集索引。如果创建数据表时没指定聚集索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率。
2.trx_id: 操作这个数据事务 ID ，也就是最后一个对数据插入或者更新的事务 ID 。
3.roll_ptr:回滚指针，指向这个记录的 Undo Log 信息。

mysql慢

一个 SQL 执行的很慢，我们要分两种情况讨论：

1. 大多数情况下很正常，偶尔很慢，则有如下原因
   a. 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
   b. 执行的时候，遇到锁，如表锁、行锁。

2. 这条 SQL 语句一直执行的很慢，则有如下原因。
   a. 没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
   b. 数据库选错了索引。