1.MySql基础
数据库三范式
- 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。（属性不可分）
- 第二范式：如果关系模式R满足第一范式，并且R的所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。（非主键属性，完全依赖于主键属性）
- 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.（非主键属性无转递）
Innodb和MyAsm的比较
- 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
- 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
- 外键：InnoDB 支持外键。
- 备份：InnoDB 支持在线热备份。
- 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
- 其它特性：MyISAM 支持压缩表和空间数据索引。
MySql逻辑架构
InnoDB的逻辑存储结构
InnoDB存储引擎的逻辑存储结构和Oracle大致相同，所有数据都被逻辑地存放在一个空间中，我们称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block），1 extent = 64 pages,InnoDB存储引擎的逻辑存储结构大致如图所示。
段：常见的段有数据段、索引段、回滚段，数据段为B+树的叶子节点，索引段为B+树的非索引节点
区：由连续的页组成的空间，在任何情况下每个区的大小都为1MB。一个区中有64页
页：每个页的大小为16kb,各个数据页之间可以组成双向链表。可以通过参数innodb_page_size将页的大小修改为4k,8k,16k。
基础文件
- .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
- .ibd和.ibdata文件：用来存储InnoDB存储引擎的表数据和索引信息
- .myd文件：主要用来存储使用MyISAM存储引擎的表数据信息。
- .myi文件：主要用来存储使用MyISAM存储引擎的表数据文件中任何索引的数据树。
InnoDB的存储结构
InnoDB的表空间
- 系统表空间
- 主要存储MySQL内部的数据字典数据，如information_schema下的数据。
- 用户表空间
- 当开启innodb_file_per_table=1时，数据表从系统表空间独立出来存储在以table_name.ibd命令的数据文件中，结构信息存储在table_name.frm文件中。
- Uodo表空间
- 存储Undo信息，如快照一致读和flashback都是利用undo信息。
- 查询某个表属于哪个表空间
- select * from information_schema.innodb_tablespace where name=’数据库名/表名字’;
段/Segment
- innodb把逻辑上有关联的区/簇归属为一个段。
- 段是表空间的逻辑组成部分，用来存储具有相同意义的数据，如：B+对中的非叶子节点或B+树中的叶子节点。常见的段有数据段、索引段、回滚段等。
区/簇/Extent
- 一个区/簇是物理上连续分配的一段空间,extent又被划分成连续的页，以存储同一逻辑单元的数据（如下面的索引段、数据段）。一个区/簇，默认由64个连续的页（Page）组成，每个页默认大小为16K。
页/page
- 页的大小默认是16KB
- 当page size为4、8、16KB时，对应一个extent的page数量同步变化，以保证extent(区/簇)大小保持1M不变。当page size为32KB或64KB时，extent内的page数量保证不变，extent同步变为2M和4M;
- 每个页都有一个对应的从0开始的编号，这个编号叫做页号。因为表空间的数据文件会被划分成大小相等的页，所以知道页号，再根据文件的初始位置，就可以计算出页在磁盘中的准确地址。
行/row
索引
- 索引的优点
- 大大减少了服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机IO变为顺序IO
B树和B+树
B树
- B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层
B+树
- B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。为什么说B+树查找的效率要比B树更高
- B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
- B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
- B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
- B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。
B+树和红黑树的比较
- B+ 树有更低的树高
- 磁盘访问原理
- 操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。
- 如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。
- 磁盘预读特性
- 为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。
索引类型
- FULLTEXT
- 即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。
- HASH
- 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
- BTREE
- BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。一般为2到4层
- B+Tree对于BTree来说：把非叶子节点冗余一下，提高了范围查找的效率
- B+树中一个节点为一页或页的倍数最为合适。
- 演变历程
- 二叉树
- BST树(二叉查找树):进行排序操作,满足左子树值<=根值<=右子树
- AVL树：
- RTREE
- RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找
索引种类
- 普通索引（INDEX）：仅仅加速查询
- 唯一索引（UNIQUE）：加速查询且列值唯一（可以有null)
- 主键索引（PRIMARY KEY）：加速查询且列值唯一（不可以有null)+表中只有一个
- 每个表都有主键索引，如果不存在，则InnoDB引擎回首先判断表中是否有非空的唯一索引，如果有则将建表时第一个定义的作为主键索引（此处的顺序为定义索引的顺序而不是建表的列的顺序），如果不符合上述规则，则自动创建一个6字节大小的指针作为主键索引
- 组合索引：多列值组合为一个索引，专门用于组合搜索，其效率大于索引合并
- 最左匹配：先查第一个列，在查第二个列，按照顺序查询才能进行组和索引查询，不按顺序的会优化
- 索引下推(ICP)
- 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
- 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
- 例如：SELECT * from user where name like ‘陈%’ and age=20，当有多个请求时，进行最左前缀索引查询，如果age为索引列，会在遍历索引中直接处理age=20的条件，而不用再回表查询一次来判断
- 全文索引（FULLTEXT）：对文本的内容分词进行搜索
聚簇索引和非聚簇索引
聚簇索引
- 按照每个表的主键构造一棵B+树，同时叶子节点中存放的数据即为整张表的行记录数据
- 每个表只能有一个聚集索引，查询优化器倾向于聚集索引，聚集索引的存储在物理上不是连续的，在逻辑上是连续的
- 优点
- 可以把相关的数据保存在一起
- 数据访问更快
- 可以使用覆盖索引查询
- 缺点
- 聚簇索引针对的情景是IO密集型应用，对于内存，聚簇索没有优势
- 插入速度严重依赖于插入顺序，按照主键插入是最快的方式
- 更新聚簇索引列的代价很高，会强制将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
非聚簇索引（辅助索引）
叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以为，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB索引引擎那里可以找到与索引相关的行数据。
索引覆盖：从辅助索引就可以查询到数据，不需要再查询聚集索引中的记录。使用辅助索引来查询获得主键的数据
索引的匹配方式
- 全值匹配：和索引中所有列进行匹配
- select * from people where name = ‘liming’ and age = 18 and sex = ‘1’ 查询三项为组合索引
- 匹配最左前缀：只匹配前面几列
- select * from people where name = ‘liming’ and age = 18
- 匹配列前缀：匹配某一列的值的开头部分
- select * from people where name like ‘liming%’
- 匹配范围值：查找某一范围的数据
- select * from people where age > 18
- 精确匹配某一列并范围匹配另外一列：查询第一列的全部和第二列的部分
- select * from people where name = ‘liming’ and age > 18
- 只访问索引的查询：覆盖索引
- select id from people whre age > 18
索引注意事项
创建索引的注意事项
- 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
- 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
- 值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段
- 字符字段只建前缀索引，使用前缀索引就不能使用索引覆盖，并且无法做Order by和Group by
- 字符字段最好不要做主键
- 不用外键，由程序保证约束
- 尽量不用UNIQUE，由程序保证约束
- 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引
那些列适合作为索引
- 查询频繁的列，在where，group by，order by，on从句中出现的列
- where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
- 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
- 离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高
索引不生效的原因
- 索引列是表示式的一部分，或是函数的一部分
- 隐式类型转换
- 隐式编码转换
- 使用 order by 造成的全表扫描
视图
视图的概念
- 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，他们包含不是数据而是根据需要检索数据的查询。
- 视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据
应用范围
- 重用sql
- 简化复杂的sql操纵，在编写查询后，可以方便的重用它而不必知道其基本查询细节
- 使用表的一部分而不是整个表
- 保护数据，可以属于用户访问表的特定部分的权限，而不是整个表的访问权限
- 更改数据格式和表示视图可返回与底层表的表示和格式不同的数据
视图的优缺点
- 优点：
- 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
- 用户通过简单的查询可以从复杂查询中得到结果。
- 维护数据的独立性，试图可从多个表检索数据
- 对于相同的数据可产生不同的视图
- 缺点
- 性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么就无法更改数据
锁
按模式分类
乐观锁
- 业务实现
1
2-- 如果更新失败则重新尝试
update value = value where version = curversion
悲观锁
- select…for update 注意weher条件要明确指定主键
- 对符合
WHERE条件的行加锁（默认行级锁） - 必须在事务中（
BEGIN或START TRANSACTION）使用，否则锁会立即释放。 - 如果
WHERE条件未命中索引，InnoDB 会升级为 表级锁（全表扫描代价高）。 - 例如库存扣减场景
1
2
3START TRANSACTION;
-- 检查库存，计算新值
COMMIT;
按加锁粒度分类
全局锁
- Flush tables with read lock (FTWRL)
- 让整个数据库处于只读状态，很危险不建议，只要数据库引擎支持事务操作就使用官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
- 如果要全库只读，为什么不使用set global readonly=true的方式？
- 1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
- 2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
表级锁
- 表锁
- lock tables … read/write
- lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
- 元数据锁(meta-data-lock MDL)
- MDL 不需要显式使用，在访问一个表的时候会被自动加上,
- 当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- MDL作用是防止DDL(对表数据的增删改查)和DML(对表结构的增删改查以及新增表)并发的冲突
- 意向锁
- 当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。加意向锁的目的是为了快速判断表里是否有记录被加锁。
行级锁
- 记录锁，锁住的是一条记录。而且记录锁是有S锁和X锁之分的，满足读写互斥，写写互斥
- 间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
- Next-Key Lock 称为临键锁，是 Record Lock+Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
按属性分类
共享锁和排他锁
共享锁（Shared），简写为 S 锁，又称读锁。select …..lock in share mode
排他锁（Exclusive），简写为 X 锁，又称写锁 。sleect … for update
有两个规定
- 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
- 一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。
按状态分类
意向共享锁和意向排他锁
使用意向锁(Intention Locks)可以更容易支持多粒度封锁。
在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。
意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁
规定：
- 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
- 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。
- 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
- 这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）
自增锁
- 完成对自增长值插入的sql语句后立刻释放。表示为当插入一个自增数据后，无论事务是否提交，下次插入的数据均为该自增数据的下一个自增值。
按算法分类
间隙锁（GAP）
- 间隙锁是一个在索引记录之间的间隙上的锁。但不包括记录本身
- 保证某个间隙内的数据在锁定情况下不会发生任何变化。比如mysql默认隔离级别下的可重复读（RR）
- 如果Where条件全部命中，则不会用Gap锁，只会加记录锁。如果where条件部分命中或者全不命中则会加Gap锁。
记录锁(Record Lock)
- 记录锁 是直接锁定索引中的一条具体记录的锁。
- 仅锁定单行，不涉及范围。
临键锁(Next-Key Lock)
- 临键锁 是 记录锁（Record Lock） + 间隙锁（Gap Lock） 的组合，锁定索引记录及其前面的间隙
- 例如：如果表中存在
id=3和id=7的记录，临键锁可能锁定区间(3, 7](左开右闭区间)。 - 目的
- 防止其他事务在锁定范围内插入新记录（避免幻读）。
- 阻止其他事务修改或删除锁定范围内的记录。
- MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。
封锁协议
三级封锁协议
一级封锁协议
事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
二级封锁协议
在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
三级封锁协议
在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
两段锁协议(2PL)
- 一个事务中一旦开始释放锁，就不能再申请新锁了。事务的加锁和解锁严格分为两个阶段，第一阶段加锁，第二阶段解锁。引入2PL是为了保证事务的隔离性，保证并发调度的准确性，多个事务在并发的情况下等同于串行执行。
- 在事务中只有提交(commit)或者回滚(rollback)时才是解锁阶段，其余时间为加锁阶段。
Mysql隐式和显示锁定
- MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。
- InnoDB 也可以使用特定的语句进行显示锁定：
Mysql如何处理死锁
- 等待，直到超时
- 发起死锁检测，主动回滚一条事务，让其他事务继续执行
死锁检测
- 死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。
回滚
- 检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。
如何安全的给小表加字段
- ，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
- MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
- ALTER TABLE tbl_name NOWAIT add column …
- ALTER TABLE tbl_name WAIT N add column …
如何避免发生死锁
- 使用事务，不使用
lock tables。 - 保证没有长事务。
- 操作完之后立即提交事务，特别是在交互式命令行中。
- 如果在用
(SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)，尝试降低隔离级别。 - 修改多个表或者多个行的时候，
将修改的顺序保持一致。 - 创建索引，可以使创建的锁更少。
- 最好不要用
(SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。 - 如果上述都无法解决问题，那么尝试使用
lock tables t1, t2, t3锁多张表
并发一致性问题
丢失修改
丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。
脏读
读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据
不可重复读
不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻影读
幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
不可重复读和幻影读的区别
- 不可重复读是读取同样的数据，前后读取结果不一致，重点是数据的修改，幻影读是同样的条件，检索的结果集不一致，重点是新增或者删除
- 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
并发一致性问题总结
- 产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。
数据库事务
ACID
- 原子性（Atomicity）：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些操作即可。
- 一致性(Consistency):数据库在事务的执行前后都保持着一致性的状态，在一致性的状态下，所有事务对一条数据的读取结果都时相同的。
- 隔离性(Isolation)：一个事务在提交事务前的所有修改，对其他事务来说是不可见的
- 持久性(Durability)：一旦事务提交，则其所作的修改将会永久的保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失。使用重做日志来保证持久性。系统发生崩溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。
- 解释
- 只有满足一致性，事务的执行结果才是正确的。
- 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
- 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
- 事务满足持久化是为了能应对系统崩溃的情况。
隔离级别
未提交读(READ UNCOMMITED)
事务中的修改，即使不提交，对其他事务也是可见的读已提交(READ COMMITED)(默认)
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。可重复读（REPEATABLE READ）
保证在同一个事务中多次读取同一数据的结果是一样的。可串行化（SERIALIZABLE）
强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。隔离级别 脏读 不可重复读 幻读 锁策略 读未提交 (Read Uncommitted) 可能 可能 可能 无锁（或极弱锁） 读已提交 (Read Committed) 不可能 可能 可能 行级锁（短暂持有） + MVCC 可重复读 (Repeatable Read) 不可能 不可能 不可能 临键锁（Next-Key Lock） + MVCC 串行化 (Serializable) 不可能 不可能 不可能 表级锁或严格的行级锁 RR级别下并没有完全解决幻读问题，但通过使用Next-Key Locks（一种Gap Lock和Record Lock的组合锁）的机制，它在大多数情况下可以防止幻读的发生。以下是RR级别下，幻读会发生的情况
- 快照读（非锁定读）：在RR隔离级别下，同一个事务内混合“快照读”和“当前读”，
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15-- 事务 A (RR 隔离级别)
START TRANSACTION;
-- 快照读 (基于 Read View V1), 假设返回记录数: 5
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- 此时事务 B 插入一条 age=25 的新记录并提交
-- COMMIT; (事务 B)
-- 当前读 (读取最新已提交数据, 获取 Next-Key Locks)
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- 这个 SELECT FOR UPDATE 看到了事务 B 新插入的 age=25 的记录！
-- 事务 A 的第二次查询比第一次查询多出了一行 (幻读发生)
COMMIT; -- 事务 A - UPDATE/DELETE 操作“看到”幻影行并修改它们
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21-- 事务 A (RR 隔离级别)
START TRANSACTION;
-- 快照读 (基于 Read View V1), 假设返回记录数: 5
SELECT COUNT(*) FROM users WHERE age BETWEEN 20 AND 30; -- 结果: 5
-- 此时事务 B 插入一条 age=25 的新记录并提交
-- COMMIT; (事务 B)
-- UPDATE 是当前读! 它会看到事务 B 提交的新记录 (age=25)
UPDATE users SET status = 'active' WHERE age BETWEEN 20 AND 30;
-- 这个 UPDATE 语句实际影响了 6 行记录 (包括事务 B 插入的那一行)
-- 再次快照读 (基于 Read View V1), 仍然看不到新行
SELECT COUNT(*) FROM users WHERE age BETWEEN 20 AND 30; -- 结果: 5 (快照读不变)
-- 但是! 执行当前读:
SELECT COUNT(*) FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE; -- 结果: 6 (看到新行)
-- 或者提交后看数据，会发现 6 行被更新了
COMMIT; -- 事务 AUPDATE和DELETE语句在 RR 级别下也是当前读。它们在查找要修改的行时，会读取数据库的最新已提交状态（并获取 Next-Key Locks）。因此，它们能“看到”并修改在事务开始之后、但在UPDATE/DELETE执行之前由其他已提交事务插入的“幻影行”。
- 快照读（非锁定读）：在RR隔离级别下，同一个事务内混合“快照读”和“当前读”，
MVCC(多版本并发控制)
- 多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读的可重复读以及快照读的幻读问题这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。
- 读不加锁，读写不冲突。MVCC 在不加锁的情况下解决了脏读、不可重复读
MVCC基础概念
- 版本号
- 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
- 事务版本号 TRX_ID ：事务开始时的系统版本号。
- 隐藏的列
- MVCC在每行记录后面都保存着两个隐藏的列，
- DB_TRX_ID：最后修改改行的事务ID
- DB_ROLL_PTR(roll_pointer)：指向旧版本数据的指针。
- 每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到
undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息
- 每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到
- MVCC在每行记录后面都保存着两个隐藏的列，
- undo日志
MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。
当前读和快照读
快照读
- MVCC 中的 SELECT 操作是快照中的数据，不需要进行加锁操作。
- 每一次修改数据都会在undo-log 中存有快照记录，快照读就是读取某一个版本的快照
当前读
- 当前读是给读操作加上共享锁、排它锁，DML 操作加上排它锁，读取记录的最新版本。
- 快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；
- MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）隐式的加上排他锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。
特性 当前读 快照读 数据版本 读取最新已提交数据 读取历史快照数据（基于事务/语句时间点） 加锁 是（排他锁或共享锁） 否 并发性能 低（锁竞争可能阻塞其他事务） 高（无锁，读写并行） 适用操作 FOR UPDATE、UPDATE、DELETE、INSERT普通 SELECT隔离级别依赖 所有级别（行为一致） 读已提交和可重复读（行为不同） 触发条件 显式加锁的 SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE。
任何数据修改语句（UPDATE/DELETE/INSERT）在执行前会隐式触发当前读。所有普通 SELECT语句默认使用快照读。
事务首次读取数据时生成快照，后续读取沿用该快照。
MVCC适用的隔离级别
操作类型 RC 隔离级别 RR 隔离级别 普通 SELECT动态快照读（每次生成新 Read View）固定快照读（复用首次 Read View）写操作/加锁读 当前读（读取最新数据） 当前读（读取最新数据，加间隙锁防幻读）
read-view
- 进行快照读时，会产生read-view,RC在每一次进行普通SELECT操作前都会生成一个ReadView，而RR只在第一次进行普通SELECT操作前生成一个ReadView，数据的可重复读其实就是read-view的重复使用
- read-view有四个重要变量
m_ids：活跃事务id列表，当前系统中所有活跃的（也就是没提交的）事务的事务id列表。min_trx_id：m_ids 中最小的事务id。max_trx_id：生成 ReadView 时，系统应该分配给下一个事务的id（注意不是 m_ids 中最大的事务id），也就是m_ids 中的最大事务id + 1 。creator_trx_id：生成该 read-view 的事务的事务id。
- trx_id是版本链中的各个id, 遍历版本链，通过比较trx_id和m_ids中的数的关系来确认当前可见的版本是哪个
- trx_id == creator_trx_id，版本链中的这个版本是当前事务修改的，所以该快照记录对当前事务可见。
- trx_id<min_trx_id，表示此版本是已经提交的事务生成的，由于事务已经提交所以数据是可见的。
- trx_id>max_trx_id，表示此版本是由将来启动的事务生成的，是肯定不可见的。
- min_trx_id<=trx_id<=max_trx_id会存在俩种情况
- trx_id 在数组中，表示此版本是由还没提交的事务生成的，不可见，但是当前自己的事务是可见的。
- row 的 trx_id 不在数组中，表明是提交的事务生成了该版本，可见。
WAL
- write-Ahead-logging
- MySql 在执行写操作时并不是立刻更新到磁盘上，而是先记录在日志中，之后在合适的时间更新到磁盘上
日志
错误日志(err log)
- 记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息。
二进制日志(bin log)
- 默认是关闭的，需要通过配置：log-bin=mysql-bin进行开启。其中mysql-bin是binlog日志文件的basename，binlog日志文件的名称：mysql-bin-000001.log
- binlog用于实现mysql主从复制以及数据恢复。
通用查询日志(general query log)
- 默认情况下通用查询日志是关闭的
- 由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。
慢查询日志(slow query log)
- 默认是关闭的。需要通过设置：slow_query_log=ON进行开启。
- 记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句
事务日志
redolog和undolog
redo log是重做日志，提供前滚操作。有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失
- redo是固定大小的，redo log是innodb层产生的，只记录该存储引擎中表的修改
- 在概念上，innodb通过***force log at commit***机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。
- 为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中
undo log是回滚日志，提供回滚操作。
- 也是innodb层产生的。
- 核心作用
- 事务回滚：
记录事务修改前的旧值，用于回滚未提交的事务（保证原子性）。 - MVCC支持：
为其他事务提供旧版本数据，实现非锁定读（读不阻塞写，写不阻塞读）。
- 事务回滚：
特性 redo log undo log 核心目的 保证事务的持久性（Durability） 支持事务的原子性（Atomicity）和多版本并发控制（MVCC） 日志类型 物理日志（记录数据页的修改） 逻辑日志（记录逆向操作，如SQL的反向逻辑） 写入时机 事务进行中实时写入 事务修改数据前记录旧值 生命周期 事务提交后即可覆盖（循环写入） 保留到没有事务依赖旧版本数据（可能长期存在） 恢复作用 崩溃恢复时重放未落盘的数据（前滚） 回滚未提交事务，或提供MVCC的旧版本数据 存储方式 顺序写入（高效） 存储在回滚段（undo segments）中
分区表
分区表的底层原理
分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。
分区表的操作按照以下的操作逻辑进行：
- select查询
当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据 - insert操作*
当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表 - delete操作
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作 - update操作
当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作
有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。 - 分区类型
1.MySql基础
https://x-leonidas.github.io/2022/02/01/05数据库/05-1关系型数据库/1.MySql基础/