MySQL Architecture tiering

1 service Layer

Connection thread Handling

2 Core Layer

Query Cache Analyzer

Optimizer

3 Storage Engine Layer

1 service Layer

Connection processing, authorization authentication, security, etc. for client requests

2 Core Layer

Query parsing, analysis optimization, caching, stored procedures, views

3 Storage Engine Layer

Access and extract data and deal with things

1 service Layer

Main thread processing, single process multithreading

2 Core Layer

Query cache is judged to have returned results

No

Parse query optimization execution query return results

3 Storage Engine Layer

Pluggable Storage Engine

MyISAM InnoDB Cluster memory

InnoDB file system: Ext4 NTFS wait

Cluster files and logs index binary slow

MySQL things processing

A thing is a logical processing unit consisting of a set of SQL statements

Atomicity: Atomic operating Unit,

Consistency:

Isolation:

Persistence: After completion, modifications to the data are permanent

MySQL concurrency control

1 Many, many requests come,

Serial execution

Cross Concurrent execution

Concurrent execution simultaneously

concurrency control

Correct scheduling of concurrent operations

Ensure the isolation of things

Keep your data consistent

Serial execution, at the same time only one thing running, inefficient, unable to make full use of resources

Cross Concurrent execution: Multi-object cross-running, single-processor concurrency

Concurrent execution: Used in a multi-processing system where each processor handles one thing

Concurrent execution issues,

Same-read-write problem, resulting in disruptive isolation and inconsistent data

Inconsistencies in data

Missing modifications

Non-REPEATABLE READ

Read "Dirty" data

MySQL Architecture tiering