八股文mysql

系列文章目录

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

---

前言

mysql这段时间八股文总结，后面有待补充

---

一、基础

1.1连接器

连接命令： mysql -h$ip -u$user -p$pwd
顾名思义，$ip是连接的IP地址，如果是连接本地的mysql服务，则无需该参数；$是指定的用户名，管理员角色为root；$pwd指定的密码，自己电脑上试验一次该命令：

连接时先经过TCP三次握手mysql是基于TCP协议传输的，如果一切没有问题，连接器会获得该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读取到的权限来进行权限逻辑的判断。所以如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再建新的连接才会使用新的权限设置。

如何查看mysql服务被多少个客户端连接了？
执行show processlist命令来进行查看，查看截图如下所示：

如图所示，command分别为daemon守护进程和query操作进程（crud），sleep（休眠进程），休眠的连接。
休眠的连接会一直占用着嘛，mysql定义了空闲连接的最大空闲时长，由wait_timeout参数控制的，空闲连接超过这个时间，连接器就会自动将该连接断开，在自己的mysql执行一下如下图所示：

默认值是8小时（28800s），也可以使用kill connection+id的命令来手动断开空闲的连接。

mysql的连接数有限制嘛
mysql服务支持的最大连接数由max_connections参数控制，在自己的电脑上执行查看mysql最大连接数的命令，如下图所示：

我的mysql上默认是200个，超过这个值，系统会拒绝接下来的连接请求too many connections
长连接和短链接

//短链接
连接mysql服务（TCP三次握手）
执行mysql
断开mysql服务（TCP四次挥手）
//长连接
连接mysql服务（TCP三次握手）
执行sql
执行sql
执行sql
,…
断开mysql服务（TCP四次挥手）

可以看到使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般推荐使用长连接
但是使用长连接后可能占用内存增多，因为mysql在执行查询过程中临时使用内存管理连接对象，这些连接对象会一直保存在内存中只有在连接断开时才会释放。如果长连接累计很多，将导致mysql服务占用内存太大，有可能被系统强制杀掉，这样会发生mysql服务异常重启的现象。

怎么解决长连接占用内存的问题

1. 定期断开长连接。
2. 客户端主动重置连接。mysql5.7版本实现了mysql_reset_connection()函数的接口。当客户端执行了很大的操作后，在代码里调用该函数来重置连接，达到释放内存的效果。

连接器总结

• 与客户端进行三次握手建立连接
• 校验客户端的用户名和密码，如果用户名或密码不对，则会报错
• 如果用户名和密码都对了，就会读取该用户的权限，然后后面的权限逻辑判断都是基于此时读取到的权限。

1.2查询缓存

如果sql是查询语句（select 语句），mysql就会先去查询缓存（query cache）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以key-value形式保存在内存中的，key为sql查询语句，value为sql语句查询的结果。同一般的缓存一样，如果命中缓存，则直接返回value给客户端；如果未命中，则继续往下执行，等执行完后，再将查询的结果存入查询缓存中。
mysql查询缓存很鸡肋，对于更新频繁的表，查询缓存的命中率是很低的，mysql8.0之后将查询缓存删掉了注意是server层的查询缓存，存储引擎中的查询缓存仍然保留

1.3 解析sql

在正式执行sql查询语句前，mysql会先对sql语句做解析，这个工作交给解析器来完成。
解析器会做如下两件事：

1. 词法分析，mysql会根据你输入的字符串识别出关键字，构建出sql语法树，这样方便后面模块获取sql类型、表名、字段名、where条件等。
2. 语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个sql语句是否满足mysql语法。
   如果输入的sql语句语法不对，就会在解析器这个阶段报错。如下面语句from写成form，此时mysql解析器报错，错误截图如下所示：
   
   表不存在或者字段不存在，不是在解析器做的，是在接下来的阶段做的。

1.4 执行sql

经过解析器后，接着要进入执行sql查询语句的流程了，每条select查询语句流程主要可以分为下面三个阶段：

• prepare阶段，也就是预处理阶段
• optimize阶段，也就是优化阶段
• execute阶段，也就是执行阶段

预处理器

• 检查sql查询语句中的表或者字段是否存在
• 将select *中的*符号，扩展为表上的所有列

优化器
经过预处理阶段后，还需要为sql查询语句先制定一个执行计划，这个工作交给优化器来完成。
优化器主要负责将sql查询语句的执行方案确定下来，比如表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引，简单的查询语句select * from goverment where id = 27，就使用主键索引，想知道优化器使用了哪个索引，可以在查询语句最前面加个explain命令，即会输出这条sql语句的执行计划，执行计划中的key就表示执行过程中使用了哪个索引，比如下图的key为primary就是使用了主键索引。

如果查询语句的执行计划中的key为null说明没有使用索引，那就会全表扫描(type = ALL),这种扫描的方式是效率最低档次的，如下图：

现在将这张表中的name设置为普通索引（二级索引）

这时users表中就有主键索引（id）和普通索引（name）。假设执行查询语句
select id from users where id > 28 and name like 's%';
这条查询语句既可以用主键索引也可以用普通索引，但执行的效率会不同。这时就需要优化器来决定使用哪个索引。
这条语句采用了覆盖索引,直接在二级索引就能查到结果（因为二级索引的B+树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的B+树的成本会比查询二级索引的B+成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。
执行语句后，输出如下图所示：

如图所示：possible_keys代表可以选择的索引，key代表优化器选择的索引，可以看到优化器选择了普通索引

执行器
优化器确定了执行方案，接下来mysql就真正开始执行语句了。在执行的过程中，执行器会和存储引擎交互，交互是以记录为单位的。
有三种方式执行过程（执行器和存储引擎的交互过程）：

• 主键索引查询
• 全表扫描
• 索引下推

主键索引查询
以select * from users where id = 27为例，看执行器是怎么工作的，因为是主键索引且等值查询（由于主键id是唯一），因此优化器决定以访问类型为const进行查询，也就是使用主键索引查询一条记录，那么执行器与存储引擎的执行流程是这样的：

• 执行器第一次查询，会调用read_first_record 函数指针指向的函数，因为优化器选择的访问类型为const，这个函数指针被指向为innoDB引擎索引查询的接口，把条件id = 27交给存储引擎，让存储引擎定位符合条件的第一条记录
• 存储引擎通过主键索引的B+树结构定位到id = 27的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；
• 执行引擎从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合就跳过该记录。
• 执行器查询的过程是一个while循环，所以还会再查一次，但是因为这不是第一次查询了，所以会调用read_record函数指针指向的函数，因为优化器选择的访问类型是const，这个函数指针被指向一个永远返回-1的函数，所以当调用该函数，执行器会退出循环，结束查询。
  至此，这个语句执行完成。

全表扫描
全表扫描，即当where的条件不是主键，且其它字段没有建立索引时，会出现。例子如下所示：select * from users where name = 'first';由于这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为all进行查询，也就是全表扫描的方式查询，此时执行器和存储引擎的执行流程是这样的：

• 执行器第一次查询，会调用read_first_record函数指针，因为优化器选择的访问类型是all，这个函数指针被指向innoDB引擎全扫描的接口，让存储引擎读取表的第一条记录
• 执行器会判断读到的这条记录的name是不是first，如果不是则跳过；如果是则将记录发给客户端（server每从存储引擎读到一条数据就会发给客户端，客户端等语句查询完成后，才会显示所有的记录）
• 执行器查询的过程是一个while循环，所以还会再查一次，调用read_record函数指针指向的函数，因为优化器选择的访问类型为all，read_record函数指向的还是innoDB引擎全扫描的接口，所以接着向存储引擎要求继续读刚才那条记录的下条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端。
• 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层）返回了读取完毕的信息。
• 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询
  至此，这个语句执行完毕

** 索引下推**
索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将server层部分负责的事情，交给存储引擎去处理。
举例：给age和username字段建立了联合索引，sql语句create index idx_ageName on park.user(age,username);
联合索引当遇到范围(>、<)就会停止匹配，举例select * from user age > 17 and userName = 'qqt';，在该种情况下，age字段可以使用联合索引，但是username字段则无法使用索引。
在不使用索引下推的情况下，执行器和存储引擎的执行流程是这样：

• Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age>17的第一条记录；
• 存储引擎根据二级索引的B+树快速定位到这条记录，获取主键值，然后进行回表操作（即再查一次表），将完整的记录返回给server层。
• server层判断username是否等于’qqt’，如果成立则将其发送给客户端；否则跳过该记录。
• 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给server层。
• 如此往复，直到存储引擎把表中所有记录读完。
  可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给server，接着server再判断该记录的username是否等于qqt。
  索引下推的含义是将username是否等于qqt的工作从server层下放到存储引擎层，过程如下：
• server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age > 17的第一条记录；
• 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（username）的条件是否成立。如果不成立 ，则直接跳过二级索引；如果成立，则执行回表操作，将完成记录返回给server层。
• server层再判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
• 如此往复，直到存储引擎把表中的所有记录读完。
  使用了索引下推后，虽然username列无法使用到联合索引，但是因为它包含在联合索引（age，username）里，所以直接存储引擎过滤出满足username = 'qqt’的记录后，才会执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。
  
  可以看到执行计划中的Extr部分显示了“using index condition”，说明使用了索引下推。

1.5总结

执行一条sql语句，发生的过程：

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中缓存则直接返回，否则继续往下执行（后期版本已被舍弃）
• 解析sql，通过解析器对sql查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型
• 执行sql：执行sql共有三个阶段：
  预处理阶段：检查字段或表是否存在，将select * 中的*符号扩展为表上所有列。
  优化阶段：考虑查询成本，选择查询成本最小的执行计划
  执行阶段：根据执行计划执行sql查询语句，从存储引擎读取记录，返回给客户端

---

二、索引

2.1 什么是索引

索引简单来说就是数据的目录，索引就是为了帮助存储引擎快速获取数据的一种数据结构（索引和数据都在存储引擎中）

2.2 索引的分类

索引的分类可以从四个角度，分别是：

1. 按数据结构分类：B+tree索引、hash索引，Full-text索引
2. 按物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）
3. 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引
4. 按字段个数分类：单列索引、联合索引

2.2.1 按数据结构分类

虽然有上述的三种按数据结构分类的索引方式，但B+tree索引类型是mysql存储引擎采用最多的索引类型。
在创建表时，innoDB存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）
• 如果没有主键，就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键（key）
• 在上述两个都没有的情况下，innoDB将自动生成一个隐式自增id列作为聚簇索引的索引键（key）

创建的主键索引和二级索引默认使用的是B+tree索引
B+树是一种二叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包含了所有的索引值信息，并且每个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表，示意图如下图所示：

通过主键查询商品数据的过程
select * from product where id = 5;
这句话使用了主键索引查询id号为5的商品。查询过程中B+Tree会自顶向下逐层进行查找：

• 将5与根节点的索引数据（1，10，20）比较，5在1和10中间，因此找到第二层索引数据（1，4，7）；
• 在第二层中，5在4和7之间，因此找到第三层索引数据（4，5，6）
• 在叶子节点的索引数据（4，5，6）中查找，然后找到索引值为5的行数据

数据库的索引和数据都是存储在硬盘上的，因此读取一个节点就是一次磁盘I/O操作。因此在上述过程中一共经历三个节点，即进行了三次I/O操作。
B+树存储千万级的数据只需要3-4层高度就可以满足，意味着从千万级的表查询目标数据最多需要3-4次磁盘I/O，所以B+树相比于B树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况下，查询一个数据的磁盘I/O仍然维持在3-4次。

通过二级索引查询商品数据的过程
主键索引的B+树和二级索引的B+树区别如下：

• 主键索引的B+树的叶子节点存放的是实际数据，所有完整的用户记录 都存放在主键索引的B+树中
• 二级索引的B+树的叶子节点存放的是主键值，而不是实际数据。

因此当将product中的product_no（商品编码）设为二级索引后，查询某一行商品的信息，会先在辅助索引中找到主键值，然后通过主键索引中的B+树查询到对应的叶子节点，然后获取整行数据。这个过程叫做回表，也就是说要查到两个B+树才可以查到数据
但如果查询的数据是能在二级索引的B+树的叶子节点中查询到，就不要再回表使用主键索引查，比如只获取product的主键，即在二级索引的B+树就能查到结果的过程叫做覆盖索引，也就是只需查一个B+树就能找到数据

为什么使用B+树作为索引的数据结构
B+树 vs B树
B+树只在叶子节点存储数据，而B树的非叶子节点也要存储数据，因此B+树在同样的内存页的情况下，可以存放更多的节点，空间局部性更好。
另外B+树的叶子节点采用的是双链表连接，适合mysql中常见的范围查找，B树的叶子节点没有链表连接，只能通过递归的方式寻找。
B+树 vs 二叉树
B+树即使是数据达到千万级别高度仍然维持在3-4层左右一次数据查询操作只需做3-4次的磁盘I/O即可查到数据，但二叉树由于每个父节点只能有两个子结点，因此高度会比较高，I/0次数会更多
B+树 vs hash
Hash在做等值查询的时候效率很快，复杂度为O(1)，但不适合做范围查询。

2.2.2 按物理存储分类

索引分为聚簇索引和二级索引，上一小节已经提到二者：

• 主键索引的B+树的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+树的叶子节点中。
• 二级索引的B+树的叶子节点上存放的是主键值，而不是完整的用户记录。

因此，如果查询的数据在二级索引上可以查到，就不用回表；否则需要回表，再次查询B+树。

2.2.4 联合索引和主键索引

从字段个数角度来看，索引分为单列索引和联合索引

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引

设置联合索引，create index index_product_no_name on product(product_no,name);,在使用联合索引时，联合索引的非叶子节点用两个字段的值作为B+树的key值。当在联合索引查询数据时，先按product_no字段比较，在product_no相同的情况下再按name字段比较。即联合索引的B+树先按product_no进行排序，在product_no相同的情况下再按name字段排序。
因此，存在最左匹配原则，即按照最左优先的方式进行索引的匹配。在使用联合索引进行查询时，如果不遵循最左匹配原则，联合索引就会失效。
假设创建了一个（a，b，c）联合索引，，是先按a排序，在a相同的情况下再按b排序，在b相同的情况下再按c排序。因此，b和c是全局无序的，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用索引的。
利用索引的前提是索引里的key是有序的。
联合索引范围查询
联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的B+树，部分字段没有用到B+树的情况。
如果出现范围查询的话，联合索引的最左匹配原则会一直向右匹配到范围查询停止匹配。也就是`范围查询的字段可以用到联合索引，但范围查询字段后面的字段无法用到联合索引

Q1 select * from table where a > 1 and b = 2,联合索引（a,b）中哪一个字段使用到了联合索引的B+树`
联合索引是先按照a字段的值排序的，所以符合a > 1条件的二级索引记录肯定是相邻，于是在进行索引扫描时，可以定位到符合a > 1的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合a > 1条件位置，因此a字段可以在联合索引的B+树中进行索引查询。

但是在符合a > 1条件的二级索引记录的范围内，b字段的值是无序的。此时由于a不是一个固定的值，因此b字段是无序的，因此不能根据查询条件b = 2来进一步减少需要扫描的记录数量（b字段无法利用联合索引进行索引查询）因为索引查询前提条件是有序
因此，Q1这条查询语句只有a字段用到联合索引进行索引查询，b字段没有用到联合索引

Q2 select * from table where a >= 1 and b = 2，联合索引(a,b)哪一个字段用到了联合索引的b+树
在这种情况下，虽然a >= 1条件下，b字段是无序的，但对于符合a = 1的二级索引记录的范围内，b字段是有序的。于是，在确定需要扫描的二级索引的范围时，当二级索引记录的a字段值为1时，可以通过b = 2条件减少需要扫描的二级索引记录范围。（从符合a = 1，b = 2条件的第一条记录开始扫描，而不是从第一个a字段值为1的记录开始扫描）
因此Q2这条查询语句a和b字段都用到了联合索引进行索引查询（只是b字段用的比较少）

Q3 select * from table where a between 2 and 8 and b = 2
该种情况由于有等于的情况（mysql中between包含value1和value2边界值），因此相当于Q2的情况Q3这条查询语句a和b字段都用到了联合索引进行索引查询
Q4 select * from table where name like ‘j%’ and age = 22
同理，Q4这条查询语句二者都用到了联合索引进行查询。

综上所述，联合索引的最左匹配原则，在遇到范围查询（如 < > )的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是范围查询字段的后面的字段无法用到联合索引。注意，对于 >= 、 <= 、between、like前缀匹配的范围查询，并不会停止匹配，即后面的字段也会用到联合索引

2.3 什么时候需要/不需要创建索引

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大
• 创建索引和维护索引要耗费时间，这种事件随着数据量的增加而增大
• 会降低表的增删改的效率，每次增删改表，B+树为了维护索引有序性，都需要进行动态维护

什么时候适用索引

• 字段具有唯一性限制的，比如商品编码
• 经常用于where查询条件的字段，这样能够提高整个表查询速度，联合索引，查询条件不是一个字段
• 经常用于group by和order by的字段，这样在查询的时候就不需要再做一次排序了，因为建立索引的字段在B+树中都是排序好的。

什么时候不需要索引

• 对应需要第一条：

---

3、事务

即保证某个业务里的所有数据库的操作都是不可分割的，要么全部执行成功，要么全部失败，不允许出现中间状态的数据。
innoDB支持事务，MyISAM不支持事务
事务具有四个特性（ACID）：

• 原子性（Atomicity） 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，事务在执行过程中发生错误，会被回滚到事务开始前的状态。
• 一致性（Consistency） 事务操作前和操作后，数据满足完整性约束，即数据库保持一致性状态。
• 隔离性（isolation） 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
• 持久性（Durability） 事务处理结束后，对数据的修改是永久的，即使系统故障也不会丢失。

InnoDB引擎通过什么技术来保证事务的四个特性：

• 持久性是通过redo log（重做日志）来保证的
• 原子性是通过undo log（回滚日志）来保证的
• 隔离性是通过MVCC（多版本并发控制）或锁机制来保证的；
• 一致性通过持久性+原子性+隔离性来保证的。

3.1 并行事务会引发什么问题隔离性

mysql服务端允许多个客户端连接（有多个连接），因此mysql会出现同时处理多个事务的情况。
因此，在同时处理多个事务时，就可能出现脏读、不可重复读、幻读的问题

3.1.1 脏读

如果一个事务读到了另一个事务未提交事务修改过的数据，这就意味着发生了脏读现象。

即假设事务A和B访问mysql，事务A获取余额100w，并更新数据为200w，此时事务A还没有提交，事务B读到了更新的数据200w但由于事务A并没有提交，因此随时可能产生回滚，从而使事务A读取到了过期的数据，这种现象称为脏读

3.1.2 不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读取到的数据不一样的情况，这意味着发生了不可重复读现象。

假设有A和B两个事务同时在处理，事务A先开始从数据库中读取余额数据。然后继续执行逻辑处理，在这过程中事务B更新了这条数据，并提交了事务，那么当事务A再次读取该数据时，就会发现前后两次读到的数据不一致，这也就是不可重复读

3.1.3 幻读

在一个事务内多次查询某个符合查询条件的记录数量，即前后两次查询到的记录数量不有用的情况，出现幻读现象。

假设A和B两个事务同时在处理，事务A先开始从数据库查询账户余额大于100w的记录，发现共有五条，然后事务B插入了一条大于100w的记录，此时查询到6条记录，发现和前一次读到的记录数量不一样了，即发生幻觉，这种现象称为幻读

3.2事务的隔离级别有哪些

即解决三种现象：

• 脏读：读到其他事务未提交的数据
• 不可重复读：前后读取的数据不一致
• 幻读：前后读取的记录数量不一致

三种现象的严重性排序如下：

脏读 > 不可重复读 > 幻读

sql标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率越低。串行化 > 可重复读 > 读已提交 > 读未提交

• 读未提交，指一个事务还没提交时，它做的变更就能被其他事务看到
• 读提交，指一个事务提交之后，它做的变更才能被其他事务看到
• 可重复读，指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，mysql innoDB引擎的默认隔离级别
• 串行化，会对事务加上读写锁，在多个事务对这个记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等待前一个事务执行完成，才能继续执行

mysql在可重复读隔离级别下，可以很大程度避免幻读现象的发生但不是彻底避免，由于串行化隔离级别的性能消耗比较大，因此mysql不会使用串行化隔离级别来避免幻读现象的发生。
在可重复读的情况下，有两种解决方案来解决幻读：

• 针对快照读（普通select语句）：通过MVCC方式解决了幻读，因为在可重复读隔离级别下，事务执行过程中看到的数据和事务启动时看到的是一致的，因此即使中途有其他事务插入了一条数据，也是查询不出来的，很好的避免了幻读情况。
• 针对当前读（select … for update语句），是通过next-key lock（记录锁+间隙锁）方式解决了幻读，因为在执行select … for update语句的时候，会加上next-key lock，如果有其他事务在next-key lock锁范围内插入了一条记录，那么插入语句就会被阻塞，无法成功插入，避免幻读问题。

四种隔离级别的实现方式

• 读未提交：略
• 串行化：通过加读写锁的方式来避免并行访问
• 读提交和可重复读：通过read view 来实现的，区别在于创建read view的时机不同，read view 是一个数据快照，定格某一时刻的风景。读提交隔离级别是在每个语句执行前都会重新生成一个read view，而可重复读隔离级别是启动事务时生成一个read view，然后整个事务都在用这个read view

read view在MVCC里是如何工作的

• read view 四个字段的作业
• 聚簇索引记录中两个跟事务有关的隐藏列

read view到底是什么东西：

分别分析这四个字段：

• creator_trx_id：指的是创建该Read View 的事务的事务id
• m_ids：创建readView时，当前数据库中活跃（即启动还未提交）的事务id列表
• min_trx_id：指的是创建read view时，当前数据库中活跃事务中事务id最小的事务，也就是m_ids的最小值
• max_trx_id：这个不是m_ids的最大值，而是创建read view 时当前数据库应该给下一个事务的id值，也就是全局事务中最大的事务id值+1

主键索引中有两个隐藏列，分别是trx_id和roll_pointer

• trx_id：当一个事务对某条主键索引记录进行改动时，就会把该事务的事务id记录在trx_id隐藏列中。

• roll_pointer：每次对某条主键索引记录进行改动时，就会把旧版本的记录写入到undo日志中，然后这个隐藏列是一个指针，指向每一个旧版本记录 ，于是就可以通过它找到修改前的记录
  注意这些隐藏列是属于记录，不是某个事务
  
  一个事务去访问记录时，除了自己的更新记录总是可见的，还有这几种情况：

• 如果记录的trx_id值小于Read View中的min_trx_id值，表示这个版本的记录是在创建read view前已提交的事务生成的，所以该版本的记录对当前事务可见。

• 如果记录的trx_id值大于等于read view中的max_trx_id值，表示这个版本的记录是在创建read view后才启动的事务生成的，所以该版本的记录对当前事务不可见。

• 如果记录的trx_id值在read view的min_trx_id和max_trx_id之间，需要判断trx_id是否在m_ids中：

  • 如果记录的trx_id在m_ids列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
  • 如果记录的trx_id不在m_ids列表中，表示生成该版本记录的活跃事务已经被提交，记录对当前事务可见。

这种通过版本链来控制并发事务访问同一个记录时的行为就叫MVCC（多版本并发控制）

4、锁

4.1 mysql有哪些锁

4.1.1 全局锁

使用全局锁后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞。

• 对数据的增删改操作，比如insert、delete、update等语句；
• 对表结构的更改操作 ，比如alter table、drop table等语句

全局锁主要应用于做全库逻辑备份，加上全局锁后，整个数据库都是只读状态，业务只能读数据而不能更新数据。如果数据库引擎支持可重复读的隔离级别，可以先创建read view，对read view进行备份，这样即使其他事务更新了表的数据，也不会影响read view。

4.1.2 表级锁

4.1.2.1 表锁

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作，表锁的颗粒度太大，会影响并发性能，因此一般情况下使用innoDB实现的颗粒度更细的行级锁。
共享表锁，读锁：lock tables t_student read;
独占表锁，写锁：lock tables t_student write;
释放当前会话所有表锁：unlock tables

4.1.2.2 元数据锁

元数据所MDL并不用显示的使用，在对数据库表进行操作时，会自动给这个表加上MDL。

• 对一张表进行CRUD操作时，加的是MDL读锁；
• 对一张表做结构变更操作时，加的是MDL写锁；

写锁获取的优先级高于读锁

4.1.2.3 意向锁

• 在使用innoDB引擎的表里对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁；
• 在使用innoDB引擎的表里对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁;

当执行增删改时，需要先对表加上意向独占锁，然后对该记录加独占锁。
普通的select是不会加行级锁的，普通的select语句是利用MVCC实现一致性读，是无锁的。
不过，select也可以对记录加共享锁和独占锁，具体方式如下：

//先在**表**上加上意向共享锁，然后对读取的**记录**加共享锁
select ... lock in share mode;
//先在**表**上加上意向独占锁，然后对读取的**记录**加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁(lock tables ... read)和独占表锁(lock tables ... write)发生冲突
意向锁是为了快速判断表里是否有记录被加锁（没有意向锁，在加独占表锁时，需要遍历表里的所有数据，查看是否有记录存在独占锁）

4.1.2.4 AUTO-INC锁

略

4.1.3行级锁

行级锁主要分为三类：

1. Record Lock，记录锁，也就是仅仅把一条记录锁上
2. Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身
3. Next-Key Lock:Record Lock + GapLock的组合，锁定一个范围，并且锁定记录本身

插入意向锁：
一个事务在插入一条记录时，需要判断插入位置是否已被其他事务加了间隙锁。
如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止，在此期间，会生成一个插入意向锁，表明有事务想在某个区间加入新纪录，但是现在处于等待状态。

如上图所示：事务A对表加上了一个范围id为(3,5)间隙锁，当事务A还没提交时，事务B向该表插入一条id = 4 的新纪录，这时会判断到插入的位置已经被事务A加了间隙锁，于是事务B会生成一个插入意向锁，然后将锁的状态设为等待状态。（锁状态为等待状态，不意味着事务成功获取到了锁，只有当锁状态为正常时，才代表事务成功获取到了锁）此时事务B发生阻塞，直到事务A提交了事务。

mysql没加索引会锁全表
在mysql可重复读隔离级别情况下，会针对当前的索引加记录锁和间隙锁，这样可以避免其他事务执行增删改导致幻读的问题。
在执行update、delete、select for update等具有加锁性质的语句，一定要检查语句是否走了索引；如果未走索引，即全表扫描的话，会对每一个索引加临键锁，相当于把整个表锁住了。

4.2 mysql死锁

间隙锁和间隙锁不是冲突的，但插入意向锁和间隙锁是冲突的，所以当两个事务持有一个间隙的间隙锁时，都要在此间隙插入数据，此时两个都要获取插入意向锁，但由于二者都有间隙锁无法释放。都在等待对方事务的间隙锁释放，获取插入意向锁，于是造成了循环等待，导致死锁。

如何避免死锁

• 设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务回滚，于是锁就释放，另一个事务就可以继续执行了。
• 开启主动死锁检测：发现死锁后，主动回滚死锁链条中的某一个事务，使其他事务得以继续执行。

5、日志

6、内存

7 番外

7.1 mysql分库分表

数据库相关优化方案

• sql调优：即让sql执行尽量命中索引
• 表结构优化：加上冗余字段，减少联表操作，选择冗余字段时要尽量选择不经常更新的字段（冗余字段更新时会涉及到多个表的更新）
• 架构优化：读写分离 + redis缓存
• 硬件优化

7.1.1 分表方案

为什么要分表：sql操作变慢，如果数据库中存在一张数据量非常大的表，一条sql没有命中索引就会全表扫描，这个查询耗时会非常的久。
本质上就是表太大，即两种可能，要么是表的字段太多，要么是表的记录数太多，也即产生了两种不同的分表方案：即切分字段（垂直分表）和切分记录（水平分表）

垂直分表
垂直分表主要用在有必须的text类型来存储数据时，可以利用垂直拆分来减少表的大小，将text字段拆分到子表中。
水平分表
依据哪一个字段来拆分呢，该字段需要满足唯一性很高，同时业务访问该表的大部分场景是否根据该字段来过滤。
假设订单表，可以根据用户id进行hash，将相同hash值用户的订单存放到一个数据库表中。
按月分表
将数据同步到历史库后，可以删除原来这个月的数据库表用于释放空间
mysql 分区表
即根据某一个字段的hash值来将这些表分到不同的分区里。

7.1.2分库方案

为什么要分库：

• 大量请求阻塞：在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态
• 存储出现问题：业务量剧增，单个数据库的数据量越来越大，给存储造成巨大压力。

有两种拆库的方式：按业务分库和按表分库

按业务分库
按业务模块进行拆分，将原来的数据库拆分到三个RDS实例中，数据库的写入能力提升了，服务的接口响应时间也变短了，提高了系统的稳定性。
按表分库
即根据之前的分表方案，将分出来的表放到不同的RDS实例中即可，通过分区键定位到哪个RDS实例，并进一步定位到具体的子表，再做数据操作服务调用链路变长了，对系统的稳定性有一定的影响

7.1.3 拆分后的问题

1. 跨域join问题
   在垂直拆分之前，系统中所需的数据可以通过join联表来完成，但分库之后，由于数据库可能会分布在不同的RDS实例上，join处理比较复杂根据mysql开发规范，一般是禁止跨域join的，那该如何处理这种情况：

• 全局表
  每个数据节点上都有一份全量数据，例如一些数据字典表，数据很少修改，可以避免跨域join的性能问题。
• 数据同步
  通过数据同步工具将一个库的一个表实时同步到另一个库中，但这种方案比较依赖同步工具的稳定性，如果同步有延迟，就会导致数据不一致，产生脏数据。

2. 分布式事务问题
   分布式事务也要遵循事务的ACID特性，其中有两个重要的理论：CAP（consistency一致性，availability可用性，partition tolerance分区容错性）和BASE（basically available基本可用，soft state 软状态，eventually consistent最终一致性）

总结

mysql八股文总结