[MySQL]知识点整理

都在酒里
316 阅读4分钟

隔离级别

SQL中定义了4种隔离级别：

  1. READ-UNCOMMITTED: 读取未提交；最低的隔离级别，假如有A、B两个事务，A事务能够读取B事务中未提交的数据。会导致脏读、不可重复读和幻读的问题。
  2. READ-COMMITTED: 读取提交；事务中只能读取其他事务提交的数据。可以解决脏读的问题，但是仍然会导致不可重复读、幻读。
  3. REPEARABLE-READ: 可重复读；在一个事务中，对相同数据多次读取的结果是一致的。可以解决脏读、不可重复读的问题，但是仍然无法解决幻读的问题。
  4. SERIALIZABLE: 可串行化，最高的隔离级别。所有事务，按照顺序依次执行，能够解决脏读、不可重复读、幻读的问题，但是销量比较低。

MySQL InnorDB引擎默认的隔离级别是 REPEATABLE-READ ，可以通过命令SELECT @@tx_isolation;查看。

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里需要注意的是，跟SQL标准不同的是在于InnoDB 存储引擎在 REPEATABLE-READ事务隔离级别下已经使用GAP间隙锁避免了幻读的产生。所以说使用InnoDB引擎默认支持的隔离级别（REPEATABLE-READ）就 已经可以完全保证事务的SERIALIZABLE隔离性要求。 因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ并不会有任何性能损失。

参考资料

  1. MySQL重要知识点/面试题总结

索引

索引分类

按底层结构分类：

  1. B+树：MySQL InnorDB引擎主键使用的是B+树作为索引的底层实现。

  2. B-树：MySQL InnorDB引擎其他索引通常使用的B-tree作为索引的底层实现。

  3. Hash索引

    B+tree：
    B-tree：
    Hash索引：

聚簇索引、非聚簇索引

什么是覆盖索引？

索引的最左匹配原则

MySQL中的锁

锁的分类

  • 按使用方式分：乐观锁、悲观锁。
  • 按粒度分：
    1. 表级锁：开销小、加锁快速。锁定范围大，锁冲突概率比较高，并发能力不好。
      • 表共享锁
      • 表独占锁
      • 意向读锁(IS)
      • 意向写锁(IX)
    2. 页级锁
    3. 行级锁：开销大、加锁速度慢。锁定粒度小，锁冲突发生概率比较小，并发能力比较好。
      • 共享锁(S锁)
      • 排他锁(X锁)

MySQL中，不同的存储引擎所支持的锁是不一样的。MyISAM只支持“表锁”，而InnorDB既支持“表锁”也支持“行锁”。值得注意的是，InnorDB的行锁是基于索引的，也就是说SQL执行的时候如果没有命中索引，就会使用“表锁”。

GAP间隙锁

当我们使用范围而不是通过等值的形式进行检索数据，并请求共享或排他锁时，InnoDB会给所有符合范围条件的的已有记录的索引加锁；同时会对索引值在“范围”内、却不存在的记录也加锁，这个锁就叫做GAP间隙锁。

注：GAP间隙锁只会在REPEATABLE-READ隔离级别下才能生效。

e.g.假如存在以下数据表student：

id name sex birthday
1 张三 M '1991-09-09'
2 李四 F '1992-01-10'
6 王五 M '1990-10-01' 
SELECT *
FROM `student`
WHERE id > 3 for update

如果执行以上SQL文，InnoDB不仅会对id = 6的记录行加锁（实际对索引加锁），还会在间隙(3,5)、(7,+∞)加上锁。

死锁（Dead Lock）

当产生“死锁”的时候，数据库会通过自动回滚优先级比较低的事务来解决“死锁”问题。在并发很高的情况下，往往是无法完全避免“死锁”，但是能够通过优优化来降低“死锁”发生的概率。

  1. 以固定的顺序访问表和行。
  2. 尽量缩小事务范围。大事务需要锁定的资源往往更多，更加容易产生死锁。
  3. 在同一个事务中尽量一次性锁定所有资源。（这个好像在SQL中不太好实现）
  4. 降低隔离级别。如果业务允许可以使用READ-COMMITTED替代REPEADABLE-READ，这样可以避免很多GAP锁冲突。
  5. 为表添加合理的索引，减小锁冲突的概率。

参考资料 数据库两大神器【索引和锁】

avatar
文章
阅读
粉丝