Differences between MySQL storage engines

Differences between MySQL 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 the performance and functions of your application, because 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.