MySQL MyISAM Storage engine compression table

First, Introduction

MySQL's MyISAM storage engine supports compressed table space, the compressed table space will be reduced, but the compressed table is read-only, cannot insert and update data, if need to update, you need to update after decompression, and then compression.

Second, the case

1. Create the database, create the table and specify the storage engine as MyISAM, and the character set to UTF8

Create DATABASE Myisamdb;use myisamdb;create table MyISAM (id int unsigned auto_increment primary key, name varchar) not NULL) Engine=myisam Charset=utf8;

2. Inserting data into the table MyISAM table

Insert into MyISAM (name) VALUES (' Lenovo Tinkpad '), (' Dell Aliens '), (' Thor-Xuanwu '), (' Mac Pro ');

3. Rapid expansion of the MyISAM table space using worm replication

mysql> INSERT INTO MyISAM select Null,name from MyISAM; Query OK, 262144 rows Affected (1.00 sec) records:262144  duplicates:0  warnings:0

4. View the size of the MyISAM table space before compression

[[email protected] myisamdb]# LS-LH myisam*-rw-rw----1 mysql mysql 8.4K February  20:00 myisam.frm-rw-rw----1 MySQL Mys QL  12M February  20:06 MyISAM. MYD-RW-RW----1 mysql mysql 5.2M February  20:06 MyISAM. MYI

5. Compress the MyISAM table space using the Myisampack command

[Email protected] myisamdb]# myisampack  myisamcompressing MyISAM. MYD: (524288 Records)-Calculating statistics-compressing file47.89%     Remember to run MYISAMCHK-RQ on compressed tabl E

6. View the compressed MyISAM table space Size

[[email protected] myisamdb]# LS-LH myisam*-rw-rw----1 mysql mysql 8.4K February  20:00 myisam.frm  #表结构文件-R W-RW----1 mysql mysql 6.0M February  MyISAM. MYD  #表空间文件-rw-rw----1 mysql mysql 1.0K February  MyISAM. MYI#表索引文件

　　

7. The index is restored because the table space changes after compression, which causes the index to not find the corresponding index location for the record.

[Email protected] myisamdb]# MYISAMCHK-RQ Myisam-check record delete-chain-recovering (with sort) myisam-table ' MyISAM ' Data records:524288-fixing index 1

8. View the recovered index file size

[[email protected] myisamdb]# LS-LH myisam*-rw-rw----1 mysql mysql 8.4K February  20:00 myisam.frm-rw-rw----1 MySQL Mys QL 6.0M February  20:06 MyISAM. MYD-RW-RW----1 mysql mysql 5.1M February  20:08  MyISAM. MYI

　　

　

　

　

　

MySQL MyISAM Storage engine compression table