MySQL InnoDB 共享表空间和独立表空间

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL  InnoDB 共享表空间和独立表空间 官网:https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html 前言:学习mysql的时候总是习惯性的和oracle数据库进行比较。

前言:学习mysql的时候总是习惯性的和oracle数据库进行比较。在学习mysql InnoDB的存储结构的时候也免不了跟oracle进行比较。Oracle的数据存储有表空间、段、区、块、数据文件;mysql InnoDB的存储管理也类似,但是mysql增加了一个共享表空间和独立表空间的概念;

image

一、概念

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

独立表空间:

二、查看数据库的表空间

mysql> show variables like 'innodb_data%';

image


l 表空间有四个文件组成:ibdata1、ibdata2、ibdata3、ibdata4,每个文件的大小为10M,当每个文件都满了的时候,ibdata4会自动扩展;

l 当前的存储空间满的时候,可以在其他的磁盘添加数据文件,语法如下:语法如下所示:

pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]

如果用 autoextend 选项描述最后一个数据文件,当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件,每次增量为 8 MB。示例:

不管是共享表空间和独立表空间,都会存在innodb_data_file文件,因为这些文件不仅仅要存放数据,而且还要充当着类似于ORACLE的UNDO表空间等一些角色。

三、共享表空间优缺点

既然Innodb有共享表空间和独立表空间两种类型,那么这两种表空间存在肯定都有时候自己的应用的场景,存在即合理。以下是摘自mysql官方的一些介绍:

3.1 共享表空间的优点

表空间可以分成多个文件存放到各个磁盘,所以表也就可以分成多个文件存放在磁盘上,表的大小不受磁盘大小的限制(很多文档描述有点问题)。

数据和文件放在一起方便管理。

3.2 共享表空间的缺点

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

共享表空间分配后不能回缩:当出现临时建索引或是创建一个临时表的操作表空间扩大后,就是删除相关的表也没办法回缩那部分空间了(可以理解为oracle的表空间10G,但是才使用10M,但是操作系统显示mysql的表空间为10G),进行数据库的冷备很慢;

四、独立表空间的优缺点

4.1 独立表空间的优点

每个表都有自已独立的表空间,每个表的数据和索引都会存在自已的表空间中,可以实现单表在不同的数据库中移动。

空间可以回收(除drop table操作处,表空不能自已回收)

Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

4.2 独立表空间的缺点

单表增加过大,当单表占用空间过大时,存储空间不足,只能从操作系统层面思考解决方法;

五、共享表空间和独立表空间之间的转换

5.1 查看当前数据库的表空间管理类型

脚本:show variables like "innodb_file_per_table";

mysql> show variables like "innodb_file_per_table";

image


ON代表独立表空间管理,OFF代表共享表空间管理;(查看单表的表空间管理方式,需要查看每个表是否有单独的数据文件)

5.2 修改数据库的表空间管理方式

修改innodb_file_per_table的参数值即可,但是修改不能影响之前已经使用过的共享表空间和独立表空间;

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

5.3共享表空间转化为独立表空间的方法(参数innodb_file_per_table=1需要设置)
单个表的转换操作,脚本:alter table table_name engine=innodb;

当有大量的表需要操作的时候,先把数据库导出,然后删除数据再进行导入操作,该操作可以用mysqldump进行操作(http://blog.itpub.net/12679300/viewspace-1259451/

总结:经过以上操作便完成数据库的存储空间的转换,了解技术是为了更好的利用技术,当数据量很小的时候建议使用共享表空间的管理方式。数据量很大的时候建议使用独立表空间的管理方式。



Advantages of File-Per-Table Mode

1 当truncate或者drop一个表时可以释放磁盘空间。如果不是独立表空间,truncate或drop一个表只是在ibdata文件内部释放,实际ibdata文件并不会缩小,释放出来的空间也只能让其他innodb引擎的表使用(有时这会造成磁盘空间容量监控的一个痛点,无法判断它到底有多满)

2 独立表空间下,truncate table操作会更快

3 独立表空间下,可以自定义表的存储位置,通过CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory命令实现(有时将部分热表放在不同的磁盘可有效地提升IO性能)

4 独立表空间下,可以回收表空间碎片(比如一个非常大的delete操作之后释放的空间),alter table xxx engine=innodb

5 可以移动单独的innodb表,而不是整个database

6 可以copy单独的innodb表从一个实例到另外一个实例(也就是transportable tablespace特色)

7 独立表空间模式下,可以使用Barracuda的文件格式,这个文件格式有压缩和动态行模式的特色。这个当表中有blob或者text字段的话,动态行模式(dynamic row format)可以发挥出更高效的存储。

8 独立表空间模式下,可以更好的改善故障恢复,比如更加节约时间或者增加崩溃后正常恢复的机率。

9 单独备份和恢复某张表的话会更快

10 可以使得从一个备份中单独分离出表,比如一个lvm的快照备份

11 可以在不访问MySQL的情况下方便地得知一个表的大小,即在文件系统的角度上查看。

12 在大部分的linux文件系统中,如果innodb_flush_method为O_DIRECT,通常是不允许针对同一个文件做并发写操作的。这时如果为独立表空间模式的话,应该会有较大的性能提升。

13 如果没有独立表空间模式,那么所有的东西都在共享表空间,而共享表空间是有64T的容量上限的(这点比较扯淡)

Potential Disadvantages of File-Per-Table Mode

1 独立表空间模式下,每个表或许会有很多没用到的磁盘空间,因为初始表空间大小是固定的吧,有些表根本没使用都这么多。如果没做好管理,可能会造成较大的空间浪费。

2 fsync操作必须运行在每一个单一的文件上,独立表空间模式下,多个表的写操作就无法合并为一个单一的IO,这样就添加许多额外的fsync操作

3 mysqld必须保证每个表都有一个open file,独立表空间模式下,这样就需要很多打开文件数,可能会影响性能

4 当drop一个表空间时,buffer pool会被扫描,如果buffer pool有几十G那么大,或许要花费几秒钟时间。这个扫描操作还会产生一个内部锁,可能会延迟其他操作,共享表空间模式下不会有这个问题。

5 如果许多表都增长迅速,那么可能会产生更多的分裂操作(应该指的是表空间大小的扩充),这个操作会损害drop table和table scan的性能。

6 innodb_autoextend_increment参数对独立表空间无效,这个参数指的是当系统表空间满了以后,它再次预先申请的磁盘空间大小,单位为M

 

Copying Tablespaces to Another Server(Transportable Tablespaces)

Transportable Tablespaces特性可以处理以下几个痛点

1 跑报表,单独拎出需要的几个表到新的实例上跑报表,可以保证报表产生巨大服务器开销不影响到现网服务

2 通过这个特性快速地建立一个新的slave

3 在当前库发生一个故障后备份一个该表的临时版本

4 一种比mysqldump更快的moving data的方式

5 可以将部分表移交到一个更合适的硬件环境下,比如小而操作频繁的放在SSD下,大而操作不频繁的放在HDD

Tablespace Copying Limitations and Usage Notes(Transportable Tablespaces)

1 必须是在独立表空间模式下

2 在迁移过程中,涉及到的表只读不可写

3 两边的page size必须保持一致(innodb_page_size)

4 DISCARD TABLESPACE操作不一致分区表,否则报错ERROR 1031 (HY000): Table storage engine for 'part' doesn'thave this option.

5 DISCARD TABLESPACE时,如果foreign_key_checks为1,那么不支持存在外键约束的表

6 ALTER TABLE ... IMPORT TABLESPACE操作不会去检查外键约束

7 ALTER TABLE ... IMPORT TABLESPACE并不需要.cfg的元数据文件来导入一个表空间。但是,这样的话就没有元数据检查了,同时会给出一个waring

Message: InnoDB: IO Read error: (2, No suchfile or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)

如果确认schema没有配错,那么不用.cfg文件可能更方便简洁,并且有利于元数据无法在.ibd文件里收集的故障恢复。

8 主从环境下,master和slave都必须设置为独立表空间模式

Example Procedure: Copying a Tablespace From One Server ToAnother (Transportable Tablespaces)

1 在源server上,创建一个测试表

mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

2 在目标server上,同样创建出相同表结构的表

mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

3 在目标server上,discard掉该表的表空间

mysql> ALTER TABLE t DISCARD TABLESPACE;

4 在源server上,运行FLUSH TABLES ... FOR EXPORT命令来静默这张表,并创建出.cfg元数据文件(.cfg文件创建在innodb的data目录)

mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;

5 复制源server上的.ibd和.cfg文件到目标server上

shell> scp /path/to/datadir/test/t.{ibd,cfg}destination-server:/path/to/datadir/test

6 在源server上,使用unlock tables命令来释放flush tables..for export命令上的锁

mysql> use test;
mysql> UNLOCK TABLES;

7 在目标server上,导入表空间

mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;

Tablespace Copying Internals (Transportable Tablespaces)

ALTER TABLE ... DISCARD TABLESPACE做了下面这两件事

1 给table加X锁

2 分离出表空间

FLUSH TABLES ... FOR EXPORT做了以下几件事

1 给table加S锁

2 停止purge线程

3 脏页刷新到磁盘

4 表的元数据写到.cfg文件

Unlock tables做了以下几件事

1 删除.cfg文件

2 释放之前的共享锁,并重启purge线程

ALTER TABLE ... IMPORT TABLESPACE做了以下几件事

1 检查表空间内的每个页是否有损坏

2 更新每页上的space ID和LSNs

3 更新开头页的有效标志位和LSN

4 更新btree页

5 将页状态改为“脏页”,刷新到磁盘

Storing InnoDB Undo Logs in SeparateTablespaces

  5.6.3以前,innodb undo log存放在系统表空间,5.6.3以后可将undo log从系统表空间中分离出来。这样带来的好处是,可将undo放到SSD盘提升性能。这个特性主要由这几个参数实现

innodb_undo_tablespaces:undo logs创建的表空间文件数量,默认为0,表示存放在系统表空间,最大值为126

innodb_undo_directory:undo logs存放的路径,默认为.表示和其他日志文件一致

innodb_undo_logs:系统表空间中在一个事务里最大使用到的回滚段数量,默认为最大值128

分离步骤如下

1 决定undo logs的存放路径,修改innodb_undo_directory参数

2 决定一个非0的innodb_undo_logs值,可以通过压测决定到底取啥值,起始值设置小一些

3 决定一个非0的innodb_undo_tablespaces值

4 利用上述配置创建出新的mysql实例

5 压测IO负载情况

6 压测期间慢慢增大innodb_undo_logs,找到最合适的值

7 上生产环境下再测试观察

Changing the Number or Size of InnoDB LogFiles and Resizing the InnoDB Tablespace

Changing the Number or Size of InnoDB Log Files

1 SET GLOBAL innodb_fast_shutdown = 1;

2 正常关闭mysqld进程,确保正常关闭

3 备份旧的log files

4 删除旧的log files

5 修改my.cnf配置

6 重启mysqld,确保启动后log files的数量和新配置一致

5.6.8以后的版本,不再需要innodb_fast_shutdown,也不用删除旧的log files。

Increasing the Size of the InnoDB Tablespace

由innodb_data_file_path参数的autoextend参数控制就好,表空间会自动增长。默认每次增长为8M,系统表空间每次增长量可由参数innodb_autoextend_increment控制。如果要两个系统表空间文件,一个固定大小,一个自增,可以这么弄(建议一个就好,不要这么搞,有点乱,不好管理)

innodb_data_file_path =/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Decreasing the Size of the InnoDB Tablespace

独立表空间回收磁盘空间,只要运行alter table xx engine=innodb就行,系统表空间回收步骤如下

1 使用mysqldump dump出所有的表

2 关闭mysql

3 删除整个data目录,包括ibdata文件和log file

4 启动mysql

5 导入dump文件



Mysql Innodb的两种表空间方式

要说表空间,Mysql的表空间管理远远说不上完善。换句话说,事实上Mysql根本没有真正意义上的表空间管理。Mysql的Innodb包含两种表空间文件模式,默认的共享表空间和每个表分离的独立表空间。只要在my.cnf里面增加innodb_file_per_table=1就可以从共享表空间切换到独立表空间。当然对于已经存在的表,则需要执行alter table MY_TABLE engine=innodb命令迁移数据。

共享表空间方式

由于是默认的方式,就暂且理解为Mysql官方推荐的方式。相对而言所有的数据都在一个(或几个)文件中,比较利于管理,而且在操作的时候只需要open这一个(或几个)文件即可,相对来说代价很低。

但问题是在数据达到以G为单位来计算的时候优劣逆转。一个大小惊人的文件很不利于管理,而且对于一个如此巨大的文件来说,读写它需要耗费的资源一样巨大。更加令人费解的是,MySQL竟然将索引和数据保存于同一个文件中,索引和数据之间尚存在资源争用,不利于性能的提升。你当然可以通过innodb_data_file_path的配置规划多个表空间文件,但MySQL的逻辑是“用满后增加”,仅仅是一个文件的拆分而已,不能从根本上分离数据和索引。

之前曾经遭遇到700G以上的表空间文件,而且更加让人郁闷的是对于如此大的文件还在以每天数G的数量增加。由于无法停机,即便是拷贝一下也要花费差不多一夜,只能眼睁睁看着它继续增大而毫无保守可行的办法。

独立表空间方式

相对而言对立表空间每个表都有独立的多个数据文件,而且做到了索引和数据的分离。多个小文件之间很方便的完成跨数据库甚至跨硬件的数据拷贝和迁移。相对来说灵活性很好。

这样做同样带来另一个方面的问题。当数据库中的表数量达到一定级别时,每次操作所涉及的文件过多,如果按照默认Centos的ulimit -n = 1024的话,仅仅只能保证同时打开256个表以内,这在习惯上“拆库拆表”的MySQL数据结构上很难达到要求。尚且这种数据文件的利用率不算很高,当大量“不高”的文件集中起来,浪费的空间也很惊人,更何况最后可能出现的状况不是“一堆K级别的小文件”而是“一堆G级别的大文件”,有点适得其反的意思。你自然可以联想到分区表,又是一个“仅仅做文件拆分而已”,多个分区文件缺一不可。

之前同样遇到过这个问题,MySQL连接大的状况下大量的timeout,但主机负载还算可以,查了一圈才知道是open files限制的问题,限制一修改,负载变得惊人,但连接数却又提升的不多。

总之,两种方法各有所长,部分互补,但都不是解决问题的终极方案。期待MySQL能够出现真正意义上表空间的概念,更加自由的规划数据文件。



对于innodb的数据存储文件,首先要解决两个概念性的问题: 共享表空间以及独占表空间。(innodb引擎与MYISAM引擎的区别很大。特别是它的数据存储方式等.)

1、共享表空间和独占表空间介绍

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

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

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


2、共享表空间和独占表空间的区别

共享表空间:
优点:
1)可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。

 











 

 

 

 





  • <
    >

     

     






































 

















    



 

 

   

 

  

 

 



>

                         


                        

 

 

>

          
        
       
    
          
 
   

  
     
 
   
   
    
     
      
 
       

 

 



   






























相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
136 57
|
2天前
|
存储 算法 关系型数据库
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
11 1
|
2天前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
8 0
|
2天前
|
存储 算法 关系型数据库
【MySQL技术内幕】4.4-InnoDB数据页结构
【MySQL技术内幕】4.4-InnoDB数据页结构
9 1
|
17小时前
|
监控 安全 关系型数据库
精通MySQL:数据库核心技术与应用实践
h3> 一、引言 MySQL作为开源关系型数据库管理系统的佼佼者,凭借其出色的性能、灵活性和稳定性,成为许多企业和开发者的首选
|
1天前
|
存储 关系型数据库 MySQL
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
MySQL周内训参照1、ER实体关系图与数据库模型图绘制
6 1
|
2天前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库,一步就能脱胎换骨
阿里云瑶池数据库来开课啦!自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定!
|
2天前
|
SQL 存储 关系型数据库
MySQL 示例数据库大全
我们练习 SQL 时,总会自己创造一些测试数据或者网上找些案例来学习,其实 MySQL 官方提供了好几个示例数据库,在 MySQL 的学习、开发和实践中具有非常重要的作用,能够帮助初学者更好地理解和应用 MySQL 的各种功能和特性,特别是练习 SQL 的好帮手。
22 0
|
3天前
|
SQL 关系型数据库 MySQL
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
|
4天前
|
SQL 关系型数据库 MySQL
MySQL中如何查看所有数据库的名称?
【6月更文挑战第12天】MySQL中如何查看所有数据库的名称?
15 3

推荐镜像

更多