mysql表空间

原创 2018年04月17日 10:33:39

一、表空间概念:

对于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文件中,如果存储空间不足,只能从操作系统层面思考解决方法。
最佳实践
独立表空间迁移请参考:共享表空间中最佳实践


mysql表空间总结

mysql 表空间管理与共享维护 INNODB 对于表的存储有两种形式 一种是共享表空间,及多张表放在一个文件中,还有一种是独立表空间,每个表都有独立的数据文件。 下面实验分别展示了两种形式 ...
  • daohengshangqian
  • daohengshangqian
  • 2015-11-30 17:54:26
  • 3450

mysql共享表空间转化为独立表空间

1.查看当前表空间情况: mysql> show variables like '%per_table'; +-----------------------+-------+ | Variabl...
  • qq_31666147
  • qq_31666147
  • 2016-05-17 17:36:31
  • 784

MYSQL:查看的数据库表空间

1.查看索引 (1)单位是GB SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index...
  • c_enhui
  • c_enhui
  • 2013-08-07 11:49:06
  • 16248

mysql共享表空间和独立表空间

  innodb表的数据结构  innodb这种引擎,与MYISAM引擎的区别很大。特别是它的数据存储格式等. 对于innodb的数据结构,首先要解决两个概念性的问题: 共享...
  • wilsonke
  • wilsonke
  • 2015-09-11 19:08:02
  • 1380

Mysql数据库及表空间占用信息统计

1、mysql中查看各表的大小 这里用到一个表, information_schema.tables;对应主要字段含义如下: ABLE_SCHEMA : 数据库名 TABLE_NAME:表名 ...
  • BabyFish13
  • BabyFish13
  • 2016-07-04 11:07:03
  • 3116

mysql 查看表空间

SELECT * FROM (SELECT table_name, CONCAT(ROUND(SUM(data_length / 1024 ...
  • wangzhpwang
  • wangzhpwang
  • 2018-01-09 17:30:35
  • 158

mysql innodb表空间损坏补救方法

---首先、要了解myisam类型引擎和innodb类型引擎数据表之间的区别     ◆1.InnoDB不支持FULLTEXT类型的索引。   ◆2.InnoDB 中不保存表的具体行数...
  • max18
  • max18
  • 2013-02-26 13:32:12
  • 1348

修改mysql表空间为独立模式

使用过MySQL的同学,刚开始接触最多的莫过于MyISAM表引擎了,这种引擎的数据库会分别创建三个文件:表结构、表索引、表数据空间。我们可以将某个数据库目录直接迁移到其他数据库也可以正常工作。 然而...
  • tuna_lxg
  • tuna_lxg
  • 2014-04-24 17:44:10
  • 1648

Mysql 表空间概念

mysql> show variables like 'innodb_data%'; +-----------------------+------------------------+ | Vari...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2015-04-26 12:16:54
  • 440

mysql独立表空间和共享表空间的转换

细节参看网址:http://www.linuxidc.com/Linux/2015-01/111241.htm 后边内容。     共享表空间和独立表空间之间的转换,这也是很有用的。因为在用x...
  • donghaixiaolongwang
  • donghaixiaolongwang
  • 2017-03-06 14:18:07
  • 548
收藏助手
不良信息举报
您举报文章:mysql表空间
举报原因:
原因补充:

(最多只允许输入30个字)