MySQL数据存储

MySQL数据存储

Innodb存储引擎的数据存储，可以使用两种方式进行存储：系统表空间和独立表空间

-- ON表示使用的是独立表空间
-- OFF表示使用的是系统表空间
show variables like '%innodb_file_per_table%'

系统表空间(共享表空间)

在MySQL5.5之前默认使用的是系统表空间，系统表空间是指每一个数据库所有的表数据，索引文件全部放在一个文件中，文件名为ibdataX(X代表第几个文件，从1开始)，默认初始化是12M

show variables like 'innodb_data%';

Variable_name           Value
innodb_data_file_path ibdata1:12M:autoextend
innodb_data_home_dir 

autoextend表示自动扩展，当文件存储满时会自动进行扩展，默认增量是64M

show variables like 'innodb_autoextend_increment';

Variable_name               Value
innodb_autoextend_increment 64

优点

表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制

缺点

• 数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片
• 如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈

独立表空间

在MySQL5.6中默认使用的是独立表空间，每一个表都会生成独立的文件来进行存储，以.frm表描述文件(存储表的结构)，还有.ibd文件(存储一个表的数据内容以及索引内容)

优点

• 独立表空间可以通过 optimeze table 收缩系统文件，不需要重启服务器也不会影响对表的正常访问
• 独立表空间可以同时向多个文件刷新数据
• 可以实现单表在不同数据库中进行移动

缺点

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

如何将系统表空间的表转为独立表空间呢？

1. 使用mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出 ）可以在从服务器上操作
2. 停止Mysql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）
3. 重启Mysql，并重建Innodb系统表空间
4. 重新导入数据

alter table 同样可以的转移，但是无法回收系统表空间中占用的空间

https://zhhll.icu/2021/数据库/关系型数据库/MySQL/进阶/18.MySQL数据存储/

本文由 mdnice 多平台发布