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

MySQL架构体系(四层) 单进程多线程
    前端、
    连接池组件、管理服务与工具组件、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 ListFlush 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_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
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
                

 

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

上一篇:Python web 框架:web.py 【数据库】


下一篇:关于mysql的索引及原理