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

This article mainly introduces "what is InnoDB storage engine". In daily operation, I believe many people have doubts about what is InnoDB storage engine. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what is InnoDB storage engine"! Next, please follow the editor to study!

1. Brief introduction to InnoDB

After MySQL version 5.5, the default storage engine is InnoDB. InnoDB is a general purpose storage engine with both high reliability and high performance. In MySQL 5.7. unless you configure another default storage engine, executing a statement that CREATE TABLE does not specify ENGINE will create an InnoDB table.

# check the storage engine mysql > show engines supported by MySQL +- -+ | Engine | Support | Comment | Transactions | XA | Savepoints | +- -- + | InnoDB | DEFAULT | Supports transactions Row-level locking, and foreign keys | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | | MEMORY | YES | Hash based, stored in memory Useful for temporary tables | NO | | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | | MyISAM | YES | MyISAM storage engine | NO | | CSV | | YES | CSV storage engine | NO | | ARCHIVE | YES | Archive storage engine | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | | | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | +-| -+ # View default storage engine mysql > show variables like 'default_storage_engine' +-+-+ | Variable_name | Value | +-+-+ | default_storage_engine | InnoDB | +-+-+ 2. InnoDB advantage

The reason why InnoDB is so popular is that it has many advantages in terms of functions.

1) support transactions

The most important thing about InnoDB is to support transactions, which is a very important reason why InnoDB has become the most popular storage engine in MySQL. In addition, InnoDB also implements four isolation levels (READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ and SERIALIZABLE), making the support for transactions more flexible.

2) good disaster recovery

InnoDB ensures the security of data through commit, rollback and crash-recovery.

Specifically, crash-recovery means that if the server crashes due to hardware or software problems, no matter what the state of the data is at that time, after restarting MySQL, InnoDB will automatically return to the state before the crash.

3) use row-level locks

InnoDB changes the locking mechanism of MyISAM and implements row locking. Although the row locking mechanism of InnoDB is accomplished through indexes, after all, most SQL statements in the database use indexes to retrieve data. The row locking mechanism also enhances the competitiveness of InnoDB under the pressure of high concurrency.

4) the buffer processing is realized.

InnoDB provides a special buffer pool to achieve buffer management, which can buffer not only index but also data. Commonly used data can be processed directly from memory, which is faster than getting data from disk. On a dedicated database server, up to 80% of the physical memory is typically assigned to the buffer pool.

5) Foreign keys are supported

InnoDB supports foreign key constraints, checking foreign keys, inserts, updates, and deletions to ensure data integrity. When storing data in a table, the storage of each table is stored in primary key order, and if the primary key is not explicitly specified at the time of the table definition, InnoDB generates a 6-byte ROWID for each row as the primary key.

At this point, the study on "what is the InnoDB storage engine" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.