网站首页 > 基础教程 正文
概述
InnoDB采用按表空间(tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB, 名字为ibdata1的文件, 该文件就是默认的表空间文件(tablespce file),用户可以通过参数innodb_data_file_path对其进行设置,可以有多个数据文件,如果没有设置innodb_file_per_table的话, 那些Innodb存储类型的表的数据都放在这个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会产生一个独立表空间。那么独立表空间有哪些优劣呢?怎么去判断XX表是独立表空间还是共享表空间?
一、独立表空间优缺点
1、优点
1) 每个表都有自已独立的表空间。
2)每个表的数据和索引都会存在自已的表空间中。
3)可以实现单表在不同的数据库中移动。
4)空间可以回收(除drop table操作处,表空不能自已回收)
- Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
- 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
- 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
2、缺点
单表增加过大,如超过100个G。
二、判别数据表是独立表空间还是共享表空间
1、通过ibd文件判别
如果表的存储引擎是InnoDB,而且表空间(tablespace)是共享表空间的话,那么数据库对应目录下面是没有"表名.ibd"文件的。独立表空间的表的话,则有"表名.ibd"文件。只是这个方法很笨,对于生产环境,大量的表通过这种方式判别,确实不是一个好方法。
--innodb_file_per_table=1
show variables like 'innodb_file_per_table';
create table Independent_tablespace(name varchar(64));
ls -lrt independent_tablespace.*
--innodb_file_per_table=0
show variables like 'innodb_file_per_table';
create table common_tablespace(name varchar(64));
ls -lrt independent_tablespace.*
2、INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别
MySQL 5.6的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 关于这个系统表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用),INNODB的表空间信息。
--共享表空间
SELECT TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
,N'共享表空间' AS TABLE_SPACE
,ENGINE
,VERSION
,TABLE_ROWS
,AVG_ROW_LENGTH
,CREATE_TIME
,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB';
--独立表空间
SELECT TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
,N'独立表空间' AS TABLE_SPACE
,ENGINE
,VERSION
,TABLE_ROWS
,AVG_ROW_LENGTH
,CREATE_TIME
,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB';
3、INFORMATION_SCHEMA.INNODB_SYS_TABLES判别(推荐)
MySQL 5.7 的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES中多了SPACE_TYPE字段,不过其值全部为Single,而INFORMATION_SCHEMA.INNODB_SYS_TABLES中也多了字段SPACE_TYPE, 其值有Single与System 分别表示独立表空间和共享表空间。
--独立表空间
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE_TYPE='Single';
SELECT TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
,N'独立表空间' AS TABLE_SPACE
,ENGINE
,VERSION
,TABLE_ROWS
,AVG_ROW_LENGTH
,CREATE_TIME
,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB';
--共享表空间
SELECT TABLE_SCHEMA
,TABLE_NAME
,TABLE_TYPE
,N'共享表空间' AS TABLE_SPACE
,ENGINE
,VERSION
,TABLE_ROWS
,AVG_ROW_LENGTH
,CREATE_TIME
,UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME
WHERE I.NAME IS NULL AND T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB';
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
猜你喜欢
- 2025-01-14 手机帝国存档怎么修改 存档简单修改教程
- 2025-01-14 文档在线预览(四) 总结篇
- 2025-01-14 MySQL数据库表碎片收集整理那些事
- 2025-01-14 刘心向学(8)指针数组的应用
- 2025-01-14 C#控件美化之路(11):控件ListBox增加DataTable数据绑定功能
- 2025-01-14 SpringBoot实现MySQL数据库自动备份管理系统
- 2025-01-14 一键生成数据库文档,从此告别人工整理文档
- 2025-01-14 all about visitor management system you need to know 03
- 2025-01-14 一篇文章教会你进行Mysql数据库和数据表的基本操作
- 2025-01-14 C#中FastReport报表的使用总结
- 最近发表
- 标签列表
-
- gitpush (61)
- pythonif (68)
- location.href (57)
- tail-f (57)
- pythonifelse (59)
- deletesql (62)
- c++模板 (62)
- css3动画 (57)
- c#event (59)
- linuxgzip (68)
- 字符串连接 (73)
- nginx配置文件详解 (61)
- html标签 (69)
- c++初始化列表 (64)
- exec命令 (59)
- canvasfilltext (58)
- mysqlinnodbmyisam区别 (63)
- arraylistadd (66)
- node教程 (59)
- console.table (62)
- c++time_t (58)
- phpcookie (58)
- mysqldatesub函数 (63)
- window10java环境变量设置 (66)
- c++虚函数和纯虚函数的区别 (66)