Monty says MySQL optimization (IV) _ MySQL

Monty said MySQL optimization (IV) 16. how to store data in MySQL

The database is stored in a directory.

Tables are stored as files.

Columns are stored in files in a variable-length or fixed-length format. For BDB tables, data is stored as pages.

Supports memory-based tables.

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

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

17. MySQL table type

HEAP table: fixed-length table, which is only stored in memory and indexed using HASH indexes.

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

New version of MyIASM: IASM table, which has the following extensions:

Binary hierarchy portability.

NULL column index.

There are fewer fragments for long-varying rows than the ISAM table.

Supports large files.

Better index compression.

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 row type (for IASM/MyIASM tables)

If all columns are in a fixed-length format (no VARCHAR, BLOB, or TEXT), MySQL creates a table in a fixed-length table format. Otherwise, the table is created in a dynamic-length format.

The fixed length format is much faster and safer than the dynamic length format.

The dynamic length row format usually occupies less storage space. However, if the table is updated frequently, fragments are generated.

In some cases, it is not worthwhile to transfer all VARCHAR, BLOB, and TEXT columns to another table, but to get a faster speed for the primary table.

Using myiasmchk (for ISAM, pack_iasm), you can create a read-only compression table, which minimizes disk usage, but this is very good when using a slow disk. The compressed table makes full use of the log table that will not be updated

19. MySQL high-speed cache (all threads are shared and allocated at one time)

Key code cache: key_buffer_size. the default value is 8 MB.

Table cache: table_cache. the default value is 64.

Thread cache: thread_cache_size. the default value is 0.

Host name cache: it can be modified during compilation. the default value is 128.

Memory ing table: currently only used to compress tables.

Note: the operating system can handle the problem because MySQL does not have a high-speed cache.

20. MySQL cache zone variables (non-shared, on-demand allocation)

Sort_buffer: order by/GROUP

Record_buffer: scans tables.

Join_buffer_size: no key connection

Myisam_sort_buffer_size: REPAIR TABLE

Net_buffer_length: reads SQL statements and caches results.

Tmp_table_size: size of the temporary HEAP table