The MySQL engine

MySQL Engine overview

• Data in MySQL is stored in files (or memory) in a variety of different technologies. 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.
• Different engines, features, space-consuming, read performance differences
• MySQL engine has MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, blackhole
• The most commonly used engines are MyISAM and InnoDB.
• After Mysql5.5.5, InnoDB is the default engine

View supported engines for a database

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+----------------------------------------------------------------+--- -----------+------+------------+| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | Blackhole | YES | /dev/NULLStorage Engine (anything youWriteto it disappears) | NO | NO | NO | | CSV | YES | CSV Storage Engine | NO | NO | NO | | MEMORY | YES | Hash based, storedinchMemory, useful forTemporary Tables | NO | NO | NO | | Performance_schema | YES | Performance Schema | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------+----------- ---+------+------------+

mysql> show engines;

MyISAM engine

　Each MyISAM table corresponds to three files on a hard disk
The FRM save table definition is not part of the MyISAM engine, but a part of the server
MYD Save the table's data,
MYI is the index file for the table

MyISAM Features

• Transaction not supported: The MyISAM storage engine does not support transactions, so business scenarios that are required for transactions cannot be used
• Table-level locking: The locking mechanism is a table-level index, which can make the implementation cost of the lock small but also significantly reduces its concurrency performance
• Read/write blocking: Not only does blocking read when writing, MyISAM also blocks writes while reading, but reading itself does not block additional reads
• Only the index is cached: MyISAM can reduce disk IO by Key_buffer caching to greatly improve access performance, but this buffer caches only the index, not the data
• Reads faster and consumes less resources
• FOREIGN KEY constraints are not supported, but full-text indexing is supported

MyISAM Applicable Scenarios

1. No transaction support required (not supported)
2. A read-oriented business, or a single, read-only, or write-through operation of a database
3. Relatively low concurrency (locking mechanism issues)
4. Relatively few data modifications (blocking issues)
5. Data consistency requirements are not very high
6. Poor hardware Resources

MyISAM Tuning Advantages

• Try to index (caching mechanism) Query_cache_size/query_cache_limit/query_cache_min_res_unit
• Adjust read and write priorities to ensure important operations are prioritized based on actual requirements
• Enable deferred insertion for improved high-volume write performance
• Try to keep the insert data written to the tail in order to reduce blocking
• Decomposition of large operations (decomposition of SQL), reducing the blocking time of a single operation
• Reduce concurrency, some high concurrency scenarios are queued by application
• For relatively static data, full use of the query cache can greatly improve access efficiency
• The count of MyISAM is particularly efficient when full-table scans, and count with other conditions requires actual data access

InnoDB features

• Transactions Transaction support: Supports 4 transaction isolation levels, supports multiple versions of Read
• Row-level locking Row-level locking: Through the index implementation, the full table scan will still be a table lock, pay attention to the impact of Gap lock
• Read-write blocking is related to transaction isolation level
• The data cache & index caches has a very efficient caching feature: it can cache the index
• The entire table and primary key are stored in cluster form, forming a balance tree all Secondary index will hold the primary key information
• Full-text Search Indexes Full-text index
• Foreign keys supports foreign KEY constraints
• Supports partitioning, table spaces

InnoDB Applicable Scenarios

• requires transaction support (with good transactional characteristics) sql statement operations that make up each sql statement, execution is either full success or failure.

1 , Atomicity (atomicity): All operations in a transaction are indivisible in the database, either all completed or not executed.  2, Consistency (consistency): Several transactions executed in parallel, the results of which must be consistent with the result of serially executed in a sequential order.  3. Isolation (Isolation): The execution of a transaction is not disturbed by other transactions, and the intermediate result of the transaction execution must be transparent to other transactions. For  example --single-transaction4, Persistence (durability): For any committed transaction, the system must ensure that the transaction changes to the database are not lost, even if the database fails. Once a transaction is committed, its changes to the data in the database are permanent, and if an error is made, the transaction is not allowed to be revoked, only through the "compensatory transaction"

four major features of transactions (acid, for short)

• Row-level locking is good for high concurrency, but needs to ensure that queries are done by indexing
• More frequent scenarios for data updates
• High Data consistency requirements
• Hardware device memory is large, can take advantage of InnoDB better cache capacity to improve memory utilization, reduce disk IO as much as possible. Consumes more resources than MYISAM,INNODB, and it's not myisam faster.

InnoDB Tuning Advantages (key parameter innodb_buffer_pool_size about 50% of memory)

• The primary key is as small as possible to avoid excessive space burden on secondary index
• Avoid full table scans because table locks are used
• Cache all indexes and data as much as possible, improve response speed, and less IO
• In large batches of small inserts, try to control your own transactions instead of using autocommit auto-commit
• Set Innodb_flush_log_at_trx_commit parameter value rationally, do not pursue security excessively
• Avoid primary key updates, as this can result in a lot of data movement

The MySQL engine