朱继鹏的博客

天道酬勤，全栈不停

0%

mysql

发表于 更新于 分类于
mysql

Basic

Concept

Design

  • 数据库设计范式：范式是一种理论
  • 数据库设计模型：模型是一种实践

数据库设计范式

  • 第一范式（1NF）：属性不可再分（属性是最小的原子单位）
  • 第二范式（2NF）：属性完全依赖于主属性（消除部分依赖）
  • 第三范式（3NF）：属性不依赖于非主属性（消除传递依赖）

范式的作用是解决 数据重复 带来的问题

  • 避免数据冗余（重复存储会浪费存储空间）
  • 避免插入异常（插入时关联信息不存在不能部分插入）
  • 避免更新异常（更新时所有重复的关联信息都得更新）
  • 避免删除异常（删除时关联信息也被一起连带删除了）

数据库设计模型

E-R模型（Entity-Relationship Model）：实体关系模型

Engine

  • MyISAM不支持事务，InnoDB支持事务
  • MyISAM不支持外键，InnoDB支持外键
  • MyISAM支持全文索引，InnoDB不支持全文索引（5.7以后的InnoDB支持全文索引了）
  • MyISAM不要求有唯一索引，InnoDB要求有唯一索引（没指定的话会生成隐藏的ROW_ID）
  • MyISAM用的是非聚簇索引，InnoDB用的是聚簇索引
  • MyISAM只支持表锁，InnoDB支持表锁和行锁
  • MyISAM保存了行数，InnoDB获取行数要全表扫描

Database

Table

Charset

  • character_set_client：客户端请求数据的编码
  • character_set_connection：客户端传输数据的编码
  • character_set_results：客户端请求结果的编码
  • character_set_server：数据库系统中数据库的默认编码（可以在配置中修改）
  • character_set_database：数据库系统中数据库的实际编码
  • character_set_system：数据库系统中存储元数据使用的编码
  • character_set_filesystem：数据库系统中数据存储文件使用的编码

Collation

Config

Engine

Charset

Collation

TimeZone

Auth

Log

Usage

DDL（Data Definition Language）

create

type

string
  • CHAR：1个字节（0~255）
  • VARCHAR：2个字节（0~65535）
  • BINARY：1个字节（0~255）
  • VARBINARY：2个字节（0~65535）
  • TINYTEXT：1个字节（0~255）
  • TEXT：2个字节（0~65535）
  • MEDIUMTEXT：3个字节（0~16777215）
  • LONGTEXT：4个字节（0~4294967295）
  • TINYBLOB：1个字节（0~255）
  • BLOB：2个字节（0~65535）
  • MEDIUMBLOB：3个字节（0~16777215）
  • LONGBLOB：4个字节（0~4294967295）

ps：2个字节可以存储 64K 大小的字符串
ps：3个字节可以存储 16M 大小的字符串
ps：4个字节可以存储 4GB 大小的字符串

char和varchar对比和选择
  • char是定长存储，varchar是变长存储
  • char存储时字符数不足用空格填充尾部，varchar存储时按照实际字符数存储（还需1到2个字节来存储长度）
  • char的查询速度较快（定长存储），varchar的查询速度较慢（变长存储）
  • char读取时会去除尾部的空格，varchar读取时不会去除尾部的空格
  • char和varchar读取时都会去除头部的空格
  • char和varchar存储时超过宽度后都会截断
varchar和text对比和选择
  • varchar支持默认值，text不支持默认值
  • varchar可以设置存储宽度，text不可以设置存储宽度
  • varchar超过255后会自动转为对应长度范围的text
char和varchar的默认值
char和varchar的空格处理
char和varchar的长度选择
number
  • TINYINT：1个字节（0~255或-128~127）
  • SMALLINT：2个字节（0~65535或-32768~32767）
  • MEDIUMINT：3个字节（0~16777215或-8388608~8388607）
  • INT：4个字节（0~4294967295或-2147483648~2147483647）
  • BIGINT：8个字节
  • FLOAT：4个字节
  • DOUBLE：8个字节
  • DECIMAL：按照实际需要来分配
  • BIT：按照实际需要来分配

ps：INT的最大值为2147483647（10位），BIGINT的最大值9223372036854775807（19位）

boolean
datetime
datetime
  • DATETIME：8个字节（1000-01-01 00:00:00.000000~9999-12-31 23:59:59.999999）
  • TIMESTAMP：4个字节（1970-01-01 00:00:01.000000~2038-01-19 03:14:07.999999）
  • DATE：3个字节（1000-01-01~9999-12-31）
  • TIME：3个字节（-838:59:59.000000~838:59:59.000000）
  • YEAR：1个字节（1901~2155或0000）

####### datetime和timestamp对比和选择

  • datetime可以表示的范围较大（占8个字节），timestamp可以表示的范围较小（占4个字节）
  • datetime不受时区影响，timestamp存储时会从服务端时区转为UTC时区，timestamp读取时会从UTC时区转为U客户端时区

####### datetime和timestamp的默认值

####### datetime和timestamp的自动更新

format
timezone
enum
null
ip
url
geo

constraint

  • 主键约束：PRIMARY KEY
  • 外键约束：FOREIGN KEY
  • 唯一约束：UNIQUE KEY
  • 列值约束：NULL | NOT NULL、DEFAULT
  • 自定义约束：CHECK
为什么不推荐外键
  • 外键需要额外的检查，影响性能
  • 外键需要锁住主记录，影响性能
  • 外键不利于分表分库，影响扩展

ps：不使用外键是为了牺牲一致性来保证可用性（类似于CAP中C和A不能同时满足）
ps：不使用外键的话则需要在应用层面来做约束检查

id

单主键的好处（相对联合主键）

  1. 数据空洞更少，占用空间更少（只针对Innodb引擎）
  2. 各种数据操作更快
  3. 数据校对时也更快
为什么不推荐uuid和雪花id
  • uuid和雪花id不是单调递增的，不是直接插入到缓存页尾部，而是需要寻找合适的位置去插入，会导致性能变差
  • uuid和雪花id不是单调递增的，会分散插入到多个缓存页中，会导致页分裂和频繁的换入换出，会导致性能变差

ps：虽然雪花id对于局部（某个机器内部）来说是单调递增的，但是对于全局（所有机器整体）来说却不是单调递增的

为什么id要求单调递增

单调递增：后面挨着的比前面大就行
连续递增：后面挨着的比前面大1个

alter

drop

DML（Data Manipulation Language）

  • insert：插入
    • insertOrReject：不存在就插入，存在时就拒绝
    • insertOrIgnore：不存在就插入，存在时就忽略
    • insertOrUpdate：不存在就插入，存在时就更新（部分更新）
    • insertOrReplace：不存在就插入，存在时就替换（整体替换）
  • update：更新
    • updateOrReject：存在时就更新，不存在就拒绝（不常见）
    • updateOrIgnore：存在时就更新，不存在就忽略（很常见）
    • updateOrInsert：存在时就更新，不存在就插入（等同于insertOrUpdate）

ps：修改（modify）广义上包含insert（插入）、update（更新）、delete（删除）操作
ps：替换（replace）也是一种修改，本质是delete（删除）+ insert（插入）

防重复插入（去重）方案

  • 直接插入 + 唯一约束：适合有唯一约束的
    • 使用insert into检测到插入失败（DuplicateKeyException）后换一个主键重试
    • 使用insert ignore into检测到插入失败后换一个主键重试
  • 检测插入 + 加锁控制：适合没有唯一约束的
    • 使用select for update检测到重复后换一个主键重试

防重复更新（去重）方案

  • 状态机 + CAS
  • 版本号 + CAS
    • 计数
    • 时间戳

insert

单个插入、批量插入

insertOrReject：insert into
insertOrIgnore：insert ignore into
insertOrUpdate: insert into … on duplicate key update
insertOrReplace：replace into

update

单个更新、批量更新

updateOrReject：无
updateOrIgnore：update set
updateOrInsert：等同于insertOrUpdate

delete

单个删除、批量删除

DQL（Data Query Language）

等值查询、多值查询、范围查询、模糊查询、逻辑查询、聚合查询、关联查询、子查询

general（常规查询）

select

sql语句书写顺序
  • SELECT
  • DISTINCT
  • FROM
  • JOIN
  • ON
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • LIMIT
sql语句执行顺序
  • FROM
  • ON
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • ORDER BY
  • LIMIT

ps：ON比JOIN先执行，JOIN比WHERE先执行

sql语句处理流程
  • 查询前过滤
    • ON
    • WHERE
  • 查询前处理
    • GROUP BY
    • HAVING
  • 查询后处理
    • DISTINCT
    • ORDER BY
    • LIMIT

ps：查询后处理（去重、排序、分页）也可以在应用程序侧实现

distinct

去重查询方案

  • distinct
  • groupBy

去重部分字段

1
2
select *, count(distinct id) from table group by id;
select id, name , count(distinct id) from table group by id;

where

groupBy

分组查询其他字段

  • MAX和MIN函数
  • ANY_VALUE函数
  • GROUP_CONCAT函数

orderBy

  • 快速排序：内部排序（sort_buffer够用的时候）
  • 归并排序：外部排序（sort_buffer不够用的时候）
  • 堆排序：内部排序（包含分页操作的时候）

limit

  • limit + distinct：去重后再分页
  • limit + groupBy：分组后再分页
  • limit + orderBy：排序后再分页，但是经过优化器优化后不会等到全部排序后再分页，这样就会导致不同的页返回重复的数据（可以将id列也加到排序里面来解决这个问题）

ps：排序后再分页用的优先级队列（堆结构）进行排序（堆排序），堆排序是不稳定的排序算法

union

  • union
  • union all

union和union all的区别

  • union会对重复记录进行去重，union all不会
  • union会按照主键进行排序，union all不会

equal（等值查询）

  • =（eq）
  • !=（ne）
  • <>（ne）
  • is null
  • is not null

multi（多值查询）

  • in
  • not in

range（范围查询）

  • <（lt）、<=（le、lte）、>（gt）、>=（ge、gte）
  • between and

范围查询：左闭右闭（数据库用的多）、左闭右开（编程语言用的多）、左开右闭、左开右开

fuzzy（模糊查询）

  • 前缀匹配：prefix（startsWith）
  • 后缀匹配：suffix（endsWith）
  • 中缀匹配：infix（contains）
  • 路径匹配：glob
  • 正则匹配：regex
  • 模糊匹配：fuzzy

logic（逻辑查询）

  • and
  • or
  • not

aggregation（聚合查询）

  • count
  • sum
  • avg
  • max
  • min

count(*)和count(1)与count(column)的区别

  • 当统计表的行数时
  • count(1)和 count(*)没区别
  • 理论上列有索引时count(column)比count(*)快(count(*)需要全表扫描)
  • 理论上列无索引时count(column)和count(*)一样快（都要全表扫描）
  • 但是优化器优化后count(*)会利用索引覆盖
  • 所以实际上列有索引时count(*)和count(column)一样快
  • 所以实际上列无索引时count(*)比count(column)快
  • 所以统计表的行数时推荐count(*)（count(*)是sql标准，而count(1)不是sql标准）
`count(*)和count(column)的区别
  • count(*)：统计表的行数（理论上会全表扫描，但优化之后会利用索引覆盖）
  • count(column)：统计列的行数（会过滤掉值为null的行，有索引的话会利用索引覆盖）
count(1)和count(column)的区别
  • count(1)：统计列值全部为1的匿名列（理论上会全表扫描，但优化之后会利用索引覆盖）
  • count(column)：统计列的行数（会过滤掉值为null的行，有索引的话会利用索引覆盖）
count(*)和count(1)的区别
  • count(*)：统计表的行数（理论上会全表扫描，但优化之后会利用索引覆盖）
  • count(1)：统计列值全部为1的匿名列（理论上会全表扫描，但优化之后会利用索引覆盖）

count(1) 的实际效果和 count(*) 一样，也是统计表的行数，并且两者理论上会全表扫描，但优化之后会利用索引覆盖，所以 count(1) 和 count(*) 没区别

relation（关联查询）

on是join之前过滤，where是join之后过滤，on比where先执行

sub（子查询）

子查询分类

  • 子查询按依赖分类
    • 相关子查询 子查询依赖父查询
    • 非相关子查询 子查询不依赖父查询
  • 子查询按返回分类
    • 表子查询 返回多行多列，即返回一张表格，用于父查询的FROM子句中
    • 行子查询 返回一行多列，即返回一条记录，用于父查询的FROM、WHERE子句中
    • 列子查询 返回多行一列，即返回一个集合，用于父查询的WHERE子句中
    • 标量子查询 返回一行一列，即返回一个值，用于父查询的SELECT、FROM、WHERE子句中
  • 子查询按类型分类
    • in子查询：非相关子查询 + 行子查询
    • all子查询：非相关子查询 + 行子查询
    • any子查询：非相关子查询 + 行子查询
    • some子查询（some是any的同义词）：非相关子查询 + 行子查询
    • exists子查询：相关子查询 + 任意子查询

in和exists的区别

  • in是非相关子查询，会将条件带入主表中进行查询，适用于次表数据量小的场景
  • exists相关子查询，会扫描主表的每行并去次表中匹配，适用于主表数据量小的场景

DCL（Data Control Language）

Operator

Function

Variable

Index

ps：索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中

Key

Category

  • 按索引功能分
    • 主键索引
    • 辅助索引（也叫二级索引）
  • 按字段特性分
    • 主键索引
    • 唯一索引
    • 普通索引
  • 按字段个数分
    • 单列索引
    • 联合索引（也叫组合索引、复合索引）
  • 按字段长度分
    • 前缀索引
    • 后缀索引
    • 全键索引
  • 按数据结构分
    • B+tree索引
    • Hash索引
    • Full-text索引
  • 按存储内容分
    • 聚簇索引：主键索引时使用，索引结点中包含主键和记录
    • 非聚簇索引：辅助索引时使用，索引结点中包含关键字和主键，不包含记录

ps：数据库的 辅助索引 和搜索引擎的 倒排索引 都是 关键字到主键 的反向映射关系

Principle

  • 从表的角度考虑
    • 数据量超过300的表适合建索引
    • 频繁更新的表不适合建索引
  • 从字段的角度考虑
    • 主键和外键必须建索引
    • 经常要查询筛选的字段适合建索引
    • 多字段查询时尽量用联合索引代替单列索引
    • 多字段查询时使用频繁的字段应该放在联合索引的左边
    • 多字段查询时包含NULL值的字段不适合建联合索引
  • 从数据的角度考虑
    • 散列性高的字段适合建索引
    • 字串类字段适合建前缀索引
    • 文本类字段不适合建索引

ps：还应该删除无用的索引，避免对执行计划造成负面影响

Invalid

  • 单列索引时查询条件有问题导致索引失效
    • 不等查询（!=）
    • 取反查询（not in）
    • like查询时使用百分号开头
  • 联合索引时字段组合没遵循最左前缀原则导致索引失效
  • 联合索引时查询条件有问题导致右边的索引失效
    • 联合索引时左边使用了不等查询（!=）导致右边的索引失效
    • 联合索引时左边使用了取反查询（not in）导致右边的索引失效
    • 联合索引时左边使用了like查询时使用百分号开头导致右边的索引失效
    • 联合索引时左边使用了范围查询导致右边的索引失效
    • 联合索引时左边使用了or查询导致右边的索引失效
  • 查询时使用了函数导致索引失效
  • 查询时进行了计算导致索引失效
  • 查询时触发了隐式转换导致索引失效
  • 排序时排序字段和查询字段不一致导致无法使用索引排序
  • 优化器认为全表扫描比较快导致索引失效（数据少或者需要回表）

ps：not between会分成两个范围，本质上还是范围操作

Question

  • is null：可以使用索引
  • !=：不可以使用索引
  • in：可以使用索引
  • not in：不可以使用索引
  • like：前缀模糊匹配可以使用索引，中缀和后缀模糊匹配不可以使用索引
  • range：可以使用索引
  • or：or前后的字段都有索引可以使用索引，or前后的某个索引字段都有索引则不可以使用索引
  • join：可以使用索引
  • orderBy：orderBy的字段出现在where条件中时可以使用索引

Problem

Optimize

Query

Sort

Transaction

什么是事务

事务是数据库中的最小执行单元，包含一条或多条sql语句，这些语句要么全部执行成功，要么全部都不生效

事务描述了如何解决 数据操 作 和 数据读写`的相关问题

  • 数据操作
    • 原子性问题
    • 并发性问题
  • 数据读写
    • 持久化问题
    • 一致性问题

ACID

Basic

  • 原子性（atomicity）：事务里的所有操作要么全部生效，要么全部不生效
  • 一致性（consistency）：事务的操作不会影响数据的正确性
  • 隔离性（isolation）：事务里的数据修改何时对其他事务可见
  • 持久性（durability）：事务对数据的更改会永久的保存到磁盘

ps： ACID 中 C 是 目的 ， AID 是 措施

ps： 原子性 保障的是 复合操作 ， 隔离性 保障的是 并发操作

ps： undo日志 保证 原子性 ， redo日志 保证 持久性 ， mvcc和lock机制 保证 隔离性

Atomicity

Consistency

Isolation

事务的隔离级别

  • 读未提交（RU -> Read Uncommitted）：问题为脏读（一个事务读到另一个事务未提交的更新数据）
  • 读已提交（RC -> Read Committed）：问题为不可重复读（同一条记录前后发生了变化）
  • 可重复读（RR -> Repeatable Read）：问题为幻读（记录的数量前后发生了变化）
  • 串行化（Serializable）：没有问题，但性能差

ps：是为了解决binlog为STATEMENT时主从执行的SQL顺序不一致导致主从数据不一致的问题，RC级不能解决，RR级别通过加锁解决了

Read-Write（读写）

隔离性解决的是数据库事务并发的读写问题，是通过MVCC（快照读）和Lock（当前读）来实现的

  • 读问题
    • 并发读：不存在问题
    • 写后读：不一致性（脏读、不可重复读、幻读）
  • 写问题
    • 并发写：插入冲突、更新丢失
    • 读后写：写入偏差
  • 死锁

读操作的类型

快照读：写写冲突， 读写不冲突 （通过MVCC的写时复制机制实现），读读不冲突
当前读：写写冲突， 读写冲突 （通过读写锁的读写互斥机制实现），读读不冲突

  • 快照读（不加锁）
    • select（MVCC）
  • 当前读（加锁）
    • select for update（写锁）
    • select lock in share mode（读锁）
    • insert（写锁）
    • delete（写锁）
    • update（写锁）

ps：当前读只影响其他事务的当前读，不会影响其他事务的快照读，因为快照读不加锁

各种select的区别

  • 读快照的数据时用select
  • 读最新的数据但不修改时用select lock in share mode
  • 读最新的数据且要修改时用select for update

ps： select for update 可以用来解决并发写入时的脏写问题

Dirty Read（脏读）
脏读的场景
  • 读取到了其他事务还未提交的数据

ps：脏数据指的就是还未提交的数据

脏读的问题
  • 会读到脏数据并导致逻辑错误（脏数据被使用后如果数据被回滚后了会导致逻辑错误）
脏读的案例
  • A事务读取计数为0
  • A事务增加后更新计数为1
  • B事务读取计数为1
  • B事务增加后更新计数为2
  • A事务遇见异常回滚后更新计数为0
  • B事务提交后计数为2
  • 此时就出现了逻辑错误
  • 因为只有一个计数操作成功了
  • 所以计数正确的值应该为1而不是2
Unrepeatable Read（不可重复读）
不可重复读的场景
  • 重复读取同一条记录时内容发生了变化
不可重复读的问题
  • 看具体业务场景的要求
不可重复读的案例
  • 看具体业务场景的要求
Phantom Read（幻读）
幻读的场景
  • 前后查询时记录数量发生了变化（新增或者删除了记录）
  • 刚才不存在的记录现在存在了（检测到不存在后去插入时却提示存在了）
  • 刚才存在的记录现在不存在了（检测到存在后去查询、更新、删除却提示不存在了）
幻读的问题
  • 检查没问题后却出现了插入冲突
  • 检查没问题后却出现了更新异常
  • 检查没问题后却出现了删除异常
  • 检查没问题后却出现了查询异常
幻读的案例
  • A事务检查记录不存在
  • B事务检查记录不存在
  • B事务插入记录
  • A事务插入相同主键的记录
  • A事务因主键冲突插入失败（明明先检查了不存在，插入的时候却提示已存在，像发生了幻觉）
Dirty Write（脏写）
脏写的场景
  • 读后写：读完数据后修改并写入
  • 并发写：同时写入数据
脏写的问题
  • 写入偏差（读后写问题）
    • 检查时没问题，但插入时出现了问题，比如插入冲突
    • 检查时没问题，但更新后违背了约束，比如更新为负值
  • 更新丢失（并发写问题）
    • 先写的数据覆盖了后写的数据
    • 回滚的数据覆盖了写入的数据
脏写的案例
  • A事务读取库存为1
  • A事务扣减后更新库存为0
  • B事务读取库存为1
  • B事务扣减后更新库存为-1
  • A事务提交修改后库存为0
  • B事务提交修改后库存为-1
  • 此时就出现了库存为负数的问题

Durability

Question

  • 当前事务可以读到当前事务还未提交的修改
  • 其他事务可不可以读到当前事务已提交的修改则需要看情况
    • 事务A最先开始
    • 事务B在事务A提交之前开始
    • 事务C在事务A提交之后开始
    • 则如果隔离级别为读已提交，则B可以读到A的修改
    • 则如果隔离级别为可重复读，则B不可以读到A的修改
    • C可以读到A的修改

mysql事务隔离级别为什么默认是RR

是为了解决binlog为STATEMENT时主从执行的SQL顺序不一致导致主从数据不一致的问题，RC级不能解决，RR级别通过加锁解决了

  • 事务A删除id为1的数据
  • 事务B插入id为1的数据
  • 事务B先提交
  • 事务A后提交
  • 此时主库会存在id为1的数据
  • 此时从库不存在id为1的数据
  • 因为binlog是按照提交顺序记录的，会记录成先插入后删除

ps：binlog是提交的时候按照提交顺序记录的，不是按照事务的执行顺序记录的

Problem

MVCC

Basic

MVCC实现原理

  • 写时复制（CopyOnWrite）
  • 快照读（ReadView）
  • 版本链（UndoLog）

MVCC实现原理之隐藏字段

DB_ROW_ID：6字节，记录ID，没有主键时会自动用DB_ROW_ID生成一个聚簇索引
DB_TRX_ID：6字节，版本号，记录的是修改这条记录的事务的版本号
DB_ROLL_PTR：7字节，回滚指针，指向记录的上一个版本

MVCC实现原理之ReadView

核心概念

  • 当前事务id：creator_trx_id，创建ReadView的事务id
  • 活跃事务id：m_ids，创建ReadView时未提交的事务id集合
  • 最小事务id：min_trx_id，m_ids里的最小id
  • 最大事务id：max_trx_id，系统分配给下一个事务的事务id
  • 记录事务id：trx_id，修改记录时的事务的事务id

查找过程

  • 判断记录是否可见
    • trx_id等于creator_trx_id，当前事务修改过的数据，对于当前事务可见
    • trx_id小于min_trx_id，当前事务开启前修改的数据，对于当前事务可见
    • trx_id大于等于max_trx_id，当前事务开启后修改的数据，对于当前事务不可见
    • trx_id在m_ids中，当前事务开启时未提交的数据，对于当前事务不可见
    • trx_id不在m_ids中，当前事务开启时已提交的数据，对于当前事务可见
  • 如果记录可见，就接受当前记录
  • 如果记录不可见，则通过记录的roll_ptr找到上一个版本的记录继续判断，直到所有版本的记录都判断过

ps：删除的记录会有删除标记（delete_mark），会被过滤掉

Question

MVCC和隔离级别的关系

隔离级别为 读已提交 和 可重复读 时会启用MVCC机制

  • 读已提交：事务中每次查询时都生成新的ReadView
  • 可重复读：事务中第一次查询时只生成一次ReadView

Problem

  • MVCC可以解决 不可重复读 的问题
  • 不可重复读是通过MVCC中的快照（ReadView）和版本链（UndoLog）解决的
  • MVCC只能解决 幻读 的部分问题， 当前读 时的幻读问题
  • 幻读中的 记录数量变化 问题可以通过MVCC（快照读）来解决
  • 幻读中的 记录新增和删除 问题需要通过LOCK（当前读）来解决

Lock

Basic

锁的分类

锁的类型分类
  • 是否锁住资源：悲观锁（锁住资源）、乐观锁（不锁住资源）
  • 是否独占资源：排他锁（独占资源）、共享锁（共享资源）、读写锁（读共享和写独占）
锁的实现分类
  • 悲观锁：
    • 读写锁
  • 乐观锁：
    • 版本字段
    • 时间戳字段
锁的范围分类
  • 表锁：table锁（开销小，加锁快，无死锁）
  • 行锁：row锁（开销大，加锁慢，易死锁）
    • 记录锁：record锁
    • 间隙锁：gap锁
    • 临键锁：Next-key锁

ps：查询时如果有索引会使用 行锁 ，没有索引时则会使用 表锁

ps：Next-key锁是record锁和gap锁的组合，是左开右闭的

意向锁

  • 意向锁和意向锁之间是兼容的
  • 意向锁和表锁之间的共享锁是兼容器的，共享锁和互斥锁以及互斥锁和互斥锁之间是不兼容的
  • 意向锁和行锁之间是兼容的

ps：意向锁是表级别的锁，只影响表锁，不影响行锁

Question

锁和隔离级别的关系

读未提交：只加record锁，操作完记录就释放锁
读已提交：只加record锁，事务提交时才释放锁
可重复读：加record锁或者gap锁或者Next-key锁，事务提交时才释放锁

锁和查询索引的关系

  • 有索引：行锁
  • 无索引：表锁

锁和查询类型的关系

  • 有唯一索引：会先加 临键锁
    • 等值查询：
      • 命中索引时 临键锁 会退化成 记录锁
      • 没有命中索引时 临键锁 会退化成 间隙锁
    • 范围查询：
      • 命中索引时 临键锁 会退化成 间隙锁
      • 没有命中索引时加临键锁，不退化
  • 有非唯一索引：两端都会先加 临键锁
    • 等值查询：
      • 命中索引时除了 临键锁 还会额外添加一把 间隙锁 （会加2把锁）
      • 没有命中索引时除了 临键锁 会退化成 间隙锁
    • 范围查询：
      • 有没有命中索引都加临键锁，不退化
  • 无索引：会导致全表扫描，行锁会升级为表锁

Problem

DeadLock（死锁）

Log

Basic

mysql事务日志

  • undolog：事务回滚数据时需要的日志
  • redolog：宕机修复数据时需要的日志
  • binlog：主从数据同步时需要的日志

ps：WAL（Write-Ahead Log）：预写日志（顺序写磁盘，速度快），undo和redo都是预写日志

Question

为什么需要undolog

undolog是用来支持事务回滚的

  • undolog记录了数据的上一个版本

为什么需要redolog

redolog是用来支持数据恢复的

  • redolog记录了数据的变更
  • redolog是顺序写操作，比直接将数据写入磁盘时的随机写更快
  • redolog的占用空间小，比直接将数据写入磁盘时的耗时更短

为什么需要binlog

binlog是用来支持主从复制和备份恢复的

  • binlog记录了数据的变更操作（SQL语句）

undolog、redolog、binlog的区别

  • 日志归属
    • undolog是引擎层面的日志（InnoDB有而MyISAM没有）
    • redolog是引擎层面的日志（InnoDB有而MyISAM没有）
    • binlog是服务层面的日志（InnoDB和MyISAM都有）
  • 日志类型
    • undolog是物理日志，记录的是数据行的变更
    • redolog是物理日志，记录的是数据页的变更
    • binlog是逻辑日志，记录的是变更操作（SQL语句）

undolog、redolog、binlog的产生

  • undolog：
    • 事务执行前生成
  • redolog：可以通过innodb_flush_log_at_trx_commit控制写入策略
    • 0：事务执行时写到redolog buffer，每隔1秒刷新到redolog file后立即调用fsync刷盘
    • 1：事务执行时写到redolog buffer，事务提交时刷新到redolog file后立即调用fsync刷盘
    • 2：事务执行时写到redolog buffer，事务提交时刷新到redolog file，每隔1秒调用fsync刷盘
  • binlog：可以通过sync_binlog控制写入策略
    • 0：事务执行时写到binlog cache，事务提交时刷新到binlog file，何时刷盘由操作系统决定
    • 1：事务执行时写到binlog cache，事务提交时刷新到binlog file，每次提交时刷盘
    • N：事务执行时写到binlog cache，事务提交时刷新到binlog file，每N个事务提交后刷盘

ps：innodb_flush_log_at_trx_commit默认为1，sync_binlog默认为0
ps：undolog也会生成对应的redolog来保证undolog的日志数据的持久化

undolog、redolog、binlog的释放

  • undolog：事务提交后不会立即释放，要等到没用的时候释放
  • redolog：刷盘后就释放
  • binlog：需要手动释放或者配置过期时间

redolog和binlog的区别

  • redolog属于InnoDB引擎的功能，binlog属于MySQL-Server的功能
  • redolog是物理日志，记录是数据页的修改，binlog是逻辑日志，记录的数据更改操作
  • redolog是循环写入，日志空间是固定大小，binlog是追加写入，日志空间会持续增长
  • redolog是作为服务器异常宕机后事务数据自动恢复使用的，binlog是作为主从复制和备份恢复使用的
  • redolog有crash-safe的能力，binlog没有crash-safe的能力

ps：binlog时二进制文件，而不是文本文件

redolog和binlog的主从一致性

redolog和binlog其中有一个写盘失败，会导致主（redolog）从（binlog）数据不一致的问题

mysql使用两阶段提交来保证redolog和binlog的主从一致性

  • 阶段1：redolog写盘后，修改事务的状态为prepare状态
  • 阶段2：binlog写盘后，修改事务的状态为commit状态

写失败的场景分析

  • redolog写盘时崩溃，此时redolog和binlog都没有新数据，不存在不一致性
  • redolog写盘后修改事务状态时崩溃，此时binlog中没有数据且事务处于initial状态，恢复的时候会回滚事务
  • binlog写盘时崩溃，此时binlog中没有数据且事务处于prepare状态，恢复的时候会回滚事务
  • binlog写盘后修改事务状态时崩溃，此时binlog中有数据且事务处于prepare状态，恢复的时候会提交事务

binlog为什么没有crash-safe的能力

  • 虽然binlog拥有全量的日志，但binlog没有一个标志能够判断哪些数据已经刷盘和哪些数据还没有
  • redolog则不存在这种问题，因为redolog刷盘成功后已刷盘的部分会被清除

Problem

Scope

session和global控制的是新的设置生效的范围

  • session：会话，也就是当前连接立即生效
  • global：全局，不包含当前连接，之后新获取的连接都会生效

Problem

并发操作

  • 读问题
    • 并发读：不存在问题
    • 写后读：不一致性（脏读、不可重复读、幻读）
  • 写问题
    • 并发写：插入冲突、更新丢失
    • 读后写：写入偏差
  • 死锁

数据丢失

  • 持久化时innodb_flush_log_at_trx_commit设置为0或2会丢数据（未刷盘的数据在宕机的时候会丢数据）
  • 主从切换时主从同步不完整时会丢数据
  • 程序有bug误操作删除数据
  • 人为误操作删除数据

主从延迟

主从延迟会导致读写分离时不能立马读到最新的数据

Performance

  • optimize（查询和性能优化）
  • explain（查询分析工具）
  • profile（性能分析工具）
  • slowlog（慢查询日志）
  • monitor（性能监控工具）

Optimize

查询优化

  • 索引
    • 常用的查询条件要添加索引
    • 避免单列索引时查询条件有问题导致索引失效
      • 避免不等查询（!=）
      • 避免取反查询（not in）
      • 避免like查询时使用百分号开头
    • 避免联合索引时字段组合没遵循最左前缀原则导致索引失效
    • 避免联合索引时查询条件有问题导致右边的索引失效
      • 避免联合索引时左边使用了不等查询（!=）导致右边的索引失效
      • 避免联合索引时左边使用了取反查询（not in）导致右边的索引失效
      • 避免联合索引时左边使用了like查询时使用百分号开头导致右边的索引失效
      • 避免联合索引时左边使用了范围查询导致右边的索引失效
      • 避免联合索引时左边使用了or查询导致右边的索引失效
    • 避免查询时使用了函数导致索引失效
    • 避免查询时进行了计算导致索引失效
    • 避免查询时触发了隐式转换导致索引失效
  • 查询
    • select时尽量只选择需要的字段
    • or查询优化为in或者union
    • 排序时注意要利用索引排序
    • 分页时可以使用子查询进行索引覆盖
    • 子查询和关联查询时注意要用小表驱动大表
    • 查询时避免使用长事务
  • 大数据
    • 预处理
      • 预统计 + 物化
      • 预查询 + 缓存
    • 分库分表
    • 使用数据仓库和大数据组件

性能优化

  • 使用更好的服务器
  • 增大缓冲池
  • 增大连接数

操作优化

  • 多次的操作优化为批量处理
  • 长时间操作优化为分批处理

Analysis

explain的输出说明

  • id：每个select子句的标识id（值越大越先被执行）
  • select_type：查询类型
  • table：当前表名（有时不是真实的表名）
  • type：查询方式
  • possible_keys：可能使用的索引
  • key：实际使用的索引
  • key_length：使用的索引长度
  • ref：索引的哪一列被使用了
  • rows：可能需要扫描的行数
  • Extra：额外的信息说明

explain的Type说明

  • system：只有一条数据的表
  • const：主键索引，根据键直接查询到记录（常量级别）
  • eq_ref：唯一索引，查出关联的一个主键后，根据主键回表查询记录
  • ref：普通索引，查出关联的多个主键后，根据主键回表查询记录
  • range：范围查找
  • index：索引覆盖（select的字段在索引里就有）
  • all：全表扫描

explain的Extra说明

  • Using temporary：使用了临时表
  • Using filesort：使用了文件排序
  • Using index：使用了索引覆盖
  • Using index condition: 使用了索引下推（Index Condition Pushdown）
  • Using where: 使用了引擎过滤
  • Using join buffer：使用了连接缓冲（连接时没有使用索引）
  • Using MRR：使用了MRR优化（先将主键排序后再回表，因为相近的索引可能在相同的页上）

Diagnosis

SlowLog

Monitor

Architecture

  • 主备：主节点负责读写，备节点负责备份（主节点故障后备节点会替换主节点）
  • 主从：主节点负责读写，从节点负责读取（主节点故障后从节点会替换主节点）
  • 主主：
  • 集群：
  • 分布式：

Replication

主从复制

主从复制过程

  • master将DDL和DML操作记录到binlog文件中
  • slave的IO线程负责从master的logdump线程那里接收binlog并写入relaylog文件中
  • slave的SQL线程负责执行relaylog文件的sql语句

ps：主从同步是从库去主库拉取，而不是主库推给从库

主从复制模式

  • 异步复制模式：主库写完binlog后不管binlog是否同步到了从库就返回
  • 同步复制模式：主库写完binlog后等待binlog同步到了所有的从库才返回
  • 半同步复制模式：主库写完binlog后等待binlog同步到了至少一个从库就返回
  • 全局事务ID步复制模式：半同步复制并用全局事务ID来改善主从同步的一致性问题

ps：半同步：semi-sync
ps：全局事务ID：GTID
ps：半同步复制时master如果没有收到slave的ack，会降级为异步复制

主从复制文件

  • binlog：master的日志文件
  • relaylog：slave的日志文件
  • master.info：master的信息文件，保存了slave读取binlog文件和位置信息
  • relaylog.info：slave的信息文件，保存了slave应用relaylog的执行点信息

binlog的格式

  • STATMENT（statement-based replication, SBR）：记录sql语句
  • ROW（row-based replication, RBR）：记录每行的变更
  • MIXED（mixed-based replication, MBR）：混合使用SBR和RBR

ps：SBR在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数
ps：MBR会根据sql语句选择格式，优先使用SBR，SBR不能用的话才使用RBR

binlog格式和隔离级别的关系

  • RC：只支持row格式的binlog（如果指定了mixed格式也会自动使用row格式）
  • RR：支持statement、row和mixed格式

ps：statement和mixed格式可能会存在主从数据不一致的问题

主从延迟

TODO：mysql主从延迟

Cluster

HA（High Availability）：高可用

主从架构

MHA：Mysql High Availability

主主架构

主主架构就是互为主从架构

Distributed

分布式数据核心对象

  • 分片（shard）：负责读写
  • 副本（replica）：负责备用

HA（High Availability）：高可用

ps：分布式是系统一般自带高可用机制

数据分片（Sharding）

分表分库的实现方案

  • 分表
    • 垂直拆分：将字段拆分到多个表中
    • 水平拆分：将记录拆分成多个表中
  • 分库
    • 垂直拆分：将表格拆分到多个库中
    • 水平拆分：将记录拆分到多个库中

分表分库的查询问题

  • join
  • group by
  • order by
  • 事务问题
  • 非分区键查询

分表分库的分区策略

  • 按照范围分
  • 使用hash算法分
    • 普通hash
    • 一致性hash
    • hash槽

ps：一致性hash和hash槽能够很好的支持节点的扩容和缩容
ps：节点路由（node route）问题是通过hash算法来解决的
ps：节点故障（node failure）问题则是通过主从架构来解决的

分表分库的查询过程

  • 基于分区键的查询会直接找到对应的分区进行查询
  • 不基于分区键的查询会在所有的分区中查询并合并结果

分区键的分区方式

  • 单键分区：比如user_id
  • 多键分区：比如user_id和order_time合成一个单键user_id:order_time后再分区

非分区键的查询优化

  • 冗余法：根据user_id和order_id各做一个分表分库的实现，查询order_id时直接去order_id的分表分库中查
  • 索引法：建立order_id和user_id的全量数据关联表，查询order_id时先去关联表里面查出user_id
  • 基因法：order_id的低n位设置为user_id的低n位，这样order_id和user_id的hash取模结果就一样

数据分区（Partition）

Query

读写分离

冷热分离

TODO：mysql冷热分离

Backup

Application

  • OLTP：联机事务处理
  • OLAP：联机分析处理
  • HTAP：联机混合事务和分析处理

Config

基于唯一键和hash索引实现

  • namespace
  • key
  • value

Counter

基于 update counter set count = count + 1 where name = 'pv' 实现

ps：发号器也可以通过计数器来实现

  • if not update counter set count = count + 1

  • then insert into counter(name, count) values('pv', 1)

  • select count from counter where name = 'pv'

  • namespace

  • key

  • count

Theory

mysql的耗时点为磁盘IO，这就要求IO的次数尽可能少，即结点的层级尽可能得少

  • 红黑树是二叉树，结点的层级很高，排除
  • B树是多叉树，结点的层级较低，但是B树的结点既包含索引所以又包含数据，导致结点的层级比B+树高，也排除
  • 红黑树和B树的范围查找比B+树复杂（B+树的叶子结点通过指针连接起来）

Other

Management

管理

Visual

Tools

Permission

Audit

Dump

Operation

运维

TODO：mysql不停机迁移

只想买包辣条