今天我要跟你分享的话题是：“大家常说的表空间到底是什么？究竟什么又是数据表？”
这其实是一个概念性的知识点，当作拓展知识。涉及到的概念大家了解一下就好，涉及的参数，留个印象就好。
从 InnoDB存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间( tablespace)。表空间又由段(segment)、区( extent)、页(page)组成。页在一些文档中有时也称为块( block), InnoDB存储引擎的逻辑存储结构大致如图所示。
一、什么是表？
但凡是用过MySQL都知道，直观上看，MySQL的数据都存在数据表中。
比如一条Update SQL：
update user set username = '白日梦' where id = 999;
它将user这张数据表中id为1的记录的username列修改成了‘白日梦’
这里的user其实就是数据表。当然这不是重点，重点是我想表达：数据表其实是逻辑上的概念。而下面要说的表空间是物理层面的概念。
二、什么是表空间？
不知道你有没有看到过这句话：“在innodb存储引擎中数据是按照表空间来组织存储的”。其实有个潜台词是：表空间是表空间文件是实际存在的物理文件。
大家不用纠结为啥它叫表空间、为啥表空间会对应着磁盘上的物理文件，因为MySQL就是这样设计、设定的。直接接受这个概念就好了。
MySQL有很多种表空间，下面一起来了解一下。
三、sys表空间
你可以像下面这样查看你的MySQL的系统表空间
Value部分的的组成是：name:size:attributes
默认情况下，MySQL会初始化一个大小为12MB，名为ibdata1文件，并且随着数据的增多，它会自动扩容。
这个ibdata1文件是系统表空间，也是默认的表空间，也是默认的表空间物理文件，也是传说中的共享表空间。
关于这个共享表空间，直观上看，如果这个表空间能为multiple tables.存储数据，那么它就可以被称为共享表空间，所以你可以认为系统表空间是共享表空间。
四、配置sys表空间
系统表空间的数量和大小可以通过启动参数：innodb_data_file_path
# my.cnf [mysqld] innodb_data_file_path=/dir1/ibdata1:2000M;/dir2/ibdata2:2000M:autoextend
五、file per table 表空间
如果你想让每一个数据库表都有一个单独的表空间文件的话，可以通过参数innodb_file_per_table设置。
这个参数只有在MySQL5.6或者是更高的版本中才可以使用。
可以通过配置文件
[mysqld] innodb_file_per_table=ON
也可以通过命令
mysql> SET GLOBAL innodb_file_per_table=ON;
让你将其设置为ON，那之后InnoDB存储引擎产生的表都会自己独立的表空间文件。
独立的表空间文件命名规则：表名.ibd
注意：
独立表空间文件中仅存放该表对应数据、索引、insert buffer bitmap。
其余的诸如：undo信息、insert buffer 索引页、double write buffer 等信息依然放在默认表空间，也就是共享表空间中。
需要先了解即使你设置了innodb_file_per_table=ON 共享表空间的体量依然会不断的增长，并且你即使你不断的使用undo进行rollback，共享表空间大小也不会缩减就好了。
查看我的表空间文件：
最后再简述一下这种file per table的优缺点：
优点：
- 提升容错率，表A的表空间损坏后，其他表空间不会收到影响。s
- 使用MySQL Enterprise Backup快速备份或还原在每表文件表空间中创建的表，不会中断其他InnoDB 表的使用
缺点：
对fsync系统调用来说不友好，如果使用一个表空间文件的话单次系统调用可以完成数据的落盘，但是如果你将表空间文件拆分成多个。原来的一次fsync可能会就变成针对涉及到的所有表空间文件分别执行一次fsync，增加fsync的次数。
六、临时表空间
临时表空间用于存放用户创建的临时表和磁盘内部临时表。
参数innodb_temp_data_file_path定义了临时表空间的一些名称、大小、规格属性如下图：
查看临时表空间文件存放的目录
七、undo表空间
相信你肯定听过说undolog，常见的当你的程序想要将事物rollback时，底层MySQL其实就是通过这些undo信息帮你回滚的。
在MySQL的设定中，有一个表空间可以专门用来存放undolog的日志文件。
然而，在MySQL的设定中，默认的会将undolog放置到系统表空间中。
如果你的MySQL是新安装的，那你可以通过下面的命令看看你的MySQL undo表空间的使用情况：
大家可以看到，我的MySQL的undo log 表空间有两个。
也就是我的undo从默认的系统表空间中转移到了undo log专属表空间中了。
那undo log到底是该使用默认的配置放在系统表空间呢？还是该放在undo表空间呢？
这其实取决服务器使用的存储卷的类型。
如果是SSD存储，那推荐将undo info存放在 undo表空间中。
八、mysql表碎片清理和表空间收缩
mysql表碎片清理和表空间收缩(即清理碎片后report_site_day.ibd文件磁盘空间减小,该方案基于独立表空间存储方式)
OPTIMIZETABLE [tablename],当然这种方式只适用于独立表空间
清除碎片的优点:
降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率。
OPTIMIZE TABLE ipvacloud.report_site_day;对myisam表有用 对innodb也有用，系统会自动把它转成 ALTER TABLE report_site_day ENGINE = Innodb; 这是因为optimize table的本质，就是alter table
所以不管myisam引擎还是innodb引擎都可以使用OPTIMIZE TABLE回收表空间。
mysql innodb引擎 长时间使用后，数据文件远大于实际数据量(即report_site_day.ibd文件越来越大)，导致空间不足。
就是我的mysql服务器使用了很久之后，发现\data\ipvacloud\report_site_day.ibd 目录的空间占满了我系统的整个空间，马上就要满了。
MySQL5.5默认是共享表空间 ，5.6中默认是独立表空间(表空间管理类型就这2种)
独立表空间 就是采用和MyISAM 相同的方式, 每个表拥有一个独立的数据文件( .idb )
1.每个表都有自已独立的表空间。
2.每个表的数据和索引都会存在自已的表空间中。
3.可以实现单表在不同的数据库中移动(将一个库的表移动到另一个库里,可以正常使用)。
4.drop table自动回收表空间 ，删除大量数据后可以通过alter table XX engine = innodb;回收空间。
InnoDB引擎 frm ibd文件说明：
1.frm ：描述表结构文件，字段长度等2.ibd文件
a如果采用独立表存储模式(5.6)，data\a中还会产生report_site_day.ibd文件（存储数据信息和索引信息）D:\java\mysql5.6\data\ipvacloudreport_site_day.frm 和
D:\java\mysql5.6\data\ipvacloud\report_site_day.ibd
b如果采用共享存储模式(5.5)，数据信息和索引信息都存储在ibdata1中
(其D:\java\mysql5.6\data\目录下没有.ibd文件,只有frm文件)
D:\java\mysql5.5\data\ipvacloudreport_site_day.frm
查看当前数据库的表空间管理类型(on表示独立表空间开启,5.6默认开启独立) 脚本：show variables like "innodb_file_per_table"; mysql> show variables like "innodb_file_per_table";
1、小结
结合mysql官方网站的信息，个人是这样理解的。当你删除数据时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频烦的表，要定期进行optimize，一个月一次，看实际情况而定了。
举个例子来说吧。有100个php程序员辞职了，但是呢只是人走了，php的职位还在那里，这些职位不会撤销，要等新的php程序来填补这些空位。招一个好的程序员，比较难。我想大部分时间会空在那里。哈哈。
当我们使用mysql进行delete数据，delete完以后，发现空间文件ibd并没有减少，这是因为碎片空间的存在，举个例子，一共公司有10号员工，10个座位，被开除了7个员工，但这些座位还是保留的，碎片整理就像，让剩下的3个员工都靠边坐，然后把剩下的7个作为给砸掉，这样就能释放出空间了
好处除了减少表数据与表索引的物理空间，还能降低访问表时的IO，这个比较理解，整理之前，取数据需要跨越很多碎片空间，这时需要时间的，整理后，想要的数据都放在一起了，直接拿就拿到了，效率提高。
2、手册中关于OPTIMIZE的一些用法(标红的为应用场景)和描述
OPTIMIZE TABLE tbl_name [, tbl_name] …
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
碎片产生的原因
(删除时留白, 插入时尝试使用留白空间 (当删除后并未将所有留空的都插入数据,既可以认为未被立即使用的留空就是碎片)
（1）表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；
（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
（3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；
一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，仍看做是10万字节的表进行处理，所以，碎片越多，会降低访问表时的IO,影响查询性能。
3、备注：
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE 运行过程中，MySQL会锁定表。
4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld –skip-new或者mysqld –safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。
OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作。
比较好的方式就是做个shell,定期检查mysql中 information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片。
问题产生: 例如你有1个表格里面有约10000000条，大概10G的数据，但是你手动删除了5000000条数据，即约5G的数据，但是删除后，你会发现系统的空间还是占用了10G，
解决方案: 表空间收缩即D:\java\mysql5.6\data\ipvacloud\report_site_day.ibd文件变小。
create database frag_test;
use frag_test;
create table frag_test (c1 varchar(64));
insert into frag_test values ('this is row 1');
insert into frag_test values ('this is row 2');
insert into frag_test values ('this is row 3');
insert into frag_test values ('this is row 4');
insert into frag_test values ('this is row 5');
SELECT * FROM frag_test;
-- 碎片查看(即查看frag_test库下所有表的状态,1条记录是1个表) frag_test是库名
-- 需要注意的是，“data_free”一栏显示出了我们删除一行后所产生的留空空间 删除前 Data_free: 0字节 删除一条记录后再查看碎片 Data_free: 20字节
-- 如果没有及时插入,那么删除一条记录后,留空的20字节就变成碎片; 现在如果你将两万条记录删到只剩一行，
-- 列表中有用的内容将只占二十字节，但MySQL在读取中会仍然将其视同于一个容量为四十万字节的列表进行处理，并且除二十字节以外，其它空间都被白白浪费了。
-- 现在我们删除一行，并再次检测:
delete from frag_test where c1 = 'this is row 2';
-- 删除一条记录后再查看碎片 Data_free: 20字节 即留空了20字节 data_free 是碎片空间
show table status from frag_test;
--字段解释：
--Data_length : 数据的大小。
--Index_length: 索引的大小。
--Data_free :数据在使用中的留存空间，如果经常删改数据表，会造成大量的Data_free 频繁 删除记录 或修改有可变长度字段的表
-- data_free碎片空间 TABLE_SCHEMA后等于表名 (data_length+index_length)数据和数据索引的之和的空间 data_free/data_length+index_length>0.30 的表认为是需要清理碎片的表
select table_schema db,table_name,engine,table_rows,data_free,data_length+index_length length from information_schema.tables where TABLE_SCHEMA='frag_test';
-- table_schema db, table_name, data_free, engine依次表示 数据库名称 表名称 碎片所占字节空间 表引擎名称
-- 列出所有已经产生碎片的表 ('information_schema', 'mysql'这两个库是mysql自带的库)
select table_schema db, table_name, data_free, engine,table_rows,data_length+index_length length
from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;
-- 库名.表名 清理2个表的碎片(逗号隔开即可) OPTIMIZE TABLE ipvacloud.article,ipvacloud.aspnet_users_viewway;
-- 存储过程里的table_schema就是数据库名称 虽然提示 Table does not support optimize, doing recreate + analyze instead 该命令执行完毕后 返回命令,虽然提示不支持optimize，但是已经进行重建和分析，空间已经回收(即碎片得到整理,表空间得到回收)。 原来对于InnoDB 通过该命令还是有用的，OPTIMIZE TABLE ipvacloud.article;
OPTIMIZE TABLE ipvacloud.article;
-- 清除碎片操作会暂时锁表，数据量越大，耗费的时间越长 可以做个脚本，例如每月凌晨3点，检查DATA_FREE字段，
-- 大于自己认为的警戒值(碎片空间占数据和数据索引空间之和的百分比>0.30)的话，就清理一次
/*
清理mysql下实例下表碎片(当碎片字节空间占 数据字节与索引字节空间 之和大于0.30时, 这些表的碎片都需要清理,使用游标遍历清理) 定时任务事件 每月凌晨4点调用此清理表碎片的任务
table_schema是数据库名 OPTIMIZE TABLE ipvacloud.article;
*/
DROP PROCEDURE IF EXISTS `optimize_table`;
DELIMITER ;;
CREATE PROCEDURE `optimize_table`()
BEGIN
DECLARE tableSchema VARCHAR(100);
DECLARE tableName VARCHAR(100);
DECLARE stopFlag INT DEFAULT 0;
-- 大于30%碎片率的清理
DECLARE rs CURSOR FOR SELECT table_schema,table_name FROM information_schema.tables WHERE ((data_free/1024)/((data_length+index_length+data_free)/1024)) > 0.30;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;
OPEN rs;
WHILE stopFlag <> 1 DO
FETCH NEXT FROM rs INTO tableSchema,tableName;
IF stopFlag<>1 THEN
-- SET @table_optimize = CONCAT('ALTER TABLE `',tableName,'` ENGINE = INNODB');
SET @table_optimize = CONCAT('OPTIMIZE TABLE `',table_schema,'`.`',tableName,'`');
PREPARE sql_optimize FROM @table_optimize;
EXECUTE sql_optimize;
END IF;
END WHILE;
CLOSE rs;
END
;;
DELIMITER ;
/*
此定时任务 事件每月凌晨4点清理mysql实例下的表碎片
*/
DROP EVENT IF EXISTS `event_optimize_table`;
DELIMITER ;;
CREATE EVENT `event_optimize_table` ON SCHEDULE EVERY 1 MONTH STARTS '2017-12-15 04:00:00' ON COMPLETION PRESERVE ENABLE DO CALL optimize_table()
;;
DELIMITER ;
如何缩小共享表空间 optimize table xxx; 对共享表空间不起作用
如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。
无法自动收缩，必须数据导出，删除ibdata1，然后数据导入(数据库变为独享表空间)
解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。
本地mysql5.5创建一个ipvacloud库,将其他服务的1张表数据传输到本地的ipvacloud
ibdata1(ibdata1存放数据和索引等)文件从18M增加到178M 其ipvacloud文件下只新增了frm文件 即D:\java\mysql5.5\data\ipvacloud\report_site_day.frm
导出数据(navicat导出或mysqldump)
关闭MySQL服务：
删除ibdat1、ib_log*和应用数据库目录
更改myini文件(在最后一行添加innodb_file_per_table=1)
启动mysql 使用此命令看(ibdata1又回到初始的18M,ipvacoud已是空的) 独立表空间是否开启成功(on表示开启成功)show variables like ‘%per_table%’;
导入数据库 .sql文件(导入数据成功后ibdat1从18M增加到34M, 独立表空间有ibd文件,来存放数据和索引信息)
将表由共享表空间迁移到了独立表空间中，同时也是对共享表空间”瘦身”
>mysqldump -h192.168.2.227 -u root -p ipvacloud site_all_info > d:456.sql
参考文章：
https://www.jb51.net/article/200547.htm
https://blog.51cto.com/xiaocao13140/212785
转自：https://www.cnblogs.com/better-farther-world2099/articles/14713523.html