一.MySQL主要物理文件有
(1)Redolog重做日志文件
(2)Undolog回滚日志文件
(3)Binlog二进制日志文件
(4)Relaylog中继日志文件(主从复制、slave才有)
(5)Errorlog错误日志文件
(6)Querylog全查询日志文件
(7)Slowlog慢查询日志文件
(8)Data 数据文件(表结构文件/表数据文件)

1.参数文件 my.cnf :
mysq实例启动的时候在哪里可以找到数据库文件,并且指定某些初始化参数,这些参数定义了某种内存结构的大小等设置,
Mysq实例启动时,会先读取配置参数文件my.cnf

2.日志文件 :
记录mysq对某种条件做出响应时候写入的文件,记录了影响mysql数据库的各种类型活动,
常见的日志文件有错误日志、二进制日志、慢查询日志、全查询日志、redo日志、undo日志。
(1)错误日志:错误日志对mysql的启动、运行、关闭过程进行了记录问题分析必看。
(2)二进制日志:记录了对数据库进行变更的操作,但是不包括 select操作以及show操作。主要用于恢复(recovery)、复制(replication)。
(3)慢査询日志:记录运行较慢的sql语句信息,给sql语句的优化带来很好的帮助。
(4)全查询日志:记录mysql所有的请求,数据库审计加问题排查跟踪性,损失3%-5%性能。
(5)Redo日志:数据库都是日志先行,先写日志,再写数据文件,保证恢复与完整性,这个对 innodb存储引擎非常重要,因为它们记录了对于innodb存储引擎的事务日志。
(6)Undo日志:里面存储了与redo相反的数据更新操作,如果 rollback的话,就把undo段里面数据回写到数据文件里面。
(7)Redo与undo他们并不是各自独立没有关系的,他们是有关联的,交替合作来保证数据的一致性和安全性。

3.Socket文件 :
当用linux的mysql命令行窗口本地localhost登录的时候需要的文件, 可以用参数 socket控制,一般默认在/tmp目录下

4.Pid 文件 :
mysql实例的进程文件,当mysql实例启动的时候,会将自己的进程id写入文件中,该文件即为pid文件,由参数 pid_file控制,默认路径位于数据库目录下

5.mysql 数据文件 :
存放mysql表结构定义文件,如:*.frm
存放mysql表数据的文件,如:*.ibd 。
注意innodb没有索引文件 *.myi

6.存储引擎文件 :
记录存储引擎信息的文件,innodb存储引擎在存储设计上模仿了oracle,该文件就是默认的表空间文件,可以用多个文件组成一个表空间,例如:ibdata*文件

二.mysql数据库与实例的关系
Oracle 是多进程,而mysql是单进程多线程 。
mysql实例,后台进程+内存结构,mysql实例在系统上的表现就是一个进程,实例才是真正用于操作数据库文件的。
mysql:1个实例对应1个数据库或者1个实例对应多个数据库。
ORACLE:1个实例对应1个数据库或者多个实例对应1个数据库。
Oracle数据库中,一个会话对应一个连接进程。(进程)
mysql 数据库中,一个会话对应一个连接线程。(线程)
mysql不存在实例名,服务名,只有数据库名,如果在一台服务器上存在多个mysql实例,只有通过端口来区分不同实例之间的存在

三.存储引擎
概念:mysql存储引擎称为表类型(说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法)
mysql用得比较多存储引擎:Myisam、 Innodb、 memory。
注:5.7以后主要用Innodb

1.MYISAM存储引擎(了解):
这种存储引擎不支持事务,不支持行级锁(支持表锁),只支持并发插入的表锁,主要用于高负载的 select
支持3种不同的存储格式,分别是:静态表、动态表、压缩表静态表。
(1)静态表:
表中的字段都是非变长字段,这样每个记录都是固定长度的,优点存储非常迅速,容易缓存,出现故障容易恢复;
缺点是占用的空间通常比动态表多(因为存储时会按照列的宽度定义补足空格)。
(2)动态表:
记录不是固定长度的,这样存储的优点是占用的空间相对较少。
缺点∶频繁的更新、删除数据容易产生碎片,需要定期得对表进行优化/检査/修复
(3)压缩表:
因为每个记录是被单独压缩的,所以只有非常小的访问开支

2.memory存储引擎:
使用存在于内存中的内容来创建表。
每个 memory表只实际对应一个磁盘文件,格式是 .frm ;
该文件只存储表的结构,而其数据文件,都是存储在内存中,这样有利于对数据的快速处理,提高整个表的处理能力。
memory因为它的数据是放在内存中的,但是一旦服务关闭,表中的数据就会丢失掉

3.INNODB存储引擎(重点):
该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全,支持行级锁、使用了B+ Tree索引、支持自动增长列,支持外键约束。
但是对比MYISAM引擎,写的处理效率会差一些,并且会占用更多的磁盘空间以保留数据和索引。
memory存储引擎默认使用哈希(HASH)索引,其速度比使用B+ TREE索引要快。
Hash索引结构其检索效率非常高,索引的检索可以一次定位。
B-Tree索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问。
所以Hash索引的查询效率要远高于B-Tree索引。
虽然Hash索引效率高,但是Hash索引本身由于其特殊性也带来了很多限制和弊端,功能有限,支持也有限,一般生产环境都是使用B-TREE索引
注:在有限的范围内表达无限的场景,这就是hash

四.InnoDB逻辑存储结构
1.mysql 逻辑存储结构是 表空间 –> 段 –> 区 –> 页。
表空间: 所有的数据都放在表空间里面。
段: 表空间有若干个段组成,常见的有数据段/索引段/回滚段等。
区: 每64个连续的页组成区,一个区大小为1M。
页: 页是InnoDB磁盘管理的最小单元,固定大小为16K。

2.表空间
表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都是存放在表空间中
默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有数据都放在这个表空间内
对于启用了innodb_file_per_table的参数选项,需要注意的是,每张表的表空间内存放的只是数据。
对于索引和插入缓冲,其他类的数据,如撤销(Undo)信息、系统事务信息、二次写缓冲(double write buffer)等还是存放在原来的共享表空间内。
这也就说明了另一个问题:即使在启用了参数innodb_file_per_table之后,共享表空间还是会不断地增加其大小
修改表空间管理方式:
(1)修改innodb_file_per_table这个参数即可
on表示为独立表空间;off表示为共享表空间
(2)修改innodb_file_per_table的参数值即可
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间
注:但是修改无法影响之前已经使用过的共享表空间和独立表空间
show variables like innodb_file_per_table; –查看当前表空间管理方式
show global variables like ‘%innodb_file_per_table%’;–查看独立表空间
show global variables like ‘%innodb_data_file_path%’;–查看共享表空间
注:物理文件为ibdata文件,默认情况下ibdata存放InnoDB表(InnoDB数据字典)元数据、undo logs、the change buffer, and the doublewrite buffer

3.段(segment)
主要的段有
(1)Leaf node segment :叶子节点段,这个段是专门存放B+树叶子节点的段,而我们都知道在B+树中叶子节点保存的都是数据,所以该段也称数据段。
(2)Non-Leaf node segment : 非叶子节点段,这个段中存放着B+树的非叶子节点,也即索引,所以也称索引段。
(3)Rollback segment : 回滚段,用来临时的保存当数据库数据发生改变时的先前值

4.区(Extent)
​区是由连续的页组成的空间,一个区中默认存放着64个page,每个page默认是16KB,所以默认情况下,一个区的大小为1MB。
一次性从磁盘申请45个区,也就是说,每一次ibd文件增长,一次增长45MB,避免了区在磁盘中存在碎片化

5.页(page)
​页是InnoDB中磁盘读写的最小逻辑单位,默认是16KB,而一个数据页就是一个B+树的叶子节点(B+ Tree Node)
主要的页有:
(1)数据页(B-tree Node)。
(2)Undo页(Undo Log Page)。
(3)系统页(System Page)。
(4)事务数据页(Transaction system Page)。
(5)插入缓冲位图页(Insert Buffer Bitmap)。
(6)插入缓冲空闲列表页(Insert Buffer Free List)。
(7)未压缩的二进制大对象页(Uncompressed BLOB Page)。
(8)压缩的二进制大对象页(Compressed BLOB Page)。

6.行(Row)
InnoDB表中的数据按行存储,InnoDB存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放,
每个页存放的行记录也是有硬性定义的,最多允许存放16KB/2~200行的记录,即7992行记录

五.InnoDB存储引擎体系架构
主要分为4大块,9小块
1.Buffer Pool :Innodb Buffer Pool 可以缓存索引数据,还会缓存表的数据,而且完全按照数据文件中的数据快结构信息来进行缓存,
这一点和 Oracle Sga中的 buffer cache非常类似
(1)Undo page:事务提交/回滚处理,保证事务的一致性和完整性,还有进行旧版本的页删除操作

(2)change buffer/insert buffer page:是mysql5.5加入的新特性,change buffering是insert buffer的加强,insert buffer只针对insert有效,
change buffering对insert、delete、update(delete+insert)、purge都有效
工作原理
当修改一个索引块(secondary index)中的数据时,索引块在buffter pool中不存在,修改信息就会被cache在change buffer中,
当通过索引扫描把需要的索引块读取到buffer pool时,会和change buffer中修改信息合并,再择机写回disk。目的还是为了减少随机IO带来性能损耗

(3)Adaptive Hash Index :InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引
注:自适应哈希索引通过缓冲池的B+树构造而来,因此建立的速度很快。而且不需要将整个表都建哈希索引

(4)Index Page : 对记录的操作缓存,主要有3种操作记录查找、插入、删除,是对数据操作的一个缓存,正真保存数据页的缓存区

(5)Lock Info : 缓存InnoDB 存储的锁信息

(6)Data Dictionary : 缓存一些数据字典信息,例如表结构这些

2.Additional Memory Pool : 附加内存池
用来保存数据字典信息和其他内部数据结构的内存池的大小,初始化为8M,数据库中的表数量越多,该缓冲区会占得空间越大,
如果 Innodb用完了内存池中的内存,就会从操作系统中分配内存

3.Redo Log Buffer : redo entries –> redo log buffer –> 重做日志文件。
innodb_log_buffer_size的大小(默认16M),将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中的3种情况:
(1)Master thread每秒将重做日志缓冲刷新到重做日志文件;
(2)每个事务提交时会将重做日志缓冲刷新到重做日志文件;
(3)当重做日志缓冲池剩余空间小于1/2时,重做日志缓冲刷新到重做日志文件;
select @@innodb_log_buffer_size / 1024 / 1024 MB;–查看重做日志缓冲区大小

4.Binlog Buffer : 二进制日志缓冲区
主要用来缓存由于各种数据变更操做所产生的 Binary Log信息,为了提高系统的性能,mysql并不是每次都是将二进制日志直接写入 Log File,
而是先将信息写入 Binlog Buffer中,当满足某些特定的条件之后再一次写入 Log File文件中。
二进制日志和重做日志的对比:

–类别区分:
二进制日志 :记录mysql数据库相关的日志记录,包括 INNODB、Myisam等其它存储引擎的日志。
重做日志 :只记录innodb存储引擎本身的事务日志。

–内容区分:
二进制日志 :记录事务的具体操作内容,是逻辑日志。
重做日志 :记录每个页的物理更改情况。

–时间区分:
二进制日志 :只在事务提交完成后进行写入,只写磁盘一次,不论这个事务量有多大。
重做日志 :在事务进行中,就不断有重做日志条目 (redo entry)写入重做日志文件

5.Double Write : 是InnoDB表空间 ibdata中一块连续的128 page * 16k = 2M的存储空间
InnoDB用户脏刷数据,是先把数据刷到ibdata的Double Write Buffer的区域中,然后Double Write再刷到数据文件中,
它的作用的是处理产生 partial write(部分写入)时候的 data recovery
注意:Double Write不是内存区域,而是物理文件ibdata表空间中的区域,相当于是把磁盘当内存使用
double write的机制:
(1)data page1从idb数据文件中被读取到buffer pool中
(2)user A发起事务T1 开始修改 page1,此时page1 会变成一个脏页(dirty page)
(3)同时mysql会开始写redo log,当然这里是先将page1的变更写入redo buffer,并不会立刻落盘
(4)User A 开始发起commit提交这个事务T1
(5)此时mysql会将redo buffer内容flush到redo log中,即完成此时的redo落盘操作
(6)完成日志落盘后,Page1 会被先通过memcopy到double write buffer中
(7)最后double write buffer被写满或者用户本身发起了刷脏行为,那么会把数据从double write buffer写入到数据文件中

写入到double write buffer成功,但是page1 在写入到数据文件中时失败,进行恢复问题
解决:
由于doublewritebuffer中是写成功了,数据文件写失败,那么在恢复时mysql在检查这个page1时发现checksum有问题,
会直接从double write buffer中copy 已经写完成的page1覆盖到数据文件对应位置,然后再去应用redo等等
注:不完整的页是不能通过redo恢复的,double write机制更像在数据落盘之前多了一层缓冲