MySQL Query performance optimization one

MySQL performance can only be achieved when query optimization, index optimization, and library table structure optimization go hand in hand.

Before trying to write a quick query, it's important to be clear that the real importance is response time.

In general, the life cycle of a query can be roughly in order: from the client, to the server, then to the server to parse, generate execution plans, execute, and return results to the client.

"Execution" can be considered as the most important phase of the entire life cycle, which includes a large number of calls to retrieve data to the storage engine, as well as post-invocation data processing, including sorting, grouping, etc.

For the entire life cycle of a query, the columns above are not complete. Here we just want to say: to understand the life cycle of the query, clear query time consumption is of great significance for optimizing the query.

Slow Query Basics: Optimizing data access

1. Do you request unwanted data to the database?

2.mysql whether to scan for additional records

Whether the query scanned too much data. The simplest measure of query overhead is the following three indicators:

Response time.

The number of rows scanned.

The number of rows returned.

No metric can perfectly measure the cost of a query, but they roughly reflect how much data MySQL needs to execute queries internally, and can calculate when the query runs.

These three metrics are recorded in a slow log of MySQL, so checking for slow logging is a good way to find queries that scan too many rows.

　　　　Response Time:

　　　　　　Is the sum of two parts: service time and queue time.

Service time is how long it really takes the database to process this query.

Queuing time is the time that the server is not actually executing a query because it waits for some resources. ---may be waiting for the IO operation to complete, or it may be waiting for a row lock, and so on.

　　　　Number of rows scanned and number of rows returned:

　　　　　　When you parse a query, it is helpful to view the number of rows scanned by the query. This is to some extent an indication that the query is inefficient in finding the data it needs.

　　　　Number of rows scanned and type of access:

　　　　　　The type column in the Expain statement reflects the access types. There are many types of access,

From full table scan (all) to

Index Scan to

Range Scan () to

Unique index query to

Constant references, and so on. These are listed here, the speed from slow to fast, the number of rows scanned is also small to large.

If you find that a query needs to scan a large amount of data but only returns a few rows, you can usually try the following techniques to optimize it:

Overwrite the scan with an index.

Change the structure of the library table. For example, use a separate summary table.

Rewrite this complex query. Allows the MySQL optimizer to execute this query in a more optimized manner.

How to refactor the query:

　　1. A complex query or multiple simple queries

　　　　One important question to consider when designing queries is whether you need to divide a complex query into multiple simple queries.

　2. Slicing queries

Sometimes for a big query we need to "divide and conquer", the big query cut into small queries, each query function exactly the same, only a small part, each time

Returns only a small subset of the query results.

　　3. Decomposing the associated query

SELECT * FROM Tag

Join tag_post on tag_post.tag_id = tag.id

Join post on tag_post.post_id = Post.id

where Tag.tag = ' MySQL '

Can be broken down into these queries instead:

> select * from tag where tag = ' MySQL '

> select * from tag_post where tag_id = 1234

> select * from Post where post_id in (123, 456, 567, 9098, 8904)　　　　

Advantage:

Make the cache more efficient.

After the query is decomposed, executing a single query can reduce the competition for locks.

The application layer makes it easier to split the database, making it easier to perform high-performance and scalable.

The efficiency of the query itself may also be improved.

Queries that can reduce redundant records,

Further, this is equivalent to implementing a hash association in your application instead of using the nested Loop Association of MySQL.

Basis for query execution:

　　When you want MySQL to run queries with higher performance, the best way is for farmers to know how MySQL is optimized and queried.

Once this is understood, much of the query optimization effort is implemented by following some principles that allow the optimizer to operate in a reasonable manner as expected.

　　

1. The client sends a query to the server

2. The server checks the query cache first, and if the cache is hit, returns the results stored in the cache immediately, otherwise entering the next stage.

3. The server performs SQL parsing, preprocessing, and then generates the corresponding execution plan by the optimizer.

4.mysql invokes the API of the storage engine to execute the query, based on the execution plan generated by the optimizer.

5. Return the results to the client.

MySQL Query performance optimization one