|
|
51CTO旗下网站
|
|
移动端

一文看懂MySQL如何判断InnoDB表是独立表空间还是共享表空间

InnoDB采用按表空间(tablespace)的方式进行存储数据,那么独立表空间有哪些优劣呢?怎么去判断XX表是独立表空间还是共享表空间?

作者:波波说运维来源:今日头条|2020-03-22 21:46

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"文件。只是这个方法很笨,对于生产环境,大量的表通过这种方式判别,确实不是一个好方法。

  1. --innodb_file_per_table=1 
  2. show variables like 'innodb_file_per_table'; 
  3. create table Independent_tablespace(name  varchar(64)); 
  4. ls -lrt independent_tablespace.* 
  5. --innodb_file_per_table=0 
  6. show variables like 'innodb_file_per_table'; 
  7. create table common_tablespace(name varchar(64)); 
  8. ls -lrt independent_tablespace.* 

一文看懂MySQL如何判断InnoDB表是独立表空间还是共享表空间

一文看懂MySQL如何判断InnoDB表是独立表空间还是共享表空间

2. INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别

MySQL 5.6的INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 关于这个系统表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用),INNODB的表空间信息。

  1. --共享表空间 
  2. SELECT TABLE_SCHEMA 
  3.     ,TABLE_NAME 
  4.     ,TABLE_TYPE 
  5.     ,N'共享表空间' AS TABLE_SPACE 
  6.     ,ENGINE 
  7.     ,VERSION 
  8.     ,TABLE_ROWS 
  9.     ,AVG_ROW_LENGTH 
  10.     ,CREATE_TIME 
  11.     ,UPDATE_TIME 
  12. FROM INFORMATION_SCHEMA.TABLES  T 
  13. LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME 
  14. WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB'
  15. --独立表空间 
  16. SELECT TABLE_SCHEMA 
  17.     ,TABLE_NAME 
  18.     ,TABLE_TYPE 
  19.     ,N'独立表空间' AS TABLE_SPACE 
  20.     ,ENGINE 
  21.     ,VERSION 
  22.     ,TABLE_ROWS 
  23.     ,AVG_ROW_LENGTH 
  24.     ,CREATE_TIME 
  25.     ,UPDATE_TIME 
  26. FROM INFORMATION_SCHEMA.TABLES  T 
  27. INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME 
  28. WHERE T.TABLE_SCHEMA='HWB'  AND T.ENGINE='InnoDB'

一文看懂MySQL如何判断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 分别表示独立表空间和共享表空间。

  1. --独立表空间 
  2. SELECT * FROM  INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE_TYPE='Single'
  3. SELECT TABLE_SCHEMA 
  4.     ,TABLE_NAME 
  5.     ,TABLE_TYPE 
  6.     ,N'独立表空间' AS TABLE_SPACE 
  7.     ,ENGINE 
  8.     ,VERSION 
  9.     ,TABLE_ROWS 
  10.     ,AVG_ROW_LENGTH 
  11.     ,CREATE_TIME 
  12.     ,UPDATE_TIME 
  13. FROM INFORMATION_SCHEMA.TABLES  T 
  14. INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME 
  15. WHERE T.TABLE_SCHEMA='HWB'  AND T.ENGINE='InnoDB'
  16.  
  17. --共享表空间 
  18. SELECT TABLE_SCHEMA 
  19.     ,TABLE_NAME 
  20.     ,TABLE_TYPE 
  21.     ,N'共享表空间' AS TABLE_SPACE 
  22.     ,ENGINE 
  23.     ,VERSION 
  24.     ,TABLE_ROWS 
  25.     ,AVG_ROW_LENGTH 
  26.     ,CREATE_TIME 
  27.     ,UPDATE_TIME 
  28. FROM INFORMATION_SCHEMA.TABLES  T 
  29. LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES I ON  CONCAT(T.TABLE_SCHEMA, '/',T.TABLE_NAME)=I.NAME 
  30. WHERE I.NAME IS NULL  AND T.TABLE_SCHEMA='HWB' AND T.ENGINE='InnoDB'

一文看懂MySQL如何判断InnoDB表是独立表空间还是共享表空间

【编辑推荐】

  1. MySQL:第一次看到有人把MySQL主从复制讲解的这么清楚
  2. 深入理解 MySQL 索引底层原理
  3. 一文彻底搞懂MySQL分区
  4. 为什么MySQL的常用引擎都默认使用B+树作为索引?
  5. MySQL引起的CPU消耗过大,你会如何优化?
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

秒杀高并发白话实战

秒杀高并发白话实战

主流高并发架构
共15章 | 51CTO崔皓

57人订阅学习

网络排障一点通

网络排障一点通

网络排障及优化调整案例
共20章 | 捷哥CCIE

458人订阅学习

VMware NSX 入门到实战

VMware NSX 入门到实战

网络虚拟化革命性技术
共16章 | Cloud袁

227人订阅学习

视频课程+更多

Web安全原理与防御

Web安全原理与防御

讲师:Web安全探究者65340人学习过

Excel企业实战系列5:数据整理类问题(单表单文件的)

Excel企业实战系列5:数据整理类问题(单表单

讲师:王子宁23412人学习过

【精品】PMP

【精品】PMP

讲师:李骐23806人学习过

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微