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

各引擎比较

特征MyISAMBDBMemoryInnoDBArchiveNDB
容量限制64TBNOYES
事务
锁粒度
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存储引擎,不同的存储引擎区别,Innodb存储引擎,多线程模型,主线程工作方式
//我随便找了个默认是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'//00 表示关闭数据库时要完成所有的full purge,merge insert buffer,所有脏页刷回磁盘,最后再关闭
    相当于7仔店关门前,要做大扫除.
    1 不需要full purge,merge insert buffer,但是脏页要刷回磁盘,最后再关闭
    2 将日志写入日志文件,下次启动MYSQL时再恢复 (RECOVERY),相当于有急事,拉闸关门明早再说
    宕机也会触发RECOVERYshow 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 logbinlog 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
    检测页是否已经完整地写入磁盘