How MySQL Syntax execution works

Directory

[TOC]

First, the MySQL syntax implementation work principle

The client request is forwarded by the Nginx load Balancer server to Tomcat,tomcat from MySQL, if the requested data is in the MySQL cache, then MySQL will return the data from the cache to the client, if the cache does not have the requested data, Then MySQL will parse the SQL syntax through the parser whether there is a problem (user permissions problem), in the case of SQL syntax is not a problem, the SQL to the optimizer, see whether SQL through the index and so on to query, and then through the storage engine to skim data on the disk.

The detailed process is as follows, for the IE-->TOMCAT-->MYSQL process, the client request after reaching TOMCAT, the journey goes as follows:

① the data in the Tomcat JVM first, and if the corresponding data is fished out, the request is returned directly;

② in the MySQL cache (cache dataset, SQL syntax, table definition, table permissions, fields) query data, if the query, MySQL in the cache query to the data returned to the application cache, will check the user permissions, at this time, verify that the user permissions will not go to the parser; Will come to the parsing and preprocessing links.

③ SQL to the second layer of MySQL to parse the syntax of the SQL statement, which includes the parser and the preprocessor. The parsing here can be divided into hard parsing and soft parsing, in the case of SQL syntax is not a problem, the next step is done.

parser: also known as a parser, SQL statements after the parser parsing, will generate a corresponding resolution number, in the parser, MySQL will use the syntax rules to verify and parse the query, such as whether to use the wrong keyword, the correct order of the use of keywords, Verify that the quotation marks match correctly, and so on.

preprocessor: in preprocessing, the parsing number is further resolved and a new parsing number is generated. For example: Check the existence of the data table and data column, the name and alias of the SQL statement is ambiguous, and then the preprocessor will verify the user permissions (the permission check is generally very fast, unless there are a lot of permission settings on the server).

hard parsing: through hardware monitoring, such as SELECT * from STUDENT WHERE id= ' test001 ', parsing SQL statements and corresponding operation of the user's permissions, for a type of SQL, in the process of the first query, are hard parsing, The query is slow.

Soft Parse: when querying the select * from STUDENT where id= ' test002 ', when the second use of the query STUDENT this table, and again with the ID of this field as the Retrieval field, this SQL is now cached, The SQL is cached as follows: SELECT * from STUDENT WHERE id=$, the second query, there is no need to consume the CPU to parse this SQL syntax, thereby reducing CPU consumption. It is important to note that the MySQL cache requires the entire SQL statement to be hit, while the soft parsing, when MySQL is only variable changes, will take advantage of the existing analytic number and execution plan.

④ after the parse tree has been generated, it comes to the query optimizer layer, in the query optimizer, the analytic number into the execution plan, the general one query statement can be completed by a variety of execution plans, the optimizer's work is from these execution methods to find the best performance execution plan.

full table Scan: SELECT * FROM table_name where ..., a table no matter what kind of data, all the time out of the hard disk, such a way, in the table of large amounts of data, disk IO will be relatively large, resulting in high CPU usage (Monitoring IO events).

Index Scan: primary key index, normal index, uniqueness index, full-text index (Introduction link: https://www.cnblogs.com/qianzf/p/7131760.html)

After ⑤ finds the optimal execution plan, MySQL calls the query execution engine (InnoDB, MyISAM) to skim the data on the disk.

1.1 Cases

Case 1:

There is a table with a table named Table1, fields with fields such as Id,name,age, ID as the primary key, and a second table with the table named Table2, the field of tables and the same data as the Table1, but no primary key, no index, and the following SQL

SELECTFROMWHEREid1;SELECTFROMWHEREid1;SELECTFROM table1;SELECTFROM table2;

The SQL statement above will take all the data from the disk through I/O, regardless of whether the where statement is used or not, but after using the index, the database will be indexed into the cache to skim the data, the query is particularly efficient.

Case 2:

Scene:

There is a table with a table named Table1, fields with fields such as Id,name,age, if first to two SQL are as follows:

SELECTFROMWHEREid1;

SELECTFROMWHEREid2;

The first SQL-like syntax arrives when MySQL finishes parsing the returned results, can the second SQL hit the cache?

Analysis:

When the SQL statement arrives at MySQL in the cache to skim data, this SQL will be hashed, this SQL even if the case is different, syntax, but different variables, will not hit the MySQL cache, at this time, the second SQL will go to the parser, but, MySQL has analyzed the first SQL syntax in the parser and preprocessor, the corresponding parsing plan, and the second SQL and the first SQL syntax is the same, only the variable, so in the MySQL cache, still cache the SQL parsing plan, when the second SQL into the parser, Will be in the MySQL cache for parsing results, if hit, then will go soft parsing route, improve the query performance of SQL statement.

Second, from the principle of MySQL syntax Performance Test 2.1 hybrid scenario testing and the need for long-time stability testing

Due to the caching mechanism and locking mechanism of the database, in the process of mixed line and large concurrency, when adding and deleting the business line parallel, the phenomenon of resource contention will occur, and the mixed scene test can be observed, and if the database cache setting is unreasonable, In the process of mixed scene and long-time stability test, the database cache of the previous query is purged prematurely, resulting in the database frequently parsing SQL statements, increasing the time of system I/O and database query, affecting system performance, in summary, A mixed scenario test for a typical transaction and a long-time stability test are used to verify that the process of resource contention is justified during testing and that the cache space is adequately configured to perform high reliability and high availability testing of the system. Therefore, uniform capitalization is important for SQL statements.

2.2 Database Cache Settings The relationship between size and performance

As described in 2.1, the database cache settings are too small, the database cache cleanup too fast, affecting system performance, in addition, for MySQL master-slave storage disaster cluster deployment system if the database cache configuration is relatively large, in the case of a database server outage, in addition to switching the instantaneous query efficiency is relatively slow, after the establishment of the cache, can guarantee the partial Yes.

Third, database server cache configuration policy

In general, the larger the database cache, the better the performance of the database, so if the physical memory is large enough, you can configure as much memory as possible to the database. If the physical amount of memory 8G, you can configure 65% to the database, if the physical memory 4G, you can configure 1/3 to the database, for 2G of physical memory, see the situation to configure. In general, the problem of SQL syntax is excluded, and then the memory configuration of the database is considered.

Iv. deployment methods of each database cluster

Oracle

Bring your own cluster deployment method (RAC).

MySQL

MySQL itself does not support cluster deployment, but through third-party tools, the master-slave configuration can be done to complete the cluster deployment.

PostgreSQL

You can use the PostgreSQL-brought tool (Pgpool) to complete your cluster deployment.

How MySQL Syntax execution works