【java面试题-MySQL篇-2024】

##java面试题大全

详细面试题-持续更新中-点击跳转

点赞、收藏、加关注
·
·

1、SQL 执行顺序

语句：

select distinct
    查询字段
from
    左表
(left|inner|right) join 
    右表
on 
    连接条件
where
    筛选条件
group by
    分组字段
having
    分组条件
order by
    排序字段
limit
    分页;

执行顺序：

1、from 左表
2、on 连接条件
3、(left|inner|right) join 右表
4、where 筛选条件
5、group by 分组字段
6、聚合函数
7、having 分组条件
8、select
9、distinct 查询字段
10、order by 排序字段
11、limit 分页

2、MySQL 事务特性：ACID

原子性（Atomicity）：
一个事务内的操作统一成功或失败

一致性（Consistency）：
事务前后的数据总量不变。比如 A 向 B 转账，不可能 A 扣了钱，B 却没收到

隔离性（Isolution）：
事务与事务之间相互不影响

持久性（Durability）：
事务一旦提交发生的改变不可逆

3、事务靠什么保证

原子性：
由 undolog 日志保证，它记录了需要回滚的日志信息，回滚时撤销已执行的 SQL

一致性：
由其他三大特性共同保证，是事务的目的

隔离性：
由锁机制和 MVCC 保证

持久性：
由 redolog 日志和内存保证，MySQL 修改数据时内存和 redolog 会记录操作，宕机时可恢复

4、事务的隔离级别

① Read uncommitted，读未提交：
一个事务可以读取另一个未提交事务的数据。可能出现脏读、不可重复读、幻读问题

② Read committed，读提交：
一个事务要等另一个事务提交后才能读取数据。可以出现不可重复读、幻读问题

③ Repeatable read，可重复读：
在开始读取数据（事务开启）时，不再允许修改操作。可能出现幻读问题

④ Serializable，串行化：
最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用

脏读：
读到了脏数据，即无效数据。如：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据

不可重复读：
是指在数据库访问中，一个事务内的多次相同查询却返回了不同数据，比如修改了行记录

幻读：
指同一个事务内多次查询返回的结果集不一样，比如新增了行记录。不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除

拓展：
大多数数据库默认的事务隔离级别是 Read committed，比如 SqlServer , Oracle。MySQL 的默认隔离级别是 Repeatable read

5、聚簇索引和非聚簇索引的区别

聚簇索引：
B+树 的数据结构，将数据与索引一起存储，索引结构的叶子节点保存了行数据。物理有序

非聚簇索引：
B+树 的数据结构，将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。物理无序

聚簇索引优点：

1. 通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高
2. 聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
3. 聚簇索引适合用在排序的场合，非聚簇索引不适合

聚簇索引缺点：
如果用 uuid 作为主键，数据存储会很稀疏；修改主键或乱序插入会让数据行移动导致页分裂；所以一般定义主键时尽量让主键值小，并且定义为自增和不可修改

6、InnoDB 和 MyISAM 的区别

1. InnoDB 支持事务；MyISAM 不支持
2. InnoDB 支持外键；MyISAM 不支持
3. InnoDB 支持行(默认)级锁；MyISAM 不支持行级锁
4. InnoDB 是聚集索引；MyISAM 是非聚集索引
5. InnoDB 存储文件有 frm、ibd，frm 是表定义文件，ibd 是数据文件；Myisam 是 frm、MYD、MYI，frm 是表定义文件，myd 是数据文件，myi 是索引文件
6. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描；MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何 WHERE 条件）
7. InnoDB 不支持全文索引；MyISAM 支持全文索引；PS：5.7以后的 InnoDB 支持全文索引了
8. InnoDB 表必须有唯一索引（如主键）（用户没有指定的话会自己找一个隐藏列 Row_id 来充当默认主键）；Myisam 可以没有

选择：

1. 是否需要支持事务：需要，选择 InnoDB；不需要，可以考虑 MyISAM
2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM；如果既有读也有写，请使用 InnoDB

7、什么是快照读和当前读

快照读：
读取的是当前数据的可见版本，可能是会过期数据，不加锁的 select 就是快照读

当前读：
读取的是数据的最新版本，并且当前读返回的记录都会上锁，保证其他事务不会并发修改这条记录。如 update、insert、delete、select …… for undate（排他锁）、select …… lockin share mode（共享锁） 都是当前读

8、MVCC 是什么

MVCC是 Multi-Version Concurrent Contrl，多版本并发控制，为每次事务生成一个新版本数据，每个事务都有自己的版本，从而不加锁就杜绝读写冲突，这种读叫做快照读。只在读已提交和可重复读中生效

9、MySQL 有哪些索引

主键索引：
一张表只能有一个主键索引，主键索引列不能有空值和重复值

唯一索引：
唯一索引不能有相同值，但允许为空

普通索引：
允许出现重复值

组合索引：
对多个字段建立一个联合索引，减少索引开销，遵循最左匹配原则

全文索引：
MyISAM引擎支持，广泛用于搜索引擎

10、MySQL 如何做慢 SQL 优化

开启慢查询日志：
超过阈值的 SQL 记录到慢查询日志中：set global long_query_time=阈值;
启用慢查询日志：set global slow_query_log=‘ON’;

通过 explain 关键字进行分析：
几个重要的属性：

1、possible_keys：可能使用到的索引
2、key：实际上使用到的索引
3、type：查询使用的类型
4、rows：查询到需要的数据，扫描了多少行

对于 type，从坏到好 all < index < range < ref < eq_ref < const < system

一般保证达到 range，ref 最好（system 表中只有一行数据；const 表示只匹配一行数据，通过索引一次就找到了；eq_ref 针对主键或唯一键查询结果唯一；ref 针对非唯一键查询多个；range 索引范围查询；index 全索引扫描；all 全表扫描)

优化：

1. 分析 SQL 语句，避免使用 select *，是否加载了不需要的数据列
2. 分析 SQL 执行计划，字段有没有索引，索引是否失效，是否用对索引
3. 使用复杂查询时，尽量使用关联查询来代替子查询，并且最好使用内连接
4. where 语句中需要使用 or 的情况下，需 or 相连的所有字段都有索引才行
5. 避免在 where 子句中对字段进行 null 值判断。尽量使用 not null，或使用特殊值，如 0、-1
6. 避免在 where 子句中使用 != 或 <> 操作符， MySQL 只有对以下操作符才使用索引：<、<=、=、>、>=、between、in
7. 能用 union all 就不用 union，union 过滤重复数据要耗费更多的 CPU 资源
8. 避免部分 like 查询，如 ‘%ConstXiong%’
9. 避免在索引列上使用计算、函数
10. in 和 not in 慎用，能用 between 不要用 in
11. 表中数据是否太大，是不是要分库分表
12. 表的结构是否合理(范式、反范式需要结合实际场景来决定)

11、为什么要用内连接而不用外连接

用外连接的话连接顺序是固定死的，比如 left join，它必须先对左表进行全表扫描，然后一条条到右表去匹配；而内连接的话 MySQL 会自己根据查询优化器去判断用哪个表做驱动

12、MySQL整个查询的过程

1. 客户端向 MySQL 服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则返回存储在缓存中的结果。否则进入下一阶。PS：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了
3. 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
4. MySQL 根据执行计划，调用存储引擎的 API 来执行查询
5. 将结果返回给客户端，同时缓存查询结果

13、执行计划中有哪些字段

看一个 SQL 的执行计划使用的语句是 explain SQL 语句

id：
select 查询的优先级，id 越大优先级越高，子查询的 id 一般会更大

select_type：
查询的类型，是普通查询还是联合查询还是子查询，常见类型有 simple（不包含子查询），primary（标记复杂查询中最外层的查询）等等

table：
查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表

type：
查询使用了何种类型，它在 SQL 优化中是一个非常重要的指标，效率从底到高为
all（全表扫描) > index(全索引扫描，我们需要的数据在索引中可以获取) > range(范围索引扫描) > ref(使用非唯一索引列进行了关联查询) > eq_ref (使用唯一索引进行关联查询) > const(表示只匹配一行数据，通过索引一次就找到了) > system(表中只有一行数据)

possible_keys（可能的）：
当前查询语句可能用到的索引，可能为 null(如果用了索引但是为null有可能是表数据太少 InnoDB 认为全表扫描更快)

key：
实际使用到的索引

rows：
以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数

Extra ：
不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示

14、哪些情况索引会失效

1. where 条件中有 or，除非所有查询条件都有索引，否则失效
2. like 查询用 % 开头，索引失效
3. 对索引字段进行计算操作、字段上使用函数，索引失效
4. 违背最左匹配原则，索引失效
5. 在索引字段上使用 not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0
6. 索引字段发生类型转换，索引失效，如字符串不加双引号
7. MySQL 觉得全表扫描更快时（数据少），索引失效

15、MySQL有哪些锁

按属性分：

1. 共享锁(share lock)：共享锁又称读锁，简称 S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题
2. 排他锁(exclusive lock)：排他锁又称写锁，简称 X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取，避免了出现脏数据和脏读的问题

按粒度分：

1. 表锁(table lock)：表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问。特点：粒度大，加锁简单，容易冲突;
2. 行锁：行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问。特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高
3. 页锁：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般
4. 记录锁(Record lock)：记录锁属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是，事务在加锁后锁住的只是表的某一条记录，加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题
5. 间隙锁：属于行锁的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在 REPEATABLE_READ(重复读) 的事务级别中
6. 临键锁(Next-Key lock)：属于行锁的一种，并且它是 InnoDB 的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住

悲观锁和乐观锁：

1. 悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁
2. 乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量

16、MySQL 内连接、左连接、右连接的区别

内连接：
结合两张表的记录，返回的是两个表的交集部分。关键字：INNER JOIN

左连接：
左连接查询，左表的信息全部展示出来，右表只会展示符合搜索条件的信息，不足的地方记为 NULL。关键字：LEFT JOIN

右连接：
右连接查询，右表的信息全部展示出来，左表只会展示符合搜索条件的信息，不足的地方记为 NULL。关键字：RIGHT JOIN

17、如何设计数据库

1. 抽取实体，如用户信息，商品信息，评论
2. 分析其中属性，如用户信息：姓名、性别…
3. 分析表与表之间的关联关系

参考三大范式进行设计，设计主键时，主键要尽量小并且定义为自增和不可修改

18、where 和 having 的区别

1. where 是约束声明；having 是过滤声明
2. where 早于 having 执行
3. where 不可以使用聚合函数；having 可以使用聚合函数

19、三大范式

第一范式：
属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式：
在第一范式的基础上，且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

第三范式：
在第二范式的基础上，且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

20、char 和 varchar 的区别

1. 最大长度：char 最大长度是255字符；varchar 最大长度是 65535(2^¹⁶) 个字节
2. 定长：char 是定长的，不足的部分用隐藏空格填充；varchar 是不定长的
3. 空间使用：char 会浪费空间；varchar 会更加节省空间
4. 效率：char 效率更高；varchar 效率低
5. 尾部空格：char 插入时可省略；varchar 插入时不会省略，查找时省略
6. 存储方式：char 一个英文字符（ASCII）占用1个字节，一个汉字占用两个字节；varchar 一个英文字符占用2个字节，一个汉字也占用2个字节

21、InnoDB 什么情况下会产生死锁

事务1已经获取 数据A 的写锁，想要去获取 数据B 的写锁，然后事务2获取了 B 的写锁，想要去获取 A 的写锁，相互等待形成死锁

MySQL 解决死锁的机制：

1. 等待，直到超时
2. 发起死锁检测，主动回滚一条事务，死锁检测的原理是构建一个以事务为顶点、 锁为边的有向图， 判断有向图是否存在环， 存在即有死锁

22、MySQL 删除自增 id，随后重启 MySQL 服务，再插入数据，自增 id 会从几开始

InnoDB 引擎：

• MySQL8.0 前：下次自增会取表中最大 id + 1。原理是最大 id 会记录在内存中，重启之后会重新读取表中最大的 id
• MySQL8.0 后：从删除数据 id 后算起。原理是它将最大 id 记录在 redolog 里

MyISAM 引擎：
自增的 id 从删除数据 id 后算起。原理是它将最大 id 记录到数据文件里

23、关系数据库中连接池的机制是什么

前提：
为数据库连接建立一个缓冲池

使用机制：

1. 从连接池获取或创建可用连接
2. 使用完毕之后，把连接返回给连接池
3. 在系统关闭前，断开所有连接并释放连接占用的系统资源
4. 能够处理无效连接，限制连接池中的连接总数不低于或者不超过某个限定值

详解：

• 数据库连接池负责分配、管理并释放数据库连接，它允许应用程序重复使用一个现有的数据库连接
• 最小连接数是连接池一直保持的数据连接。如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费掉
• 最大连接数是连接池能申请的最大连接数。如果数据连接请求超过此数，后面的数据连接请求将被加入到等待队列中，这会影响之后的数据库操作
• 如果最小连接数与最大连接数相差太大，那么，最先的连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放

24、建表时注意什么

• 注意选择存储引擎，如果要支持事务需要选择 lnnoDB，仅用于查询可以使用 MyISAM 提高性能
• 注意字段类型的选择
  • 日期类型：如果要记录时分秒建议使用 datetime，只记录年月日使用 date 类型
  • 字符类型：固定长度字段选择 char，不固定长度的字段选择 varchar，varchar 比 char 节省空间但速度没有 char 快
  • 长文本字段：内容介绍的长文本字段使用 text 或 longtext 类型，如果存储图片等二进制数据使用 blob 或 longblob 类型
  • 金额字段：使用 decimal
  • 数值类型：在确保取值范围足够的前提下尽量使用占用空间较小的类型
• 主键字段建议使用自然主键，不要有业务意义，建议使用 int unsigned 类型，特殊场景使用 bigint 类型
• 如果要存储 text、blob 字段建议单独建一张表，使用外键关联，因为一般大文本都是点击详情时才去查询
• 尽量不要定义外键，保证表的独立性，可以存在外键意义的字段
• 设置字段默认值，比如：状态、创建时间等
• 每个字段写清楚注释
• 注意字段的约束，比如：非空、唯一、主键等