MySQL-optimized data table processing (1)

Selecting the appropriate table type can also greatly optimize the retrieval speed and maximize the database performance. Select a table type using MySQL. Currently (version 3.23.5) You can choose between 4 available table formats from a speed point of view. · Static MyISAM is the simplest and safest format, and is also used on disks.

Selecting the appropriate table type can also greatly optimize the retrieval speed and maximize the database performance. Select a table type using MySQL. Currently (version 23.5) You can choose between the formats of 4 available tables from a speed point of view. · Static MyISAM is the simplest and safest format, and is also used on disks.

Selecting the appropriate table type can also greatly optimize the retrieval speed and maximize the database performance.

Select a table Type

With MySQL, you can choose between the formats of 4 available tables from a speed point of view (version 23.5.

· Static MyISAM

This format is the simplest and safest format, and it is also the fastest in the disk format. The speed comes from the hard way data can be found on the disk. When there is an index and a static format, it is very simple, just multiply the length of the row by the number of rows. When scanning a table, it is easy to read constant records from each disk. Security comes from the fact that if your computer crashes when writing a static MyISAM file, myisamchk can easily point out where each row starts and ends, so it can usually reclaim all records, except for the partially written ones. Note: in MySQL, all indexes can always be rebuilt.

· Dynamic MyISAM

This format is a bit complicated, because each line must have a head to show how long it is. When a record changes for a long time, it can also end at more than one position. You can use OPTIMIZE table or myisamchk to organize a table. It may be a good idea to move dynamic columns into another table if you have static data that is accessed/changed as some VARCHAR or BLOB columns in the same table to avoid fragmentation.

· Compressing MyISAM

This is a read-only type, which is generated using the optional myisampack tool.

· Memory (HEAP)

This type of table is very useful for small/medium-sized search tables. Copying/creating a common query table (joined by a join) to a (maybe temporary) HEAP table may speed up the joining of multiple tables. Suppose we want to make the following join, it may take several times to use the same data.

SELECT tab1.a, tab3.a FROM tab1, tab2, tab3WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;

To speed up it, we can use tab2 and tab3 to create a temporary table, because we use the same column (tab1.a) for search. Here is the command for creating the table and selecting the result.

CREATE TEMPORARY TABLE test TYPE=HEAP SELECT tab2.a as a2, tab3.a as a3 FROM tab2, tab3 WHERE tab2.a = tab3.a and c = 0;SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;

Characteristics of static (fixed-length) tables

This is the default format. It is used when a table does not contain VARCHAR, BLOB, or TEXT columns.

Fill all CHAR, NUMERIC, and DECIMAL columns to the column width.

Very fast.

Easy to buffer.

It is easy to rebuild after a crash because the record is in a fixed position.

You do not need to be reorganized (using myisamchk) unless a large number of records are deleted and you want to return free disk space to the operating system.

It usually requires more disk space than dynamic tables.

Dynamic table features

This format is used if the table contains any VARCHAR, BLOB, or TEXT columns.

All string columns are dynamic (except those with less than 4 characters ).

Each record is preceded by a bitmap, indicating which column is NULL ('') for the string column, or which column is zero for the number column (which is different from the column containing the NULL value ). If the string column has a zero length after the white space is deleted, or the numeric column has a zero value, it is marked in the bid and not saved to the disk. A non-null string is stored as a Length Byte plus string content.

Generally, a table occupies more disk space than a fixed-length table.

Each record only uses the required space. If a record is larger, it is cut into multiple segments as needed, which leads to record fragmentation.

If you update a row with information that exceeds the length of the row, the row is segmented. In this case, you may have to always run myisamchk-r to improve performance. Use myisamchk-ei tbl_name for statistics.

It is not easy to reconstruct after a crash, because a record can be divided into multiple segments and a connection (fragment) can be lost.

The expected row length for dynamic size records is:

3+ (number of columns + 7) / 8+ (number of char columns)+ packed size of numeric columns+ length of strings+ (number of NULL columns + 7) / 8

The penalty for each connection is 6 bytes. A dynamic record is linked whenever the change increases the record. Each new Link contains at least 20 bytes, so the next increase may be in the same link. If not, there will be another link. You can use myisamchk-ed to check the number of links. All links can be deleted using myisamchk-r.

Features of a compressed table

A read-only table created using the myisampack utility. All customers with MySQL extension email support can retain a copy of myisampack for internal use.

The decompressed code exists in all MySQL distributions so that customers without myisampack can read tables compressed by myisampack.

Occupies a small disk space to minimize disk usage.

Each record is compressed separately (with a low access overhead ). The header of a record is a fixed length (1-3 bytes), depending on the maximum record in the table. Each column is compressed in different ways. Some compression types are:

There is usually a different table for each column.

Suffix blank compression.

Empty prefix compression.

Use a number with a value of 0 for 1-bit storage.

If the value of an integer column has a small scope, the column uses the smallest possible type for storage. For example, if all values are in the range of 0 to 255, a bigint column (8 bytes) can be stored as a TINYINT column (1 byte.

If a column has only one small set of possible values, the column type is changed to ENUM.

You can use the combination of the preceding compression methods for the column.

Records with a fixed or dynamic length can be processed, but BLOB or TEXT Columns cannot be processed.

You can use myisamchk to decompress the package.

MySQL supports different index types, but the general type is ISAM. This is a B-tree index and you can roughly calculate the size of the index file as (key_length + 4) * 0.67, the sum of all keys. (This is the worst case, when all keys are inserted in order .)

String indexes are empty compressed. If the first index is a string, it also compresses the prefix. If the string column contains many trailing spaces or a VARCHAR Column cannot use the full length, the blank compression will make the index file smaller. If many strings have the same prefix, prefix compression is helpful.