来自大厂面试官的MySQL灵魂十连问，你真的有把握全部回答出来吗？

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

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

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

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

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

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

正文

读已提交(Oracle默认)：别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读(MySQL默认)：别人改数据的事务已经提交，我在我的事务中也不去读，以此保证重复读一致性。

串行：我的事务尚未提交，别人就别想改数据。

标准跟实现：上面都是关于事务的标准，但是每一种数据库都有不同的实现，比如MySQL InnDB 默认为RR级别，但是不会出现幻读。因为当事务A更新了所有记录的某个字段，此时事务A会获得对这个表的表锁，因为事务A还没有提交，所以事务A获得的锁没有释放，此时事务B在该表插入新记录，会因为无法获得该表的锁，则导致插入操作被阻塞。只有事务A提交了事务后，释放了锁，事务B才能进行接下去的操作。所以可以说  MySQL的RR级别的隔离是已经实现解决了脏读，不可重复读和幻读的。

五、MySQL中的锁

无论是Java的并发编程还是数据库的并发操作都会涉及到锁，研发人员引入了悲观锁跟乐观锁这样一种锁的设计思想。

悲观锁：

优点：适合在写多读少的并发环境中使用，虽然无法维持非常高的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性

缺点：加锁会增加系统开销，虽然能保证数据的安全，但数据处理吞吐量低，不适合在读书写少的场合下使用

乐观锁：

优点：在读多写少的并发场景下，可以避免数据库加锁的开销，提高DAO层的响应性能，很多情况下ORM工具都有带有乐观锁的实现，所以这些方法不一定需要我们人为的去实现。

缺点：在写多读少的并发场景下，即在写操作竞争激烈的情况下，会导致CAS多次重试，冲突频率过高，导致开销比悲观锁更高。

实现：数据库层面的乐观锁其实跟CAS思想类似， 通数据版本号或者时间戳也可以实现。

数据库并发场景主要有三种：

读-读：不存在任何问题，也不需要并发控制

读-写：有隔离性问题，可能遇到脏读，幻读，不可重复读

写-写：可能存更新丢失问题，比如第一类更新丢失，第二类更新丢失

两类更新丢失问题：

第一类更新丢失：事务A的事务回滚覆盖了事务B已提交的结果 第二类更新丢失：事务A的提交覆盖了事务B已提交的结果

为了合理贯彻落实锁的思想，MySQL中引入了杂七杂八的各种锁：

锁分类

MySQL支持三种层级的锁定，分别为

1.表级锁定

MySQL中锁定粒度最大的一种锁，最常使用的MYISAM与INNODB都支持表级锁定。

2.页级锁定

是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

3.行级锁定

Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

MyISAM中的锁

1. 虽然MySQL支持表，页，行三级锁定，但MyISAM存储引擎只支持表锁。所以MyISAM的加锁相对比较开销低，但数据操作的并发性能相对就不高。但如果写操作都是尾插入，那还是可以支持一定程度的读写并发

2. 从MyISAM所支持的锁中也可以看出，MyISAM是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。

InnoDB中的锁

该模式下支持的锁实在是太多了，具体如下：

共享锁和排他锁 (Shared and Exclusive Locks)

意向锁（Intention Locks）

记录锁（Record Locks）

间隙锁（Gap Locks）

临键锁 （Next-Key Locks）

插入意向锁（Insert Intention Locks）

主键自增锁 (AUTO-INC Locks)

空间索引断言锁（Predicate Locks for Spatial Indexes）

举个栗子，比如行锁里的共享锁跟排它锁：lock in share modle 共享读锁：

为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了S 锁。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update排它写锁:

为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到for update。相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

Gap Lock间隙锁：

1、行锁只能锁住行，如果在记录之间的间隙插入数据就无法解决了，因此MySQL引入了间隙锁(Gap Lock)。间隙锁是左右开区间。间隙锁之间不会冲突。

2、间隙锁和行锁合称NextKeyLock，每个NextKeyLock是前开后闭区间。

间隙锁加锁原则(学完忘那种)：

1、加锁的基本单位是 NextKeyLock，是前开后闭区间。

2、查找过程中访问到的对象才会加锁。

3、索引上的等值查询，给唯一索引加锁的时候，NextKeyLock退化为行锁。

4、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock退化为间隙锁。

5、唯一索引上的范围查询会访问到不满足条件的第一个值为止。

六、MVCC

MVCC：

1、全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的理念，维持一个数据的多个版本，使得读写操作没有冲突。

2、MVCC在MySQL InnoDB中实现目的主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MySQL InnoDB下的当前读和快照读

1.当前读

1、像select lock in share mode(共享锁)、select for update 、update、insert、delete(排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

2、当前读可以认为是悲观锁的具体功能实现

2.快照读

1、不加锁的select就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

2、快照读就是MVCC思想在MySQL的具体非阻塞读功能实现，MVCC的目的就是为了实现读-写冲突不加锁，提高并发读写性能，而这个读指的就是快照读。

3、快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。

因为大佬不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出了MVCC，所以我们可以形成两个组合：

MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突

MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

MVCC的实现原理

MVCC实现原理主要是依赖记录中的 四个隐式字段、undo日志 、Consistent Read View来实现的。

四个隐式字段：

1.DB_TRX_ID：

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

2.DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

3.DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

4.FLAG

一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

事务对一条记录的修改，会导致该记录的undo log成为一条记录版本线性表(链表)，undo log的链首就是最新的旧记录，链尾就是最早的旧记录。

undo日志：此知识点上文已经说过了，对MVCC有帮助的实质是update undo log，undo log实际上就是存在rollback segment中旧记录链。

一致读视图 Consistent Read View：Read View是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的)。拿着这个ID跟记录中ID对比进行选择性展示，这里说下大致的思维。

你可以简单的理解为MVCC为每一行增加了两个隐藏字段，两个字段分别保存了这个行的当前事务ID跟行的删除事务ID。

1.insert时：

InnoDB为新插入的每一行保存当前系统版本号作为版本号。

2.select时：

1、 InnoDB只会查找版本早于当前事务版本的数据行(也就是行的系统版本号<=事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2、行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行在事务开始之前未被删除。

3、只有1，2 同时满足的记录，才能返回作为查询结果。

3.delete时：

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

4.update时：

InnoDB执行update，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要update的行的删除时间。

上面只是一个浅显的讲解MVCC选择标准流程，源码层面应该是根据低水位跟高水位来截取的。具体实现可自行百度。

重点：

1、事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力。

2、在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

七、缓冲池（buffer pool）

应用系统分层架构，为了加速数据访问，会把最常访问的数据，放在缓存(cache)里，避免每次都去访问数据库。操作系统，会有缓冲池(buffer pool)机制，避免每次访问磁盘，以加速数据的访问。MySQL作为一个存储系统，同样具有缓冲池(buffer pool)机制，以避免每次查询数据都进行磁盘IO，主要作用：

1、存在的意义是加速查询

2、缓冲池(buffer pool) 是一种常见的降低磁盘访问 的机制；

3、缓冲池通常以页(page 16K)为单位缓存数据；

4、缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

5、InnoDB对普通LRU进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，该页被访问，才进入新生代，以解决预读失效的问题页被访问。且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

预读失效：

由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效

缓冲池污染：

当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。解决办法：加入老生代停留时间窗口策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内仅仅访问了一次的页。

八、table瘦身

空洞：

MySQL执行delete命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是空洞。插入时候引发分裂同样会产生空洞。

重建表思路：

1、新建一个跟A表结构相同的表B

2、按照主键ID将A数据一行行读取同步到表B

3、用表B替换表A实现效果上的瘦身。

重建表指令：

1、alter table A engine=InnoDB，慎重用，牛逼的DBA都用下面的开源工具。

2、推荐Github：gh-ost

九、SQL Joins、统计、 随机查询

7种join具体如下：

统计：

1、MyISAM模式下把一个表的总行数存在了磁盘上，直接拿来用即可

2、InnoDB引擎由于 MVCC的原因，需要把数据读出来然后累计求和

3、性能来说 由坏到好：count(字段) < count(主键id) < count(1) ≈ count(*)，尽量用count(*)即可。

随机查询：

mysql> select word from words order by rand() limit 3;

直接使用order by rand()，explain 这个语句发现需要 Using temporary和 Using filesort，查询的执行代价往往是比较大的。所以在设计的时要避开这种写法。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1;
select * from t limit @Y2,1;
select * from t limit @Y3,1;

这样可以避免临时表跟排序的产生，最终查询行数 = C + (Y1+1) + (Y2+1) + (Y3+1)

exist 和 in 对比：

1、in查询时首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。

2、子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。

3、两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。

4、查询用not in 那么内外表都进行全表扫描，没有用到索引；而not exists 的子查询依然能用到表上的索引。not exists比not in要快。

十、MySQL优化

SQL优化主要分4个方向：SQL语句跟索引、表结构、系统配置、硬件。

总优化思路就是最大化利用索引、尽可能避免全表扫描、减少无效数据的查询：

1、减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。

2、返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。

3、减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。

4、减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用 。

5、分表分区：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。

SQL语句优化大致举例：

1、合理建立覆盖索引：可以有效减少回表。

2、union，or，in都能命中索引，建议使用in

3、负向条件(!=、<>、not in、not exists、not like 等) 索引不会使用索引，建议用in。

4、在列上进行运算或使用函数会使索引失效，从而进行全表扫描

5、小心隐式类型转换，原字符串用整型会触发CAST函数导致索引失效。原int用字符串则会走索引。

6、不建议使用%前缀模糊查询。

7、多表关联查询时，小表在前，大表在后。在 MySQL 中，执行 from 后的表关联查询是从左往右执行的(Oracle 相反)，第一张表会涉及到全表扫描。

8、调整 Where 字句中的连接顺序，MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

SQL调优大致思路：

1、先用慢查询日志定位具体需要优化的sql

2、使用 explain 执行计划查看索引使用情况

3、重点关注(一般情况下根据这4列就能找到索引问题)：

1、key（查看有没有使用索引）

2、key_len（查看索引使用是否充分）

3、type（查看索引类型）

4、Extra（查看附加信息：排序、临时表、where条件为false等）

4、根据上1步找出的索引问题优化sql 5、再回到第2步

表结构优化：

1、尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED 。

2、VARCHAR的长度只分配真正需要的空间 。

3、尽量使用TIMESTAMP而非DATETIME 。

4、单表不要有太多字段，建议在20以内。

5、避免使用NULL字段，很难查询优化且占用额外索引空间。字符串默认为’'。

读写分离：

只在主服务器上写，只在从服务器上读。对应到数据库集群一般都是一主一从、一主多从。业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。一般 读写分离 的实现方式有两种：代码封装跟数据库中间件。

分库分表：分库分表 分为垂直和水平两个方式，一般是先垂直后水平。

1、垂直分库：将应用分为若干模块，比如订单模块、用户模块、商品模块、支付模块等等。其实就是微服务的理念。

2、垂直分表：一般将不常用字段跟数据较大的字段做拆分。

3、水平分表：根据场景选择什么字段作分表字段，比如淘宝日订单1000万，用userId作分表字段，数据查询支持到最近6个月的订单，超过6个月的做归档处理，那么6个月的数据量就是18亿，分1024张表，每个表存200W数据，hash(userId)%100找到对应表格。

4、ID生成器：分布式ID 需要跨库全局唯一方便查询存储-检索数据，确保唯一性跟数字递增性。

目前主要流行的分库分表工具 就是Mycat和sharding-sphere。

TiDB：开源分布式数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。TiDB 具备如下核心特点

1、支持 MySQL 协议（开发接入成本低）。

2、100% 支持事务（数据一致性实现简单、可靠）。

3、无限水平拓展（不必考虑分库分表），不停服务。

4、TiDB 支持和 MySQL 的互备。

5、遵循jdbc原则，学习成本低，强关系型，强一致性，不用担心主从配置，不用考虑分库分表，还可以无缝动态扩展。

适合：

1、原业务的 MySQL 的业务遇到单机容量或者性能瓶颈时，可以考虑使用 TiDB 无缝替换 MySQL。

2、大数据量下，MySQL 复杂查询很慢。

3、大数据量下，数据增长很快，接近单机处理的极限，不想分库分表或者使用数据库中间件等对业务侵入性较大、对业务有约束的 Sharding 方案。

4、大数据量下，有高并发实时写入、实时查询、实时统计分析的需求。5、有分布式事务、多数据中心的数据 100% 强一致性、auto-failover 的高可用的需求。

不适合：

1、单机 MySQL 能满足的场景也用不到 TiDB。

2、数据条数少于 5000w 的场景下通常用不到 TiDB，TiDB 是为大规模的数据场景设计的。

3、如果你的应用数据量小（所有数据千万级别行以下），且没有高可用、强一致性或者多数据中心复制等要求，那么就不适合使用 TiDB。

写在最后

大家看完有什么不懂的可以在下方留言讨论.
谢谢你的观看。
觉得文章对你有帮助的话记得关注我点个赞支持一下！

作者：谷鸡泰
链接：https://juejin.cn/post/6914526244994301965

最后

小编精心为大家准备了一手资料

以上Java高级架构资料、源码、笔记、视频。Dubbo、Redis、设计模式、Netty、zookeeper、Spring cloud、分布式、高并发等架构技术

【附】架构书籍

1. BAT面试的20道高频数据库问题解析
2. Java面试宝典
3. Netty实战
4. 算法

BATJ面试要点及Java架构师进阶资料

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

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

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

【附】架构书籍

1. BAT面试的20道高频数据库问题解析
2. Java面试宝典
3. Netty实战
4. 算法

[外链图片转存中…(img-YAa7zUi8-1713475338868)]

BATJ面试要点及Java架构师进阶资料

[外链图片转存中…(img-hszlqOnd-1713475338869)]

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

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

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