Java八股文——MySQL篇

已于 2025-06-10 09:16:55 修改
阅读量753 收藏 16
点赞数 10
分类专栏： Java八股文 文章标签： java mysql adb 数据库 八股文 索引 事务
于 2025-06-07 10:39:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44371842/article/details/148492099
版权
#王者杯·14天创作挑战营·第2期#

文章目录

Java八股文专栏其它文章

Java八股文——MySQL篇

慢查询

如何定位慢查询？

方案一：开源工具

  • 调试工具：Arthas
  • 运维工具：Prometheus、Skywalking

方案二：MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

#开启MySQL慢日志查询
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

如何分析慢SQL

  • 聚合查询（可以添加临时表解决）
  • 多表查询（可以试着优化SQL语句结构）
  • 表数据量过大查询
  • 深度分页查询
Explain
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

  • possibel_key ：当前sql可能会使用到的索引

  • key ：当前sql实际命中的索引

  • key_len ：索引占用的大小

  • Extra ：额外的优化建议

    • Using where; Using Index：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
    • Using index condition：查找使用了索引，但是需要回表查询数据（有优化空间）

  • type：这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、range、index、all

    NULL：查询没有用到表

    system：查询系统中的表

    const：根据主键查询

    eq_ref：主键索引查询或唯一索引查询

    ref：索引查询

    range：范围查询

    index：索引树扫描

    all：全盘扫描

标准答案

如果一条sql执行很慢的话，我们通常会使用sql自动的执行计划explain去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了可以尝试添加索引或修改返回字段来修复。

索引

索引（index）是帮助MySQL高校获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+）树，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引类型

索引类型是否唯一是否允许为 NULL是否支持多列典型用途特点总结
主键索引✅❌✅唯一标识一条记录表只能有一个主键，自动创建索引
唯一索引✅✅（仅一条 NULL）✅限制字段值不能重复可有多个，允许一个 NULL 值
普通索引❌✅✅加速查询（如 WHERE、ORDER BY）最常见，不强制唯一
联合索引可选✅✅多字段组合查询遵循最左前缀原则
全文索引❌✅✅（InnoDB 5.6+）文本内容检索（英文效果好）仅支持 MATCH ... AGAINST 查询
空间索引❌❌（必须非空）✅地理空间数据（GIS）仅支持 GEOMETRY 类型字段
前缀索引❌✅✅长字符串字段索引优化需指定前缀长度，节省索引空间

索引底层数据结构

MySQL默认使用的索引底层数据结构是B+树。

  • 阶数更多，路径更短
  • 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
  • B+树便于扫库和区间查询，叶子节点是一个双向链表
特性B树B+树
数据存储位置内部节点和叶子节点仅叶子节点
叶子节点连接无有
查询路径不一定到叶子节点必定到叶子节点
范围查询效率较低高
节点扇出较小较大
应用场景内存数据结构外部存储（数据库、文件系统）

什么是聚簇索引什么是非聚簇索引？（二级索引）（回表）

分类含义特点
聚簇索引（Clustered Index）将数据存储与索引放到了一块，索引结构的叶子节点保存了整行数据必须有，而且只有一个
二级索引（Secondary Index）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个
聚集索引选取规则
  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
回表查询
  • 在使用非聚簇索引查询时，先通过索引定位到主键值，再通过主键去聚簇索引中查找真实数据行的过程。

什么是覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

举例

#id为主键，默认是主键索引
#name字段为普通索引
select * from user where id = 1;   #是覆盖索引，因为主键索引是聚簇索引，包括了一整行数据。不需要回表查询。
select id, name from user where name = 'Arm';    #是覆盖索引，因为条件使用了索引，并且要查询的字段id和name都使用了索引，不需要回表查询。
select id, name, gender from user where name = 'Arm';	#不是覆盖索引，因为返回的列中gender没有索引，所以需要回表查询。

MySQL超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率较低。

覆盖索引+子查询

  • 只查主键再回表，减少排序数据量

    SELECT * FROM table 
WHERE id IN (
  SELECT id FROM table ORDER BY id LIMIT 100000, 10
);

select * from
sku t, (select id from sku order by id limit 100000,10) a 
where t.id = a.id;

SELECT t.* FROM (
  SELECT id FROM table ORDER BY id LIMIT 100000, 10
) AS a
JOIN table t ON t.id = a.id;
使用索引字段+条件过滤

  • 通过记录上次的最大id来“跳页”

    -- 第一次查询
SELECT * FROM table WHERE id > 0 ORDER BY id LIMIT 10;

-- 下一页（记录上一页最大 id 为 120）
SELECT * FROM table WHERE id > 120 ORDER BY id LIMIT 10;
存储游标或位置值
  • 如果是固定翻页结构（如前端分页），可以在后端缓存当前页最大ID或游标，前端传回续查。

索引创建原则有哪些

  1. 针对与数据量较大，且查询比较频繁的表建立索引。 （单表超过10万数据）
  2. 针对常作为查询条件、排序、分组操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

什么情况下索引会失效

1. 违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引。

正例：

-- seller表有联合索引：name 1、status 2、address 3
select * from seller where name = '小米';  #✅
select * from seller where name = '小米' and status = '1';  #✅
select * from seller where name = '小米' and status = '1' and address = '北京市';  #✅

反例：

-- seller表有联合索引：name 1、status 2、address 3
select * from seller where status = '1' and address = '北京市';  #❌
select * from seller where status = '1';  #❌
select * from seller where name = '小米' and address = '北京市';  #❌
2. 范围查询右边的列，不能使用索引

当遇到第一个范围查询时，联合索引后面的字段不再参与索引过滤，只能回表做判断。

SELECT * FROM seller WHERE name = '小米' AND status = '1' AND addres = '北京市'; #可以完全索引✅
SELECT * FROM seller WHERE name = '小米' AND status > '1' AND addres = '北京市'; #在遇到第一个大于条件后，后面的字段（address）无法再参与索引❌
3. 不能在索引列上进行运算操作，索引将失效
select * from seller where substring(name, 3, 2) = '科技'; #❌
4. 字符串不加单引号，造成索引失效。

索引字段是字符串，传入数字会触发隐式转换，索引失效。索引字段上发生任何的类型转换都会导致索引失效。

select * from seller where status = '0'; #✅
select * from seller where status = 0; #❌
5. LIKE 开头是通配符
WHERE name LIKE '%Tom%'  -- 前面有通配符，索引失效 ❌
WHERE name LIKE 'Tom%'   -- 后面通配符，索引生效 ✅

SQL优化的经验

表的设计优化
  • 设计表的时候参考了阿里开发手册《嵩山版》
  • 比如设置合适的数值，要根据实际情况选择
  • 比如设置合适的字符串类型，char定长效率高，varchar可变长度，效率稍低。长度尽量设置在2^n - 1；
SQL语句优化

  • SELECT语句务必指明字段名称（避免直接使用select *）

  • SQL语句要避免造成索引失效的写法

  • 尽量使用union all代理union，union会多一次过滤，效率低

    select * fromn user where id > 2 union all select * from user where id < 5; #Union all会直接将两个查出来的进行拼接，不会过滤其中重复的
select * from user where id > 2 union select * from user where id < 5; # Union会在拼接之后，帮你过滤掉其中重复的字段

  • 避免在where子句中对字段进行表达式操作

  • Join优化 能用inner join就不用left join + right join，如必须使用，一定要以小表为驱动。

    内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或right join，不会调整顺序。

    SELECT * FROM 小表 LEFT JOIN 大表 ON 条件;
SELECT * FROM 大表 RIGHT JOIN 小表 ON 条件;
    JOIN 类型保留哪一侧全部数据匹配不到的行是否显示没匹配的列填什么
    INNER JOIN两表都要匹配成功否不显示
    LEFT JOIN左表全部是NULL
    RIGHT JOIN右表全部是NULL
索引优化
主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。

读写分离解决的是，数据库的写入，影响了查询的效率。

事务

事务特性

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

ACID
  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务带来哪些问题

1. 脏读（Dirty Read）

读取到了其他事务未提交的数据。

示例：

  • 事务A：修改账户余额为500，但未提交。
  • 事务B：读取到余额是500（其实还没真正生效）。
  • 后来事务A回滚了，数据其实还是原来的1000，
    → B读到的是“脏”的数据。
2. 不可重复读（Non-repeatable Read）

同一事务内，两次读取同一数据，值不一致。

示例：

  • 事务A：第一次查询账户余额是1000
  • 事务B：修改余额为500并提交
  • 事务A：再次查询余额变成500
    → A两次读取结果不一致，称为不可重复读
3. 幻读（Phantom Read）

同一事务内，两次查询数据集合，结果集的“行数”不同（有新行出现或消失）。

示例：

  • 事务A：查询“所有工资 > 5000 的员工”返回10条
  • 事务B：插入一条工资为6000的新员工并提交
  • 事务A：再次查询，变成11条
    → 多了一条“幻影”数据，称为幻读

四种隔离级别

隔离级别是否会脏读不可重复读幻读并发性能MySQL 默认
读未提交（Read Uncommitted）✅ 会✅ 会✅ 会高❌
读已提交（Read Committed）❌ 不会✅ 会✅ 会较高❌（Oracle 默认）
可重复读（Repeatable Read）❌ 不会❌ 不会✅ 会中✅（MySQL 默认）
串行化（Serializable）❌ 不会❌ 不会❌ 不会低（加锁最多）❌
  1. 读未提交
  • 事务可以读到其他事务未提交的数据。
  • 最弱隔离，可能出现脏读、不重复读、幻读。
  1. 读已提交
  • 只能读到已提交的数据，避免脏读。
  • 但事务期间数据可能被别的事务修改，出现不可重复读和幻读。
  • Oracle 默认隔离级别。
  1. 可重复读（MySQL 默认）
  • 同一事务多次读取，结果始终一致（通过MVCC实现）。
  • 解决了不可重复读。
  • 幻读仍可能出现（但InnoDB通过间隙锁在某些场景下可防止幻读）。
  1. 串行化
  • 所有操作都串行执行，强制加锁。
  • 最安全，但并发性能最低。

undo log和redo log的区别（事务的实现方式）

  • 缓冲池（buffer pool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
  • 数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页中存储的是行数据。
undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）。undo log和redo log记录物理日志不一样，它是逻辑日志

  • 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然

  • 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

  • 作用：记录数据被修改前的旧值，用于：

    事务回滚

    MVCC（多版本并发控制，支持快照读）

  • 触发场景：INSERT、UPDATE、DELETE 时生成 undo log。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

  • 作用：记录数据修改后的新值，用于：

    事务提交后保证数据持久化（即使崩溃也能恢复）。

  • 触发场景：

    事务提交前，先把 redo log 写到磁盘（WAL：Write-Ahead Logging）。

项目undo logredo log
作用回滚、MVCC快照读崩溃恢复、保证持久化
记录内容修改前的数据修改后的数据
使用时机事务回滚、快照读事务提交、宕机恢复
物理位置InnoDB表空间（undo段）InnoDB redo log 文件
典型用途回滚、快照一致性数据库宕机时恢复

事务的隔离性如何保证（MVCC）

排他锁

如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁。

MVCC：多版本并发控制（Multi-Version Concurrency Control）。指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC-实现原理

  • 记录中的隐藏字段

    隐藏字段含义
    DB_TRX_ID最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
    DB_ROLL_PTR回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
    DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

  • undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

undo log版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

  • readview

ReadVie（读视图）是快照SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

  • 当前读

    读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如select ... log in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。

  • 快照读

    简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

    • 读已提交（Read Committed）：每次select，都生成一个ReadView
    • 可重复读（Repeatable Read）：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

ReadView中包含了四个核心字段

字段含义
m_ids当前活跃的事务ID集合
min_trx_id最小活跃事务ID
max_trx_id预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_idReadView创建者的事务ID

版本链数据访问规则：

trx_id：代表是当前事务ID

  1. trx_id == creator_trx_id？可以访问该版本✅ → 成立，说明数据是当前这个事务更改的。
  2. trx_id < min_trx_id？可以访问该版本✅ → 成立，说明数据已经提交了。
  3. trx_id > max_trx_id？不可以访问该版本❌ → 成立，说明该事务是在ReadView生成后才开启。
  4. min_trx_id <= trx_id <= max_trx_id？如果trx_id不在m_ids中是可以访问该版本的✅ → 成立，说明数据已经提交。

总结：只看在当前 ReadView 生成前已经提交的版本，未提交的版本看不到。

主从同步

原理

MySQL主从复制的核心就是二进制日志：

​ 二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

  1. Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
  2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。
  3. slave重做中继日志中的事件，将改变反映它自己的数据。

主从同步和双机热备区别

对比点主从同步双机热备
目的提升读性能 / 备份高可用、故障自动切换
数据同步方向单向（主 → 从）通常双向或实时同步
故障切换需手动或自动提升从为主快速切换，秒级高可用
负载分担主写、从读（读写分离）一般主负责，备不参与业务
常见场景读多写少系统强要求高可用、不能停机系统
主从同步（主库 → 从库）

特点：

  • 主库处理 写请求，从库主要处理 读请求。
  • 数据同步是 单向的（主 → 从）。
  • 一般用于 读写分离、提升读性能、备份。
  • 可能存在 同步延迟。
双机热备（HA，高可用）

特点：

  • 两台机器（主 + 备）运行同一套服务，互为备份。
  • 备机通常处于 热备状态，实时或准实时同步数据。
  • 主机故障后，快速切换到备机，保证业务连续。
  • 目标是 高可用（HA），不是分担读写负载。

分库分表

分库分表的时机：

  1. 前提，项目业务数据逐渐增多，或业务发展比较迅速。单表的数据量达到1000W或20G以后

  2. 优化已解决不了性能问题（主从读写分离、查询索引）

  3. IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

拆分策略

垂直拆分

  • 垂直分库：以表为依据，根据业务将不同的表拆分到不同库中。

    特点：

    1. 按业务对数据分级管理、维护、监控、扩展
    2. 在高并发下，提高磁盘IO和数据量连接数

  • 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

    拆分规则：

    • 把不常用的字段单独放在一张表
    • 把text，blob等大字段拆分出来放在附表中

    特点：

    • 冷热数据分离
    • 减少IO过度争抢，两表互不影响
水平拆分

  • 水平分库：将一个库的数据拆分到多个库中。

    路由规则：

    • 根据id节点取模
    • 按用户 ID、地区、业务线、时间分。
    • 按id进行范围路由，节点1（1-100万），节点2（100万-200万）
    • …

    特点：

    • 解决了单库大数量，高并发的性能瓶颈问题
    • 提高了系统的稳定性和可用性

  • 水平分表：将一个表的数据拆分到多个表中（可以在同一个库内）。

    路由规则同水平分库

    特点：

    • 优化单一表数据量过大而产生的性能问题。
    • 避免IO争抢并减少锁表的几率。

新的问题和新的技术

  • 分布式事务一致性问题
  • 跨节点关联查询
  • 跨节点分页、排序函数
  • 主键避重
分库分表中间件：
  • sharding-sphere
  • mycat
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值