MySQL Storage engine __ Notes

MySQL Storage engine (table type):

MySQL database:

In general, a database is a collection of data, specific to a computer database can make a collection of some files on the memory or some memory

The collection of memory data for the data.

The MySQL database is a relational database of open source code. Currently, it can provide features such as: Support for SQL language, subqueries, stored procedures, triggers, views, indexes,

Transactions, locks, foreign key constraints, and impact replication. In the late stages, we will explain these features in detail.

As with large database systems such as Oracle and SQL Server, MySQL is also a database of client/server systems and single-process multithreaded architectures.

One important feature of MySQL that differs from other database systems is the support for the plug-in storage engine.

Storage Engine:

This is how to store the data, how to index the stored data and how to update, query the data and other techniques to implement the method. Because in relational data

Storage is stored in the form of a label, so the storage engine can also be called a table type (that is, the type that stores and operates this table).

Like large database systems such as Oracle and SQL Server, MySQL is also a database of client/server systems and single-process multithreaded architectures.

One important feature of MySQL that differs from other database systems is the support for the plug-in storage engine, which is the same for all storage management mechanisms. and MySQL

The database provides a variety of storage engines. The user can choose different storage engine for the data table according to different requirements, the user can also according to their own needs

Write your own storage engine.

MySQL Storage engine:

1.Mylsam: This engine was first provided by MySQL. Divided into static mylsam, dynamic Mylsam and compression Mylsam three kinds:

Static Mylsam: If the length of each data column in the datasheet is pre-fixed, the server will automatically select this type of table. Because the data table

Each record occupies the same amount of space, so the table access and update efficiency is very high. Recovery work is also compared when data is compromised

Easy to do.

Dynamic Mylsam: If the Varchar,xxxtext or Xxxblob fields appear in the datasheet, the server will automatically select this type of table. Relative to

Static Mylsam, this table storage space is small, but because of the length of each record is different, so the data in the data table after multiple modifications can

Discrete storage in memory, which leads to a decrease in execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table can be

Defragment with the Optimize table command or the Optimization tool

Compression Mylsam: The two types of tables mentioned above can be compressed with the Mylamchk tool. This type of table further reduces the amount of storage consumed, but

This table is compressed and can no longer be modified. In addition, because it is compressed data, such a table should be read to the first decompression.

In either case, transactions, row-level locks, and foreign key constraints are not supported at this time.

2.mylsam Merge Engine: This type is a variant of the Mylsam type. Merging tables is the merging of several identical mylsam tables into a single virtual table.

Often used in logs and data warehouses.

3.InnoDB: Can be seen wrong is a further update to the MYLSAM product, which provides transactions, row-level locking mechanisms and foreign key constraints.

4.Memory (heap): This type of data table only exists in memory. It uses a hash index, so the data is stored very quickly. Because it exists in

In memory, so this type is often used in temporary tables.

5.archive: This type only supports SELECT and INSERT statements, and does not support indexing. Often applied to logging and aggregation analysis.

Of course the MySQL supported table types do not know these kinds.

Operations on the storage engine in MySQL

1. View the operations of the storage engine that the database can support

2.

(go) MySQL storage engine __ Notes