MySQL Optimization: Performance Analysis of like and =

Time:2021-4-14

introduction

Most of the people who have used the database know the similarities and differences between the functions of like and = signs
    1, difference: like can be used as fuzzy query, but '=' does not support this function. For example, query the data whose first letter of field ID in info table is 1
select * from info where id like '1%';
2. Similarities: both like and "=" can be used for precise query,
For example, in the following example, from the result point of view, it is the result of querying the field ID in the info table equal to '12345':
select * from info where id like '12345';
These are the similarities and differences between like and '=' in the returned results. The curious little friend may be about to ask, what about the implementation process? Is the execution process of MySQL the same whether it encounters like or '='?

MySQL Optimization: Performance Analysis of like and =

Yes, we can't just look at the surface. If you study it carefully, you will find that like and the equal sign '=' are not so simple. Next, we will analyze the real difference between them in detail~~~

text

First of all, let’s introduce the explain keyword in MySQL; explain means execution plan, that is, to see how the SQL is executed through this command.
The method of use is also very simpleExplain + SQL statement, for example:

explain select * from info where id like '12345';

Let’s use explain to test the query under like and =. First, let’s test the fields that are indexes

EXPLAIN SELECT
    * 
FROM
    crms_customer 
WHERE
    id = '7cf79d7c8a3a4f94b5373b3ec392e32d';

MySQL Optimization: Performance Analysis of like and =

Now let’s replace “=” with “like”

EXPLAIN SELECT
    * 
FROM
    crms_customer 
WHERE
    id LIKE '7cf79d7c8a3a4f94b5373b3ec392e32d';

MySQL Optimization: Performance Analysis of like and =

By comparison, we can see that the data in the type field and the extra field of the two returned results are different. Why are they different and what do they mean?

Type field

The type field is an optional value. The ranking of the performance of these values from low to high is as follows:

type explain
SYSTEM System, the table has only one row (= system table). This is a special case of the const join type
CONST Constant, the table has at most one matching row, because there is only one row, and the column values in this row can be regarded as constants by the rest of the optimizer. Const tables are very fast, because they are read only once
EQ_REF Use primary key or unique type when searching
REF Finds one or more values based on the index
INDEX_MERGE Merge indexes and search with multiple single column indexes
RANGE Range lookup on index columns
index Full index table scan
ALL Full data table scan

It is obvious from the table that const is a constant search, while range is a range search for index columns, so the performance is obviously reflected.

What does the extra field represent when using like query? What does using where in the extra field stand for?

Extra field

1. The extra field is also a very important column in the explain output, which represents the important supplementary information to the query plan during the query execution process of MySQL query optimizer.
2. Using where in the extra field means that the MySQL server will filter after the rows are retrieved by the storage engine. So it’s one more step than using ‘=’.
Obviously, from the above summary, we can draw a conclusion: when we use index fields for conditional queries, the performance of ‘=’ is faster than that of like.

Do you think it’s over here?

But it didn’t
MySQL Optimization: Performance Analysis of like and =

Some small partners should ask, which non index field?
Yes, let’s continue to test non index fields

EXPLAIN SELECT
    * 
FROM
    crms_customer 
WHERE
    customer_ Name ='zhang Fei ';
-----------------------------------
    
EXPLAIN SELECT
    * 
FROM
    crms_customer 
WHERE
    customer_ Name like 'Zhang Fei';

In addition to the same two statements of “=” and like, let’s run them:
“=”:
MySQL Optimization: Performance Analysis of like and =
like:
MySQL Optimization: Performance Analysis of like and =
It can be seen that when non index fields are used, like and “=” are the same, and there is no difference in performance.
(of course, there are many other fields in explain, which I will explain to my friends one by one later. )

conclusion

After our unremitting efforts, we can draw a conclusion: when like and “=” use non index field query, their performance is the same; when using index field, because “=” is a direct hit index, only read once, and like needs range query, so “=” is better than like. ~~~~