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、allNULL：查询没有用到表
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或游标，前端传回续查。
索引创建原则有哪些
- 针对与数据量较大，且查询比较频繁的表建立索引。 （单表超过10万数据）
- 针对常作为查询条件、排序、分组操作的字段建立索引。
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储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） | ❌ 不会 | ❌ 不会 | ❌ 不会 | 低（加锁最多） | ❌ |
- 读未提交
- 事务可以读到其他事务未提交的数据。
- 最弱隔离，可能出现脏读、不重复读、幻读。
- 读已提交
- 只能读到已提交的数据，避免脏读。
- 但事务期间数据可能被别的事务修改，出现不可重复读和幻读。
- Oracle 默认隔离级别。
- 可重复读（MySQL 默认）
- 同一事务多次读取，结果始终一致（通过MVCC实现）。
- 解决了不可重复读。
- 幻读仍可能出现（但InnoDB通过间隙锁在某些场景下可防止幻读）。
- 串行化
- 所有操作都串行执行，强制加锁。
- 最安全，但并发性能最低。
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 log | redo 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_id | ReadView创建者的事务ID |
版本链数据访问规则：
trx_id：代表是当前事务ID
- trx_id == creator_trx_id？可以访问该版本✅ → 成立，说明数据是当前这个事务更改的。
- trx_id < min_trx_id？可以访问该版本✅ → 成立，说明数据已经提交了。
- trx_id > max_trx_id？不可以访问该版本❌ → 成立，说明该事务是在ReadView生成后才开启。
- min_trx_id <= trx_id <= max_trx_id？如果trx_id不在m_ids中是可以访问该版本的✅ → 成立，说明数据已经提交。
总结：只看在当前 ReadView 生成前已经提交的版本，未提交的版本看不到。
主从同步
原理
MySQL主从复制的核心就是二进制日志：
二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。
- Master主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
- 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log。
- slave重做中继日志中的事件，将改变反映它自己的数据。
主从同步和双机热备区别
| 对比点 | 主从同步 | 双机热备 |
|---|---|---|
| 目的 | 提升读性能 / 备份 | 高可用、故障自动切换 |
| 数据同步方向 | 单向（主 → 从） | 通常双向或实时同步 |
| 故障切换 | 需手动或自动提升从为主 | 快速切换，秒级高可用 |
| 负载分担 | 主写、从读（读写分离） | 一般主负责，备不参与业务 |
| 常见场景 | 读多写少系统 | 强要求高可用、不能停机系统 |
主从同步（主库 → 从库）
特点：
- 主库处理 写请求，从库主要处理 读请求。
- 数据同步是 单向的（主 → 从）。
- 一般用于 读写分离、提升读性能、备份。
- 可能存在 同步延迟。
双机热备（HA，高可用）
特点：
- 两台机器（主 + 备）运行同一套服务，互为备份。
- 备机通常处于 热备状态，实时或准实时同步数据。
- 主机故障后，快速切换到备机，保证业务连续。
- 目标是 高可用（HA），不是分担读写负载。
分库分表
分库分表的时机：
前提，项目业务数据逐渐增多，或业务发展比较迅速。单表的数据量达到1000W或20G以后
优化已解决不了性能问题（主从读写分离、查询索引）
IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）
拆分策略
垂直拆分
-
垂直分库：以表为依据，根据业务将不同的表拆分到不同库中。
特点：
- 按业务对数据分级管理、维护、监控、扩展
- 在高并发下，提高磁盘IO和数据量连接数
-
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。
拆分规则：
- 把不常用的字段单独放在一张表
- 把text，blob等大字段拆分出来放在附表中
特点：
- 冷热数据分离
- 减少IO过度争抢，两表互不影响
水平拆分
-
水平分库：将一个库的数据拆分到多个库中。
路由规则：
- 根据id节点取模
- 按用户 ID、地区、业务线、时间分。
- 按id进行范围路由，节点1（1-100万），节点2（100万-200万）
- …
特点：
- 解决了单库大数量，高并发的性能瓶颈问题
- 提高了系统的稳定性和可用性
-
水平分表：将一个表的数据拆分到多个表中（可以在同一个库内）。
路由规则同水平分库
特点：
- 优化单一表数据量过大而产生的性能问题。
- 避免IO争抢并减少锁表的几率。
新的问题和新的技术
- 分布式事务一致性问题
- 跨节点关联查询
- 跨节点分页、排序函数
- 主键避重
分库分表中间件：
- sharding-sphere
- mycat
扫一扫
597
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
