MySQL Basic beginner learning "14" storage engine

To view the create command for a data table:

"Storage Engine": MySQL can store data in different odd numbers in the file (memory), this technology becomes the storage engine

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

In the relational database, the storage of data is implemented in the form of a table. So the storage engine can also be called a table type

"MySQL-supported storage Engine":

MyISAM,

InnoDB,

Memory,

Csv

Archive

"Concurrent Processing":

Concurrency control: a technology that guarantees data consistency and integrity when multiple connections operate on records.

For example, two users logged in at the same time, one to delete a row of records, and another user happens to read this row of records,

The result may be that the user who is reading the message exits with an error and may get a message that does not match the actual record.

The way to solve this classic problem is to use concurrency control: when dealing with concurrent reads or concurrent writes, the system uses a lock system to solve this problem.

This locking system consists of two types of "locks":

"Shared lock (Read lock)": During the same time period, multiple users can read the same resource, the data will not be changed in the reading process (do not interfere with each other);

Exclusive lock (Write lock): Only one user can write to a resource at any time, and other read or write lock operations (for security policy considerations) are blocked when a write lock is made.

"Lock Grain": the unit when locked. We just need to lock the modified data exactly, without having to lock all the resources. Locking only the most right, not the biggest.

Locking increases the overhead of the system: we seek a balance between lock overhead and data security through lock policy

"Table lock": is a locking policy with minimal overhead. When a user operates on a data table, the user obtains the write-lock permission on the table, and the write lock prevents other users from reading and writing. This table can only have one lock.

"Row lock": is one of the most expensive locking policies, and is the case that supports maximum concurrent operations processing. How many records in this table may have a lock on every record

　　

"Transaction": "Transaction" is one of the important characteristics of the database that distinguishes the file system, and is used to guarantee the integrity of the database.

For example, if a user wants to transfer 200 dollars to someone via online banking, the operation is as follows:

1. Lose 200 yuan from the current account (account balance is greater than or equal to 200 yuan);

2. Add $200 to the offset account.

Two processes should appear as a whole, regardless of which process has a problem, should revert to the original state of the data.

"Characteristics of the transaction--acid":

1. atomicity (atomicity)

2. Consistency (consistency)

3. Isolation (Isolation)

4. Persistence (Durability)

　　　　　　

"Foreign Key and Index": "Foreign Key" is a policy to ensure data consistency (see http://www.cnblogs.com/jade-91/p/8672669.html)

An index is a structure that sorts the values of one or more columns in a data table, and using an index provides quick access to specific information in a data table and is a way to quickly locate a record (the directory of the analogy book).

Can be divided into ordinary index, unique index, full-text index, btree index, hash index ...

　　　　　　　　　

"Individual storage Engine features"

Characteristics	MyISAM	InnoDB	Memory	Archive
Storage limits	256TB	64TB	Have (determined by memory size)	No
Transaction security	-	Support	-	-
Support Index	Support	Support	Support
Lock particles	Table lock	Row lock	Table lock	Row lock
Data compression	Support	-	-	Support
Support for foreign keys	-	Support	-	-

In addition, CSV is a comma-delimited storage engine that creates a. csv file (an ordinary text file) for each table in a subdirectory of the database, where each row of data occupies a line of text and does not support indexing

Blackhole: Black hole engine, write data will disappear, generally used to do data replication relay.

The most commonly used storage engines are the two storage engines, MyISAM and InnoDB.

Among them, MyISAM is applicable to the situation where the transaction is not handled much;

The InnoDB is suitable for transactions that require foreign key support for more processing.

Daniel can also extend the data engine.

"Setting up the storage Engine"

"How to modify the storage Engine":

Method one: By modifying the MySQL configuration file implementation: Default-storage-engine = Engine_name

Method Two: By creating a data Table command implementation:

CREATE TABLE table_name (

...

...

) ENGINE = Engine_name;

　　　　　　　　　　　　

　　　　　　　　　　　　

Method Three: By modifying the data Table command to implement

ALTER TABLE table_name ENGINE [=] engine_name;

　　　　　　　　　　　　

　　　　　　　　　　　　　　

MySQL Basic beginner learning "14" storage engine