MYSQL---storage engine

MySQL can store data in files in different technologies, a technique known as the storage engine.

Each storage engine uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different functions.

The storage engines supported by MySQL are:

MyISAM

InnoDB

Memory

Csv

Archive

Concurrency control: Ensures data consistency and integrity when multiple connections modify records.

(If a user is deleting record 22nd and another user may be querying number 22nd, this may cause an error, in order to avoid this error, concurrency control is required, and the system will take a shared lock (read lock) and an exclusive lock (write lock))

Shared locks: During the same time period, multiple users can read the same resource without any changes to the data during the read.

Exclusive lock: Only one user can write to the resource at any time, and when a write lock is made, other read or write locks are blocked.

Lock particles

-table lock, which is a locking strategy with minimal overhead. (a table with one lock, less overhead) when a user writes to a table, other users cannot read or write to the table if a table lock is added.

-row lock, which is the most expensive lock policy. (a table requires more than one lock, with a large overhead)

Transaction processing

. Things

-Things used to ensure the integrity of the database

Example: Transfer

User A transfers 200 to User B

Steps:

(1) minus 200 yuan from a account

(2) b account plus 200 yuan

Any problem at any step goes back to its original state.

。 ACID Properties of Things

-Atomicity (atomicity)

-Consistency (consistency)

-Isolation (Isolation)

-Durability (durability)

。 FOREIGN key

-a strategy to ensure consistent data

。 Index

-is the structure for sorting one or more column values in a data table. (equivalent to a book's catalogue)

MYSQL---storage engine