Shulou(Shulou.com)06/01 Report--

MySQL5.6 starts with independent tablespaces: MySQL5.6 innodb_file_per_table=1 # uses independent tablespaces with dynamic parameters. (default OFF,5.7 default ON)

1. Drop/truncate table operation table space can be automatically reclaimed (disk space)

1) create procedure and cycle insert a certain amount of data # # use test # # drop procedure pro1

DELIMITER / / create procedure pro1 () begin declare i int; set iTunes 1; while i call pro1 ()

3). View table size and amount of data: select table_name, (data_length+index_length) / 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 clear table: mysql > truncate table test.cc; Query OK, 0 rows affected (0.73 sec)

5) check again that the tablespace has been reclaimed:

Cc.ibd is recycled to 96KB by 11264KB.

Mysql > select table_name, (data_length+index_length) / 1024 plus 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 >

Note: the drop table test.cc; physical file cc.ibd will also be deleted.

2. Under independent tablespaces, you can customize the storage location of tables (sometimes putting some hot meters on different disks can effectively improve IO performance)

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. Under independent tablespaces, you can recycle tablespace fragments (such as space released after a very large delete operation).

1) create a test table

DELIMITER / / create procedure pro_test1 () begin declare i int; set iTunes 1; while i delete from test1; test1.ibd 384KB

3) Recycle tablespace mysql > alter table test1 engine=innodb; test1.ibd 96KB

Mysql > select table_name, (data_length+index_length) / 1024 plus 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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.