Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "what are the differences between mysql engines". Xiaobian shows you the operation process through actual cases. The operation method is simple, fast and practical. I hope this article "what are the differences between mysql engines" can help you solve the problem.

Differences: 1. InnoDB engine is used to process a large number of short-term transactions and rarely rolls back;2. MyISAM engine does not support transaction and row-level locks, and cannot be safely recovered after a crash;3. Archive engine supports Insert and Select operations;4. Blackhole engine does not have any storage mechanism and will discard all written data.

Operating environment of this tutorial: Windows 10 system, mysql version 8.0.22, Dell G3 computer.

What is the difference between MySQL engines?

I. InnoDB

InnoDB is MySQL's default transactional engine and the most important and widespread storage engine. It is designed to handle a large number of short-term transactions, most of which are normally committed and rarely rolled back. InnoDB's performance and automatic crash recovery features make it popular for non-transactional storage needs as well. In addition to using other engines for very specific reasons, InnoDB is also a very good object to spend time on.

InnoDB's data is stored in tablespaces, which are black box file systems managed by InnoDB and consist of a series of system files. InnoDB can store data and indexes for each table in separate files. InnoDB can also use bare devices as the table space storage medium.

InnoDB prevents phantom reads by next-key locking. InnoDB is based on clustered indexes, which are very different from other storage engines. Cluster indexes have high performance for primary key queries, but their secondary indexes (non-primary key indexes) must contain primary key columns. So if the primary key column is large, the index will be large.

II. MyISAM

MyISAM was the default engine prior to 5.1, and MyISAM had a number of special features, including full-text indexing, compression, and spatial functions. But MyISAM does not support transaction and row-level locking, and it cannot be safely recovered after a crash. Even though MyISAM supports transactions in later versions, many people still have the concept of an engine that does not support transactions.

MyISAM isn't doing nothing. For some read-only data, or small tablespaces that can tolerate recovery operations, MyISAM can be used. MyISAM stores tables in two files: a data file and an index file. They are.MYD and.MYI extensions respectively. MyISAM tables can contain dynamic or static rows. MySQL selects which row format to use based on the table definition. The number of rows in the MyISAM table depends on disk space and the maximum size of a single file in the operating system.

In MySQL, the default configuration can store only 256TB of data. Because the pointer to the data record is 6 bytes long. You need to modify the MAX_ROWS and AVG_ROW_LENGTH options that modify tables. Multiplication of two is the maximum size. This will cause the index to be rebuilt.

MyISAM locks the entire table, not the row lock. When reading, it locks the table with a shared lock, and when writing, it locks the exclusive lock. However, while the table has read queries, records can also be written to the table.

For MyISAM, even long fields such as Blob, Text, etc. can be indexed based on the first 500 characters. MyISAM supports full-text indexing, which is an index created based on word segmentation, and can also support complex queries.

MyISAM can choose to delay updating index keys, specify the delay_key_write option when creating the table, and write the modified index data not to disk immediately after each modification is completed, but to the cache, and only write the index to disk when cleaning the cache or closing the table. This can greatly improve write performance, but in the event of a host crash, the index will be corrupted and a repair operation will be required.

Another feature of MyISAM is support for compressed tables. If the data is not modified after it is written, then this table fits into MyISAM compressed tables. MyISAM tables can be packaged using myisampack, compressed tables are data that cannot be modified. Compressed tables can greatly reduce disk usage, so they can reduce disk IO and improve performance. Compressed tables also support indexes, but indexes are also read-only.

MyISAM isn't that bad overall, but since there is no row locking mechanism, it will cause all queries to be Locked when writing heavily.

Other storage engines

MySQL also has some other special-purpose engines, some of which may no longer be supported, see Database Support Engine for specific support.

III. Archive

Archive engine support is Insert, Select operations, now supports indexing, Archive engine caches all writes and compresses write rows with zlib, so less disk IO than MyISAM tables. However, a full table scan needs to be performed on each Select query. So Archive is suitable for logging and data collection applications. This type of application often requires a full table scan for analysis and can also be used in faster Insert scenarios.

The Archive engine supports row-level locks and dedicated caches, so it can achieve high concurrent writes, and Archive prevents other Select executions until all the rows in the query are returned, which is used to achieve consistent reads. In addition, batch write data is invisible to read operations until the end of batch write, which mimics the characteristics of transactions and MVCC, but Archive is not a transactional engine, but a simple engine optimized for high write compression.

IV. Blackhole

Blackhole does not implement any storage mechanism, it will discard all written data, do not do any saving, but the server will log Blackhole tables, used to copy data to the backup repository, or simply log to the log, this special storage engine can be used in some special replication architecture and log audit. But not recommended.

V. CSV

CSV engine can process ordinary CSV files as MySQL tables, but this table does not support indexes. CSV can be copied or copied out when the database is running. Data in Excel and other spreadsheets can be stored in CSV files, and then copied to MySQL, which can be opened and used in MySQL. Similarly, if you write data to a CSV engine table, other external programs can read CSV data from the table's data file. CSV can therefore be used as a data exchange mechanism. Very easy to use.

VI. Federated

The Federated Engine is a proxy for accessing other MySQL servers, creating a client connection to the remote MySQL server, transmitting queries to the remote server for execution, and then extracting or sending the required data. The storage engine was originally designed to compete with similar features of enterprise-class databases such as Microsoft SQL Server and Oracle, and is more of a marketing exercise. Although the engine seems to provide a good cross-server flexibility, it often causes problems and is disabled by default.

VII. Memroy

Memory tables (formerly known as HEAP tables) are useful if you need to access data quickly, and if the data won't be modified or lost after a restart. Memory tables are at least an order of magnitude faster than MyISAM tables because all data is stored in memory and no disk I/O is required. The structure of the Memory table is preserved after a restart, but the data is lost.

VIII. Merge

Merge engine is a variant of MyISAM engine. A Merge table is a virtual table that merges multiple MyISAM tables. If MySQL is used for logging or data warehouse-like applications, the engine can work. But with the introduction of partitioning, the engine has been abandoned

IX. NDB Cluster Engine

The NDB Cluster Storage Engine acts as an interface between SQL and NDB native protocols. The combination of MySQL servers, NDB cluster storage engines, and distributed, share-nothing, disaster-tolerant, and highly available NDB databases is called MySQL Cluster.

special

X. PERFORMANCE_SCHEMA

MySQL 5.5 adds a new storage engine named PERFORMANCE_SCHEMA , which is mainly used to collect database server performance parameters. MySQL users are unable to create tables with a storage engine of PERFORMANCE_SCHEMA.

The performance_schema provides the following functionality:

Provide details of process waiting, including locks, mutex variables, and file information;

Save historical event summary information to provide detailed judgment on MySQL server performance;

It is very easy to add and delete monitoring event points, and you can change the monitoring cycle of mysql server at will, for example (CYCLE, MICROSECOND)

performance_schema enabled and partial table functionality

Performance is easy to start. Add performanc_schema to [mysqld] in my.cnf to check whether the performance database is started:

SHOW VARIABLES LIKE 'performance_schema';

If the returned value is ON, it indicates that the performance database is normally opened.

About "what are the differences between mysql engines" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the industry information channel. Xiaobian will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.