MySQL optimization 3 (optimization rules)

Time:2021-4-20

When you look at the following content, make sure you can use explain to analyze SQL!

Single index table optimization case:

Question:
For example, we want to query the first record sorted by user name with password = 666 and age > 30. After analysis, we will find that the SQL internal optimizer has carried out a full table scan, and there is also using filesort in type = all extra, which indicates that the external index is used for sorting, but not the index of this table, so the optimization must be done!
MySQL optimization 3 (optimization rules)
solve:
A: We now index all the fields involved after where, for example:
create index idx_pau on admin(password,age,username);
Then the analysis is carried out
MySQL optimization 3 (optimization rules)
We found that type = range avoids the full table scan of type = all, but there is still using file sort in extra. This is because the index we created doesn’t work. Do you ask if there is IDX in the key field_ The Pau index indicates that the index is working!? when we are looking up, the following is password = constant age > constant, and then order by username. Password will be used to match the content in the index, and age will also be used to match the content in the index. However, one rule is that the index after the range is invalid. The premise is that you create a composite index. At present, it looks like this! Because we create the index in the order of password age username, because age is the range of fetching, so the latter index username will be invalid, so it will cause the situation of using filesort or the existing situation!
B. The first correct solution:
Index every field after where. For example:
create index idx_p on admin(password);
create index idx_p on admin(age);
create index idx_u on admin(username);
In this way, we can add indexes to all the fields involved after where. First of all, the problem of type = all must be solved. In addition, the problem of order by username is also solved. As we said before, the rules for establishing indexes for the fields after order by should be the same as those in front of order by. If not, we should create composite indexes together with the fields in front of order by and keep the same order. See using for details But there is another situation here, that is, the index is created for the field after order by, and there is no using filesort!
C . The second correct solution:
That is to create a composite index only for password and username
create index idx_pu on admin(password,username);
If age > 0 does not follow the index, it will not affect the use of the index following order by! If age > 0 does not follow the index, it will not affect the use of the index following order by!

Index two tables Association Optimization:

Question: ID in boys = boyfriend in beauty_ ID uses left join to query our conventional writing method, and then through the analysis of explain, we come to the conclusion that type = all has carried out a full table scan, so we have to optimize it!
MySQL optimization 3 (optimization rules)
How to optimize it?
Summary: if it’s a left join, add an index to the corresponding field in the right table. If it’s a right join, add an index to the corresponding field in the left table!
So let’s use left join to add an index to the fields in the table on the right to boyfriend in beauty_ ID to create an index, and then analyze it
MySQL optimization 3 (optimization rules)
We found that there is ref in the type and the row number of rows is also reduced a lot, perfectly completing the optimization task! We found that there is ref in the type and the row number of rows is also reduced a lot, perfectly completing the optimization task!

The three tables are optimized

The principle is to ensure that the join condition field on the driven table in the join statement has been indexed
The left side of the left join is the main table, that is, the drive table, and the right side is the driven table
Right join
The principle of minimizing the total number of loops in a join statement is to always use a small result set to drive a large result set
How does myslq optimize left join:https://www.cnblogs.com/zedosu/p/6555981.h…

Index optimization

A. How to avoid index invalidation? A. how to avoid index invalidation?
MySQL optimization 3 (optimization rules)
For the explanation of the above summary:
Full value matching:
For example, we created a composite index for the admin table
create index idx_ UPA on admin (user name, password, age); / / a composite index is created for the three fields of user name, password, age
Execute SQL:
explain select * from admin where username=’john’ and password=8888 and age=29;
MySQL optimization 3 (optimization rules)
We found that the index was fully utilized ! The best way to avoid index failure is full value matching. For example, we created IDX_ For uPA composite index, when we query, the order of the fields is the same as the order in which we create the composite index. At the beginning and the end, there are many fields in the middle. In this way, the efficiency is the highest and the index failure can be absolutely avoided. This is called full value matching !
The best left prefix method: (if multiple columns are indexed, that is, a composite index is created, the leftmost prefix method should be followed, which means that the query starts from the leftmost front row of the index and does not skip the columns in the index)
Take the above case
We created IDX_ UPA composite index can’t be all value matching when querying. For example, we don’t look up user name now = ‘’ password = ‘’ age = If we want to find any two of them, the index will fail!
A . For example: Select * from admin where username =’’ and password =’’;
MySQL optimization 3 (optimization rules)
The condition we checked is username =’’ and password =’’ Because user name and password and composite index idx_ UPA is all in order. U stands for user name, P stands for password, and the order is consistent, so there will be no index failure
B. If we look up select * from admin where password =’and age = ‘, then the index will be invalid, because it violates the left prefix rule. When looking for the index, we will first match the leftmost index, and then look for the first index later. It can be understood that the locomotive can’t be found, so the latter one can’t be used!
MySQL optimization 3 (optimization rules)
C. If we are looking for select * from admin where user name =’john ‘and age = 29; So, although there will be no index failure prompt, the key also has the index value, but before the index does not fail, the consts in ref are two consts, but now it is a const, which means that one of them is not used for the index. In this case, if there is a large amount of data, it will also affect the performance, and the key_ The length of len is the same as the length when we search for username = ‘, so we can be sure that the latter one is not indexed
MySQL optimization 3 (optimization rules)
D. If we look up select * from admin where age = 29 or password = 8888 separately, the index will also fail, because we are looking for the second composite index and the third index which will definitely fail;
MySQL optimization 3 (optimization rules)
So there is a formula:
The leader can’t be less!
The middle brother cannot be broken!
Except for big brother, other brothers will die if they act alone!
The leading elder brother abandons the middle brothers and takes the back brothers. As a result, only the leading elder brother can live and the others will die!
Not doing anything on the index column (calculating function type conversion) will cause the index implementation to switch to full table scanning
MySQL optimization 3 (optimization rules)
Composite index idx_ UPA we use the function on the index column from the locomotive’s user name, which directly leads to index failure!
The column to the right of the range condition in the index cannot be used in MySQL
In the case of full-time matching: ref level and ref column has three consts representing constants
MySQL optimization 3 (optimization rules)
There are ranges in the index: (if you don’t create an index, it must be a full table scan. Some indexes don’t work.)
MySQL optimization 3 (optimization rules)
The final result is that the range level is obviously not as high as ref level. In addition, if ref is empty, some index columns don’t work! The solution has been mentioned above!
If we do not use a composite index, but instead create separate indexes for three fields, it becomes the ref level
MySQL optimization 3 (optimization rules)
Try to use overlay index (query that only access index (index column is consistent with query column) to reduce select *)
MySQL optimization 3 (optimization rules)
The above is the premise of composite index ha!
The index cannot be used when it is not equal to! = or < > in mysql, which will result in full table scan
MySQL optimization 3 (optimization rules)
Is null in not null also cannot use index
And is not null will scan the whole table
So we can give the default value to the field, so that there will be no null, and the use of is null and is not null will be avoided!
If like starts with a wildcard character (% ADB% or% ABC), the MySQL index will fail and become a full table scan, but like ‘ABC%’ will not cause index failure
But in the development process, we often encounter the situation that must be% ABC%. How to solve the problem of index invalidation?
That is to use overlay index! What is an overlay index
For example:
create index idx_ username on admin ( username ); We created an index for the username column
Then, select user name from admin where user name like ‘% H%’;
MySQL optimization 3 (optimization rules)
But if we do this: select * from admin where username like ‘% ABC%’;
Then it will cause index failure and scan the whole table, because it is not used to cover the index!
And if we look up select password from admin where username like this way‘ % abc %’; Also full table scan and index failure
But if we look up select id this way , username from admin where username like ‘ % abc %’; Will not cause the whole table scan and index failure, because ID is always the primary key, also from the index table to find, equivalent to also covering the index!
Note: if we create a composite index, as long as the query column is within the scope of the composite index, we can avoid full table scanning and index invalidation!
The index of varchar type column string is invalid if it is not enclosed with single quotation marks
For example, we usually store the varchar type as a string, but sometimes we have to store 2000 or other numbers. If we don’t pay attention to adding single quotation marks when querying, then the SQL optimizer will first perform implicit type conversion, and then search, and the most important thing is that the index fails and the full table is scanned
Use or less, it will cause index invalidation when it is used to link
If you use or to connect, for example, select * from admin where user name = a or user name = B; this will also cause index failure and cause full table scanning; the solution is not as good as separate query, and then go to PHP to splice into an array
Simple small test with understanding:
MySQL optimization 3 (optimization rules)
MySQL optimization 3 (optimization rules)

It is helpful to understand and use the interview questions
The premise is that we have created the composite index idx_ test03_ C1234 C1 C2 C3 C4 such a sequence of composite index Oh !
A: The first is C1 C2 C3 C4
The second is C1 C2 C4 C3
The third is C4 C3 C2 C1
The result is the same, all indexes are used, that is to say, whether indexes are used or not has nothing to do with the order of composite indexes
MySQL optimization 3 (optimization rules)
B: The result of range query is range level, which means that C1, C2 and C3 are all used, but ref is empty. According to the principle, ref should be four consts, but ref is null and key_ The length of len is just one less than that of all four, which means that the C4 index is invalid! This also verifies the principle of full invalidation after range
MySQL optimization 3 (optimization rules)
C.c1=’a1’ and c2=‘a2’ and c4>’a4’ and c3 = ‘a3’
In this case, although the order of our SQL instructions has changed, the SQL optimizer will search according to the index order of C1 C2 C3 C4. Although C4 is a range search, it is found by the SQL optimizer at last, so the four indexes are effective
MySQL optimization 3 (optimization rules)
D: C1 =’a1’and C2 =’a2’and C4 =’a4’order by C3, the middle brother can’t be broken. It seems that it’s obviously broken here, so it’s certain that C1 C2 is absolutely used. In fact, C3 is also used. It’s just that the order is not reflected in explain! C4 is absolutely not used! If C3 is not used, then there must be using filesort in extra. As I said before, this kind of using filesort is extremely inefficient!
MySQL optimization 3 (optimization rules)
E: C1 = A1 and C2 = A2 order by C3, the result is the same as that of D. C4 is definitely not used to sort with C3, and C1 and C2 are absolutely used in the explanation!
MySQL optimization 3 (optimization rules)
F: C1 = A1 and C2 = A1 order by C4; the result is that C1 C2 is used, C3 C4 is not used, and using filesort is also generated
MySQL optimization 3 (optimization rules)
G: C1 = A1 and C5 = A5 order by C2, the result of C3 is that C1 is used, C2 and C3 are used, but sorting will not produce using file sort
MySQL optimization 3 (optimization rules)
H: C1 = A1 and C5 = A5 order by C3, C2 result is that C1 is used because the index order is 1234, but when order by appears 3 and then 2, it will lead to using filesort
MySQL optimization 3 (optimization rules)
1: C1 = A1 and C2 = A2 order by C2, C3 the result is that C1 and C2 are all used and sorted according to C2 and C3. It’s perfect
c2 = A2 is a constant. Order by constant , C3, then the constant sort is dead. In fact, it is sorted according to C3, and C3 is in the index, so there is no problem
(provided that C2 = A2 is a constant)
MySQL optimization 3 (optimization rules)
j : c5 = A5 is just a Wahaha to confuse you
MySQL optimization 3 (optimization rules)
K: C1 = A1 and C5 = A5 order by C3, C2 must use C1, but the inconsistent order and index of C3 and C2 will also lead to the generation of filesort
MySQL optimization 3 (optimization rules)
L : c1 = a1 and c4 = a4 group by c2,c3 ; C1 must have been used, index C4 was never used, and C2 and C3 were also used in group by, because 1234 23, which is also in line with our index order, is in line with this order, but it is not displayed in explain
MySQL optimization 3 (optimization rules)
M: : C1 = A1 and C4 = A4, group by C3, C2 C1 must be the one with index, C4 is never used, C3 C2 after group by violates our index order, so it will produce temporary table and using filesort, which is very bad!
MySQL optimization 3 (optimization rules)

Summary:
Explain analysis first looks at the fixed value, that is, constant, then the index after the range must be invalid, and then the order by order by must be consistent with the index order. If the order is inconsistent, it will lead to the occurrence of the filesort situation. What do you mean? We specify the order of the index, but we do not write the SQL instruction according to the order Then the SQL optimizer will sort internally, that is, we can see the use file sort situation
Basically, group by needs to sort. If the field index used is incorrect, it is the same as order by to sort internally, and then move or delete after sorting, so the temporary table is generated
So the use of index is very important, especially the correct use of index!

Optimization formula:
MySQL optimization 3 (optimization rules)

Query Optimization:
Always small table drives big table
As shown in the figure, perhaps the results of the two for loops in the PHP program are the same, but they are different in MySQL. The first one will establish five MySQL links, and the second one will establish 1000 links. For MySQL, the most harmful thing is to establish and release the connection, so always use the small table to drive the large table

Optimization principle: small tables drive large tables, that is, small data sets drive large data sets
MySQL optimization 3 (optimization rules)
The small result set on the right of in and the large result set on the right of existx

Order by

The order by clause should be sorted by index instead of using file sort
For the research of order by, we mainly focus on whether it will produce using filesort
Next, let’s look at our own case
create index idx_ p_ U on admin (password, username); create password, username composite index
Special note: as long as you use order by, don’t use select * in the front, but write whatever field you want to find on demand, because once you write select * in the bottom, the summary will be inaccurate
In the first case:
Explain select user name, password from admin order by user name; / / there is a rule of compound leftmost prefix after user name order by, so there is no problem
MySQL optimization 3 (optimization rules)
Second case:
Explain select username, password from admin where username =’john ‘
MySQL optimization 3 (optimization rules)
Third case:
explain select username , password from admin where password > 10 order by username ;// The order of our index is username password. Although the order we wrote in the instruction is not correct, the SQL optimizer will automatically sort the index according to the left most prefix rule after order by
MySQL optimization 3 (optimization rules)
Fourth case:
Explain select user name, password from admin where user name =’john’order by password; / / the combination of where clause and order by clause condition column meets the index leftmost prefix rule!
MySQL optimization 3 (optimization rules)
Let’s look at the following classic case:
By default, all the index columns we create are sorted in ascending order. Although the order of our order by here has not changed, the sorting method has changed. Birth is the desc method, and the internal index SQL will also be sorted externally, so it will also produce a summary of the occurrence of filesort: either ascending or descending at the same time
MySQL optimization 3 (optimization rules)
Order by can be sorted by index in two cases:
The first explanation: order by is followed by fields in the same order as the index column, such as IDX_ name_ Age we order by name or order by name, age will not have the case of file sort
The second explanation: use the combination of where clause and order by clause condition column. For example, we create the composite index of name age, select * from admin where name =’a’order by age; this is where clause and order by clause condition column combination satisfies the leftmost prefix rule of index;
That is to say, sort the index columns as much as possible according to the best left prefix rule of index construction;
MySQL optimization 3 (optimization rules)
Finally, let’s make a final summary (very important) of order by
MySQL optimization 3 (optimization rules)

Group by

Everything else is the same as order by
The biggest difference is where is higher than having. If you can write the conditions defined in where, don’t go to having!
MySQL optimization 3 (optimization rules)

This work adoptsCC agreementReprint must indicate the author and the link of this article

Hu Jun

Recommended Today

Analysis of super comprehensive MySQL statement locking (Part 1)

A series of articles: Analysis of super comprehensive MySQL statement locking (Part 1) Analysis of super comprehensive MySQL statement locking (Part 2) Analysis of super comprehensive MySQL statement locking (Part 2) Preparation in advance Build a system to store heroes of the Three KingdomsheroTable: CREATE TABLE hero ( number INT, name VARCHAR(100), country varchar(100), PRIMARY […]