MySQL history and architecture

Time:2022-5-9

MySQL logical architecture

Connection management and security

Each client connection has a thread
Authentication is based on user name, original host information and password

Optimization and execution

MySQL will parse the query and optimize it
For select, the query cache will be checked first, and the result set will be returned directly if it can be found

concurrency control

Read write lock

The lock is automatically released on commit or rollback

Read lock (shared lock)

Sharing, non blocking, multiple users can read the same resource at the same time

Write lock (exclusive lock)

Ensure that only one user writes and prevent other users from writing or reading data

Lock granularity

Locking, checking and unlocking all consume system resources
LockedObject sizebecomeLock granularity

Lock strategy

Find a balance between lock overhead and data security

Row lock

It supports concurrency to the greatest extent. The MySQL server layer is not implemented and is implemented by the storage engine

Watch lock

Minimum overhead
MySQL server can add table lock according to its own purpose and ignore the lock mechanism of storage engine

affair

A set of atomic SQL statements, either all executed or none executed
ACID
  1. Atomicity:Either all or none
  2. uniformity:The change of state is complete, and there is no case that half of the change is saved
  3. Isolation:Invisible to other transactions before commit
  4. persistence:Data changes are saved forever

Isolation level

Read uncommitted

Even if the transaction has not been committed, the data is visible to other transactions Namely transactionUncommitted data can be read, also known asDirty readingorRead dirty data

Can be submitted for reading

That is, before a transaction is committed, the change is invisible to other transactionsUnreal reading(when one transaction has not committed after reading the data, another transaction changes the data)

Repeatable reading

The problem of unreal reading and dirty reading is solved

Serializable

Force the transaction to execute serially and lock each row of data

deadlock

Deadlock refers to the vicious circle caused by two or more transactions occupying the same resource and requesting to lock the resources occupied by the other party
After a deadlock occurs, it can only be resolved by partially or completely rolling back the transaction
example

Transaction log

benefit

  1. Avoid frequently writing data from memory to disk (log operations can be recorded and written slowly in the background, so the log file is written additionally)
  2. Facilitate recovery after failure

Transactions in MySQL

Automatic submission (autocommit)

Mixed use of multiple storage engines

InnoDB:Support services
MyISAM:Transaction not supported
Table rollback that does not support transactions cannot undo changes

Implicit and explicit locking

Explicit:Automatic locking
Implicit:Active locking

Multi version concurrency control (mvcc)

  • One column holds the creation time and the other holds the deletion time(Time refers to the version number)
  • Every startNew business,The system version number will be incremented andVersion number at the beginning of the transactionIt is used as the version number of the transaction to match the version number of each line queried

MySQL storage engine

  • The definition of the table is stored in.frmIn the file
  • adoptshow table status ‘tableName’View related definition information of the table

InnoDB

It is the default transaction engine of MySQL and the most widely used storage engine. It is designed to handle a large number of short-term transactions
  • Data is stored in a tablespace
  • Mvcc is used to realize concurrency and four isolation levels. It can be read repeatedly by default
  • be based onClustered index (the second index must contain the primary key)establish

MyISAM

shortcoming

  1. Transaction and row level locks are not supported
  2. Unable to recover safely after database crash

characteristic

  1. Lock the whole table instead of rows, but you can still insert new data when reading data(Concurrent insertion)
  2. Delayed update index key

Other engines

CSV

For conversion.csvFiles into tables in the database

Memory

Archive

Third party engine

Choose the right engine

Unless some features that InnoDB does not have are used and there is no alternative, the InnoDB engine should be preferred
It’s best not to mix multiple storage engines!!!

Replace the engine of the table

ALTER TABLE

ALTER Table name Engine = InnoDB

Import and export

Create and query