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

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

2022-02-04 16:47:54 mb61ab44019023e

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

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 ...

mysql Optimization :where Medium like and = Performance analysis of

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';

mysql Optimization :where Medium like and = Performance analysis of

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

EXPLAIN SELECT

  • FROM

crms_customer WHERE

id LIKE '7cf79d7c8a3a4f94b5373b3ec392e32d';

mysql Optimization :where Medium like and = Performance analysis of

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 :

mysql Optimization :where Medium like and = Performance analysis of

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

mysql Optimization :where Medium like and = Performance analysis of

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 .

 Insert picture description here

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

copyright notice
author[mb61ab44019023e],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/02/202202041647533901.html

Random recommended