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

Editor to share with you what is the use of InnoDB, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

InnoDB is a general-purpose storage engine with high performance and high reliability. Since MySQL 5.5, it is the default storage engine for MySQL.

Key features of InnoDB table

1. DML operations follow ACID principles, and transactions protect user data through commit, rollback, and crash-recovery and other functions.

two。 Support for row-level locks, similar to oracle-like consistent reads to improve multi-user concurrency and performance

3. InnoDB tables are organized and stored on disk in the order of primary keys (index organizes tables), and queries based on primary keys are optimized.

4. Support for foreign key constraints

5. Can be mixed with tables from other storage engines. Such as join query, etc.

6. InnoDB can make efficient use of CPU when dealing with large amounts of data.

Features of InnoDB engine

a. Geospatial indexing is supported starting with MySQL 5.7.5

B.Innodb does not support hash indexes, but innodb internally uses hash indexes to implement adaptive hashing

c. Full-text indexing is supported starting from MySQL 5.6.4

Support for compressing tables in d.Barracuda file format

E 、 f 、 g. Data encryption, master-slave replication, backup and point-in-time recovery are implemented in the server layer

InnoDB uses buffer pool in memory to cache data and indexes.

By default, innodb_file_per_table is turned on, and each table and its associated index are stored in a separate file

When innodb_file_per_table is turned off, all innodb tables and indexes are stored in a single system tablespace (which may contain several files or partitions).

Since MySQL 5.7.6, innodb tables can be stored in a regular tablespace, and multiple tables share a tablespace to store data.

Innodb tables can handle large amounts of data, even under OS where the file size is limited to 2GB

Innodb as the default storage engine MySQL 5.7innodb is the default storage engine. Innodb is a transaction-safe storage engine in mysql, which protects user data through commit, rollback and and crash-recovery functions. Innodb row-level locks (no lock escalation issues), and consistent reads similar to oracle to improve multi-user concurrency. The Innodb table is an index organization table, which organizes the storage of data in the order of primary keys to reduce IO. Foreign key constraints are supported.

Benefits of Innodb tables

1. Crash recovery

2. Buffer pool buffers accessed data and indexes, and hot data is processed directly in memory. This cache is used for many types of information, thus speeding up processing.

3. Foreign key constraint

4. If the data in disk or memory is corrupted, you can use the checksum mechanism to repair the data before using it.

5. The index organizes the features of the table, and queries are fast through the primary keys where, order by,group by and join

6. Change buffering feature, automatically optimize DML operation. Not only does the Innodb table allow parallel reads and writes, it also caches modified data to reduce IO

7. Adaptive hash

8. Compressed tables and their associated indexes

9. Delete and create indexes online

10. Can quickly Truncate a file_per_table, and free up space, the operating system can be reused. Instead of file_per_table off, it is stored in the system tablespace, free space, only innodb can be reused

11. More efficient storage for BLOB and long text, in dynamic row mode

twelve。 Internal work can be monitored through INFORMATION_SCHEMA

13. You can view performance-related information through the table under Performance

Innodb Best practices some recommendations for using innodb tables:

1. Define a primary key for each table: use the most frequently queried column (or more columns), or if there is no obvious primary key, use a self-growing value as the primary key

two。 Turn off autocommit: the performance limit is submitted up to hundreds of times per second (limited by IO performance of the storage device)

3. Use START TRANSACTION and COMMIT to control the granularity of transaction commit

4. Instead of using LOCK TABLES statements, innodb can handle multiple sessions reading and writing to the same table without sacrificing reliability and high performance. To get exclusive access to some lines, you can use SELECT... FOR UPDATE to lock in the desired row

5. Turn on the innodb_file_per_table option to store the data and indexes of each table in a separate file rather than in a large system tablespace. And it is convenient to use some functions, such as table compression, fast truncate and so on.

Innodb_file_per_table is enabled by default starting from MySQL 5.6.6.

6. Evaluate whether your data and access patterns are applicable to the innodb table compression feature (specified in the create table statement (ROW_FORMAT=COMPRESSED)), table compression can improve IO performance

7. The runtime specifies the-- sql_mode=NO_ENGINE_SUBSTITUTION option to prevent the required storage engine from being disabled or uncompiled, automatically replacing the storage engine

Turning off the InnoDB engine Oracle recommends innoDB as the first choice, starting with MySQL 5.5, which is the default storage engine.

Starting with MySQL 5.7.5, the-- skip-innodb (--innodb=OFF,-- disable-innodb) option is deprecated, and if you use this option, you will get a warning. This option will be removed in future versions of MySQL.

Before 5.7.5, if you don't want to use the innodb table, turn it off by following these steps:

1. Specify-- innodb=OFF or-- skip-innodb to shut down the innodb engine at startup

two。 Because innodb is the default engine, to turn it off, you must use-- default-storage-engine and-- default-tmp-storage-engine to set the default other engines for permanent and temporary table engines

3. To prevent database crash when querying innodb-related information_schema tables, you need to disable their related tables. Specify the following in the [mysqld] section of the my.cnf location file:

Loose-innodb-trx=0

Loose-innodb-locks=0

Loose-innodb-lock-waits=0

Loose-innodb-cmp=0

Loose-innodb-cmp-per-index=0

Loose-innodb-cmp-per-index-reset=0

Loose-innodb-cmp-reset=0

Loose-innodb-cmpmem=0

Loose-innodb-cmpmem-reset=0

Loose-innodb-buffer-page=0

Loose-innodb-buffer-page-lru=0

Loose-innodb-buffer-pool-stats=0

Loose-innodb-metrics=0

Loose-innodb-ft-default-stopword=0

Loose-innodb-ft-inserted=0

Loose-innodb-ft-deleted=0

Loose-innodb-ft-being-deleted=0

Loose-innodb-ft-config=0

Loose-innodb-ft-index-cache=0

Loose-innodb-ft-index-table=0

Loose-innodb-sys-tables=0

Loose-innodb-sys-tablestats=0

Loose-innodb-sys-indexes=0

Loose-innodb-sys-columns=0

Loose-innodb-sys-fields=0

Loose-innodb-sys-foreign=0

Loose-innodb-sys-foreign-cols=0

The above is all the content of this article "what's the use of InnoDB?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.