MySQL技术内幕 InnoDB存储引擎

第一章、mysql体系结构与存储引擎

1、定义数据库和实例

数据库：物理操作系统文件或其他形式文件类型的集合
数据库实例：有数据库后台进程/线程以及一个共享内存区组成。
共享内存可以被运行的后台进程/线程所共享。
数据库实例是真正用来操作数据库文件的
MySQL被设计问一个单进程多线程架构的数据库

2、mysql体系结构

MySQL由以下几部分组成：

1、连接池组件
2、管理服务和工具组件
3、SQL接口组件
4、查询分析器组件
5、优化器组件
6、缓冲（Cache）组件
7、插件式存储引擎
8、物理文件
插件式体系结构是MySQL独有的，并且存储引擎是MySQL区别于其他数据库的一个最重要特性。存储引擎是基于表的，而不是数据库。
存储引擎的好处：每个存储引擎有各自的特点，可根据不同的应用建立不同的存储引擎表。

3、MySQL表存储引擎

InnoDB存储引擎
默认的存储引擎，主要面向OLTP（联机事务处理，面向基本的、日常的事务处理）

特点：
支持事务，支持外键、支持行锁（有的情况下也会锁住整个表）、非锁定读(默认读取操作不会产生锁)

通过使用MVCC（多版本并发控制）来获取高并发性，并且实现sql标准的4种隔离级别，默认为可重复读（repeatable）级别

使用一种被称成next-key locking的策略来避免幻读(phantom)现象

还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能高可用技术。

表数据采用聚集方式，每张表的存储都按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键

MyISAM存储引擎：

不支持事务、支持全文索引，表锁设计，主要面向一些OLAP（联机分析处理，数据仓库的主要应用）。

它的缓冲池只缓冲索引文件，而不缓冲数据文件．

该存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件.

NDB:

是一个集群存储引擎，其特点是数据全部放在内存中。

因此主键查找速度极快，并通过添加NDB数据库存储节点可以线性提高数据库性能，是高可用，高性能的集群系统。

Memory:

将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。

它非常适合存储临时数据的临时表．默认采用哈希索引。

只支持表锁，并发性较差。

二、InnoDB存储引擎

InnoDB是事务安全的MySQL存储引擎。

内存池：

1、主线程：缓冲池中的数据异步刷新到磁盘，保证数据的一致性

  1、purge thread
      回收已经使用并分配的undo页
      innodb_purge_threads
  2、page_cleaner_thread
      刷新脏页
      innodb_page_cleaners

2、缓冲池

2.1、概述

    1、Innodb是基于磁盘的存储引擎，将记录按照页的方式进行管理
    2、通过内存弥补磁盘速度较慢对数据库性能的影响
    3、对于数据库中页的修改操作，首先修改在缓冲池中的页，然后以一定的频率刷新到磁盘
    4、CheckPoint技术
    5、innodb_buffer_pool_size设置缓冲池的大小
    6、innodb_buffer_pool_instance设置缓冲池实例数量

2.2、缓冲池中缓存的数据页类型

    0、页的默认大小是16KB
    1、数据页
    2、索引页
    3、插入缓存页
    4、自适应哈希索引
    5、锁信息
    6、数据字典

2.3、缓冲池管理方式

    1、LRU list
        1、Innodb_old_blocks_pct: 把LRU列表分为new/old列表，新读取的页放在中点位置
        2、Innodb_old_blocks_time: 超时时间还没有刷出列表，那么就可以列为new 列表中，主要是针对表还没有满的情况
            page_made_young
            page_not_made_young
    2、free list: 启动的时候，缓存池还是空的，所有可用空间都在free列表中
    3、flush list : 页被修改的同时，也会加入到刷新列表中，与LRU列表互不影响

3、重做日志缓冲（redo log buffer)

InnoDB_log_buffer_size
重做日志缓存刷新到文件的三种情况
    1、master thread每秒将redo_log_buffer刷新
    2、每个事务提交
    3、重做日志缓冲剩余空间不足一半

4、CheckPoint技术

1、解决以下问题：
    1、缩短恢复时间
    2、缓存池不够用，将脏页刷新到磁盘
    3、重做日志不可用，刷新脏页
2、分类
   sharp checkpoint
        数据库关闭时将所有的缓冲数据刷新到磁盘，innodb_fast_shutdown=1
    fuzzy checkpoint
        只刷新部分脏页
3、出现checkpoint的情况
    1、主线程每秒或每十秒都会进行checkpoint
    2、lru列表需要保证有足够的空闲页 ：5.6开始通过innodb_lru_scan_depth来控制空闲页数量：单独放在page_cleaner线程中进行
    3、重做日志不可用的时候出现同步/异步checkpoint: 5.6开始单独放在page_cleaner_thread进行
    4、脏页太多 ：innodb_max_dirty_pages_pvt

5、主线程工作方式

5.1、4个循环

    1、主循环
        1、每秒
            1、总是把重做日志缓冲刷新到磁盘中，即使事务还没有提交（总是）
            2、根据前一秒io操作小于5次，合并插入缓冲
            3、脏页的比例超过阈值，刷新100个脏页到磁盘
            4、没有用户操作，切换到后台循环
            5、每秒操作单独放在page_cleaner_thread进行
        2、每十秒
            1、前十秒io操作小于200，刷新100个脏页到磁盘（可能）
            2、合并至多5个插入缓冲
            3、将重做日志刷新到磁盘
            4、删除无用的undo页：full purge 操作
            5、脏页超过阈值70 删除100个脏页，否则删除10个脏页
    2、后台循环
        1、删除无用的undo页
        2、合并20个插入缓冲
        3、有任务跳转回主循环
        4、没有任务就刷新循环
    3、刷新循环
        不断刷新100个脏页，直到脏页的比例没有超过阈值
    4、暂停循环

5. 2、参数

    1、innodb_io_capacity
    2、innodb_adaptive_flush

6、关键特性

6.1、插入缓冲

    1、原理：判断插入的非聚集索引页是否在缓冲池中，如在直接插入
    ，如不在，那么先放在Insert Buffer对象中，然后以一定的频率进行insert buffer 和辅助索引页子节点合并操作
    2、条件：非唯一的辅助索引
    3、参数
        innodb_chager_buffering inserts、deletes、purges、change、all、none:设置插入缓冲的类型
        innodb_chager_buffer_max_size最大50% ：设置插入缓冲最大可以占用缓冲池的比例
    4、插入缓冲合并时机
        1、辅助索引页被读取到缓冲池中
        2、insert_buffer_bitmap页追踪到辅助索引页没有可用空间
        3、master_thread

6.2、两次写

    1、内存中存在一个2MB的doublewrite buffer，共享表空间连续的128页（2个区，2MB）
    2、memcopy将脏页拷贝到buffer中，然后一次复制1MB的数据到共享表空间中，同步磁盘
    3、将buffer的页写到各个表空间中
    4、通过、show status like 'innodb_dblwr'查看

6.3、自适应哈希索引

    数据库的自优化
    只能做等值查询操作

6.4、异步IO

    innodb_use_natice_aio开启

5、刷新邻接页

    刷新一个脏页的时候，innodb引擎会检测该页所在区的所有页，如果是脏页就一起刷新

7、启动、关闭、恢复

1、innodb_fast_shutdown 
    0:完成所有的full purge，merge insert buffer，所有的脏页刷新到磁盘
    1：不完成full purge，merge insert buffer，但是完成所有的脏页刷新工作
    2：都不
2、innodb_force_recovery:
    0:进行所有的恢复操作

三、文件

1、日志文件

1、错误日志

    1、针对mysql的启动运行关闭过程中进行记录
    2、show variables like 'log_error'查看日志位置

2、二进制文件

    1、记录数据库执行更改的所有操作，用于复制恢复审计
    2、log_bin指定文件名，开启二进制日志
    3、max_binlog_size指定二进制文件最大值
    4、二进制日志缓冲
        1、binlog_cache_size
            1、是基于会话的
            2、所有未提交的二进制日志会计入到缓存中，等待事务提交时直接将二进制日志缓冲写入到日志文件中
        2、binlog_cache_use
        3、bin_cache_dish_use  show status like 'binlog_cache%'查看日志位置
        4、sync_binlog 
            1/N；表示不通过系统缓冲，直接就同步写磁盘的方式写入到日志文件中
            0：表示mysql不控制刷新，由操作系统对缓冲进行控制
     5、binlog_format
        statement  逻辑sql语句
        row       记入表的更改情况
        mixed
            1、默认采用statement格式
            2、以下情况采取row格式：
                1、使用UUID(),user()等不确定的函数
                2、使用insert delay语句
                3、使用用户定义函数
                4、使用临时表
                5、NDB存储引擎

3、慢日志查询

    1、slow_query_log查看是否开启慢查询
    2、long_query_time设置查询超时阈值
    3、long_queries_not_using_index查询语句没有使用索引，也会放到慢查询日志中
    4、mysqldumpslow命令分析慢查询日志
    5、慢查询日志格式查询：show variables like ’log_output'; file/table
4、查询日志 通用日志查询，记录所有对数据库的请求信息

2、innodb存储引擎文件

1、重做日志文件

    1、循环日志组ib_logfile()，ib_logfile1
    2、重做日志与二进制日志的区别
        1、二进制日志是记录所有与mysql数据库相关的日志记录，重做日志只是记录与innodb存储引擎相关的事务日志
        2、二进制日志记录的是关于一个事务的具体操作内容，是逻辑日志。重做日志记录的是关于页的更改的物理情况
        3、写入时间不同：二进制日志仅在事务提交前进行提交，即只写盘一次；只要事务在进行，就会产生重做日志
    3、Innodb_flush_log_art_trx_commit
        0：等待主线程去刷新重做日志缓冲
        1：将重做日志同步刷新到磁盘
        2：异步刷新到磁盘，即写入到系统的缓冲中

2、表空间文件

    1、innodb_data_file_path
        1、格式 ：文件路径[:文件大小:autoextend];...
        2、默认的共享表空间
    2、innodb_file_per_table

四、表

1、索引组织表IOT

    1、表按照主键的顺序存放的
    2、innodb存储引擎中，每张表都有一个主键
        1、如果有非空的唯一索引，那就是主键（not null+unique key /primary key）
        2、如果没有，自动创建一个

2、Innodb逻辑存储结构

    1、段（sement) 1、数据即索引，索引即数据，数据段就是B+树的叶子节点，索引段就是非叶子节点
    2、区（extent）1、每个区固定大小1M，页大小默认为16K，所以一个区有64个页子主题
    3、页（page） 
        innodb_page_size(默认就是16KB)
        如果页中只能放下一条记录，那么innodb存储引擎会自动将行数据存放在溢出页

3、约束

    primary key
    unique key
    foreign key
        cascade级联 :父表与子表delete/update 同步
        set null :父表delete/update，子表set null
        not action ：父表delete/update ,抛出错误
        restrict： 父表delete/update,抛出错误 ：默认方式
    default
    not null

4、分区

1、分区类型

        1、水平分区
            1、mysql只支持该分区类型
            2、意思是表的某范围行作为一个分区
        2、垂直分区： 意思是表的某范围列作为一个分区

2、mysql支持的分区类型

        1、range分区
        2、list分区
        3、hash分区
        4、key分区

3、如果表中存在主键，唯一索引，那么分区列必须是唯一索引的一部分

五、索引与算法

1、哈希索引

     1、存储引擎根据表的情况自动生成索引，不能人为干预
     2、自适应哈希索引特性：InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在表中生成哈希索引。

2、B+树

    为磁盘或其他存取辅助设备设计的一种平衡查找树。
    所有记录点按大小顺序存放在同一层的叶子节点上。
    各叶子节点由指针进行连接。

1、B+树索引

    B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据B+索引在数据库中有一个特点是高扇出性
    B+树的高度一般在2~4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。
    扇入：指直接调用该模块的上级模块的个数。
    扇出：是指该模块直接调用的下级模块的个数。
    B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），但是不管是聚集还是辅助索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。
    聚集索引和辅助索引不同的是，叶子节点存放的是否是一整行的信息。

2、索引管理

        1、show index ：cardinality：索引中唯一值数目的估计值，越接近1越好
        2、快速索引创建（FIC)
            1、主键的创建和删除过程
                1、首先创建一个新的临时表
                2、原表的数据导入到临时表中
                3、接着删除原表
                4、重命名临时表
            2、辅助索引的创建和删除
                在创建索引的表上加S锁，避免创建临时表
        3、在线架构改变（OSC）
        4、在线数据定义（online DDL）
            1、在创建辅助索引的过程中可以指定算法和加锁方式
                1、算法
                    1、copy创建临时表方式
                    2、inplace 不需要创建临时表
                2、加锁方式
                    1、none
                    2、share加S锁
                    3、exlusive加X锁
                    4、default
            2、原理：在执行创建或删除索引的同时，将insert/update/delete操作日志写入到缓冲中，待操作完成后再执行DML操作，保证数据一致性
                Innodb_online_alter_log_max_size(默认是128M)

3、cardinality估算值

         1、越接近真实的行数，就具备了给选择性
         2、更新策略
             1、表中1/16的数据已经发生改变
             2、修改次数达到指定阈值
        3、实现机制： 默认innodb存储引擎对8个叶子节点采样统计  innodb_stats_transient_sample_pages
        4、Mult-Range Read多范围读优化  optimizer switch
        5、index condition pushdown ICP优化  在去除索引的同时进行where过滤

4、聚集索引

      1、聚集索引就是按照每张表的主键构造一棵B+树。
      2、叶子节点中存放的是整张表的行记录数据，叶子节点也成称为数据页。
      3、 索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页通过一个双向链表来进行链接。
      4、聚集索引能够特别快的访问针对范围值的查询。

5、辅助索引（非聚集索引）

1、叶子节点并不包含行记录的全部数据。
2、叶子节点除了包含键值外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。
3、该书签用来告诉innodb存储引擎哪里可以找到与索引相对于的行数据。
4、辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。       
5、当通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过页基本的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

6、索引覆盖

     Innodb存储引擎支持索引覆盖，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
     使用索引覆盖的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

3、全文索引

       1、5.6开始支持全文索引

2、主要概念

        1、辅助表
            1、用于存放倒排索引
            2、有6张表，都持久化在磁盘上
        2、FTS Index Cache 全文检索索引缓存
            1、查看分词信息
                1、指定表为辅助表  set innodb_ft_aux_table='test/tb'
                2、select * from information_schema.innodb_ft_index_tabke;
        3、FTS Document ID
            1、为了支持全文索引，innodb会自动添加一列：
                FST_DOC_ID bigint unsigned not null unique key分区
            2、optimize table tb 可以删除已经被我们删除的索引
                innodb_optimize_fulltext_only只做全文优化，不进行cardinality统计
                innodb_ft_num_work_optimize设置每次删除索引的数量
        4、stopword列表  分词列表

3、全文索引限制

    1、每张表只能有一个全文检索的索引

4、全文检索

    match（col1，col2,...) against('word')
    in natural language node
        查询带有指定word的文档
        默认的方式
    in boolean mode 可以出现+ 字符，相当于扩展
    with query expansion 自动相关查询

六、Innodb锁

1、锁的类型

    1、行级锁
        1、共享锁（读锁 或 S LOCK），允许事务读一行数据
        2、排他锁（写锁 或 X LOCK），  允许事务删除或者更新一行数据，更改操作innodb自动加锁
   2、表级别锁
        1、意向共享锁(IS Lock），事务想要获得一个表中某几行的共享锁。
         2、意向排它锁（IX Lock），事务想要获得一个表中某几行的排它锁。 
   3、如果更改操作不是在索引上，那边加锁就是表锁

2、一致性非锁定读

    1、定义：通过多版本控制的方式来读取当前执行时间数据库中的数据  默认的读取方式
    2、如果读取得行正在执行delete/update 操作，读取操作不会等待行上锁的释放，直接读取行的快照数据
    3、在读已提交的隔离级别上，快照数据总是最新数据
    4、在可重复读的隔离级别上，快照数据是读取事务开始时的行数据版本

3、一致性锁定读

    select for update
    select lock in share mode

4、自增长锁

    innodb_autoinc_lock_mode
        0:AUTO-INC Lock方式
            锁的释放不在事务结束才释放，而是在完成自增长值插入的SQL语句立即释放
        1：默认值 
            1、对应插入确定行数的操作会采用互斥量来产生自增长值
            2、不能确定行数采用auto-Inc lock
        2：采用互斥量来产生自增长值  可能导致值不是连续的

5、锁的算法

    1、record lock记录锁 ：单行记录上的锁，总是去锁定索引记录
    2、gap lock间隙锁
        1、锁定一个范围，但不包含记录本身
        2、阻止多个事务将记录插入到同一范围内
        3、显示关闭gap-lock记录锁  
            1、隔离级别设置为读已提交
            2、innodb_locks_unsafe_for_binlog=1
    3、next-key lock
        1、锁定范围并且锁定记录本身
        2、解决幻读问题
        3、对于行的查询都是采用next-key lock
        4、查询的索引是唯一属性，那么next-key lock 降级为record key
        5、查询的是辅助索引，那么还会锁定下一个键值gap-lock

6、锁问题

    1、幻读（不可重复读）
        1、读到了已提交的数据
        2、可重复读隔离级别下，采用了next-key lock避免幻读出现
    2、脏读 读取到了未提交的数据
    3、丢失更新  更新操作加排他锁来避免            

七、事务

 事务是访问并更新数据库的一个程序执行单元。
事务会把数据库从一种一致状态转换为另一种一致状。
在数据库提交工作时，可以确保其要么所有修改都已经保存了，要么所有修改都不保存。
InnoDB存储引擎中的事务完全符合ACID的特性。
ACID：
Atomicity 原子性: 数据库事务是不可分割的工作单位, 要么都做, 要么都不做.
Consistency 一致性: 事务不会破坏事务的完整性约束. 事务将数据库从一个一致状态转变为另一个一致状态
Isolation 隔离性: 事务之间相互分离, 在提交之前相互不可见.
Durability 持久性: 事务一旦提交, 其产生的效果就是永久的

 事务的隔离性由锁实现
原子性，一致性，持久性 由 redo log / undo log 实现
undo log 保证事务的一致性, 逻辑日志, 根据每行记录进行记录，帮助事务回滚及MVCC
redo log（重做日志）： 保证事务的原子性和持久性, 物理日志

1、redo log

    1、用于保证事务的原子性和持久性
    2、恢复提交事务修改的页操作
    3、重做日志刷新策略innodb_flush_log_at_trx_commit
        1、默认值1：表示事务提交一次就同步刷新一次
        2、0：表示同步刷新由主线程来进行
        3、2：事务提交仅仅写入重做日志缓冲中

2、undo log

    用于保证事务的一致性
    用于回滚到特性版本

3、隔离级别

READ UNCOMMITTED（读未提交）：事务隔离最低的级别，但是存在脏读的问题
READ COMMITED（读提交）：ORACLE和SQL SERVER默认的隔离级别，解决了脏读，但是一个事务多次读取的内容不同，出现了不可重复读的问题。
READ REPEATABLE(可重复读）：innodb引擎的默认事务隔离级别，解决了不可重复读的问题，但是产生了幻读，innodb通过Next-key lock解决了幻读。
SERIALIZABLE(可串行化）：通过强制事务排序解决幻读问题，会降低性能。可串行化中，InnoDB会对每个select语句后加上LOCK IN SHARE MODE，也就是为每个读加上共享锁。该级别主要用于分布式事务。

4、分布式事务

指允许多个独立的事务资源参与到一个全局的事务中。
全局事务要求在其中所有参与的事务要么都提交，要么都回滚。
InnoDB的隔离级别要设置为可串行化。
 分布式事务由一个或多个资源管理器（Resource Managers）,一个事务管理器（Transaction Manager）以及一个应用程序组成
资源管理器： 提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务中的所有资源管理器进行通信。
应用程序：定义事务的边界，指定全局事务中的操作。

八、备份与恢复

备份的方法：

1、Hot Backup 热备  Online Backup（在线备份）
Hot Backup 是指在数据库运行中直接备份，对正在运行的数据库没有任何影响。
2、Cold Backup 冷备 Offline Backup（离线备份）
Cold Backup 是指在数据库停止的情况下备份，这种备份只需要拷贝相关的数据库物理文件即可
3、Warm Backup 温备  
在数据库运行时进行，但是会对当前数据库的操作有所影响。加一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容：
逻辑备份
裸文件备份

按照备份数据库的内容来分：
完全备份
增量备份
日志备份
    日志备份主要是指对Mysql数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做来完成数据库的
	point-in-time 的恢复工作。
	mysql数据库复制（Replication）的原理就是异步实时进行二进制日志重做。

快照备份

 mysql数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。
备份的前提是将所有数据库文件放在同一文件分区中，
然后对该分区执行快照工作。支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统，Solaris的ZFS文件系统，
GNU/Linux的逻辑卷管理器（Logical Volume Manager、LVM ）等。
LVM是linux系统下对磁盘分区进行管理的一种机制。
LVM在硬盘和分区智商建立一个逻辑层来提高磁盘分区管理的灵活性。
LVM使用了写时复制（Copy-on-write）技术来创建快照。当创建一个快照时，仅拷贝原始卷里数据的元数据，
并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成后，原始卷上有写操作时，快照会跟踪原始卷块的改变，
将要改变的数据在改变之前拷贝到快照预留的空间里，因此这个原理的实现叫做写时复制。而对于快照的读取操作，如果读取的数据块
是创建快照后没有修改过的，那么会将读操作直接重定向到原始卷上；如果要读取的是已经修改过的块，则将读取保存在快照中的原始
数据。因此，采用写时复制机制保证了读取快照时得到的数据与快照创建时的数据是一致的。

1、复制的工作原理：

复制是Mysql数据库提供的一种高可用、高性能的解决方案。

复制的工作原理氛围以下三个步骤：
1、主服务器把数据更新记录到二进制日志中
2、从服务器把主服务器的二进制日志拷贝到自己的中继日志（Relay Log）中。
3、从服务器重做中继日志中的时间，把更新应用到自己的数据库上
其实就是完全备份和二进制日志备份的还原。

从服务器有两个线程：
一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志。
一个是SQL线程，复制执行中继日志。

2、快照+复制的备份架构

复制可以用来作为备份，但其功能不仅限于备份，其主要功能如下：
1、数据分布。 由于Mysql数据库提供的复制并不需要很大的带宽，因此可以在不通的数据中心之间实现数据的拷贝。
2、读取的负载均衡。 通过建立多个从服务器，可将读取平均地分布到这些从服务器中，从而减少主服务器的压力。
    一般可以通过DNS的Round-Robin和Linux的LVS功能实现负载均衡。
3、数据库备份。
4、高可用性和故障专业。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。