Differences between MySQL storage engines (InnoDB, MyISAM, etc.) and their startup Methods

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 articleArticleIn, we are not prepared to focus on the technical aspects of different storage engines (although we will inevitably 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.

How to determine which storage engines are available

You can use the Display Engine Command in MySQL (for example, MySQL Server 4.1.2 or later) to get a list of available engines.

Mysql> show engines; + ------------ + --------- + ---------------------------------------------------- + | Engine | Support | comment | + ------------ + --------- + ----------------------------------------------------- + | MyISAM | default engine as of MySQL 3.23 With great performance | | Heap | Yes | alias for memory | | Memory | Yes | hash based, stored in memory, useful for temporary tables | | Merge | Yes | collection of identical MyISAM tables | | Mrg_myisam | Yes | alias for merge | | Isam | no | obsolete storage engine, now replaced by MyISAM | | Mrg_isam | no | obsolete storage engine, now replaced by merge | | InnoDB | Yes | supports transactions, row-level locking, and foreign keys | | Innobase | Yes | alias for InnoDB | | Bdb | no | supports transactions and page-level locking | | Berkeleydb | no | alias for bdb | | Ndbcluster | no | clustered, fault-tolerant, memory-based tables | | NDB | no | alias for ndbcluster | | Example | no | example storage engine | | Archive | no | archive storage engine | | CSV | no | CSV storage engine | + ------------ + --------- + ------------------------------------------------------- +

16 rows in SET (0.01 Sec) This table shows the list of available database engines and whether these engines are supported on the current database server.

For MySQL versions earlier than MySQL 4.1.2, you can use mysql> show variables like "have _ %" (display variables similar to "have _ % ):

Mysql> show variables like "have _ % "; + ------------------ + ---------- + | variable_name | value | + ------------------ + ---------- + | have_bdb | Yes | | have_crypt | Yes | | have_innodb | disabled | | have_isam | Yes | | have_raid | Yes | | have_symlink | Yes | | have_openssl | Yes | | have_query_cache | Yes | + ------------------ + ---------- + 8 rows in SET (0.01 Sec)

You can set the available engine in the MySQL Installation Software by modifying the options in the setting script. If you are using a pre-packaged MySQL binary release software, the software contains common engines. However, it should be noted that if you want to use some uncommon engines, especially CSV, rchive (archive) and blackhole (black hole) engines, you need to manually recompile the MySQL source code.

Use a specified storage engine

You can specify a storage engine in many ways. The simplest method is to set a default engine type (using the storage_engine option) in the MySQL settings file if you like a storage engine that meets the needs of most of your databases) you can also add the -- default-storage-engine or -- default-table-type option after the command line when starting the database server.

A more flexible way is to specify the storage engine used when the mysql client is released along with the MySQL server. The most direct method is to specify the storage engine type when creating a table, as shown below:

Create Table mytable (ID int, title char (20) engine = InnoDB

You can also change the storage engine used by the existing table by using the following statement:

Alter table mytable engine = MyISAM

However, you need to be very careful when modifying the table type in this way, because modifying a type that does not support the same index, field type, or table size may cause data loss. If you specify a storage engine that does not exist in your current database, a MyISAM (default) type table will be created.

Differences between storage engines

To determine which storage engine to choose, we must first consider the different core functions provided by each storage engine. This feature allows us to differentiate different storage engines. We generally divide these core functions into four categories: supported fields and data types, lock types, indexes, and processing. Some engines have unique features that can motivate you to make decisions. Let's take a closer look at these specific issues.

Field and Data Type

Although all these engines support common data types, such as integer, real, and complex types, not all engines Support other field types, especially blogs (Binary large objects) or text type. Other engines may only support limited character widths and data sizes.

These limitations may directly affect the data you can store, or indirectly affect the type of the search you implement or the index you create for the information. These differences can affect your application.ProgramBecause you must choose to make decisions on the features of the storage engine based on the data type you want to store.

Lock

The locking function in the database engine determines how to manage access and update information. When an object in the database is locked for Information Update, other processes cannot modify the data before the update is complete (in some cases, this data cannot be read ).

Locking affects not only how many different applications update information in the database, but also the query of that data. This is because the query may need to access the data being modified or updated. In general, this latency is very small. Most locking mechanisms are designed to prevent multiple processes from updating the same data. Because inserting and updating information to data both needs to be locked, you can imagine that multiple applications may have a great impact on using the same database.

Different storage engines Support locking at different object levels, and these levels affect information that can be accessed at the same time. Three supported levels are available: Table locking, block locking, and row locking. Table locks are the most supported. Such locks are provided in MyISAM. When the data is updated, it locks the entire table. This prevents many applications from updating a specific table at the same time. This has a great impact on many multi-user databases, because it delays the update process.

Page-level locking uses the Berkeley DB Engine and locks data based on the uploaded information page (8 KB. When the database is updated in many places, this locking will not cause any problems. However, the last 8 KB of the data structure will be locked when several lines of information are added. When a large number of rows are required, especially a large amount of small data, this will cause problems.

Row-level locking provides the best parallel access function. Only one row of data in a table is locked. This means that many applications can update the data of different rows in the same table without locking. Only the InnoDB Storage engine supports row-level locking.

Create an index

Index creation can significantly improve performance when searching and recovering data in the database. Different storage engines provide different indexing technologies. Some technologies may be more suitable for the data type you store.

Some storage engines do not support indexing at all, probably because they use basic table indexes (such as the merge engine) or because indexes (such as federated or blackhole engines) are not allowed in data storage ).

Transaction Processing

The transaction processing function provides reliability during the update and insertion of information into the table. This reliability is achieved through the following method, which allows you to update the data in the table, but only accepts the changes to the table after all the related operations of the application are complete. For example, each accounting entry in the accounting process will include changes to the debit and credit account data, you need to use the transaction processing function to ensure that the changes to the data of the debit account and the credit section are successfully completed before accepting the changes. If any operation fails, you can cancel the transaction and the modifications do not exist. If the transaction processing is completed, we can confirm this operation by allowing this modification.

 

 

More write less read/more read less write ------------> MyISAM

Read/write concurrency -------------------- InnoDB