mysql技术内幕-innodb
2021-06-30 22:00:22 51 举报AI智能生成
看书记录的笔记
作者其他创作
大纲/内容
Mysql
索引和算法
innodb的索引
Cardinality
表示索引中不重复记录数量的预付值，尽可能接近1，如果非常小，说明该索引列中的重复数据很多，而对取值范围很小的列设置索引的意义不大（如对性别列设置索引）
为了监控一个列是否适合建立索引，需要参考 cardinality 的值，而这个值需要进行统计才能得出，但是如果对所有的数据都统计则非常消耗性能，所以 数据库对 cardinality 的统计都是通过 采样 完成的
在 insert/update 时统计
表中1/16的数据发生变化
stat_modified_counter>2000000000
B+树索引
B+树的插入和删除
B+树索引发分裂
索引的管理
查看索引信息show index
cardinality非常关键的值，表示索引中唯一值的数据的估计值。应该尽可能的接近1，如果非常小，可以考虑去掉该索引
alter table add/drop index
可以只对一个列的开头部分数据进行索引，alter table add key idx_b (b((100))
聚集索引
按照没涨表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页非叶子节点的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录
辅助索引
叶子节点存放 索引的键值对，不包含行记录的全部数据，但包含一个 告诉 innodb 引擎哪里可以找到与索引相对应行数据的 书签（bookmark）
当通过辅助索引来寻找数据时 ，Innodb 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录
联合索引
对表上的多个列进行索引
覆盖索引
Innodb存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录而不需要查找聚集索引中的记录
覆盖索引不包含整行的数据，其大小远小于聚集索引，所以可以减少IO次数
优化器不使用指定索引的情况
一般发生再 sql 指定查询条件包含辅助索引但是实际上SQL执行器却没通过辅助索引查找数据
执行EXplain 命令进行 sql分析时 ，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，直接进行全表的扫描来得到数据
多反生于范围查找、Join 连接操作
当SQL查找的字段刚好是索引列，或者查找全部的字段但通过索引查找的数量只是少数时，优化器会选择使用索引列查找，这种情况和索引覆盖类似
但是如果查找的字段不全部在索引中，而且方位的数据占比较大（20%）时，会考虑放弃使用索引而使用表扫描（即扫描全表数据）这是因为通过辅助索引直接得到的数据只包括本身值和指向真实数据页的书签，想要获取其他字段还需要通过读取数据页才能得到，但是因为辅助页获取的数据页不是连续的，这样就造成读取磁盘也是离散的读。所以如果访问到数据占据整个表较大一部分时，优化器会选择聚集索引查找数据（全表扫描就是通过扫描聚集索引叶子节点），因为顺序读的速度远远大于离散读
如果使用的是固态硬盘，因为不需要寻道，所以离散读的性能比较好，可以通过 force index() 强制使用某个索引
索引提示
正常情况下SQL优化器会选择合适的索引，但是DBA可以根据经验直接指定索引来完成查询
MRR Multi-Range Read优化
使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找
减少缓冲池中页被替换的次数
批量处理对键值的查询操作
工作方式
将查询得到的辅助索引键值存放与一个缓存中，这时缓冲中的数据是根据辅助索引键值对排序的
将缓存中的键值根据RowId进行排序
根据Rowid的排序顺序来访问实际的数据文件
ICPIndex condition pushdown优化
没优化前，当进行索引查询时，首先根据索引来查找记录，再根据where条件来过滤记录，这样会产生大量的记录页加载
ICP会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取，从而提高数据库的整体性能
支持range/ref/eq_ref/ref_or_null类型的查询
全文索引
B+索引虽然支持对某列的前缀进行索引，但是对于查找一个博客中包含单词 xxx 的文章是无法满足的
全文检索是将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术
Innodb从 1.2.x版本开始，已经支持全文检索，还支持了其他特性
全文检索通过 倒排索引 实现
倒排索引需要将 word 放到一张表中，这张表称为 auxiliary table 辅助表
为了提供检索性能，辅助表可能有多个，没张表根据word 的 latin编码进行分区
引擎根据 文档分词 将分词结果放入到FTS Index Cache(全文检索索引缓存)中，然后再根据情况刷新到 辅助表
在一个辅助表中存储单词与单词自身在一个或多个文档所在位置之间的映射
inverted file idex 其表现为{单词，[单词所在的文档ID数组] }
Innodb采用 full inverter index 的方式
innodb全文检索的限制
每张表只能有一个全文检索的索引
由多列组合而成的全文检索的索引列必须使用相同的字符集和排序规则
不支持没有单词界定符的语言，如中文、日语、韩语等
哈希索引（自适应哈希索引）
直接寻址数组大小受限
哈希表、哈希碰撞
将关键字转换为自然数，通过除法散列、乘法散列或者全域散列，数据库一般使用除法散列
链接法
哈希索引只适合等值查询，对于范围查询是失效的
innodb不支持直接设置哈希索引，引擎会根据数据访问情况，自己控制建立哈希索引，称为自适应哈希索引
表
索引组织表
表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表
Innodb 逻辑存储结构
所有的数据都被逻辑地存放在一个空间，称之为表空间，表空间由段、区、页组成
表空间
Innodb所有的数据都放在表空间，默认所有数据存在一个共享表空间，可以通过 innodb_file_per_table 将没张表内的数据放入一个单独的表空间
共享表空间
回滚信息、插入缓冲索引页、系统事务信息、二次写缓存等
区
由连续的页组成的空间，大小为1mb
页
最小的磁盘管理单位，默认大小16k，通过 innodb_page_size 设置
分类
数据页
undo页
系统页
事务数据页
插入缓冲位图页
插入缓冲空闲列表页
未压缩的二进制大对象页
压缩的二进制大对象页
存储结构
file header 文件头
page header 页头
infimun / Supremum records
user records 用户空间，即行记录
free spack 空闲空间
page directory 页目录
file trailer 文件结尾信息
行
innodb是面向列的，所以数据是按行进行存放的，每个页最多允许存放 16KB/ 2~200行的记录
一个数据行最多支持1024列，就是一个表最多有1024个字段
行记录格式
Compact
一个页存放的行数据越多，性能越高
Null 值除了占用标记位，实际存储不占有任何空间
Redundant
compressed/dynamic
zlib算法压缩
mysql 中对varchar 的最大长度是65532，但是因为需要保留字段记录当前 varchar 的实际长度是多少需要占用两个字节，所以实际上是不能存满的
varchar 最长长度 65532不是针对单个字段，而是一个表字段的所有总和
一个页是 16K，最多存储16384字节，所以对于超长的varhcar 字段，当发生行溢出时，数据放在页类型为uncompress blob页中
约束
约束是为了保证数据完整性
Enum 和 set 约束
触发器约束
外键约束
视图
虚表，由一个SQL查询来定义，可以当做表使用，视图中的数据没有实际的物理存储
根据情况可以对视图进行更新，实际是更新到真实的表中
表分区
分区的过程是将一个表或者索引分解为多个更小、更可管理的部分。从逻辑上讲只有一个表，但是在物理上可能由数十个物理分区组成，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理
例如可以对每个分区做单独的统计，也可以把各个去的统计再合并作为总表统计
mysql 是水平分区，分区方式是局部分区索引，一个分区中即存放了数据又存放了索引
对应的全局分区是数据放在各个分区，但是所有数据的索引放在一个对象中
MYSQL根据 分区列 应用下面的分区类型实现分区，如果表中存在主键或者唯一索引，那么分区列必须是其中的一个组成部分；如果未指定主键或者唯一索引，那么可以指定任何一个列为分区列；包含NULL值的列也可以
分区修建/partition pruning 当查询条件满足分区数据要求时 ，sql只会搜索指定的分区，而不会搜索所有分区
mysql分区中，对 null 值的处理是总是视为任何一个小于非null值
分区类型
Range分区行数据基于属于一个给定连续区间的列值被放入分区
主要用于区分日期列的分区
List分区和Range分区类似，只是List分区面向的是离散的值
与range分区相似，但是分区列的值是离散的
HASH分区根据用户自定义的表达式的返回值来进行分区
将数据均匀的分布到预先定义的各个分区中，不需要像range/list指定分区列的集合或范围用户将要进行哈希分区的列值指定一个列值或者表达式，以及指定被分区的表将要被分隔成的分区数量
哈希分区中， partition不需要指定分区名，但是 partition {num} 表示 分区的数量
create table t_hash(a int b datetime)engine = innodb partition by hash (year(b))partition 4;
KEY分区根据Mysql数据库提供的哈希函数来进行分区
与HASH分区相似，但是不需要用户指定hash 函数，而是使用mysql提供的函数分区
create table t_key(a int b datetime ) engine = innodbpartition by key(b)partition 4;
Columns分区
前面四种的分区的条件是：数据必须是整数，如果不是整数，必须通过函数将其转化为整数。
从MYSQL5.5版本开支持 columns 分区，可以直接使用非整形的数据进行分区，分区根据类型直接比较而得。
子分区
在分区的基础上在进行分区，也称为复合分区；运行在 range 和 list 分区基础上再进行 hash key 分区
每个子分区的数量必须相同
要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有的子分区
每个子分区必须有一个唯一的名字
可以通过 data directory = '/disk0/data' 来将一个表的分区文件分配到多个磁盘中，可以提升性能
分区和性能
OLAP适合分区，因为在线分区一般都会处理一段维度内的数据
对于OLTP则需要根据实际情况处理，因为在线事务查询条件比较复杂，如果需要跨多个分区查找数据，会需要更多的IO
文件
参数文件
show variables like '% %'
日志文件
错误日志
对启动、运行、关闭过程进行处理
慢查询日志/slow log
记录运行时间超过阈值的所有SQL
long_query_time，默认10
mysqldumpslow
查询日志
记录所有对mysql数据库的请求信息，无论这些请求是否得到正确的执行
二进制日志
记录对数据库执行更改的操作，但是不包括select 和 show 这类操作
二进制日志 会先写到buffer中，如果缓冲溢出大小，mysql会吧缓冲中的日志写入一个临时文件中
binlog_format 参数
statement
记录逻辑SQL语句
row
记录表的行的更改情况
mixed
默认采用 statement，但是会根据实际情况调整
作用
恢复
复制，通过复制二进制日志文件是一台远程的从数据库和一个 master 服务器进行实时同步
审计
通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击
socket文件
本地连接mysql可以采用 unix 域套接字方式，这种方式需要一个套接字文件
pid文件
记录进程ID的文件
mysql 表结构文件
表结构定义，视图定义
存储引擎文件
表空间文件
重做日志文件
与二进制日志的区别是，二进制文件记录所有的有关日志记录，包括innodb、myisam、heap 等，而innodb存储引擎的重做日志只记录有关改引擎本身的事务日志
InnoDB存储引擎
体系架构
后台线程
多线程模型，不同的后台线程处理不同的任务
Master Thread
核心线程，负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、Undo页的回收
IO Thread
InnoDB 使用AIO处理IO请求，提高数据库性能，IO Thread负责这些请求的回调
write/read/insert buffer/log io thread
Purge Thread
事务被提交后，其使用的undolog可能不再需要，需要 purge thread来回收已经使用并分配的undo页
Page Cleaner Thread
将之前版本中脏页的刷新操作都放入到单独的线程中来完成
减轻原Master Thrad 的工作及对于用户查询线程的阻塞，进一步提供InnoDB存储引擎的性能
内存
InnoDB是基于磁盘存储的，但磁盘读取速度慢，所以使用缓冲池技术来提高数据库的整体性能
缓冲池
引擎读取数据不是直接读取硬盘，而是通过一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响
读缓存，读取数据页时，先判断该页是否在缓冲区中，如果缓存命中则返回；如果不存在，那么先从磁盘读取该页到缓存池（FIX过程），再返回数据
写缓存，所有的修改操作，都先修改缓存池的值，再通过某种机制刷新到硬盘
checkpoint
缓冲池数据页类型
索引页
UNDO页
插入缓冲
自适应哈希索引
innodb存储的锁信息
数据字典信息
多缓冲池实例，减少数据库内部竞争，增加数据库的并发能力
innodb_buffer_pool_instances
内存管理，淘汰机制
LRU List、Free List、Flush Fist
Lru
最近最少使用淘汰，最频繁使用的放列表前端，最少使用的放尾端，缓冲池满时先释放尾端的页
mindpoint
新增的页，不是直接放到前端，而是放到 minpoint 的位置上
避免全盘扫描或者某个不常用的数据的偶尔加载，插入到队列前会形成误杀
重做日志缓冲
InnoDB首先将重做日志放入到这个缓冲区，然后按一定的频率将其刷新到重做文件
innodb_log_buffer_size
刷新情况
Master Thread 每一秒将重做日志缓冲刷新到重做日志文件
每个事务提交时会将重做日志缓冲刷新到重做日志文件
当重做日志缓冲池剩余空间小于1/2时
额外的内存池
数据结构本身的内存进行分配时 ，需要从该区域分配
页面操作先在内存缓冲区，再刷新到磁盘，如果刷新磁盘时发生的宕机，那么数据将丢失。为了解决这个文件，当前事务数据库普遍使用 write ahead log策略，即事务提交时，先写重做日志，再修改页。这样即使宕机，也可以通过重做日志来完成数据的恢复。但是重做日志没有redis的redo功能，对于运行时间较长或者提交较大的重做日志恢复是非常耗时的，所以需要 CheckPoint 解决
Checkpoint 解决的问题
缩短数据库的恢复时间
数据库只需要到checkpoint后的日志进行恢复
缓冲池不够用时 ，将脏页刷新到磁盘
重做日志不可用时，刷新脏页
Master Thread 工作方式
早期版本的循环
主循环
每秒一次的操作
日志缓冲刷新的磁盘，即使这个事务未提交（总是）
合并插入缓冲（可能）
至多刷新100个Innodb的缓冲池中的脏页到磁盘(可能)
如果当前没有用户活动，则切换到backgroud loop(可能)
每十秒一次的操作
刷新 100 个脏页到磁盘 可能
合并至多5个插入缓冲 总是
将日志缓冲刷新到磁盘 总是
删除无用的 undo 页 总是
full purge 清理无用的行
刷新100 个或者 10个脏页到磁盘 总是
10+1 而不是 9+1的操作
后台循环
合并20个插入缓冲 总是
跳回主循环 总是
不断刷新 100 个页直到符合条件 可能（跳转到 flush loop）
刷新循环
不断刷新 100 个页直到符合条件
跳到 suspend_ loop
暂停循环
没什么事 ，就挂起来
更新后的循环
随着磁盘IO的提升，硬编码的方式固定 主循环的 各种数量可能得不到最大的性能
在合并插入缓冲时，合并的数量未innodb_io_capacity值的5%
从缓冲区刷新脏页时， 刷新脏页的数量为 innodb_io_capacity
自适应刷新，动态调节每秒刷新脏页的数量
从Master Thread 线程分离出一个单独的 page cleaner thread 用于脏页的刷新操作，提高系统并发性
InnoDB的关键特性
插入缓冲 insert buffer
对于主键顺序插入的数据，插入速度很快，因为数据页的存放是按照主键顺序存放的。但是对于非聚集的且不是唯一的索引，数据的插入不是连续的，所以需要离散的访问非聚集索引页，随机读取的存在会导致插入操作性能下降
mysql 的插入缓冲，在非聚集索引的插入或更新时，不直接插入到索引页，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；如果不在，不会去读数据，而是先放入到一个insert buff对象中，然后再以一定的频率和情况进行 insert buffer 和 辅助索引页子节点合并操作，这是通常能将多个插入合并到一个操作中，这就大大提高了对于非聚集索引插入的性能
使用插入缓存的条件
索引是辅助索引
索引不是唯一的
因为在插入时，数据库并不去查找插入记录的唯一性，否则就需要离散的读取数据，这使 insert buffer 失去了意义
风险点，如果数据库宕机时还有大量的缓存没合并到实际的索引中去，恢复这些数据可能需要很长的时间
change buffer ，insert buffer的升级，对 insert/delete/update 操作都进行缓存
内部实现
两次写 double write
提高innodb的数据页可靠性
在引擎写入磁盘时，如写入16K，但是发生了宕机，实际上只写入了4k，就会发生 部分写失效 ，导致数据丢失的情况
部分写失效不能通过重做日志恢复，因为重做日志记录的是对也的物理操作，如偏移量800，写‘aaa’记录，但是以为部分写导致这个也本身损坏了，所以重做也是没有意义的
因此需要有一个也的副本，但是写入失效发生时，先通过也的副本来还原该页，在进行重做，这就是doublewrite
过程
doublewrite 有两部分组成，一部分是内存中的doublewrite buffer，大小为2M，另一部分是物理磁盘上共享表空间中连续的 128页，大小也是2M。在对脏页进行刷新时 ，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 doublewrite buffer，之后分两次每次一1M顺序写入共享表空间的物理硬盘，然后马上调用fsync刷盘。在完成了共享表空间的写入后，再将doublewrite buffer 中的页写入到真实的表空间文件。
如果写入磁盘的过程发生崩溃，在恢复过程汇总，引擎会先从共享表空间中的doublewrite中找到该页的部分，将其复制到表空间文件，再应用重做日志。
如果是从服务器，可以通过 skip_innodb_doublewrite 关闭 doublewrite来提升性能，但是主服务器必需开启doublewrite功能
自适应哈希索引 adaptive hash index
InnoDB监控对表上索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引AHI
AHI要求对这个页的连续访问模式必须是一样的
异步IO async io
为了提高磁盘操作性能，当前数据库都采用异步IO，异步IO可以发出一个请求后无需等待立即发起另一个请求，当所有IO请求发送完毕后，等待所有IO操作的完成。
AIO的另一个优势是可以进行IO Merge操作
刷新邻接页 flush neighbor page
当刷新一个脏页时，Innodb存储引擎会 检测该页所在区的所有页，如果是脏页，那么一起进行刷新
启动、关闭和恢复
innodb_fast_shutdown参数
0
表示mysql数据库关闭时，innodb需要完成所有的full purge 和 merge insert buffer，并将所有的脏页刷新会磁盘。耗时长
1
默认值，表示不需要完成 full purge 和 merge insert buffer 操作，但是在缓冲池中的一些数据还是会刷新会磁盘
2
表示不完成 full purge 和 merge insert buffer 操作，也不刷新脏页，而是将日志都写入日志。这样不会有任何的事务丢失，但是下次数据库启动时，需要进行恢复操作
Mysql体系结构和存储引擎
定义数据库和实例
数据库
物理操作系统文件、内存数据或者其他形式文件类型的集合
实例
Mysql数据库由后台线程以及一个内存共享区组成
Mysql 体系结构
连接池组件
管理服务和工具组件
SQL接口组件
查询分析器组件
优化器组件
缓冲（Cache）组件
插件式存储引擎
物理文件
mysql存储引擎
Innodb
支持事务，设计目标主要面向在线事务处理（OLTP）的应用
行锁设计、支持外键，默认读取操作不会产生锁。mysql 5.5.8版本开始的默认存储引擎
通过多版本并发控制（MVCC）来获得高并发，并实现了SQL标准的4种隔离级别。
使用next-keylocking的策略来避免幻读
提供插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能
才用聚集的方式，每张表的存储都是按主键顺序存放，如果没有显示指定主键，Innodb会为每一行生成一个6字节的rowid为主键
MyISAM
不支持事务、表锁设计，支持全文索引。
缓冲池只缓存索引文件，而不索引数据文件
由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件
mysql连接
TCP/IP
client
server
命名管道和共享内存
Unix域套接字
名词解释
脏页
为了提供新能，mysql数据会先写入内存缓冲池，所以会存在一部分数据在 内存 和磁盘 不一致，这部分数据就是脏页
脏页什么时候刷入到磁盘
redo log 写满时
内存不够时，需要将一部分缓存数据页淘汰掉，如果淘汰的页时脏页，那么先将脏页写入到磁盘
mysql空闲时间
mysql 正常关闭
主键索引
非聚集索引
其他索引
OLTP在线事务处理
OLAP在线分析处理
幻读
指同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行
脏读
值A事务读取到另B事务未提交的数据，然后A保存后B回滚了，导致数据不一致的问题违反了事务的隔离性，当前事务能够看到其他事务的结果
ACID
原子性
事务内的一一系列操作要嘛都成功，要嘛都不执行
一致性
隔离性
每个读写事务的对象对其他事务的操作能相互分离，即该事务提交前对其他事务都不可见，通常使用锁来实现
持久性
事务一旦提交 ，其结果就是永久性的
QPS
Question per second 每秒请求数
TPS
Transaction per second
回表 和 索引覆盖
聚集索引即存储了索引列，也保存了数据本身；非聚集索引节点只包含索引列和主键，如果查询语句需要的字段刚好是索引列包含的，那么就叫 索引覆盖 ，否则需要通过主键去聚集索引再查找一次，就叫 回表
锁
用于管理对共享资源的并发访问，提供数据的完整性和一致性
lock 与 latch
latch 一般称为轻量级锁，其锁定的时间必须非常短，否则其应用的性能会非常差
分为mutex互斥量和relock读写锁，
目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行
lock对象仅在事务commit或者rollbakc后释放，存在死锁机制
Innodb存储引擎中的锁
select * from information_schema.Innodb_lock_waits
锁类型
共享锁 S Lock
允许多个事务读同一行数据
排他锁 X Lock
只允许一个事务读取和修改数据
意向锁 Intention Lock
将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁
意向锁是一个树状结构锁，对下层对象加锁，需要对上层对象上锁
意向共享锁 IS Lock事务想要获得一张表中某几行的共享锁
意向排他锁 IX Lock事务想要获得一张表中某几行的排他锁
一致性非锁定读
通过行多版本控制的方式来读取当前执行时间数据库中行的数据
主要用于隔离级别为 read Committed 和 Repeatable read 的场景，读已提交的意思就是，同时存在两个事务操作同一条数据，一个读一个写，但是对于读事务来说是不应该读到另一个事务还没有提交的数据的，所以可以 通过保留多个版本的数据快照 实现，称为 多版本并发控制
对于 Read Committed事务隔离下，非一致性读总是读取被锁定行的最新一份快照数据
对于Repeatable Read 的隔离级别下，总是读取事务开始时的行数据版本
保留多版本数据通过 undo 段完成，读取快照数据不需要加锁，因为不会对历史数据进行修改操纵
一致性锁定读
默认情况下，事务的隔离级别是 Repeatable read 模式，使用的是一致性非锁定读再某些情况下，用户需要显示的对数据库读取操作进行加锁一保证数据逻辑的一致性，这要求数据库支持加锁语句，即使是对于select的只读操作。
select ... for update / select ... lock in share mode
自增长与锁
auto-inc locking再早起版本，表的自增长主键的获取需要加锁操作，这样在批量插入时会阻塞其他事务的插入操作
mysql 5.1.22版本开始，innodb提供了一个轻量级互斥量的自增长实现机制。
innodb_autoinc_lock_mode mysql 引擎会根据配置和实际插入情况，确定获取增长主键使用表锁还是轻量级锁
外键和锁
对于外键的插入和更新，首先需要查询父表中的记录，并且会对父表加入一个 S 锁，防止因为事务的原因导致数据的约束被破坏
外键的插入和更新，总是需要查询父表记录的，假设不采用加锁的方式读取父表数据，此时有两个事务，一个修改父表，一个插入外键，插入外键的线程读取到了父表事务中插入的数据，然后在子表保存了数据，但是此时父表回滚了，那么子表的外键就实际在父表就不存在了
锁的算法需要明确一点的是，锁除了加在数据行上外，还可以加在索引上
锁的三种算法
record lock 单个行记录上的锁
总是锁住索引记录，就是会锁定主键
当事务隔离级别是 READ Committed时，仅采用 record lock
如果仅采用 record lock，那么别的事务仍可以插入新的数据，对于范围查询，可能两次查询的数据条数会不一样
Gap lock间隙锁，锁定一个范围，但是不包含记录本身
用于解决 Phantom Problem 问题
可以显示关闭间隙锁将事务级别设置为 read committed将参数 innodb_locks_unsafe_for_binlog设置为1
Next-key lockGap lock + Record Lock 锁定一个范围，并且锁定记录本身
innodb对于行的查询都是采用这种算法，当查询的索引含有唯一索引时，会降级为record lock
Repeatable Read 级别下的加锁方式
解决 phantom problem
采用 next-key locking 算法
锁问题
脏数据事务对缓冲池中行的修改，但是还没有被提交
一个事务内的脏读就是读到了别的事务未提交的数据，这样是违反了 隔离性 要求
设置隔离级别至少是 read committed 可以避免脏读
不可重复读
事务A在多次读取一个数据集合时，如果此时事务B对同一数据集合进行了修改并提交，那么事务A虽然能得到同一数据集合，但是数据前后值却不一样
脏读是读到了未提交的数据，可能有回滚的风险；不可重复读是读到了同一数据行，但是两次得到的同一行数据的字段却不一样
不可重复读违反了事务的 一致性 要求
innodb 的默认隔离级别是read repeatable 采用 next-key lock算法，避免不可重复读现象
丢失更新
一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致
事务T2将行记录r更新为V2，不提交
事务T1提交
事务T2提交
事务T1数据丢失
单纯的数据库操作不很难发生这样的问题，因为都会对行加锁，T2会被阻塞
这种操作主要反生在用户程序，因为需要对取到的值进行计算再更新会数据库中
解决方法是
采用 串行化 的隔离级别，在读取数据的时候加上 X锁
用户代码层面使用锁，依然是串行化
阻塞
一个事务的锁需要等待其他事务的锁释放
通过 innodb_lock_wait_timeout 控制等待时间，默认是50s
innodb默认不对阻塞超时的事务进行回滚操作
死锁
两个以上的事务在执行过程中，因争夺锁资源而造成的一种相互等待的现象
死锁的解决方法
检查到死锁马上放弃等待，直接回滚
给死锁设置超时，超时到达后回滚但是简单的按照超时就回滚的做法是不公平的，因为可能有的事务需要回滚的信息比另一个多
wait-for graph（等待图）
innodb采用的方式，更为主动的死锁检测方法
数据库保存两个信息
锁的信息链表
事务的等待链表
引擎根据锁的信息链表检测是否存在死锁，然后选择回滚 undo 量最小的事务
锁升级
锁是稀有的资源，系统在适合的时候会自动地将行、键或者分页锁升级为更粗粒度的表级锁
细颗粒度的锁可以运行更大的并发
innodb不存在锁升级的问题，因为其不是根据每个记录来产生的锁，而是根据每个事务访问的每个页来对锁进行管理的，采用的是位图的方式，因此不管一个事务锁住页一个记录还是多个记录，其开销通常都是一致的
事务
扁平事务
要嘛都成功，要嘛都回滚
带有保存点的扁平事务
可以在操作过程中设置保存点，如同一个数组一样
可以回滚到事务中某个保存点的状态，但更像数组一分为二将保存点的后半部分更改回滚，只提交前半部分数据
需要整个事务结果后才释放锁
链事务
保存点模式的变种
提交一个事务时，释放不需要的数据对象，将必要的上下文传给下一个要开始的事务
保存点模式可以回滚到任意保存点，但是链事务因为每一个链节点事务不是关联的，所以只能回滚当前节点的事务
链事务在执行commit后即释放当前事务所持有的锁
嵌套事务
嵌套事务是一个树状事务一个父事务可以拥有多个子事务，子事务也可以有自己的子事务子事务允许提交和回滚，但是他的提交操作不会马上生效，直到其父事务提交任一父事务的回滚都会将其子事务回滚，因此子事务保留了 A C I特性，不具有D特性
分布式事务
分布式环境下的扁平事务
事务的实现
通过 redo log 和 undo log来完成
redo
用来保证事务的原子性和持久性，但基本是顺序写，不需要读
redo 通常是物理日志，记录的是页的物理修改操作，用于将数据库物理地恢复到原来的样子
为了保证每次日志都写入重做日志，需要每次将重做日志缓冲写入重做日志文件后，都调用一次 fsync 操作，强制将操作系统的文件缓冲写入磁盘
innodb_flush_log_at_trx_commit
刷新redo日志只在master thread中操作
每次提交事务都强制 fsync
每次提交都提交写文件的操作，但是什么时候写到磁盘由操作系统决定
与binlog的区别
binlog用来进行 ponit-in-time的恢复及主从复制环境的建立
binlog是数据库的上层产生的，任何引擎都会产生二进制文件
二进制日志是逻辑日志，记录的是对应的sql文件
二进制文件的恢复不是幂等性的，而redo是如：二进制日志记录的是事务提交的sql，如insert，两次 insert 会产生两条数据但是 redo log 是 物理页修改记录，page(2) offset 3 val = 4，这样即使执行再多次，结果也是一样的
LSN
log sequence number ，表示日志序列号，占用8个字节，单调递增
LSN的含义
重做日志写入的总量
checkpoint的位置
页的版本
每个页的头部，有一个FIL_PAGE_LSN，记录了该页的LSN，表示该页刷新时LSN的大小，用来判断也是否需要从重做日志进行恢复操作
redo 的恢复
checkpoint技术记录了已经刷新到磁盘的数据点，因此从redo 日志恢复时只需要恢复checkpoint后面的部分
undo
undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段。undo段位于共享表空间内
undo是逻辑恢复，即是在逻辑上恢复数据，如insert一条数据，就会有相应 的delete sql，但是对于物理的数据页，可能不会直接删除，而是逻辑上把这个数据标志为删除如一个事务在修改一个页的某几条记录，同时还有别的事务在对同一页中另外几条记录进行修改，如果再事务回滚后把页回复到事务之前的样子，就会丢失掉其他事务的修改数据
undo的另一个作用是实现MVCC，当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取
undo log 会产生redo log，也就是undo log的产生会伴随这redo log 的产生，因为undo log也需要持久性的保护
事务提交后不能马上删除undo log，因为可能有其他事务通过 undo log 来得到了记录之前的版本，所以事务提交时将undo log 放入一个链表中，最终是否删除由 purge 线程判断
undo log格式
insert undo log
因为插入数据在隔离性要求下未提交前对其他事务是不可见的，所以可以事务提交后直接删除
update undo log
记录的是对delete 和 upate 操作产生的undo log，可能用于 MVCC机制，因此不能在事务提交时就进行删除，而是放入到 undo log 链表等待 purge 线程清理
purge
delete 和 update 操作可能不直接删除原有的数据，purge用于最终完成delete和update操作，清理之前行记录的版本
group commit
每次事务提交都会进行一次fsync 操作，以保证重做日志都写入磁盘，但是磁盘的fsync性能有限为了提高效率，当前数据库提供 group commit 功能呢，即一次fsync可以刷新确保多个事务日志被写入文件
TODO 二次提交，没接触过的概念，以后再补充
事务控制语句
Start Transaction / Begin 显式地开启一个事务
COMMIT 提交事务
Rollback 回滚
Savepoint identifier 创建事务保存点，一个事务可以有多个保存点
release savepoint indentifier 删除一个事务保存点
rollback to [savepoint] identifier 回滚到一个标记点
set transaction 设置隔离级别
对事务操作的统计
计算方法 (com_commit+com_rollback) /time
只统计显示提交的SQL，隐式提交和回滚的不会计算
事务的隔离级别
Read Uncommited
Read Committed
唯一性的约束检查和及外键约束检查需要 gap lock
Repeatable read
Innodb 默认隔离级别，使用next-key lock锁算法，避免幻读产生
Serializable
值允许多个独立的事务资源，参与到一个全局的事务中
在使用分布式事务时，innodb 存储引擎的隔离级别必须设置为serializable
XA事务
通过XA事务来支持分布式事务的实现
组成
资源管理器resouece managers
提供访问事务资源的方法，通常一个数据库就是一个资源管理器
事务管理器Transaction manager
协调参与全局事务中的各个事务，需要和参与全局事务的所有资源管理器进行通信
应用程序application program
定义事务的边界，指定全局事务的操作
分布式事务使用两段式提交（two-phase commit）
第一阶段，所哟参与全局事务的节点都开始准备，告诉事务管理器他们准备好提交了
第二阶段，事务管理器告诉资源管理器执行Rollback还是Commit
如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚
与本地事务不同的是，分布式事务需要多一次的prepare操作，待收到所有节点的同意信息后，再进行commit或是rollback操作
内部XA事务
前面的分布式事务是外部事务，即资源管理器是mysql数据库本身。mysql还存在另外一种分布式事务，其在存储引擎与插件之间，又或者在存储引擎和存储引擎之间，称之为内部XA事务
常见的内部XA事务存在于binlog与innodb存储引擎之间，这两个log的写入必须是原子性的若二进制文件先写入，但是innodb的redolog写入失败了，那么在主从模式下，slave可能会收到master传过去的二进制文件并执行，最终导致主从不一致的情况
内部的XA事务，当事务提交时，Innodb存储引擎会先做一个perpare操作，将事务的xid写入，接着进行二进制日志的写入，如果在Innodb存储引擎提交前，mysql数据库宕机了，那么mysql数据库再重启后会先检查准备的uxid事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作
不好的事务习惯
在循环中提交
循环提交会导致不知道其中哪个事务发生了回滚因为每次提交都会调用 fsync 刷新磁盘，所以性能也不好
使用自动回滚
长事务
执行时间较长的事务
长事务的执行和回滚时间都是十分耗时，应该转化为小批量的事务来进行处理
备份与恢复
备份与恢复的概述
备份方式
热备 host backup在线备份 online backup
值数据库运行中直接备份，对正在运行的数据库操作没有任何影响
冷备 cold backup
在数据库停止的情况下，一般只需要复制相关的数据库物理文件即可
温备 warm backup
同样在数据库运行中进行，但是会对当前数据库操作有所影响，如加一个全局读锁以保证备份数据的一致性
快照备份
指通过文件系统支持的快照功能对数据库进行备份
备份文件内容分类
逻辑备份
一本是文本文件，内容如一条条SQL好处是可以观察导出的文件内容，适用于数据库的升级，迁移等工作缺点是执行SQL需要较长的时间
裸文件备份
复制数据库的物理文件，即可以是数据库运行中的复制，也可以是在数据库停止运行时直接的数据文件复制恢复时间比逻辑备份短很多
备份内容
完全备份
对数据库进行一个完整的备份
增量备份
在上次完全备份的基础上，对更改的数据进行备份
日志备份
指对mysql数据库二进制日志的备份，通过对一个完成备份二进制日志的重做来完成数据库的point-in-time的恢复工作
mysql复制原理就是异步实时地将二进制日志重做传送并应用到从数据库中
冷备
备份mysql数据库的frm文件，共享表空间文件，独立表空间文件（*.ibd），重做日志文件
优点
备份简单，只要复制相关文件即可
备份文件易于在不同操作系统，不同mysql版本上进行恢复
恢复简单，只需要把文件恢复到指定的位置即可
恢复速度快，不需要执行任何SQL语句，也不需要重建索引
缺点
Innodb存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放这很多其他的数据，如undo段，插入缓冲等
冷备跨平台支持性差。操作系统、mysql版本、文件大小写敏感和浮点数格式都会成为问题
mysqldump
mysqldump db... > file_name
mysql -uroot -p < backup.sql
source backup.sql
mysql dump 不能备份视图
select ... into outfile
二进制日志备份与恢复
默认情况下不开启二进制日志，需要配置开启[mysqld]log-bin=mysql-big
mysqlbinlog
热备
ibbackup
工作原理
记录备份开始时，innodb存储引擎重做日志文件监测点LSN
复制共享表空间文件以及独立表空间文件
记录复制完表空间文件后，Innodb存储引擎重做日志文件检查点的LSN
复制在备份时产生的重做日志
是mysql的收费软件
在线备份，不阻塞任何的SQL语句
备份新能好，备份的实质是复制数据库文件和重做日志
支持压缩备份，通过选项，可以支持不同级别的压缩
恢复步骤
恢复表空间文件
应用重做日志
xtrabackup
与ibbackup类似，但是开源免费
支持增量备份
首先完成一个全备，并记录下此时检查点的LSN
在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当时检查点的LSN
复制
一种高可用高性能的解决方案，一般用于建立大型的应用
原理是完全备份加上二进制日志备份
原理步骤
主服务器把数据更改记录到二进制日志中
从服务器slave把主服务的二进制日志复制到自己的中继日志（relay log）中
从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性
数据库复制是 异步实时 的
从服务器有两个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志另一个SQL线程，复制执行中继日志
主从服务+快照
从服务开启read-only选项，保证从服务器上的数据仅与主服务器进行同步，避免其他线程修改数据
性能调优
选择合适的CPU
内存的重要性
MYSQL有大量的内存缓冲区用于提升性能，
磁盘性能
合理的设置RAID
RAID的基本思想是吧多个相对便宜的硬盘组合起来，成为一个磁盘数组
操作系统的选择
不同的文件系统对数据库性能有影响
收藏
收藏
0 条评论
回复 删除
下一页
