在@BhupeshPant提供的优秀答案基础上,此处补充:
InnoDB的存储被划分为表空间。一个表空间是与多个数据文件(对象)相关联的逻辑结构。每个表空间包含页面(块)、区和段。
![enter image description here](https://istack.dev59.com/6YgLY.webp)
页面 - InnoDB中可容纳一个或多个行(取决于行大小)的最小数据单元,也称为块。
通常,每个表都有自己的表空间,与一个数据文件相关联。
以下命令显示完整的表空间列表及其相应的数据文件 -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
为了更好地理解,请创建一个示例数据库 -
database1
。
CREATE DATABASE database1;
USE database1;
现在创建一个示例表
table1
-
CREATE TABLE table1 (
-> col1 INT
-> );
现在我们可以看到相应的表空间和数据文件也已被创建 -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+
| TABLESPACE_NAME | FILE_NAME |
+
|database1/table1 | ./database1/table1.ibd |
+
这里的.ibd
扩展名表示Innodb数据文件。
现在创建另一个示例表table2
-
CREATE TABLE table2 (
-> col1 INT
-> );
我们可以看到相应的表空间和数据文件已经被创建 -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+
| TABLESPACE_NAME | FILE_NAME |
+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
+
这里系统正在为表空间创建默认名称。这些由系统生成的表空间称为 - 单表表空间 (Single-Table tablespaces)
只有当innodb_file_per_table
为ON
时,系统才会创建单表表空间。
可以通过以下方式进行检查 -
SHOW VARIABLES LIKE "innodb_file_per_table";
+
| Variable_name | Value |
+
| innodb_file_per_table | ON |
+
默认情况下,该值为ON。它可以通过-关闭。
SET GLOBAL innodb_file_per_table = OFF;
SHOW VARIABLES LIKE "innodb_file_per_table";
+
| Variable_name | Value |
+
| innodb_file_per_table | OFF |
+
如果该值关闭,将不会创建单表表空间。
为了演示这一点,我们再次创建一个示例表 -
CREATE TABLE table10 (
-> col1 INT
-> );
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+
| TABLESPACE_NAME | FILE_NAME |
+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
+
因此,我们可以看到没有名为
database1/table10
的表空间被创建。
在这种情况下,表格被存储在
innodb_system
表空间中。
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES WHERE FILE_NAME LIKE "%ibdata1";
+
| TABLESPACE_NAME | FILE_NAME |
+
| innodb_system | ./ibdata1 |
+
然而,这并不是一个好的实践,因为它会给系统表空间增加更多负担,导致性能相关问题。
在系统生成的表空间中,当删除表时,表空间也会自动被删除。
还有另一种类型的表空间,称为常规表空间,我们可以为表空间创建自定义名称。
为此,我们需要发出以下命令 -
CREATE TABLESPACE tablespace1 ADD DATAFILE '/var/lib/mysql/tablespace1.ibd' engine = Innodb;
我们可以检查表空间和相应的数据文件是否已被创建 -
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+
| TABLESPACE_NAME | FILE_NAME |
+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
| tablespace1 | /var/lib/mysql/tablespace1.ibd |
+
所有的表空间都创建在Mysql的datadir
中,默认情况下为/var/lib/mysql/
可以通过以下命令检查datadir
的值 -
SELECT @@datadir;
+
| @@datadir |
+
| /var/lib/mysql/ |
+
MySQL只允许在表空间中添加一个数据文件。但是在Oracle或SQL Server中,可以向表空间添加多个数据文件。
要在名为 tablespace1
的表空间中创建表,我们需要发出以下命令 -
CREATE TABLE table3 (
-> col1 INT
-> )
-> TABLESPACE = tablespace1;
对于一般的表空间,可以在一个表空间内创建多个表-
CREATE TABLE table4 (
-> col1 INT
-> )
-> TABLESPACE = tablespace1;
在创建表空间时,不一定需要专门指定数据文件的位置。以下命令也可以使用 -
CREATE TABLESPACE tablespace2 ADD DATAFILE 'tablespace2.ibd' engine = Innodb;
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+
| TABLESPACE_NAME | FILE_NAME |
+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
| tablespace1 | /var/lib/mysql/tablespace1.ibd |
| tablespace2 | ./tablespace2.ibd |
+
默认情况下,所有表空间都会创建在 datadir
目录中。
表空间会创建一个默认值的页面大小。但是,在创建表空间时我们也可以设置自己的页面大小。但是,该大小必须在默认页面大小范围内,否则会出现错误。默认页面大小可以在安装 MySQL 服务器期间设置。
允许的页面大小为 - 4 kb、8 kb、16 kb、32 kb、64 kb。
一般情况下,在安装过程中,16 kb 被设置为默认页面大小。
以下命令用于查看默认页面大小 -
SHOW VARIABLES LIKE 'innodb_page_size';
+
| Variable_name | Value |
+
| innodb_page_size | 16384 |
+
创建自定义页面大小的表空间的命令为 -
CREATE TABLESPACE tablespace3_8kb ADD DATAFILE '/var/lib//mysql/tablespace3_8kb.ibd' FILE_BLOCK_SIZE = 8192 ENGINE = Innodb;
SELECT TABLESPACE_NAME, FILE_NAME FROM information_schema.FILES;
+
| TABLESPACE_NAME | FILE_NAME |
+
| database1/table1 | ./database1/table1.ibd |
| database1/table2 | ./database1/table2.ibd |
| tablespace1 | /var/lib/mysql/tablespace1.ibd |
| tablespace2 | ./tablespace2.ibd |
| tablespace3_8kb | /var/lib//mysql/tablespace3_8kb.ibd |
+
要将表存储到用户定义页面大小的表空间中,需要使用不同的语法-
CREATE TABLE table5 (
-> col1 INT
-> )
-> TABLESPACE = tablespace3_8kb
-> KEY_BLOCK_SIZE = 8;
通常由系统生成的表空间与一般表空间不同,如果我们删除表时,一般表空间不会自动删除,需要显式地将表空间删除。
命令是 -
DROP TABLESPACE tablespace_name;
CREATE TABLESPACE
语句感兴趣。 - bishopSHOW CREATE TABLE
和其他相关信息以了解正在发生的情况。 - Rick Jamesmysql_update
吗? - Rick James