2021 大厂面试题之 mysql 篇
简单讲讲innodb索引原理？
主键索引
innoDB 主键索引是一棵树， 联合索引是另外一棵树。
回表的意思，就是select a,b,c from table where a='1' and b='2' 这种，先去联合索引里找到索引a b 索引，然后对应叶子节点上有id，这时候通过id 再去 主键索引那棵树上找，通过id 找到对应 a,b,c 三个字段。
B+树和B树的区别？
1、b树所有键值分布在整颗树中（索引值和具体data都在每个节点里，包含非叶子节点）；B+树所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
2、b树的叶子节点没有指针；B+树所有叶子结点增加了一个链指针3、因为内节点并不存储 data，所以一般B+树的叶节点和内节点大小不同，而B-树的每个节点大小一般是相同的，为一页。
联合索引的数据结构是怎样？
MYSQL explain 各个字段简单讲下
MySQL的隔离级别有哪些？
read uncommit 读未提交 （没有提交的事务仍然可以读取到）
read commit 读已提交（只能读到已提交的事务）
read repeat 重复读（一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的）
serializable 串行化
读未提交（readuncommitted）、读提交（read committed）、可重复读（repeatable read）串行化serializable ）。
下面逐一解释：
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
什么是MySQL回表
通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，
如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
假设，现在我们要查询出 id 为 2 的数据。
那么执行 select * from xttblog where ID = 2;这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。
主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。
但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2;原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。
这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。
因此，我们在应用中应该尽量使用主键查询。
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低
如何解决回表问题？
这里引出索引覆盖，所谓索引覆盖只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
什么是覆盖索引
ALTER TABLE `staffs` ADD INDEX idx_staffs_nameAgePos(`name`, `age`, `pos`);
使用覆盖索引查询：
EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name`='July' AND age=25 AND pos='dev'
以下这些查询也可以使用覆盖索引：-- 范围查询
EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name`='July' AND age>25 AND pos='dev'
-- 查询条件中使用部分字段
EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name`='July' AND age=25;
-- 查询列中使用部分字段
EXPLAIN SELECT `name` FROM staffs WHERE `name`='July' AND age=25;索引失效例子有哪些?
对索引列运算
运算包括（+、-、*、/、！、<>、%、like'%_'（%放在前面）、or、in、exist等），导致索引失效
select * from User where age/2 >10
null 无法使用索引
使用 is null 或者 is not null 也不能使用索引,把字段改成空字符串，不要用 null
explain select * from User where name is not null; ```
like 已通配符开头
explain select * from User where name like '%李%';使用不等于
explain select * from User where name != '李国辉'
简单描述索引下推
假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。
那么，查询语句是这么写的：mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？
当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。
但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。
图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。
因此，需要回表 4 次。
图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。
在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
聚簇索引和非聚簇索引的区别
先来一张带主键的表，如下所示，pId是主键
聚簇索引
在Mysql中是没有语句来另外生成的。在Innodb中，Mysql中的数据是按照主键的顺序来存放的。那么聚簇索引就是按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。
如上图所示，分为上下两个部分，上半部分是由主键形成的B+树，下半部分就是磁盘上真实的数据！那么，当我们， 执行下面的语句
select * from table where pId='11'
那么，执行过程如下
如上图所示，从根开始，经过3次查找，就可以找到真实数据。如果不使用索引，那就要在磁盘上，进行逐行扫描，直到找到数据位置。显然，使用索引速度会快。但是在写入数据的时候，需要维护这颗B+树的结构，因此写入性能会下降！
接下来引入非聚簇索引。
非聚簇索引
复合索引、前缀索引、唯一索引，都是属于非聚簇索引。
create index index_name on table(name);
此时结构图如下所示
如果我们执行下列语句
select * from table where name='lisi'
此时结构图如下所示
通过上图红线可以看出，先从非聚簇索引树开始查找，然后找到聚簇索引后。根据聚簇索引，在聚簇索引的B+树上，找到完整的数据！
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。如果觉得还有帮助的话，可以点一下右下角的【推荐】。】
MYSQL如何实现事务
InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。
undo log
实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：
当事务对数据库进行修改时，InnoDB会生成对应的undo log；
如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子undo log属于逻辑日志，它记录的是sql执行相关的信息。
当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：
对于每个insert，回滚时会执行delete；
对于每个delete，回滚时会执行insert；
对于每个update，回滚时会执行一个相反的update，把数据改回去。
以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态
redo log
背景，Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。
如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。
redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？
主要有以下两方面的原因：
（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。
binlog 和 redo log 怎么保持一致的？
binlog记录了数据库系统所有的更新操作，主要是用来实现数据恢复和主从复制的。
一方面，主从配置的MySQL集群可以利用binlog将主库中的更新操作传递到从库中，以此来实现主从数据的一致性；另一方面，数据库还可以利用binlog来进行数据的恢复。
redo log是用来实现事务的持久性，即当事务在提交时，必须先将该事务的所有操作日志写到磁盘上的 redo log file进行持久化，这也就是我们常说的 Write Ahead Log 策略。
有了redo log，在数据库发生宕机时，即使内存中的数据还没来得及持久化到磁盘上，我们也可以通过redo log完成数据的恢复，这样就避免了数据的丢失。
redo log 和 binlog的区别
redo log和binlog的产生方式不同。redo log是在物理存储引擎层产生，而binlog是在MySQL数据库的Server层产生的，并且binlog不仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对数据库的更改都会产生binlog。
redo log和binlog的记录形式不同。
MySQL Server层产生的binlog记录的是一种逻辑日志，即通过SQL语句的方式来记录数据库的修改；而InnoDB层产生的redo log是一种物理格式日志，其记录的是对于磁盘中每一个数据页的修改。
redo log和binlog记录的时间点不同。binlog只是在事务提交完成后进行一次写入，而redo log则是在事务进行中不断地被写入，redo log并不是随着事务提交的顺序进行写入的，这也就是说在redo log 中针对一个事务会有多个不连续的记录日志。
综上所述，binlog和redo log都是在事务提交阶段记录的。
这时我们不禁会有一些疑问：
是先写binlog还是先写redo log的呢？
写binlog和redo log的顺序对于数据库系统的持久性和主从复制会不会产生影响？
如果有影响，MySQL又是怎么做到binlog和redo log的一致性的呢？
两阶段提交原理描述:第一阶段： InnoDB Prepare阶段。
此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。
此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。
此阶段对binlog不会有任何操作。
第二阶段：commit 阶段，这个阶段又分成两个步骤。
第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；
第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。可以看出，此过程中是先写redo log再写binlog的。
但需要注意的是，在第一阶段并没有记录完整的redo log（不包含事务的commit标签），而是在第二阶段记录完binlog后再写入redo log的commit 标签。
还要注意的是，在这个过程中是以第二阶段中binlog的写入与否作为事务是否成功提交的标志。
通过上述MySQL内部XA的两阶段提交就可以解决binlog和redo log的一致性问题。
数据库在上述任何阶段crash，主从库都不会产生不一致的错误。
UPDATE 语句的执行过程是怎样
update 语句时的内部流程。
执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成
mysql 主从同步的原理
1.主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2.从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；3.从：sql执行线程——执行relay log中的语句；
MySQL 主从复制涉及到三个线程：一个在主节点的线程：log dump thread从库会生成两个线程：一个 I/O 线程，一个 SQL 线程如下图所示:
主库会生成一个 log dump 线程,用来给从库 I/O 线程传 Binlog 数据。
从库的 I/O 线程会去请求主库的 Binlog，并将得到的 Binlog 写到本地的 relay log (中继日志)文件中。SQL 线程,会读取 relay log 文件中的日志，并解析成 SQL 语句逐一执行。