Introduction to mysql logical architecture and storage engine _ MySQL

Introduction to mysql logical architecture and storage engine MySQL logical architecture:

Concurrency control: implemented by locks

Read locks: also known as shared locks. read locks do not block each other. After A locks the table, A, B, c, and d can read the table but cannot write it.

Write lock: Also called exclusive lock. write locks are blocked. After an exclusive lock is applied to A, other threads cannot read or write the table.

Lock granularity:

Table lock: locks a table with a low concurrency granularity. Represents the storage engine MyISAM

Row lock: locks a row of data with a large concurrency granularity and good performance in concurrent operation tables. Represents the storage engine InnoDB. The lock granularity is small, and the cost of the lock is also large.

If a read lock is applied to a table, other threads cannot perform write operations on the table. if a row lock is applied, the thread only blocks read and write of this row of data, data of other rows in the table can be read and written by other threads to improve the concurrency performance.

Transaction: it is equivalent to packing N statements into one statement. If all N statements in this package are successfully executed, if one of the statements is not successfully executed, the transaction rolls back to the original state. if the transaction fails to be executed, the transaction is not written to the hard disk.

ACID (atomicityconsistency isolation durability) atomicity, consistency, isolation, and durability. Is a feature of the transaction storage engine.

Atomicity: a transaction is equivalent to a package of N statements. it can be executed successfully or fails. for a transaction, it cannot be successfully executed as part of a transaction. this is atomicity.

Consistency: changes from a consistent state to another consistent state. Either the status is successfully converted to the successful status, or the failure is rolled back to the original status.

Isolation: Transaction A cannot see transaction B.

Persistence: if the transaction is successfully executed, the data is written to the hard disk. this is called persistence.

Storage Engine:

· The database exists in the directory where the tables in the database exist.

· View the storage engine: show table status like 'Table _ name'/G;

MyISAM storage engine

· Store. MYD data in the table in three files. MYI storage index. frm storage table definition.

· Table lock supported

· When the myisam table is large, it is more prone to faults. you need to manually repair the table, and the repair time will be very long. Repair table step: check the table and repair the table.

· Compressing tables

InnoDB

· Store the ibdata1 data file in two files and the. frm table definition file in the Database Directory.

· Transaction support

· Supports row locks

· Foreign key constraints

· Based on clustering indexes, clustering indexes have high query performance on primary keys.

· InnoDB supports hot backup, while none of other storage engines support hot backup.

Select an appropriate storage engine:

· Mysql5.5 the default storage engine is InnoDB. The simplest answer is that if you don't know which Storage Engine to choose, you can use InnoDB because it supports transactions and has good performance. Some people say that MyISAM performance is faster than InnoDB, which is not necessarily the case. many times InnoDB makes MyISAM difficult.

Selection basis:

1. Transactions: InnoDB supports transactions. MyISAM is not a transactional storage engine.

2. InnoDB supports online hot backup.

3. crash recovery. InnoDB is supported by the transaction storage engine. it is very slow to fix a large table after MyISAM crashes.

4. special features, such as clustered indexes, foreign key constraints, and row locks.

If you do not have high requirements on data security and are mainly reading data and the database table is not very large, use MyISAM.

If the data volume is very large, InnoDB should be used. when reading and writing are very frequent, row-level locks provide better performance.