MySQL Optimization: Performance Analysis of like and = in where, Java Architect growth express updated to

========================================================================

First , Let's introduce it mysql Medium explain keyword ;explain It means to carry out the plan , That is, view this message through this command sql How is it implemented .

It's easy to use , namely explain + sql sentence , for example ：

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

Specifically for explain The introduction of keywords will be summarized in my blog in the future , At present, I don't know that small partners can baidu on their own ...

Let's use it explain Test it like and = Query under , First, let's test the fields that are indexed ：

EXPLAIN SELECT

• FROM

crms_customer WHERE

id = '7cf79d7c8a3a4f94b5373b3ec392e32d';

And now we put "=" Switch to like Have a try ：

EXPLAIN SELECT

• FROM

crms_customer WHERE

id LIKE '7cf79d7c8a3a4f94b5373b3ec392e32d';

Through comparison, you can see two returned results type Fields and Extra The data in the field is different , Then why is it different , What do they mean ？

[]( )type Field ：

===========================================================================

type Field is an optional value , The performance of these values from low to high is sorted as follows ：

It is obvious from the table that , among const Is a constant lookup , and RANGE Is to search the range of index columns , So the performance is obviously reflected .

That use like When inquiring ,Extra What does the field represent ？Extra In the field Using where, What does it stand for ？

[]( )Extra Field

=======================================================================

1,Extra The fields are Explain Also important columns in the output , Represents MySQL Important supplementary information to query plan during query optimizer execution .

2,Extra In the field Using where signify mysql The server will filter the rows after the storage engine retrieves them . So instead of using ’=' Another step in the search process .

Obviously, from the above summary, we can draw a conclusion ： When we use index fields for conditional queries ,’=' It's better than like fast .

Do you think it's over here ？

But there is no

Some friends should ask about the non indexed fields ？

Right , Let's continue to test non indexed fields

EXPLAIN SELECT

• FROM

crms_customer WHERE

customer_name = ' Zhang Fei ';

summary

Internet companies like the characteristics of talent ： Passion for Technology , Strong technical foundation strength ; Take the initiative , Good at teamwork , Good at summarizing and thinking . No matter which company , They attach great importance to high concurrency and high availability technology , Pay attention to the foundation , So don't underestimate any knowledge . Interview is a two-way choice process , Don't be afraid to interview , Not conducive to their own play . At the same time, we should not only focus on salary , It depends on whether you really like the company , Can I really get exercise . Actually I wrote so much , It's just my own summary , Not necessarily for everyone , I believe after some interviews , Everyone will have these feelings .

In addition, I also sorted and collected 2021 More than one company interview knowledge and a variety of technical points collated

Here are some screenshots, hoping to help you .

This article has been  CODING Open source project ：【 A big factory Java Analysis of interview questions + Core summary learning notes + The latest explanation video + Actual project source code 】 Included