MySQL Optimization (iv)

16, how the MySQL storage data

The database is stored as a directory.

Tables are stored as files.

Columns are stored in a file in a variable-length or fixed-length format. For the BDB table, the data is stored in a page form.

Supports memory-based tables.

Databases and tables can be connected by symbols on different disks.

On Windows, MySQL supports the use of the. sym file internal symbols to connect to the database.

17. mysql Table type

Heap table: A table of fixed presidents, stored only in memory and indexed by a hash index.

ISAM table: The early B-tree table format in MySQL 3.22.

A new version of the Myiasm:iasm table, like the following extensions:

Portability of the binary hierarchy.

The null column index.

Fewer fragments are available for variable-length rows than for ISAM tables.

Support for large files.

Better index compression.

A better key? Statistical distribution.

Better and faster auto_increment processing.

Berkeley DB (BDB) Table from Sleepcat: Transaction security (with Begin work/commit| ROLLBACK).

18, MySQL line type (specifically iasm/myiasm table)

If all columns are in a fixed-length format (no varchar, blobs, or text), MySQL creates the table in a fixed-length table, otherwise the table is created in dynamic length format.

The fixed-length format is much faster and more secure than the dynamic long format.

The dynamic length row format generally takes up less storage space, but if the table is frequently updated, fragmentation occurs.

In some cases, it's not worth moving all varchar, blobs, and text columns to another table, just getting faster on the main table.

With Myiasmchk (for isam,pack_iasm), you can create a read-only compressed table, which minimizes disk usage, but it's pretty good when you use a slow disk. Compact tables take full advantage of log tables that will no longer be updated