当前位置：Gxlcms > 数据库问题 > 看懂MySQL怎样判断InnoDB表是独立表空间还是共享表空间

看懂MySQL怎样判断InnoDB表是独立表空间还是共享表空间

时间：2021-07-01 10:21:17 帮助过：33人阅读

本文参考原文-http://bjbsair.com/2020-03-22/tech-info/5348/
概述

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. <code>--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.*
  9. </code>

技术图片

技术图片

2、INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别

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

  1. <code>--共享表空间  
  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‘;
  29. </code>

技术图片

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. <code>--独立表空间  
  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‘;
  31. </code>

技术图片

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

本文参考原文-http://bjbsair.com/2020-03-22/tech-info/5348/
概述

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. <code>--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.*
  9. </code>

技术图片

技术图片

2、INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别

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

  1. <code>--共享表空间  
  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‘;
  29. </code>

技术图片

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. <code>--独立表空间  
  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‘;
  31. </code>

技术图片

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

本文参考原文-http://bjbsair.com/2020-03-22/tech-info/5348/
概述

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. <code>--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.*
  9. </code>

技术图片

技术图片

2、INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别

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

  1. <code>--共享表空间  
  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‘;
  29. </code>

技术图片

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. <code>--独立表空间  
  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‘;
  31. </code>

技术图片

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

本文参考原文-http://bjbsair.com/2020-03-22/tech-info/5348/
概述

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. <code>--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.*
  9. </code>

技术图片

技术图片

2、INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别

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

  1. <code>--共享表空间  
  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‘;
  29. </code>

技术图片

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. <code>--独立表空间  
  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‘;
  31. </code>

技术图片

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

本文参考原文-http://bjbsair.com/2020-03-22/tech-info/5348/
概述

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. <code>--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.*
  9. </code>

技术图片

技术图片

2、INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES判别

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

  1. <code>--共享表空间  
  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‘;
  29. </code>

技术图片

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. <code>--独立表空间  
  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‘;
  31. </code>

技术图片

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

看懂MySQL怎样判断InnoDB表是独立表空间还是共享表空间

标签：如何   配置   varchar   inf   表空间   cat   sql   type   格式   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！