简要说说不同的存储引擎区别,主要说说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

各引擎比较

二. INNODB

线程模型

Innodb是多线程模型

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

缓冲池

//我随便找了个默认是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
• 检测页是否已经完整地写入磁盘

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

本文持续更新修正