时间:2020-08-20来源:www.pcxitongcheng.com作者:电脑系统城
MySQL的Innodb存储引擎包含两种表空间文件模式,默认的共享表空间和每个表分离的独立表空间,每个表都有自已独立的表空间,每个表的数据和索引都会存在自已的表空间中,可以实现单表在不同的数据库中移动。
每个表都有自已独立的表空间,每个表的数据和索引都会存在自已的表空间中,可以实现单表在不同的数据库中移动。
空间可以回收(除drop table操作处,表空不能自已回收),Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table table1(a int);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table table1;
+--------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys test
mysql> create tablespace general add datafile 'general.ibd';
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| table1 |
+----------------+
1 row in set (0.00 sec)
mysql> create table table2(b int) tablespace=general;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table table2;
+--------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
`b` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `general` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf general.ibd ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys test
[root@db03 ~]# ls /usr/local/mysql/data/test/
db.opt table1.frm table1.ibd table2.frm
[root@db03 ~]# mkdir /tablespace
[root@db03 ~]# chown -R mysql.mysql /tablespace
mysql> create tablespace external add datafile '/tablespace/external.ibd';
Query OK, 0 rows affected (0.01 sec)
mysql> create table table3(c int) tablespace=external;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table table3;
+--------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------+
| table3 | CREATE TABLE `table3` (
`c` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `external` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf external.isl general.ibd ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys test
[root@db03 ~]# ls /usr/local/mysql/data/test/
db.opt table1.frm table1.ibd table2.frm table3.frm
[root@db03 ~]# ls /tablespace/
external.ibd
[root@db03 ~]# cat /usr/local/mysql/data/external.isl
/tablespace/external.ibd
mysql> create table table4(d int) data directory='/tablespace';
Query OK, 0 rows affected (0.01 sec)
mysql> show create table table4;
+--------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------+
| table4 | CREATE TABLE `table4` (
`d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/tablespace/' |
+--------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@db03 ~]# ls /usr/local/mysql/data/test/
db.opt table1.frm table1.ibd table2.frm table3.frm table4.frm table4.isl
[root@db03 ~]# ls /tablespace/test/
table4.ibd
[root@db03 ~]# cat /usr/local/mysql/data/test/table4.isl
/tablespace/test/table4.ibd
mysql> create table table5(e int) tablespace=innodb_system;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table table5;
+--------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------+
| table5 | CREATE TABLE `table5` (
`e` int(11) DEFAULT NULL
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@db03 ~]# ls /usr/local/mysql/data/test
db.opt table1.frm table1.ibd table2.frm table3.frm table4.frm table4.isl table5.frm
[root@db03 ~]# ls /tablespace/test
table4.ibd
mysql> drop database test;
Query OK, 5 rows affected (0.01 sec)
[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf external.isl general.ibd ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys
[root@db03 ~]# ls /tablespace/test/
[root@db03 ~]# ls /tablespace
external.ibd test
mysql> drop tablespace general;
Query OK, 0 rows affected (0.00 sec)
mysql> drop tablespace external;
Query OK, 0 rows affected (0.00 sec)
[root@db03 ~]# ls /tablespace
test
[root@db03 ~]# ls /usr/local/mysql/data
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql mysql_upgrade_info performance_schema sys
[root@db03 ~]# rm -rf /tablespace
2024-04-11
台式机电脑如何连接外接显示器2024-04-11
小新系列打印机手机配置网络的方法教程2024-04-11
Thinkpad 笔记本F1-F12快捷键分别是什么功能ThinkPad蓝牙鼠标如何配对解答步骤41U5008鼠标驱动官网地址: https://support.lenovo.com/en_US/downloads/detail.page?&LegacyDocID=MIGR-67201 第一种方式是比较传统的:使...
2024-04-11
故障现象: USB设备U盘、移动硬盘等插入后提示无法识别的设备,确认设备本身正常,设备可加电,或插入设备后加电但无任何反应,无法使用。新型号机器多表现为黄色USB接口存在此问题,...
2024-04-11