MySQL5.6開始可以使用獨立表空間， innodb_file_per_table=1

shytodear發表於2016-10-20
MySql
MySQL5.6開始可以使用獨立表空間：
MySQL5.6 
innodb_file_per_table=1 #使用獨立表空間，動態引數。（5.6預設OFF，5.7預設ON）


1、drop/truncate table方式操作表空間能自動回收（磁碟空間）

1)、建立procedure，迴圈insert一定量資料
##use test
##drop procedure pro1;

DELIMITER //
create procedure pro1()
begin
declare i int;
set i=1;
while i<100000 do
    insert into test.cc(id,name) values(i, "aa");
    set i=i+1;
end while;
end;//

2)、呼叫procedure ：
mysql> call pro1();


3)、檢視錶大小、資料量：
select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows
   from information_schema.tables where table_schema='test' and table_name='CC';

+------------+------------+------------+
| table_name | total_mb   | table_rows |
+------------+------------+------------+
| cc         | 3.51562500 |     100246 |
+------------+------------+------------+
1 row in set (0.31 sec)

4)、truncate清表：
mysql> truncate table test.cc;
Query OK, 0 rows affected (0.73 sec)

5)、再次檢視錶空間已經回收：

cc.ibd 由  11264KB 回收到96KB 。

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows
    -> from information_schema.tables where table_schema='test' and table_name='CC';
+------------+------------+------------+
| table_name | total_mb   | table_rows |
+------------+------------+------------+
| cc         | 0.01562500 |          0 |
+------------+------------+------------+
1 row in set (0.00 sec)

mysql>

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.08 sec)

mysql>

注：drop table test.cc ; 物理檔案cc.ibd也會同時被刪除。


2、獨立表空間下，可以自定義表的儲存位置，（有時將部分熱表放在不同的磁碟可有效地提升IO效能）
create table test(id int) data directory='c:/software';
create table test1(id int,name varchar(20),primary key (id)) data directory='c:/software';

3、獨立表空間下，可以回收表空間碎片（比如一個非常大的delete操作之後釋放的空間）

1）建立測試表
DELIMITER //
create procedure pro_test1()
begin
declare i int;
set i=1;
while i<10000 do
    insert into test.test1(id,name) values(i, "aa");
    set i=i+1;
end while;
end;//

##call pro_test1();


表大小：test1.ibd   368KB

2）delete後表大小：
mysql> delete from test1;
test1.ibd   384KB

3）回收表空間
mysql> alter table test1 engine=innodb; 
test1.ibd   96KB

mysql> select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows
   from information_schema.tables where table_schema='test' and table_name='TEST1';

+------------+------------+------------+
| table_name | total_mb   | table_rows |
+------------+------------+------------+
| test1      | 0.01562500 |          0 |
+------------+------------+------------+
1 row in set (0.00 sec)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27126919/viewspace-2126817/，如需轉載，請註明出處，否則將追究法律責任。

相關文章