SQL optimization best practices

Catalog

1 Business logic optimization , Reduce the scope of data query

2 What you take is what you use , Avoid select *

3 Establish a reasonable index , Good at using indexes

4 Avoid doing operations or function operations on index columns

5 avoid like Use , Be sure to use , Field plus index

6 It is recommended to use join Substitute subqueries

7 Avoid using join

8 DML Be short

9 Make good use of force Field

---

sql The essence of optimization is to reduce io、cpu The consumption of resources , Give Way sql Faster execution , Finally meet our performance requirements .

Here's what we've been through sql Summary of optimization practice ：

1 Business logic optimization , Reduce the scope of data query

#  With  in  For example , Alibaba protocol recommends that batch queries be controlled at 200 within 

select * from  Table name  where id in (1,3,,5,7 ......)

2 What you take is what you use , Avoid select *

What you take is what you use , That is, we can take whatever fields we need , avoid select * .

Why avoid select * Well ？ Suppose our business table has 50 A field ,select * It means to take this... At one time 50 A field , this 50 The transmission of two fields in the network takes up a lot of bandwidth , Affect transmission speed .

3 Establish a reasonable index , Good at using indexes

• Select the column with high field discrimination in the table to establish the index .
• Priority is given to where and order by Index the columns involved .

# name stay user High discrimination of tables , You can give name Add index 

select name from user where name = " Zhang San ";

# age Index , because age Field order , It can reduce the consumption of sorting 

select name from user order by age limit 20;

# (name,age) Set up a joint index 

select name,age from user where name = " Zhang San " order by age limit 20;

4 Avoid doing operations or function operations on index columns

Do operations or function operations on index columns , Will result in index invalidation .

select id from user where age/2 = 5;

5 avoid like Use , Be sure to use , Field plus index

select id from user where name like 'jim%'

6 It is recommended to use join Substitute subqueries

#  Don't suggest 

select a.name from a where a.id in (select rid from b)

#  Suggest 

select a.name from a left join b on a.id = b.rid;

7 Avoid using join

In principle, avoid using join, If it must be used , It is suggested that the associated fields should have indexes , The small table is preferred as the driving table .

select a.name from a join b on a.id = b.rid where a.id = 9;

8 DML Be short

So-called DML Be short , What does that mean , for instance , We need to update 1 Ten thousand data , We can divide it into 10 Secondary update , Every time 1 A thousand , perform 10 Time , Why do you want to do this ？ An update if you hold the lock for a long time , It's blocking other sql, Affect system performance , And if it is divided into several times , It will greatly reduce the probability of lock collision .

update user set b = 1 where id between 1 and 1000;

update user set b = 1 where id between 1001 and 2000;

......

update user set b = 1 where id between 9001 and 10000;

9 Make good use of force Field

You may have met sql There is no problem with the right index , It can be used at this time force Give Way sql Go to the index .

select a.name from user force index( Indexes );

Don't abuse force, Only in sql When there is no index , And we need to make sql Use when indexing .