MySQL detailed (2)----------storage Engine

what is the storage engine?

Mysqldata is stored in a file using a variety of different technologies(or Memory)the. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

For example, if you are working on a large amount of temporary data, you may need to use a memory storage engine. The memory storage engine can store all the tabular data in memory. Or, you might need a database that supports transactional processing(to ensure that the data fallback capability is not successful when the transaction is processed).

it different technologies and associated functionsMysqlis called the storage engine(also known as table type). Mysqla number of different storage engines are configured by default and can be pre-set orMysqlenabled on the server. You can choose the storage engine for servers, databases, and tables to choose how you want to store your information, how to retrieve that information, and what performance and functionality you need your data to combine.time to provide you with maximum flexibility.

The flexibility of choosing how to store and retrieve your data isMysqlThe main reason why this is so popular. Other database Systems(including mostnumber of business options)only one type of data store is supported. Unfortunately, other types of database solutions take the“one size fits every need”way means you either sacrifice some performance or youuse a few hours or even days to tweak your database in detail. UseMysql, we just need to modify the storage engine we use.

in this article, we are not prepared to set The technical aspects of different storage engines are discussed in(Although we inevitably have to study some aspects of these factors), instead, we'll focus on what these different engines are best suited to and how to startuse a different storage engine. To achieve this, we have to understand some basic issues before we introduce the specifics of each storage engine.

Mysqlmany different storage engines are supported, sometimes referred to as table types. Each table in the database can have a different storage engine, and it can be easily converted.
Create　TABLE　Tablenametype=typename

MySQL5.5 later uses the InnoDB storage engine by default, where InnoDB and BDB provide transaction-safe tables, and other storage engines are non-transactional security tables.
To modify the default engine, you can modify the Default-storage-engine in the configuration file. You can do this by:

Show variables like ' default_storage_engine ';

View the current database to the default engine.

Command:show Engines and show variables like ' have% '

You can list the engines that the current database supports.

A record where value is displayed as disabled indicates that the database supports this engine and is disabled when the database is started.

After MySQL5.1, there is a engines table in the INFORMATION_SCHEMA database that provides information that is exactly the same as show engines, and you can use the following statement to query which storage engines support the processing of things:

Select engine from information_chema.engines where transactions = ' yes ';

You can specify the engine that you use when you create or modify a database by using the engine keyword.
Main storage Engine: MyISAM, InnoDB, Memory and merge introduction:
When creating a table by engine= ... or type= ... To specify what you want to use to the engine.

Show Table status from DBname to view the specified table to the engine.

A MyISAM
　　It does not support transactions, does not support foreign keys, especially fast access, 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 and table names, but with the following extensions:

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

Data files and index files can be placed in different directories, evenly allocated Io, to get faster speeds. To specify the path to the data file and the index file, you need to specify the file path by using Data directory and the index directory statement when creating the table, which requires an absolute path.

Each MyISAM table has a flag that is set by the server or MYISAMCHK program when it checks the MyISAM data table. The MyISAM table also has a flag indicating whether the data table has been shut down properly since it was last used. This flag can be used to determine if a data table needs to be checked and repaired if the server thinks it is a crash or a machine. If you want this check to occur automatically, you can use the--myisam-recover behavior when you start the server. This causes the server to automatically check the data table flags and perform the necessary repair processing each time a MyISAM data table is opened. Tables of type MyISAM may be corrupted, you can use the Check Table statement to check the health of the MyISAM table, and fix a corrupt to MyISAM table with the Repair table statement.

The MyISAM table also supports 3 different storage formats:

• static (fixed-length) table
• Dynamic tables
• Compression table

Where the static table is the default storage format. The fields in the static table are non-variable length fields, so each record is fixed-length, the advantage of this storage method is that the storage is very fast, easy to cache, the fault is easy to recover, the disadvantage is that the space occupied is usually more than the dynamic table. A static table complements the spaces defined by the column definition when the data is stored, but does not receive these spaces when accessed, and these spaces are removed before they are returned to the app. It is also important to note that, in some cases, you may need to return a space after the field, and the following space will be automatically processed when using this format.

Dynamic tables contain variable-length fields, where records are not fixed lengths, so storage has the advantage of less space, but frequent updates to delete records can be fragmented, requiring periodic execution of optimize table statements or MYISAMCHK-R commands to improve performance, and recovery is relatively difficult in the event of a failure.
Compressed tables are created by the Myisamchk tool and occupy very little space because each record is individually compressed, so there is very little access expense.

Two InnoDB
　　The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.

1) Auto-Grow column:
　　The auto-grow column of the InnoDB table can be inserted manually, but if it is empty or 0 inserted, then the actual insert is automatically increased to the value. You can pass the ALTER TABLE ... Auto_increment=n; " Statement to set the starting value of the autogrow value by default to 1, but the force to default value is saved in memory and the value is lost after the database restarts. You can use LAST_INSERT_ID () to query the value used by the last inserted record for the current thread. If you insert more than one record at a time, the auto-grow value used by the first record is returned.
For InnoDB tables, the autogrow column must be an index. 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 combined index to the previous column sort.

2) foreign KEY constraint:
mysql support for foreign key storage engine only InnoDB, When creating a foreign key, the parent table must have a corresponding index, and the child table will automatically create the corresponding index when the foreign key is created.
      When you create an index, you can specify the corresponding actions on the child table when the parent table is deleted, updated, including restrict, Cascade, set NULL, and no action. where restrict and no action are the same, it means that the parent table cannot be updated when the child table is associated, casecade means that the parent table updates or deletes the records corresponding to the child table when updating or deleting, and the set null  indicates that the parent table is updated or deleted. The field corresponding to the child table is set NULL.
When a table is created by another table with a foreign key reference, the table's corresponding index or primary key is forbidden from deletion. The
can use set foreign_key_checks=0, temporarily turn off foreign key constraints, set Foreign_key_checks=1, and open constraints.

Three MEMORY
　　Memory creates a table using the content that exists in it. Each memory table actually corresponds to a disk file in the format of. frm. The memory type of table access is very fast because it goes to the data in memory, and the hash index is used by default, but once the server shuts down, the data in the table is lost, but the table continues to exist.
By default, the memory data table uses a hash index, which makes the "equality comparison" very fast, but the "range comparison" is much slower. Therefore, the hash index value is suitable for use in the "=" and "<=>" operators, not suitable for use in the "<" or ">" operators, also not applicable in the order by sentence. If you do want to use the < or > or the betwen operator, you can use the Btree index to speed up.
The data rows stored in the Memory data table use the same length format, thus speeding up processing, which means that you cannot use a variable length data type such as BLOB and text. VARCHAR is a variable-length type, but it can be used because it is treated as a fixed-length char type within MySQL.

create table tab_memory engine=memoryselectid,name,age,addrfrom man orderbyid;

　　

Use the using Hash/btree to specify a specific to index.

create index mem_hashusinghash ontab_memory(city_id);

　

When you start the MySQL service, use the--init-file option to put a statement such as insert into...select or load data infile into this file to load the table from a persistent data source when the service starts.
The server needs enough memory to maintain the memory table at the same time, and when it is no longer used, it is necessary to execute DELETE from or TRUNCATE table or to delete the entire table when it is not in use.
The size of the amount of data placed in each memory table is constrained by the max_heap_table_size system variable, the initial value of this system variable is 16M, and the max_rows clause can be used to specify the maximum number of rows in the table when creating the memory table.

Four MERGE
　　The merge storage engine is a combination of a set of MyISAM tables in which the MYISAM table structure must be identical, there is no data in the merge table, operations on tables of the merge type can be queried, updated, deleted, and these operations are actually operations on the internal MyISAM table. For the insert operation on the merge table, which is the inserted table defined by the INSERT_METHOD clause, there can be 3 different values, first and last values allow the insert operation to be performed on the initial or final table, without defining this clause or no, Indicates that the merge table cannot be inserted. You can drop operations on the merge table, which simply removes the definition of the merge table and has no effect on the internal table. The merge retains 2 files that start with the merge table name: the definition of the. frm file storage table; the. mrg file contains information about the combined table, including which tables are composed of the merge table, and the basis for inserting the data. Can be modified by. MRG file to modify the merge table, but will be refreshed by flush table after modification.

createtableman_all(id int,namevarchar(20))engine=mergeunion=(man1,man2) insert_methos=last;

(v) Archive engine

The archive storage engine supports only insert and select operations, and indexes are not supported until MySQL5.1.

The archive table is suitable for log and data collection applications.

The archive engine supports row-level locks and dedicated buffers, so high concurrent insertions can be achieved, but it is not a thing-based engine, but a simple engine optimized for high-speed insertion and compression.

(vi) Blackhole engine

The Blackhole engine does not implement any storage mechanism, it discards all inserted data and does not save any. However, the server logs the Blackhole table, so it can be used to replicate the data to the repository, or simply log it. But this kind of application will encounter many problems, so it is not recommended.

(vii) CSV engine

The CSV engine can handle the normal SCV file as a MySQL table, but it does not support indexing.

The CSV engine can be useful as a mechanism for exchanging data.

(eight) federated engine

The federated engine is a proxy for accessing other MySQL servers, although the engine appears to provide a good cross-server flexibility, but it often poses problems and is therefore disabled by default.

Second, choose the right engine

In most cases, InnoDB are the right choice, and can simply be summed up in a sentence "Unless you need to use some InnoDB features that are not available, and there is no alternative, you should prefer the InnoDB engine."

Unless it is a last resort, it is recommended not to mix multiple storage engines, which could lead to a range of responsible issues and potential bug and boundary issues.

If your application requires a different storage engine, consider the following factors first:

Transaction:

If the application requires transactional support, then InnoDB (or XTRADB) is currently the most stable and validated option.

Backup:

If you can shut down the server periodically to perform backups, the backup factor can be ignored. Conversely, if you need online hot backup, then choosing InnoDB is the basic requirement.

Crash recovery

The probability of damage after the MyISAM crashes is much higher than the InnoDB, and the recovery rate is slower.

Unique Features

If a storage engine has some key features and lacks some of the necessary features, sometimes it has to be a compromise or a tradeoff in architecture design.

Some query SQL behaves differently on different engines. The more typical are:

SELECT COUNT (*) from table;

For MyISAM it will be quick, but the rest may not work.

Iii. Examples of application

1, log-type application

MyISAM or archive storage engines are suitable for such applications because they have low overhead and very fast insertion.

What if you need to make an analytic report of the logged log, and the SQL that generated the report is likely to cause a noticeable decrease in insertion efficiency?

One solution is to use MySQL's built-in replication scheme to copy data to a repository, and then perform comparisons of time-consuming and CPU-based queries on the standby. Of course, you can also perform report query operations when the system load is low, but the application is constantly changing, and if relying on this strategy may cause problems later.

Alternatively, the name of the log table contains year and month information, which allows frequent query operations on history tables that do not already have an insert operation, without interfering with the most recent insert operation on the current table.

2, read-only or read-only tables in most cases

Some tables of data are used for the preparation of categories or lists (such as jobs), which is typically read-write less business. If you don't mind the MyISAM crash recovery problem, choosing the MyISAM engine is appropriate. (MyISAM only writes data to memory, and then waits for the operating system to periodically brush the data out to disk)

3. Order Processing

Involving order processing, supporting transactions is necessary, InnoDB is the best choice for order Processing class applications.

4. Large Data volume

If your data grows to a level above 10TB, you may need to build a data warehouse. Infobright is the most successful solution for MySQL Data Warehouse. Some large databases are not suitable for infobright, but may be suitable for tokudb.

MySQL detailed (2)----------storage Engine