MySQL面试题

1 having关键字的用法（⭐）

用于对分组之后的结果集进行筛选，可以用聚合函数

2 where与having的区别

1. where必须在group by分组之前使用，having必须在group by分组之后使用
2. where后面不能用聚合函数，having可以用聚合函数

3 关联查询

左连接 left join，以左边的表为主表，右边的表只显示能够匹配的行；

右连接 right join，以右边的表为主表，左边的表只显示能够匹配的行；

内连接 inner join，两边的表都只显示能够互相匹配的行。

4 主键与外键

主键是一个特殊的索引，具有唯一性，即主键字段的值不能重复且不能是空值；

主键用于提高数据表的查询速率，同时也可以用作每行记录的唯一标识；

主键可以是一个字段，也可以是多个字段的组合。

外键是一个关联其他表主键的字段，一个表可有多个外键。

5 索引

5.1 为什么要使用索引（索引的优点）

索引可以提高查询效率，如果不使用索引，mysql默认的检索规则是从第一行开始读完整张表，表越大，查询花费时间越长。

5.2 索引有几种（⭐）

1 单列索引：普通索引、唯一索引、主键索引

2 组合索引

3 全文索引

4 空间索引

5.3 唯一索引与主键索引的区别

唯一索引值是唯一的，允许有空值NULL；

主键索引是一种特殊的唯一索引，要求值是唯一的且不能有null值；

5.4 如何触发索引（⭐）

触发索引遵从最左前缀原则，即必须在where条件中先查询组合的最左边字段才会触发索引

5.5 索引的使用原则（⭐）

对数据量大（>50w）且查询频繁（QPS > 1000/S）的表建立索引，并且应在重复值较少的列建立索引。

QPS——每秒查询速率

5.6 索引优化（⭐）

1. 应在查询频繁的表中建立索引；
2. 应在重复值较少的字段建立索引；
3. 索引数量不要超过5个；索引并不是越多越好，创建和维护索引也有内存开销，如果我们有大量的索引，索引文件可能会比数据文件更快达到上限值。

6 存储过程的优点与缺点（⭐）

优点：是预编译的，提高数据库执行速度

缺点：1 不是跨平台的，无法迁移； 2 不易调试

7 存储过程的作用（⭐）

存储过程中封装了复杂的SQL语句集，经过编译并保存在数据库中，实现了SQL 语言层面的代码封装与重用。

8 事务的四个特性（⭐⭐）

1. 原子性(Atomicity)：事务是最小单位，不可再分
2. 一致性(Consistency)：事务要求所有的DML（insert/update/delete）语句操作的时候，必须保证同时成功或者同时失败
3. 隔离性(Isolation)：事务A和事务B之间相互独立，互相不影响

隔离级别（4个）：

(1)读未提交：read uncommitted

(2)读已提交：read committed

(3)可重复读：repeatable read（默认）

(4)串行化：serializable

1. 持久性(Durabilit)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

9 不同事务隔离级别存在的问题（⭐）

读未提交	读已提交	可重复读（默认）	串行化
脏读
不可重复读	不可重复读
幻读	幻读	幻读

=====================================================================

1. 脏读：指的是读到了其他事务未提交的数据；
2. 不可重复读：指的是在一个事务中，不同时刻读同一批数据，读到的结果是不一样的，可能会受到其他事务影响；
3. 幻读：事务A修改了某一行记录，在未提交之前，另一个事务B插入了一行与事务A修改之前相同的记录，并先提交了事务。此时在事务A中查询发现好像修改没起作用，但其实是另一个事务新插入的记录，这就称为幻读。

9 什么是死锁？（⭐）

多个事务（或线程）因争夺系统资源而产生相互等待的现象，称为死锁。

10 如何避免死锁？（⭐）

两种避免办法：

1. 如果一个线程的请求会导致死锁，则不启动该线程；

2. 如果一个线程的增加资源请求会导致死锁 ，则拒绝该申请。

避免死锁的具体实现通常利用银行家算法：

在线程开始执行前，允许动态地申请资源，但系统在进行资源分配之前，先计算此次分配资源是否全部满足，若不满足继续等待。

11 死锁产生的四个必要条件（⭐）

互斥：某种资源一次只允许一个线程访问，直到该线程执行结束。

占有且等待：一个线程本身占有资源，同时还有资源未得到满足，正在等待其他线程释放该资源。

不可抢占：别人已经占有了某项资源，不能再被抢占。

循环等待：存在一个进程链，使得每个进程都占有下一个进程所需的至少一种资源。 

12 MySQL有几种引擎

MyISAM：使用的是表级锁，在执行查询语句前自动给涉及到的表加读锁；在执行DML语句前自动给涉及到的表加写锁；

InnoDB：是MySQL8的默认引擎，使用的是行级锁，会自动给DML语句涉及到的多行记录加写锁；InnoDB行锁是通过给索引项加锁来实现的，意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

13 读锁与写锁（⭐）

读锁，又叫共享锁，一行记录如果被事务占用，该行记录对其他事务而言可读不可写的；

写锁，又叫排他锁，一行记录如果被事务占用，该行记录对其他事务而言不可读也不可写；

DML语句（insert/update/delete）执行时默认是使用排他锁；

14 select如何实现排他锁？（⭐）

在select语句后面加上for update

15 悲观锁与乐观锁（⭐）

悲观锁，先加锁再操作数据；

乐观锁，先操作数据，提交时通过比对版本号的大小来控制sql是否能执行成功；

16 BTree索引结构的原理（⭐）

InnoDB引擎中索引的底层结构是BTree，是一颗多路平衡搜索树，特点是：

1 所有叶子节点都在同一层；

2 每一个叶子节点都包含指向下一个叶子节点的指针；

3 每一个非叶子节点至少包含两个孩子；

和二叉树相比，BTree查询数据的效率更高， 因为对于相同的数据量来说，BTree的层级结构比二叉树小，因此搜索速度快。

17 数据库设计范式

第一范式，每一列的值都不可再分；

第二范式，一个表必须有一个主键，其他字段必须完全依赖主键；

第三范式，任何非主键字段不能依赖其他非主键字段；

18 数据库有几种约束

非空约束、默认约束、主键约束、唯一约束、外键约束

19.索引相关概念：

索引是一种数据结构，用于提高数据的查询效率；

索引的分类：

我们可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

索引为什么使用b+？

1.这种结构胖矮！矮的作用就是减少io操作，因为每次进行行的跳转就会涉及到io操作；胖是一个层级可以存储很多数据，3层机构就可以存储千万级别的数据；

2.所有叶子节点都在同一层；

3.所有叶子节点之间通过双向链表进行连接；

聚簇索引与普通索引的区别？

聚簇索引：1.非叶子节点存储的是id和页号；2.叶子节点存储的是行数据；

普通索引：1.非叶子节点存储的是id，索引列和页号；2.叶子节点存储的是id和索引列；

索引的一些现象和行为？

索引覆盖：是指需要查询的字段全都被索引包含；

回表查询：在普通索引的情况下，查询的字段要多于我们的索引字段，此时查找到id后回到聚簇索引在查找行数据；

索引下推：能使用索引的情况下MySQL会尽量使用索引；即使不满足最左前缀原则也可能会使用；5.6之前的版本是根据索引找到一条数据行，检索数据其他的过滤条件；而现在是现在引擎层判断完全部的条件之后再将结果返回；

索引跳跃：MySQL8之后的索引查询，即使不满足最左匹配原则，MySQL可能优化后还是走的索引；

那种情况不会发生索引跳跃：

1.不是单表查询，而是多表关联；

2.select中的字段包含非索引字段；

3.sql中带有group by或者distinct关键字；

索引失效情况：

不满足最左前缀；

对索引列进行了运算；

mysql认为使用索引效率低于全表扫描；

索引建立原则：

不要创建太多索引，因为索引也是占用磁盘的，索引的维护也需要消耗性能；

对于频繁查询的字段建立索引；

对于重复值较少的列建立索引；

20.主从复制

首先说一下主从复制：

意思就是主库的数据经过一定的处理写入到从库中的过程就叫做主从复制；

主从复制流程：

首先在主库中将数据写入到binlog中；

然后从库的io线程通过网络传输将binlog数据写入从库的中继日志中；

然后从库通过sql线程会读取中继日志中的事件，并在从库上重放（或重做）这些事件，从而将修改应用到从库的数据存储中。；

binglog日志（逻辑日志或者二进制日志）：

他有三种状态：statement（记录的具体sql）、row（记录哪一行，id，修改信息），mixed；

缺点：

1.不支持事务

2.效率不够高；

3.宕机无法恢复

为了解决binlog缺点，使用redo log：

数据页的变更直接记录到磁盘上；

21.隔离级别和mvcc

事务特性：

原子、一致、隔离、持久

事务隔离级别：

读未提交、读已提交、可重复读、串行化

事务隔离级别的问题？

读未提交：脏读：读到了其他事务还没有提交的数据

读已提交：不可重复读：在不同时间读取同一批数据但是结果不一致的问题；

可重复读：幻读：数据量不一致问题，一个事务更新后未提交，另一个事务插入了一条更新之前的数据，这个事务查询发现好像是修改失败，但实际上是新插入一个记录；

串行化：没有问题，但是开销大；

mvcc多版本并发控制：通过多个版本链解决并发情况下数据返回结果不一致的问题

mvcc的组成成分：

1.undolog

2.mysql的隐藏字段trx_id和指向上一个版本的指针

mvcc为了解决什么问题？

为了解决不可重复读的问题：

如何解决不可重复读问题？

通过readview（读视图）解决；

readview组成成分：最大事务id、最小事务id、当前事务id、活跃事务列表；

不可重复读就是通过在第一次就固定readview；

而读已提交是每次更新数据都会变更readview；

具体实现：1.trx_id小于最小事务id（可读）；2.trx_id大于最大事务id(不可读)3.在最小值与最大值之间，但是不在活跃事务列表(可读)；4.在最小值与最大值之间，但是在活跃事务列表(不可读)

什么是快照读和当前读？

快照读：快照读的执行方式是生成readview，然后通过mvcc机制进行读取的，不会对记录加锁；

当前读：每次读取的都是最新的数据，通过加锁的形式实现的；

当前读加锁的几种形式？

select lock in share mode；

select for update；

dml操作；

快照读和当前读来解决幻读问题

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

间歇锁？

当我们的索引使用范围查询时，并且请求共享锁或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间歇锁，间歇锁的左右都是开区间的；

间歇锁加行锁，即左开右闭的组合叫做next-key lock；

间歇锁和next-key lock的引入帮我们解决幻读问题；

22.间歇锁和幻读、RR级别下的间歇锁的调优和死锁案例、MySQL调优的方法和工具

第一部分 间歇锁和幻读

间歇锁是什么？

间歇锁就是我们的查询索引使用的范围查询时，会将索引之间不存在的间隙进行加锁操作，避免其他事务进行插入操作；

例子：select 。。。 where id>10 就会为10以后的数据加锁；

版本链的理解？

版本链是记录的当前数据的不同版本的信息，以及事务id和指向上一个事务的指针；

MySQL如何解决不可重复读的问题？

通过mvcc多版本并发控制进行解决的；

具体的就是通过undolog以及mysql隐藏的字段事务id和指向上一个版本的指针，然后结合readvie实现的；

读已提交和可重复读的事务实现原理的区别？

主要还是read view的生成时机，读已提交是每次查询都会更新readview；而可重复读只在第一次查询数时确定了read view，之后的查询就不修改了；

当前读和快照读？

快照读：简单的select语句，通过执行readview结合mvcc解决不可重复读问题，不加锁；

当前读：时对读取的数据进行加锁操作；

MySQL解决幻读了吗？

解决了快照读语义上的幻读；

未解决当前读语义上的幻读；

第二部分：RR级别下的间歇锁的调优和死锁案例

间歇锁会导致什么问题？

锁冲突和性能下降：当多个事务试图锁定相同的间隙范围时，它们可能会相互阻塞，导致锁冲突和性能下降。这是因为每个事务都需要等待其他事务释放锁，才能继续执行其操作。

无法插入锁定范围内的数据：由于间隙锁会锁定范围内的间隙，即使间隙中不存在任何数据，其他事务也无法在该范围内插入新的数据。这可能导致死锁或其他并发问题，特别是在高并发的场景下。

死锁：当两个或多个事务相互等待对方释放资源时，可能会发生死锁。在间隙锁的场景下，如果一个事务持有一个间隙锁并等待另一个事务释放其持有的间隙锁或行锁，而另一个事务又持有另一个间隙锁并等待第一个事务释放其锁时，就可能会发生死锁。

如何解决这些问题？

第一种：降低隔离级别，使用读已提交；

第二种：使用分布式锁，因为这先查后改一定会涉及到并发的问题，而MySQL的悲观锁策略先加锁在操作数据，一方面是性能低，另一方面就可能会导致死锁，所以使用redis的分布式锁，先查后改，改完后释放锁，然后其他事务在查发现已被修改或者存在；

死锁案例？

这里事务a有select for update where id=3 然后insert id=4

这里事务b有select for update where id=3 然后也是insert id = 4的情况；

在事务a现对3加锁，然后对4加锁；而事务b现队b加锁，然后对a加锁，a锁完3想要锁4的时候，等待b释放锁4；

而b锁完4想要锁3的时候，等待a释放锁3；

第三部分：MySQL调优的方法和案例？

一般大方向可以分为一下几种：

硬件方面：

例如cpu、内存、硬盘、mysql版本、连接池数量等；

架构方面：

读写分离、分库分表、添加缓存（redis）

sql优化方面（主要）：

可以为合适的字段建立索引；

尽量不要使用*，会增加sql优化器的负担；

limit的优化，可以先通过id定位到你要查找的页数，然后再查询指定数据；

尽量避免使用子查询，可以替换为连接查询；

索引数也不要过多；

为group by和order by后面的字段建立索引；

MySQL调优分为以下两个方面，一个是性能，一个是sql？

性能调优：

1.硬件方面的调优操作：使用更好的cpu、硬盘、mysql版本等；

2.架构方面的调优操作：主从复制、读写分离、分库分表、加缓存缓解压力；

SQL调优：

不要建立太多索引，因为索引的维护也是一个不小的开销；

尽量查询根据业务查询需要字段，实现索引覆盖现象，尽量不要出现回表查询；

多个普通索引可以再合适的情况下优化为联合索引；

按照最左匹配原则使用联合索引；

通过explain关键字判断是否使用索引；

小表驱动大表；

explain关键字的使用？

type列：效率从高到底

system，const，eq_ref,ref,range,index, all

all：全表扫描，

index：索引扫描全表；

range：范围扫描，这个类型通常出现在使用了索引列的范围查询（如BETWEEN、<、>等）时；

ref：使用非唯一性索引或唯一性索引的前缀来检索单个行的匹配项。；

eq_ref:使用唯一性索引查找单个行的匹配项；

const：通常用于通过主键或唯一索引进行的等值查询，而system是const的特例，当查询的表只有一行时

system：const的特例；

extra列说明？

using index：索引覆盖；

using where using index：查询没有满足最左前缀原则，但是二级索引列中包含查询字段；

using index condition：查询的列不完全被索引覆盖，可能会回表查询；

using filesort：按照文件排序；