MySQL (2) ---------- storage engine, mysql ----------

MySQL (2) ---------- storage engine, mysql ----------

What is the storage engine?

Data in MySQL is stored in files (or memory) using different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of different features and capabilities. By choosing different technologies, you can get additional speeds or features to improve the overall functionality of your application.

For example, if you are studying a large amount of temporary data, you may need to use the memory storage engine. The memory storage engine can store all table data in the memory. Alternatively, you may need a database that supports transaction processing (to ensure the ability to roll back data when the transaction processing fails ).

These different technologies and related functions are called storage engines (also called table types) in MySQL ). MySQL is configured with many different storage engines by default, which can be set in advance or enabled on the MySQL server. You can select a storage engine for servers, databases, and tables, this gives you maximum flexibility when choosing how to store your information, how to retrieve it, and what performance and functions you need to combine your data.

The flexibility to choose how to store and retrieve your data is the main reason why MySQL is so popular. Other database systems (including most commercial options) support only one type of data storage. Unfortunately, the "one size fits all needs" approach adopted by other types of database solutions means you have to sacrifice some performance, you can adjust your database in several hours or even days. To use MySQL, we only need to modify the storage engine we use.

In this article, we do not prepare a set to discuss the technical aspects of different storage engines (although we inevitably want to study some aspects of these factors). On the contrary, we will focus on the requirements that these different engines are most suitable for and how to enable different storage engines. To achieve this, we must understand some basic problems before introducing the specific situation of each storage engine.

MySQL supports many different storage engines, also known as table types. Each table in the database can have different storage engines and can be easily converted.
Create TABLE tablenameTYPE = typename

 

MySQL5.5 and later are used by defaultInnoDBThe storage engine. InnoDB and BDB provide transaction security tables. Other storage engines are non-transaction security tables.
To modify the default engine, you can modify the default-storage-engine in the configuration file. You can use:

Show variables like 'default _ storage_engine ';

View the current database to the default engine.

Command:Show enginesAndShow variables like 'have %'

You can list the engines supported by the current database.

The Value indicates that the database supports this engine and is disabled when the database is started.

After MySQL5.1, The INFORMATION_SCHEMA database has an ENGINES table, which provides the same information as the show engines statement. You can use the following statement to query which storage ENGINES Support transaction processing:

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

You can use the engine keyword to specify the engine used when creating or modifying a database.
Main storage engines: MyISAM, InnoDB, MEMORY, and MERGE:
When creating a table, useEngine =...OrType =...To specify the engine to be used.

Show table status from DBnameTo view the specified table to the engine.

(1) MyISAM
　　It does not support transactions, nor does it support foreign keys, especially the fast access speed. It does not require transaction integrity or can be used to create tables for applications dominated by SELECT and INSERT.
Each MyISAM is stored as three files on the disk. The file names and table names are the same, but the extensions are:

• . Frm (storage table definition)
• MYD (MYData, data storage)
• MYI (MYIndex, storage index)

Data Files and index files can be placed in different directories, and I/O is evenly allocated to get faster speed. To specify the path of the DATA file and INDEX file, you must use the data directory And index directory statements when creating the table. The absolute path must be used for the file path.

Each MyISAM table has a flag. The server or myisamchk Program sets this flag when checking the MyISAM data table. The MyISAM table also has a flag to indicate whether the data table was normally disabled after it was used last time. If the server thinks it is a crash, this flag can be used to determine whether the data table needs to be checked and repaired. If you want this check to be performed automatically, you can use -- myisam-recover when starting the server. This will enable the server to automatically check and fix a MyISAM data table each time. MyISAM tables may be damaged. You can use the check table statement to CHECK the health of MyISAM tables and use the repair table statement to REPAIR a damaged MyISAM TABLE.

MyISAM tables also support three different storage formats:

• Static (fixed length) Table
• Dynamic table
• Compressed table

The static table is the default storage format. Fields in a static table are non-variable-length fields, so that each record has a fixed length. The advantage of this storage method is that it is very fast to store, easy to cache, and easy to recover when a fault occurs; the disadvantage is that it usually occupies more space than dynamic tables. When data is stored in a static table, spaces are supplemented according to the width definition of the column, but these spaces are not obtained during access. These spaces are removed before they are returned to the application. At the same time, note: In some cases, the space after the field may be returned. When this format is used, the Space following it will be automatically processed.

A dynamic table contains variable-length fields and records do not have a fixed length. In this way, the storage space is relatively small, but fragments are generated when records are updated or deleted frequently, you need to regularly execute the optimize table statement or myisamchk-r command to improve performance, and it is relatively difficult to recover when a fault occurs.
The compressed table is created by the myisamchk tool, which occupies a very small space, because each record is compressed separately, so there is only a small access cost.

 

(2) InnoDB
　　The InnoDB Storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with the storage engine of MyISAM, InnoDB writes less efficiently and occupies more disk space to retain data and indexes.

1) Auto-increment column:
　　The automatic growth column of the InnoDB table can be inserted manually. However, if the inserted column is null or 0, the actual inserted column is automatically inserted to the value after the insertion. You can use "alter table... AUTO_INCREMENT = n; "The statement forces you to set the starting value of the automatic growth value. The default value is 1, but this value is saved in the memory by default. This value will be lost after the database is restarted. You can use LAST_INSERT_ID () to query the value of the last inserted Record of the current thread. If multiple records are inserted at a time, the returned value is the automatic growth value used by the first record.
For an InnoDB table, the auto-increment column must be an index. For a composite index, it must also be the first column of the composite index. For a MyISAM table, the auto-increment column can be another column of the composite index, auto-increment columns are sorted by composite index to the first few columns and then incremented.

2) foreign key constraints:
　　MySQL only supports the InnoDB Storage engine for Foreign keys. When creating foreign keys, the parent table must have corresponding indexes, when a sub-Table creates a foreign key, the corresponding index is automatically created.
When creating an index, you can specify operations for the child table When deleting or updating the parent table, including restrict, cascade, set null, and no action. The restrict and no action are the same, which means that the parent table cannot be updated when the sub-table is associated. casecade indicates that when the parent table is updated or deleted, update or delete the records corresponding to the sub-Table. set null indicates that the field corresponding to the sub-table is set null when the parent table is updated or deleted.
When a table is created with a foreign key reference by another table, the index or primary key of the table cannot be deleted.
You can use set foreign_key_checks = 0; temporarily disable the foreign key constraint, set foreign_key_checks = 1; open the constraint.

(3) MEMORY
　　Memory uses the content in memory to create a table. Each MEMORY table corresponds to a disk file in the format of. frm. MEMORY tables can be accessed very quickly because they store data in the MEMORY and use HASH indexes by default. However, once the server is disabled, data in the table will be lost, but the table will continue to exist.
By default, memory data tables use Hash indexes. Using these indexes for "equal comparison" is very fast, but the speed for "range comparison" is much slower. Therefore, hash index values are suitable for operators "=" and "<=>", but not "<" or ">, it is also not suitable for use in order by statements. If you really want to use the "<" or ">" or betwen operator, you can use the btree index to speed up the process.
The data rows stored in the MEMORY data table use a format with the same length. Therefore, the processing speed is accelerated, which means that the variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a variable-length type, but it can be used because it is regarded as a CHAR type with Fixed Length in MySQL.

 

create table tab_memory engine=memoryselect id,name,age,addrfrom man orderby id;

　　

 

Use using hash/BTREE to specify specific indexes.

 

create index mem_hashusing hash on tab_memory(city_id);

　

When starting the MySQL service, use the -- init-file option to insert... when a statement such as select or load data infile is put into this file, you can load the table from a persistent and stable data source at service startup.
The server needs enough MEMORY to maintain the MEMORY table used at the same time. When the MEMORY table is no longer used, the MEMORY occupied by the MEMORY table should be released, delete from or truncate table or DELETE the entire table.
The size of data stored in each MEMORY table is limited by the max_heap_table_size system variable. The initial value of this system variable is 16 Mb, when creating a MEMORY table, you can use the MAX_ROWS clause to specify the maximum number of rows in the table.

(4) MERGE
　　The merge storage engine is a combination of MyISAM tables. These MyISAM tables must have the same structure and have no data in the MERGE table. You can query, update, and delete tables of the MERGE type, these operations are actually performed on the internal MyISAM table. The insert operation on the MERGE table is based on the inserted Table defined by the INSERT_METHOD clause. It can have three different values, the values of first and last make the insert operation take effect on the first or last table. If this clause is not defined or NO, insertion cannot be performed on the MERGE table. You can perform the drop operation on the MERGE table. This operation only deletes the definition of the MERGE table and has no impact on the internal table. MERGE retains two files starting with the MERGE table name on the disk :. definition of the frm file storage table ;. the MRG file contains information about the combined tables, including which tables are composed of MERGE tables and the basis for data insertion. You can modify the. MRG file to modify the MERGE table, but you must refresh the table with flush.

 

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

 

 

(5) Archive Engine

The Archive storage engine only supports INSERT and SELECT operations, and does not support indexes before MySQL5.1.

Archive tables are suitable for log and data collection applications.

The Archive engine supports row-level locks and dedicated cache areas, so it can implement highly concurrent inserts, but it is not a thing-type engine, it is a simple engine optimized for high-speed insertion and compression.

 

(6) Blackhole Engine

The Blackhole engine does not implement any storage mechanism. It discards all inserted data and does not save any data. However, the server will record the Blackhole table logs, so it can be used to copy data to the slave database or simply record the logs. However, this application method may encounter many problems, so it is not recommended.

 

(7) CSV Engine

The CSV engine can process common SCV files as MySQL tables, but does not support indexing.

The CSV engine can be used as a data exchange mechanism.

 

(8) Federated Engine

The Federated engine is a proxy for accessing other MySQL servers. Although the engine seems to provide good cross-server flexibility, it often causes problems, so it is disabled by default.

 

 

 

2. select an appropriate engine

In most cases, InnoDB is the right choice and can be simply summarized as one sentence, "unless some features not available in InnoDB are used, and there is no alternative, otherwise, InnoDB engine should be preferred ".

Unless you have to, we recommend that you do not mix multiple storage engines. Otherwise, there may be a series of responsible problems, as well as some potential bugs and boundary problems.

If the application requires different storage engines, consider the following factors:

Transaction:

If the application requires transaction support, InnoDB (or XtraDB) is currently the most stable and verified choice.

Backup:

If you can regularly shut down the server for backup, you can ignore the backup factors. If you need online hot backup, InnoDB is the basic requirement.

Crash recovery

The probability of damage after MyISAM crashes is much higher than that of InnoDB, and the recovery speed is also slow.

Unique features

If a storage engine has some key features but lacks some necessary features, you may have to consider them or make some trade-off in architecture design.

 

Some query SQL statements have different performance on different engines. Typical examples are:

Select count (*) FROM table;

MyISAM will indeed be fast, but none of them will work.

 

3. Application Example

 

1. Log-type applications

The MyISAM or Archive storage engine is suitable for such applications because of their low overhead and fast insertion speed.

If you need to analyze the recorded logs, the SQL statement that generates the report may significantly reduce the insertion efficiency. What should you do at this time?

One solution is to use the built-in MySQL replication solution to copy data to the standby database, and then execute time-consuming and CPU-consuming queries on the standby database. Of course, you can also perform REPORT query when the system load is low, but the application is constantly changing. If you rely on this policy, it may cause problems in the future.

Another method is to include the year and month information in the name of the log record table, so that frequent query operations can be performed on the History Table that has no insert operation, it does not interfere with the insert operation on the latest table.

 

2. Read-only or read-only tables in most cases

Some table data is used to compile a category or a list (such as a job position). This type of application scenario is a typical business of reading, writing, and less. If you don't mind the problem of MyISAM crash recovery, it is appropriate to use the MyISAM engine. (MyISAM only writes data to the memory, and then waits for the operating system to regularly fl the data to the disk)

 

3. Order Processing

When processing orders, it is necessary to support transactions. InnoDB is the best choice for order processing applications.

 

4. Large data volume

If the data grows to more than 10 TB, you may need to create a data warehouse. Infobright is the most successful MySQL Data Warehouse solution. Some large databases are not suitable for Infobright, but may be suitable for TokuDB.