mysql 表空间

什么是表空间
默认情况下，InnoDB只包含一个称为系统表空间的表空间，其标识符为0.可以使用innodb_file_per_table配置参数间接创建更多表空间。表空间由一系列文件组成。文件的大小不必被数据库块大小整除，因为我们可能只保留最后一个未完成的块。将新文件追加到表空间时，还会指定文件的最大大小。目前，我们认为最好在创建文件时将文件扩展到其最大大小，因为当表空间需要更多空间时，我们可以避免动态扩展文件。数据文件是动态扩展的，但重做日志文件是预先分配的。另外，如前所述，只有系统表空间可以有多个数据文件。还清楚地提到，即使表空间可以有多个文件，它们也被认为是连接在一起的单个大文件。因此表空间中文件的顺序很重要。

一个数据文件，可以保存一个或多个InnoDB表和相关索引的数据。

根据每个表的信息通道配置，有许多类型的表空间。这些是，

a：系统表空间

b：每个表空间的文件

c：常规表空间

系统表空间包含，

1.InnoDB数据字典。
2.DoubleWrite缓冲区。
3.改变缓冲区
4.撤消日志。
除此之外它还包含，

1.表
2.索引数据
相关文件是 .idbdata1

innodb_file_per_table选项在MySQL 5.6及更高版本中默认启用，允许在每个表的文件表空间中创建表，每个表都有一个单独的数据文件。启用innodb_file_per_table选项可以使用其他MySQL功能，例如表压缩和可传输表空间。相关文件是 .idbd

InnoDB在MySQL 5.7.6中引入了通用表空间。常规表空间是使用CREATE TABLESPACE语法创建的共享表空间。它们可以在MySQL数据目录之外创建，能够保存多个表，并支持所有行格式的表。

怎么通过SQL语句查看MySQL数据库的表空间状态

 1. 查看数据库的索引空间大小，运行以下SQL语句：

-- 以GB为单位

# 查看mysql库, 如果想查看其他库，改变结尾数据库名称
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'mysql';

 

 

 

 -- 以MB为单位

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 6), ' MB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'mysql';

查看数据库中所有表的信息

在MySQL Workbench中运行以下SQL语句，查看数据库中所有表的表名、表行数、数据空间大小、索引空间大小和总大小：

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
table_rows AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),6),' MB') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024),6),' MB') AS 'Index Size',
CONCAT(ROUND((data_length+index_length)/(1024*1024),6),' MB') AS'Total Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'database';

其中，database是待查看数据库的名称，例如：mysql。运行结果如下图所示：