Mysql B+数的优点、聚簇索引和非聚簇索引的区别、乐观锁和悲

Mysql使用B+数的优点：

• 非叶子节点只有索引，没有数据，可索引的范围更广，降低树的高度，查询速度更快。
• 数据只存在叶子节点，叶子节点两两相连接，符合磁盘读取特征(一次读取一段数据)
• 叶子节点拥有连续索引，支持范围查询

聚簇索引和非聚簇索引的区别

• MyISAM是非聚簇索引，B+树的叶子节点储存的不是数据，而是数据地址；

• 主索引和辅助索引没有区别，主索引的key一定要唯一；

• 主索引的B+树的叶子节点存储了主键，辅助索引的B+树的叶子节点存储了辅助键

• 表数据存储在单独的位子，两种索引的树叶子节点节点都使用一个地址指向真正的数据

• InnoDB是聚簇索引，行数据就存在叶子节点

• 主键查询，直接找到叶子节点，返回数据

• 非主键查询，先找到主键，然后再根据主键二次查询数据

• 聚簇索引数据的物理存放顺序和索引顺序是一致的，即，索引是相临的，数据也是相临的，聚簇索引查询效率要比非聚簇索引高效。

• 聚簇索引 主索引+辅助索引的好处是，数据行变动，主索引会更新，但是辅助索引不需要更新

• 非聚簇索引，类似于书的目录，

• 每个表只能有一个聚簇索引，因为一个表的物理顺序是唯一的。

主索引和辅助索引

主索引：

• 将主文件分块，每一块对应一个索引项，每个存储块的第一条记录，又称为锚记录
• 主索引是按照索引字段值进行排序的一个有序文件，通常建立在有序文件的基于主码的排序字段上
• 主索引是稀疏索引

辅助索引：

• 定义在主文件的任意一个或者多个非排序字段上的辅助存储结构
• 辅助索引通常对字段(该字段非排序)的每一个不同值有一个索引项
• 字段值不唯一，引入中间桶保存指针列表
• 辅助索引是稠密索引

主索引和辅助索引的差别：

• 一个主文件仅有一个主索引，单可以有多个辅助索引
• 主索引通常建立在主码/排序码上面
• 可以利用主索引重新组织主文件数据，辅助索引不可以

悲观锁和乐观锁

1. 悲观锁：

• 在数据处理过程中，给数据加锁，防止其它进程修改
• 需要将MySQL的自动提交功能关闭，

set autocommit = 0

• 分为 共享锁(IS锁) 和 排它锁(IX锁)
• 共享锁(IS锁)
  实现方式：SELECT ......LOCK IN SHARE MODE
  会给符合条件的数据加上共享锁，其它session也可以读这些数据，也能加共享锁，但是无法修改，只有这个加锁的session执行完成后，才能修改。
  一般形式：
  //开始事务
  start transaction
  //查询信息
  select * from order where id=1 LOCK IN SHARE MODE;
  //其它操作
  ......
  //修改数据
  update order set status=3;
  //提交事务
  commit;
• 排它锁(IX锁)
  实现方式：SELECT ..... FOR UPDATE
  会给服务条件的数据加上排它锁，其它session可以读取这些数据，但是
  不能对这些数据加悲观锁，包括共享锁(IS锁)和排它锁(IX锁)，
  一般形式：
  //开始事务
  start transaction
  //查询信息
  select * from order where id=1 for update;
  //其它操作
  ......
  //修改数据
  update order set status=3;
  //提交事务
  commit;

2. 乐观锁
   乐观锁，它认为数据一般情况下不会造成冲突，所以在数据提交更新是才去做冲突检测，
   实现方式：给数据加版本号，数据读取后记录下版本号，修改时带上版本号修改，如果修改失败，说明数据已经被其它会话修改，就需要程序额外处理了。
   一般形式：
   //开始事务
   start transaction;
   //查询信息
   select status,version from order where id = 1;
   获取version的值，v1}
   //修改不成功 回滚事务
   rollback;
   //修改成功 提交事务
   commit;
   //如果修改失败，说明版本号发生了变更，数据已经被其它会话修改过，此次操作失败，就可以回滚事务了,后续做报错处理。

在部分场景，比如秒杀活动，
用悲观锁，会导致大量用户长时间等待加锁
用乐观锁，都会导致大量用户修改失败
二者都不太好，有一个乐观锁的优化方案，
一般乐观锁：
select num,version from good where id =1
.....
update good set num=num-1,version=version+1 where id=1 and version={$v}
改进策略,非常巧妙
select num from good where id = 1;
update good set num=num-1 where id=1 and num>0

MySQL当前读和快照读

• 当前读，读取最新数据，并且对数据加锁，拒绝其它会话对数据修改，只要涉及的有：共享锁读(select ... lock in share mode)、排它锁读(select ... for update)、insert、update、delete
• 快照读，单纯的select 查询，不包括 select lock in share mode,select ... for update，并且事务隔离级别不是串行化(备注1)
  Read Commited隔离级别：每次select都生成一个快照读
  Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是已开启事务就快照读

当前读和快照读的实现方式

• 当前读的实现方式：next-key锁（行记录锁+Gap间隙锁）
  间隙锁：只有Read Repeatable和Serializable隔离级别才有，就是锁定范围空间的数据，假设id有3,4,5,锁定id>3的数据，是指4,5以及后面的数字都会锁定，因为如果此时不锁定未出现的数据，当新入了id=6，会出现幻读,间隙锁避免了幻读。
  1：对主键或唯一索引，如果当前读时，where条件全部精准命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁
  2：没有索引的列，当读操作时，会加全表gap锁，生产环境需要注意
  3：非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。例如：某表数据非唯一索引列有值2，4，6，9，9，11，15。如果语句要操作值为9的数据，gap锁将会锁定的列是(6,11](注意区间是左开有闭)，该区间内无法插入数据。
• 快照读的实现方式：undolog和多版本并发控制(MVCC)

备注：
1： 事务隔离级别是串行化，那么所有的会话之间都是排它锁形式，都是当前读，非快照读