Java八股文——MySQL篇

#王者杯·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(多版本并发控制,支持快照读)

  • 触发场景:INSERTUPDATEDELETE 时生成 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、付费专栏及课程。

余额充值