抱歉,您的浏览器无法访问本站
本页面需要浏览器支持(启用)JavaScript
了解详情 >

MySQL表空间详解

InnoDB文件构成

从物理意义上来讲,InnoDB表由共享表空间文件(ibdata1)、独占表空间文件(ibd)、表结构文件(.frm)、以及日志文件(redo文件等)组成。

表结构文件

​ 在MYSQL中建立任何一张数据表,在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息,包括表结构的定义等,.frm文件跟数据库存储引擎无关,也就是任何存储引擎的数据表都必须有.frm文件,命名方式为数据表名.frm,如user.frm. .frm文件可以用来在数据库崩溃时恢复表结构。

表空间文件

表空间结构分析

以下为InnoDB的表空间结构图:

​ 数据段即B+树的叶子节点,索引段即为B+树的非叶子节点InnoDB存储引擎的管理是由引擎本身完成的,表空间(Tablespace)是由分散的段(Segment)组成。一个段(Segment)包含多个区(Extent)。

​ 区(Extent)由64个连续的页(Page)组成,每个页大小为16K,即每个区大小为1MB,创建新表时,先使用32页大小的碎片页存放数据,使用完后才是区的申请(InnoDB最多每次申请4个区,保证数据的顺序性能)

独占表空间文件

​ 若将innodb_file_per_table设置为on,则系统将为每一个表单独的生成一个table_name.ibd的文件,在此文件中,存储与该表相关的数据、索引、表的内部数据字典信息。

共享表空间文件

在InnoDB存储引擎中,默认表空间文件是ibdata1(主要存储的是共享表空间数据),初始化为10M,且可以扩展,如下图所示:

实际上,InnoDB的表空间文件是可以修改的,使用以下语句就可以修改:

1
Innodb_data_file_path=ibdata1:370M;ibdata2:50M:autoextend

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

​ 而在使用单独表空间存储方式时,每个表的数据以一个单独的文件来存放,这个时候的单表限制,又变成文件系统的大小限制了。

以下即为不同平台下,单独表空间文件最大限度。

1
2
3
4
5
6
7
Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/ HFS+ 2TB
NetWare w/NSS file system 8TB

以下是MySQL文档中的内容:

Windows用户请注意: FAT和VFAT (FAT32)不适合MySQL的生产使用。应使用NTFS。

共享表空间与独占表空间

共享表空间以及独占表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。

独占表空间: 每一个表都将会生成以独立的文件方式来进行存储(.ibd文件,这个文件包括了单独一个表的数据内容以及索引内容)。

存储内容比较

使用独占表空间之后:

  • 每个表对应的数据、索引和插入缓冲 存放在独占表空间(.idb文件)

  • 每个表对应的撤销(undo)信息,系统事务信息,二次写缓冲等还是存放在了原来的共享表空间内(ibdata1文件)

特点比较

具体的共享表空间和独立表空间优缺点如下:

共享表空间

优点

  • 可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。
    所以其大小限制不再是文件大小的限制,而是其自身的限制。从Innodb的官方文档中可以看到,其表空间的最大限制为64TB,
    也就是说,Innodb的单表限制基本上也在64TB左右了,当然这个大小是包括这个表的所有索引等其他相关数据。
  • 表数据和表描述放在一起方便管理。

缺点

​ 所有的数据和索引存放到一个文件中,将有一个很常大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日志系统这类应用最不适合用共享表空间。

独立表空间

在配置文件(my.cnf)中设置 innodb_file_per_table。

优点

  1. 每个表都有自已独立的表空间。
  2. 每个表的数据和索引都会存在自已的表空间中。
  3. 可以实现单表在不同的数据库中移动。
  4. 空间可以回收(drop/truncate table方式操作表空间不能自动回收)
  5. 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

缺点

​ 单表增加比共享空间方式更大。

总结

​ MySQL数据库是由N多表组成的,每个表有自己的表定义文件.frm文件,表索引文件,表数据文件。INNODB的索引文件和数据文件是在一起的,MYISAM是分开的。
对于表空间,INNODB分成共享表空间和独享表空间2种。共享的情况下,所有表的数据都存在一个文件里,独享情况下,每个表单独文件存储此表的数据。

默认表空间

在服务器资源有限,单表数据不是特别多的情况下, 独立表空间明显比共享方式效率更高 . 但是MySQL 默认是共享表空间 。

共享表空间以及独占表空间配置

修改独占空表空间配置,配置以下参数

innodb_data_home_dir = “/user/local/mysql/var” 数据库文件所存放的目录

innodb_log_group_home_dir = “/user/local/mysql/var” 日志存放目录

innodb_data_file_path=ibdata1:10M:autoextend 设置配置一个可扩展大小的尺寸为10MB的单独文件(共享数据文件),名为ibdata1。没有给出文件的位置,所以默认的是在MySQL的数据目录内。

innodb_file_per_table=1 是否使用共享还是独占表空间 (1:为使用独占表空间,0:为使用共享表空间)

查看innodb_file_per_table 变量,如果为OFF说明所使用的是共享表空间(默认情况下,所使用的表空间为共享表空间)

对innodb_file_per_table 进行修改时,对于之前使用过的共享表空间不会影响,除非手动的去进行修改

缺点

​ 单表体积可能过大,如超过100个G。

注意

​ InnoDB不创建目录,所以在启动服务器之前请确认所配置的路径目录存在。

​ 做数据的移植以及备份时,要注意数据文件的完整性.

文件存储

InnoDB一棵B+树可以存放多少行数据

答案是:大约2千万条数据。下面将解释为什么是2千万条:

最小存储单元

​ 在计算机中磁盘存储数据最小单元是扇区,一个扇区的大小是512字节;
​ 文件系统(例如XFS/EXT4)他的最小单元是块,一个块的大小是4k,文件系统中一个文件大小只有1个字节,但不得不占磁盘上4KB的空间;
​ InnoDB存储引擎也有自己的最小储存单元——页(Page),一个页的大小是16K,Innodb的所有数据文件(后缀为ibd的文件),他的大小始终都是16384(16k)的整数倍。

默认页大小

​ 在MySQL中的InnoDB页的大小默认是16k:

​ 数据表中的数据都是存储在页中的,所以一个页中能存储多少行数据呢?假设一行数据的大小是1k,那么一个页可以存放16行这样的数据。

​ 如果数据库只按这样的方式存储,那么如何查找数据就存在性能问题,因为我们不知道要查找的数据存在哪个页中,也不可能把所有的页遍历一遍。因此InnoDB采用了一种更高效的存储结构,用B+树的方式组织这些数据。

​ 先将数据记录按主键进行排序,分别存放在不同的页中(为了便于理解我们这里一个页中只存放3条记录,实际情况可以存放很多),除了存放数据的页(叶子结点所在的页)以外,还有存放键值+指针的页(非叶子结点所在的页),如图中page number=3的页,该页存放键值和指向数据页的指针,这样的页由N个键值+指针组成。当然它也是排好序的,此为聚簇索引表。现在来看下,要查找一条数据,怎么查?

1
select * from user where id=5;

​ 这里id是主键,通过这棵B+树来查找,首先找到根页,如何知道user表的根页在哪呢?其实每张表的根页位置在表空间文件中是固定的,即page number=3的页(这点我们下文还会进一步证明),找到根页后通过二分查找法,定位到id=5的数据应该在指针P5指向的页中,那么进一步去page number=5的页中查找,同样通过二分查询法即可找到id=5的记录。

​ InnoDB存储引擎的最小存储单元是页,页可以用于存放数据也可以用于存放键值+指针,在B+树中叶子节点存放数据,非叶子节点存放键值+指针;
索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中,进而在去数据页中查找到需要的数据;
回到开始的问题,通常一棵B+树可以存放多少行数据?
先假设B+树高为2,即存在一个根节点和若干个叶子节点,那么这棵B+树的存放总记录数为:根节点指针数*单个叶子节点记录行数。
​ 上文我们已经说明单个叶子节点(页)中的记录数=16K/1K=16。(这里假设一行记录的数据大小为1k,目前生产环境上存储的一条数据大概在1k左右)。

​ 假设主键ID为bigint类型,长度为8字节,而指针大小在InnoDB源码中设置为6字节,这样一共14字节,我们一个页中能存放多少这样的单元,其实就代表有多少指针,即16384/14=1170。那么可以算出一棵高度为2的B+树,能存放1170*16=18720条这样的数据记录。

​ 根据同样的原理我们可以算出一个高度为3的B+树可以存放:1170乘1170乘16=21902400条这样的记录;

​ 所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

​ 怎么得到InnoDB主键索引B+树的高度(了解即可)?
在InnoDB的表空间文件中,约定page number为3的代表主键索引的根页,而在根页偏移量为64的地方存放了该B+树的page level。如果page level为1,树高为2,page level为2,则树高为3。即B+树的高度=page level+1;下面我们将从实际环境中尝试找到这个page level;

执行结果为:

可以看出数据库dbt3下的customer表、lineitem表主键索引根页的page number均为3,而其他的二级索引page number为4;

下面对数据库表空间文件做想相关的解析:

因为主键索引B+树的根页在整个表空间文件中的第3个页开始,所以可以算出它在文件中的偏移量:163843=49152(16384为页大小)。
另外根据《InnoDB存储引擎》中描述在根页的64偏移量位置前2个字节,保存了page level的值,因此我们想要的page level的值在整个文件中的偏移量为:16384
3+64=49152+64=49216,前2个字节中。
接下来我们用hexdump工具,查看表空间文件指定偏移量上的数据:

linetem表的page level为2,B+树高度为page level+1=3;region表的page level为0,B+树高度为page level+1=1;customer表的page level为2,B+树高度为page level+1=3;

这三张表的数据量如下:

lineitem表的数据行数为600多万,B+树高度为3,customer表数据行数只有15万,B+树高度也为3。可以看出尽管数据量差异较大,这两个表树的高度都是3,换句话说这两个表通过索引查询效率并没有太大差异,因为都只需要做3次IO。那么如果有一张表行数是一千万,那么他的B+树高度依旧是3,查询效率仍然不会相差太大。
region表只有5行数据,当然他的B+树高度为1。

经过上面的分析我们就可以很清楚的回答下面的问题:
为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树?

总结

B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;

​ B+树的聚簇索引只有叶子结点存储数据,且树高为3,IO次数也只为3,因此B+树相比B树查询时的IO操作较少,增加了查询效率。

评论