Detailed analysis of MySQL optimization of like and = performance

introduction

Most people who have used databases know the similarities and differences between the like and = signs in terms of function. Let me briefly summarize them here:

1. Differences: Like can be used for fuzzy query, while '=' does not support this function; as in the following example, query the data in the info table whose first letter of the field id is 1:

select * from info where id like '1%';

2. Similarities: Both like and "=" can be used for precise search.

For example, the following examples all query the results of the info table where the field id is equal to '12345':

select * from info where id like '12345';

The above are the similarities and differences between like and '=' in the returned results. Then curious friends may ask, what about the execution process? Is the execution process of MySQL the same whether it encounters like or '='?

That's right, things can't just be judged by their appearance. If you study carefully, you will find that like and the equal sign '=' are not that simple. Below we will analyze the real difference between the two in detail~~~

text

First, let's introduce the explain keyword in MySQL; explain means the execution plan, that is, use this command to see how this SQL is executed.
The usage is also very simple, that is, explain + sql statement, for example:

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

Then let's use explain to test the query under like and =. First, let's test the indexed fields:

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

Now let's replace "=" with like and try it:

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

By comparing, you can see that the data in the type field and 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, and the values ​​are sorted from lowest to highest performance as follows:

It can be clearly seen from the table that const is a constant search, and RANGE is a range search on the index column, so the performance is clearly reflected.

So what does the Extra field represent when using a like query? What does Using where in the Extra field mean?

Extra Field

1. The Extra field is also a very important column in the Explain output, which represents important supplementary information of the query plan during the query execution process of the MySQL query optimizer.

2. Using where in the Extra field means that the MySQL server will filter after the storage engine retrieves the row. Therefore, compared to using '=', there is one more step in the search process.

Obviously, through the above summary we can conclude that when we use index fields for conditional queries, the performance of '=' is faster than that of like.

Do you think this is the end?

However, there is no

Some friends may ask, what about non-index fields?

Yes, let's continue testing non-indexed fields.

EXPLAIN SELECT
 * 
FROM
 crms_customer 
WHERE
 customer_name = 'Zhang Fei';
-----------------------------------
 
EXPLAIN SELECT
 * 
FROM
 crms_customer 
WHERE
 customer_name LIKE '张飞';

Let's run the same two statements except for "=" and like:
"=":

like:

It can be seen that when it is a non-index field, like and "=" are the same, and there is no difference in performance.

(Of course, there are many other fields in explain, and I will explain them one by one later.)

in conclusion

Through our unremitting efforts, we can draw the conclusion that when like and "=" are queried using non-indexed fields, their performance is the same; when using indexed fields, since "=" directly hits the index and is only read once, while like requires a range query, "=" has better performance than like. ~~~~

This is the end of this article about MySQL optimization of like and = performance. For more relevant MySQL like and = performance content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!