sql中的where条件在数据库中提取与应⽤浅析

  • 关系型数据库中的数据组织
    • 关系型数据库中，数据组织涉及到两个最基本的结构：表与索引。表中存储的是完整记录，⼀般有两种组织形式：堆表(所有的记录⽆序存储)，或者是聚簇索引表(所有的记录，按照记录主键进⾏排序存储)。索引中存储的是完整记录的⼀个⼦集，⽤于加速记录的查询速度，索引的组织形式，⼀般均为B+树结构
  1. create table t1 (a int primary key, b int, c int, d int, e
  2. varchar(20));
  3. create index idx_t1_bcd on t1(b, c, d);
  4. insert into t1 values (4,3,1,1,’d’);
  5. insert into t1 values (1,1,1,1,’a’);
  6. insert into t1 values (8,8,8,8,’h’):
  7. insert into t1 values (2,2,2,2,’b’);
  8. insert into t1 values (5,2,3,5,’e’);
  9. insert into t1 values (3,3,2,2,’c’);
  10. insert into t1 values (7,4,5,5,’g’);
  11. insert into t1 values (6,6,4,4,’f’);
  13. select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != 'a';
  • 此SQL，覆盖索引idxt1bcd上的哪个范围
    • 起始范围：记录[2,2,2]是第⼀个需要检查的索引项。索引起始查找范围由b >= 2，c > 1决定
    • 终⽌范围：记录[8,8,8]是第⼀个不需要检查的记录，⽽之前的记录均需要判断。索引的终⽌查找范围由b < 8决定
    • 还可以使⽤c > 1 and d != 4条件进⾏索引记录的过滤
    • e != ‘a’这个查询条件，⽆法在索引idxt1bcd上进⾏过滤，因为索引并未包含e列
  • 所有SQL的where条件，均可归纳为3⼤类
    • Index Key (First Key & Last Key)
      • ⽤于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于⼀个范围，⾄少包含⼀个起始与⼀个终⽌，因此Index Key也被拆分为Index First Key和Index Last Key，分别⽤于定位索引查找的起始，以及索引查询的终⽌条件
      • Index First Key：⽤于确定索引查询的起始范围。提取规则：从索引的第⼀个键值开始，检查其在where条件中是否存在，若存在并且条件是=、>=，则将对应的条件加⼊Index First Key之中，继续读取索引的下⼀个键值，使⽤同样的提取规则；若存在并且条件是>，则将对应的条件加⼊Index First Key中，同时终⽌Index First Key的提取；若不存在，同样终⽌Index First Key的提取
      • Index Last Key：功能与Index First Key正好相反，⽤于确定索引查询的终⽌范围。提取规则：从索引的第⼀个键值开始，检查其在where条件中是否存在，若存在并且条件是=、<=，则将对应条件加⼊到Index Last Key中，继续提取索引的下⼀个键值，使⽤同样的提取规则；若存在并且条件是 <，则将条件加⼊到Index Last Key中，同时终⽌提取；若不存在，同样终⽌Index Last Key的提取
    • Index Filter
      • 在完成Index Key的提取之后，我们根据where条件固定了索引的查询范围，但是此范围中的项，并不都是满⾜查询条件的项。
      • Index Filter的提取规则：同样从索引列的第⼀列开始，检查其在where条件中是否存在：若存在并且where条件仅为 =，则跳过第⼀列继续检查索引下⼀列，下⼀索引列采取与索引第⼀列同样的提取规则；若where条件为 >=、>、<、<= 其中的⼏种，则跳过索引第⼀列，将其余where条件中索引相关列全部加⼊到Index Filter之中；若索引第⼀列的where条件包含 =、>=、>、<、<= 之外的条件，则将此条件以及其余where条件中索引相关列全部加⼊到Index Filter之中；若第⼀列不包含查询条件，则将所有索引相关条件均加⼊到Index Filter之中
    • Table Filter
      • Table Filter是最简单，最易懂，也是提取最为⽅便的。提取规则：所有不属于索引列的查询条件，均归为Table Filter之中
  • Index Key/Index Filter/Table Filter⼩结
    • Index First Key，只是⽤来定位索引的起始范围，因此只在索引第⼀次Search Path(沿着索引B+树的根节点⼀直遍历，到索引正确的叶节点位置)时使⽤，⼀次判断即可；
    • Index Last Key，⽤来定位索引的终⽌范围，因此对于起始范围之后读到的每⼀条索引记录，均需要判断是否已经超过了Index Last Key的范围，若超过，则当前查询结束；
    • Index Filter，⽤于过滤索引查询范围中不满⾜查询条件的记录，因此对于索引范围中的每⼀条记录，均需要与Index Filter进⾏对⽐，若不满⾜Index Filter则直接丢弃，继续读取索引下⼀条记录；
    • Table Filter，则是最后⼀道where条件的防线，⽤于过滤通过前⾯索引的层层考验的记录，此时的记录已经满⾜了Index First Key与Index Last Key构成的范围，并且满⾜Index Filter的条件，回表读取了完整的记录，判断完整记录是否满⾜Table Filter中的查询条件，同样的，若不满⾜，跳过当前记录，继续读取索引的下⼀条记录，若满⾜，则返回记录，此记录满⾜了where的所有条件，可以返回给前端⽤户
  • MySQL 5.6中引⼊的Index Condition Pushdown
    • 将Index Filter 给 Push Down到索引层⾯进⾏过滤
    • 在MySQL 5.6之前，并不区分Index Filter与Table Filter，统统将Index First Key与Index Last Key范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进⾏过滤
    • ⽽在MySQL 5.6之后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层⾯进⾏过滤，减少了回表与返回MySQL Server层的记录交互开销，提⾼了SQL的执⾏效率

聊聊如何使⽤MySQL实现分布式锁

  • 分布式锁的功能
    • 分布式锁使⽤者位于不同的机器中，锁获取成功之后，才可以对共享资源进⾏操作
    • 锁具有重⼊的功能：即⼀个使⽤者可以多次获取某个锁
    • 获取锁有超时的功能：即在指定的时间内去尝试获取锁，超过了超时时间，如果还未获取成功，则返回获取失败
    • 能够⾃动容错，⽐如：A机器获取锁lock1之后，在释放锁lock1之前，A机器挂了，导致锁lock1未释放，结果会lock1⼀直被A机器占有着，遇到这种情况时，分布式锁要能够⾃动解决，可以这么做：持有锁的时候可以加个持有超时时间，超过了这个时间还未释放的，其他机器将有机会获取锁
  • 预备技能：乐观锁
    • 通常我们修改表中⼀条数据过程如下：
      • t1：select获取记录R1
      • t2：对R1进⾏编辑
      • t3：update R1
    • 如果A、B两个线程同时执⾏到t1，他们俩看到的R1的数据⼀样，然后都对R1进⾏编辑，然后去执⾏t3，最终2个线程都会更新成功，后⾯⼀个线程会把前⾯⼀个线程update的结果给覆盖掉，这就是并发修改数据存在的问题
    • 可以在表中新增⼀个版本号，每次更新数据时候将版本号作为条件，并且每次更新时候版本号+1
    • 重点在于步骤t4，当多个线程同时执⾏到t1，他们看到的R1是⼀样的，但是当他们执⾏到t4的时候，数据库会对update的这⾏记录加锁，确保并发情况下排队执⾏，所以只有第⼀个的update会返回1，其他的update结果会返回0，然后后⾯会判断count是否为1，进⽽对事务进⾏提交或者回滚。可以通过count的值知道修改数据是否成功了
  1. t1：打开事务start transaction
  2. t2：select获取记录R1,声明变量v=R1.version
  3. t3：对R1进⾏编辑
  4. t4：执⾏更新操作
  5. update R1 set version = version + 1 where user_id=#user_id# and version = #v#;
  6. t5：t4中的update会返回影响的⾏数，我们将其记录在count中，然后根据count来判断提交还是回滚
  7. if(count==1) {
  8.  //提交事务
  9.  commit;
  10. } else {
  11.  //回滚事务
  12.  rollback;
  13. }

MySQL如何确保数据不丢失的？有⼏点我们可以借鉴

  • ⽇志先⾏，io顺序写，异步操作，做到了⾼效操作
    • 对数据页，先在内存中修改，然后使⽤io顺序写的⽅式持久化到redo log⽂件；然后异步去处理redo log，将数据页的修改持久化到磁盘中，效率⾮常⾼，整个过程，其实就是 MySQL ⾥经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写⽇志，再写磁盘
  • 两阶段提交确保redo log和binlog⼀致性
    • 为了确保redo log和binlog⼀致性，此处使⽤了⼆阶段提交技术，redo log 和 binlog的写分了3步⾛：
      • 携带trx_id，redo log prepare到磁盘
      • 携带trx_id，binlog写⼊磁盘
      • 携带trx_id，redo log commit到磁盘
  • 电商中资⾦账户⾼频变动解决⽅案
    • 乐观锁：并发量⼤的时候，只有部分会成功
    • 先写账户操作⽇志，然后异步对⽇志进⾏操作，在⽣成流⽔，借鉴了mysql中的设计
  • 跨库转账问题：使⽤mysql上⾯介绍的⼆阶段提交来解决