2024年最新MySQL面试题总结，单点登录（Single-Sign-On）解决方案

本文链接：https://blog.csdn.net/2401_84103216/article/details/138604024

最后

Java架构进阶面试及知识点文档笔记

这份文档共498页，其中包括Java集合，并发编程，JVM，Dubbo,Redis，Spring全家桶，MySQL，Kafka等面试解析及知识点整理

Java分布式高级面试问题解析文档

其中都是包括分布式的面试问题解析，内容有分布式消息队列，Redis缓存，分库分表，微服务架构，分布式高可用，读写分离等等！

互联网Java程序员面试必备问题解析及文档学习笔记

Java架构进阶视频解析合集

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

如何解决幻读？

MVCC 加上 间隙锁 的方式
（1）在快照读读情况下，mysql 通过 mvcc 来避免幻读。
（2）在当前读读情况下，mysql 通过 next-key 来避免幻读。锁住某个条件下的数据不能更改。

快照读：简单的 select 操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

三. 索引

索引分类

普通索引：加速查询
唯一索引：加速查询和唯一约束（此索引列的值不能重复，可含null）
主键索引：加速查询和唯一约束（此索引列的值不能重复，不可含null）
组合索引

索引使用原则

控制数量

索引数量不是越多越好，索引越多，维护索引的代价自然也就越高。对于 DML 操作比较频繁的表，索引过多会导致很高的维护代价。

使用短索引

索引使用硬盘存储，假如构成索引的字段长度比较短，那么在储块内就可以存储更多的索引，提升访问索引的 IO 效率。

建立索引

对查询频次较高且数据量比较大的表建立索引。索引字段的选择，最佳候选列应当从 WHERE 子句的条件中提取，如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

使用前缀索引

对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。前缀索引是一种能使索引更小更快的有效方法，缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 以及覆盖扫描。

选择合适的索引顺序

当不需要考虑排序和分组时，将选择性最高的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高，唯一索引的选择性是 1，因此也可以使用唯一索引提升查询效率。

删除重复索引

MySQL 允许在相同列上创建多个索引，重复索引需要单独维护，重复索引是指在相同的列上按照相同顺序创建的同类型的索引，应该避免创建。如果创建了索引 (A,B) 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引，对于 B-Tree 索引来说是冗余的。解决重复索引和冗余索引的方法就是删除这些索引。

索引失效的情况

1. like '%xx'
    select * from tb1 where name like '%cn';
2.使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
3. or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
4. 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
5. 不等于号 !=  
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
6. 大于号 > 
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
7. order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
8. 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引
    当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

B 树与 B+ 树简明扼要的区别

定义一条数据记录为一个二元组[key,data]：

key为记录的键值，key唯一

data为数据记录除 key 外的数据

B树

	**每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。**

在这里插入图片描述

B+树

	**只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。**

在这里插入图片描述

后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

主要原因：一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。

在MySQL中，最常用的两个存储引擎是MyISAM和InnoDB，它们对索引的实现方式是不同的。

InnoDB

data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。
在这里插入图片描述
MyISAM

data存的是索引(数据的地址)。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。
在这里插入图片描述

补充：

（1）在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。
在这里插入图片描述

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

（2）MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
在这里插入图片描述

Hash 索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。

限制：

数据不是按照索引值顺序存储的，无法排序。
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
只支持等值比较查询，不支持范围查询。

自适应哈希索引

自适应哈希索引是 InnoDB 的一个特殊功能，当它注意到某些索引被使用得很频繁时，会在内存中创键哈希索引，让 B-Tree 索引也具有哈希索引的一些优点。

覆盖索引

指一个索引包含所有需要查询字段的值，不再需要根据索引回表查询。

优点：

① 索引条目通常远小于数据行大小，如果只需要读取索引可以减少数据访问量

② 索引按照列值顺序存储，对于 IO 密集型的范围查询会比随机从磁盘读取每行数据的 IO 少得多。

③ 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 很有帮助。InnoDB 的二级索引在叶子节点保存了行的主键值，如果二级主键能覆盖查询那么可以避免对主键索引的二次查询。

Like 查询能否用到索引

使用 like x% 查询是可以用得到索引的，而使用 like %x% 和 like %x 查询是用不到索引的。

那么使用 like %x% 和 like %x 查询为什么用不到索引？

因为索引是一种有序的 B+ Tree 数据结构，叶子节点都是按照顺序从左向右排的，如果使用 like %x% 和 like %x 查询的话，不知道开头是哪个，就会去进行全表扫描。

联合索引（abc）命中规则

AND

只要用到了最左侧a列，和顺序无关,都会使用索引

a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引

不包含最左侧的 a 的不使用索引

c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引

OR 不使用索引

a = 1 AND b = 2 OR c = 3 ; 未使用索引
a = 1 OR b = 2 AND c = 3 ; 未使用索引
a = 1 OR b = 2 OR c = 3 ; 未使用索引

最左侧的‘a’列被大于，小于，不等于比较的，使用 range 索引

a > 1 AND b = 2 AND c = 3 ; 使用range索引
a < 1 AND b =  2 AND c = 3 ; 使用range索引
a > 1 ; 使用range索引
a <> 1 AND b = 2 AND c = 3 ; 使用range索引

最左侧 a=xx，后面列大于小于无所谓，都使用索引（但后面必须 and and ）

a = 1 AND b < 2 AND c = 3 ; 使用索引
a = 1 AND c = 2 AND b < 3 ; 使用索引
a = 1 AND b < 2 ; 使用索引
a = 1 AND b <> 2 AND c = 3 ; 使用索引
// 可以说 OR一出现就不使用
a = 1 AND b < 2 OR c = 2 ; 未使用索引

ORDER BY
a = xx，后面 order 无所谓都使用索引（和最上面的最左匹配一样）

a = 1 AND b = 2 AND c = 3 ORDER BY a;// 或者 ORDER BY b ， ORDER BY c ，ORDER BY d ; 使用索引
a = 1 ORDER BY a; // 或者 ORDER BY b,ORDER BY c,ORDER BY d ; 使用abc索引

b = xx，不使用索引

b = 1 ORDER BY a; //ORDER BY b 都 未使用索引

为什么主键通常建议使用自增 id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

三个范式

第一范式: 每个列都不可以再拆分.

第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分.

第三范式: 非主键列只依赖于主键,不依赖于其他非主键.

左外连接、右外连接与内连接的区别

内连接，显示两个表中有联系的共有数据，没有联系的数据不显示。
左链接，以左表为参照，显示数据，右表数据少了补NULL值，多了不显示。
右链接，以右表为参照，显示数据，左表少了补NULL，多了不显示。

四. 锁

MySQL 支持多用户同时读写吗？

mysql 支持多用户同时读写，mysql 是一个关系型数据库管理系统。

mysql中的锁机制就是为了解决共享资源的问题，当我们需要对一个数据读取的时候就需要获取读锁同理当我们需要对数据进行修改的时候就需要获取写锁，读取和写锁之间是互斥的。
mysql为了从不同程度上控制资源读写，相应引入全局锁，表级锁，行锁以及不同的锁类型。
mysql支持集群，一主多备，多主多备等，这样都能支持多用户同时读写

数据库并发场景

所有系统的并发场景都是三种，对于数据库而言为：

读-读：不存在任何问题，也不需要并发控制。
读-写：有线程安全问题，可能会造成事务隔离性问题，也就是脏读，不可重复读，幻读。
写-写：有线程安全问题，会存在更新丢失问题：第一类更新丢失（回滚丢失），第二类更新丢失（覆盖丢失）。

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：
1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

共享锁和排它锁

分为共享锁（S 锁）和排它锁（X 锁），也叫读锁和写锁。

读锁是共享的，相互不阻塞，多个客户在同一时刻可以读取同一资源。
写锁是排他的，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入。

写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但读锁不能插入到写锁前面。

乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

表锁和行锁

表锁和行表，开销小，不会出现死锁，但锁冲突概率高、并发度低。

行锁可以最大程度地支持并发，锁冲突概率低，但开销大，会出现死锁。行锁只在存储引擎层实现，InnoDB 实现了行锁。

数据库死锁

当多个事务以不同顺序锁定资源，或者同时锁定同一个资源时都可能产生死锁。

解决：

InnoDB 会自动检测，并使一个事务回滚，另一个事务继续。
设置超时等待参数 innodb_local_wait_timeout。

避免：

不同业务并发访问多个表时，约定以相同的顺序访问。
在事务中，如果要更新记录，使用排它锁。

五. 存储过程、函数、视图、触发器

存储过程

存储过程是由流控制和 SQL 语句组成的程序，经过编译和优化后存储在数据库服务器中，使用时只需要调用即可。

好处

使用流控制语句编写，具有较强的灵活性。
保证数据安全性，使没有权限的用户间接存取数据库。
保证数据完整性，使一组相关动作在一起执行。
调用存储过程前，数据库已经对其进行了语法分析，并给出优化执行方案，可以改善 SQL 语句的性能。
降低网络通信量，减小负载。

函数

由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。

和存储过程的区别：

存储过程的参数有 in，out，inout 三种，存储过程声明时不需要返回类型；函数参数只有 in，需要描述返回类型，且函数中必须包含一个有效的 return 语句。
存储过程可以有 0 或多个返回值，适合做批量插入、更新；函数有且仅有一个返回值，针对性更强。
存储过程可以返回参数，如记录集，函数只能返回值或者表对象。
存储过程一般作为独立部分执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，所以在查询中位于from 关键字后面，SQL 语句中不可以含有存储过程。

触发器

触发器是一段能自动执行的程序，和存储过程的区别是，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。触发器在对某一个表或者数据进行操作时触发，例如进行 UPDATE、INSERT、DELETE 操作时，系统会自动调用和执行该表对应的触发器。触发器一般用于数据变化后需要执行一系列操作的情况，比如对系统核心数据的修改需要通过触发器来存储操作日志的信息等。

视图

视图本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

六. 主从复制

为什么需要主从复制？

数据备份。
读写分离。
架构扩展，业务量越来越大，读写频率过高时，单机无法满足。

什么是主从复制？

指数据可以从一个mysql数据库服务器主节点复制到一个或者多个从节点。mysql使用异步复制方式。

主从复制过程

从库会生成两个线程,一个I/O线程,一个SQL线程;
I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
主库会生成一个log dump线程,用来给从库I/O线程传binlog;
SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

MySQL中有哪些日志？

mysql server层：

binlog (二进制日志)：记录数据库的变化情况，必要时可以使用二进制日志恢复数据库。
error log (错误日志)：记录Mysql实例每次启动、停止的详细信息，以及Mysql实例运行过程中产生的警告或者错误信息。默认开启，无法关闭。
general log (普通查询日志)：记录了Mysql运行的所有操作，无论这些操作执行成功与否。另外还包括一些事件，例如客户端连接断开的一些信息。默认不开启。
slow query log (慢日志)：记录执行时间过程和没有使用索引的查询语句。

innodb层：

redo log (重做日志)：记录的是物理数据页面的修改的信息。
undo log (回滚日志)：在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的。这一点是不同于redo log的。
relay log (中继日志)：从库同步主库时的一个中间文件。

七. 优化

查询执行流程

① 客户端发送一条查询给服务器。

② 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。

③ 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。

④ MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。

⑤ 将结果返回给客户端。

查询优化器？

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个

EXPLAIN 的字段

执行计划是 SQL 调优的重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执行计划，如果作用在表上，那么该命令相当于 DESC。EXPLAIN 的指标及含义如下：

指标名	含义
id	SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。
select_type	查询中每个 SELECT 子句的类型，例如 SIMPLE 表示简单查询，PRIMARY 表示复杂查询的最外层查询。
type	访问类型，性能由差到好：ALL、index、range（至少达到）、ref（要求）、const、system、NULL。
possible_keys	查询时可能用到的索引，列出大量可能索引时意味着备选索引太多。
key	查询时实际使用的索引，没有则为 NULL。
key_len	所用索引字段的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。
ref	表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows	估算找到所需记录所需要读取的行数。
Extra	额外信息，例如 Using temporary 表示需要使用临时表存储结果集；Using index 表示只需使用索引就可满足查询要求，说明表正在使用覆盖索引。