MySQL技术

一、MVCC多版本并发控制：实现隔离性，解决读-写冲突（无锁并发控制）

⭐⭐背诵版：

• 1） 多版本并发控制（MVCC）是一种用来解决 「读-写冲突」 的 「无锁并发控制」 ，同时也是实现 「隔离性」 的机制。它为事务分配单向增长的时间戳，为每个修改保存一个版本存储在undo log中，「读操作只能读到该事务开始前的数据库的快照」。

• 2） 通过MVCC机制，能够解决 「快照读」 下的 「脏读、幻读、不可重复读」 等问题，但不能解决第二类更新丢失问题；

• 3） MVCC的实现原理主要依赖于：
    ① 聚簇索引记录中的 「3个隐式字段」：隐藏的自增id、最后一次修改记录的事务id、指向undo log中上个版本的回滚指针
    ② 记录着历史记录版本链式结构「undo log日志」
    ③ 以及 根据可见性判断决定当前快照读能够读到的版本的读视图「Read View 」 来实现的；

• 4） MVCC机制只在 「读已提交」 和 「可重复读」 的隔离级别下生效；在 「读已提交」 的隔离级别下，当前事务的每次快照读都会生成一个新的Read View， 这就保证了当前事务一定能通过可见性判断，读到其他事务提交的最新数据；而在 「可重复读」 的隔离级别下，Read View 只在当前事务第一次快照读的时候创建 ，后边再快照读时，读的都是第一个Read View ，保证当前事务能够重复读取数据；

MVCC决定当前快照读能读到的版本的步骤如下：

---

1、MVCC解决的是什么问题？

数据库并发有三种场景：

  1）读-读： 不存在任何任何问题，不需要考虑并发控制；

  2）读-写： 有线程安全问题，还要考虑事务隔离性，可能会出现脏读、幻读、不可重复读等问题；而加锁虽然能够解决读-写冲突，但是性能太差。因此引入了一种MVCC机制，可以实现无锁并发，实现事务隔离性，解决脏读、幻读、不可重复读等问题；但是不能解决更新丢失问题；

  3）写-写： 有线程安全问题。采用加锁和乐观锁的机制可以解决写-写冲突；

  通过MVCC 乐观锁的机制（MVCC解决读写冲突，乐观锁解决写写冲突），能够最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题；

---

1）MVCC能解决幻读问题吗？

• 1） 当前读下MVCC无法解决幻读问题；需要用临键锁解决；
• 2）快照读下，读已提交 MVCC无法解决幻读；原因是： 读已提交隔离级别下，MVCC每次快照读都会创建一个新的读视图ReadView，用于保证本次快照读能够通过可见性判断，进而读到其它事务已经提交的结果，此时【会出现幻读问题】；
• 3）快照读下：可重复读隔离级别 MVCC能解决幻读问题；原因是： 可重复读隔离级别下，MVCC机制的ReadView，只在第一次快照读的时候创建，后续读到的都是第一次的ReadView（可重复读），保证不能通过可见性判断，进而每次都读取历史版本而不读最新版本，因此【不会出现幻读问题】；

---

2、MVCC的实现原理

---

3、举例说明MVCC的执行过程

persion表中有一条记录，name为Jerry，age为24岁，隐式主键是1。

1）初始没有事务修改，也没有历史版本，事务ID和回滚指针均为NULL

2） 事务1将表中name字段Jerry的改为Tom

  ① 在事务1修改该行(记录)数据时，数据库会先对该行加排他锁；

  ② 然后把该行数据拷贝到undo log中，作为当前新记录的历史版本；

  ③ 修改数据，事务id字段存储事务1的id，回滚指针指向undo log的历史版本数据；

  ④ 事务1提交后，释放锁；

3) 又来了个事务2修改person表的同一个记录，将age修改为30岁

  ① 在事务2修改该行(记录)数据时，数据库会先对该行加排他锁；

  ② 然后把该行数据拷贝到undo log中，发现undo log中已经有数据，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面

  ③ 修改数据，事务id字段存储事务2的id，回滚指针指向undo log的历史版本最新数据；

  ④ 事务2提交后，释放锁；

---

4、Read View 可见性判断算法过程

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，可能是最新数据，也可能是undo log 中某个历史版本的数据；

Read View维护了维护系统当前活跃（未提交）事务的id列表，它有三个属性

我们现在拿着最后一次修改记录的事务id（DB_TRX_ID）去进行比较；

Step1~~Step3的循环判断，直到找到当前事务快照读能读到的记录版本；

---

二、数据库的锁

1、MySQL锁机制

⭐记录锁、间隙锁、临键锁的底层

⭐ 幻读问题，比如数据库中有3条记录

注意：幻读指的是前后两次查询同一个范围；

① 如果我们想查询id≥2的张三，那么首先for update会将在 id≥2范围内的所有记录加锁，其它事务就不能修改这个范围内的数据了；

• select * from user where id≥2 and name = “张三” for update;

② 为了防止其它事务操作间隙，比如其他事务想插入一条数据：

• insert into (id,name) values(3,“张三”)；

单纯用记录锁是解决不了该问题的，因此还要加上间隙锁，使得其它事务不能通过id=1(举例)通过next指针访问到id = 3的间隙，阻止其插入的动作；

---

2、悲观锁与乐观锁

注意：悲观锁与乐观只是一种思想；

---

3、如何避免死锁？

---

三、数据库事务

1、事务的ACID是怎么保证的？

---

2、事务的隔离级别?

---

3、快照读与当前读

---

4、脏读、幻读、不可重复读、更新丢失问题

数据库第二类更新丢失的问题：

  第二类数据丢失的问题是关于多个事务同时更新一行数据导致的问题，如下表所示，事务A和事务B都更新一行数据，他们事务开始的时候都查询到账户有1000元，然后都往账户添加了100元，最后大家都提交了各自的事务，结果却是错误的。

---

四、数据库的日志系统

1、四种日志总结

---

2、bin log 日志 与 redo log 日志?

1）逻辑日志 bin log

• 作用： 逻辑日志bin log的作用是记录数据库表和表数据的修改操作，它有三种记录模式：基于SQL语句模式的statement、基于行的模式row以及混合模式；
• 工作层： MySql中的Srevice层上，可以结合任意存储引擎使用
• 空间大小： 无限
• 记录模式： 基于SQL语句的模式、基于行的模式、混合模式

---

2）物理日志redo log

• 作用： 物理日志redo log的作用是实现数据库的持久化；Mysql中有一个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都太高。因此，MySql引入了WAL技术（Write-Ahead Logging），关键点就在于先写入redo log日志然后同步更新内存，InnoDB 引擎在系统空闲的时候再写入到磁盘中持久化，提高效率；
• 工作层： InnoDB存储引擎特有的
• 空间大小： 空间大小固定，循环写入，当空间不够时，就需要先持久化到磁盘，然后空出来的部分再写入

---

3）bin log 日志与 redo log 日志的区别？

---

4）bin log 日志与 redo log 日志如何保证一致性：二阶段提交（2pc）

阶段一：二阶段提交

redo log 和 binlog 有一个共同的数据字段，叫 XID；

---

阶段二：数据恢复：

⭐若在上述二阶段提交的过程中，数据库宕机了；那么在MySql重启时，会根据 redo log的标识 与 bin log的二阶段XID字段 进行数据恢复，判断是否应该提交[宕机时未能提交的事务]以保证redo log 与 undo log 中数据的一致性；

⭐ 恢复关键点：redo log 与 bin log 是否全部写入成功；

  原因：redo log与bin log的执行过程是在事务的执行过程中进行的，实际上当redo log 与 bin log 都写入成功时，事务就已经真正意义上的提交了，因此数据恢复时，是否提交[由于宕机未能提交的事务]关键就在于：redo log 与 bin log 是否都写入成功；

---

5）MySQLl的执行查询过程？

注意：查询缓存现在已经没有了，因为缓存命中率低；

---

五、MySQL主从复制

1、什么是主从复制?

  MySQL主从复制指的是，数据可以MySQL的主服务器（master）复制到其它的从服务器（slave）中；MySQL默认采用异步复制的方式，因此从服务器可以不用一致连接着主服务器，甚至可以用拨号的方式断断续续的连接主服务器；根据配置文件，从服务器可以指定复制主服务器中全部或者部分的数据；作用：通过主从复制，可以实现读写分离（主写从读）以及数据备份，提高数据库的访问性能以及数据安全性；

---

2、主从复制的作用？

• 1）读写分离，提高数据库访问性能； 主(写)从(读)，减少主服务器的访问压力；
• 2）数据备份，提高数据安全性； 从服务器可多地备份主服务器的数据，且能随时终止复制流程，不破坏主服务器的数据；

---

3、主从复制的流程（原理）？

主从复制过程的关键点：

⭐主从复制的过程：

---

六、MySQL索引

1、MySQL中的两种存储引擎

---

2、为什么索引数据结构使用B 树？

1）索引的数据结构总结

---

2）B树与B 树的查询过程

⭐2.1）B树的查询过程

   注意：① B树的每个节点都存储着数据data；
      ② 一次IO就是读取一个16KB大小的磁盘块（数据页）

16KB的数据可以改，调整参数innodb_page_size；

1）以查询 key值为【29】的数据data为例：

2）以查询key值为【64】的数据data为例：

---

⭐2.2）B 树的查询过程

   注意：B 树只有叶子节点存储着数据data，其它节点只存储叶子节点的key值；因此在查找时，要先从非叶子节点中获取key值，然后再去叶子节点中获得数据data

1）以查询 key值为【15】的数据data为例：

2.3）B 树的两种查询方式

• 在B Tree上有两个头指针，一个向指向根节点。另一个指向主键key最小的叶子节点。且所有叶子节点（数据节点）之间是一种链式环型结构。因此B 树有两种查询方式：
• 1）从【根节点】开始进行开始【随机查找】；
• 2）从【主键最小的叶子节点】开始进行【范围查找和分页查找】

---

3）为什么B 树只在叶子节点中存储数据，非叶子节点只存储key？

① 通常情况下，数据库的B 树高度在3~4层左右，已经能够存储千万级别的数据量了；

②能够当作key值的类型的优先级：主键索引 > 唯一索引 > 6个字节的row_id

⭐原因：

• 总： B树与B 树1次读取就是与磁盘的1次IO交互，而磁盘(数据页)占用16KB的空间；也就是说，如果16KB的空间能够存储的key值（主键值）范围越大，那么我们1次IO交互就能读到的数据量越多；换句话说，在数据量固定的情况下，磁盘能存储的key值范围越大，我们就能减小IO交互次数，提高IO读写效率；
• 分： ① B树的每个16KB的磁盘节点（可以调整参数innodb_page_size）中，存储着三个类型的数据：【key值（主键）、value值（查询的数据）、指针（存储下一个磁盘节点的地址信息）】，而这些数据中（查询的数据）占用的空间最大，导致剩余能够存储key值范围的空间就很少；
    ② 而B 树只会在叶子节点层进行存储（查询的数据），而非叶子节点层只需要存储叶子节点的key值与指针，而且一旦可以不存储data数据（data数据占主要的16KB极大部分的内存），那么相同高度下就能够比B树存储更多的key值范围；这样1次IO交互读取的内容就越多，从而减少数据库IO交互次数，提高数据库的IO读写效率；

4）索引主键key的存储类型是用int还是varchar？

• 综上分析，在一个16KB的磁盘中，主键key占的字节越少，那么能存储的key范围就越大；因此用int还是varchar就看哪个占的字节小；int（4个字节），① 当varchar占的字节小于4个时，推荐用varchar ② 反之，则用int类型；

---

以一颗高度为3的B树与B 树举例说明上述原因：

• ⭐B树： key值（主键）和 指针（存储下一个磁盘节点的地址信息）所占的空间非常小，我们假设忽略；并且假设value值（查询的数据）占1KB的空间，那么一个磁盘就只能包含16 / 1 = 16个key-value对，这个16也被称之为节点的度（Degree）；那么3层的高度，就可以存储 16 × 16 × 16 = 4096 个数据，如图所示：
  

---

• ⭐B 树： 由于B 树只在叶子节点层存储数据，其它层都不存数据；因此其它层能够存储的key值范围就越大；B 树在非叶子节点层只存储两个类型的数据：【叶子节点中的key值，指针（存储下一个磁盘节点的地址信息）】，我们就假设这两个类型的数据占10个字节，那么16KB的磁盘数据，就能存储 16 × 1024 / 10 = 1638个key值；那么高度为3，已知最后一层可以存储16个数据；那么总共能存储：1638 × 1638 × 16 = 42928702 个数据，已经达到了千万级别，是B树所能存储数据的1万倍；

  图略：B 树的图就略了，原理与上边B树的一样；

  一句话总结原因： B 树通过只在叶子节点存储数据的方式，能够大大的提高一个磁盘（数据页）中存储的key值数量；因此存储相同的数据量时，B 树比B树的高度要低的多；在查询数据时，B树由于每个叶子节点都存放了数据，因此总的IO次数(读取磁盘)是不一定的，要看目标数据离根节点是否近，如果离着近，那么IO次数是比B 树少的；但是如果是在叶子节点，那么B树高度又高，磁盘IO次数会非常多，远超过B 树；而B 树的只在叶子节点存放数据，这就保证了每次IO的次数固定，IO次数等于树的层数，而且B 树的高度又低，通常3-4层已经能够应对千万级别的数据了，所以总的IO效率B 树更好；

---

3、聚簇索引与非聚簇索引

1）为什么聚簇索引的key值推荐使用自增主键？

• ① 自增主键的区分度为1，检索效率高；

• ② 由于自增主键是连续的，因此在插入或者删除过程中，只需要追加到最后；能够减少数据的移动，减少页分裂和移动的频率

• 解释： 每次向B 树添加或者删除节点时，都会影响到B 树的整体结构或者高度的变化（当达到某个磁盘块（数据页页）的最大值时，就会向上分裂，树的高度会变化）；如果不适用自增主键，那么B 树每次插入和删除都要判断是否要分裂，维护麻烦；反之如果使用自增主键id，那么我们只需要向叶子节点后按顺序追加到最后即可，当达到磁盘页的最大值时，再进行分裂即可，维护方便；

2）分布式情况下还能用自增主键充当B 树的叶子节点key吗？

• 不能用自增主键；
• 分布式数据库，B 树的key需要用雪花算法或者分布式id生成器；

---

4、回表查询

1）什么是回表？

   ① 数据只在聚簇索引中的叶子节点中存储，非聚簇索引只存储【自己本身的索引字段数据 聚簇索引中叶子节点的key值】；

   ② 一个表中可以含有多个字段充当索引， 但是能够当作聚簇索引中的key值的只有3种：主键索引 > 唯一索引 > 6个字节的row_id ；除了这些，其它索引都属于非聚簇索引；

   ③ 因此，当我们使用非聚簇索引进行查询时，首先要查询非聚簇索引的B 树。然后判断是否非聚簇索引自己本身的字段已经能够命中想要查询的全部内容（覆盖索引）；1) 如果能覆盖，则不需要再去聚簇索引的B 树中查询（回表）；2) 如果不能覆盖，则需要拿着从非聚簇索引中查询的key值，再去聚簇索引的B 树中查询；

2）回表的几种情况分析

三种查询的执行流程：

• 1）根据主键索引（聚簇索引）查询：不需要回表

    ①SQL语句： select * from table where id = 5；

    ②分析： 直接查询聚簇索引的B 树结构，索引与数据放在一起，直接命中要查询的内容，无需回表；
  

---

• 2）根据二级索引（非聚簇索引）查询

  2.1）不需要回表的情况：二级索引覆盖

    ①SQL语句： select name,id from table where name = 李四；

    ②分析： name是二级索引（非聚簇索引），先查询非聚簇索引B 树，查到了数据（name = 李四，id = 5），此时发现要查询的内容全部命中，那么可以不用去聚簇索引中回表查询；
  

  2.2）需要回表的情况：二级索引没有覆盖

   ① SQL语句： select * from table where name = 李四；

   ② 分析： name是二级索引（非聚簇索引），先查询非聚簇索引B 树，查到了数据（name = 李四，id = 5），此时发现要查询的内容没有全部命中，那么就根据 id = 5 去查询聚簇索引B 树，最终得到数据（id = 5，name = 李四，age = 25，sex = 男） ；
  

3）索引下推

• 索引下推的作用：提高非聚簇索引的查询效率，减少回表次数：
• ① 在MySQL5.6之前，当使用非主键联合索引进行查询的时，存储引擎在非主键索引B 树中查询数据之后，然后将结果返回给MySQL的Server层，在Server层判断是否符合条件（即回表）。
• ② MySQL5.6之后，推出了索引下推技术，判断是否符合条件的这一步是在存储引擎层中实现的；存储引擎会直接根据索引条件过滤掉不符合的结果，然后回表查询得到结果，将结果返回给MySQL server。

例子：(name，level)是联合索引，查询下表

SELECT address FROM user_info WHERE name LIKE "大%" AND level = 1;

1）在MySQL5.6之前，没有索引下推；

2）在MySQL5.6之后，开始索引下推技术

---

5、最左前缀原则

最左前缀原则与B 树的匹配原理相关，从建立的联合索引顺序从左到右匹配；

例子：表table建立了联合索引(a,b,c)；

• 1）MySQL的索引顺序必须按照从左往右排序；

1、不会走索引的情况：跳过索引a
	select * from table where b = "1";
	select * from table where c = "1"; 
	select * from table where  b = "1" and c = "2"; 	
	
2、走索引的情况

  1)全部索引都存在，但是乱序。优化器可以优化
	select * from table where c = "1" and b = "2" and a = "3"; 
	
  2) 部分索引顺序是相对从左至右的，也可以走索引
	select * from table where a = "1" and  c = "3"; 
	select * from table where a = "1" and  b = "3"; 

• 2）MySQL会一直向右匹配直到遇到范围查询就停止匹配索引；

1、不会走索引的情况

	1) 遇到范围查询（in、between、>、<）
	select * from table where a = "1" and b > "2" and c = "3";// c不走索引
	select * from table where a in ("1","2") and b = "2" and c = "3";// b、c都不走索引 		

• 3）MySQL遇到不符合最左前缀原则的模糊查询，也不会走索引

1、不会走索引的情况

	1) %在前 或 %%
	select * from table where a like %张 and b = "1" and c = "2";// a,b,c都不会走索引，全表扫描
	select * from table where a like %张% and b = "1" and c = "2";// a,b,c都不会走索引，全表扫描  

1、走索引的情况

	1) %在后
	select * from table where a like 张% and b = "1" and c = "2";// a,b,c都走索引；
	//a的索引类型为index或者range（由优化器决定）

---

6、索引失效的几种情况

---

七、MySQL调优，解决慢查询

问到如何调优的万能回答：

这篇好文章是转载于：学新通技术网