MySQL(事务&锁)

引擎

InnoDB

• 索引

  • 默认聚簇索引

  • 主键索引-叶子节点包含整行数据、回滚指针、更新版本号、删除版本号（后两个用于MVCC）

  • 普通索引-叶子节点包含主键值，通过查询主键值，再使用主键索引查询整行数据

    • 不带行指针，减小数据移动和页分裂的开销

• 锁

  • 行锁

    • 使用索引查询数据时，锁加在索引上（oracle加在数据行上），当多个数据对应同一个索引时，会产生阻塞

  • 表锁(其实不是)

    • 未使用索引查询数据时，将全表搜索，给每一行加锁，大大降低并发性

  • SQL1：select * from t1 where id = 10;
    SQL2：delete from t1 where id = 10; 加锁总结

    • 在前面八种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采用的是快照读 下面的讨论中就忽略，主要讨论SQL2：delete操作的加锁

    • 一，id列是主键，RC隔离级别

      id是主键时，此SQL只需要在id=10这条记录上加X锁即可

    • 二，id列是二级唯一索引，RC隔离级别

      若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录

      
      

    • 三，id列是二级非唯一索引，RC隔离级别

      若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁

      
      

    • 四，id列上没有索引，RC隔离级别

      若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL_Server层面进行的。 因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化， 对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁， 但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束

    • 五，id列是主键，RR隔离级别

      • 与组合一相同

    • 六，id列是二级唯一索引，RR隔离级别

      • 与组合二相同

    • 七，id列是二级非唯一索引，RR隔离级别

      Repeatable_Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete_from_t1_where_id_=10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁， 然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。 直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束

    • 八，id列上没有索引，RR隔离级别

      在Repeatable_Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP 杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent_read，来缓解加锁开销与并发影响 但是semi-consistent_read本身也会带来其他问题，不建议使用
      READ COMMITTED隔离级别和关闭nodb_locks_unsafe_for_binlog还有另外一个负作用：MySQL会释放掉不匹配Where条件的记录锁。例如，对于UPDATE语句，InnoDB只能进行“半一致性(semi_consistent)读”，所以，它会返回最新提交事务所做改变，从而产生不可重复读和幻像问题。

    • 九，Serializable隔离级别

      在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读

    • lock in share mode\for update

      锁住返回的记录，通过索引查询，锁住索引相关记录，全表扫描时，锁住所有记录。

    • insert 只对插入记录加锁

  • 当前读时，根据where条件过滤，确定存储引擎返回给server的记录，该记录需加锁

  • 当两个insert操作，对索引的同一间隙范围插入数据时，并不会造成阻塞，称为插入意向间隙锁

• 事务隔离级别

  • 读未提交

    • 总是读取最新版本，可能脏读、不可重复读、幻读

  • 读提交

    • 使用MVCC

    • 增删改操作提交后，对其它事务可见，可能不可重复读、幻读 若一事务未提交前另一事务便查询，数据来自回滚段

      RR隔离级别下，一致性读读取记录的最新版本，若最新版本被锁（有事务在操作未提交），则读取回滚段最新版本，回滚段数据不存在有事务在操作

  • 可重复读（默认级别）

    • MVCC

      • 每个事务开始时InnoDB分配事务ID，ID自增 读快照不加锁 读当前版本加锁 insert、update、delete均为当前读 读（select）不加锁，读写不冲突，并发性提升

      • 增

        • 更新版本号 = 当前事务ID

      • 删

        • 删除版本号 = 当前事务ID

      • 改

        • 更新版本号 = 当前事务ID
        • 将更改前数据复制到回滚段，版本同样 = 当前事务ID X？

      • 查

        • 更新版本号 <= 事务版本号 && 删除版本号未定义 或> 事务版本号
        • 若无法返回最新记录，可能返回回滚段数据
        • 一致性读，同一事物多次读，读取的是第一次读的快照版本

    • 插入操作提交后，对其它事务可见，可能幻读

    • 间隙锁

      • 锁住满足查询条件存在和不存在的记录（防止增删），避免幻读

        MVCC在select（读快照）的时候，可以通过版本筛选，避免幻读（大于当前事务ID不返回数据），为什么需要间隙锁？因为在类似当前读如select...for update的时候，读取当前版本，会产生幻读。

      • 但如当另一个事务插入一个记录，本事务也要插入一个相同主键的记录时，会报错，可见较串行化而言幻读避免并不完全

  • 串行化

    • 总是读取最新版本，所有操作加锁代替MVCC，读写冲突，并发性降低

• where 条件过滤

  • Index Key，决定间隙锁

    • Index First Key

      • 确定索引查询的起始范围

    • Index Last Key

      • 确定索引查询的终止范围

    • 存储引擎使用索引过滤

  • Index Filter

    • Index Key 范围中需满足的条件
    • 5.6之前，在MySQL_Server过滤 5.6之后，支持Index_Condition_PushDown，存储引擎使用索引过滤

  • Tab Filter

    • 不属于索引列的查询条件
    • 在MySQL_Server过滤

MyISAM

• 索引

  • 不支持聚簇索引
  • 主键索引-叶子节点保存行号（指针）
  • 普通索引-叶子节点同样保存行号
  • 区别：主键索引仅是在普通索引上要求唯一非空

• 锁

  • 仅支持表锁

索引

特点

• 加快select速度，降低写改删速度

类别

• 聚簇

  • 特点

    索引项的顺序与表中记录的物理顺序一致。叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。 聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。

  • 插入数据

    插入操作根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。 如果数据页已满，则需要拆分数据页，调整索引指针（且如果表还有非聚集索引，还需要更新这些索引指向新的数据页）。 而类似于自增列为聚集索引的，数据库系统可能并不拆分数据页，而只是简单的新添数据页。

  • 删除数据

• 非聚簇

  • 特点

    • 表数据存储顺序与索引顺序无关。叶结点包含索引字段值及指向数据页数据行的逻辑指针。 非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

  • 插入数据

    • 数据行没有特定的顺序，所有的新行将被添加到表的末尾位置。

  • 删除数据

• B-Tree

• Hash

• 组合索引

  • 假设存在组合索引index_i1 c1c2(c1,c2)， select * from t1 where c1=1 and c2=2 能够使用该索引。 select * from t1 where c1=1 也能够使用该索引。但是，查询语句 select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。
  • 实质就是根据前面的where条件过滤提取

• 覆盖索引

  • 当需要查询的数据列全部包含在一个组合索引中时，MySQL仅使用索引就可查出数据，无需通过索引二次查询

索引排序

1、ORDER BY的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化...

集群

读写分离

master-slave

分区

范围分区

哈希分区

count(*)

innodb遍历全表得出长度，采用行锁，若维护一个需要同步修改的rows，行锁就无意义

myisam采用表锁，修改rows同步无压力

命令

不可重复读VS幻读

不可重复读

• 同一事务中，多次查询结果，行数相同，值不同

幻读

• 同一事务中，多次查询结果行数不同

在当前读时，存储引擎返回给Server的记录需要加锁 所以在不能使用索引时，将全表搜索，返回所有记录给server，导致所有行加锁

在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的