Mysql高级面试点汇总

VivianStark

已于 2023-10-27 18:38:43 修改

阅读量195

点赞数

文章标签： mysql 面试数据库

于 2023-02-21 21:29:40 首次发布

本文链接：https://blog.csdn.net/VivianStark/article/details/129150041

版权

索引

索引数据结构使用B+Tree，而非B-Tree、hash的原因

1、非叶子节点不存储data，只存储索引，可以放更多的索引
2、叶子节点包含所有索引字段
3、叶子节点用指针连接，提高区间访问（范围查询）的性能
4、一个节点（一行/一页）mysql给分配16kb大小，一个索引(主键bigint)约8b，一个索引地址(指针)约6b，16kb/(8+6)b =1170，也就是说非叶子节点可以放1170个元素，第二层的非叶子节点同样也可以放1170个元素，
叶子节点存储数据算1kb，则可以存储16个元素；所以总共可存储的元素为1170乘1170乘16，差不多有上千万的索引。
5、在查找数据时一次页的查找代表一次 IO，因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少,指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。
b+数结构中，如下图，非叶子节点索引15和56之间白色区域存储的是对应下一页的地址(第二行最左边页)
B+Tree结构
在这里插入图片描述
B-Tree结构

索引最左匹配原则

比如针对a,b,c三个字段建立一个联合索引，由于底层的B+树是按照a,b,c三个字段
从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则

explain中重要的列

select_type列

表示对应行是简单还是复杂的查询。
1、simple：简单查询。
2、primary：复杂查询中最外层的 select
3、subquery：包含在 select 中的子查询（不在 from 子句中）
4、derived：包含在 from 子句中的子查询。

type列：

表示访问类型，依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
1、const, system：用于主键查询，最多匹配一行数据。system是特例表示表里只有一条数据
2、eq_ref：主键索引的所有部分被连接使用，出现在关联查询中，最多只会匹配到一条数据
3、ref：是使用普通索引或者唯一性索引的部分前缀，可能匹配到多条记录
4、range：使用索引范围扫描，通常出现在 in(), between ,> ,<, >= 等操作中
5、index：全表扫描某个二级索引，扫描完就能拿到全部结果，不用再回表（比如只有id、name字段的表，id为主键，name为普通索引，查全表就会走name普通索引，会拿到name和id的所有信息）
6、ALL：全表扫描，扫描聚簇索引的所有子节点（普通的不带条件查全表）

key列：

实际采用哪个索引

Extra列

额外信息
1、Using index：使用了覆盖索引，覆盖索引（所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了）
2、Using where：查询的列未被索引覆盖，就可以使用覆盖索引优化
3、Using temporary：需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，比如用非索引字段distinct
4、Using filesort：将用外部排序而不是索引排序，即排序字段没有使用索引

Using filesort文件排序方式

优化器会根据sort buffer（默认1M）大小自动来选择用哪种方式
单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

双路排序（又叫回表排序）：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段

回表

二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表

索引下推

索引下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
SELECT * FROM xxx WHERE name like 'LiLei%'AND age = 22; 联合索引(name,age)
非索引下推：根据name模糊匹配到id，再回表找到满足的记录；将记录交给服务层，服务层根据age再对数据做过滤。
有了索引下推：按照最左前缀原则，在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age这个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据；

索引优化措施

1、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换）
2、尽量使用覆盖索引，否则会进行回表，减少 select * 语句
3、!=，<>，not in，not exists，is null,is not null，%xxx的时候无法使用索引
4、尽量在索引列上完成排序，遵循索引建立顺序时的最左前缀法则。
5、如果order by的条件不在索引列上，就会产生Using filesort。
6、group by其实质是先排序后分组，如果不需要排序的可以加上order by null禁止排序。注意where高于having
7、关联查询关联字段加索引，小表驱动大表
8、like左右模糊查询优化：尽量使用右模糊 ‘xxx%’，可以用到索引

深分页优化

select * from t_order order by id limit 1000000, 10；
t_order 表数据达到千万上亿级别，执行这条sql时，会查1000010条记录，然后筛选出后10条，严重影响性能。可以通过以下方式来优化
1、利用VtDriver进行流式处理，采用流式处理 + 归并排序的方式，配置临界值，达到临界值时采用流式处理。
2、sql优化，可以从根本解决
范围查询：select * from t_order where id > 100000 LIMIT 10；
子查询：select * from t_order where id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;
区间限制法：select * from t_order where biz_content = #{bizContent} and id < #{lastId} and id >={minId} order by id desc limit 10；其中最小minId 通过大数据平台的离线计算。lastId为上次查询终止的id。

事务、锁机制

事务四大特性

原子性：由undo log日志来实现，
隔离性：由MySQL的各种锁以及MVCC机制来实现
持久性：由redo log日志来实现
一致性：由其它3个特性以及业务代码正确逻辑来实现

锁分类

从粒度分：表锁、页锁、行锁

从类型分：读锁（共享锁）、写锁（排它锁）、意向锁、间隙锁

意向锁：当事务对数据行加了读锁或写锁后，同时会给表设置一个标识（代表已经有行锁），其他事务再想对表加锁，就不必再逐行判断有没有行锁，而是用这个标识就绪。

间隙锁：在可重复读下生效，锁的是两个值之间的间隙，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，这样就能防止其它事务在这个间隙范围内插入数据，例如：(-xx,1)(1,4)(4,9)(9，xxx)

Next-key : 间隙锁+右记录锁。例如：(-xx,1](1,4](4,9](9，xxx)

行锁：行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁)

RR级别会升级为表锁原因：RR（可重复读）级别下会把扫描过得索引和间隙都会加上锁，如果索引失效，则会扫描全表，导致变为表锁

优化措施

1、将查询等数据准备操作放到事务外
2、更新等涉及加锁的操作尽可能放在事务靠后的位置
3、尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
4、合理设计索引，尽量缩小锁的范围
5、尽可能减少检索条件范围，避免间隙锁

MVCC多版本控制

sql查询语句在一个事务里多次执行查询结果相同
实现原理：undo日志版本链和read view事务一致性视图

可重复读的隔离级别下使用了MVCC机制，select操作是快照读（历史版本）；insert、update和delete是当前读（当前版本）

undo日志版本链：一条数据多次修改时，会将undo日志通过roll_pointer串在一起形成一个版本链

read view事务一致性视图：执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成。

版本链比对规则：
1、如果 row 的 trx_id 落在绿色部分( trx_id<min_id)，表示这个版本是由已提交事务生成的，可见的
2、如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
3、如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
3.1、若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；
3.2、若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。
在这里插入图片描述

底层原理

mysql内部组件

MySQL 可以分为 Server 层和存储引擎层两部分
Server 层主要包括连接器、查询缓存、分析器、优化器、执行器等
连接器：负责和各种客户端建立连接，进行用户权限校验
查询缓存：执行select 语句后会将结果缓存起来，下一次执行相同select语句时会直接命中缓存（很鸡肋）
分析器：对sql语句进行解析，会进行词法分析、语法分析、构建执行树、生成执行计划等
优化器：进行sql优化，决定使用哪个索引，根据效率选择执行方案
执行器：校验操作表的权限，和引擎层交互操作数据

Innodb引擎SQL执行的底层原理

以update为例
1、从磁盘上找到该条数据所在的缓存页，将其加载到bufferPool中
2、将原来的数据写入undo日志文件，方便回滚数据
3、将新值更新到bufferPool缓冲区
4、开始写redo日志，先将数据写入redo log Buffer缓存区
5、准备提交事务
5.1、redo日志顺序写入磁盘
5.2、binlog日志写入磁盘，并写入conmit标记到redo日志中，保证redo和binlog数据一致
6、系统空闲时，用另外的线程将bufferPoll缓存区的数据，以页为单位随机写入磁盘

bufferPool结构

默认是128MB，建议调整innodb_buffer_pool_size为物理内存的60%~80%，申请之后划分了N个空的缓存页和一些链表结构。
缓存页大小是16kb，每个缓存页都有对应的控制块，控制块是缓存页大小的5%，控制块包含了表空间号，页号，在bufferpool中的地址，LSN信息等。

free链表：所有空闲的缓存页对应的控制块作为一个节点放到一个链表中组成，每当从磁盘加载一页到Buffer Pool 中时，就从 free 链表中取一个空闲的缓存页。

缓存页的hash：用表空间号+页号作为key，缓存页作为value组成的哈希表；这样就可以避免每次都从磁盘获取页数据。

flush链表：结构和free链表差不多，存储修改过的缓存页对应的控制块；其对应的缓存页都是需要被刷新到磁盘上的。

LRU链表管理：按照最近最少使用的原则去淘汰缓存页，把页从磁盘加载到 Buffer Pool 中的缓存页时，就把该缓存页对应的控制块作为节点塞到 LRU 链表的头部，每次使用缓存页时，就把他移动到头部，淘汰尾部数据。

刷新脏页到磁盘：1、从 LRU 链表的冷数据中刷新一部分页面到磁盘。2、从 flush 链表中刷新一部分页面到磁盘。

change buffer写缓冲

它是一种应用在非唯一普通索引页不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更（Buffer Changes），等未来数据被读取时，再将数据合并（Merge）恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。
只适用于非唯一普通索引页的原因
如果索引设置了唯一（Unique）属性，在进行修改操作时，InnoDB必须进行唯一性检查。也就是说，索引页即使不在缓冲池，磁盘上的页读取无法避免（必须从磁盘页上读取到数据才能做唯一性检查）。

适合场景
1、数据库大部分是非唯一索引
2、业务是写多读少，或者不是写后立刻读取

写入数据时
1、在写缓冲中记录这个操作
2、写入redo log，一次磁盘顺序写操作

查询数据时
1、buffer pool缓冲池未命中，通过IO操作载入索引页
2、从写缓冲读取相关信息，再将数据合并（Merge）恢复到buffer pool缓冲池中

WAL预写机制

在事务的具体实现机制上，所有的修改都先被写入到日志中，然后再被应用到系统中。

redo log

redo 是物理日志，记录的是“在某个数据页上做了什么修改”，占用的空间非常小，是顺序写入磁盘的，顺序写是随机写的两个数量级的效率。
redo log只会记录未刷盘的日志，已经刷入磁盘的数据都会从redo log这个有限大小的日志文件里删除（此处刷盘指的是数据从bufferpool刷入磁盘）。

redo log buffer刷盘时机：
1、log buffer 空间不足时
2、事务提交时
3、后台线程每秒刷一次

LSN：redo日志的序列号，唯一自增（包括写入磁盘和未写入磁盘的）

innodb_flush_log_at_trx_commit 写入策略
写入顺序：redo log buffer <后台线程，每1s通过操作系统函数write> page cache <操作系统函数fsync写入> 磁盘文件
0：事务提交时都只是把redo log写入redo log buffer中,数据库宕机可能会丢失数据。
1(默认值)：事务提交时都将redo log直接持久化到磁盘，数据最安全。
2：事务提交时都只是把 redo log写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

MySQL 崩溃恢复
系统宕机了，bufferpool里的数据还没来得及写入磁盘，首先会获取日志检查点信息，随后根据日志检查点信息使用 Redo Log进行恢复。如果事务未提交，则接下来使用Undo Log回滚数据。如果事务已经提交，则用Redo Log恢复数据即可。

binlog

binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2这的 c 字段加 1 ”
binlog是追加日志，保存的是全量的日志

binlog_format日志格式
STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能。
ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，但这种方式日志量较大，性能不如Statement。假设update语句更新10行数据，则记录被修改的10行数据。
MIXED：混合模式复制，前两种模式的结合，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种

sync_binlog 写入策略
0(默认值)：每次提交事务都write到page cache，由系统自行判断什么时候执行fsync写入磁盘。
1：表示每次提交事务都会执行fsync写入磁盘，这种方式最安全。
N(N>1)：表示每次提交事务都write到page cache，但累积N个事务后才fsync写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

binlog日志文件重新生成时机
服务器启动或重新启动
服务器刷新日志，执行命令flush logs
日志文件大小达到 max_binlog_size 值，默认值为 1GB

删库跑路，数据如何恢复问题
推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据
数据库备份：mysqldump
binlog恢复数据：mysqlbinlog --no-defaults --start-position=219 --start-position=701 --database=test D:xxxxx/data/mysql‐binlog.000001 | mysql ‐uroot ‐p123456 ‐v test

undo log

采用回滚段的方式管理文件，每个回滚段记录了1024个undo log segment ，每个事务只会使用一个；InnoDB支持最大128个回滚段，故其支持同时在线的事务为 128*1024

undo log日志删除时机
新增类型的，在事务提交之后就可以清除掉了。
修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

主从复制

主从复制流程

1、slave通过IO线程连接到master同步数据
2、master收到请求后，通过log dump线程将binlog日志发送给slave
3、slave收到master同步的binlog数据后先写入relay log文件
4、slave通过SQL线程将relay log文件写入到binlog文件，再解析文件写入数据库

主从复制模式

异步复制

默认使用该模式，master将binlog日志同步给slave后，并不需要经过slave的确认，就可以提交事务了。

半同步复制

master将binlog日志同步给slave后，必须确保binlog日志已经写入slave 的relay log日志中，收到slave给master的响应后，才能提交事务。

全同步复制

master将binlog日志同步给slave后，binlog日志写入slave 的relay log日志中，并将relay log数据写到slave的binlog，且解析完写入slave数据库，即slave提交完事务，master才能提交事务。

MGR组复制

由多个节点共同组成一个复制组，一个事务的提交，必须经过组内半数以上节点决议并通过，才能得以提交。
一个复制组由若干个节点(数据库实例)组成，组内各个节点维护各自的数据副本，通过一致性协议实现原子消息和全局有序消息，来实现组内实例数据的一致。
引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体)，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案