MySQL Archive storage engine and mysqlarchive Storage

MySQL Archive storage engine and mysqlarchive Storage
Introduction

From the explanation of archive words, we can understand the purpose of this storage engine. This storage engine is basically used for data archiving. It has a very high compression ratio, the storage space is about 10-15 points in innodb, so it is very suitable for storing historical data. Because it does not support indexing and cannot cache indexes and data, therefore, it is not suitable as a storage engine for concurrent table access. The Archivec storage engine uses row locks to implement highly concurrent insert operations, but it does not support transactions. Its design goal is to provide high-speed insert and compression functions.

 

 

 

Features

Each archive table has two files on the disk.

. Frm (storage table definition)

. Arz (Data Storage)

 

1. the archive storage engine supports insert, replace, and select operations, but does not support update and delete operations.

2. the archive storage engine supports large field types such as blob and text. Auto_increment auto-increment columns and auto-increment columns can be non-unique indexes.

3. archive supports the auto_increment column, but does not support inserting a value smaller than the current maximum value into the auto_increment column.

4. archive does not support indexes. Therefore, you cannot create primary keys, unique indexes, and general indexes on the archive table.

 

Storage

Data inserted to the archive table is compressed. archive uses zlib to compress the data. archive supports optimize table and check table operations.

An insert statement only inserts data into the compression cache. The inserted data is locked in the compression cache. When the select operation is performed, the data in the compression cache is refreshed. Except insert delay.

For a bulk insert operation, you can view the record only after it is fully executed, unless there are other inserts operations at the same time point, in this case you can see part of the record, select never refreshes bulk insert unless a general Insert operation exists during its loading.

 

Search

Rows returned from the query request are not compressed and data is not cached. A select query performs a full table scan. When a select query occurs, it searches for all valid rows in the current table, the select statement executes consistent read operations. Note that too many select query statements may degrade the compression insert performance unless bulk insert or delay insert is used, you can use optimize table or repair table to obtain better compression. You can use show tables status to view the record rows in the archive table.

 

Partition

The Archive storage engine supports partitioning.

create table tb_archive(id int not null ,name varchar(30),address varchar(300),mark text)engine=archive;

ALTER TABLE tb_archive PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5),  PARTITION part1 VALUES LESS THAN (10),  PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;ALTER TABLE tb_archive PARTITION BY LIST COLUMNS (name) (    PARTITION a VALUES IN ('A','B'),    PARTITION b VALUES IN ('C'),    PARTITION c VALUES IN ('D'));ALTER TABLE tb_archive PARTITION BY KEY(address)PARTITIONS 3;

 

 

 

Reference from: http://dev.mysql.com/doc/refman/5.6/en/archive-storage-engine.html

Summary

Because of the features of high compression and fast insertion, Archive is very suitable for serving as a storage engine for log tables, but the premise is that the table is not frequently queried.

 

 

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion