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

转载来自：http://blog.itpub.net/12679300/viewspace-1379902/

http://www.cnblogs.com/cuisi/p/6519939.html

http://blog.csdn.net/lqx0405/article/details/48373005

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

一、概念

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

独立表空间：

二、查看数据库的表空间

mysql> show variables like 'innodb_data%';

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";

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/）

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

关于共享表空间和独立表空间的知识总结！

【共享表空间/系统表空间】

Innodb 将存储的数据按照表空间(tablespace)进行存放，默认配置下，会有一个初始大小10M，名为：ibdata1的文件，这就是默认的表空间文件。

 

配置文件中的设置：

1.默认设置

      innodb_data_file_path = ibdata1:10M:autoextend

     生成文件默认是在data目录下。

2.多路径设置

      innodb_data_file_path = /data1/db1/ibdata1:100M:autoextend; /data2/db2/ibdata2:100M:autoextend

      放在不同的磁盘，可以平均磁盘负载，提高数据库性能。

3.记录内容

     记录所有基于Innodb存储引擎的表的数据。

 

【独立表空间】

1.配置

mysql> show variables like 'innodb_file_per%'\G;

*************************** 1. row ***************************

Variable_name: innodb_file_per_table

  Value: ON

注意：MySQL5.6.7之后默认开启

 

2.位置

在对应的表所在的目录，生成：表名.ibd文件

[root@monitor wb]# pwd

/mysqlData/con1/data/wb

[root@monitor wb]# ll -h u1.*

-rw-rw----. 1 mysql mysql 8.5K Feb 28 20:36 u1.frm

-rw-rw----. 1 mysql mysql 96K Feb 28 20:37 u1.ibd

 

3.记录内容

独立的表空间，仅存储该表的：数据，索引和插入缓冲BITMAP等信息。

其余信息仍存储在默认表空间。

 

【相关知识点说明】

 

1.启用innodb_file_per_table后，每张表的表空间只存放自己的：数据，索引和插入缓冲BITMAP页。其它信息仍放在默认表空间。

 

其它信息如：回滚(undo)信息、插入缓冲索引页、系统的事物信息、二次写缓冲(Double write buffer)等。

 

2.file_per_table的特点

 

可以灵活选择：row format和file format。对于 数据压缩、truncate table操作会更快写。

 

回收空间可以再次被利用，相反默认表空间不会收缩空间。

 

MySQL Enterprise Backu对于拥有独立表空间的表，更灵活。表能从备份中单独出来，适合备份不是太频繁的表。

 

3.表空间：从逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）

 

4.共享表空间大小不会自动收缩。

MySQL Study之--MySQL增加新的表空间（tablespace）

系统环境：

    操作系统：RedHat EL64(64)

    MySQL：  Percona Server 5.6

MySQL innodb引擎采用表空间的方式管理数据存储，默认系统会建立一个共享表空间；如果共享表空间，空间不足，需要增加新的tablespace。在MySQL扩展表空间是使用的参数：

innodb_data_file_path：
      该变量设置innoDB数据文件的位置和大小。指定的文件必须大于10M，如果不受系统文件限制，可以设置大于4G。该变量是mysql服务器容量规划和性能扩展能力的核心要素。通常设置是创建一个数据目录内容的基线大小，在10M到128M之间，第二个文件设置为10M并自动扩展。如innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend。

      当没有使用innodb_file_per_table也没有启用自动扩展，那么随着数据的增长，表空间将满了。在这情况下，需要添加额外的表空间来扩展容量。方法如下：
1. 停止mysql服务
2. 备份配置文件，便于出现问题好回退
3. 编辑innodb_data_file_path值
根据你的环境更改ibdata1:$size;ibdataN:$size;…ibdataN:$size; 当前定义的表空间或默认表空间是不能改变的，否则启动失败，但是，可以额外的添加表空间，ibdataN序列根据当前的数量递增，$size自定义。
4. 启动mysql服务
6. 观察mysql错误日志是否有错

1、查看表空间信息

mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------------------+
| Variable_name         | Value                              |
+-----------------------+------------------------------------+
| innodb_data_file_path | ibdata1:10M|
+-----------------------+------------------------------------+
1 row in set (0.01 sec)

2、修改配置文件，增加新的表空间

[root@rh64 mysql]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
explicit_defaults_for_timestamp=true
innodb_buffer_pool_size = 128M
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

3、重新启动server
[root@rh64 mysql]# service mysql start
Starting MySQL (Percona Server).The server quit without updating PID file (/var/lib/mysql/rh64.pid).[FAILED]

启动失败，查看日志：
[root@rh64 mysql]# tail -100 /var/log/mysqld.log
150911 11:51:16 mysqld_safe mysqld from pid file /var/lib/mysql/rh64.pid ended
150911 11:54:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-09-11 11:54:38 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-09-11 11:54:38 0 [Note] /usr/sbin/mysqld (mysqld 5.6.25-73.1) starting as process 3588 ...
2015-09-11 11:54:38 3588 [Note] Plugin 'FEDERATED' is disabled.
2015-09-11 11:54:38 3588 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-11 11:54:38 3588 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-11 11:54:38 3588 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-11 11:54:38 3588 [Note] InnoDB: Memory barrier is not used
2015-09-11 11:54:38 3588 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-09-11 11:54:38 3588 [Note] InnoDB: Using Linux native AIO
2015-09-11 11:54:38 3588 [Note] InnoDB: Not using CPU crc32 instructions
2015-09-11 11:54:38 3588 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-09-11 11:54:39 3588 [Note] InnoDB: Completed initialization of buffer pool
2015-09-11 11:54:39 3588 [ERROR] InnoDB: Data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file 1280 pages!
2015-09-11 11:54:39 3588 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2015-09-11 11:54:39 3588 [ERROR] Plugin 'InnoDB' init function returned error.
2015-09-11 11:54:39 3588 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-09-11 11:54:39 3588 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-09-11 11:54:39 3588 [ERROR] Aborting

----错误提示：ibdata1数据文件size设置大小和系统不匹配！！

查看表空间数据文件的大小：

[root@rh64 mysql]#  ls -lh
total 167M
-rw-rw----. 1 mysql mysql   56 Sep  6 18:08 auto.cnf
-rw-rw----. 1 mysql mysql  12M Sep 11 11:56 ibdata1
-rw-rw----. 1 mysql mysql  10M Sep 11 11:50 ibdata2
-rw-rw----. 1 mysql mysql  48M Sep 11 11:56 ib_logfile0
-rw-rw----. 1 mysql mysql  48M Sep  6 18:06 ib_logfile1
-rw-rw----. 1 mysql mysql  48M Sep 11 11:50 ib_logfile101
drwx------. 2 mysql mysql 4.0K Sep  6 18:06 mysql
srwxrwxrwx. 1 mysql mysql    0 Sep 11 11:56 mysql.sock
drwx------. 2 mysql mysql 4.0K Sep  6 18:06 performance_schema
-rw-rw----. 1 mysql mysql    5 Sep 11 11:56 rh64.pid
-rw-r--r--. 1 root  root   293 Sep  6 18:07 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql  293 Sep  6 18:07 RPM_UPGRADE_MARKER-LAST
drwx------. 2 mysql mysql 4.0K Sep  6 18:06 test

重新修改配置文件配置参数：
[root@rh64 mysql]# vi /etc/my.cnf
innodb_data_file_path=ibdata1:12
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextend

---对于已有的表空间数据文件的size，必须按照系统查看的size设置，否则报错！

（亦可以将表空间建立在不同的目录下如：/data/ibdata3:10M,首先需建立/data目录）

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
explicit_defaults_for_timestamp=true
innodb_buffer_pool_size = 128M
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
~

重新启动server：
[root@rh64 mysql]# service mysql start
Starting MySQL (Percona Server).[  OK  ]

登录server，查看tablespace：
[root@rh64 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------------------+
| Variable_name         | Value                              |
+-----------------------+------------------------------------+
| innodb_data_file_path | ibdata1:12M;ibdata2:10M:autoextend |
+-----------------------+------------------------------------+
1 row in set (0.01 sec)

---新的tablespace增加成功 ！！