nosqlmysql高级篇

innodb文件架构体系(mysql8)

本文字数: 3k 阅读时长: 10 分钟
阅读次数次

文件分类

参数文件

  • 概述
    告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，
    这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。

  • 路径

    1
    /var/lib/mysql/

  • 相关命令

1
2
3
4
show [global] variables like "%xxxxx%";
set key value;
select @@session.read_buffer_size\G;

日志文件

  • 概述
    用来记录MySQL实例对某种条件做出相应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
    查看各种log

    1
    2
    show variables like "%log_%";
    # 开启通过my.cnf

  • 分类

    • 错误日志

      1
      2
      3
      4
      show variables like "%log_error%";
      vim my.cnf
      # 需要手动指定路径
      log-error= /xxx/log-error.txt

    • 慢日志

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      show variables like "%slow%";
      # 路径
      /var/lib/mysql/

      long_query_time:
      默认超过10秒为慢查询

      log_queries_not_using_indexes
      未用索引的sql会被慢查询记录

      查看文件输出格式（默认file）
      show variables like "%log_output%"\G;
      set global log_output='TABLE';

    • 二进制日志
      二进制文件记录了对MySQL数据库执行的更改操作，但是不包括SELECT和SHOW这类操作。因为这类操作对数据本身没有修改，
      然而，若操作本身并没有导致数据库发生变化，那么该操作也会写入二进制。

      • 作用
        1
        2
        3
        恢复：通过二进制日志恢复数据
        复制：主从复制通过二进制日志进行
        审计：通过二进制日志审计是否有注入攻击
        1
        2
        3
        4
        show variables like "%bin%";
        # 路径
        /var/lib/mysql

socket文件

  • 概述
    z注意在UNIX系统下，当用UNIX域套接字方式进行连接时需要的文件。

  • 路径

    1
    /var/run/mysqld/

  • 相关命令

    1
    show variables like "%socket%";

pid文件

  • 概述
    Pid文件是指当MySQL实例启动时，会将自己的进程ID写入PID文件中。

  • 路径

    1
    /run/mysqld/

  • 相关命令

    1
    show variables like "%pid%";

MySQL表结构文件

  • 概述
    用来存放MySQL表结构定义文件，无论存储引擎怎么变，表结构只有一份，mysql8已取消.frm文件。

存储引擎文件

因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

- frm文件 表对象结构定义文件，与引擎无关mysql8已取消
- ibd文件 InnoDB数据文件（含索引）
- MYD文件 MyISAM数据文件    
- MYI文件 MyISAM索引文件
- CSV文件 CSV引擎数据文件
- ARZ文件 ARCHIVE引擎数据文件

innodb详解

表空间文件

  • 共享表空间
    Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，
    而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包
    括这个表的所有索引等其他相关数据。

    1
    show variables like "%innodb_data%";

  • 单独表空间
    每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动，
    空间可以回收（除drop table操作处，表空不能自已回收）。

    1
    show variables like "innodb_file_per_table";
重做日志文件

即innodb存储引擎产生的日志，也可以称为重做日志文件，默认在innodb_data_home_dir下面有两个文件ib_logfile0和
ib_logfile1，MySQL官方手册中将这两个文件叫文InnoDB存储引擎的日志文件，因为重做日志的事务过程记录性质，使得
innodb支持可靠事务。（记录事务中的每一步，但是也同样花费了切换日志的时间）

  • innodb log的作用
    当MySQL的实例和介质失败的时候，Innodb存储引擎就会使用innodb log文件进行恢复，保证数据库的完整性。

  • innodb log的写原理

    • 每个InnDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有两个重做日志文件，默认的为ib_logfile0、ib_logfile1；
    • 日志组中每个重做日志的大小一致，并循环使用；
    • InnoDB存储引擎先写重做日志文件，当文件满了的时候，会自动切换到日志文件2，当重做日志文件2也写满时，会再切换到重做日志文件1；
    • 为了保证安全和性能，请设置每个重做日志文件设置镜像，并分配到不同的磁盘上面。

  • 相关命令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    show variables like 'innodb%log%'; 
    innodb_mirrored_log_groups  镜像组的数量，默认为1，没有镜像；

    innodb_log_group_home_dir  日志组所在的路径，默认为data的home目录；

    innodb_log_files_in_group    日志组的数量，默认为2；

    innodb_log_file_size              日志组的大小,默认为5M；

    innodb_log_buffer_size        日志缓冲池的大小，图上为30M;

  • 重做日志与二进制日志的区别

    • 记录的范围不同
      二进制日志会记录MySQL的所有存储引擎的日志记录（包括InnoDB、MyISAM等)，
      而InnoDB存储引擎的重做日志只会记录其本身的事务日志。

    • 记录的内容不同
      二进制日志文件记录的格式可以为STATEMENT或者ROW也可以是MIXED，其记录的都是关于一个事务的具体操作内容。
      InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况。

    • 写入的时间也不同
      二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被写入到重做日志文件中。

索引组织表

innodb通过主键索引加data的b+tree的方式进行存储，所以必须有主键索引，如果我们在创建表的时候没有显示的创建主键，
那么innodb会通过几种方式进行隐式的创建索引(辅助索引需要通过主键索引去得到值，值存在主键索引的叶子结点)

  • 1.首先判断表中是否有唯一的非空索引（unique not null）, 如果有，则该列即为主键。
  • 2.如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。
  • 3.当表中有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个非空索引作为主键。
innodb逻辑存储结构

所有数据被存储到表空间中，称之为表空间（tablespace)。表空间由段segment、区extent、页page组成。（页也可称为块）。

  • 表空间
    前面介绍过的，Innodb存储结构的最高层，所有的数据都存放在表空间中。如果用户启用了参数innodb_file_per_table，则每张表内数据可以单独放到表空间内。

    • 注意
      1
      2
      3
      如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓存Bitmap页，其他类的数据，如回滚（undo）信息，插入缓冲索引页，系统事务信息，二次写缓冲（double write buffer）等还是存放在原来的共享表空间ibdata1内。
      这也说明了，即使启用了参数innodb_file_per_table之后，共享表空间还是会不断增大。
      show variables like "innodb_file_per_table";

  • 段
    表空间由段组成，索引段为b+树的非叶子结点，数据段为b+树的非叶子结点，还有回滚段等
    段的控制一般是由引擎自身完成的。

  • 区
    区的大小为1MB, 区是由连续页组成的，为了保证区中页的连续性，InnoDB一次会从磁盘中申请4-5个区，
    默认情况下存储引擎页的大小是16KB，即一个区中有连续64个页。

    • 碎片页：（fragment page）
      用户在启用了参数innodb_file_per_table后，创建的表默认大小是96KB。区中是64个连续的页。每个段开始，先用32页大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请，一次来节省空间。

  • 页
    innodb磁盘管理的最小单位。默认每页的大小为16kb，页分了很多类数据页、系统页、事务数据页…..

    • 为什么innodb大概存2000万数据就不行了？
      • 主键索引加入8bytes(longint)+6bytes(指针) = 14bytes
      • 一页默认为16kb，那么16*1024/14 = 1170
      • 假如第三层为叶子结点，1170117016位数据页大小
      • 假如数据每行为1kb，总数约为2200万数据

聚簇索引和非聚簇索引

聚簇索引

典型为innodb，必须有主键（没有也创建）
可以看到innodb只有一个数据文件存放主键索引（一级索引）和数据，数据为索引的一部分，而我们平时创建的索引index为辅助索引，
当通过辅助索引查询时，先通过辅助索引找到主键索引，再到主键索引的叶子结点取得数据。

  • 优缺点
    • 优点
      1
      2
      1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
      2.聚簇索引对于主键的排序查找和范围查找速度非常快
    • 缺点
      1
      2
      3
      4
      1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
      因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
      　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
      　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
非聚簇索引

典型myisam
　MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，辅助索引不通过主键索引，直接去数据文件找数据。

页分裂和页合并

页合并

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用，当页中删除的记录达到
MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

页分裂

插入数据时，页可能填充至100%，在页填满了之后，下一页会继续接管新的记录，但如果这个时候下一页也满了，由于顺序性，就需要在两页之间创建新的页，
然后把当前页的数据分裂一部分到新的页，并且插入新的数据。

  • 影响
    页分裂会发生在插入或更新，并且造成页的错位（dislocation，落入不同的区）
    InnoDB用INFORMATION_SCHEMA.INNODB_METRICS表来跟踪页的分裂数。
    一旦创建分裂的页，唯一（译注：实则仍有其他方法，见下文）将原先顺序恢复的办法就是新分裂出来的页因为低于合并阈值（merge threshold）被删掉。这时候InnoDB用页合并将数据合并回来。
    另一种方式就是用OPTIMIZE重新整理表。这可能是个很重量级和耗时的过程，但可能是唯一将大量分布在不同区的页理顺的方法。

  • 建议
    如果有大量插入删除操作，请确定花一段时间整理表，你必须不时（at regular intervals）重建一些表。

分享到