innodb_file_per_table

来源：互联网发布：长大的好处知乎编辑：程序博客网时间：2024/06/05 15:14

该参数设置为on时，每张表都建一个ibd文件，否则合用ibdata1

首先看为off的情况

mysql> show global variables like 'innodb_file_per_table';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | OFF   |+-----------------------+-------+1 row in set (0.01 sec)mysql> create database pt;Query OK, 1 row affected (0.00 sec)mysql> use pt;Database changedmysql> create table emp as select * from test.emp;Query OK, 14 rows affected (0.16 sec)Records: 14  Duplicates: 0  Warnings: 0mysql> create table dept as select * from test.dept;Query OK, 4 rows affected (0.05 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> system ls /data/mysql/user_3306/data/pt/ -ls总用量 28 4 -rw-rw----. 1 mysql mysql   59 3月   9 12:49 db.opt12 -rw-rw----. 1 mysql mysql 8624 3月   9 12:49 dept.frm12 -rw-rw----. 1 mysql mysql 8780 3月   9 12:49 emp.frmmysql> exit

下面来看参数改为on的情况

mysql> show global variables like 'innodb_file_per_table';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | ON    |+-----------------------+-------+1 row in set (0.00 sec)mysql> system cat /etc/my.cnf | grep innodb_file_per_tableinnodb_file_per_table          = 1mysql> mysql> drop database pt;Query OK, 2 rows affected (0.10 sec)mysql> create database pt;Query OK, 1 row affected (0.00 sec)mysql> use ptDatabase changedmysql> create table emp as select * from test.emp;Query OK, 14 rows affected (0.02 sec)Records: 14  Duplicates: 0  Warnings: 0mysql> create table dept as select * from test.dept;Query OK, 4 rows affected (0.04 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> system ls /data/mysql/user_3306/data/pt/ -ls总用量 284  4 -rw-rw----. 1 mysql mysql    59 3月   9 12:51 db.opt 12 -rw-rw----. 1 mysql mysql  8624 3月   9 12:51 dept.frm128 -rw-rw----. 1 mysql mysql 98304 3月   9 12:51 dept.ibd 12 -rw-rw----. 1 mysql mysql  8780 3月   9 12:51 emp.frm128 -rw-rw----. 1 mysql mysql 98304 3月   9 12:51 emp.ibd

可以看到多了文件dept.ibd与emp.ibd

0 0