mysql事务特性、存储引擎、B+树索引介绍

一、mysql事务特性

事务是一组不可再分割的操作集合（工作逻辑单元）
事务的手工操作：
    begin / start transaction
    commit / rollback
    
设置事务是否自动提交：
    set session autocommit = 'on/off'
    commit / rollback
    
事务特性：ACID
    原子性（事务是最小工作单元，要么一起成功，要么全部失败回滚） 
    一致性 （事务中操作的数据改变的结果符合预期）
    隔离性（并发访问场景下事务间共享的数据可见性设定带来的问题） 
    持久性（事务所作的修改会永久保存，不会因为系统意外导致数据的丢失）  
    
    原子性的回滚：
        Undo Log 物理日志，记录的是事务过程中每条数据的变化情况。默认存储在系统表空间(ibdata)中.
        保证事务的原子性回滚；快照读
        
    一致性：
        WAL（write ahead logging） 日志预写 + Redo Log数据崩溃功能
        刷脏过程中当出现页断裂的情况  双写机制 Doublewrite Buffer => Redo Log
        
    持久性：
        Redo Log机制和刷盘策略
        
    隔离性问题：
        脏读（读到事务B未提交的数据，后来事务B又进行回滚）、
        幻读（前后两次读取的数据条数不一样）、
        不可重复读（两次读取的数据结果不一致）
    
    SQL92事务隔离级别：
        未提交读
        已提交读
        可重复读
        串行化

    mysql的默认隔离级别是可重复读；oracle、sqlserver的默认隔离级别是读已提交。oracle只实现了已提交读和串行化事务
    
    隔离级别解决方案：
    LBCC 事务并发访问数据时，即对数据进行加锁，阻止其他事务对数据进行操作。利用锁的排他性独占数据的操作权限。
        当前读（sql操作的数据是最新数据）
        DML | select ... lock share mode（读锁） | select ... for update (写锁)
        
    MVCC 事务并发访问数据时，对正在事务内处理的数据做多版本的管理。避免写操作的堵塞从而引发的读操作的并发阻塞问题。
            将数据在当前时间点进行一份数据快照的备份（多版本管理），并用这个快照提供给其他事务进行一致性读取。
        快照读（读的的数据的历史版本）
        DQL
        
    多版本管理基于以下实现：
        DB_TRX_ID 事务的插入版本号
        DB_ROLL_PTR 事务的删除版本号
    
    
    锁的分类；
        共享锁-读锁 s锁（行锁） 
            多个事务对于数据可以共享访问（读读锁不互斥），但不能对数据进行修改
            加锁方式：select ... lock in share mode  释放锁：commit/rollback
        排他锁-写锁 x锁（行锁） 
            读写锁互斥，获取了x锁才可以对数据进行修改操作
            加锁：DML | select ... for update  释放锁：commit/rollback
        
    行锁的具体实现（算法）：
        临键锁 Next-key lock(基于索引搜索且进行范围查找且数据命中时采用) 锁住当前命中的数据所在的区间以及相邻的下一个区间（左开右闭）    --- 默认的行锁算法，解决脏读幻读
        范围锁 Gap Lock(基于索引搜索且数据不存在) 锁住不存在的数据所在的gap区间（左开右开）  --- 查询不到数据，Next-key lock退化成Gap Lock
        记录锁 Record Lock(基于索引搜索进行等值匹配且命中查询)  锁住命中的具体索引的索引项
            
        
    Innodb行锁实现原理：
        Innodb的行锁是通过给索引的索引项加锁实现的。
        sql的执行基于索引的检索时，才会使用行锁。未使用索引检索的sql执行时，Innodb将使用表锁。
        基于辅助索引检索的sql，辅助索引以及对应的主键索引都将锁定指定的索引项。
    
   
    show engine innodb status 可查看当前innodb的执行情况，也可查看死锁情况。

二、mysql的存储引擎

1、Mysql体系结构：

client connectors(jdbc,odbc,python)
     ||
Mysql server
    connection pool（连接池处理客户端发起的连接，验证连接的正确性）
    sql Interface（DBL，DML，触发器，存储过程等）   =》  
    parser(解析器，sql数据转义，解析数据对象)   =》  
    optimizer(优化器，找到sql的最优执行计划，物理优化（去掉恒等式的过滤条件，将条件重新排序让其能够使用联合索引等），逻辑优化（子查询优化，in等）)   =》  
    caches(sql执行缓存，已经执行过的sql作为key，数据作为value存储起来，5.8以后废弃了) 

2、存储引擎：

    CSV    不能定义索引，列定义必须not null, 数据以','隔开，数据可以通过编辑文本的形式修改
        应用场景；可进行数据的快速导入导出
        
    Archive 压缩协议（ARZ格式）进行数据的存储，磁盘占用少；只支持insert和select操作；只允许自增ID列建立索引
        应用场景：数据备份系统（日志系统、文件归档）
        
    Memory 数据都存储在内存中，处理效率高；表大小限定默认16M(可配置)；不支持blog\text等大数据存储类型字段；不支持可变长的字符类型；支持hash索引；重启数据库服务或服务器崩溃数据会丢失
        应用场景：Mysql临时表存储，当表的大小超过了配置的大小，会采用Myisam存储引擎
        
    Myisam 较快进行数据插入和读取；支持索引缓存，不支持数据缓存；磁盘空间占用较少；支持表锁，不支持事务；数据与索引文件分开存储，主键索引与辅助索引同级；关于数据条数有额外的常数进行存储，count（*）效率很高
        应用场景：适用于已读为主的业务（因为不支持事务相关操作）
    
    Innnodb 支持事务；
            支持行级锁；
            支持索引缓存和数据缓存；
            聚集索引；
            外键支持（一般不会在数据库层面强制执行主外键关系）；
            支持地理位置的数据类型；
            MVCC（多版本协议控制）；
            存储限制为64TB

3、 sql的执行过程：

   请求客户端=》 Mysql服务层（缓存 / sql Interface=> 解析器 =》优化器=》执行器） =》存储引擎层
    管理连接，验证权限=》 （缓存命中及权限=》此法分析，语法分析=》最优执行计划生成=》执行引擎调用API）
    

    DQL 数据查询语言
    DML 数据操作语言
    DDL 数据定义语言
    DCL 数据权限控制语言
    
    innodb的数据库页的pagesize是16K，操作系统的pagesize是4K。
    Buffer Pool用于缓存表数据和索引数据，把磁盘上的数据加载到Buffer Pool，避免每次访问都进行磁盘操作。
    基于空间局部性原理（预读），磁盘访问按数据库页大小读取能够提高性能，缓冲池（Buffer Pool）按数据库页（Innodb_page_size）缓存数据。
    总体设计遵循LRU淘汰策略（按数据库页进行淘汰）。
    Buffer Pool设计：新生代（放置热点数据）+老年代停留时间配置阈值 5:3  Tail Head（初次读取的数据放入Head中，若数据被第二次读取，则放入新生代中）  (解决预读失效和缓冲池污染的问题)
                     相关配置参数 innodb_buffer_pool_size
    
    DQL执行：Mysql的数据读取按数据页来读取，而不是按需读取，将数据从IBD磁盘文件中读入Buffer Pool中。
    
    DML语句在Innodb存储引擎中的执行流程：change Buffer（将变更的数据先存储到Buffer Pool（存储脏页）中，不会立即刷盘） =》 
                            <----         记录事务日志文件RedoLog（prepare） =》 
                    类似2pc                  Mysql服务层记录BinLog => 
                            <----         commit（事务日志文件RedoLog刷盘）=》
                                         等待刷脏（将Buffer Pool中的脏页刷入磁盘中的过程）
    
    在Innodb的一次事务提交过程中，事务操作并不会马上将变更的数据刷新到磁盘中。事务的完结只需要将变更记录在Buffer Pool中形成脏页，完成Redo Log的记录即可。
    刷脏的时机：RedoLog满了；Buffer Pool空间不够了；Mysql服务器空闲时；Mysql服务器关闭时。
        
    Innodb RedoLog为重做日志，记录的是事务提交过程中数据的变更情况，是物理日志。主要用于保证数据库服务意外中断，导致脏页数据未及时刷脏时数据的恢复手段。
    RedoLog是一组固定大小的文件，记录日志信息循环使用。
    Redol Log Buffer为RedoLog在内存中的日志缓冲区，一旦RedoLog Buffer中的内容刷到了磁盘的Redo Log文件中，事务成功。
    RedoLog 刷盘策略：0 每秒刷一次 1 每笔刷一次 2 每笔刷一次内存缓存（OS cache），然后os cache每秒刷一次id_logfile文件（磁盘文件）
    
    Mysql BinLog日志：mysql server层的逻辑（过程/操作）日志文件；
        以事件的形式记录了所有的DDL和DML操作；
        以文件追加的方式进行记录，没有大小的限制；
        是主从复制（搭建集群）和数据恢复的神器（生产环境一定要开启，bin-log=logfile文件名）

三、mysql的B+平衡树

BTree 多路平衡二叉树  
为了保证绝对平衡，节点的合并和分裂，插入时不断地做左旋右旋的操作
二叉树 --》 平衡二叉树 --》 BTree
查询时磁盘与内存之间的IO操作次数递减
n个关键字对应n+1个子节点，每个节点都会存储对应的数据区

mysql的B+树：采用左闭合数据比较规则  数据区位于叶子节点（叶子节点的关键字内容形成一个双向的链式结构）
Gap数据区    1 <= X < 28
            28 <= X <66
            66 <= X
若查询最小值范围以下的数据X=-1的数据，则查询直接中断，不会进入索引

相较于B树的优势：
1、排序（叶子节点的关键字内容形成一个双向的链式结构）；
2、IO效率（数据只存在叶子节点中）；
3、查询稳定性(每一次查询必须要搜索到到叶子节点，IO的次数是固定的，取决于树的深度);
4、全索引数据的扫描（执行计划中type=Index，把索引中的数据全部扫描一遍，B+树只会将叶子节点的数据加载到内存，B树需要把查找过程中的所有层的数据全部加载到内存）

B+树：
Myisam引擎中  索引与数据完全隔离，索引的叶子节点的数据区中存储的是数据记录的地址  查询时会索引合并，一次查询中可能会触发多个索引（多个行的条件）
Innodb引擎中  聚集索引（主键索引）  索引的叶子节点的数据区中存储的是表中对应数据行的所有数据   一次查询只会触发一个索引
              若表中没有建主键，则默认将表中第一个唯一索引作为表的聚集索引，若也没有，则建在rowid上。
              只有主键索引是聚集索引，其余都是辅助索引（非聚集索引、二级索引、普通索引）
              
              辅助索引的叶子节点的数据区中存储的是主键值（没有存数据行的地址是因为数据行的地址会不断变化，存主键值的话就只需要变主键索引，而不需要每个辅助索引的数据区都变），
              根据辅助索引查到主键值后，再到主键索引中进行二次搜索（回表操作）
              

explain / desc + sql  查看生成的执行计划
information_schema.potimizer_trace 查看sql执行计划的轨迹
        id  type 使用了什么类型的索引 key 使用的索引名   key_len 索引使用情况      
        
        最好使用自增主键，因为插入数据时，只会变化主键索引B+树的右边树部分，不会变化左边树
        
        离散型差的字段不建议做索引，重复性越高，离散型越差（不满足驱动性索引条件，满足条件的数据覆盖率过高，超过百分之六十，可能会导致不走索引，直接全表扫描）
        
        计算、函数、（自动or手动）类型转换, 列的隐式转换（列的类型是varchar2，查询条件传入int类型）会导致索引失效
        
        索引关键字比较规则：从左到右依次比较
        
        联合索引要遵循最左前置原则：最常用列 > 离散度高 > 使用空间最小（字段占用长度）
        
        巧妙利用覆盖索引（创建更合适的联合索引）避免回表操作   覆盖索引：通过索引项的信息可直接返回所需的查询列