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

1. InnoDB introduction

InnoDB is a highly reliable, high-performance multi-purpose database storage engine. InnoDB is the default storage engine in MySQL5.7. Of course, you can configure other storage engines, such as table creation statements that are not set to InnoDB through ENGINE.

Key features of InnoDB

List the following important points:

DML operations are performed in ACID mode (insert, update, delete operations, note that query operations may sometimes be thought of as DML operations, such as select * from table for update), that is, about transaction commit, rollback, fault recovery.

Row-level locks and Oracle-like unlocked reads and multi-user support.

In a clustered index, InnoDB sorts the data when the data is stored according to the primary key of the table to reduce the Ibind O when querying through the primary key. The primary key of each InnoDB table is a clustered index

In terms of ensuring data integrity, InnoDB supports foreign keys. When there are foreign keys, there are foreign key constraints when data is inserted, updated and deleted.

InnoDB characteristics

Storage limits

Storage limit

64TB

Transactions

Business

Yes

Locking granularity

Lock granularity

Row

MVCC

Multi-version concurrency control

Yes

Geospatial data type support

Geographic coordinate data type support

Yes

Geospatial indexing support

Coordinate index support

Yes [a]

B-tree indexes

B-tree index

Yes

T-tree indexes

T-tree index

No

Hash indexes

Hash indexing

No [b]

Full-text search indexes

Full-text index

Yes [c]

Clustered indexes

Clustered index

Yes

Data caches

Caching

Yes

Index caches

Index cache

Yes

Compressed data

Compression function

Yes [d]

Encrypted data [e]

Encryption function

Yes

Cluster database support

Cluster support

No

Replication support [f]

Replication support

Yes

Foreign key support

Foreign key support

Yes

Backup / point-in-time recovery [g]

Point-in-time recovery

Yes

Query cache support

Query cache support

Yes

Update statistics for data dictionary

Update static dictionary data

Yes

Some of the features describe:

[a] InnoDB supports geolocation in MySQL5.7.5 and later versions.

[B] InnoDB uses a hash index inside an adaptive hash index.

[C] InnoDB supports full text in MySQL 5.6.4 and later.

[d] the table compression function requires Barracuda file format

[e] it is realized by encryption function.

[F] supported, but not implemented at the engine level.

[G] supported, but not implemented at the engine level. .

Comparisons of the various storage engines are detailed in later chapters.

1.1 Why choose InnoDB

The reasons for choosing InnoDB are as follows:

If your server crashes due to hardware or software problems, you don't have to think about what happened to the database, and you don't need to do any special processing after the database is restarted. InnoDB's fault recovery mechanism will automatically help you complete all submissions before the failure occurs, and do nothing about actions that are not committed.

The InnoDB storage engine holds a cache pool of table data and indexes in memory for the accessed data, and some user queries can return data directly from the cache pool in memory. This caching method is suitable for many scenarios that improve processing speed. On a dedicated database server, the memory footprint of the InnoDB cache pool is usually more than 80% of the total memory.

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.