MySQL

发表于 更新于 分类于 ，

1. 优化

1.1 慢查询

页面加载过慢, 接口测试时响应时间过长

1.1.1 如何定位

Pasted image 20260310200109 ##### 开源工具 1. 调试工具: Arthas 2. 运维工具: Prometheus、Skywalking
MySQL自带慢查询

MySQL在/etc/my.cnf中设置了sql慢查询语句
Pasted image 20260310165720

1.1.2 慢查询日志分析

这条SQL语句执行地很慢该如何分析?

Pasted image 20260310214714
type

type(该语句查询用了什么类型的表)
这一列有NULL、system、const、eq_ref、range、index、all

NULL: 没有用到表
system: 查询系统中的表
const: 根据主键查询
eq-ref: 主键索引查询或唯一索引查询
ref: 索引查询
range: 范围查询
index: 索引树查询
all: 全盘扫描

explain
  • 通过key和key_len检查是否命中索引 (索引本身是否存在失效的情况)
  • 通过type字段查看sql是否有进一步优化的空间, 是否存在全索引扫描/全盘扫描
  • 通过extra建议判断是否出现回表的情况, 出现了可以尝试添加索引/修改返回字段来修复

1.2 索引

通过减少磁盘 IO, 帮助数据库快速定位数据的数据结构。
数据库索引是一种用于快速定位数据的数据结构。

1.2.1 B+树

在 MySQL 的 InnoDB 存储引擎中,索引通常使用 B+Tree 来实现。
B+Tree 是一种多叉搜索树
其节点在物理上对应一个 page,InnoDB 默认 page 大小为 16KB。
非叶子节点只存储 key 和子节点页号,用于索引导航,而真正的数据存储在叶子节点中。

1
2
3
4
5
6
Page
 ├─ Header
 ├─ Record 链表扫描 (Record 通常对应一行数据(Row)，但除了用户列外，还包含事务信息、回滚指针等隐藏字)
 └─ Slot 稀疏索引用以二分查找, 注意, 他只在页内使用
 (利用指针可以频繁增删, 但是不好查找, 
因此引入page dictionary, 即slot(页内稀疏索引) , slot会进行二分查找, 然后进入链表顺序查找)
查找三步走

分为 B+树层级比较找到page + 页内稀疏索引查找record (slot二分) + 顺序查找(双向链表)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT *  
FROM tb_sku  
WHERE id > 9000000  
LIMIT 10;

1.root
↓
internal node
↓
leaf page  (id ≈ 9000000)

2.进入page后: 
slot（二分）
↓
record
找到id > 9000000

3.利用叶子节点链表向后扫描10条
B+树和红黑树

数据库的真正瓶颈是磁盘IO, 所以谁磁盘IO消耗小, 谁才是胜者!

由于每个 page 可以存储大量 key,因此 B+Tree 的 fanout 非常大,树高度通常只有 3 到 4 层(10亿到100亿)
而红黑树本质是二叉搜索树, 在大量数据下fanout小, 则高度大, 导致磁盘IO

叶子节点之间通过双向链表连接,因此 B+Tree 非常适合范围查询。

如果没有索引,数据库需要进行全表扫描,读取所有数据页并逐行比较,这会产生大量磁盘 IO,因此查询效率较低。

1.2.2 聚簇索引和二级索引 (数据与索引)

聚簇索引

1.数据和索引 存储在同一棵 B+Tree
2.叶子节点存储完整行数据
3.一张表只能有一个聚簇索引 (因为数据在磁盘上只能按一种顺序存储)

二级索引

1.数据和索引 分开存储
2.叶子节点存主键值

回表查询

通过二级索引找到主键值, 再通过主键索引找到行数据

索引下推

索引下推 让数据库 在扫描索引时,直接在索引层面进行过滤,而不是把数据扫描出来后再通过 WHERE 子句去筛选,减少了不必要的 I/O 操作。

覆盖索引(是否回表)

如果一个查询 需要的所有字段都能从索引中直接获取,数据库就不需要再访问表数据页。

比如我用了 name=amy 这个二级索引来select id, name
name二级索引中的叶子节点是id, 所以二级索引覆盖了我要找的所有字段

深分页查询优化

MySQL 执行 LIMIT offset,size 时需要扫描 offset + size 条记录,
MySQL 无法直接定位到第 offset 条记录,
必须从索引或数据的起点开始顺序扫描。

如何优化?
1.游标分页: 借助主键/唯一字段进行翻页 游标分页vs传统分页

1
2
3
4
5
6
7
SELECT * FROM tb_sku LIMIT 9000000, 10;

SELECT * 
FROM tb_sku
WHERE id > 9000000
ORDER BY id
LIMIT 10;

2.覆盖索引+子查询
在大范围扫描 且需要进行排序的情况下, 子查询尽量使用覆盖索引.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
原 SQL：
SELECT * FROM tb_sku ORDER BY create_time LIMIT 9000000,10;

问题：
按 create_time 排序时会扫描大量二级索引项；
如果直接 SELECT *，会产生大量回表。

优化：
SELECT t.*
FROM tb_sku t
JOIN (
    SELECT id
    FROM tb_sku
    ORDER BY create_time
    LIMIT 9000000,10
) s ON t.id = s.id;

原理：
子查询只查 id，可利用 create_time 二级索引形成覆盖索引；
先拿到目标 10 个 id，再外层按主键回表查询完整数据。 (回表即通过二级索引找到主键回找行数据)

核心：
减少深分页场景下的大量回表。

1.2.3 索引创建的原则

  1. 数据量大且查询频繁的表要建立索引
  2. 尽量使用联合索引, 而非单列索引 (覆盖索引可减少回表次数)
  3. 索引数量不能太多 (删改数据要维护, 多则成本大)
  4. 用常作为查询条件、排序、分组的字段作为索引
  5. 索引内容区分度要高, 比如地区全是北京就没必要建立地区索引
  6. 索引列不能存储NULL值, 在创建表时要使用NOT NULL约束

1.2.4 索引失效的情况

1.违反最左前缀法则

比如有一索引是 id_name_address, 但是却 where id=xx and address=xx,
违反最左前缀法则

2.范围查询, 右侧字段无法使用索引

where id = 1 and name > ‘Tom’ and address = ‘广州’
name范围查询, 所以address无法使用索引

3.’%xxxx’的模糊查询

where name like ‘%Tom’
因为左侧有 %,无法利用 B+Tree 的有序性,所以 索引失效。

4.在索引上进行操作运算 (计算、函数、表达式)

where substring(name, 1, 3) = ‘Tom’
因为对索引列做了函数运算,MySQL 无法直接利用原索引,所以 索引失效。

5.字符串不加单引号而引起的类型转换

比如有一索引是 phone,字段类型是 varchar,但是却:
where phone = 13800138000
因为字符串字段和数字比较时会发生类型转换,可能导致 索引失效。

总结
  1. 违反最左前缀法则, 联合索引中断
  2. 范围查询之后,联合索引右侧列失效
  3. like 以 % 开头,索引失效
  4. 对索引列做函数/运算,索引失效
  5. 隐式类型转换,索引可能失效

1.3 优化

1.3.1 表的设计优化

  1. 对于字段, 根据实际情况选择合适的数值
  2. 设置合适的字符串类型( char 和 varchar ), char是定长效率更高, varchar可变长度效率偏低

1.3.2 SQL语句的优化

  1. select语句务必指明字段名称, 避免直接使用select * (因为可能会产生回表)
  2. SQL语句要避免索引失效的写法详见
  3. 尽量用union all 代替union, union会多一次过滤掉重复的, 效率低
  4. 避免在where子句中对字段进行表达式操作
  5. join能用inner join就别用left right join,
    如果必须用left/right, 则小表在外, 大表在内 (减少连接次数,提高效率)

1.3.3 读写分离 主从复制

主节点负责写入操作, 从节点负责读操作, 读写分离, 避免写操作影响读操作

2. 事务

2.1 事务的特性

事务具有原子性, 一致性, 持久性, 隔离性

  1. 原子性A: 事务是不可分割的最小操作单元, 要么成功要么失败 undo log
  2. 一致性C: 事务完成时, 所有数据都保持一致状态
  3. 隔离性I: 多个事务同时干活,彼此别乱影响。
  4. 持久性D: 事务一旦提交或回滚, 它对数据库中的数据的改变是永久的 redo log
例如 A 向 B 转账 500:  
A 扣 500,B 加 500。  
原子性:两步必须一起成功或一起失败。  
一致性:转账前后系统总金额不变。  
隔离性:转账过程中不能被其他事务干扰。  
持久性:转账提交后,即使数据库重启数据仍然存在。

2.2 并发事务问题与隔离级别

2.2.1 脏读 & 幻读 & 不可重复读

脏读: 事务B读到了事务A未提交的数据 –> 读未提交

不可重复读: 事务A先后读取同一条记录, 两次读取到的数据却不一样 –>

幻读: 事务查询数据时, 没有发现对应的数据行
插入数据时, 却发现已经存在了

2.2.2 RU & RC & RR & S

隔离级别 脏读 不可重复读 幻读
Read Uncommitted √ √ √
Read Committed × √ √
Repeatable Read(默认) × × √
Serializable × × ×
隔离级别越高, 性能越差

2.2.3 undo log & redo log

redo log (持久性)
内存层的引入

由于 磁盘 I/O 的性能远低于内存,
如果每次执行 update 操作都直接将数据页写入磁盘,会产生大量随机 I/O
因此 MySQL 在 数据文件与磁盘之间加入了内存层(Buffer Pool):

1
2
3
4
5
数据修改  
↓  
先修改内存中的数据页  
↓  
后台线程再统一刷盘
redo log引入

1.引入原因

如果 MySQL 在数据页刷盘之前宕机,内存中的修改就会丢失。
为了解决这个问题,MySQL 引入了 redo log(重做日志)。

redo log 的核心思想是 WAL(Write-Ahead Logging):

在真正修改数据文件之前,先将修改操作记录到日志中。

update即时的更新到磁盘是随机访问式的, 但是redolog像清单一样顺序地写入修改操作, 效率更高

写入方式 IO类型
数据页更新 随机写
redo log 顺序写

2.redolog的组成

redolog = redo log buffer(内存) + redo log file(磁盘)

过程如下:

修改 Buffer Pool 数据页
↓
生成 redo log
↓
写入 redo log buffer
↓
刷入 redo log file
↓
事务提交

3.redolog的写入方式

checkpoint: 之前的 redo log ,对应的数据页 ,已经写入磁盘, 其前面的日志是可以覆盖的
redo log file 是固定大小,采用循环写入。当日志写满后,只能覆盖已经完成 checkpoint 的日志。

undo log (原子性)

undo log 是 InnoDB 的回滚日志,用于记录数据修改前的旧值。
它通过记录与数据页完全相反的操作实现以下两个功能:
第一是支持事务回滚,实现事务的原子性;
第二是为 MVCC 提供历史版本,实现多版本并发控制.

对于insert, 产生的undo log日志, 只在回滚时需要, 一旦事务提交即可被立即删除
而update、delete, 产生的undo log日志不仅在回滚时需要, MVCC也需要, 不会立即被删除

redo log undo log
类型 物理日志 逻辑日志
作用 crash recovery rollback + MVCC
保证 持久性 原子性 & 一致性
写入方式 顺序写 随数据修改生成

2.2.4 MVCC

MVCC详解

MySQL InnoDB 的事务隔离是通过 锁和 MVCC 实现的。
MVCC 是多版本并发控制,它通过维护数据的多个版本,使读写操作不冲突。

MVCC 的实现依赖三个核心组件:
第一是 隐藏字段,包括 trx_id 和 roll_pointer,分别记录修改事务和指向旧版本。
第二是 undo log,用于保存数据的历史版本,并通过roll_pointer形成版本链。
第三是 ReadView,用于在查询时判断当前事务可以看到哪个版本的数据。

查询时会沿着版本链查找,通过 ReadView 的规则判断版本是否可见。
如果版本不可见,就继续向旧版本查找,直到找到可见版本。

在 RC 隔离级别下,每次查询都会生成新的 ReadView
在 RR 隔离级别下,只在第一次查询生成 ReadView,之后复用,从而实现可重复读。

3. 主从同步原理

MySQL主从复制的核心就是binlog
binlog记录了所有DDL & DML语句, 但不包括数据查询(select, show)等语句

Pasted image 20260313152657
  1. 主库在(DDL & DML)事务提交时, 会把数据的变更记录在binlog
  2. 从库读取 binlog 写入到从库的中继日志relay log
  3. 从库重做relay log的事件, 并记录数据

4. 分库分表

分库分表
水平拆分一般指的是把数据行拆开, 而垂直拆分一般指的是把字段拆开

分表: 比如原来只有:
order
后来拆成:
order_0
order_1
order_2
order_3
通常还在同一个库里,这叫 水平分表。

垂直分表: 不同字段访问频率不同, 可以按频率拆分字段, 经常访问的字段组合在一起

例如用户表:
user(id, name, age, phone, avatar, intro, address, create_time)
你发现很多字段访问频率不一样,可以拆成:
user_base(id, name, age, phone, create_time)
user_profile(id, avatar, intro, address)
特点:
1. 冷热数据分离
2. 减少IO过度争抢, 两表互不影响

垂直分库: 把不同业务模块拆到不同库。

水平分库和水平分表都是靠%的方式来决定去哪存/找

分库后的问题

分布式事务的一致性问题
跨节点关联查询
跨节点分页、排序
主键避重

UUID vs Snowflake

解决方式: 中间件MyCat、Sharding-sphere