MySQL 硬核知识点总结

目录

1. MySQL 的索引
2. 索引的数据结构
3. MySQL 有哪些存储引擎？区别是什么？
4. 事务的四个特性
5. 事务的隔离级别
6. MySQL 用了哪种默认隔离级别，实现原理是什么？
7. MySQL 中的锁

1. MySQL 的索引

• 索引是什么？

  帮助MySQL高效获取数据的数据结构，常见的有主键索引、唯一索引、普通索引、组合索引、全文索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）。

• 索引的优劣势？

  优势：

  • 提高检索效率，减低磁盘 IO 的成本。
  • 通过索引列对数据排序，可降低数据排序的成本和 CPU 的消耗。

  劣势：

  • 占据磁盘空间
  • 做增删改操作时，会降低更新表的效率

• 索引类型

  • 主键索引：索引列的值唯一，不允许有空值
  • 唯一索引：索引列的值唯一，允许有空值
  • 普通索引：没有特殊限制，允许重复值和空值
  • 组合索引：即多个数据列组成的索引，B+树会按照从左到右的顺序建立搜索树，比如当(name, age, sex) 来查询，B+树会优先对比 name 来确定下一步搜索方向。组合索引需要遵循最左匹配原则（即构造索引时生成的B+树，从索引列的最左字段开始构造，一直到最后的字段）。当通过组合索引（A，B，C）查询数据时，条件里面存着（AB）或者（AC），都可以走索引查询数据，但查询条件只有（BC）时，不会走索引。一般，在条件允许的情况下，可将组合索引替代多个索引列使用。
  • 全文索引：只能在文本类字段 CHAR, VARCHAR, TEXT 上创建，一般用于字段长度比较大时，创建普通索引在做 like 查询时效率低，此时可以创建全文索引。

2. 索引的数据结构

• Hash表：以键值对的形式存储，Key 为索引列，当索引为聚簇索引时，Value 为存储的行记录；非聚簇索引时，Value 存储磁盘地址。Hash 做范围查询时可能会扫描全表，效率低下。只适合等值查询，可以通过 O(1) 的时间复杂度直接获取数据。

• B树：

  • 每个节点存储多个元素，是平衡多路查找树
  • 节点中包含键值和元素，节点中的键值从小到大排列
  • 父节点中的元素不会出现在子节点中
  • 叶子节点位于同一层，且具有相同的深度，叶节点之间没有指针连接

• B+树与B树有哪些优势？

  • B树不支持范围查找，每次查询时需要重新回到根节点进行查找，效率很低
  • 中间节点存储的数据可能是行记录，当数据表的字段很多时，行记录所占的空间会变大。此时，一个页中存储的数据量就会变少，树的高度就会变高。磁盘IO次数随着变多，查询效率就变低了
  • B+树是B树的升级版，主要有两个改动：一是将数据全部移到叶子节点上保存，中间节点只存储键值；二是让叶子节点通过链表连接起来，因为叶子节点的数据都是有序的，所以在范围查询的时候可以顺着链表往下走，就可以直接返回一段数据，进而减少IO次数，提升查询性能。

3. MySQL 有哪些存储引擎？区别是什么？

• MyISAM：表结构、数据和索引分别放在 frm、myd 和 myi 文件里。索引结构用的是 B+ 树，但 MyISAM 用的是非聚簇索引，叶子节点保存的是行记录的地址。MyISAM 不支持事务，所以存储速度较快。 如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。
• InnoDB：MySQL默认的存储引擎，支持事务，外键以及行级锁。数据的物理组织形式时聚簇表，所有的数据按照主键来组织。数据和索引放在一起，都位于 B+ 树的叶子节点上，这意味着通过主键做等值查询时可以直接返回数据。InnoDB 还支持缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。InnoDB 的表结构放在frm文件，数据和索引放在 ibd 文件中。
• Memory： 将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是 frm。Memory 默认使用 Hash 索引，默认为表级锁，这意味着访问量大时，很容易出现性能问题。由于它的数据是放在内存中的，一旦服务器故障，数据就会丢失。

4. 事务的四个特性

• 事务是 InnoDB 存储引擎的一个特质，它的四个特性是原子性、一致性、隔离性和持久性，即 ACID：

  • 原子性（Atomicity）：事务包含的操作要么全部成功，要么全部不成功。比如 A、B账户的初始余额为500元，100元。此时，A 向 B 转账 100 元，那么分解开来就是 A 账户减 100 元，B 账户加 100 元。最终结果是 A 账户余额为 400 元，B 账户余额为 200 元。这两个账户余额更新的操作，要么全部执行，要么都不执行。
  • 一致性（Consistency）：事务执行前，和执行后都会保持一致性状态。A、B 账户在转账前和转账后，账户的总额都应该为 600 元，这就是前后一致性。
  • 隔离性（Isolation）： 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 A 和 B 转账的时候，不管别人怎么转账，都不会影响他们的交易。
  • 持久性（Durability）： 一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
  • 其实，事务的其他三大特性，都是为一致性服务的。一致性是事务最终需要呈现的结果，其余三个特性是保证一致性的关键。

5. 事务的隔离级别

• RU - 读未提交（Read uncommitted）：如果一个事务开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读取此行数据。RU 可以通过“排他写”，但是不排斥读线程实现。这种隔离级别可能会出现脏读，即事务 B 读取到了事务 A 未提交的数
• RC - 读已提交（Read committed）：该隔离级别在一个事务进行数据写入时，不允许别的事务对该行数据进行访问（包括读写），这样就可以保证事务读到的数据一定是已经提交了的，解决了脏读的问题。但是会出现不可重复读的问题，比如：事务 A 需要读取两次数据，在读取完第一次数据后，有另一个事务 B 对该数据进行的更新并提交事务，此时事务 A 再次读取该数据时，数据已经发生了改变。
• RR - 可重复读（ Repeatable read）：在同一个事务内，多次读取同一个数据，在这个事务还未结束时，其他事务不能访问该数据（包括读写）。这种隔离级别下解决了脏读和不可重复读的问题，但是可能会出现幻读，如事务 A 在多次读取数据时，有另一个事务 B 在数据行中间插入或删除了数据，此时事务 A 再次读取时，可能会发现数据的行数变了。简单来说，RR - 可重复读可以保证当前事务不会读取到其他事务已提交的 update 操作，但无法感知其他事务的 insert 和 delete 操作。
• 可串行化（Serializable）：该隔离级别下，事务只能依次执行，解决了脏读、不可重复读和幻读的问题。但是代价较高，性能很低，一般很少使用。

6. MySQL 用了哪种默认隔离级别，实现原理是什么？

• MySQL 的默认隔离级别是 RR - 可重复读，我们可以通过命令来查看 MySQL 中的默认隔离级别，命令自行百度，这里就不给出了。

• RR - 可重复读是基于多版本并发控制（Multi-Version Concurrency Control，MVCC ）实现的。MVCC，在读取数据时通过一种类似快照的方式将数据保存下来，不同事务的 session 会看到自己特定版本的数据，这样读锁和写锁就不冲突了。

• 在 InnoDB 存储引擎里，在有聚簇索引的情况下，每行数据都包含两个必要的隐藏列：

  DB_TRX_ID：记录某条数据的上次修改它的事务ID（trx_id）
  DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本。我们每次对聚簇索引行进行修改时，都会把老版本写入到undo日志里，这个指针就指向了老版本的位置，当需要进行回滚操作时，事务就通过回滚指针以获取上一个版本的数据（注意：插入操作的undo日志没有回滚指针，因为它是新增的数据，没有老版本；而已删除的信息会在undo日志记录的头信息中存一个delete flag标记，当该标记为true时，表示已删除，则不返回数据）。
  

• 下图就是一个简洁的版本链概念，InnoDB 中的 undo 日志保存的就是一个版本链：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dtVwSEU1-1628352241513)(img/版本链.png)]

• 除了版本链，我们在实现 MVCC 还用到了另一个概念：read-view，一致性试图。我们在查询数据，当使用 select 语句时，InnoDB 会自动生成一个当前活动的（即未提交的）事务 ID 数组，这个 read-view 就是由查询时所有未提交事务 ID 组成的数组。数组中最小的事务 ID 为 min_id 和已创建的最大事务 ID 为 max_id 组成，查询的数据结果需要跟 read-view 做比较从而得到快照结果。

  我们做查询时，会查询出当前 session 的 trx_id，通过和 read-view 比对：

  1）若 trx_id 比 read-view 中的 min_id 小，则该版本是已经提交的事务生成，一定可见；

  2）若 trx_id 比 read view 中的 max_id 大，则该版本是还未提交的事务生成，一定不可见；

  3）当 trx_id 在 read-view 列表中，即 min_id <= trx_id <= max_id时，如果 trx_id 在 read-view 的数组中，则还未提交，不可见，但是当前事务是可见的；如果 trx_id 不在数组中，表明是已经提交的事务，则该版本可见。

  当版本不可见时，需要通过 DB_ROLL_PTR 获取上一版本的 trx_id，再次比对，直到版本数据可见时，返回结果。

• 就以上比对的三种情况，用图示说明下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UscpajwJ-1628352241517)(img/read-view.png)]

​ 1）select 语句执行时，上次更新的 trx_id 为 100，read-view 中未提交的事务为 [101]。此时 read-view 的 min_id 为 101，trx_id 比它小，则该版本是已经提交的事务生成，所以返回 zhangsan。

​ 2）假设当前 select 的 trx_id 为 102，read-view 中未提交的事务为 [101]，则需要通过 DB_ROLL_PTR 获取上一版本的 trx_id 100，注意 trx_id 为 101 的事务是改变了另一张表的数据，所以 undo 日志里版本链指向的上一条数据 trx_id 为 100，还是会返回 zhangsan。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7t6pCKlo-1628352241519)(img/trx_id1.png)]

​ 3）当 trx_id 在 read-view 中间时：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S37QAsit-1628352241524)(img/trx_id2.png)]

​ 此时 trx_id 为 101，read-view 为 [101]，当前事务 ID 在数组中，所以不可见。需要用 DB_ROLL_PTR 找到上一条版本的位置 trx_id 为 100，还是会返回 zhangsan。

• RC 隔离级别在查询时，同一个事务多次查询，每次会生成独立的 read-view。而 RR - 可重复读只在第一次查询时生成统一的 read view，之后的读取都复用之前的 read view。而 RU - 读未提交是可以读取还没提交的数据，没有 undo 版本的概念；可串行化隔离级别在每次读取时都需要加锁控制，没法并发，所以通过版本的概念去控制并发也就没有意义。

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xq4BW60c-1628352241527)(img/RC与RR级别.png)]

  当使用 RC 级别时，两次 select 的 read-view 不一样，第一次查询时是 [101]，第二次是 [100, 101]。而用 RR 级别时，会复用第一次查询的 read-view，故多次查询的结果是一样的。这也是 MySQL 的隔离级别默认用 RR - 可重复读的原因之一，不用重复生成 read-view，提升数据库的操作性能。

• 总结，每次 select 数据时生成 read view 列表，再配合 undo 日志中的版本链，让不同的事务读-写，写-读操作可以并发执行，进而实现 MVCC。

7. MySQL 中的锁

锁是计算机为了协调多个进程或线程，并发访问某一资源的机制（避免资源争抢）

1）基于锁的属性，有共享锁（读锁）和排它锁（写锁）。

2）基于锁的粒度，有表级锁（InnoDB、MyISAM 支持）、行级锁（InnoDB 支持）、页级锁（BDB 引擎支持）、记录锁、间隙锁和临键锁，最后三种锁其实都是行级锁。

3）基于锁的状态分类：意向共享锁、意向排它锁。

• 共享锁：

共享锁，又称作读锁。当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，直到所有的读锁释放以后才能加写锁。共享锁就是为了保证数据读取的时候，该数据不被其他事务进行修改，避免出现脏读和重复读的问题。

• 排它锁

排它锁，又称作写锁。当一个事务为数据加上写锁之后，其他事务只不能对数据加任何锁，直到该写锁释放。排它锁的目的是在数据修改的时候，不允许其他事务同时修改，也不允许读取，避免出现脏读的问题。

• 表级锁（table lock）：

事务上锁的时候锁定整张表，当下一个事务访问该表时，需要等待前一个事务把表锁释放以后才能访问。表锁的锁粒度大，资源消耗少，加锁快，不会出现死锁，但出现锁冲突的概率最高，并发性能较低。

• 行级锁（row lock）：

行级锁，即锁定表数据的某一行或多行记录，其他事务访问同一张表时，除了被锁住的行记录不能访问，其余的记录可正常访问。它的锁粒度小，不容易发生锁冲突，可以最大程度的支持并发处理。当然开销也最大，加锁慢，且可能会出现死锁，同时行锁只在InnoDB存储引擎层进行实现。
注意：行锁在InnoDB中是基于索引实现的，一旦某个加锁操作没有使用索引，那么该锁就会退化为表级锁。

• 页级锁（page lock）：

页锁即一次锁定相邻的一组记录，是一种粒度介于行锁和表锁之间的锁，锁开销不大不小，会出现死锁，并发度一般。

• 记录锁（record lock）：

记录锁是属于行锁的一种，只不过记录锁的范围只是表中的某一条记录。
注意：记录锁命中的条件字段一定是唯一索引或主键列，同时查询条件需要精准命中，即=查询，不能为like, <, >等，否则会退化为临键锁。
加锁语句：
-- id 列为主键列或唯一索引列
SELECT * FROM table WHERE id = 1 FOR UPDATE;

• 间隙锁（gap lock）：

间隙锁也是行锁的一种，事务加锁后其锁住的是表记录的一个区间，即表记录相邻ID之间出现的空隙。
比如，表里的数据ID有1,5,15，加间隙锁后会形成以下几个区间：(负无穷, 1), (1,5), (5,15), (15,正无穷)
注意：间隙锁基于非唯一索引，查询条件必须未命中记录，同时间隙锁只会出现在RR-可重复读的事务隔离级别下。
加锁语句：
-- name 列为非唯一索引列，且name为zhangsan的id有1和10
SELECT * FROM table WHERE name = 'zhangsan' FOR UPDATE;
加了间隙锁后，所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

• 临键锁（next-key lock）：

临键锁，属于行锁的一种，是InnoDB中行锁默认算法。可以理解为是间隙锁和记录锁的结合，当事务持有某一行记录的临键锁时，会锁住一段左开右闭的数据。
比如，表里的数据ID有1,5,15，加临键锁后会形成以下几个区间：(负无穷, 1], (1,5], (5,15], (15,正无穷(mysql默认的一个不存在的最大值)]
注意：临键锁需要范围查询，且必须用到索引。临键锁在范围查询时，可以阻止其他事务在锁定区间内插入或删除数据，解决了脏读、不可重复读和幻读的问题。