如何在MySQL中选择合适的表空间以优化性能和管理效率

原标题&＃xff1a;MySQL选择合适的表空间-爱可生

表空间的选择&＃xff0c;可以说是对表的日常管理以及访问性能有非常紧密的联系。

表空间是用来管理 MySQL 关系表的一种形式&＃xff0c;有自己的磁盘文件。

MySQL 表空间可分为共享表空间和单表空间&＃xff1b;其中共享表空间又可分为系统表空间和通用表空间。

下面我来逐一看下每种表空间的相关特性。

一、系统表空间

在 MySQL 数据目录下有一个名为 ibdata1 的文件&＃xff0c;可以保存一张或者多张表。

923275 12M -rw-r----- 1 mysql mysql 12M 3月 18 10:42 ibdata1

这个文件就是 MySQL 的系统表空间文件&＃xff0c;默认为 1 个&＃xff0c;可以有多个&＃xff0c;只需要在配置文件 my.cnf 里面这样定义即可。

innodb_data_file_path&＃61;ibdata1:200M;ibdata2:200M:autoextend:max:800M

系统表空间不仅可以是文件系统组成的文件&＃xff0c;也可以是非文件系统组成的磁盘块&＃xff0c;比如裸设备&＃xff0c;定义也很简单

innodb_data_file_path&＃61;/dev/nvme0n1p1:3Gnewraw;/dev/nvme0n1p2:2Gnewraw

系统表空间里都有些啥内容&＃xff1f;

具体内容包括&＃xff1a;double writer buffer、 change buffer、数据字典(MySQL 8.0 之前)、表数据、表索引。

那 MySQL 为什么现在主流版本默认都不是系统表空间&＃xff1f;

究其原因&＃xff0c;系统表空间有三个最大的缺点&＃xff1a;

原因 1&＃xff1a;无法做到自动收缩磁盘空间&＃xff0c;造成很大的空间浪费。

即使它包含的表都被删掉&＃xff0c;这部分空间也不会自动释放。

举个例子&＃xff1a;

# 表 t1

mysql> create table t1(id int, r1 char(36)) tablespace innodb_system;

Query OK, 0 rows affected (0.03 sec)

# ibdata1 初始大小为 12M

mysql> \! ls -sihl ibdata1

923275 12M -rw-r----- 1 mysql mysql 12M 3月 18 15:32 ibdata1

# ... 插入一部分数据

# ...

mysql> select count(*) from t1;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 262144 |

&＃43;----------&＃43;

1 row in set (0.10 sec)

# ibdata1 增长到 76M

mysql> \! ls -sihl ibdata1

923275 76M -rw-r----- 1 mysql mysql 76M 3月 18 15:34 ibdata1

# 删除这张表

mysql> drop table t1;

Query OK, 0 rows affected (0.02 sec)

# 空间并没有释放

mysql> \! ls -sihl ibdata1

923275 76M -rw-r----- 1 mysql mysql 76M 3月 18 15:39 ibdata1

如何才能释放 ibdata1 呢?

这个比较麻烦&＃xff0c;而且严重影响服务可用性&＃xff0c;大致几个步骤&＃xff1a;

1. 用 mysqldump 导出所有表数据&＃xff1b;

2. 关闭 MySQL 服务&＃xff1b;

3. 设置 ibdata1 为默认大小&＃xff1b;

4. source 重新导入数据。

原因 2&＃xff1a;扩容时&＃xff0c;单表分离速度慢。

系统表空间在无限制增大导致磁盘满需要扩容时&＃xff0c;无法快速的把表从系统表空间里分离出来&＃xff0c;必须得经过停服务&＃xff1b;改配置&＃xff1b;扩容&＃xff1b;重新导入数据&＃xff1b;启服务等步骤方才可行。

原因 3&＃xff1a;多张表的数据写入顺序写。

对多张表的写入数据依然是顺序写&＃xff0c;这就致使 MySQL 发布了单表空间来解决这两个问题。

二、单表空间

单表空间不同于系统表空间&＃xff0c;每个表空间和表是一一对应的关系&＃xff0c;每张表都有自己的表空间。具体在磁盘上表现为后缀为 .ibd 的文件。

比如表 t1&＃xff0c;对应的表空间文件为 t1.ibd

917107 96K -rw-r----- 1 mysql mysql 96K 3月 18 16:13 t1.ibd

单表空间如何应用到具体的表呢&＃xff1f;

有两种方式&＃xff1a;

方式 1&＃xff1a;在配置文件中开启。

在配置文件中开启单表空间设置参数 innodb_filer_per_table&＃xff0c;这样默认对当前库下所有表开启单表空间。

innodb_file_per_table&＃61;1

另外也可以直接建表时指定单表空间

mysql> create table t1 (id int, r1 char(36)) tablespace innodb_file_per_table;

Query OK, 0 rows affected (0.04 sec)

单表空间除了解决之前说的系统表空间的几个缺点外&＃xff0c;还有其他的优点&＃xff0c;详细如下&＃xff1a;

1. truncate table 操作比其他的任何表空间都快&＃xff1b;

2. 可以把不同的表按照使用场景指定在不同的磁盘目录&＃xff1b;

比如日志表放在慢点的磁盘&＃xff0c;把需要经常随机读的表放在 SSD 上等。

mysql> create table ytt_dedicated (id int) data directory &＃61; &＃39;/var/lib/mysql-files&＃39;;

Query OK, 0 rows affected (0.04 sec)

3. 可以用 optimize table 来收缩或者重建经常增删改查的表。

一般过程是这样的&＃xff1a;建立和原来表一样的表结构和数据文件&＃xff0c;把真实数据复制到临时文件&＃xff0c;再删掉原始表定义和数据文件&＃xff0c;最后把临时文件的名字改为和原始表一样的。

举个例子&＃xff0c;表 t1 之前的大小 324M

root&＃64;ytt-pc:/data/ytt/mysql/data/ytt# ls -sihl

总用量 325M

934068 4.0K -rw-r----- 1 mysql mysql 67 3月 6 23:01 db.opt

917593 12K -rw-r----- 1 mysql mysql 8.5K 3月 18 16:35 t1.frm

918181 325M -rw-r----- 1 mysql mysql 324M 3月 18 16:38 t1.ibd

进行表重建

mysql> optimize table t1;

&＃43;--------&＃43;----------&＃43;----------&＃43;-------------------------------------------------------------------&＃43;

| Table | Op | Msg_type | Msg_text |

&＃43;--------&＃43;----------&＃43;----------&＃43;-------------------------------------------------------------------&＃43;

| ytt.t1 | optimize | note | Table does not support optimize, doing recreate &＃43; analyze instead |

| ytt.t1 | optimize | status | OK |

&＃43;--------&＃43;----------&＃43;----------&＃43;-------------------------------------------------------------------&＃43;

2 rows in set (10.26 sec)

重建期间抓取到的结果&＃xff1a;如愿以偿看到 # 开头的临时表定义和数据文件。

root&＃64;ytt-pc:/data/ytt/mysql/data/ytt# ls -sihl

总用量 409M

934068 4.0K -rw-r----- 1 mysql mysql 67 3月 6 23:01 db.opt

917100 12K -rw-r----- 1 mysql mysql 8.5K 3月 18 16:38 &＃39;#sql-1791_7.frm&＃39;

917107 85M -rw-r----- 1 mysql mysql 84M 3月 18 16:39 &＃39;#sql-ib51-975102565.ibd&＃39;

917593 12K -rw-r----- 1 mysql mysql 8.5K 3月 18 16:35 t1.frm

918181 325M -rw-r----- 1 mysql mysql 324M 3月 18 16:38 t1.ibd

重建完成&＃xff0c;表 t1 实际占用空间 84M

root&＃64;ytt-pc:/data/ytt/mysql/data/ytt# ls -sihl

总用量 85M

934068 4.0K -rw-r----- 1 mysql mysql 67 3月 6 23:01 db.opt

917100 12K -rw-r----- 1 mysql mysql 8.5K 3月 18 16:38 t1.frm

917107 85M -rw-r----- 1 mysql mysql 84M 3月 18 16:39 t1.ibd

root&＃64;ytt-pc:/data/ytt/mysql/data/ytt#

4. 可以自由移植单表

并不需要移植整个数据库&＃xff0c;可以把单独的表在各个实例之间灵活移植。

比如现在要把 ytt.t1 的数据移植到 ytt2.t1 里。

mysql> create database ytt2;

Query OK, 1 row affected (0.01 sec)

mysql> use ytt2

Database changed

mysql> create table t1 like ytt.t1;

Query OK, 0 rows affected (0.05 sec)

# 进行表数据移植。

mysql> alter table t1 discard tablespace;

Query OK, 0 rows affected (0.02 sec)

root&＃64;ytt-pc:/data/ytt/mysql/data/ytt# cp -rfp /tmp/t1.ibd ../ytt2/

mysql> alter table t1 import tablespace;

Query OK, 0 rows affected (0.38 sec)

# 确认下数据是否一致。

mysql> select (select count(*) from ytt.t1) &＃39;ytt.t1&＃39;,(select count(*) from ytt2.t1) &＃39;ytt2.t1&＃39;;

&＃43;---------&＃43;---------&＃43;

| ytt.t1 | ytt2.t1 |

&＃43;---------&＃43;---------&＃43;

| 2097152 | 2097152 |

&＃43;---------&＃43;---------&＃43;

1 row in set (1.69 sec)

5. 单表空间的表可以使用 MySQL 的新特性&＃xff1b;

比如表压缩&＃xff0c;大对象更优化的磁盘存储等。

6. 可以更好的管理和监控单个表的状态&＃xff1b;

比如在 OS 层可以看到表的大小。

7. 可以解除 InnoDB 系统表空间的大小限制&＃xff1b;

InnoDB 一个表空间最大支持 64TB 数据(针对 16KB 的页大小)。如果是系统表空间&＃xff0c;整个实例都被这个限制&＃xff0c;单表空间则是针对单个表有 64TB 大小限制。

当然了&＃xff0c;单表空间也并不是没有缺点。比如&＃xff1a;当多张表被大量的增删改后&＃xff0c;表空间会有一定的膨胀&＃xff1b;相比系统表空间&＃xff0c;打开表需要的文件描述符增多&＃xff0c;浪费更多的内存。

三、通用表空间

通用表空间先是出现在 MySQL Cluster 里&＃xff0c;也就是 NDB 引擎。从 MySQL 5.7 引入到 InnoDB 引擎。通用表空间和系统表空间一样&＃xff0c;也是共享表空间。每个表空间可以包含一张或者多张表&＃xff0c;也就是说通用表空间和表之间是一对多的关系。

mysql> create tablespace ts1 add datafile &＃39;/var/lib/mysql-files/ts1.ibd&＃39; engine innodb;

Query OK, 0 rows affected (0.02 sec)

mysql> create table t1(id int,r1 datetime) tablespace ts1;

Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(id int,r1 datetime) tablespace ts1;

Query OK, 0 rows affected (0.03 sec)

mysql> create table t3(id int,r1 datetime) tablespace ts1;

Query OK, 0 rows affected (0.03 sec)

通用表空间其实是介于系统表空间和单表空间之间的一种折中的方案。

和系统表空间类似&＃xff0c;不会自动收缩磁盘空间&＃xff1b;

和系统表空间类似&＃xff0c;可以重命名表空间名字&＃xff1b;

和单表空间类似&＃xff0c;可以很方便把表空间文件定义在 MySQL 数据目录之外&＃xff1b;

比单表空间占用更少的文件描述符&＃xff0c;但是又不能像单表空间那样移植表空间。

幸运的是&＃xff0c;可以在这三个表空间里随便切换。不过要注意切换时间点&＃xff0c;毕竟切换涉及到数据的迁移&＃xff0c;类似 copy 文件对系统的影响。

# 表 t1 随时切换各种表空间

mysql> alter table t1 tablespace innodb_file_per_table;

Query OK, 0 rows affected (14.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table t1 tablespace innodb_system;

Query OK, 0 rows affected (16.95 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table t1 tablespace ts1;

Query OK, 0 rows affected (13.98 sec)

Records: 0 Duplicates: 0 Warnings: 0

到本篇的末尾&＃xff0c;再来看下三种表空间如何销毁&＃xff1a;

系统表空间无法销毁&＃xff0c;除非把里面的内容全部剥离出来&＃xff1b;

单表空间如果表被删掉了&＃xff0c;表空间也就自动销毁&＃xff1b;或者是表被移植到其他表空间&＃xff0c;单表空间也自动销毁。

通用表空间需要引用他的表全部删掉或者移植到其他表空间&＃xff0c;才可以被成功删除。

# 删除表空间 ts2 失败

mysql> drop tablespace ts2;

ERROR 3120 (HY000): Tablespace &＃96;ts2&＃96; is not empty.

mysql> show errors;

&＃43;-------&＃43;------&＃43;--------------------------------&＃43;

| Level | Code | Message |

&＃43;-------&＃43;------&＃43;--------------------------------&＃43;

| Error | 3120 | Tablespace &＃96;ts2&＃96; is not empty. |

&＃43;-------&＃43;------&＃43;--------------------------------&＃43;

1 row in set (0.00 sec)

# 查看数据字典表来进一步查看表空间 ts2 被哪些表引用了

mysql> select regexp_replace(a.name,&＃39;/.&＃43;&＃39;,&＃39;&＃39;) dbname,regexp_replace(a.name,&＃39;.&＃43;/&＃39;,&＃39;&＃39;) tablename from innodb_tables a, innodb_tablespaces b where a.space &＃61; b.space and b.name&＃61; &＃39;ts2&＃39;;

&＃43;--------&＃43;-----------&＃43;

| dbname | tablename |

&＃43;--------&＃43;-----------&＃43;

| ytt | t4 |

&＃43;--------&＃43;-----------&＃43;

1 row in set (0.00 sec)

# 删除对应的表或者转到其他的表空间。

mysql> alter table t4 tablespace innodb_file_per_table;

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 删除表空间 ts2。

mysql> drop tablespace ts2;

Query OK, 0 rows affected (0.02 sec)

本篇主要介绍了 MySQL 三种表空间&＃xff1a;单表空间、系统表空间、通用表空间。并把它们的优缺点以及相关注意事项简单进行了说明&＃xff0c;希望大家在建表之初考虑好表空间的所属问题。