mysql表空间

一、表空间概念:

对于innodb的数据结构,首先要解决两个概念性的问题: 共享表空间以及独占表空间。表空间的概念实际上是引擎层的,共享表空间以及独占表空间都是针对数据的存储方式而言的。只要在my.cnf里面增加innodb_file_per_table=1就可以从共享表空间切换到独立表空间。当然对于已经存在的表,则需要执行alter table table_name engine=innodb命令迁移数据。

定义:从逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,即:"表空间"


二、共享表空间:

innodb_data_file_path参数配置的就是一个共享表空间,数据都往这一个文件里放,也就是ibdata1,
共享表空间还包含:回滚(undo)信息、插入缓冲索引页、系统的事物信息、双写缓冲(Double write buffer)等。

ibdata1会伴随时间、数据等因素持续增长,且无法收缩,这是共享表空间一直让人所诟病的问题。

   优点:

1.由于所有的数据都放在共享表空间所以文件数量相对很少,方便管理。

2.表空间可以分成多个文件存放到各个磁盘,所以表也就可以分成多个文件存放在磁盘上,用于提升IO性能。(现在的版本已经不支持该功能)

    缺点:

1.所有的数据和索引存放到一个或多个文件中,但是多个表及索引在表空间中混合存储,当数据量非常大的时候。带来的性能会有下降。
2.正由于集中管理的方式,也间接导致了存储空间中有可能多个表数据存放在一起,此时如果一个pgae包含的多一个表对象都请求该page时,就会有锁的争抢。

3.共享表空间分配后的空间不能回收:当出现创建一个表的操作表空间扩大后,即使删除相关的表数据也没办法回缩那部分已分配的空间;这就是很多线上为什么MySQL ibdata*文件会变成几百G的原因。同时也会为物理备份的方式带来额外的负担。

注意:如果想回收共享表空间的大小,只能是逻辑导出,重建数据库,在导入!


最佳实践
实例一:共享表空间的使用
要求MySQL实例共享表空间方式启动(参数:innodb_file_per_table = 0)

mysql> create database share_tablespace;
Query OK, 1 row affected (0.01 sec)

mysql> create table t_share_innodb (id bigint,table_name varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_share_innodb (table_name) values ('t1');
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_share_innodb (table_name) values ('t2');
Query OK, 1 row affected (0.00 sec)
没有生成ibd文件,说明就是使用共享表空间

mysql> system ls /data1/db3306/share_tablespace
db.opt  t_share_innodb.frm
实例二:共享表空间的扩容
如果觉得一个共享表空间实在太大,担心影响性能,可以扩展多个共享表空间

# mysqladmin -S /data1/db3306/my3306.sock shutdown

# vim /etc/my.cnf
......  省略 ......
#innodb_data_file_path = ibdata1:100M:autoextend
innodb_data_file_path = ibdata1:100M;ibdata2:10M;ibdata3:50M:autoextend
......  省略 ......

# service mysql start
Starting MySQL..                                           [  OK  ]
实例三:共享表空间数据迁移

# vim /etc/my.cnf
......  省略 ......
innodb_file_per_table          = 1
......  省略 ......

[root@localhost db3306]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
此时注意,实例虽然已经完成变更,但是表还是共享表空间方式

# ls /data1/db3306/share_tablespace
db.opt  t_share_innodb.frm
将表迁移到独立表空间,两种方式:

mysql> optimize table t_share_innodb;
mysql> #or
mysql> alter table t_share_innodb engine = innodb;

mysql> system ls /data1/db3306/share_tablespace

db.opt  t_share_innodb.frm  t_share_innodb.ibd

三、独立表空间:

     优点
1.表空间可以回收,也可以整理表空间碎片(alter table table_name engine=innodb; 线上慎重,有DDL锁)
2.使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。

3.每个表都有自已独立的表空间,每个表的数据和索引都会存在自已的表空间中,减少page级别的锁争用。


     缺点
1.数据都是在表所路径的*.table_name.ibd文件中,如果存储空间不足,只能从操作系统层面思考解决方法。
最佳实践
独立表空间迁移请参考:共享表空间中最佳实践


阅读更多

没有更多推荐了,返回首页