InnoDB

MySQL-InnoDB

架构

CheckPoint

已经被flush到页上的LSN。

刷盘策略

• 缩短数据库恢复时间
• 缓冲池不够用时，将脏页刷到磁盘
• 重做日志不够用时，刷新脏页

Sharp CheckPoint

• 关闭数据库时

• innodb_fast_shutdown=0

  • full purge
  • merge insert buffer
  • flush dirty page

• innodb_fast_shutdown=1

  • flush dirty page

• innodb_fast_shutdown=2

  • flush redo log only

Fuzzy CheckPoint

• Master Thread CheckPoint

• FLUSH_LRU_LIST CheckPoint

  • 保证约100个空闲页可用

  • Innodb1.1.x版本前

    • 在用户查询线程中查询空闲页，不够CheckPoint，会阻塞用户线程

  • MySQL5.6之后

    • 由Page Cleaner线程查询

      • innodb_lru_scan_depth修改空闲页数量

• Async/Sync Flush CheckPoint

  • 重做日志空间不足时
  • checkpoint_age = redo_lsn - checkpoint_lsn
  • async_water_mark = 75% * total_redo_log_file_size
  • sync_water_mark = 90% * total_redo_log_file_size
  • checkpoint_age < async_water_mark NO FLUSH
  • async_water_mark < checkpoint_age < sync_water_mark Async Flush
  • checkpoint_age > sync_water_mark Sync Flush
  • Inonodb1.2.x前，Async Flush阻塞查询线程，Sync Flush阻塞所有用户线程，MySQL5.6之后由Page Cleaner处理

• Dirty Page too much CheckPoint

  • innodb_max_dirty_pages_pct

后台线程

Master Threads

• loop

  • per seconds

    • 刷新日志缓冲（总是）

      即使事务还未提交，日志也会刷盘，可解释为什么很大的事务提交时间也很短（只需日志刷盘）

    • 合并插入缓冲（可能）

      前一秒IO次数小于5次

    • 刷新最多100个脏页（可能）

      buf_get_modified_radio_pct>innodb_max_dirty_pages_pct

    • 没有用户活动，切换到background loop（可能）

• background loop

  • 数据库空闲或shutdown时切换到此

IO Threads

• 处理异步IO

• write

  • innodb_write_io_threads

• read

  • innodb_read_io_threads

• insert buffer

• log

Purge Threads

• 回收undo页
• innodb_purge_threads

Page Cleaner Threads

• 刷盘脏页

内存

缓冲池

• innodb_buffer_pool_size

• 设置多个缓冲池

  • innodb_buffer_pool_instances

• information_schema.INNODB_BUFFER_POOL_STATS

• Insert Buffer\Change Buffer

  将索引叶子节点不在缓存池的插入操作缓存起来，暂时不合并到非唯一辅助索引的叶子节点，之后批量合并到叶子节点。

  Why 辅助索引：聚簇索引的插入往往是顺序递增的（如自增主键），不会产生随机IO，而insert buffer是用来将离散的插入操作合并提升性能的。

  Why 非唯一：插入唯一辅助索引时，需要将叶子节点读出判断索引是否唯一，此时叶子节点已被读入缓存池，插入操作便可直接合并了。

  Change Buffer对DML操作均可缓存。

  • 合并对非唯一辅助索引页的插入

  • Insert Buffer B+ Tree

    全局一颗B+树，存放在共享表空间。

    • 非叶子节点

      • space，表id
      • offset，页偏移

    • 叶子节点，change record

      • space，offset

      • metadata

        • record进入Insert Buffer的顺序

      • record data

        插入记录的各字段值。

  • Insert Buffer Bitmaps页

    • 追踪16384个辅助index page
    • index page容量
    • index page是否有record在B+树中
    • index page是否是insert Buffer B+的索引页

  • Merge Insert Buffer

    • 辅助索引页被读到缓冲池
    • 由bitmap追踪到index page容量不足（随机选择index page检查）
    • Master Thread发起

List

• LRU List

  • midpoint

    为了防止大批量、遍历操作，如select * from table这种操作淘汰掉LRU列表所有热点数据，MySql将页面读入内存时，先插入的midpoint处，淘汰掉列表尾的节点，这样可以防止批量操作淘汰midpoint之前的数据（真正的热点数据），同时，提供innodb_old_blocks_time参数，设定midpoint之后的页面，在加入LRU列表指定时间之后，移动到midpoint之前

    • 5/8
    • innodb_old_blocks_pct
    • innodb_old_blocks_time

  • unzip LRU Page?

• Free List

• Flush List

  • 存放LRU中被修改过的页，checkpoint 刷盘

redo log buffer

• innodb_log_buffer_size
• Master Threads每秒Flush
• 事务提交后Flush
• 剩余空闲小余1/2时Flush

other buffer

• 缓冲池的帧缓冲

• 缓冲控制对象

  • LRU、锁、等待等信息

double write

先将脏页memcpy到共享表空间128个连续页，刷盘

再将脏页刷盘

写到一半宕机，根据连续页数据恢复

为什么redoLog不能恢复？

当物理页损坏时，redolog只记录了物理页的更改，无法恢复，但doublewrite的共享表空间buffer记录了页的完整备份。

若下层存储如LUN或FS提供了此功能，数据库中应关闭

文件

数据库文件

• 日志文件

  • 错误日志（err log）

    show variables like 'log_error';

  • 二进制日志(binlog)

    数据库日志，记录包括Innodb存储引擎在内的所有存储引擎产生的日志记录。
    Innodb重做日志文件只记录Innodb存储引擎事务产生的事务日志。

    • STATEMENT

      记录SQL语句，在SQL语句中存在类似随机函数时，远程复制从端会和主端数据不一致。

    • ROW

      记录表行的更改情况，还是逻辑更改，不同于重做日志的记录页的物理更改，可解决STATEMENT的远程复制主从数据不一致问题。

    • MIXED

      在会产生数据不一致的函数调用时用ROW，其他用STATMENT。

    • 记录Mysql事物的逻辑更改

    • 通过拷贝二进制日志同步数据到slave

    • 事务提交前写盘，无论事务多大

      sync_binlog=[N]
      每缓冲写N次，就刷一次盘；
      当N=1时，将不使用操作系统的缓冲来写二进制日志。

  • 慢查询日志(slow query log)

    show variables like 'long_query_time';
    show variables like 'log_slow_queries';
    show variables like 'log_queries_not_using_indexes';

    mysql.slow_log

  • 查询日志(log)

    记录所有对MySQL数据库请求的信息，无论请求是否得到了正确的执行。

Innodb文件

• 重做日志

  • 记录Innodb事物对每个页的物理更改

  • 事务进行过程中不断将redo entry写盘

  • 格式

    • redo_log_type
    • space
    • page_no
    • redo_log_body

  • 按最小单位扇区写入，保证写入成功，不需double write

  • innodb_flush_log_at_trx_commit

    0：事务提交时不写入重做日志，仅由Master Thread每秒fsync，未写入文件系统缓存，数据库宕机就会丢失日志。
    1：事务提交时必须fsync重做日志（默认）。
    2：事务提交时，仅将重做日志写入文件系统缓存，若数据库宕机，重做日志不会丢失，操作系统宕机时，丢失Master Thread还未fsync的日志。

  • 物理结构

    LOG_BLOCK_HDR_NO：大概是log block在Redo Log Buffer这个数组中的下标。LOG_BLOCK_HDR_DATA_LEN：记录log block写入了多少字节数据。LOG_BLOCK_FIRST_REC_GROUP:记录log block中第一个事务日志的偏移。LOG_BLOCK_CHECKPOINT_NO：该log block最后一次被写入时的check point第4字节值。
    LOG_BLOCK_TRL_NO:同LOG_BLOCK_HDR_NO。

表

innodb逻辑存储结构

• 表空间

  • 共享表空间

    • 段

      • 数据段

        • 32碎片页

          使用完后按区分配空间

        • 区

          • 1MB/64pages

          • 数据页 B-tree Node

            • 每页最多16KB/2-200行，最少2行

            • File Header

              38字节
              记录页的头信息

              • checksum

                4字节

              • page offset

                4字节
                查询(space,1)->(10,1)
                查询表空间id为10的第二个页

              • prev page

                4字节
                当前页的前一个页

              • next page

                4字节
                当前页的下一个页

              • LSN

                8字节
                当前页最后被修改的日志序列位置LSN

                LSN（重做日志中）：事务被写入到重做日志的总量，单位字节。
                LSN（页中）：表示该页最后flush时的LSN大小。
                数据库启动时，若检测到重做日志中的LSN大于页中的LSN，并且重做日志中大于的部分，事务已经提交，则需将该部分事务应用到页，以恢复。

                Log sequence number，日志序列号，不断随着事务被记录到重做日志，而更新重做日志的LSN。
                Log flush up to,已经被记录到重做日志的Log sequence number。
                Last checkpoint at，对应页中LSN。

              • page type

                2字节

              • flush LSN

                8字节
                仅在系统表空间的一个页中定义，代表文件至少被更新到该LSN，独立表空间中值为0

              • space id

                4字节

            • Page Header

              56字节
              记录页的状态信息

              • dir slots

                2字节
                在Page Directory中的slot数

              • heap top

                2字节
                堆中第一条记录的指针，记录在页中以堆得形式存放（X）
                代表空闲空间的开始位置偏移量

              • N heap

                2字节
                堆中的记录数，第15位表示行记录格式

              • free

                2字节
                指向可重用空间的首指针

              • garbage

                2字节
                已删除记录的字节数，对应记录结构中delete flag 为1的记录总大小

              • last insert

                2字节
                最后插入记录的位置

              • direction

                2字节
                最后插入的方向
                PAGE_LEFT
                PAGE_RIGHT
                PAGE_SAME_REC
                PAGE_SAME_PAGE
                PAGE_NO_DIRECTION

              • N direction

                2
                一个方向连续插入记录的数量

              • N recs

                2字节
                该页中记录的数量

              • max trx id

                8字节
                修改该页的最大事务号

              • level

                2字节
                该页在索引树中的层级

              • index id

                8字节
                记录当前页属于哪个index

              • btr seg leaf

                10字节
                数据页非页节点所在段的
                segment header，仅在B+树的root页定义

              • btr seg top

                10字节
                数据页所在段的segment header

            • Infimun and Supremun Records

              页创建时建立，用于限定页中记录的边界。

            • User Records

              • 行

                • Compact

                  • 变长字段长度列表

                    变长字段小于255字节，长度为1字节，大于255字节，对应长度2字节，变长字段最大2字节，对应varchar最长65535字节

                  • NULL标志位

                    1字节

                  • record header

                    5字节

                    - （）
                    - （）
                    - deleted_flag

                    1bit
                    该行是否被删除
                    

                    - min_rec_flag

                    1bit
                    该行是否被定义为最小的记录
                    

                    - n_owned

                    4bit
                    该记录拥有的记录数
                    作为directory slots
                    

                    - heap_no

                    13bit
                    索引堆中该条记录的排序记录
                    

                    - record_type

                    3bit
                    记录类型
                    000普通
                    001B+树节点指针
                    010Infimum
                    011Supremum
                    1xx保留
                    

                    - next_record

                    16
                    页中下一条记录的相对位置
                    

                  • 事务ID
                    - 回滚指针
                    - 列1数据
                    - 列2数据...
                    - 行溢出数据

                    varchar BLOB TEXT

                    当不能保证一个page不能至少存放两条记录时，产生行溢出数据，数据页中存放前缀数据，其余数据存放在BLOB page。

                    page至少存放两条记录，防止B+树退化成链表

                    - 768prefix前缀数据
                    - 偏移量

                • Redundant

                • Compressed

                  • 对BLOB采用完全行溢出

                    行数据中只存指针,实际数据都存在Off Page

                  • 行数据采用zlib算法压缩

                • Dynamic

            • Free Page

              • 空闲空间

                记录被删除后被放到空闲链表

            • Page Directory

              • Directory Slots

                B+树的目录槽

                非数据页，目录槽存放键值范围，及范围第一个记录的页偏移，记录中存放键值及数据页的页号，通过页号找到数据页

                数据页，记录records的页偏移，通过目录槽追踪（二叉查找）到记录查询结果的页，将页读入内存，然后在内存中，再通过recorder header的next_header来找到最终结果

                聚簇索引并非按索引的顺序严格存放物理记录，否则会带来巨大的维护开销，数据页和页中的记录，都是通过双向链表维护顺序的。

            • File Trailer

              8字节

              • FILE_PAGE_END_LSN

                8字节

                前4字节 checksum

                后4字节 同File Header中的FILE_PAGE_LSN
                为了检测页是否完整写入磁盘

      • 索引段

        • B-tree Node level

      • 回滚段（rollback segment）

        Innodb 1.1之后，最大支持128个rollback segment，每个rollback segment包含1024个undo log segment，所以最大并发事务为128 * 1024.

        • undo log segment

          • undo页

        • 1024个 undo log segment

      • 其他段

        • 系统页
        • 事务数据页
        • Insert Buffer Bitmap
        • Insert Buffer Free List
        • UnCompressed BLOB Page
        • Compressed BLOB Page

  • innodb_file_per_table

    • 数据段
    • 索引段
    • insert buffer bitmap页

Index

存储引擎选择不使用索引的原因

当使用辅助索引不能返回所有需要查询的列时，会通过辅助索引查到主键值，在通过主键值使用聚集索引查找完整列，由于经过了辅助索引后，再访问聚集索引，在需要查询的记录量比较大时，会产生大量随机IO，在机械盘的情况下，性能会大大降低，这时优化器可能选择直接遍历聚集索引（顺序IO）。

但在需要查询的记录量比较小时，可能依然先使用辅助索引，或者在SSD盘的情况下，用户可设置先使用辅助索引。
select * from table force index(.)...

MRR优化

在通过辅助索引查询大量记录的时候，MRR优化先将辅助索引查询得来的主键ID排序，再通过主键ID顺序查询记录整行记录，以此将随机IO转换为顺序IO；
另外，MRR可以将范围查询拆分为键值对查询，如 select * from t where idx1 >= 1000 and idx1 << 2000 and idx2 = 10000;
其中(idx1,idx2)为联合索引。
MRR将会过滤掉idx2 = 10000的数据，只取出满足查询条件的主键ID页，否则将先返回满足idx1条件的数据，再在内存中过滤掉idx2条件。。。

Index Condition Pushdown

Mysql5.6之后将Index Filter 过滤条件在innodb存储引擎索引层面过滤，5.6之前是只使用Index first key和Index last key查询记录，再将记录返回到server层过滤。

锁

Innodb根据事务访问的每个页对锁进行管理，采用位图的方式，锁住一页中的一条记录和多条记录开销差不多。

自增长列

• AUTO-INC Locking

  为了自增长列++互斥的锁，在完成插入自增长列的语句之后释放锁。

• Mutex

  使用互斥量来互斥内存中自增长计数累加的过程；
  在statment-based方式的replication：
  对于simple inserts,插入的记录数确定，并发情况下，插入记录的自增列主从一致；
  对于bulk inserts，插入的记录数不确定，并发插入时，可能出现主从自增列不一致。

• 插入类型

  • insert-like

    所有插入类型

  • simple inserts

    能确定插入行数
    如insert

  • bulk inserts

    不能确定插入行数
    如 insert...select...

  • mixed-mode inserts

    插入的记录有些自增长列是确定的，有些是自增长的。

• innodb_autoinc_lock_mode

  0：AUTO-INC Locking
  1：simple inserts 使用mutex互斥预分配插入条数对应的自增列；其它使用AUTO-INC Locking。
  2:所有插入语句都使用mutex，并发性能提高，但在replication下会出现主从自增列不一致问题。

外键

对子表插入记录时，会先select父表，查看父表是否存在相应的外键值，此时select不再读快照，而是采用select ... lock in share mode的方式，若此时父表相应外键已经被加了X锁，则子表的插入阻塞。

这样是为了防止死锁，若select 父表采用快照读，则会读到已经加了X锁记录的快照，然后完成对子表的插入，之后加X锁的事务再修改外键，造成子表父表外键对应不上。

调优

CPU

OLTP应用属于IO密集型，采购设备应更关注IO能力强的配置。
根据CPU支持的核数，设置数据库线程（purge,IO等）的数量，由于是IO密集型，可适当调整线程为核的两倍。

内存

预估数据库“活跃”数据量。
判断内存是否达到瓶颈：
缓冲池命中率>99%

硬盘/SSD

RAID/阵列

测试工具

XMind - Trial Version