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

Storage engine of MYSQL

1 MYSQL can store data in files and memory with different technologies, which becomes the storage engine.

2 each storage engine uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of different functions

3 commonly used storage engines supported by MYSQL

3.1 MYISAM

Suitable for situations with few transactions, storage limit up to 256TB, support for indexing, table-level locking, and data compression

3.2 InnoDB

Suitable for situations where there are many transactions and foreign key support is required, storage is limited to 64TB, transactions and indexes are supported, and lock granules are row locks.

3.3 Memory

3.4 CSV

Indexing is not supported

3.5 Archive

3.6 BlackHole

Black hole engine, the read of the written data will disappear, and is generally used to relay the data.

4 concurrency control

Ensure data consistency and integrity when multiple connections modify records

5 lock

5.1 shared lock (read lock)

In the same time period, multiple users can read the same resource without any change in the data during the reading process.

5.2 exclusive lock (write lock)

Only one user can write to the resource at any one time, and other read or write lock operations will be blocked when the write lock is performed.

6 lock granule

6.1 Table lock

It is a locking strategy with the least overhead.

6.2 Line Lock

It is the most expensive locking strategy.

7 transactions

Used to ensure the integrity of the database

8 characteristics of transactions

Atomicity

Consistency

Isolation

Persistence

9 Foreign key is a strategy to ensure data consistency

10 Index

Is a structure that arranges the values of one or more columns in a data table

11 the index is divided into

General index

Unique index

Full-text index

Hash index

Btree index and so on

12 methods of modifying the storage engine

12.1 by modifying the MYSQL configuration file

-default-storage-engine=engine

12.2 by creating a command table

Step 1 CREATE TABLE table name

The second step S1 character length such as VARGINE (10)

Step 3 ENGINE= the storage engine you want to modify

12.3 through the command to modify the data table

ALTER TABLE table name ENGINE= the storage engine you want to modify

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.