MySQL
2023-05-24 11:19:14 3 举报AI智能生成
适合后端、测试、运维
作者其他创作
大纲/内容
MySQL基础知识
三大范式
第一范式（确保每列保持原子性）
数据库表中所有字段值都是不可分解原子值（原子的定义主要还是依据业务）
第二范式（确保表中的每列都和主键相关）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
第三范式（确保每列都和主键列直接相关，而不是间接相关）
第三范式需要确保数据库表中的每一列数据都和主键直接相关，而不是间接相关
日志文件
错误日志
作用
记录MySQL服务的启停时正确和错误的信息，还记录启动、停止1、运行过程中的错误信息
设置
慢查询日志
作用
记录所有执行时间超过long_query_time的所有查询或不使用索引的查询
设置
long_query_time=10 #指定慢查询超时时长（默认10秒），超出此时长的属于慢查询
log_queries_not_useing_indexes=OFF #查询没有使用索引的时候是否也计入慢查询日志
查询日志
作用
记录了所有对数据库请求的信息，不能正确执行的信息也会进行记录
设置
二进制日志
作用
记录所有更改数据或可能引起更改的语句，可用于数据恢复和复制
设置
使用mysqlbinlog工具进行查看
以事件的形式记录，使用mysqlbinlog工具
InnoDB事务日志
redo 日志
概念
重做日志，通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样
作用
它用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置）
构成
内存中的日志缓存（redo log buffer）,该部分日志是易失性的
磁盘上的重做日志文件（redo log file）,该部分日志是持久的
redo log 与二进制日志的区别
1、二进制日志是在存储引擎的上层产生的，而redo log是InnoDB层产生的，只记录该存储引擎中表的修改，并且二进制日志先于redo log被记录
2、二进制日志记录操作的方法是逻辑性的语句，即使他是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少，而redo log是物理格式上的日志，它记录的是数据库中每个页的修改
3、二进制日志只在每次事务提交的时候一次性写入缓存日志“文件”（对于非事务表的操作，则是每次执行语句成功后就直接写入）。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作（先写入再提交）
4、事务日志记录的是物理页的情况，他具有幂等性，因此记录日志的方式极其简练，幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化，而二进制日志记录的是所有影响数据的操作，记录的内容较多，例如插入一行记录一次，删除该行又记录一次
undo 日志
概念
undo log一般是逻辑日志，根据每行记录进行记录，用来回滚行记录到某个版本
作用
提供回滚和多版本并发控制MVCC
构成
他是逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，他会记录一条对应相反的update记录
存储引擎
MyISAM
事务支持
不支持事务
锁机制
只支持表锁
表的存储方式
数据表和索引表分开（非聚集索引）
MyIASM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针
外键支持
不支持
索引类型
一般是非聚集索引
InnoDB
事务支持
支持事务
锁机制
提供行锁，表锁
表的存储方式
数据表同时也是主索引表（聚集索引）
InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键值
外键支持
支持外键
索引类型
主键索引是聚集索引
其他区别
InnoDB不保存具体表的具体行数，执行select count(*) from table 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
InnoDB表必须有主键（用户没有指定的话会自己找/生产一个隐藏Row_id来充当默认主键），而MyISAM可以没有
索引
索引分类
存储方式划分
哈希索引
B+树索引
聚集索引
辅助索引（非聚集索引）
逻辑区分
普通索引
没有任何限制（允许重复与空值），唯一目的就是加快系统对数据的访问速度
创建关键字时INDEX或KEY
唯一索引
创建唯一索引的目的不是为了提高访问速度，而是为了避免数据出现重复
不允许重复值，允许为null
创建关键字时UNION
主键索引
主键索引就是专门为主键字段创建的索引，不允许重复值和null
创建关键字时PRIMARY KEY
全文索引
全文索引主要用来查找文本中的关键字，只能在CHAR、VARCHAR、或TEXT类型的列上创建
允许有重复值和null
创建关键字时FULLTEXT
实际使用区分
单列索引
单列索引就是索引只包含原表的一个列，在表中的单个字段上创建索引，单列索引只根据该字段进行索引
联合索引
多列索引时在表的多个字段上创建一个索引，该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中的第一个字段时，索引才会被使用
B树与B+树
B树
关键字（包含数据）集合分布在整棵树里面
任何一个关键字出现且仅只出现在一个节点中
搜索有可能在非叶子节点结束
其搜索性能等价于在关键字全集类做一次二分查找
B+树
关键字不保存数据，只用来索引，所有数据都保存在叶子节点中
叶子节点包含了所有的关键字，是有序的，同时时一个双向链表，尾指针指向头部
非叶子节点可以视为索引部分，仅包含其子树的最大（或最小）关键字
B树和B+树的对比
1、B+树空间利用率更高，可减少IO次数
索引往往以索引文件的形式存在于磁盘上
而B+树的每个节点只存放索引信息，B树的节点还需要额外存放关键字具体信息地址指针
因此同样一次IO，在读入相同大小的情况下，B+树可以查找更多的关键字
2、范围查询的效率更高
B+树的叶子节点包含所有的关键字，并以有序链表结构存储，基于范围查询的效果更好，同时也利用了磁盘预读的特性（B+树扫库只要扫叶子节点，B树扫库要进行中序遍历）
3、B+树查询效果更稳定
B+树每次查询的时间复杂度都是logn,而B树的复杂度是不稳定的
B+树与红黑树的对比
1、更低的树高度
平衡树的查找时间复杂度是和树高直接相关的，红黑树的出度为2，B+树的出度非常高，一般B+树的高度都在2-4层
2、B+树利用了磁盘预读特性
数据库系统将索引的一个节点大小设置成一个页的大小，使得每次IO都可以完全载入一个节点，节省IO次数，同时相邻的节点也可以被预先载入
为什么要把自增ID作为主键
B+树索引
聚集索引
按照每一张表的主键构造一颗B+树，同时叶子节点存放的是一整张表的行记录数据，也称为数据页，数据页之间用指针连通
非叶子节点只存关键字
好处
对主键的排序查找和范围查找的速度非常快，一方面数据页有序且用指针连通，另一方面不需要再去查询数据页，而是直接获取
非聚集索引（辅助索引）
叶子节点除了包含关键字以外，每一个叶子节点中的索引行还包含一个书签，就是相应数据行的聚集索键
查询流程
首先遍历辅助索引，并通过叶子节点，获得指向对应主键索引的指针
然后在通过主键索引来找到一个完整的行记录
也就是要查两次
联合索引
定义
联合索引是指对表上的多个列合起来做一个索引
排序规则
(1,1)，(1,2)，(2,1)，(2,4)，(3,1)，(3,2)，数据按照(a,b)的顺序进行了存放（就是字典序，先排第一位，然后相同情况下看次位）
最左匹配原则
定义：最左优先，以最左边的起点任何连续的索引都能匹配上，同时遇到范围查询（>、<、between、like）就会停止匹配
假设组合索引为a,b,c的话；那么当SQL中对应有：a或a，b或a，b，c的时候，可称为完全满足最左原则；当SQL中查询条件对应只有a，c的时候，可称为部分满足最左原则；当SQL中没有a的时候，可称为不满足最左原则
建立(a,b)顺序的索引，如果b=2是匹配不到(a,b)索引的，但是如果查询条件是a=1and b=2或者a=1(又或者是b=2 and b=1)就可以，因为优化器会自动调整a,b的顺序。再比如a=1 and b=2 and c>3 and d=4如果建立（a,b,c,d）顺序的索引，d是用不到索引的，因为c字段是一个范围查询，他之后的字段会停止匹配
覆盖索引
从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录
好处：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
Hash索引
概念
哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针
Hash索引与B+树索引
1、如果是等值查询，哈希索引有绝对的优势
memory引擎可以不要求键值唯一，需要先找到键值所在链表，然后往后扫
2、Hash索引不支持范围查询与排序
3、在有大量重复值的情况下，Hash索引的效率很低
4、Hash索引不支持键的部分匹配
全文索引
概念
通常在类型为char、vchar、text的列建立全文索引，可以在列内容比较大的情况下，进行模糊查询（包含语法语义上的查询，也可以查字符串，返回匹配值）
原理
全文索引的对象是一个“全文集合”，如果对表的多个列建立全文索引，MySQL就会将这几列拼接成一个字符串，然后进行索引。全文索引实际上也是B+Tree结构，不过比较特殊，它一共有两层，第一层是所有关键字，第二层则是每个关键字的一组指文档针，例如"X"->行1，行2，行3.....。通俗解释全文索引结构就是：它是以关键字去找文档（行），而不是像其他一些索引以行主键来找其他列的内容
倒排索引
在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关键数组实现，其拥有两种表现形式
inverted file index：其表现形式为（单词，单词所在的文档ID）
full inverted index：其表现形式为{单词，(单词所在文档的ID，在文档中的具体位置)}
总结
总的来说就是有一个表存放分词后的关键字，然后每个关键字都把自己在哪些字段出现过标出
缺点
1、占用存储空间更大，如果内存一次性装不下全部索引，性能会非常差
2、增删改代价更大，修改文本中10各单词，则要操作维护索引10次，而不是普通索引的一次
3、如果一个列上有全文索引则一定会用上，即使有性能更好的其他索引也不会用上。由于只是存储文档指针，也就用不上索引覆盖
Cardinality
索引的实际使用
聚集索引的创建情况
InnoDB默认对主键建立聚簇索引
如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替
如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引
创建索引的优缺点
优点
1、索引可以将随机IO变成顺序IO，可以大大提高系统的性能
2、通过创建唯一性索引，可以保证数据库中每一行数据的唯一性
3、索引大大减小了服务器需要扫描的数据量，提高数据的检索速度
4、可以加速表与表的连接
5、创建索引可以帮助服务器避免排序和临时表，在使用分组和排序子句进行检索的时候，可以显著减少时间
缺点
1、创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加
2、索引需要占用物理空间，如果是聚集索引，占用空间更大
3、对表中的数据进行增加、删除和修改的时候，索引也要动态维护，降低了维护的速度
如何选择索引列
适合创建
1、经常需要搜索的列
2、主键列
3、经常需要连接的列
4、经常需要范围搜索的列
5、经常需要排序的列
不适合创建
1、查询很少的列
2、不重复很少的列（比如性别）
3、频繁修改更新的列
4、定义为text，image的列
查询优化
1、定位慢查询语句
查看慢查询日志
使用explain进行分析
2、优化数据访问
减少请求的数据量
只返回必要的行（不用select *）
只返回必要的列（limit）
缓存重复查询的数据
减少服务器扫描的行数
最好就是存在覆盖索引的操作
3、重构查询方式
切分大查询
分解大连接查询
改为对单表进行查询
索引失效的情况
1、条件中有or 不走索引
2、违反最左匹配原则
还有一种情况如下：（A,B,C）建索引，如果任意一个字段使用了范围搜索（>,<,等）那么这个字段之后的索引是失效的
3、like查询以%开头
4、not in /not exist
5、查询条件使用函数在索引列上，或者对索引列进行运算
例如：select * from test where id-1=9
6、字符型字段为数字时在where条件里不添加引号
锁
锁的类型
锁粒度划分
表锁
种类
读锁/共享锁/S锁
针对同一份数据，多个读操作可以同时进行而不会相互影响（select）
写锁/排他锁/X锁
当前写操作没完成前，会阻塞其他读和写操作（update、insert、delect）
意向排他锁（IX）
一个事务给一个数据行加排他锁时，必须先获取该表的意向排他锁IX锁
意向共享锁（IS）
一个事务给一个数据行加排他锁时，必须先获取该表的意向共享锁IS锁或者更强的锁
特点
对整表加锁
开销小
加锁快
无死锁
锁的粒度大，发生锁冲突的概率极高，并发性低
引擎默认锁
MyISAM
行锁
种类
读锁（S）/共享锁
允许一个事务去读一行，阻止其他事务获得相同的数据集的排他锁
写锁（X）/排他锁
允许获得排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁
特点
对一行数据加锁
开销大
加锁慢
会出现死锁
锁粒度小，发生锁冲突的概率低，并发度高
引擎默认锁
InnoDB
页锁
开销、加锁、粒度介于表锁和行锁之间
乐观悲观
乐观锁
概念
乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发生冲突了，则让返回用户错误的信息，让用户决定如何去用
具体流程（版本号实现)
1、为数据添加一个版本标识，数据每更新一次，版本号+1
2、在读取数据的时候，将版本号快照保留下来，然后更新数据
3、在提交更新的时候，判断当前版本信息与之前保留的版本快照是否相同
4、如果相同，那么认为没有冲突，直接更新即可
5、否则认为是过期数据
应用场景
事务之间的数据竞争时比较小的情况
悲观锁
概念
指的是数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度（悲观），因此，在整个数据处理过程中，将数据处于锁定状态，悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）
具体流程
1、在对任意记录进行修改前，先尝试为该记录加上排它锁（exclusive locking）
2、如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定
3、如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁
4、其间如果有其他对该记录做修改或加排它锁的操作，都会等待我们解锁或直接抛出异常
具体场景
悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本环境中
读写锁意向锁
写锁
当前写操作没完成前，会阻塞其他读和写操作（update、insert、delect）
读锁
针对同一份数据，多个读操作可以同时进行而不会相互影响（select）
意向锁
在没有意向锁的情况下，事务T想对表A加排它锁（X锁），就要先检测是否有其他事务对表A或者表A的任意一行加锁，因此就需要对表A的每一行进行检测
在引入意向锁之后，一个事务如果先获得某个行的S锁，就需要先获得表的IS锁；一个事务如果先获得某个行的X锁，就需要先获得表的IX锁，不需要遍历每行进行检测
锁问题
脏读
不可重复读
更新丢失
InnoDB锁的特性
特点：MySQL的行锁是针对索引加的锁，不是针对记录加的锁，虽然是访问不同行的记录，但是使用相同的索引键，还是会出现锁冲突的
1、通过非索引项检索数据，加表锁（准确的说应该是在每个聚集索引后面加上了锁，表现起来就像是一个表锁）
2、当使用索引检索数据时不同事物可以操作不同行数据
行锁的三种算法
Record Lock
概念
记录锁，单个行记录上的锁（注意，record lock锁的是索引，而不是数据本身）
Gap Lock
概念
间隙锁，锁定一个范围，但不包含记录本身
当使用范围条件查询的时候然后更新的时候，而不是等值查询的时候，就会给符合条件的范围索引加锁
普通索引加锁
（举例：num 1,3,5）
1、单行加锁：where num =3 for update，普通索引可能会出现重复值，因此在对单行加锁的时候，要锁住前后区间[1,3)U(3,5]
2、范围加锁：where num >=3 for update，锁住的区间为 [1,3)U(3,+∞]
唯一索引加锁
（举例：num 1,3,5）
1、单行加锁：where num =3 for update由于有唯一性，所以不需要加gap锁
2、范围加锁：where num >=3 for update，锁住的区间为(3,+∞]
Next-Key Lock
概念
临键锁（Record Lock+Gap Lock），锁定一个范围，并锁定记录本身
InnoDB对行的查询都是采用next-key lock
如果索引具有唯一性，就会降级为Record Lock
如何上锁
表锁
隐式上锁（默认，自动加锁自动释放）
select(读锁)
insert、update、delete（写锁）
显示上锁（手动
lock table tableName read
lock table tableName write
解锁（手动）
unlock table tableName #单表
unlock tables #所有表
行锁
隐式上锁（默认，自动加锁自动释放）
select(不自动加锁)
insert、update、delete（自动加排它锁）
显示上锁（手动
select * from user lock in share mode 共享锁
select * from user for update 排它锁
解锁（手动）
提交事务（commit）
回滚事务（rollback）
kill阻塞进程
排查锁的问题
表锁
show open table #查看表锁情况
show status like 'table%' #表锁分析
table_lock_waits #出现竞争等待次数
table_locks_immediate #产生表锁的次数
行锁
show status like 'innodb_row_lock%'
innodb_row_lock_current_waits #当前正在等待锁定的数量
innodb_row_lock_times #从启动到现在锁定的时间总长度
innodb_row_lock_time_avg #每次等待平均所花时间
innodb_row_lock_waits #系统启动到现在总共等待次数
information_schema库
innldb_lock_waits表
innodb_locks表
innodb_trx表
锁优化
1、尽可能让数据的检索通过索引完成
2、尽可能减少索引范围，避免间隙锁
3、控制事务大小，减少锁定资源量和时间长度
4、尽可能降低事务隔离级别
死锁
定义
两个事务或多个事务在同一资源上相互占用，并请求锁定对方占用资源，从而导致死锁
原因
InnoDB获取锁是分布的，不是一次性获取所有的锁，所以会出现死锁，MyISAM是表锁，因此不会出现死锁
解决
查看死锁
show engine innodb status \G
自动检测机制，超时自动回滚代价较小的事务
innodb_lock_wait_timeout默认是50s
等待图（主动检测）
wait for graph
人为解决，kill阻塞进程
show processlist
如何避免
加锁顺序一致，尽可能一次锁定所需要的行
尽量基于主键或者唯一字段（unique key）更新数据
单次操作的数据量不宜过多，涉及的表尽量少
尽量使用相同的条件访问数据，可以避免间隙锁对并发的插入影响
尽量使用索引去访问数据
借助相关工具：pt-deadlock-logger
事务
事务的基本要素
原子性（Atomicity）
事务开始后所有操作，要么全部做完，要么全部不做不可能停滞在中间环节，事务执行过程中出错，会回滚到事务开始前的状态，所有操作就像没有发生一样，也就是说事务是一个不可分割的整体
一致性（Consistency）
事务开始和结束后，数据库的完整性约束没有被破坏，比如A向B转账，不可能A扣了钱，B却没收到
隔离性（Isolation）
同一时间，只允许一个事务请求同一数据，不同事物之间彼此没有任何干扰，比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
持久性（Durability）
事务完成后，事务对数据库的所有更改将被保存到数据库，不能回滚
事务并发问题
脏读
事务A读取了事务B更新的数据，然后事务B回滚操作，那么A读取到的数据就是脏数据
不可重复读
事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据，结果不一致（重点在于修改）
幻读
事务A在进行的过程中，事务B提交了的新增数据，在事务A结束之后就会发现还有一条之前没有出现过的数据，这就是幻读（重点在于新增/删除）
事务隔离级别
读未提交（read-uncommitted）
再读未提交隔离级别下，事务A可以读取事务B修改过但未提交的数据，可能发生脏读、不可重复读和幻读问题
已提交读或不可重复读（read-committed）
在读已提交隔离级别下，事务B只能在事务A修改过并且已经提交后才能读取事务B修改的数据，读已提交隔离级别解决了脏读的问题，但可能发生不可重复读和幻读问题
可重复读（repeatable-read）
在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据，可重复读隔离级别解决了脏读和不可重复读的问题，但可能发生幻读问题
串行化（serialiable）
各种问题（脏读、不可重复读、幻读）都不会发生，通过加锁实现（读锁和写锁）
事务的实现
redo log
概念
redo log叫做重做日志，是用来实现事务的持久性
组成
redo log buffer
缓存中
redo log file
持久化到磁盘
作用
mysql为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓存池)里头，把这个当作缓存来用，然后使用后台线程去做缓冲池和磁盘之间的同步
那么问题来了，如果还没来及同步的时候宕机或者断电了怎么办，还没来得及执行持久化操作，这样会导致丢失部分已提交事务的修改信息
所以引入了redo log来记录已成功提交事务的修改信息，冰球会把redo log持久化到磁盘，系统重启之后在读取redo log恢复更新数据
而binlog在每次事务提交之后，是记录到缓冲池的，然后在异步写道磁盘中去，因此不能保证事务的持久性
redo log和binlog区别
作用不同
redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性，binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制
层次不同
redo log 是InnoDB存储引擎实现的，而binlog是MySQL的服务器层实现的，同时支持InnoDB和其他存储引擎
内容不同
redo log 是物理日志，内容基于磁盘的Page，binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句，基于数据本身或者二者的混合
写入时机不同
binlog在事务提交时写入；redo log的写入时机相对多元
undo log
概念
undo log 叫做回滚日志，用于保障未提交事务的原子性，同时也是隔离性实现的基础
组成
undo log属于逻辑日志，它记录的是sql执行相关的信息，当发生回滚时，InnoDB会根据undo log的内容作与之前相反的工作，对于每个insert 回滚时会执行delete，回滚时会执行delete；对于每个delete，回滚是也会执行insert；对于每个update，回滚时会执行一个相反的update，把数据该回去
作用
InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log，如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子
四大特性的实现
事务的原子性是通过undo log来实现的
事务的持久性是通过redo log来实现的
事务的隔离性是通过（读写锁+MVCC）来实现的
事务的一致性是通过原子性，持久性，隔离性来实现的
MVCC实现机制
MVCC概念
概念：全称Multi-Version Concurrency Control,即多版本并发控制（InnoDB用于实现已提交读和可重复读）
好处：在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
缺点：每行记录都需要额外的空间，需要进行更多的行维护和检查工作
隔离性的实现
（一个事务）写操作对（另一个事务）写操作的影响：锁机制保证隔离性（写写冲突）
（一个事务）写操作对（另一个事务）读操作的影响：MVCC保证隔离性（读写冲突）
当前读和快照读
当前读
像select lock in share mode(共享锁)，select for update;update,insert,delete(排它锁)这些操作都是一种当前读，为什么叫当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提时隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
当前读和快照读与MVCC的关系
MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读，而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
就是在快照读的情况下不需要加锁解决读写冲突
数据库并发场景
读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失
MCVV实现基础
Read View
系统版本号
是一个递增的数字，每开始一个新的事务，系统版本号都会自动递增
事务版本号
事务开始的时候的系统版本号
隐式字段
创建版本号
创建一个数据行的时候，在后面隐式插入一个当前的系统版本号
删除版本号
InnoDB会为删除的每一行保存当前系统的版本号作为删除标识
undo日志
MVCC使用的快照存储在undo日志里面，这个日志通过回滚指针把一个数据行的所有快照连接起来
MVCC实现过程
出发点
当开始一个新的事务的时候，该事务的版本号一定会大于当前所有数据行快照的创建版本号
insert
将当前系统版本号作为数据行快照的创建版本号
delete
将当前系统版本号作为数据行快照的删除版本号
select
原则
每一个事务都要去读自己可以读到的最新的数据行版本
如果这个事务自身有修改了这恶鬼数据行，读自己这个版本即可
实现
记一个没有对数据行进行修改的事务为T
T所要读取的数据行的创建版本号必须小于T的版本号
如果创建版本号是大于等于T的版本号，说明这个数据行快照创建的时间是晚于事务T的创建，事务T是读不到这个版本的
T所要读取的数据行的删除版本号必须大于T的版本号（或者是undefined）
如果删除版本号是小于等于事务T的版本号，说明这个数据行的删除时间是先于事务T的创建，事务T是读不到这个版本的，因为对事务T来说是已经被删除的过期数据
update
相当于先进性delete,后进行insert
先将更新前的数据行快照的删除版本号改为当前的系统版本号
然后更新后的数据行快照的创建版本号改为当前的系统版本号
事务控制语句
事务开始
begin或start transation
事务提交
commit或commit work
回滚
rollback或rollback work
保存点设置
savepoint标识
回滚到保存点
rollback to savepoint标识
删除保存点
release savepoint标识
自动事务
set aotucommit
分布式事务
定义
就是一次大的操作由不同的小操作组成，这些小的操作分布在不同的服务器上，且属于不同的应用，分布式事务需要保证这些小操作要么全部成功，要么全部失败。本质上来说，分布式事务就是为了保证不同数据库的数据一致性
实现
基于XA协议的两阶段提交
参与角色
事务管理者（协调者）：协调参与全局的各个事务，和参与全局事务的所有资源管理器进行通信
资源管理器（参与者）
提供访问事务的方法，通常一个数据库就是一个资源管理器
流程
准备阶段
1、协调者向所有参与者发送事务内容，询问是否可以提交事务，并等待所有参与者答复
2、各参与者执行事务操作，将undo和redo信息记入事务日志中（但不提交事务）
3、如参与者执行成功，给协调者反馈yes，即可以提交，如执行失败，给协调者反馈no，即不可提交
提交阶段
情况1：当所有参与者均反馈yes，提交事务
1、协调者向所有参与者发出正式提交事务的请求（即commit请求）
2、参与者执行commit请求，并解释整个事务期间占用的资源
3、各参与者向协调者反馈ack(应答)完成的消息
4、协调者收到所有参与者反馈的ack消息后，即完成事务提交
情况2：当任何阶段，一个参与者反馈no，中断事务
1、协调者向所有参与者发出回滚请求（即rollback请求）
2、参与者使用阶段1中的undo信息执行回滚操作，并释放整个事务期间占用的资源
3、各参与者向协调者反馈ack完成的消息
4、协调者收到所有参与者反馈的ack消息后，即完成事务中断
存在问题
性能问题
所有参与者在事务提交阶段处于同步阻塞状态，占用系统资源，容易导致性能瓶颈
可靠性问题
如果协调者出现故障，参与者将一直处于锁定状态
数据一致性问题
在阶段2中，如果发生局部网络问题，一部分事务参与者收到了提交消息，另一部分事务参与者没收到提交消息，那么就导致了节点之间数据的不一致
基于XA协议的三阶段提交
流程
准备阶段
1、协调者向所有参与者发出包含事务内容的canCommit请求，询问是否可以提交事务，并等待所有参与者答复
2、参与者收到canCommit请求后，如果认为可以执行事务操作，则反馈yes并进入预备状态，否则反馈no
预提交阶段
情况1：阶段1所有参与者均反馈yes，参与者预执行事务
1、协调者向所有参与者发出preCommit请求，进入准备阶段
2、参与者收到preCommit请求后，执行事务操作，将undo和redo信息记录事务日志中（但不提交事务）
3、各参与者向协调者反馈ack响应或no响应，并等待最终指令
情况2：阶段1任何一个参与者反馈no，或者等待超时后协调者尚无法收到所有参与者的反馈，即中断事务
1、协调者向所有参与者发出abort请求
2、无论收到协调者发出的abort请求，或者在等待协调者请求过程中出现超时时，参与者均会中断事务
提交阶段
情况1：阶段2所有参与者均反馈ack响应，执行真正的事务提交
1、如果协调者处于工作状态，则向所有参与者发出do Commit请求
2、参与者收到do Commit请求后，会正式执行事务提交，并释放整个事务期间占用的资源
3、各参与者向协调者反馈ack完成的消息
4、协调者收到所有参与者反馈的ack消息后，即完成事务提交
情况2：阶段2任何一个参与者反馈no,或者等待超时后协调者无法收到所有参与者的反馈，即中断事务
1、如果协调者处于工作状态，向所有参与者发出abort请求
2、参与者使用阶段1中的undo信息执行回滚操作，并释放整个事务期间占用的资源
3、各参与者向协调者反馈ack完成的消息
4、协调者收到所有参与者反馈的ack消息后，即完成事务中断
进入阶段3后，无论协调者出现问题，或者协调者与参与者网络出现问题，都会导致参与者无法接收到协调者发出的do Commit请求或abort请求，此时，参与者都会在等待超时之后，继续执行事务提交
优缺点
优点
相比二阶段提交，三阶段提交降低了阻塞范围，在等待超时后协调者或参与者会中断事务，避免协调者单点问题，阶段3中协调者出现问题时，参与者会继续提交事务
缺点
数据不一致问题依然存在，当在参与者收到preCommit请求后等待do commit指令时，此时如果协调者请求中断事务，而协调者无法与参与者正常通信，会导致参与者继续提交事务，造成数据不一致
消息事务+最终一致性
子主题 1
备份/复制/分库分表
主从复制
用处好处
实现读写分离
做数据热备份
架构扩展，避免单机性能成为瓶颈
概念
MySQL主从复制是指可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据库的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表
原理
参与线程
主库log dump线程
用来给从库I/O线程传binlog
从库I/O线程
去请求主库的binlog，并写入本地的relay-log(中继文件)文件中
从库SQL线程
读取本地relay log文件中的日志，并解析成sql语句逐一执行
流程
master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中
slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒
备份与恢复
冷备份
概念
冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库，冷备份时将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言，冷备份是最快最安全的方法
优点
1.是非常快速的备份方法（只需拷贝文件）
2.容易归档（简单拷贝即可）
3.容易恢复到某个时间点上（只需将文件再拷贝回去）
4.能与归档方法相结合，做数据库“最新状态”的恢复
5.低度维护，高度安全
缺点
1.单独使用时，只能提供到“某一时间点上”的恢复
2.在实施备份的全过程中，数据库必须要做备份而不能做其他工作。也就是说，在冷备份过程中，数据库必须是关闭的
3.若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢
4.不能按表或者按用户恢复
热备份
概念
热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一边备份的sql语句
优点
1.可以在表空间或数据文件备份，备份时间段
2.备份时数据库仍可使用
3.可达到秒级恢复（恢复到某一时间上）
4.可对几乎所有数据库实体做恢复
5.恢复时快速的，在大多数情况下在数据库仍工作时恢复
缺点
1.不能出错，否则后果严重
2.若热备份不成功，所得结果不可用于时间点的恢复
3.困难于维护，所以要特别仔细小心，不允许“以失败告终”
分库分表
分库分表的目的
分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干个数据库组成，将数据大表拆分成若干个数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的
垂直拆分
垂直分库
根据业务耦合性，将关联度低的不同表存储在不同的数据库
例如电商项目中，商品库、订单库
垂直分表
垂直分表时基于数据库的“列”进行，某个表字段较多，可以新建一张扩展表，将不经常用或者字段长度较大的字段拆分出去到扩展表中
优缺点
优点
解决业务系统层面的耦合，业务清晰
与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈
缺点
部分表无法join，只能通过接口聚合的方式解决，提升了开发的复杂度
分布式事务处理复杂
依然存在单表数据量过大的问题（需要水平切分）
水平拆分
当一个应用难以在细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写，存储性能瓶颈，这时候就需要水平切分了
库内分表
表内数据内在逻辑关系，将同一个表按不同的条件分散到多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小
库内分表只解决了单一表数据量过大问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决
分库分表
表内数据内在逻辑关系，将同一个表按不同的条件分散到多个数据库中，每个表中只包含一部分数据，从而使得单个表的数据量变小
优缺点
优点
不存在单库数据量过大，高并发的性能瓶颈，提升系统稳定性和负载能力
应用端改造较小，不需要拆分业务模块
缺点
跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展难度和维护量较大
引入的问题和解决方案
分布式事务
基于XA协议的两阶段提交
基于XA协议的三阶段提交
基于消息中间件的最终一致性
表连接
将原来的连接分为多个单表连接，然后在用户程序中进行连接
分布式全局ID
UUID
Snowflake雪花算法
SQL语法
语法与执行顺序
查询
建表/更新表/建立索引
插入/删除/更新
函数
收藏
收藏
0 条评论
下一页
