MySQL Note series: MySQL architecture

• MySQL logical architecture

       

connection Management and security:

1, MySQL will assign a thread to each client connection, and this connection query will only be executed on this separate thread. The server is responsible for caching threads, so there is no need to create or destroy threads for each newly established connection.

2. When a client connects to a MySQL server, the server authenticates it. Authentication is based on user name, host information, and password, and can also be connected in SSL-based manner. Once the client connection succeeds, the server continues to verify that the client has permission to execute a particular query.

optimization and execution:

1, MySQL will parse, query, and create internal data structures, and then perform various optimizations, including rewrite the query, determine the order of the table query, and select the appropriate index, and so on. The user can influence its decision-making process through a special keyword hint (hint) optimizer. You can also request the optimizer to interpret the various factors of the optimization process so that the user can specify how the server will make the optimization decision, thereby refactoring the query and optimizing the query.

2, for the SELECT statement, before parsing the query, the server will first check the query cache, if you can find the corresponding query, the server no longer perform the query parsing, optimization and execution of the entire process, but directly return the results in the query cache.

• MySQL concurrency control

1, as long as there are multiple queries need to modify the data at the same time, will produce concurrency control problems.

2. mysql can implement concurrency control at two levels: Server layer and Storage engine layer.

MySQL lock management mechanism

MySQL Lock execution flow

Lock particle size

• Table-Level Locks

small overhead, fast lock, no deadlock, locking granularity, the probability of lock conflict is the highest, the least concurrency.

• Row-level Locks

the cost is slightly big, locking is slow. A deadlock will occur. The locking granularity is minimal, the probability of lock collision is the lowest, and the concurrency is the highest.

• Page lock

overhead and lock times are between table and row locks, and deadlock occurs between a table lock and a row lock, with a common degree of concurrency.

Lock mode for table-level locks

• Table shared read lock (table read lock) and table exclusive lock (table Write lock, exclusive lock)
• MyISAM will automatically lock all tables in the design before executing the query statement (select), and automatically locks the design table before performing the update operation (update, delete, insert, etc.). To operate on the MyISAM table, the following conditions can be found:

1, read operation to the MyISAM table (read lock), does not block other processes to the same table read requests, but will block the write request to the same table. Write operations for other processes are performed only when the read lock is released.

2, write operation to the MyISAM table (write lock), will block other processes on the same table read and write operations, only when the write lock is released, the other process will perform read and write operations.

• In short, read locks Block writes, but they do not block reads, and write locks block both read and write .

• MySQL Transaction

acid properties of transactions

• Atomicity (atomicity)

a transaction must be considered an indivisible minimum unit of work, and all operations in the whole transaction either commit successfully or roll back all.

• Consistency (consistency)

the database is always transitioning from one consistent state to another.

• Isolation (Isolation)

in general, changes made by an office are not visible to other transactions until the final submission.

• Persistence (Durability)

once a transaction commits, its modifications are persisted to the database. Even if the database crashes, the modified data is not lost.

• MySQL Time line

3.23 2001

4.0 2003

4.1 2005

5.0 2006

5.1

5.5

5.6

MySQL5.6 some new features

• Supports multi-threaded replication. In fact, the city opens the corresponding independent thread for each database. That is, each library has a separate SQL Thread, if the online business, only one database or most of the pressure on the individual database, multi-threaded concurrent replication feature is meaningless.
• Enable Gtid, no need to know the Binlog and Pos point, needs to know the master IP, port, account and password can, because synchronous replication is automatic, MySQL through the internal mechanism Gtid automatically find point synchronization.
• InnoDB Full Text Search
• InnoDB more accurate collection of statistical information and more accurate execution plans.
• InnoDB undo data is isolated from the system table space as a separate table space, SSD
• InnoDB Redo log File size adjusted to 512G, previously 4G max
• The Time Type field is supported by the granularity of seconds, datatime, and timestamp to

• MySQL development model

• MySQL is divided into Community Edition and Enterprise Edition according to user group
• MySQL is released according to the update as:

GA (General availability): Officially recommended version

RC (Release Candidate): Candidate version, this version closest to the official version of

ALP Ha and Bean: Beta, alpha on-the-inside version, bean for public

Beta

MySQL Note series: MySQL architecture