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

I don't know if you have any understanding of the main features and usefulness of similar MySQL database storage engine before. Today, I'm here to tell you briefly. If you are interested, let's take a look at the body. I believe you will gain something after reading the main features and usefulness of the MySQL database storage engine.

View of the storage engine

View the storage engine that the database can configure

SHOW ENGINES

View the storage engine being used by the database

Show table status from Library where name=' Table 1; # View the storage engine use library being used in Table 1; # enter the database show create table Table 1

Configuration of storage engine

Modify by command

Use library; # enter the database alter table Table 1 engine=MyISAM; # modify the storage engine for MyISAMalter table Table 2 engine=InnoDB; # modify the storage engine to InnoDB

Modify through the configuration file (remember, it is only useful for new tables created later! )

Vim / etc/ my.cnf.mysqld] default--storage-engine=MyISAM / / add the storage engine use library directly when you want to set it as the default storage engine to create tables; # enter the library create table Table 1 (id int) engine=InnoDB; # modify the storage engine for InnoDBcreate table Table 2 (id int) engine=MyISAM; # modify the storage engine for MyISAMshow create table tables; # View the characteristics and applicable scenarios of the storage engine

Introduction to the characteristics and applicable scenarios of MyISAM

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 transactions. The main features of MyISAM are:

1) you can put data files and index files in different directories.

2) Table-level locking form in which the data locks the entire table when it is updated.

3) the database blocks each other in the process of reading and writing.

4) you can set the cache index through key_buffer_size.

5) the data is written separately or read at a fast speed and takes up less resources.

6) Foreign key constraints are not supported, only full-text indexing is supported.

7) three files are generated when the table is created, and the file name starts with the name of the table.

[.frm file storage table definition | extension of data file: .MYD (MYData) | extension of index file: .MYI (MYIndex)]

Applicable scenarios:

1) non-transactional applications

2) read-only applications

3) Space application

Introduction to the characteristics and applicable scenarios of InnoDB

InnoDB is the preferred engine for transactional databases, supporting transaction security tables (ACID), row locking and foreign keys, and is the default storage engine for MySQL databases 5.5 and later. 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 InnoDB-type tables with other MySQL table types, even in the same query.

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

3) InnoDB storage engine is fully integrated with MySQL CVM. 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 will generate a 6-byte ROWID for each row and use it as the primary key.

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

6) InnoDB does not create a directory, and 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.

Applicable scenarios:

Full-text indexing and spatial functions are already supported in MySQL5.7 and later versions.

Suitable for most OLTP applications (On-Line Transaction Processing online transaction processing process (OLTP), also known as transaction-oriented processing process, its basic feature is that the user data received by the foreground can be immediately transmitted to the computing center for processing, and the processing results can be given in a very short time, which is one of the ways to respond quickly to user operations.

The difference between MyISAM and InnoDB

MyISAM and InnoDB are the two storage engines most commonly used by many people when using MySQL, and each has its own advantages and disadvantages, depending on the specific application. The basic difference is that MyISAM does not support advanced processing such as transaction processing, emphasizing performance, which is faster than InnoDB, but does not provide transaction support, while InnoDB provides transaction support, foreign keys and other advanced database functions.

All in all, if the data table is mainly used to insert and query records, MyISAM can provide higher processing efficiency; if you want to provide transaction security (ACID compatibility) with commit, rollback, and crash resilience, and require concurrency control, InnoDB is a good choice.

What are the main features and usefulness of MySQL database storage engine after reading this article, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.