一文总结MySQL面试知识点

阳光穿透心脏的1/2处 2024-03-22 15:14 35阅读 0赞

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

*   *  知识点
     *   *  1 定位慢查询
         *  2 存储引擎
         *  3 索引
         *  4 SQL优化
         *  5 事务
         *  6 主从同步
         *  7 分库分表
     *  问答题
     *   *  1 如何定位慢查询
         *  2 那这个SQL语句执行很慢, 如何分析呢？
         *  3 MYSQL支持的存储引擎有哪些, 有什么区别 ?
         *  4 了解过索引吗？（什么是索引）
         *  5 索引的底层数据结构了解过吗?
         *  6 B树和B+树的区别是什么
         *  7 什么是聚簇索引什么是非聚簇索引?
         *  8 知道什么是回表查询吗?
         *  9 知道什么叫覆盖索引吗
         *  10 MYSQL超大分页怎么处理 ?
         *  11 索引创建原则有哪些
         *  12 什么情况下索引会失效 ?
         *  13 谈谈你对SQL优化的经验
         *  14 事务的特性是什么？可以详细说一下吗？
         *  15 并发事务带来哪些问题？怎么解决这些问题呢？MySQL的默认隔离级别是？
         *  16 undo log和redo log的区别
         *  17 好的，事务中的隔离性是如何保证的呢？(你解释一下MVCC)
         *  18 MySQL主从同步原理
         *  19 你们项目用过分库分表吗

![image-20230510162844556][]

### 知识点 ###

#### 1 定位慢查询 ####

![image-20230510163251113][]

MySQL的慢查询是指执行时间超过预设阈值的SQL查询语句。通过设置一个时间阈值，一般为几秒钟，MySQL会记录下执行时间长于该阈值的查询语句，这些查询语句称为慢查询。

慢查询一般可能与以下操作相关

*  聚合查询
 *  多表查询
 *  表数据量过大查询
 *  深度分页查询

表象：页面加载过慢、接口压测响应时间过长（超过1s）

怎么定位慢查询呢? 有以下方法

方案一：开源工具

*  调试工具：Arthas
 *  运维工具：Prometheus 、Skywalking(下图)

![image-20230510163650341][]

![image-20230510163721119][]

没用过也不要慌, MySQL自带

方案二：MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long\_query\_time，单位：秒，默认10秒）的所有SQL语句的日志  
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
    slow_query_log=1
    # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 `/var/lib/mysql/localhost-slow.log`。

![image-20230510163843931][]

怎么回答这个问题呢?

介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）  
我们系统中当时采用了运维工具（ Skywalking ），可以监测出哪个接口，最终因为是sql的问题  
在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）

![image-20230510174311902][]

![image-20230510174331465][]

SQL执行计划可以分析前三种情况MySQL执行慢的原因

可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息

语法：

![image-20230510174543628][]

*  possible\_key 当前sql可能会使用到的索引
 *  key 当前sql实际命中的索引
 *  key\_len 索引占用的大小
 *  Extra 额外的优化建议

通过`key`和`key_len`它们两个查看是否可能会命中索引

<table> 
 <thead> 
  <tr> 
   <th><strong>Extra</strong></th> 
   <th><strong>含义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using where; Using Index</td> 
   <td>查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据</td> 
  </tr> 
  <tr> 
   <td>Using index condition</td> 
   <td>查找使用了索引，但是需要回表查询数据</td> 
  </tr> 
 </tbody> 
</table>

type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq\_ref、ref、range、 index、all

*  NULL值SQL语句没有使用到表
 *  system：查询系统中的表
 *  const：根据主键查询
 *  eq\_ref：主键索引查询或唯一索引查询
 *  ref：索引查询
 *  range：范围查询
 *  index：索引树扫描
 *  all：全盘扫描

如果某条SQL使用的是`index`或者`all`, 就需要优化了

#### 2 存储引擎 ####

<table> 
 <thead> 
  <tr> 
   <th><strong>特点</strong></th> 
   <th><strong>InnoDB</strong></th> 
   <th><strong>MyISAM</strong></th> 
   <th><strong>Memory</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储限制</td> 
   <td>64TB</td> 
   <td>有</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>事务安全</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>锁机制</td> 
   <td>行锁</td> 
   <td>表锁</td> 
   <td>表锁</td> 
  </tr> 
  <tr> 
   <td>B+tree索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>Hash索引</td> 
   <td>-</td> 
   <td>-</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持(5.6版本之后)</td> 
   <td>支持</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>空间使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>N/A</td> 
  </tr> 
  <tr> 
   <td>内存使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>中等</td> 
  </tr> 
  <tr> 
   <td>批量插入速度</td> 
   <td>低</td> 
   <td>高</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>支持外键</td> 
   <td>支持</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
 </tbody> 
</table>

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

*  InnoDB: 是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
 *  MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
 *  MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

#### 3 索引 ####

![image-20230510181753376][]

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

![image-20230510192752352][]

顺序查找的话时间复杂度是O(N)

MySQL默认使用的索引底层数据结构是B+树。再聊B+树之前，我们先聊聊二叉树和B树

![image-20230510193108745][]

红黑树也是一种二叉树, 如果是1000W个数据, 需要很多层级

更先进的数据结构来了

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。  
以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key

![image-20230510193224909][]

B树已经很优秀了, 但是还有更优秀的

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

![image-20230510193928263][]

B树与B+树对比:

①：磁盘读写代价B+树更低；

②：查询效率B+树更加稳定；

③：B+树便于扫库和区间查询

![image-20230510195636881][]

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>含义</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>聚集索引(Clustered Index)</td> 
   <td>将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据</td> 
   <td>必须有,而且只有一个</td> 
  </tr> 
  <tr> 
   <td>二级索引(Secondary Index)</td> 
   <td>将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键</td> 
   <td>可以存在多个</td> 
  </tr> 
 </tbody> 
</table>

聚集索引选取规则:

*  如果存在主键，主键索引就是聚集索引。
 *  如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
 *  如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

什么是聚簇索引和非聚簇索引 ?

![image-20230510195814989][]

回表查询  
就是先通过二级索引`name`找到主键值, 再去聚集索引中拿到整行的值

![image-20230510195859545][]

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>gender</th> 
   <th>createdate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>Arm</td> 
   <td>1</td> 
   <td>2021-01-01</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Lily</td> 
   <td>0</td> 
   <td>2021-05-01</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Rose</td> 
   <td>0</td> 
   <td>2021-02-14</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Zoo</td> 
   <td>1</td> 
   <td>2021-06-01</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>Doc</td> 
   <td>1</td> 
   <td>2021-03-08</td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>Lee</td> 
   <td>1</td> 
   <td>2020-12-03</td> 
  </tr> 
 </tbody> 
</table>

*  id为主键，默认是主键索引
 *  name字段为普通索引

# 覆盖索引
    select * from tb_user where id = 1
    # 覆盖索引
    select id，name from tb_user where name = ‘Arm’
    # 非覆盖索引, 需要回表查询, 因为gender在name索引中没有
    select id，name，gender from tb_user where name = ‘Arm’

第三个查询需要在拿到了id, name之后, 根据id去聚集索引中查到一行数据, 这样就产生了回表查询, 效率较低

**MYSQL超大分页处理**

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

![image-20230510202337648][]

因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

select *
    from tb_sku t,
    	(select id from tb_sku order by id limit 9000000,10) a
    where t.id = a.id;

11s明显提升到了7s左右

![image-20230510202708747][]

索引创建原则有哪些？

1). 针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据,增加用户体验  
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。  
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。  
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。  
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。  
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。  
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效

索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题  
（适当的思考一下，回想一下，更真实）

给tb\_seller创建联合索引，字段顺序：name，status，address

![image-20230510205625652][]

那快读判断索引是否失效了呢？ 执行计划explain

什么情况下索引会失效 ?

我们创建了一个名叫`tb_seller_index`的索引, 包含name, status, address等字段.

1). 违反最左前缀法则  
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：

![image-20230510205655943][]

违法最左前缀法则， 索引失效：

下面两种情况第一种情况跳过了name, 第二种情况跳了name和status, 都不满足最左前缀匹配.

可以从key和key\_len看到索引的的确确失效了

![image-20230510205825412][]

那如果跳过中间的那个status, 查询name和address呢

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

再看看key\_len, 跟前面只使用了name的长度一样

![image-20230511101203842][]

2). 范围查询右边的列，不能使用索引 。

![image-20230511101945831][]

根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

3). 不要在索引列上进行运算操作， 索引将失效。

![image-20230511102113466][]

4). 字符串不加单引号，造成索引失效。

![image-20230511102131884][]

由于，在查询是，没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

5).以`%`开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

![image-20230511102218543][]

#### 4 SQL优化 ####

可以从以下方面着手

*  表的设计优化
 *  索引优化 (参考优化创建原则和索引失效)
 *  SQL语句优化
 *  主从复制、读写分离
 *  分库分表 (后面有专门章节介绍)

**表的设计优化**（参考阿里开发手册《嵩山版》）

*  比如设置合适的数值（tinyint int bigint），要根据实际情况选择
 *  比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

**SQL语句优化**

*  SELECT语句务必指明字段名称（避免直接使用select \* ）
 *  SQL语句要避免造成索引失效的写法
 *  尽量用union all代替union, union会多一次过滤，效率低
 *  避免在where子句中对字段进行表达式操作
 *  Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

**主从复制、读写分离**

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。  
读写分离解决的是，数据库的写入，影响了查询的效率。

![image-20230511104332967][]

#### 5 事务 ####

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

当张三给李四转账时，这个过程可以看作是一个事务。事务包括了从张三的账户中减去转账金额，以及将这些金额加到李四的账户中这两个操作。如果这两个操作都成功执行，那么这个事务就被认为是完整的，否则如果其中一个操作执行失败，整个事务都会回滚并返回到执行事务前的状态，以保持数据的一致性和完整性。

ACID是什么？可以详细说一下吗？

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。  
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。  
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。  
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务带来哪些问题？怎么解决这些问题呢？MySQL的默认隔离级别是？

并发事务问题：脏读、不可重复读、幻读  
隔离级别：读未提交、读已提交、可重复读、串行化

并发事务问题

<table> 
 <thead> 
  <tr> 
   <th><strong>问题</strong></th> 
   <th><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>脏读</td> 
   <td>一个事务读到另外一个事务还没有提交的数据。</td> 
  </tr> 
  <tr> 
   <td>不可重复读</td> 
   <td>一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。</td> 
  </tr> 
  <tr> 
   <td>幻读</td> 
   <td>一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。</td> 
  </tr> 
 </tbody> 
</table>

怎么解决并发事务的问题呢？

解决方案：对事务进行隔离

<table> 
 <thead> 
  <tr> 
   <th><strong>隔离级别</strong></th> 
   <th><strong>脏****读</strong></th> 
   <th><strong>不可重复读</strong></th> 
   <th><strong>幻读</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>Read uncommitted</strong> <strong>未提交读</strong></td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td><strong>Read committed</strong> <strong>提交读</strong></td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td><strong>Repeatable Read(默认) 可重复读</strong></td> 
   <td>×</td> 
   <td>×</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td><strong>Serializable</strong> <strong>串行化</strong></td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
  </tr> 
 </tbody> 
</table>

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

undo log和redo log的区别

![image-20230511112321928][]

缓冲池（buffer pool）:主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度

数据页（page）:是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘, 发生错误时, 进行数据恢复使用。

![image-20230511113311439][]

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是**逻辑日志**。

可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，

当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

undo log和redo log的区别

*  redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
 *  undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
 *  redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

很自然的啊, 面试官继续发问

![image-20230511114142416][]

> 锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）  
> mvcc : 多版本并发控制  
> 你解释一下MVCC?

解释一下MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突  
MVCC的具体实现，主要依赖于数据库记录中的**隐式字段**、**undo log日志**、**readView**。

![image-20230511114238583][]

![image-20230511114255917][]

记录中的隐藏字段

![image-20230511114441902][]

<table> 
 <thead> 
  <tr> 
   <th>隐藏字段</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DB_TRX_ID</td> 
   <td>最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。</td> 
  </tr> 
  <tr> 
   <td>DB_ROLL_PTR</td> 
   <td>回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。</td> 
  </tr> 
  <tr> 
   <td>DB_ROW_ID</td> 
   <td>隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。</td> 
  </tr> 
 </tbody> 
</table>

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。  
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。  
而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

undo log版本链

![image-20230511153623645][]

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readView

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

![image-20230511153820721][]

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是一种当前读。

快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

*  Read Committed：每次select，都生成一个快照读。
 *  Repeatable Read：开启事务后第一个select语句才是快照读的地方。

MVCC-实现原理

ReadView中包含了四个核心字段：

<table> 
 <thead> 
  <tr> 
   <th><strong>字段</strong></th> 
   <th><strong>含义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>m_ids</td> 
   <td>当前活跃的事务ID集合</td> 
  </tr> 
  <tr> 
   <td>min_trx_id</td> 
   <td>最小活跃事务ID</td> 
  </tr> 
  <tr> 
   <td>max_trx_id</td> 
   <td>预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）</td> 
  </tr> 
  <tr> 
   <td>creator_trx_id</td> 
   <td>ReadView创建者的事务ID</td> 
  </tr> 
 </tbody> 
</table>

![image-20230511154820499][]

readview

![image-20230511154618703][]

![image-20230511154630534][]

不同的隔离级别，生成ReadView的时机不同：

*  READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
 *  REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

![image-20230511155011252][]

![image-20230511155027963][]

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

![image-20230511155131130][]

#### 6 主从同步 ####

![image-20230511160823702][]

主从同步原理

MySQL主从复制的核心就是二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

![image-20230511160856575][]

MySQL主从复制的核心就是二进制日志binlog(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)

复制分成三步：

1.  Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
2.  从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
3.  slave重做中继日志中的事件，将改变反映它自己的数据。

#### 7 分库分表 ####

主从同步是为了读写分离, 从库分担了访问压力

而分库分表是为了解决海量数据存储的问题

![image-20230511163014923][]

分库分表的时机：

1，前提，项目业务数据逐渐增多，或业务发展比较迅速—— *单表的数据量达1000W或20G以后*  
2，优化已解决不了性能问题（主从读写分离、查询索引…）  
3，IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

拆分策略

![image-20230511163124208][]

**垂直分库**

![image-20230511163254040][]

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：

*  按业务对数据分级管理、维护、监控、扩展
 *  在高并发下，提高磁盘IO和数据量连接数

**垂直分表**

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

![image-20230511163552992][]

拆分规则：

*  把不常用的字段单独放在一张表
 *  把text，blob等大字段拆分出来放在附表中

特点：

*  1，冷热数据分离
 *  2，减少IO过度争抢，两表互不影响

**水平分库**

水平分库：将一个库的数据拆分到多个库中。

特点：

*  解决了单库大数量，高并发的性能瓶颈问题
 *  提高了系统的稳定性和可用性

![image-20230511163732427][]

路由规则

*  根据id节点取模
 *  按id也就是范围路由，节点1(1-100万 ),节点2(100万-200万)
 *  …

**水平分表**

水平分表：将一个表的数据拆分到多个表中(可以在同一个库内)。

![image-20230511163825676][]

特点：

*  优化单一表数据量过大而产生的性能问题;
 *  避免IO争抢并减少锁表的几率;

新的问题和新的技术

![image-20230511164010502][]

分库之后的问题：

*  分布式事务一致性问题
 *  跨节点关联查询
 *  跨节点分页、排序函数
 *  主键避重

分库分表中间件：

*  sharding-sphere
 *  mycat

### 问答题 ###

#### 1 如何定位慢查询 ####

我在MySQL中开启了慢日志查询, 设置的值是2s, 可以在慢查询日志中看到执行时间超过2s的SQL语句, 做针对性的优化

#### 2 那这个SQL语句执行很慢, 如何分析呢？ ####

可以通过MySQL自带的分析工具Explain

第一个呢通过key和key\_len检查是否使用了索引

还有通过type字段看看sql是否有进一步优化的空间, 是否存在全索引扫描或者全盘扫描

还可以通过extra优化建议判断是否出现了回表查询, 如果出现了可以尝试联合索引或者修改返回字段

#### 3 MYSQL支持的存储引擎有哪些, 有什么区别 ? ####

MySQL中提供了很多种存储引擎, 比较常见的就是InnoDB, MyISAM和Memory

InnoDB是MySQL5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁

MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多

Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

#### 4 了解过索引吗？（什么是索引） ####

索引是帮助MySQL高效获取数据的数据结构

可以提高数据检索的效率, 降低数据库的IO成本

通过索引对数据进行排序, 降低数据排序的成本, 降低了CPU的消耗

#### 5 索引的底层数据结构了解过吗? ####

MySQL的InnoDB引擎采用的B+树的数据结构来存储引擎

B+树在非叶子结点只存储指针, 在叶子结点存储数据

B+树这种数据结构便于区间查询.

#### 6 B树和B+树的区别是什么 ####

主要有这么两点

B树中叶子和非叶子都会存储数据, 而B+树只有叶子会存放数据, 查找效率更加稳定

第二, 在进行范围查询时, B+树效率更高, 因为B+树的数据都在叶子结点, 并且叶子结点是一个双向链表

#### 7 什么是聚簇索引什么是非聚簇索引? ####

聚簇索引(聚集索引)是指数据和索引在一块, B+树的叶子结点保存了所有数据, 有且只有一个;

非聚簇索引呢, 也叫二级索引, 数据与索引分开存储, B+树叶子结点中保存对应的主键, 可以有多个;

#### 8 知道什么是回表查询吗? ####

通过二级索引找到对应的主键值, 到聚集索引里查找整行数据, 这个过程就是回表

#### 9 知道什么叫覆盖索引吗 ####

覆盖索引是指查询使用了索引, 返回的列必须在索引中全部能够找到

比如使用id查询, 直接走聚集索引

如果返回的列中没有创建索引, 有可能会触发回表查询影响性能

#### 10 MYSQL超大分页怎么处理 ? ####

超大分页一般是在数据量较大的时候, 我们使用了limit分页查询, 并且需要进行排序, 查询效率很低.

我们可以使用覆盖索引+子查询来解决

先分页查询数据的id字段, 确定了id之后, 在用子查询来过滤, 只查询这个id列表中的数据就可以了

因为查询id时走的覆盖索引, 所以效率高很多

#### 11 索引创建原则有哪些 ####

*  尽量在数据量较大, 且查询较为频繁的表上创建索引
 *  还有就是常作为查询条件、排序、分组的字段

如果是较长的字符串，可以使用前缀索引

*  使用联合索引可以较少回表查询的次数
 *  但是索引这东西也不是多多益善的，因为添加了索引会降低增删改的效率

如果索引列不能存储null值，在创建表的时候使用not null约束

#### 12 什么情况下索引会失效 ? ####

这个情况比较多, 我说一下自己遇到过的吧

违反了最左前缀法则的

范围查询, 就是大于小于啊这些, 范围查询右边的列索引会失效

在索引列上进行运算的 索引会失效

字符串不加单引号的, 有可能会导致索引失效, 因为可能会被当成其他类型的值

以`%`开头的like模糊查询, 索引会失效

#### 13 谈谈你对SQL优化的经验 ####

这个问题涉及的面比较广, 我说一下自己遇到过的

首先是表的设计优化, 用定长的char会比不定的varchar效率高, 根据数值范围选择合适的数据类型 tinyint, int, bigint

然后是索引的优化, 针对查询频次较高的数据创建索引, 使用联合索引可以避免回表查询

然后是SQL语句优化, 使用索引时注意最左匹配原则. 避免使用`select *`, 模糊查询不要把`%`放开头

然后是主从复制读写分离可以不让数据的写入影响读操作

在一张表的数据超过500W的时候注意分库分表

#### 14 事务的特性是什么？可以详细说一下吗？ ####

事务的四个特性ACID 是原子性, 一致性, 隔离性, 持久性

以张三给李四转账为例吧

首先是原子性, 事务是不可分割的, 张三扣款&李四加钱, 这俩操作要么同时成功, 要么同时失败

然后是一致性, 无论转账是否成功, 俩账户余额之和相等

隔离性, 一个事务不会影响其他事务, 比如别人也在转账或者查看金额, 不会影响到张三给李四转账

持久性 事务提交完成后, 将保存在数据库中, 系统故障也不会丢失这笔记录

#### 15 并发事务带来哪些问题？怎么解决这些问题呢？MySQL的默认隔离级别是？ ####

并发事务可能会带来 脏读 不可重复读 幻读

脏读就是一个事务读到了另一个事务没提交的数据

不可重复读就是一个事务先后读取同一条数据, 两次读的数据不一样

幻读一个事务就是查询的时候没这条数据, 插入的时候发现这数据存在

怎么解决呢 就是设置不同的隔离级别

隔离级别分4个级别, 未提交读, 提交读, 可重复读, 串行化, 串行化可以避免上述所有问题, 但是性能太低了

MySQL默认使用可重复读, 可能会出现幻读问题

#### 16 undo log和redo log的区别 ####

redo log日志记录的是数据页的物理变化, 服务宕机可以用来同步数据, 可以保证事务的持久性

undo log记录的是SQL操作的逆操作, 在发生回滚时用, 可以保证事务的原子性和一致性.

#### 17 好的，事务中的隔离性是如何保证的呢？(你解释一下MVCC) ####

MySQL中有MVCC，多版本并发控制，使得读写操作没有冲突

主要有三个部分

第一部分是隐藏字段，

通过trx\_id记录每一次操作的事务id, 是自增的;

通过roll\_pointer指向上一个版本的事务版本记录地址

第二部分是undo log,

通过回滚日志记录老版本数据,

通过版本链记录不同事务修改数据的版本, 通过roll\_pointer指针形成一个链表

第三部分是readView, 解决的是一个事务查询选择版本的问题

根据readView的匹配规则和当前的一些事务id判断该访问哪个版本的数据

不同的隔离级别快照读是不一样的, 最终访问的结果不一样

RC是每一次执行快照读时生成readView

RR是仅在事务第一次执行快照读时生成readView, 后续复用

#### 18 MySQL主从同步原理 ####

MySQL主从同步的核心就是二进制日志文件binlog, (DDL和DML语句)

1 主库在事务提交中, 会将数据变更记录在二进制日志文件binlog中

2 从库读取主库发过来的二进制日志文件, 写入从库的中继日志relay log

3 从库解析并应用其中的SQL语句

#### 19 你们项目用过分库分表吗 ####

业务介绍  
1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）  
2，达到了什么样的量级（单表1000万或超过20G）

具体拆分策略  
1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题  
2，水平分表，解决单表存储和性能的问题

上面这俩通常都涉及 sharding-sphere、mycat

3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数  
4，垂直分表，冷热数据分离，多表互不影响

[image-20230510162844556]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/0ef8dfa480fb4eadbe78cffe564bee6e.png
[image-20230510163251113]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/043cc397cea04cde92be0b66b5236a8e.png
[image-20230510163650341]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/ba71238319be4289802cfa8d49aadbf9.png
[image-20230510163721119]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/fb7c0e6450c44906a8cec0dff64cb686.png
[image-20230510163843931]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/373bdca2b7154889bb1b64240f193e91.png
[image-20230510174311902]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/2aeba68d80cc4f31919fb1fca226262f.png
[image-20230510174331465]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/5485830734224f0c92b2ce03b54c9fc4.png
[image-20230510174543628]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/20036e76d6fa46118cb13211553fe092.png
[image-20230510181753376]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/dcf966c2f2f84c06a6bfff147be04fae.png
[image-20230510192752352]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/697a4f85e35841dc845154aca3f33ee1.png
[image-20230510193108745]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/63c8fc8ed2824061a44609c5305352a8.png
[image-20230510193224909]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/91063b17813a4b4583f9ac118fcd72fc.png
[image-20230510193928263]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/ec236a17654a41fe810fcb7dc6531019.png
[image-20230510195636881]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d9869cd168b542a09fa33f2a50164acd.png
[image-20230510195814989]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/0bbea9a2ce2a4762ab2aa29a5e828cf0.png
[image-20230510195859545]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/681d6c2bf8be4388a665c98dd8730b23.png
[image-20230510202337648]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/ee71ecaa937f49258647e38e58a19ac2.png
[image-20230510202708747]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/5d28773080e44f8c860f007fccc30d94.png
[image-20230510205625652]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/7502ab666b5243e3a651926f7292590b.png
[image-20230510205655943]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/0994468806c54c918b27db51dc8f48e6.png
[image-20230510205825412]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/b34b198259d249e59c01768ce1f26c55.png
[image-20230511101203842]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/6053dc9b8d4c4c5ea8c45c99e4d09fbc.png
[image-20230511101945831]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/cba9b74c821f491baf4274f6c4dc6a54.png
[image-20230511102113466]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/d0efea0d0c7344f383b5ef22134e46b4.png
[image-20230511102131884]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/3834645e38bb4ad59876ae1ce5801407.png
[image-20230511102218543]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/2a63394a40bd4e7c80541c53115944ba.png
[image-20230511104332967]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/13fd4f93358f46fa8a87cc9edc0ffce2.png
[image-20230511112321928]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/389f876ccb0f4a44a837542e1771e600.png
[image-20230511113311439]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/830f42e734524027950c9c359bc4cbef.png
[image-20230511114142416]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/07e947149df34de4bd5e43d1d4dae32f.png
[image-20230511114238583]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/0677c589e1d74f86bcb30baa8fc3de89.png
[image-20230511114255917]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/398b724c01ca42d2a4ebe8c75fa48850.png
[image-20230511114441902]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/318700e1426f4e6eb95c89e7e61a7cb7.png
[image-20230511153623645]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/9c1b4ea56a13457697b80eeae0f4c842.png
[image-20230511153820721]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/cf40d19219794330b6c51ece966b55f7.png
[image-20230511154820499]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/fe722c8e6d9a490387da3d38befea021.png
[image-20230511154618703]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/e855ee0abc644a02a23279246d20dfd7.png
[image-20230511154630534]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/cb1dc87f0cbb4a4bb0ab19f8aedf2318.png
[image-20230511155011252]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/25d2f20a2cb249ad8c0f368f18b47a20.png
[image-20230511155027963]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/a1cd9915ce8f417c9e43ac30b22b250e.png
[image-20230511155131130]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/24061471d5814d5ba11f65a52042e9e2.png
[image-20230511160823702]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/e0c46bf9dd0546609b00458a5e176290.png
[image-20230511160856575]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/df3ad4b823a344d49cd37bfd8767ae63.png
[image-20230511163014923]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/6ba9c279239a473399b51c130bcfb719.png
[image-20230511163124208]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/10ab248308254a118f9d0958cfb99312.png
[image-20230511163254040]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/f44488cae63841d299c97dcaeabfcc7d.png
[image-20230511163552992]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/642d21a0e3724d17835aa049a7503b42.png
[image-20230511163732427]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/a8842c84e4fb41508d80af9facadd362.png
[image-20230511163825676]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/ab95814d94014178a90fbea941268593.png
[image-20230511164010502]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/c6813adf104544afa8a672c3042ec7ac.png