Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to optimize sentences in MySQL Cost. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Xiaowen is a new bird in the database, but he has a quick temper? Today, a programmer asked him if there were any bad SQL sentences in his new business. Xiaowen said angrily, "what do you think? you wrote it yourself, ask me?"

Programmers don't respond angrily? I asked the wrong person, I guess you will not read COST, optimization to ask you is blind.

How on earth can you deal with the programmer's ASKING quickly and respond to him with superior skill? Follow me.

Almost every database evaluates the cost of the execution plan of the execution statement before executing the statement, and uses cost to determine which prepare plan is better, faster and stronger.

Drew a rough picture.

First of all, the knowledge of the database, 1 the cost of operation, 2 whether there is an alternative 3 to find the "lowest" implementation plan among many schemes.

The above query will contain several points in the order of 1 JOIN, 2 access methods 3 subqueries

So in the end, how to judge the order of which JOIN is good, the method seconds, and how to check the subquery? MYSQL is based on the two quantities of CPU I COST O to calculate and determine which COST is the lowest.

So the next question is, how on earth do I know what the cost of the table I want to access is, whether there is an index, and how to set it in the index?

Then it leads to the state of the METADATA database table, which contains information about row, index's SIZE, index, whether it is the only index, table size, range evaluation, cardinality, and so on. (no statistics today.)

STOP STOP, you Barabara said so much, how does it help me? Xiaowen is so angry, can you tell me how to optimize that damn sentence?

OK, let's turn on the OPTIMIZER_TRACE function first (Xiaowen: I don't know

MYSQL OPTIMIZER_TRACE), unknown follow me.

Let's execute a statement first.

Select *

From employees as em

Left join titles as ti on em.emp_no = ti.emp_no

Left join (select emp_no from salaries where salary > 40000) as sa on em.emp_no = sa.emp_no limit 1

Then after we execute this statement with the OPTIMIZER_TRACE function turned on, let's see what OPTIMIZER_TRACE can give me.

First of all, through optimizer_trace, we can see that the execution of a statement is divided into three steps.

1 preparation 2 optimization 3 execution

Let's take a look at what MYSQL did in the prepare phase based on the above statement and the feedback from OPTIMIZER_TRACE.

1 formatted statements, due to the non-standard writing of statements, statements are generally converted in the first step, such as fields, conditions without database prefixes, and so on.

Finally, our above statement is formatted like the following picture.

OK, after basically preparing the statement to be optimized, we need the optimizer to start optimizing. Generally speaking, the problem of optimization is in order. What is the order of how to optimize?

In this optimized statement, there are 8 steps to optimize.

1 condition_processing first determines whether there is a where condition. Since there is no where condition in the outermost layer of the statement, what is shown here is null.

2 below is to start sorting out the relevant table relationships table_dependencies

Whether the row_may_be_null: column is allowed to be NULL does not mean whether the column properties in the table are allowed to be NULL. Here, we are talking about whether the column after the JOIN operation is NULL, which is a collection concept, so if you can write JOIN or INNER JOIN, don't carelessly write LEFT JOIN.

3 the following is to consider the possible association between keys

4 then the number of rows scanned by the full table of each table participating in the operation is given and the cost value is given. If the number of rows of the table here is not accurate, or whether the cost value can be adjusted, the answer is that the rows may not be accurate, and the cost value can be adjusted (PG can be adjusted, and it can be easily adjusted).

Some people may ask why, it's all full table scans, just look at the execution plan, there is no index.

Xiaowen chimed in, "Oh, there is no index. I will just add the index."

Don't worry: optimized statements don't do this. If it's as simple as adding an index, no one will do it. To optimize the statement, we should first understand the business logic (the one who can start with the logic is the master), then see whether the writing of the statement is standard and meet the requirements of the current database optimization engine, and finally add the index. If you find in the first step that this statement does not conform to the business logic at all, then you do not even need to CANCEL this statement directly.

So what do we do now? Xiaowen asked

Let's finish watching the current optimizer_trace and continue, okay?

Finally, let's take a look at the generated execution plan. MYSQL will compare several execution plans. Below, you can see that a considered_execution_plan and rest_of_plan appear.

The first is the execution plan to be used, while the latter is a number of execution plans compared to the failed execution plans. You can do it yourself and compare it with each other.

Shall we add an index? Xiaowen said, "Don't worry. Let's take a look at how this sentence is written."

The final result of the following two sentences is the same, which you think is better.

Select *

From (select emp_no from salaries where salary > 40000) as sa

Left join titles as ti on ti.emp_no = sa.emp_no

Left join employees as em on ti.emp_no = em.emp_no limit 1

Select *

From employees as em

Left join titles as ti on em.emp_no = ti.emp_no

Left join (select emp_no from salaries where salary > 40000) as sa on em.emp_no = sa.emp_no limit 1

What else is the logic of this statement? If the sentence means, find the list of employees who earn more than 40000 yuan. Do you think it's right for him to write that?

Yes, I'll ask, Xiaowen replied.

The final result is what Xiaowen replied sheepishly, showing the developer that the sentence they said was wrong, and they rewrote it, which roughly means to find a list of employees whose wages once exceeded 40000. (if it is a payroll, there can be no weight in it, and the list is repeated.)

The sentence they gave later was

Select distinct * from (

Select em.emp_no,em.first_name,em.last_name

From employees as em

Inner join (select emp_no from salaries where salary > 40000) as sa on em.emp_no = sa.emp_no) as a

NO NO NO, Xiaowen, let's see what happens if we rewrite their sentences like this. It's less than 0.9 seconds faster.

Select em.emp_no,em.first_name,em.last_name

From (select distinct emp_no from salaries where salary > 40000) as sa

Inner join employees as em on em.emp_no = sa.emp_no

Xiaowen was beaming with joy and asked why it would be fast to write in this way. The old bird glanced at Xiaowen and said, "keep it secret."

In fact, the optimization of SQL statements can take such a step.

1 clarify the business logic of the statement, can modify the business logic, or optimize it, do not push it to the level of the statement

2 at the statement level, it is necessary to understand the general working principle of the optimization engine of the database, which will not conform to the statement of the optimizer and modify it.

3 according to the final statement, add the appropriate index to speed up the execution of the statement

On how to optimize the sentence in MySQL Cost to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.