Java八股文——MySQL篇

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. 读未提交

• 事务可以读到其他事务未提交的数据。
• 最弱隔离，可能出现脏读、不重复读、幻读。

2. 读已提交

• 只能读到已提交的数据，避免脏读。
• 但事务期间数据可能被别的事务修改，出现不可重复读和幻读。
• Oracle 默认隔离级别。

3. 可重复读（MySQL 默认）

• 同一事务多次读取，结果始终一致（通过MVCC实现）。
• 解决了不可重复读。
• 幻读仍可能出现（但InnoDB通过间隙锁在某些场景下可防止幻读）。

4. 串行化

• 所有操作都串行执行，强制加锁。
• 最安全，但并发性能最低。

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

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