SQL statement optimization--with MySQL as an example

This article refers to the following article:

1: "True and dry" MySQL Index and optimization combat

2:mysql Statement Execution Process

Several methods of 3:sql optimization

I have divided the SQL statement optimization into three aspects,(this does not include the optimization of business logic and the use of caching ): index optimization, use of keywords, effective evasion.

1: Index optimization can be divided into index hit invalid optimization and index hit inefficient optimization;

1): Invalid index hit condition:

A: If it is a single-column index, it is best not to appear null; It is said to be allowed to be null, which may result in a set of results that does not conform to expectations;

B: Index leading blur such as: like "%xx";

C: Negative condition query cannot use index: Optimization strategy: Optimize the negative condition to in query; Negative conditions are:! =, <>, not in, not exists, not like;

D: The index column participates in the calculation;

E: The index column to do the forced conversion processing;

2): Index hit inefficient condition:

A: From efficiency: union > in > or; recommended in;

B: Union index the leftmost prefix principle;

C: For order by to take advantage of the validity of the index:

2: Use of keywords:

A: optimize in with exists;  (Well, here I have a little question about optimizing the negative to conditional queries to in and the Union, or to in;) It says to use exists to optimize in;  So the question is, do you want to use the exists to optimize it? )

B: Use InStr () function to optimize like;

InStr (A, B) > 0 <==> find Like ("%b%") from column A;

InStr (A, B) = 0 <==> Find not-like ("%b%") from column A;

InStr (A, B) = 1 <==> find like ("b%") from column A;

C: More than three tables is best not to join.

Fields that require joins, data types must be consistent, and multiple table associated queries ensure that the associated fields need to be indexed

3: effective evasion:

A: use of *;

Try to use all column names instead of *;

B:union and UNION All

If the use of Union is determined, no duplicate records will be generated;  Then the Union is used; Union is more efficient than union all

C: If you know that only one result is returned, limit 1 can improve efficiency.

D: Using deferred correlation

E: Use numeric fields as much as possible:

SQL statement optimization--with MySQL as an example