MySQL数据库的常见考点

墨蓝 2024-03-27 11:24 141阅读 0赞

#### 文章目录 ####

*   *   *  1、ACID
         *   *  事务原理
             *  事务持久性
             *  事务原子性
             *  MVCC基本概念
             *  MVCC基本原理
             *  undo log
             *  undo log版本链
             *  readview
             *  MVCC实现原理
             *  RC读已提交
             *  RR可重复读
             *  MVCC实现原理
             *  总结
         *  2、并发事务引发的问题
         *  3、事务隔离级别
         *  4、索引
         *   *  4.1、索引概述
             *  4.2、索引优缺点
             *  4.3、索引结构
             *  二叉树
             *  B-Tree树
             *  B+Tree树
             *  B+Tree优化
             *  Hash索引
             *  面试题
         *  索引分类
         *   *  聚集索引和二级索引
             *  回表查询
             *  InnoDB主键索引的B+tree高度为多高呢？
             *  索引语法
             *  慢查询日志
             *  SQL性能分析
             *  explain执行计划
         *  共享锁
         *  排他锁

--------------------

#### 1、ACID ####

![image-20230201110516214][]

##### 事务原理 #####

![image-20230201183115209][]

##### 事务持久性 #####

![image-20230201184355749][]

##### 事务原子性 #####

![image-20230201184847275][]

##### MVCC基本概念 #####

![image-20230201185341645][]

##### MVCC基本原理 #####

![image-20230201185547843][]

##### undo log #####

![image-20230201193124540][]

##### undo log版本链 #####

![image-20230201193916668][]

##### readview #####

![image-20230201195741866][]

##### MVCC实现原理 #####

![image-20230201200202115][]

##### RC读已提交 #####

![image-20230201201859451][]

##### RR可重复读 #####

![image-20230201202757006][]

##### MVCC实现原理 #####

![image-20230201202953875][]

##### 总结 #####

![image-20230201203504311][]

#### 2、并发事务引发的问题 ####

![image-20230201111810443][]

#### 3、事务隔离级别 ####

![image-20230201112426384][]

**注意**：事务隔离级别越高，数据越安全，但性能越低

![image-20230201114502158][]

#### 4、索引 ####

##### 4.1、索引概述 #####

![image-20230201121618812][]

##### 4.2、索引优缺点 #####

![image-20230201121723698][]

##### 4.3、索引结构 #####

##### 二叉树 #####

![image-20230201122729877][]

##### B-Tree树 #####

![image-20230201124016293][]

##### B+Tree树 #####

![image-20230201125911225][]

##### B+Tree优化 #####

![image-20230201130137365][]

##### Hash索引 #####

![image-20230201155613366][]

**Hash索引的特点**

![image-20230201160329135][]

##### 面试题 #####

![image-20230201161040465][]

#### 索引分类 ####

![image-20230201162108409][]

![image-20230201162309066][]

##### 聚集索引和二级索引 #####

![image-20230201162522169][]

##### 回表查询 #####

![image-20230201162713316][]

##### InnoDB主键索引的B+tree高度为多高呢？ #####

![image-20230201163329831][]

##### 索引语法 #####

![image-20230201164342554][]

##### 慢查询日志 #####

![image-20230201170827235][]

##### SQL性能分析 #####

![image-20230201171916941][]

##### explain执行计划 #####

![image-20230201175550239][]

![image-20230201175947637][]

#### 共享锁 ####

共享锁，又称读锁、S锁。上厕所就相当于修改操作。当事务给行加了共享锁，此时行只能进行读操作，而不能进行写操作。别的事务也只能给其加共享锁，不能加排他锁。

#### 排他锁 ####

排他锁，又称写锁、X锁。当加了排他锁之后，就相当于把洗手间的门关上了，别人不能进去洗手、化妆之类的。也就是说不能进行写操作以及读操作，其他事务不能给其再加锁了。

对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。

所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

[image-20230201110516214]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/de371bef2a0148d49b48467c9430ed3a.png
[image-20230201183115209]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5e8439e51c234eb98c74b43592c5366d.png
[image-20230201184355749]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8ed5b85ac4a5434a973c73604dc006d0.png
[image-20230201184847275]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f473f1f7089f4a7d957f62dcdd04dcdb.png
[image-20230201185341645]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ada0979f67394a848c5528f558ada6bc.png
[image-20230201185547843]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d2d580e8c24c46d0bec1793abafb1c32.png
[image-20230201193124540]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/60f4471181fe44799a6fa550444a426b.png
[image-20230201193916668]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/52549eb9318d46efb44b4e7afee475ce.png
[image-20230201195741866]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e8842ccfb2044a15936f8f527fa79bdb.png
[image-20230201200202115]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e6a580b266cf4d8585eeb9e41c67bc04.png
[image-20230201201859451]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/eb06c9abbbe54d36b96dcdf3a5f757ca.png
[image-20230201202757006]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8faef67ef0b640249c9c412526bc439a.png
[image-20230201202953875]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/97e3337acb5d420a9b24c4dd8a91b976.png
[image-20230201203504311]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8c603a9bdae0484d968ba0a82e3b277c.png
[image-20230201111810443]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/62391c2196a74deba0cb3412d3480441.png
[image-20230201112426384]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/0c82e7529fba493ea537dd2aff5015c8.png
[image-20230201114502158]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ef3d254edb5840358763d4dc49606602.png
[image-20230201121618812]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/87b80a76eac045f9add89fc5382e36f7.png
[image-20230201121723698]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/552dcd5556ee485893c9a90943db6d5f.png
[image-20230201122729877]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/1f285127bf8643a39fa1e10913255d76.png
[image-20230201124016293]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/4d2c06787cbd4391bc21f4d2eb9a7997.png
[image-20230201125911225]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/3501fa3071864dbabd2c94086c13f147.png
[image-20230201130137365]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/1fd713c6e9904d8b959554a674107023.png
[image-20230201155613366]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/0049b9e41aef469d8c6d35626678f6b4.png
[image-20230201160329135]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f755de83428b46919fc4175bba507f9a.png
[image-20230201161040465]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e35e5beb916a4591bef6c785e7659477.png
[image-20230201162108409]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/3866dbc94dc64e8c99e605117c069cbc.png
[image-20230201162309066]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/70cb076ee33f4e87ae358518a5fce169.png
[image-20230201162522169]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/07d469b05af247d5b86256c6acd803d4.png
[image-20230201162713316]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d9ab9ee94d0e4d0eb5a94c9be7e996f5.png
[image-20230201163329831]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9f72ad54cc84412094480d456bd72074.png
[image-20230201164342554]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/84267fe47d0a46feade82ab3eba45bb2.png
[image-20230201170827235]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f09b5941631f417f9c4edc5e935de971.png
[image-20230201171916941]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ce4430c002ed45368a216e90de02725f.png
[image-20230201175550239]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/6279eaa02b344fd7af69ad09b86eec63.png
[image-20230201175947637]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/ffe46e57ecf145d7bd55ce188c5c5a89.png