关于MySQL中InnoDB引擎的一些理解

MySQL架构体系（四层） 单进程多线程
    客户端连接器(Native C API, JDBC, PHP等等)
    连接池组件、管理服务与工具组件、SQL接口、查询分析器、优化器、缓冲
    插件式存储引擎
    物理文件

常用存储引擎、优缺点
    InnoDB、事务处理、全文索引、行锁、外键、MVCC高并发、一致性非锁定读、默认可重复读、通过next-key-locking解决幻读问题（脏读、幻读、不可重复读）、插入缓冲、二次写、自适应哈希索引、预读
    MyISAM、不支持事务、表锁、非聚集索引、存储文件单独放，不完整的外键
    Memory（hash索引、堆存储、表锁并发差、不支持text、blob，定长存变长字段、临时表有可能转MyISAM）、
    NDB（内存、集群、数据库层面的join）、
    Archive（归档、压缩）、
    Meria（支持缓存数据和索引文件、行锁、MVCC、支持事务和非事务安全的选项、更好的BLOB字段处理）
    还有Federated、CSV、Merge

InnoDB存储引擎（多线程）是基于磁盘的数据库系统
    架构：后台线程、内存池、文件
        后台线程：
            Master Thread：主要负责将缓冲池中的数据异步刷新到磁盘中，保证数据的一致性，包括脏页的刷新，INSERT BUFFER、UNDO页的回收
            IO Thread：使用异步IO处理写IO的请求；一个 insert buffer线程、一个log线程、4个read thread、4个write线程
            Purge Thread（清除线程）：回收已经使用并分配的undo页，可以设置数量
            Page Cleaner Thread （页面清洁器线程）：脏页的刷新
            
        内存：
            缓冲池：
                弥补磁盘速度慢提高性能，将数据页fix到内存中，通过checkpoint机制将数据刷回磁盘。
                数据页：索引页、数据页、自适应哈希索引、insert buffer、锁信息、数据字典信息、额外的内存池、重做日志缓冲
                允许多个缓冲池实例，增加并发处理能力。
                缓冲池命中率不应低于95%
            
            LRU List、 Free List 和 Flush List：
                默认页大小：16KB
                LRU加入了midpoint，5/8处，尾端37%，前面为new，后面为old
                LRU中被修改的页称为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致，通过checkpoint机制将数据刷回磁盘
            
            重做日志缓冲：
                重做日志信息先放到这个缓冲区，然后按一定频率刷新到重做日志文件，不需要很大，8M默认
                以下三种情况会刷新到重做日志文件：
                    1、Master Thread（Page Cleaner Thread） 每秒将重做日志刷新到重做日志
                    2、每个事务提交时
                    3、重做日志缓冲池小于50%
            
            额外的内存池：缓冲帧、缓冲控制对象
    
            Write Ahead Log：防止数据丢失，先写重做日志再修改页；当宕机时可以通过重做日志恢复数据。
            Checkpoint技术：
                
                目的：
                    1、缩短数据库的恢复时间（Checkpoint之前的点，已经刷新到磁盘中）
                    2、缓冲池不够用时，将脏页刷新到磁盘（LRU溢出的页）
                    3、重做日志不可用时、刷新脏页到磁盘（然后重做日志就可以被覆盖重用了）
            
        InnoDB关键特性：
            插入缓冲、（索引是辅助索引、索引不是唯一的）提高性能
            两次写：保证数据的可靠性，对于脏页的刷新，先写到内存中的doublewrite buffer中（2M），再分两次写到物理磁盘上的doublewrite页，然后马上同步脏页到磁盘；
            自适应哈希索引：时间复杂度为O(1)，通过缓冲池的B+树页构造而来，不需要对整张表创建哈希索引，是一种数据库的自优化。
            异步IO：可以进行IO Merge
            刷新临近页：当刷新一个脏页时，会检测所在区的所有页，如果是脏页，那么一起刷新，同时可以通过AIO将多个IO合并为一个IO操作。
        
        文件：
            参数文件
            日志文件：
                错误日志、
                二进制日志（恢复、复制、审计）
                    写同步：sync_binlog=1
                    事务回滚但是写了日志：innodb_support_xa=1
                    同时主库运行rand、uuid、触发器等操作会导致数据不一致
                慢查询日志、
                查询日志
            Pid文件
            表定义文件frm
            idb文件
            InnoDB引擎的重做日志：只记录自身的事务日志
            
表：
    innoDB为索引组织表，表都根据主键顺序阻止存放的，必有一个主键，如建表时未指定主键，则判断表中是否有非空的唯一索引，如果有该索引为主键（聚集索引），否则自动生成一个6字节的rowid作为主键，除了rowid还有6字节的事务ID，7字节的回滚指针ID
    表空间：
        段、段有innoDB自己控制
        区、区固定大小1MB，
        页、页的大小可以改变4K、8K、16K
        行格式：compact
    约束：
        主键、唯一索引、外键、触发器
    视图
      
    分区：
            MySQL支持水平分区，并不支持垂直分区；局部索引分区，即分区中既存放了数据又存放索引。
            如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
            如果建表时没有指定主键和唯一索引，可以指定任何一个列作为分区列。
        
        分区类型：
            RANGE分区：行数据基于一个给定连续区间的值被放入分区。比如（1，10），（11,20），（21,30），一般年月日、可以设置maxvalue；语句： partition p0 values less than (TO_DAYS('2010-04-01'))
            LIST分区：与range分区类似，不过是离散的值。如（1,3,5,7,9），（2,4,6,8,10）；语句：使用 in 而不是less than 
            HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数（根据分区数进行取模散列）。优化类型：LINEAR HASH
            KEY分区：根据MySQL数据库提供的哈希函数来进行分区（不用用户指定表达式，直接指定列名、分区数即可）
            COLUMNS分区：可以直接使用非整数类型的数据进行分区。比如日期类型不需要YEAR()转成整数就可以直接使用
            RANGE COLUMN分区：可以使用多个列进行分区
            
            子分区：每个分区的子分区数量必须相同，必须定义所有的子分区，子分区的名字必须唯一
            
        NULL值的处理
                MySQL的分区总是认为NULL值小于任何一个非NULL值，与order by 一样。
                RANGE：总是将NULL值放在最左边的分区，如果删除含有NULL值的分区P1，那么只是删除分区定义的记录，含有NULL值的记录仍在。
                LIST：必须明确指定NULL在哪个分区
                HASH 和 KEY ：任何分区函数都会将含有NULL值的记录返回为0
            
        InnoDB 和 MyISAM 插入多个值，其中一个值不在分区的定义内时，处理方式不同，InnoDB会事务回滚。
        
        分区和性能：
            对于OLAP应用，分区可以很好地提高查询性能。
            对于OLTP应用，通常通过索引获取少量数据，因此只需2-3次IO，B+树就可以完成，如果贸然分区会增加IO次数。
                如果只对ID列分区，那么即使uname列加了索引（非唯一），通过uname进行查询时，将会查询所有分区，十个分区就是20-30次IO，会慢很多。
        
        表和分区交换数据
        
索引与算法：
    InnoDB索引介绍
        二分查找法
        二叉查找树和平衡二叉树（多用于内存中）
        B-树 和 B+树 （多用于磁盘），结构、旋转操作、拆分合并
    
    
        B+ 树索引：只能找到数据所在的页，然后将页拿到内存中进行二分查找得到具体的行。适合进行范围查询和排序
            聚集索引（叶子节点存放整行的数据）
            辅助索引（叶子节点存放指向行数据的书签），正常来讲IO要比聚集索引多一倍，但是可以覆盖索引避免回表。
        全文索引
        哈希索引（自适应，无法人为的干预）
        
    B+树索引的管理：
        ALTER TABLE  以及 CREATE/DROP INDEX 两种创建和删除索引的方式
        Cadinality值：该列的高选择性，越接近1，选择性越高，过低时优化器不使用该索引。但不是实时更新。ANALYZE TABLE可以更新该值（1/6数据变化也会更新）。
        
        在线添加索引的演变历史：
            1、建新表、修改表结构、导入数据，删除原表，将临时表重命名为原来的表名。在过程中，表完全不可用
            2、FIC，在原表上加S锁。可以读，并且只限定辅助索引，如果是主键索引，同样需要重建表
            3、OSC，Facebook用PHP实现的在线执行DDL方式，有较大的局限性。
            4、Online DDL：可以在线修改辅助索引、并可以同时对数据进行增删改，可以修改自增值、外键、列的重命名
            
        Online DDL原理：
            通过重做日志达到数据的最终一致性。
        
        B+树 比较矮胖、扇出高、IO少、故适用于磁盘，当获取表中少部分数据时，建立B+树索引才有意义。
        
    联合索引：
        对索引中第二个列已经排序，对（a,b）排序，不对（a,c）排序，有时可以避免一次排序操作
        最左前缀原则：a,b,c索引，可以使用a索引以及a,b索引和a,b,c索引，一般无法跳过a使用（如果是统计操作，并且是覆盖索引的，会使用第二个索引列）
    覆盖索引
        索引中即包含所要查询的列，不需要查询聚集索引中的记录。IO次数少
        
    不使用索引
        *号、范围查找、JOIN操作，一般会进行全表扫描（聚集索引）
    
    优先级：
        覆盖索引 辅助索引，如果数据量大（20%），或者查询整行信息，会选择聚集索引
    
    强制使用索引
        FORCE INDEX：强制使用
        USE INDEX（建议优化器使用该索引，但优化器不一定使用该索引）
        什么情况下使用：
            索引过多时，可能优化器选择索引的时间都要大于SQL执行的时间
            优化器可能错误的使用某个索引，导致查询过慢
            
    MRR优化：将随机访问转化为较顺序的访问（性能提升比较高）
    ICP优化：将先取再根据where过滤，优化为取出时就根据where过滤。
        必须是该索引能覆盖到的范围。
    
    哈希算法：
        除法散列
        拉链法（解决冲突）
        
    自适应哈希索引：
        只能用来进行等值查找。
        
    全文索引：
        使用倒排索引实现，需要辅助表存储相关信息
        https://www.cnblogs.com/lz0925/p/12022889.html
    
锁：
    用处：对共享资源的并发访问，提供数据的一致性和完整性
    
    latch：一般称为闩锁，用来保证并发线程操作临界资源的正确性，并且没有死锁检测机制。有读写锁、互斥量（内存锁，一般不需要开发去管理和优化）
    
    lock：面对事物，锁定数据库中的对象（表、页、行）。有行锁、表锁、意向锁
    
    行级锁：
        S锁 共享锁只和共享锁（意向共享锁）兼容
        X锁 排它锁和任何锁都不兼容，包括X锁本身
    意向锁：
        意向锁和意向锁兼容，IS和S锁兼容，颗粒度：表级锁
        InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
        意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁/排他锁 互斥。
        IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突（全表扫描）。
        意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

    三张表：INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS
    
    RC 的一致性非锁定读，总是读取最新的快照
    RR 的一致性非锁定读，读取事务开始时的数据版本 （这里的区别也是RR实现可重复读的根本）
    一致性锁定读就是手动加锁 for update、 lock in share mode（使用这两个语句时，务必在一个事务中）
        
    外键与锁：
        查询父表的时候使用 select ... lock in share mode，主动加S锁，如果父表已经加了X锁，子表的操作会被阻塞，否则会使用一致性非锁定读，产生数据不一致。
        innodb会自动为外键添加索引
        
    锁的算法
        行锁的三种算法：
            Record Lock: 单个行记录上的锁。
                会锁住索引记录，没有没建立索引，会使用隐式主键来进行锁定
            Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
            Next-Key-Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身（解决幻读问题，只有在RR级别下才生效）
                如果是唯一索引或主键，会降级为Record Lock，而不是锁住范围。
                如果是辅助索引，且值为 1,3，6，10， 进行where b=3 的查询时，会用Next-key-lock锁定(1,3], 然后用Gap Lock锁定(3, 6)， 将3的左右范围都锁定
                如果是where > 6 那么会锁住(6, +∞)这个范围，而不是10
        
    脏读：指一个线程中的事务读取到了另外一个线程中未commit或者回滚的数据。
    脏读官方定义：即一个事务中读取到另外一个事务中未提交的数据。
 

    不可重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据，导致两次读取到的数据内容不一致。
    不可重复读与脏读的区别是：脏读读到的是未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。
　　

    幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据，两次读取到的数据总量不一样。
    幻读官方定义：指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行
    
    丢失更新：虽然数据库能阻止丢失更新的问题，但是逻辑意义上的更新丢失无法避免。所以需要在事务中读数据时就加X锁，实现串行化，第二个事务等待第一个事务完成后再运行。
    
    
    阻塞和死锁：
        死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。
        解决死锁：
            1、超时：通过innodb_lock_wait_timeout参数设置超时的时间，超时则其中一个事务回滚
            2、wait-for-graph（等待图）进行死锁检测：判断是否有回路，若存在死锁，选择回滚undo量最小的事务。
        
        死锁的概率：
            死锁应该非常少发生，若经常发生，则系统是不可用的。
            死锁的次数要少于等待，因为至少需要两次等待才会产生一次死锁。
        大多数的死锁，innoDB可以检测到，不需人为进行干预。
        
    锁升级：将当前锁的粒度降低。行锁升级页锁，页锁升级表锁    
        innodb中不存在锁升级的问题，实际不论单条记录还是多个记录都是对页进行加锁，开销一样。
    
事务：
        ACID
        XA：
            分布式XA
            内部XA：binlog与innodb引擎之间
        
        分布式事务也可以用消息队列保持数据的最终一致性
        
备份：
    按方式分：冷备、热备、温备（在线备份但是加全局锁）
    按备份数据库的内容分：完全备份、增量备份、日志备份（二进制）
    按备份后文件的内容：逻辑备份（生成SQL语句）、裸文件备份（物理文件、快）
    
    逻辑备份：
        mysqldump
        select 。。。into
        可以使用source语句执行逻辑备份文件来恢复
    
    二进制备份：
        开启同步和XA事务支持
        mysqlbinlog命令进行恢复
    
    热备：
        ibbackup是innodb官方的热备工具可以备份innodb和MyISAM。
        实质是复制数据库文件和重做日志文件，在线备份，不阻塞任何SQL
        
复制：
    1、主服务器把数据更改记录到二进制日志中(binlog)。
    2、从服务器把主服务器的二进制日志复制到自己的中继日志(relay log)中。
    3、从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。（异步实时复制）
    
    从服务器有两个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL进程，复制执行中继日志。
    
    主从数据不一致：    
        1、跳过
        2、重做主从，锁主库，读status，复制主库到从服务器，设置同步点log_pos，开始同步
        为了避免binlog文件造成数据不一致：要打开innodb_support_xa=1、sync_binlog=1设置
        主库使用UUID、rank()等不确定函数，也会导致主从数据不一致
    从库无法进行复制：
        跳过：从最新log_pos开始复制