[Mysql技术内幕]Innodb存储引擎

简要说说不同的存储引擎区别,主要说说INNODB

一. 其他引擎

MyISAM

  • 不支持事务,表锁,支持全文索引
  • Mysql5.5.8版本以前是默认存储引擎,之后被无情抛弃
  • 存储引擎表由MYD,MYI组成,MYD用来存放数据,MYI存索引
  • 可以使用myisampack工具进一步压缩数据文件,使用哈夫曼编码静态算法来压缩数据
  • Mysql5.0以前表容量只支持4GB,之后为256TB
  • 只缓存索引文件,文件的缓存交给操作系统自身完成

NDB

  • 是一个集群存储引擎,数据全部存在内存中,主键查找速度极快,JOIN操作查询速度很慢

Memory

  • 将表中的数据存放在内存中,如果数据库重启或崩溃,数据将丢失
  • 非常适合存储临时数据表,以及经纬度表
  • 默认使用哈希索引,而不是B+Tree索引
  • 只支持表锁,并发性能较差,不支持TEXT BLOB
  • varchar是按照char方式存储,浪费内存

Archive

  • 只支持INSERT SELECT操作
  • 使用zlib算法将数据行进行压缩后存储,压缩比1:10
  • 非常适合存储归档数据,如日志信息

Federated

  • 不存放数据
  • 指向远程MYSQL数据库的某个表

Maria

  • 升级版的MyISAM 支持了行锁,MVCC,事务,BLOB

各引擎比较

特征 MyISAM BDB Memory InnoDB Archive NDB
容量限制 64TB NO YES
事务
锁粒度
MVCC
地理空间
BTREE索引
哈希索引
全文索引
聚簇索引
数据缓存
索引缓存
压缩数据
加密数据
存储成本 非常低
内存成本 中等
批量插入速度 非常高
集群
主从复制
外键
备份/定点恢复
查询缓存
更新统计信息

二. INNODB

线程模型

Innodb是多线程模型

  • Master Thread:主线程,负责缓冲池异步刷新到磁盘,脏页刷新,合并插入缓冲,UNDO页回收
  • IO Thread: insert buffer,log IO thread,4个write,4个read
  • Purge Thread: 回收undo页,支持多个县城
  • Page Cleaner Thread: 脏页刷新,从Master独立出来,提高查询性能

缓冲池

[Mysql技术内幕]存储引擎

//我随便找了个默认是0.5G
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'
  • 可以缓存索引页,数据页(前两个占大部分),undo页,插入缓冲,自适应哈希索引,锁信息,数据字典信息
  • 允许多个缓冲池实例,每个页根据哈希值平均分配到不同缓冲池,增加并发处理能力,
    //默认8
    SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'
    //查看缓冲池状态
    SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;
  • LRU算法管理缓冲池,频繁使用的页在最前面,使用少的在尾部,缓冲池满了之后,优先释放尾部数据
  • InnoDB对LRU算法进行了优化,加入了innodb_old_blocks_pct中间点概念,新的数据会插入LRU列表的37/100处,新的数据不一定是热点数据,如果让一次性的,数据量非常大的数据放在LRU头部冲走热点数据就很不爽,可以适当调节这个值以适应热点数据.
    show VARIABLES like 'innodb_old_blocks_pct'    //返回37
  • 查看缓冲池状态
    SHOW ENGINE INNODB STATUS
    ###################################
    Dictionary memory allocated 8904297
    Buffer pool size   32767#共有32767个页
    Free buffers       1024    #空闲区页的数量
    Database pages     31564#LRU列表中页的数量
    Old database pages 11631
    Modified db pages  0  #脏页页数
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 50762857, #LRU列表页一定要前端的次数
    not young 711064607##LRU列表页一定要尾部的次数
  • redo log buffer 重做日志缓冲区
    重做日志的定义:事务提交的时候,先写重做日志,再去修改页,如果发生宕机,可以通过重做日志修复数据
    show VARIABLES like 'innodb_log_buffer_size'    //默认8M

Check Point 技术

  • 将缓冲区的脏页刷回磁盘
  • 有点复杂 大概就是缓冲区和磁盘同步数据的一种技术 宕机也能恢复那种
  • 有好多种情况触发这个东西

Master Thread 主线程工作方式

  • 主循环
  • 后台循环
  • 刷新循环
  • 暂停循环

关键特性

  • 插入缓冲 Insert Buffer
    对于非聚簇索引进行插入和更新操作时,不是每一次都直接插入索引页中,而是先判断操作的非聚簇索引页是否在缓冲池中,若在,则直接插入,若不在则放到Insert Buffer中,延时刷入磁盘,大大的增加插入更新操作的性能
    唯一索引不会插入Insert Buffer
    Master Thread定时把缓冲区数据刷回磁盘
  • 两次写
    doublewrite buffer 避免宕机造成的数据丢失问题
  • 自适应哈希索引
    哈希索引的时间复杂度是O(1),而B+Tree索引一般为3-4层,
    如果观察到建立哈希索引可以带来速度提升,则全自动建立哈希索引,称之为自适应哈希索引,默认开启
    哈希索引只允许等值搜索查询,范围查找是不能使用哈希索引的
  • 异步IO Aysnchronous IO 即AIO
    如果一条SQL需要扫描多个索引页,也就是进行多次IO操作,AIO进行IO Merge操作,将多个IO合并成1个IO,提高性能
  • 刷新邻接页
    当刷新一个脏页到磁盘时,顺手把隔壁的脏页也刷新了,
    默认开启,如果磁盘是固态硬盘则建议设置为0关闭此特性
    show variables like 'innodb_flush_neighbors'

    启动,关闭,恢复

    show variables like 'innodb_fast_shutdown'//0
    0 表示关闭数据库时要完成所有的full purge,merge insert buffer,所有脏页刷回磁盘,最后再关闭
    相当于7仔店关门前,要做大扫除.
    1 不需要full purge,merge insert buffer,但是脏页要刷回磁盘,最后再关闭
    2 将日志写入日志文件,下次启动MYSQL时再恢复 (RECOVERY),相当于有急事,拉闸关门明早再说
    宕机也会触发RECOVERY
    show variables like 'innodb_force_recovery'//0
    恢复的等级
    0 进行所有的恢复操作,如果恢复不了,报错写入日志
    3 不进行事务回滚
    6 不进行任何回滚
    假如事务执行了几十万条,COMMIT之前宕机了,那么开机要对这几十万条数据进行回滚,可能要几个小时说不定,这个时候就要设置innodb_force_recovery等级为3,才能光速开机,然后再重新处理这几十万条数据

日志文件

错误日志

当MYSQL无法正常启动时,来查看这个文件,有时会有警告,可能还能得到一些优化的帮助

show variables like 'log_error'

慢查询日志 slow log

##全局修改
set global slow_query_log='ON'; //开启慢查询
set global long_query_time = 1; //设置阈值,大于1秒记录
set global log_output = 'TABLE' //默认是FILE,改成表好查一点,如果要被日志系统收集,建议还是默认FILE,设置完之后可以在mysql.slow_log查到日志
set global log_queries_not_using_indexes = 'ON'//不使用索引的记录到慢查询,开启之后数据量有点大,而且slow_log表里面貌似没有字段标志出是因为没走索引而记录
set global log_throttle_queries_not_using_indexes ##每分钟允许记录到slow_log的且未使用索引的SQL语句次数

mysql.slow_log 默认使用CSV引擎,可以改成MyISAM提高查询性能

查询日志

set global general_log = 'ON'  //默认关闭, 开启之后记录所有SQL 对性能有影响
set global log_output = 'TABLE' //慢查询日志和查询日志共用log_output,设置完之后可以在mysql.general_log查到日志

二进制日志 binary log

除了SELECT和SHOW操作,其他对数据库执行更改的所有操作,开启binlog会造成1%的性能损失,但是好处多多,主要有以下几种作用

  • 恢复 可以进行point-in-time恢复
  • 复制 主从复制
  • 审计 判断是否被注入攻击
    相关配置参数
  • max_binlog_size
    单个binlog的最大值,超过该值会产生新的二进制日志文件,后缀名+1,并记录到.index文件,默认1G
  • binlog_cache_size
    默认大小32KB,二进制日志占用缓冲区的大小,每个线程开启一个事务的时候,都会在缓冲区分配32KB,如果设置太大,同一时间多个事务发起的时候,非常消耗缓冲区的内存,如果设置太小,溢出的日志将会写到临时文件中
  • sync_binlog
    二进制日志默认先写到缓冲区,sync_binlog = N表示每写缓冲N次刷入磁盘的二进制文件中,如果使用InnoDB进行主从复制,想保证万无一失就把这个值设置为1,设置为1也是有问题的,比如一个事务COMMIT之前,二进制已经写入的磁盘中,然后宕机,重启之后事务回滚,但是不会回滚二进制日志,这个问题可以通过innodb_support_xa=1来解决
  • binlog-do-db
    决定哪些库需要保存二进制日志
  • binlog-ignore-db
    决定哪些库不需要保存二进制日志
  • log-slave-update
    从库不会将从主库取得的二进制日志写入自己的二进制日志中,除非这个值等于1,设置为1就可以实现Master=>slave=>slave 的架构
  • binlog_format
    statement 记录sql语句
    row 记录每一行被修改的记录 对磁盘空间要求大 传输binlog网络开销也大 推荐使用这个,为数据库恢复和复制带来更好的可靠性
    mixed 上面两种都有

表结构空间文件

  • .frm 存储表结构和视图文件 (MYSQL8已经去除了frm文件)
  • .ibd 独立表的数据文件
  • ib_logfile0 & ib_logfile1 InnoDB重做日志文件

    重做日志 redo log

    binlog VS redolog, binlog是mysql层的日志,redolog是innodb存储引擎层的日志,记录的是每个页的更改物理情况

索引组织表

  • 如果没有定义主键,Innodb会选建表时候定义的第一个非空单列唯一索引当主键
    SELECT _rowid FROM `table`    //查询主键列的值

表空间 段 区 页 行

  • 表空间
    如果启用了innodb_file_per_table,每张表内的数据单独放到一个表空间,存放数据,索引和插入缓冲bitmap页,其他undo信息,插入缓冲索引页,事务信息,二次写缓冲还是放外面的共享表(ibdata1)中

  • 常见有数据段,索引段,回滚段

  • 连续页组成的空间,每个区大小都是1MB

  • 页可以设置成16kb 8kb 4kb 2kb
    数据页,undo页,系统页,事务数据页,插入缓冲位图页,插入缓冲空闲列表页,未压缩的二进制BLOB,已压缩的BLOB

  • 每页只能存7992行记录

行记录格式

  • compact redundant
    原始的行记录格式,过时了
  • compressed dynamic
    MYSQL5.7之后默认的记录格式是dynamic
    compact处理blob,text时,只存前面的768个字节,剩下数据另外找页放
    dynamic处理blob,text时,数据行只存放指针,实际数据放在off page中
    compressed是dynamic 的zlib算法压缩版 用cpu算力换磁盘容量变小,据说能节省40%的空间

char 行结构存储

char指的是字符长度,非字节长度
在InnoDB存储引擎内部,不同编码下的字符占用的字节不同,英文占用一个字节,utf-8最长占用四个字节,所以对char(10)来说,可以存储10个英文字母,也可以存储10个中文汉字,所以char和varchar的实际行存储基本是没有区别的

InnoDB数据页结构

  • File Header 38字节
    FIL_PAGE_OFFSET:表空间页的偏移值,假如表空间1G,大小为16KB,那么总共有65536个页,FIL_PAGE_OFFSET表示该页在所有页中的位置
    FIL_PAGE_PREV:当前页的上一个页
    FIL_PAGE_NEXT:当前页的下一个页
    FIL_PAGE_TYPE:InnoDB引擎页的类型,可以是叶节点,undolog,索引节点,insert buffer等
    FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID:属于哪个表空间
  • Page Header
    记录数据页的状态信息
  • Infimun和Supermum Records
    限定记录的边界
  • User Records
    数据部分
  • Free Space
    空闲部分
  • Page Directory
    页的相对位置
  • File Trailer
    检测页是否已经完整地写入磁盘

读书遇到瓶颈 先去恶补其他知识回来再战

本文持续更新修正

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!