Mysql schematic diagram

1. connectors

Interact with SQL statements in other programming languages, such as PHP, Java, and so on.

2. Management Serveices & Utilities

System management and control tools

3. Connection Pool

Manage buffering user connections, thread processing, and other caching requirements

4. SQL Interface

Accepts the user’s SQL command and returns the result that the user needs to query. For example, select from calls SQL Interface

5. Parser

SQL commands passed to the parser are validated and parsed by the parser.

Main functions: a. Decomposes SQL statements into data structures and passes the structure to the subsequent steps. The subsequent SQL statements are transmitted and processed based on this structure

B. If an error is encountered in the breakdown, the SQL statement is invalid and the statement will not continue

6. Optimizer

The SQL statement uses the query optimizer to optimize the query before it is queried (multiple execution plans are generated, and the database eventually chooses the optimal solution to execute and returns the results as soon as possible). It uses the select-project-join strategy to query.

Select uid,name from user where gender = 1;

The SELECT query selects based on the WHERE statement, rather than having all the tables queried first and then gender filtering

The select query projects attributes based on UID and name, rather than fetching all attributes and filtering them later

Join the two query criteria to generate the final query result.

7. Cache and Buffer (query Cache)

If the query cache has a matching query result, the query statement can directly fetch data from the query cache.

This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache and so on

8. Storage Engine

The storage engine is the specific subsystem of MySql that deals with files. It is also one of the most distinctive aspects of Mysql.

Mysql’s storage engine is plug-in. It customizes a file access mechanism (called a storage engine) based on an abstract interface to the file access layer provided by MySql AB

SQL statement execution process

Databases are usually not used directly, but instead are called by other programming languages through SQL statements, which are processed by mysql and return execution results. How does Mysql handle SQL statements

First, requests from the program will interact with it through mysql Connectors. Once the requests are received, they will be temporarily stored in the Connection Pool and managed by the Management Serveices & Utilities. When the request goes from the wait queue to the processing queue, the manager throws the request to the SQL Interface. After receiving the request, the SQL interface hashes the request and compares it with the result in the cache. If there is a complete match, the result is directly returned through the cache. Otherwise, you need to go through the whole process:

(1) It is thrown by the SQL interface to the following Parser, which will judge whether the SQL statement is correct or not and convert it into data structure if it is correct.

(2) After the interpreter is finished, it comes to the later Optimizer, which will produce a variety of execution plans. Finally, the database will choose the optimal plan to execute, and return the results as soon as possible.

(3) Determine the optimal execution plan, THE SQL statement at this time can be submitted to the storage Engine (Engine) processing, storage Engine will be to the back-end storage device to obtain the corresponding data, and the original way back to the program.

Pay attention to the point

(1) How to cache query data

When the storage engine processes the data and returns it to the program, it also keeps a copy of the data in the cache so that it can process the same request more quickly the next time. In this case, mysql hashes the query statements and execution results and stores them in the cache for the next query.

(2) Difference between buffer and cache

A buffer is a write cache, and a cache is a read cache.

(3) How to determine whether the required data has been cached in the cache

There might be an error, when dealing with SQL statements in order to judge whether the cached query results, the whole process will go again, and then compared with the needs of execution results achieved, see if it hit, and said, now that did no matter in the cache to the query cache content, all went again to the entire process, the advantage of the cache?

After the first query, mysql hashes the query statement and the query result in the cache. When the SQL query arrives, it hashes the two hash values against each other. If the two hash values are the same, they match and the query result is returned from the cache. Otherwise, you need to go through the whole process.

When multiple operations in a database need to modify the same data, dirty data reads will inevitably occur. In this case, the database is required to have good concurrency control, all of which are implemented by the server and storage engine in MySQL.

The most effective way to solve the concurrency problem is to introduce the lock mechanism, which can be divided into shared lock and exclusive lock. When a SELECT statement is executed, a read lock can be imposed, which allows other SELECT operations to take place because the data is not changed during the process. This improves database performance. When data needs to be updated, it is necessary to impose a write lock, not to allow other operations, so as not to generate dirty and phantom data. Locks also have granularity. Table locks and row locks are used to lock rows and tables during data operations, respectively. These characteristics vary from storage engine to storage engine.

Most of MySQL’s transactional storage engines are not simply row-level locking, and they generally implement multi-version concurrency control (MVCC) for performance reasons. This approach is also used by major relational databases such as Oracle. It does this by keeping a snapshot of the data at a point in time, which ensures that each transaction sees the data consistently. For detailed implementation principles, see High-performance MySQL edition 3.

4. The transaction

1. A transaction is simply a set of atomic SQL statements. This set of statements can be thought of as a unit of work, executed either at all or at all. In MySQL, you can use the following command to manipulate transactions:

start transaction;

The select…

The update…

Insert…

commit;

Note: Autocommit is enabled in MySQL by default:

2. Transactions have properties of ACID:

Atomicity: All operations in a transaction are either committed successfully or rolled back on failure.

Consistency: A database always transitions from one consistent state to another.

Isolation: Changes made by one transaction are not visible to other transactions until committed.

Durability: Once a transaction is committed, their modifications are persisted in the database forever.

3. Transaction isolation levels: There are four isolation levels defined in the SQL standard:

READ UNCOMMITTED: Changes in a transaction are visible to other transactions even if they are not committed

READ COMMITTED: Changes made after a transaction is COMMITTED can only be seen by another transaction, which can result in different results between two queries in one transaction.

REPEATABLE READ: Only the current transaction commit can see the result of another transaction’s modification. Resolved the problem of two queries having different results in a transaction.

SERIALIZABLE: Only after one transaction is committed can another transaction be executed.

MySQL > select * from isolation level;

5. Deadlock: Two or more transactions occupy the same resource and request to lock the resource occupied by the other, resulting in a vicious cycle. Some of MySQL’s storage engines can detect deadlocked cyclic dependencies and generate errors accordingly. The InnoDB engine’s solution to deadlocks is to roll back the transactions that hold the least exclusive locks.

5.MySQL storage engine and application scheme

1.MySQL uses a plug-in storage engine architecture. You can set different storage engines for different tables based on different requirements. You can run the following command to view the status of tables in the database. For example, the user table is displayed as follows:

  • Name: Displays the table Name
  • Engine: displays the storage Engine. The storage Engine in this table is MyISAM
  • Row_format: displays row formats. For MyISAM, there are Dynamic, Fixed, and Compressed formats. Non-specific means that the table has a variable data type, the table has a fixed data type, and the table is a compressed table environment.
  • Rows: Displays the number of Rows in the table
  • Avg_row_length: average length (bytes)
  • Data_length: Data length (bytes)
  • Max_data_length: Maximum data length (bytes)
  • Data_free: Allocated but unused space, including space made free by deleting data
  • Auto_increment: Automatically increments the value of the next inserted row
  • Create_time: time when the table is created
  • Update_time: indicates the time when the table data was last modified
  • Collation: The default character set and Collation rules for a table
  • Checksum: If this parameter is enabled, it indicates the real-time Checksum of the entire table
  • Create_options: Create some other options for the presentation
  • Comment: Additional Comment information, which varies depending on the storage engine.

2. Storage Engine Introduction:

InnoDB engine:

1. Store data in a table space, which consists of a series of data files and is managed by InnoDB;

2. Data and index of each table can be stored in a separate file (innodb_file_per_table);

3. Support transactions, using MVCC to control concurrency, and achieve the standard 4 transaction isolation levels, support foreign keys;

4. The index is based on the cluster index and has high performance for primary key query;

5. Platform independence of data files, supporting data migration in different architecture platforms;

6. There are tools to support true hot spare. Such as XtraBackup;

7. Internal self-optimization, such as predictable prefetch and automatic creation of hash indexes in memory.

MyISAM engine:

MySQL5.1 does not support transaction and row-level locking by default.

2. Provides a large number of features such as full-text indexing, spatial functions, compression, delayed update, etc.;

3. After a database failure, security recovery is poor.

4. MyISAM is still very useful for read-only data that can tolerate failure recovery;

5. The log server scenario is also suitable for inserting and reading data.

6. All data and index contents are stored in two files instead of one file.

7.MyISAM locks the entire table instead of rows, so it is not suitable for scenarios where there are many write operations;

8. Index cache Is supported. Data cache is not supported.

Archive engine:

1. Only insert and SELECT operations are supported.

2. Cache all written data and compress it for storage. Row-level locking is supported but transaction is not supported.

3. Suitable for high-speed insertion and data compression, reducing I/O operations, suitable for logging and archive servers.

Blackhole engine:

1. No storage mechanism is implemented. Inserted data is discarded but binary logs are stored.

2. Will be used in some special needs of replication architecture environment.

CSV engine:

1. You can open the data stored in CSV files, export the stored data, and open it in Excel;

2. It can be used as a mechanism for data exchange and is also frequently used.

The Memory engine:

1. Cache data in memory without consuming I/O.

2. Data is quickly stored but is not retained. It is generally used to store temporary tables.

The Federated engine:

A storage engine that can access data on a remote server. Ability to establish a connection to a remote server.

Mrg_MyISAM engine:

Merge multiple MYISAM tables into one. It does not store data itself; data is stored in the middle of MyISAM tables.

NDB cluster engine:

MySQL Cluster dedicated.

3. Third-party storage engine

1. The OLTP class:

XtraDB: An improved version of InnoDB.

PBXT: Similar to InnoDB, but with engine-level replication and foreign key constraints, and appropriate support for SSD storage.

TokuDB(open source) : Supports fractal tree index structure, supports mass data analysis.

2. Column storage engine: MySQL is row oriented storage by default

Infobright: Supports tens of terabytes of data, designed for data analysis and data warehousing. Data is highly compressed.

InfiniDB: Can do distributed queries across a group of clusters, available commercially but without a typical use case.

3. Community Storage Engine:

Aria: Solves MyISAM crash security recovery and enables data caching.

Groona: Full-text indexing engine.

QQGraph: Developed by Open Query to support graph manipulation, such as finding the shortest distance between two points.

SphinxSE: This engine provides the SQL interface to the Sphinx full-text index search server.

Spider: Supports Sharding and can implement parallel queries based on sharding.

VPForMySQL: vertical partitioning is supported.

4. Reference factors for selecting storage engines

1. Whether there are transaction requirements

InnoDB or XtraDB is the best option if transaction support is required. If MyISAM is the main select and insert operation, a logging application is generally used.

2. Backup operation requirements

This factor can be ignored if the server can be shut down for backup, and InnoDB engine is a good choice for online hot backup.

3. Fault recovery requirements

InnoDB is recommended for scenarios with high recovery requirements because of the high probability of MyISAM data corruption and slow recovery.

4. Performance requirements

Some business requirements can only be met by specific storage engines, such as geospatial indexing, which is supported only by the MyISAM engine. So there are compromises to be made in the context of application architecture requirements, and of course the InnoDB engine is recommended by default for all aspects of comparison.

5. Table engine conversion method

1. Modify it directly

2. Back up changes

Use the mysqlDump backup tool to export the data and modify the storage engine options in the CREATE TABLE statement. Note Change the table name at the same time.

3. Create an insert

Source: blog.csdn.net/wypersist/a…