High performance MySQL sixth Chapter query performance optimization summary (top) query execution process

6 Query Performance Optimization

6.1 Why queries become slow

This illustrates the query execution cycle, from the client to the server side, the server-side resolution, the optimizer generates execution plans, executes (can be subdivided, the general process can be viewed through show profile), and returns the client results from the server side.

And the execution part as the most important link, need to do more things, and inappropriate query often let the execution process do unnecessary operation, or can not use the better underlying data structure, thus time longer.

6.2 Slow Query Basics: Optimizing data access

The amount of access to data that exceeds what is actually required is one reason for slow queries. There are generally two reasons for this situation

1. The application requires more than needed or duplicated data to the MySQL service, such as SELECT *.

More than needed data was accessed during the execution of the 2.mysql server.

Let's start with the first reason.

6.2.1 request more than needed data to the database

1. More than the required line was requested, if no limit was used, 100 rows of data were requested, but only 10 rows were used.

2. Using SELECT * Returns the unwanted column data, especially if the multi-table association query is more prominent and should only return the columns we need.

3. If the client uses a cache and the portion of the cache is hit by the next data demand, then there is no need to send data requests to the server.

6.2.2mysql scanned for extra records.

three metrics in the slow query log that measure query overhead: 1. Response time, 2. Number of rows scanned, 3. Number of rows returned

1. Response Time

Response time includes service time and queue time.

response times are different in different server environments and in application environments. The article mentions the use of the ' fast upper Limit Method ' (Chinese translation): Calculates the required sequential IO and random io times with query execution plan, and then combines the time of the next IO in the current environment. You can always get a ' reference value ' when you make a final addition.     (as can be seen, such a method is not strong, because not every query we can estimate the number of IO relatively accurate, different server environment, even the unified server at different times, IO efficiency will be different.) But here we can see that IO is a relatively time-consuming step in the execution process.

2. Number of rows scanned and rows returned

The number of rows scanned and the number of rows returned alone does not reflect the efficiency of any query, only the ratio of the two can explain the ability of the query to filter the data.

3. Scan number of rows and type of access

The Type column in explain lists the types of access: Full table sweep, index scan, range scan, single value query

6.3 How to refactor queries

6.3.1 a complex query or a number of simple queries

because the MySQL network protocol is ' half duplex ', the cost of disconnection is not very large, so many times the decomposition of the query is An efficient way to optimize SQL.

6.3.2 Slicing Query

split query refers to the large query to take a divide-and-conquer strategy, divided by time, but the server-side pressure. But the SQL that executes the fragment is the same, except that the data returned by Do_query () and the limit implement batch processing of the data entry.

6.3.3 Decomposition Association query

break up the associated query into multiple SQL steps.

This is done by reducing the granularity of the transaction, easing the contention of the lock, and making the cached data more modular and easy to use for caching.

6.4 Query Execution steps

Client Request Data - -server side query cache , hit return, Miss -->mysql parsing SQL , precompiling, generating execution plans - invoking the Database engine API Execution sql--> return results to client

6.4.1mysql Communication Protocol

' Half duplex '. At the same time, only one party sends the data, only after receiving the data sent by the other party, it can send a response to the other party.

There are two ways that the client gets the data, in C #, a dateset, a one-timereturn of data, a snapshot of the data on the client (cache), a dateread is to keep the client-server connection, and the client queries the data at any time. -- different languages provide different ways to call.

Query status

refers to the execution state of the query's process: It is important to note that sort result sorts the result set;Sanding Date indicates that the data is returned from the generated result set or to the client.

6.4.2 Query Cache

Is the structure of the second-step query in the query step. The seventh chapter is introduced.

6.4.3 Query Optimization

That is the third step in the query step

1. language parser and preprocessor

The parser checks the syntax, and the preprocessor checks the semantics and checks to see if the user has permissions.

2. query optimizer

The optimizer evaluates the execution plan of the preprocessing tree generated by the preprocessor, and calculates the cost of the various execution plans (with previous knowledge we know that the cost here often refers to the number of data pages that the query needs to access)

It can be understood that the optimizer's cost estimate is not entirely accurate, but for most sql,io is the bottleneck of its execution.

Note that the optimizer does not consider caching (there is no need to hit the second step and return to the client.)

optimization with static optimization and dynamic optimization of the distinction, taking into account the seventh chapter of the query cache and stored procedures described, I am considering whether MySQL to save the execution plan? How do I save the SQL execution plan? Static dynamic optimization When the two choose one or coexist?

The book lists the queries that the optimizer can optimize, which I don't list.

1. Statistics for Data indexes

The Myisam engine holds count, and InnoDB can only provide estimates (InnoDB 's data is entangled with the index, Insert Delete changes may also cause page splitting, page merging and other issues, so save a count of statistics such as maintenance is not easy)

4. execution of associated queries

Mysql 's associated query can be seen as a double-loop or multi-loop, with only two tables associated with the query, the outer loop of the table called the exterior, the inner loop of the table called inside the table. You can know that the inner loop has more queries than the outside, so the inner table needs a proper index.

5. Associated query optimizer

but inside looks are often not what we can decide,and the MySQL Optimizer will work, and the optimizer will create a depth-first tree, with different combinations to get the least expensive execution plan. However, when the associated table n is too large, the number of stars in the execution plan tree rises exponentially andn exceeds optimizer_search_depth Instead of using exhaustive methods, use a different search method to get the optimal execution plan.

in the previous paragraph , MySQL performs a nested loop on the associated query, and the execution plan behaves as a depth-first tree.

1. Sorting optimization

The third chapter describes the index ordering, which is described here in filesort.

Filesort file sorting,filesort does not always need to use the disk, when the amount of data is small can be in memory.

Whether the amount of data is greater than the file buffer is a watershed. When using disk sorting is equivalent to sorting out.

Mysql filesort Sorting Strategy

read Data two times ( old version ): Sorting is done using only the row pointer and the sort field so that the result of the order can be read to all data by a row pointer, because it is random io , io low efficiency.

Single read data (new version): reads the required columns first and then sorts them according to the given column.

two each has its merits and demerits, this discussion we put in Chapter 8

The 5.6 version has been improved here , and when limit is used,MySQL no longer sorts all the results, but only sorts the required data.

6.4.4 query Execution engine

The Mysql execution plan is a data structure that executes when the execution plan needs to call the storage engine to be responsive to the 'handler API' implementation. If it is an attribute common to all stored procedures, it is typically implemented at the server level, such as date functions, views, triggers.

6.4.5 return Data

Mysql executes the SQL procedure and starts to return data when the first result is generated, so that the server does not need to save a large number of results. (Looking back, you can see that the Union is going to need to create a new temporary table to hold the data, before the Union completes to return the data)

High performance MySQL chapter sixth query performance optimization Summary (top) query execution process