MySQL Table type selection

P131) When you create a table, the default engine is InnoDB, and if you want to modify the default storage engine, you can set default-table-type in the parameters file. To view the current default storage engine, you can use the command:

When creating a new table, you can set up the storage engine for the new table by adding the engine keyword. You can also modify an existing table into another storage engine by using the ALTER TABLE statement.

I. MyISAM(P134)

MyISAM does not support transactions, does not support foreign keys, it has the advantage of accessing speed blocks, there is no requirement for transactional integrity, or a SELECT, insert-based application can basically use this engine to create tables.

Each myisam is stored as 3 files on disk with the same file name as the table name, but the extension is:

• . frm (storage table definition);
• . MYD (MYData, storing data);
• . MYI (myindex, storage index).

Data files and index files can be placed in different directories, evenly distributed IO, for faster speeds.

The MyISAM table also supports 3 different storage formats, namely:

• static (fixed length) table;
• dynamic tables;
• Compress the table.

static tables are the default storage format, where the fields are non-variable length fields, so that the length of each record is fixed, the advantage is that the storage speed is very fast, easy to cache, the failure is easy to recover, the disadvantage is that it takes up more space than the dynamic table. The data of the static table is stored with the space defined by the width of the column, and then the last space is removed when it is acquired.

Dynamic tables contain variable length fields, which are not fixed-length, so the advantage of storage is that it takes up less space, but frequent updates and delete records are fragmented and require regular execution of OPTIMIZE TABLE statements or Myisamchk-r command to improve performance and restore relative difficulty in the event of a failure.

compressed Tables are created by the Myisampack tool and occupy very small disk space. Because each record is individually compressed, all only very small access expenses.

Two. InnoDB(P136)

The auto- grow column of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the actual insert will be the auto-grow value.

Can be passed by ALTER Table Table_name atuo_increment=n; Statement forces the initial value of the autogrow column to start at 1, but the default value of the coercion is kept in memory, and if the value is restarted before it is used, the mandatory default value is lost and needs to be reset after the database is started.

You can use select last_insert_id (); Queries the value used by the last inserted record of the current thread. If more than one record is inserted at a time, the auto-grow value used by the first record is returned.

for InnoDB tables, the autogrow column must be an indexed column. If it is a composite index, it must also be the first column of the combined index , but for the MyISAM table, the autogrow column can be the other column of the combined index, so that when the record is inserted, the autogrow column is incremented by the preceding columns of the combined index.

MySQL Table type selection