PACK_KEYS and mysqlPACK_KEYS in mysql

PACK_KEYS and mysqlPACK_KEYS in mysql

Original http://jackyrong.iteye.com/blog/2170222

 

One of the myisam engines in mysql is easy to ignore, called the compressed index PACK_KEYS,

MyISAM uses prefix compression to reduce the index size. By default, strings and integers can be compressed.

You can use PACK_KEYS to control the index compression mode when creating table.

PACK_KEYS is described as follows in the MySQL manual:

If you want the index to be smaller, set this option to 1. This usually slows down the update speed and speeds up reading. Setting the option to 0 can cancel the compression of all keywords. When this option is set to DEFAULT, the storage engine only compresses long CHAR or VARCHAR columns (only for MyISAM ).

If you do not use PACK_KEYS, the default operation is to only compress strings, but not compress numbers. If you use PACK_KEYS = 1, compress the number.

MySQL adopts prefix compression when compressing binary numeric keywords:

O each keyword requires an additional byte to indicate how many bytes of the previous keyword are the same as the next keyword.

O the pointer to the row is stored behind the keyword in the order of high byte priority to improve the compression effect.

This means that if two consecutive rows have many identical keywords, the subsequent "same" keywords usually only occupy two bytes (including pointers to rows ). In general, the subsequent keywords occupy storage_size_for_key + pointer_size (the pointer size is usually 4 ). However, prefix compression is advantageous only when many numbers are the same. If all the keywords are completely different and the keywords cannot contain NULL values, one byte is required for each keyword. (In this case, the bytes that store the compressed keyword length are the same as the bytes used to mark whether the keyword is NULL.

For example, there is a field to save the file name, such as "abc.pdf", where pdf is the same, so you can

Use the compressed index based on this field:

Create table <TABLE_NAME> (

'Id' int not null,

'Name' VARCHAR (250) NULL,

Primary key ('id '))

PACK_KEYS = 1;

Alter table table_name PACK_KEYS = 1;