MySQL八股

MySQL

基础架构

• 连接器：用户登录MySQL时，进行身份认证和鉴权
• 分析器：对SQL语句进行词法分析（提取关键字）和语法分析（分析正确性）
• 优化器：根据 SQL 语句和表的统计信息，选择一种执行成本最低的执行计划
• 执行器：检验是否有执行权限，调用存储引擎的接口执行SQL

存储引擎对比

对比项	InnoDB	MyISAM
默认存储引擎	是（MySQL 5.5+）	否
事务支持	✅ 支持 ACID	❌ 不支持
锁机制	✅ 行锁、间隙锁、临键锁	❌ 仅表锁
并发性能	高，适合高并发读写	较低，写操作容易阻塞
崩溃恢复	✅ 支持（Redo Log + Crash Recovery）	❌ 不支持，断电可能损坏数据
MVCC	✅ 支持	❌ 不支持
外键	✅ 支持	❌ 不支持
主键索引	聚簇索引（数据存储在主键叶子节点）	非聚簇索引（索引和数据分离）
二级索引	保存主键值，需要回表	保存数据文件地址，直接定位数据
COUNT(*)	扫描最小可用索引统计	维护总行数，速度快
数据与索引存储	通常存储在 .ibd 文件中	数据 .MYD，索引 .MYI 分离存储
数据安全	高，支持事务和崩溃恢复	较低，异常断电可能需要修复
典型应用	电商、金融、互联网业务、绝大多数 OLTP 系统	历史上的读多写少场景，如部分报表或日志系统（现已较少使用）

慢查询优化

性能分析

在查询语句前加EXPLAIN分析SQL的执行计划：

• possible_key：可能使用的索引

• key：实际使用的索引

• key_len：索引占用的大小（可以判断索引是否失效）

• Extra：额外的优化建议

  Extra	解释
  Using where; Using Index	使用了索引且不需要回表
  Using index condition	使用了索引，但需要回表

• type：sql的查询的类型，当为index或all时速度慢

  类型	说明
  system	查询系统中的表
  const	根据主键查询
  eq_ref	主键索引查询或唯一索引查询，只查询一条数据
  ref	索引查询
  range	范围查询
  index	索引树扫描
  all	全表扫描

sql优化

表设计优化：

1. 选择合适的数值类型（tinyint、int、bigint）
2. 选择合适的字符串类型（char、varchar）

SQL语句优化：

1. select语句避免使用select *（可能造成回表查询）
2. 避免索引失效
3. 尽量使用union all代替union（union会过滤重复数据，效率相对较低）
4. 尽量用inner join 代替left join/right join，尽量小表驱动大表，减少数据库连接。inner join会优化把小表放到外边，大表放到里边
5. 主从复制，读写分离

如何定位慢查询

方案一：开源工具

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

方案二：MySQL慢日志

# my.cnf开启慢日志（在调试阶段才会开启，因为会损耗性能）
slow_query_log = 1
# 设置执行时间(s)多久视为慢查询
long_query_time = 2

# 在localhost-slow.log中记录了慢查询

索引

高效获取数据的数据结构，提高数据库的检索效率，降低数据库的IO成本

B树 & B+树

Blance Tree，多路平衡二叉树，每个节点有多个key

对比项	B 树（B-Tree）	B+ 树（B+Tree）
非叶子节点	保存 Key 和数据	只保存 Key 和子节点指针
叶子节点	保存 Key 和数据	保存所有 Key 和完整数据（聚簇索引）或主键值（二级索引）
查询结束位置	可能在任意节点	必须到叶子节点
叶子节点之间	无连接	双向链表连接
范围查询	效率一般	效率非常高
树的高度	相对较高	更低
单个节点可容纳 Key 数	较少	更多
磁盘 I/O 次数	更多	更少
查询性能	不够稳定	稳定，每次都到叶子节点
MySQL 是否采用	❌ 不采用	✅ InnoDB 聚簇索引和二级索引均采用 B+ 树

数据结构选型对比

• 哈希表：不支持顺序和范围查询
• 二叉搜索数：搜索时非常依赖平衡程度，最坏可能退化成链表
• 平衡二叉树：自身需要频繁旋转来保持平衡
• 红黑树：平衡性相对较弱，因此保持平衡的操作开销较小，但数据量大时层数太大
• B树（Blance Tree）：多叉路平衡查找树，非叶子节点存储数据，层级比B+树大
• （mysql的）B+树：类似B树，只在叶子节点存数据(非叶子节点能存更多索引，层级更小)，叶子节点形成一个循环链表(范围查询)

B+树优势：磁盘读写代价更低，查询效率稳定，便于扫库和范围查询

索引类型

主键索引

主键列使用的就是主键索引，一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引

二级索引的叶子节点存储的是主键的值

1. 唯一索引（Unique Key）：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引（Index）：普通索引的唯一作用就是为了快速查询数据。一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引（Prefix）：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
4. 全文索引（Full Text）：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MyISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚簇索引和非聚簇索引

• 聚簇索引（必须有且只有一个）：数据和索引保存在一块，索引结构的叶子节点保存了行数据

  • 优点：速度快，对排序查找和范围查找优化
  • 缺点：若索引列不是有序的，插入时位置随机，会增大页分裂的概率，写入效率降低

• 非聚簇索引（可以有多个）：数据和索引分开存储，叶子节点关联的是对应的主键

  • 优点：更新代价比聚簇索引小
  • 缺点：可能会回表查询

  回表查询：走二级索引找到主键后需要走聚集索引才能找到数据

  覆盖索引：如果要找的数据就是二级索引的值，那么就不需要回表查询

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引

最左前缀匹配：在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率；

最左匹配原则会一直向右匹配，**直到遇到范围查询（如 >、<）为止**。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配；

可以将区分度高的字段放在最左边，这也可以过滤更多数据。

索引下推

Index Condition Pushdown，ICP。把原本需要回表后才能判断的部分条件，下推到索引扫描阶段判断，从而减少回表次数。

原本的判断是在SQL Server层执行的，索引下推则是在存储引擎层执行

-- 例如有索引
CREATE INDEX idx_name_age ON user(name, age);

-- 执行SQL，如果没有索引下推，会对每一条符合条件的记录，都回表查询判断age > 20
-- '> 不会走联合索引'
SELECT * FROM user
WHERE name = 'Tom' AND age > 20;

-- 有了ICP之后，会使用联合索引里已有的age来先判断

索引失效

• 违背最左前缀原则：跳过联合索引前导列，或遇到范围查询（如 >、<、BETWEEN、LIKE "abc%"）导致后续列中断精确定位，降级为范围扫描加过滤。
• 对索引列进行加工：在 WHERE 左侧对索引列进行数学计算或应用函数，导致原始数据发生逻辑改变，在索引树中呈现无序状态。
• 隐式类型转换（隐蔽且致命）：当“字符串类型的列”去比较“数字类型的值”时，MySQL 会默认在列上套用转换函数，直接破坏树的有序性。
• LIKE 模糊查询前置通配符：如 LIKE "%abc"，前缀字符的不确定性使得优化器无法锁定扫描区间的起始点。
• ORDER BY 排序陷阱：排序列未命中索引、排序方向与索引结构不一致等触发额外的内存或磁盘排序

建立索引建议

• 不为 NULL 的字段：对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或短字符作为替代。
• 不为经常更新的字段：会增加索引维护的成本
• 被频繁查询的字段
• 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
• 限制每张表的索引数量：控制数量不超过5个
• 尽可能建立联合索引
• 字符串类型使用前缀索引

事务

ACID

原子性(Atomicity)：事务是最小的执行单位，不允许分割，动作要么全部完成，要么完全不起作用；

一致性(Consistency)：所有的数据都保持一致的状态

隔离性(Isolation)：事务不受外部并发影响

持久性(Durability)：事务提交或回滚，对数据库的影响是永久的

并发事务问题

脏读：一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据

丢失修改：两个事务同时修改一个数据，其中一个数据的修改被覆盖了

不可重复读：事务先后两次读取同一条记录，但读到的数据不同（被其它事务修改了）

幻读：事务用相同语句读取两次数据，得到的条数多了

并发事务的控制方式

行锁：

行锁是针对索引字段加的锁；

如果INSERT和DELETE时，条件里没有使用到索引/索引失效可能会导致大范围的上锁

• 记录锁：属于单个行记录上的锁
• 间隙锁：锁定一个范围，不包括记录本身
• 临键锁：锁定一个范围，包含记录本身，主要目的是为了解决幻读问题

表锁：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

  意向锁的作用是在加表锁前快速判断是否有行锁；用户无法手动操作意向锁；意向锁之间是相互兼容的；

事务隔离级别

基于锁和MVCC实现

• READ-UNCOMMITTED(读未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。这种级别在实际应用中很少使用，因为它对数据一致性的保证太弱。
• READ-COMMITTED(读已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。这是大多数数据库（如 Oracle, SQL Server）的默认隔离级别。
• REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。MySQL InnoDB 存储引擎的默认隔离级别正是 REPEATABLE READ。并且，InnoDB 在此级别下通过 MVCC（多版本并发控制） 和 Next-Key Locks（间隙锁+行锁） 机制，在很大程度上解决了幻读问题。
• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MVCC

Multiversion concurrency control，多版本并发控制，维护一个数据的多个版本，使得读写操作没有冲突

1. 读操作（快照读）：

   • 事务会查找符合条件的数据行，并选择符合其事务开始时间的数据版本进行读取
   • 如果某个数据行有多个版本，事务会选择不晚于其开始时间的最新版本，确保事务只读取在它开始之前已经存在的数据
   • 事务读取的是快照数据，因此其他并发事务对数据行的修改不会影响当前事务的读取操作

   锁定读（当前读）：对读取到的记录加锁，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读

2. 写操作：

   • 事务会为要修改的数据行创建一个新的版本，并将修改后的数据写入新版本
   • 原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响

3. 提交和回滚：

   • 当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见
   • 当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见

InnoDB对MVCC的实现：

• 每行数据的隐藏字段：

  • DB_TRX_ID：最后一次插入或更新该行的事务 id

  • DB_ROLL_PTR：回滚指针，指向该行的 undo log 。如果该行未被更新，则为空

  • DB_ROW_ID：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

• readView：

  可重复读级别：在事务第一次进行快照读的时候创建；

  读已提交级别：每次读都创建新的；

  主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

  主要有以下字段：

  • m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
  • m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
  • m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
  • m_creator_trx_id：创建该 Read View 的事务 ID

• undo log版本链：记录的多个版本串成链表，链表头为最新的记录

  用于回滚记录；

  另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

  

日志文件

bin log：二进制日志文件，记录了 MySQL 数据库中数据的所有变化，记录每一条DDL和DML，用于备份恢复和主从复制

redo log：重做日志，记录缓冲池里数据的变化。当刷新脏页到磁盘时发生了错误，就使用redo log进行数据恢复，保证持久性

undo log：回滚日志，记录被修改前的信息或反向操作。提供回滚和MVCC，保证一致性和持久性

性能优化

读写分离

在写请求在主库进行，读请求分散到从库

代理层有多种实现，MySQL官方提供了MySQL Router轻量级数据库代理，用于将客户端请求智能路由到合适的 MySQL 实例、自动故障切换等功能

主从复制

主从复制是异步执行的，因此存在短暂的主从延迟

原理：

1. 主库记录DML和DDL语句到bin log 日志中
2. 从库启动一个线程，不断询问主库bin log 是否更新
3. 从库收到bin log 后，会先保存在relay log（中继日志）中，即使数据库重启，也不会丢失同步内容
4. 之后从库会顺序执行relay log里的记录来同步数据

如何避免主从延迟：

• 直接读主库：对于先写后读的场景，可以读主库，避免读不到自己写入的数据
• 延迟读取
• 等待从库追上进度

分库分表

分库分表的成本很高，在其他优化方法（sql优化、索引、缓存等）都效果不好后才会使用

分库：

• 垂直分库：把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库
• 水平分库：把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题

分表：

• 垂直分表：对数据表列的拆分，把一张列比较多的表拆分为多张表
• 水平分表：是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题

常用水平分库分表算法：

• 哈希分片：指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。分布均匀，但范围查询不友好
• 范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，适合范围查询，不适合随机读写，可能出现热点数据问题

分库分表带来的问题：

• join 操作：分库分表后的难点是跨分片 join：数据可能分布在多个库表中，中间件需要广播、路由、合并甚至做笛卡尔组合，性能和实现复杂度都会上升。
• 事务问题：需要引入分布式事务
• 分布式 ID
• 全局唯一约束问题：单库唯一索引只能保证单个分片内唯一。比如手机号、用户名、商家订单号如果没有作为分片键，数据库很难直接保证全局唯一。
• 跨库聚合和分页查询问题：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。

深度分页

查询偏移量过大的场景称为深度分页；

当查询偏移量过大时，MySQL 的查询优化器可能会选择全表扫描而不是利用索引来优化查询

慢的原因：对于LIMIT offset N，MySQL会查询offset + N条数记录，有必要的话会再回表查询，最后取最后N条数据。如果成本过大，还有可能全表扫描

优化方式：

1. 子查询：查出第一个记录的id，使用这个id来过滤后再limit

   -- 先通过子查询在主键索引上进行偏移，快速找到起始ID
   SELECT * FROM t_order
   WHERE id >= (
       SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1
   ) ORDER BY id LIMIT 10;

2. 延迟关联：将limit操作转移到主键索引树上，减少回表次数

   -- MySQL需要对整个查询结果集进行排序，当数据规模过大时，排序时间会明显增加
   select * from tb_sku limit 900000, 10;
   
   -- 使用覆盖索引+子查询来优化
   select *
   from tb_sku t,
   	(select id from tb_sku order by id limit 900000, 10) a
   where t.id = a.id;

3. 游标分页：通过上一页最后一个id，不支持跳页

   # 通过记录上次查询结果的最后一条记录的 ID 进行下一页的查询
   SELECT * FROM t_order WHERE id > 100000 ORDER BY id LIMIT 10

4. 覆盖索引：不需要回表

数据备份

导出备份sql文件

mysqldump -u [username] -p [database_name] > [backup_file].sql

恢复数据

mysql -u [username] -p [database_name] < [backup_file].sql

MyBatis

执行流程

1. 加载配置文件（运行环境，映射文件）
2. 构建SqlSessionFactory
3. 通过SqlSessionFactory创建SqlSession对象（提供增删改查API）
4. Executor核心作用是处理SQL请求、事务管理、维护缓存以及批处理等
5. 把映射文件里的信息封装到Exector的MappedStatement中
6. 把请求参数转化为mysql可以识别的数据类型
7. 把返回值转化为java对象

延迟加载

在执行 SQL 查询时，只加载需要立即返回到 Java 对象中的数据，而将其余数据的加载推迟到访问时

MyBatis 中的延迟加载主要是针对一对多和多对多的关联关系中的关联对象进行的

通过fetchType = true开启延迟加载

<!-- 延迟加载一对多关联关系 -->
<resultMap id="userMap" type="User">
  <id column="id" property="id"/>
  <result column="userName" property="userName"/>
  <result column="password" property="password"/>
  <collection property="posts" ofType="Post" column="id"
              select="selectPostsByUserId" fetchType="lazy"/>
</resultMap>

<select id="selectUserById" resultMap="userMap">
  SELECT * FROM user WHERE id = #{id}
</select>

<select id="selectPostsByUserId" resultType="Post">
  SELECT * FROM post WHERE userId = #{id}
</select>

开启全局延迟加载

mybatis:
  configuration:
    default-lazy-loading-enabled: true

缓存

一级缓存：作用域为Session，当Session被flush()或者close()时缓存就会清空

二级缓存：作用域为整个MySQL实例，任何客户端都可以使用

• 二级缓存需要缓存的数据要实现Serializable接口

• 只有Session提交或关闭后，一级缓存中的数据才会转移到二级缓存中

• 当进行了增删改操作后，一、二级缓存都会清空

• 一级缓存默认开启，二级缓存要手动开启：

  mybatis:
    configuration:
      cache-enabled: true

  // 在mapper上加上@CacheNamespace
  // 在方法上加上 @Options(useCache = true)
  @CacheNamespace
  public interface UserMapper {
      @Select(value = "select * from user where id = #{id}")
      @Options(useCache = true)
      User selectUserById(Integer id);
  }

  // 如果是在xml中定义的sql语句，需要加上<cache/>