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或游标,前端传回续查。
索引创建原则有哪些
- 针对与数据量较大,且查询比较频繁的表建立索引。 (单表超过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