数据库
数据库
范式
第一范式确保每列保持原子性
第二范式确保表中的每列都和主键相关
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
Mysql
Innodb数据存放
表空间文件结构
行
Redundant：不是一种紧凑的行格式
compact：是一种紧凑的行格式，5.1之后默认
dynamic：是一种紧凑的行格式，默认Dynamic
compressed：是一种紧凑的行格式
compact格式
变长字段列表：记录变长字段长度，倒序存放，使访问真实数据的时候能尽量在一个cacheline中
null值列表：记录是否为null值，大小取决于允许为null值的个数，单位为字节，同样是逆序存放
记录头信息：包含指向下一条数据的指针
row_id：行id（如果有主键或者唯一约束id那么就没有row_id）6字节
trx_id：事务id 6字节
roll_ptr：回退指针 7字节
真实数据
varchar(n) 中 n 最大取值为多少
Mysql规定除了text和blob，其他的列（不包括记录头信息和隐藏列，包括变长字段列表和null值列表）总和不超过65535个字节
varchar（n）中的n代表的是字节数，与字符集有关
要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535
65532+2+1=65535
但是与字符集有关,需要处于对应的字节数才为个数
字符集
行溢出就会指向新增页的地址
Server执行流程
- 先与server的连接器建立连接
- 发送查询语句，server会先从查询缓存（查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。）查看，有就直接返回结果
- 进入解释器，进行语法分析生成语法树
- 依次通过预处理器（查看表的字段和表是否存在等工作，将*号扩展成所有列），优化器（基于查询成本看使用什么索引），然后到达执行计划当中，最后执行器执行
- 调用存储引擎api查询数据并返回
- 存入查询缓存
执行器
主键索引查询
read_first_record 读取第一条返回条件的记录，如果满足条件就会发送给客户端，然后第二次就会调用read_record获取下一个满足条件的记录，循环往复，直到不满足索引范围了
全表扫描
与主键索引相同，但是范围是全表 read_first_record后，会不断从read_record 获取数据行并判断数据是否满足条件
索引下推
前提是需要的字段包含在索引当中不然还是需要进行回表操作
由于索引是二级索引，每次需要通过二级索引找到主键值，然后回表查询完整数据，通过索引下推，可以再使用联合索引并且查询了索引上的字段的时候避免回表查询该字段是否满足条件
事务
事务特性
ACID
原子性是通过undolog实现的（要么全部完成，要么全部不完成）
持久性是通过redolog实现的（对数据的记录和修改都会保存下来）
隔离性是通过MVCC和锁实现（一个事务不会影响，不会影响其他事物的执行，是数据库拥有并发读取修改数据的能力）
一致性是通过 原子性 隔离性 持久性（多个事务执行满足完整性要求，并行执行结果和串行执行是一样的）
事务的隔离级别
脏写在下面的这4种级别都不存在，因为会加锁
- 序列化(SERIALIZABLE) 最高的事务隔离级别
- 可重复读(REPEATABLE READ) 幻读
- 提交读(READ COMMITTED) 不可重复读问题
- 读未提交(READ UNCOMMITTED) 脏读
可重复读是如何工作的
可重复读是在事务开始的时候创建ReadView，并且之后所有的操作都在使用这个ReadView
读已提交是每条语句都是用
如果使用的是普通select使用的mvcc
select for update等语句需要更新的时候，用的是行锁加间隙锁
ReadView（快照读）
ReadView中有四个字段 创建时的事务id 创建时活跃但未提交事务事务m_ids 以及其中的最大事务id(应该传给下个事务的id)和最小事务id(m_ids中的最小事务id)
小于最小事务id或者大于等于最大事务id则可见
在其中则会检查这个事务是否已经提交，如果已经提交则可见，反之不可见
数据行中的隐藏列：trx_id,roll_pointer
trx_id：当一个事务对一个数据行进行改动操作的时候，那么就会更新成这个事务的id
roll_pointer：事务可以根据这个roll_pointer找到undo日志，并以链表形式连接多个版本
Mysql存储引擎
索引
什么时候需要索引
- 字段有唯一性限制
- 经常order by group by where 的字段
什么时候不适合
- where order group 用不到的字段，快速定位用不到的字段
- 区分度较低，如男女
- 表数据少
- 经常更新的字段
- 表的字段较长，建立索引的时候字段较长会报错（1709 - Index column size too large. The maximum column size is 767 bytes.超过系统默认767字节数限制）和字符集和版本有关
增大限制后为（之前只有联合索引才为3072，单列为767）
字符集字节占用
latian 1字节 3072/1=3072
gbk 2字节 3072/2=1536
utf8 3字节 3072/3=1024
utf8m64 4字节 3072/4=768
索引分类
- 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
- 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引或非聚簇索引）。
- 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
- 按「字段个数」分类：单列索引、联合索引。
B+树
数据页结构
将所有行记录排序后分组，通过页目录来加快访问速度，通过页目录就可以知道每个分组最大记录，二分查找的方式就知道对应的行记录在那个分组
每个分组记录只有1-8条
B+树的具体结构
和其他结构进行对比
| B+树 | 红黑树 | Hash | B树 |
|---|---|---|---|
| 由于是多叉树，树的高度相较于二叉树较低，平均的IO次数较少 | 红黑树是二叉树，树的高度较高 | 没有高度，但是节点多的情况需要建立良好的散列函数来解决散列冲突 | 由于每个节点是个页，总共16KB，B树会存储在非叶子节点，导致能存放的分叉变少，导致层数变高 |
| InnoDB 的数据是按「数据页」为单位来读写的默认16KB，能将附近的一次数据读出来，在比较的时候也不用在此从磁盘读取。 | 每个节点只能判断一次大于或是小于 | - | B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。 |
| 由于B+树在叶子结点连接了一个双向列表，能够较快的进行范围查询（表头 表尾） | 需要判断多次 | 无法进行范围查询 | 需要判断多次 |
索引失效的常见场景
使用左或左右模糊匹配
like “%x“，但是当查询字段只包括该索引上的字段的时候，就会使用二级索引，并且会覆盖索引避免回表操作
对索引使用函数
从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。
对索引隐式类型转换
‘10000’ 变成 10000
过 select “10” > 9 的结果来知道MySQL 的数据类型转换规则是什么：
- 如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；
- 如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select “10” > “9”，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码） ，那么”10”字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0。
如果在查询字段上发生自动转换那么就走不了索引
联合索引最左匹配
顺序不重要，因为有查询优化器会自动调整顺序
索引（a,b,c）
b b c 不走索引
a c 截断索引也会使用索引下推
a> b= 只走a，大于的区间里 b无序
a>= b= 走a b 等于的时候b有序
WHERE 子句中的 OR
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
explain
索引优化
设置not null 空值也会占用每行一个字节存储空间，并且空值对索引选择也较为复杂，count不包括null
设置自增主键 插入的时候，相当于直接追加在页面末尾，写满了就新增一个页面，如果不是自增的话，在中间位置话就需要进行一个裂页操作，需要进行数据移动
覆盖索引优化 查询字段尽量覆盖到索引当中避免回表操作
前缀索引优化 可以使用字符的前几位建立前缀索引，减少查询范围
count
count（*）==count（0）
count（主键）直接走索引
count（字段）最好走索引
优化
估计值
额外创建表更新count值
InnoDB锁
全局锁
全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
例子：备份过程中购买商品后，余额已经备份过了 导致数据不一致
（如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。）
表级锁
表锁
读锁和写锁
元数据锁（MDL锁）
- 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
- 对一张表做结构变更操作的时候，加的是 MDL 写锁；
意向锁
当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。
意向锁的目的是为了快速判断表里是否有记录被加锁。
AUTO-INC 锁
AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
- 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1：
- 普通 insert 语句，自增锁在申请之后就马上释放；
- 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。
行级锁
行级锁的类型主要有三类：
- Record Lock，记录锁，也就是仅仅把一条记录锁上；
- Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
- Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
Record Lock
记录锁是有 S 锁和 X 锁之分的：
Gap Lock
Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。（无所谓只是不让在指定范围插入，多个范围也是一样不能插入，但是都可以修改）
Next-Key Lock
Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
插入意向锁
在插入区间拥有间隙锁或者Next-Key Lock的时候，表明插入意向，生成插入意向锁，等待锁住区间的事务提交后即可进行插入，和间隙锁互斥
加锁分析
如果是二级索引的行锁，非主键索引的话还要对主键索引进行加锁
所有索引之间里也包括索引与无穷大或者无穷小之间的间隙
唯一索引（主键）等值索引，行锁（不存在转为间隙锁，锁住应该在的区间） 意向锁
唯一索引（主键）范围查询，行锁（存在的话），间隙锁（所有索引之间的区间有满足条件的值就加锁），意向锁
非唯一索引等值索引，行锁（存在的话），间隙锁（所有索引之间的区间有满足条件的值就加锁，包括存在记录的前后），意向锁
非唯一索引范围索引 行锁（存在的话），间隙锁（所有索引之间的区间有满足条件的值就加锁，包括存在记录的前后），意向锁
不走索引 全表加间隙锁
日志
undolog
innodb引擎产生的日志，用于保证事物的原子性，在mvcc和事务回滚的时候会用到
redolog
innodb引擎产生的日志，用于保证事物的持久性，在掉电等故障恢复
redolog 是为了防止 Buffer Pool 中的脏页丢（其中也包括bufferpool中的undolog）失而设计的，记录格式为XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新
在事务提交的时候，只需要redolog刷盘即可，不需要等到将bufferpool中的脏页刷新到磁盘。
主要作用：崩溃恢复（在数据库宕机恢复的时候不会丢失数据）和提高读写速度（从原来的随机写变成了顺序写）
redolog buffer
redolog也有自己的buffer，在执行事务中并不是直接将redolog写入磁盘的，在以下时机会触发落盘
触发时机
- mysql正常关闭的时候
- 写入量大于一半 redolog buffer 内存空间一般的时候
- 后台线程每一秒刷盘
- 根据innodb_flush_log_at_trx_commit在事务提交的时候进行刷盘策略
innodb_flush_log_at_trx_commit值为0，事务提交的时候也不进行刷盘（崩溃的时候会丢失一秒的所有事务数据）
为1在事务提交的时候进行刷盘，并持久到磁盘当中（不会丢失）
为2的话在事务提交的时候写入到redolog文件当中，但并不是真正的写入到磁盘当中，相当于写入到操作系统的pageCache当中（如果进程崩溃，不会丢失，pageCache会继续写入到磁盘当中，如果系统宕机，可能会丢失一秒内的部分数据）
redolog写满
使用的循环日志文件组，一个文件写满会写到下一个文件，有两个指针writepos（当前位置）和checkpoint（上一个记录点）都往顺时针方向移动，当writepos追上checkpoint才会表示满了，那么mysql就会被阻塞进行bufferPool的脏页刷盘，并标记那些页面可以被擦除，然后checkpoint会往前移动，就会腾出空间继续写入。
binlog
innodb引擎产生的日志，主从复制和数据备份
binlog文件格式
- statement：记录修改的sql语句，但是修改的句子如果含有now（）或者uuid（）等函数的话生成的值就会发生改变
- row：记录行数据最终被修改成什么样
- mixed 两个结合，根据不同的情况自动使用row和statement
主从复制
- 写入binlog
- 同步binlog
- 回放binlog
模型
- 同步复制 （所有从库都返回成功消息）
- 异步复制（不等从库响应）
- 半同步复制（部分从库返回成功消息即可）
刷盘策略
binlog先写到线程的binlog cache，然后在write到binlog文件
sync_binlog: 为0的时候说明不进行fsync（），交给操作系统进行；为N的时候为积累N个事务一起提交
两段式提交原理
- 开启XA事务，redolog先写入xid，然后持久化到磁盘
- binlog写入xid，然后持久化到磁盘
- 设置redo log的commit状态，只需要写入到page cache就行，因为只要写到binlog了中事务就算成功
缺点
- io次数多（两次刷盘）
- 锁竞争激烈（早期版本保证事务提交顺序需要进行加锁才能进行prepare和commit阶段）
组提交
flush阶段：写入cache
sync阶段：刷盘
commit阶段：commit操作
依次入队，出队列进下一个队列，队列中的第一个成为leader会等待一定时间(或个数)然后进行批量操作
Buffer Pool
大致原理
buffer pool 缓冲池，读取数据的时候会先检查buffer pool 中是否命中，没命中才回去磁盘当中查找。写数据的时候会将一页数据写到buffer pool当中，修改的时候，如果存在bufferpool当中，直接修改其数据所在的页，然后设置该页为脏页，在后续一个合适的时机，在写入到磁盘当中
内存分配
空闲页：Free链表
脏页：Dirty链表
在这种有限空间都会采用一种淘汰算法，mysql采用了一种分区式的LRU算法
- 分为young区（前）和old区（后），先淘汰old区的，访问过一次就会放到头部
- 预读的时候先加载到old区，真正被访问且在old区域停留超过一秒以上的时候才会加载到young区域头部
- 为了减少换到头部的次数，在young区域前1/4就不会换到头部
原有的LRU的缺点：
- 磁盘预读无效
- bufferpool污染
数据库与缓存一致性
先更新数据库然后删除缓存
如果对一致性要求高，可以通过重试或者订阅binlog来保证缓存删除失败
- 消息队列方法先把需要删除的缓存值放入消息队列，然后在读取消息如果删除成功返回消费成功，如果失败就返回消费失败，那么下次会继续收到消息
- 通过canal中间件订阅binlog，缓存收到后会进行缓存删除
读写分离
主数据库进行写操作，从数据进行读操作，主从同步通过binlog实现
分库分表
垂直分库
根据不同的业务划分不同的数据库
水平分库
通过一定划分规则划分库
垂直分表
拆分字段
水平分表
根据主键拆分
分片算法
哈希分片
范围分片
地理位置分片
融合多个算法分片
方案
Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。
ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护。
ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。
