Java面试题超详细整理《MySQL篇》，java面试常问知识

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

| | MyISAM | Innodb |

| — | — | — |

| 是否支持事务 | 不支持事务， 但是每次查询都是原子的 | 支持 ACID 的事务， 支持事务的四种隔离级别 |

| 锁支持 | 支持表级锁定 | 支持行级锁定、表级锁定，锁定力度小并发能力高 |

| 是否支持外键 | 不支持外键 | 支持外键 |

| 存储结构 | 每张表被存放在三个文件：索引文件MYI、数据文件MYD、frm表结构文件 | 所有的表都保存在同一个数据文件中（也可以是多个），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |

| 存储空间 | MyISAM可被压缩，存储空间较小 | InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |

| 文件格式 | 数据和索引是分别存储的，数据.MYD，索引.MYI | 数据和索引是集中存储的，.ibd |

| 可移植性、备份及恢复 | 跨平台的数据转移中会很方，在备份和恢复时可单独针对某个表进行操作 | 可以拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了 |

| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |

| 哈希索引 | 不支持 | 支持 |

| 全文索引 | 支持 | 不支持(但可以使用Sphinx插件) |

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

---

MySQL中字段类型CHAR 和 VARCHAR 的区别？

---

| | char | varchar |

| — | — | — |

| 长度 | 长度固定（1-255） | 长度可变 |

| 长度不足时 | 插入的长度小于定义长度时，则用空格填充，检索 CHAR 值时需删除尾随空格 | 小于定义长度时，按实际插入长度存储 |

| 性能 | 存取速度比varchar快得多 | 存取速度比char慢得多 |

| 使用场景 | 适合存储很短的，固定长度的字符串，如手机号，MD5值等 | 适合用在长度不固定场景，如收货地址，邮箱地址等 |

---

MySQL中字段类型DATETIME 和 TIMESTA的区别？

---

| 类型 | 占据字节 | 范围 | 时区问题 |

| — | — | — | — |

| datetime | 8 字节 | 1000-01-01 00:00:00到 9999-12-31 23:59:59 | 存储与时区无关，不会发生改变 |

| timestamp | 4 字节 | 1970-01-01 00:00:01 | 到 2038-01-19 11:14:07 |

如果一个表有一列定义为 TIMESTAMP ，每当行被更改时，时间戳字段将获取当前时间戳。

---

数据库三大范式

---

• 第一范式： 属性不可再分

• 第二范式： 在一范式的基础上，消除了部份依赖，属性完全依赖于主键

• 第三范式： 在二范式的基础上，消除了传递依赖，属性不依赖于其它非主属性 属性直接依赖于主键

---

数据库中的事务是什么?事务的特性？

---

事务（ transaction） 是一组有序的数据库操作。如果组中的所有操作都成功， 则认为事务成功，提交事务。 如果一个操作失败， 则事务将回滚， 该事务所有操作的影响都将取消。（事务是逻辑上的一组操作，要么都执行，要么都不执行）

事务的特性：ACID

• 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

• 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

• 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

开启一个事务

START TRANSACTION;

多条 SQL 语句

SQL1,SQL2…

提交事务

COMMIT;

---

ACID靠什么来保证？

---

• 原子性：MySQL InnoDB 引擎使用 undo log(回滚日志) 来保证事务的原子性，记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

• 一致性：保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

• 隔离性：MySQL InnoDB 引擎通过锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。

• 持久性：使用 redo log(重做日志) 保证事务的持久性，mysql修改数据的同时在内存和redo log记录这次操作，宕机的时候可以redo log恢复。redo log的刷盘会在系统空闲时进行。

InnoDB redo log写盘，InnoDB事务进入prepare状态。如果前面的prepare成功，binlog写盘，在继续将事务日志持久化到binlog中，如果持久化成功，那么InnoDB事务则进入commit状态（在redo log里面写一个commit记录）

---

什么是MVCC

---

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据,版本链

MVCC只在 READ COMMITTED（读已提交）和 REPEATABLE READ（可重复读）两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容，因为 READ UNCOMMITTED（读未提交）总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE（串行化）则会对所有读取的行都加锁。

聚簇索引记录中有两个必要的隐藏列:

trx_id：用来存储每次对某条聚簇索引记录进行修改的时候的事务id

roll_pointer：每次对哪条聚簇索引记录有修改的时候,都会把老版本写入undo日志中。这个ro‖_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

Mysql的MVCC通过版本链，实现多版本，可并发读写，写读。通过 Readview生成策略的不同实现不同的隔离级别：

• 始事务时创建 readview， readview维护当前活动的事务id，即未提交的事务id，排序生成一个数组。

• 访问数据获取数据中的事务id（获取的是事务id最大的记录），对比 readview如果在 readview的左边（比 readview都小），可以访问（在左边意味着该事务已经提交）

• 如果在 readview的右边（比 readview都大）或者就在 readview中，不可以访问，获取 roll_ pointer，取上一版本重新对比（在右边意味着，该事务在 readview生成之后出现，在 readview中意味着该事务还未提交）

• 已提交读和可重复读的区别就在于它们生成 Readview的策略不同：

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的 Readview，而可重复读隔离级别则在第一次读的候生成一个 Readview，之后的读都复用之前的 Readview

---

mysql主从同步原理

---

MySQL的主从复制中主要有三个线程： master (binlog dump thread)，slave( I/O thread、SQL thread)， Master-条线程和Save中的两条线程。

• 主节点binlog：主从复制的基础是主库记录数据库的所有变更记录到binlog。 binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。

• 主节点 log dump线程，当 binlog有变动时， log dump线程读取其内容并发送给从节点。

• 从节点I/O线程接收 binlog内容，并将其写入到 relay log文件中。

• 从节点的SQL线程读取 relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念：

• 全同步复制：主库写入 binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响

• 半同步复制：和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成

---

并发事务带来哪些问题?

---

• 脏读（Dirty read）：某个事务对数据进行修改时，另外一个事务读取了这个数据。因为这个数据是还没有提交的数据（可能会发生回滚），那么另外一个事务读到的这个数据是“脏数据”。

• 不可重复读（Unrepeatable read）：某个事务内多次读同一数据，数据不一致。可能在该事务多次读取数据期间，某一个事务修改了数据。（修改操作）

• 幻读（Phantom read）： 某个事务内多次读同一种数据，数据行数不一致。可能在该事务多次读取数据期间，某一个事务插入了数据，导致出现了本不该出现的数据。（插入删除操作）

• 丢失修改（Lost to modify）: 某个事务读取一个数据，并对数据进行修改，期间另外一个事务也访问了该数据，并对数据进行修改。导致第一个事务进行修改的的操作没有成功，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

---

四种事务隔离级别

---

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@transaction_isolation;查看

---

什么是索引？索引的基本原理

---

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于目录的作用。其本身是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。会占据一定的物理空间。

索引的优缺点：

• 优点 ：使用索引可以大大加快数据的检索速度（大大减少检索的数据量）

• 缺点 ：创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。另外，索引需要使用物理文件存储，也会耗费一定空间。

索引的原理：把无序的数据变成有序的查询

• 把创建了索引的列的内容进行排序

• 对排序结果生成倒排表

• 在倒排表内容上拼上数据地址链

• 在查询的时候，先拿到倒排表的内容，再取出数据地址链，从而拿到具体数据

使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

---

索引类型

---

• 普通索引(Index) ：用来快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

• 唯一索引(Unique Key) ：可以保证数据记录的唯一性，允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

• 主键索引(Primary Key)：数据表的主键列使用的就是主键索引。是一种特殊的唯一索引，一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。（前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同）

• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，通过建立倒排索引，可以极大提升索引效率，解决判断字段是否包含问题，是目前搜索引擎使用的关键技术。

唯一索引比普通索引快吗，为什么?

对于写多读少的情况， 普通索引利用 change buffer 有效减少了对磁盘的访问次数，而唯一索引需要校验唯一性，此时普通索引性能要高于唯一索引

MySQL 如何为表字段添加索引？

添加 PRIMARY KEY（主键索引）

ALTER TABLE table_name ADD PRIMARY KEY ( column )

添加 UNIQUE(唯一索引)

ALTER TABLE table_name ADD UNIQUE ( column )

添加 INDEX(普通索引)

ALTER TABLE table_name ADD INDEX index_name ( column )

添加 FULLTEXT(全文索引)

ALTER TABLE table_name ADD FULLTEXT ( column)

添加多列索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

select count(*)/count(distinct left(password,prefixLen));

通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)：

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。

二级索引属于非聚簇索引

---

聚集索引与非聚集索引

---

两者都是B+树的数据结构，依赖于有序的数据。

聚集索引：聚集索引即索引结构和数据一起存放，并按一定的顺序进行排序的索引，找到了索引进找到了数据。

主键索引属于聚集索引。

优点：

聚集索引的范围查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点：

• 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

• 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引：非聚集索引即索引结构和数据分开存放的索引。非聚集索引的叶子节点并不存放数据，存储的数据行地址，根据数据行地址再回表查数据。

优点：

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

缺点：

• 跟聚集索引一样，非聚集索引也依赖于有序的数据

• 可能会二次查询(回表) ：这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

MyISAM：采用非聚集索引， 索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致， 但是辅索引不用保证唯一性。

InnoDB：主键索引采用聚集索引（ 索引的数据域存储数据文件本身）， 辅助索引的数据域存储主键的值； 因此从辅助索引查找数据， 需要先通过辅助索引找到主键值， 再访问辅助索引； 最好使用自增主键， 防止插入数据时， 为维持 B+树结构， 文件的大调整。

---

创建索引的一些原则

---

适合索引：频繁查询、范围查询、排序、连接的字段

不适合索引：基数较小、频繁更新、重复值多的、字段为NULL的字段

选择合适的字段创建索引：

• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。

• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。(避免在查询条件中对字段施加函数，这会造成无法命中索引)

• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引：虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

取值离散大的字段的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；比如性别就不适合做索引。

索引不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

考虑在字符串类型的字段上使用前缀索引代替普通索引。前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

尽可能的考虑建立联合索引而不是单列索引：因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引 ：冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

一些建议：

• 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引。

• 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。

• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗

• 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

---

完整性约束包括哪些？

---

约束：保证数据的完整性而实现的摘自一套机制，即(约束是针对表中数据记录的)

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。分为以下四类：

最后

金三银四到了，送上一个小福利！

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

约束：保证数据的完整性而实现的摘自一套机制，即(约束是针对表中数据记录的)

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。分为以下四类：

最后

金三银四到了，送上一个小福利！

[外链图片转存中…(img-WX67yJEH-1713263325438)]

[外链图片转存中…(img-OXCtzB0w-1713263325438)]

[外链图片转存中…(img-W3FEZbQ1-1713263325438)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-LMZEZbYz-1713263325439)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！