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 '='?
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~~~
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 simple
Explain + 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';
Now let’s replace “=” with “like”
EXPLAIN SELECT * FROM crms_customer WHERE id LIKE '7cf79d7c8a3a4f94b5373b3ec392e32d';
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?
The type field is an optional value. The ranking of the performance of these values from low to high is as follows:
|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?
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
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:
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. )
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. ~~~~