Shulou(Shulou.com)06/01 Report--

This article mainly introduces which storage engines mysql has, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Execute the following instructions to view all mysql storage engines

InnoDB storage engine

InnoDB is the preferred engine for transactional databases, supporting transaction security tables (ACID), row locking and foreign keys. As you can see in the figure above, InnoDB is the default MySQL engine. The main features of InnoDB are:

1. InnoDB provides MySQL with transaction security (ACID compatible) storage engine with commit, rollback and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-like unlocked read in the SELECT statement. These features increase multi-user deployment and performance. In SQL queries, you are free to mix tables of type InnoDB with other MySQL table types, even in the same query

2. InnoDB is designed to handle the maximum performance of a large amount of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine lock.

3. The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB puts its tables and indexes in a logical tablespace, which can contain several files (or raw disk files). This is different from the MyISAM table, for example, where each table is stored in a separate file in the MyISAM table. InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB

4. InnoDB supports foreign key integrity constraints. When storing data in a table, the storage of each table is stored in the primary key order. If the primary key is not specified in the table definition, InnoDB generates a 6-byte ROWID for each row and uses it as the primary key.

5. InnoDB is used in many large database sites that need high performance.

InnoDB does not create a directory. When using InnoDB, MySQL will create a 10MB-sized automatically extended data file named ibdata1 under the MySQL data directory, and two 5MB-sized log files named ib_logfile0 and ib_logfile1

MyISAM storage engine

MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing and other application environments. MyISAM has high insertion and query speed, but does not support things. The main features of MyISAM are:

1. Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files

2. When deleting and updating and inserting are mixed, dynamic-sized rows produce less fragmentation. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.

3. The maximum number of indexes per MyISAM table is 64, which can be changed by recompilation. The maximum number of columns per index is 16

4. The maximum key length is 1000 bytes, which can also be changed by compiling. If the key length exceeds 250bytes, a key exceeding 1024 bytes will be used.

5. BLOB and TEXT columns can be indexed

6. NULL is allowed in the column of the index, and this value accounts for 0 bytes of each key.

7. All numeric key values are stored in high byte priority to allow a higher index compression

8. Each MyISAM type table has an internal column of AUTO_INCREMENT, which is updated during INSERT and UPDATE operations, and the AUTO_INCREMENT column is refreshed. Therefore, the AUTO_INCREMENT column update of the MyISAM type table is faster than the InnoDB type AUTO_INCREMENT

9. You can put data files and index files in different directories

10. Each character column can have a different character set

11. Tables with VARCHAR can record fixed or dynamic length

12. VARCHAR and CHAR columns can be up to 64KB

Using the MyISAM engine to create a database will produce three files. The name of the file starts with the table name, with the extension where the file type: frm file storage table definition, data file extension is. MYD (MYData), the extension of the index file. MYI (MYIndex)

MEMORY storage engine

The MEMORY storage engine stores the data in the table in memory and provides quick access without querying and referencing other table data. The main features of MEMORY are:

1. Each table of a MEMORY table can have up to 32 indexes, 16 columns per index, and a maximum key length of 500 bytes.

2. MEMORY storage engine executes HASH and BTREE miniature

3. You can have non-unique key values in a MEMORY table

4. The MEMORY table uses a fixed record length format

5. MEMORY does not support BLOB or TEXT columns

6. MEMORY supports AUTO_ index columns and indexes on columns that can contain null values

7. The MEMORY table is shared between clients (just like any other non-TEMPORARY table)

8. The MEMORY table memory is stored in memory, and the memory is shared by the internal tables created by the MEMORY table and the server in the idle time of query processing.

9. When the contents of the MEMORY table are no longer needed, to free the memory used by the MEMORY table, you should execute DELETE FROM or TRUNCATE TABLE, or delete the entire table (using DROP TABLE)

Selection of storage engine

If you want to provide transaction security (ACID compatibility) with commit, rollback, crash resilience, and require concurrency control, InnoDB is a good choice

If the data table is mainly used to insert and query records, the MyISAM engine can provide higher processing efficiency.

If you only temporarily store data, the amount of data is small, and you do not need high data security, you can choose to save the data in the in-memory Memory engine, which is used in MySQL as a temporary table to store the intermediate results of the query.

If there are only INSERT and SELECT operations, you can choose Archive,Archive to support highly concurrent insert operations, but it is not transaction-safe itself. Archive is very suitable for storing archive data. For example, you can use Archive to record log information.

Which engine to use requires flexible choice, multiple tables in a database can use different engines to meet a variety of performance and practical needs, using an appropriate storage engine will improve the performance of the whole database.

Thank you for reading this article carefully. I hope the editor will share what storage engine content of mysql is helpful to everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you have any problems. Detailed solutions are waiting for you to learn!

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.