MySQL Storage engine

Only to learn the MySQL of the simple operation of adding and removing, recently listened to the MU class network "and MySQL 0 distance contact" https://www.imooc.com/learn/122, finally talked about the MySQL storage engine, has not touched before, here to tidy up. There's something to add to the back.

Storage engine for MySQL 1. Introduction

MySQL stores data in Files (memory) in different technologies, known as the storage engine .

Common Storage engines:

-myisam

-innodb

-memory

-csv

-archive

2. Related knowledge (1) Concurrency control:

Ensure data consistency and integrity when multiple connections modify records.

The system uses a lock system to solve the problem when processing concurrent reads and concurrent writes

• Lock:

-Shared lock (read lock): in the same time period, multiple users can read the same resource, the data will not be changed during the reading process;

-Exclusive (write-Lock): Only one user can write to the resource, which blocks other read or write lock operations when a write lock is made

• Lock particle: (The force of the lock, the unit of the lock, the exact locking of the modified data)

Table lock: the least expensive strategy;

Row locks: The most expensive strategy

(2) Transaction: (one of the important characteristics of the database which differs from the file system)

• Transactions are used to ensure database integrity

　　

• Characteristics of the transaction (ACID)

　　

(3) Foreign key: a strategy to ensure data consistency (4) Index: A structure that sorts the values of one or more columns in a data table

　　Provides quick access to specific information in a data table (equivalent to a book's directory)

3. Features of each storage engine

The CSV storage engine does not support indexing

MyISAM: For situations where there are not many transactions

InnoDB: It is suitable for more transaction processing and requires foreign key support.

4. Setting up the storage engine

• Modify the MySQL configuration file
• When you create a data table

CREATE TABLE table_name (

...

) ENGINE = MyISAM;

• Modify a data Table command

ALTER TABLE table_name ENGINE = engine_name;

Mysql> CREATE TABLE TP1 ( -S VARCHAR (10) ) ENGINE = MyISAM; Query OK, 0 rows affected mysql> SHOW CREATE TABLE TP1; +-------+-------------------------------------------------------------------- --------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------- --------------------+ | TP1 | CREATE TABLE ' TP1 ' ( ' s ' varchar () DEFAULT NULL Engine=myisam DEFAULT Charset=utf8 | +-------+-------------------------------------------------------------------- --------------------+ 1 row in set mysql> ALTER TABLE TP1 engine=innodb; query OK, 0 rows affected records:0 duplicates:0 warnings:0 mysql> SHOW CREATE TABLE TP1; +-------+----------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------+ | TP1 | CREATE TABLE ' TP1 ' ( ' s ' varchar DEFAULT NULL ) Engine=innodb DEFAULT Charset=utf8 | +-------+----------------------------------------------------------------------------------------+ 1 row in Set

MySQL Storage engine