How to optimize sql statement?

Several methods for sql statement optimization include: 1. Unify the format of SQL statements; 2. Optimize queries and avoid full table scans; 3. SQL statements should be concise; 4. Consider using "temporary tables" to temporarily Store intermediate results; 5. Try to avoid large transaction operations; 6. Try to avoid returning large amounts of data to the client. The following article will introduce you to some details, I hope it will be helpful to you.

In the early days of our development project, due to the relatively small amount of business data, the impact of the execution efficiency of some SQL on the program running efficiency was not obvious, and the development and operation and maintenance personnel It is also impossible to judge how efficient SQL is on the running efficiency of the program, so special optimization of SQL is rarely carried out. As time accumulates and the amount of business data increases, the impact of SQL execution efficiency on the running efficiency of the program gradually increases. At this time, optimization of SQL is necessary.

Several methods for sql statement optimization:

1. Unify the format of SQL statements

Many people think that the following two SQL statements are the same, but the database query optimizer thinks they are different.

●select * from dual

●select * From dual

Although the case is only different, the query analyzer It is considered that they are two different SQL statements and must be parsed twice. Generate 2 execution plans. Therefore, as a programmer, you should ensure that the same query statement is consistent everywhere, even one more space will not work!

2. Use * less, replace "*" with a specific field list, and do not return any unused fields.

3. To optimize the query, try to avoid full table scans

1) You should consider creating indexes on the columns involved in where and order by.

2) Try to avoid making null value judgments on fields in the where clause, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num is null

can be used on num Set the default value 0, make sure there is no null value in the num column in the table, and then query like this:

select id from t where num=0

3). Try to avoid using != or <> operators in the where clause, otherwise it will cause The engine gives up using the index and performs a full table scan

4). Try to avoid using or in the where clause to connect conditions, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num=10 or num=20

can be queried like this:

select id from t where num=10 union all select id from t where num=20

5), use in and not in with caution, otherwise it will cause a full table scan, such as:

select id from t where num in(1,2,3)

For continuous values, you can use between Don’t use in:

select id from t where num between 1 and 3

6), use like fuzzy query reasonably

Sometimes you need to perform some fuzzy query, such as:

select * from contact where username like ‘%yue%’

Keyword %yue%, Since "%" is used in front of yue, the query must scan the entire table. Unless necessary, do not add %

7 in front of the keywords. You should try to avoid expressing fields in the where clause. This will cause the engine to give up using the index and perform a full table scan. For example:

select id from t where num/2=100

should be changed to:

select id from t where num=100*2

8), you should try to avoid performing functional operations on fields in the where clause, which will cause the engine to give up using the index and perform a full table scan. For example:

Query the ID whose name starts with abc

select id from t where substring(name,1,3)='abc'

should be changed to:

select id from t where name like 'abc%'

4. Use exists instead of in

Many times it is a good choice to use exists instead of in. Exists only checks existence and its performance is much better than in. Example:

select num from a where num in(select num from b)

Replace with the following statement:

select num from a where exists(select 1 from b where num=a.num)

5. Don’t write SQL statements that are too long, too redundant, and be concise; if you can use one sentence, don’t Use two sentences

Generally, the results of a Select statement are used as a subset, and then the query is performed from the subset. This kind of nested statement is relatively common, but according to experience, more than With three levels of nesting, the query optimizer can easily give wrong execution plans. Because it was stunned. Things like artificial intelligence are ultimately inferior to human resolution. If people are dizzy, I can guarantee that the database will be dizzy as well.

In addition, the execution plan can be reused. The simpler the SQL statement, the higher the possibility of being reused. As long as one character changes in a complex SQL statement, it must be re-parsed, and then a lot of garbage will be stuffed in the memory. It is conceivable how inefficient the database will be.

6. Consider using a "temporary table" to temporarily store intermediate results

An important way to simplify SQL statements is to use a temporary table to temporarily store intermediate results. However, the temporary table The benefits are far more than these. The temporary results are temporarily stored in the temporary table, and subsequent queries are in tempdb. This can avoid multiple scans of the main table in the program, and also greatly reduces the "shared lock" blocking "update lock" during program execution. , reducing blocking and improving concurrency performance.

7,When using an index field as a condition, if the index is a compound index, then the first field in the index must be used as the condition to ensure that the system uses the index, otherwise the Indexes will not be used, and field order should be consistent with index order whenever possible.

8. Try to use numeric fields. If the fields contain only numerical information, try not to design them as character fields. This will reduce the performance of query and connection, and increase storage overhead.
This is because the engine will compare each character in the string one by one when processing queries and connections, and only one comparison is enough for numeric types.

9. Use varchar instead of char as much as possible, because first of all, variable length fields have small storage space and can save storage space. Secondly, for queries, in a relatively small field The search efficiency is obviously higher.

10. Avoid frequently creating and deleting temporary tables to reduce the consumption of system table resources.

11. Try to avoid using cursors, because cursors are less efficient. If the data operated by the cursor exceeds 10,000 rows, you should consider rewriting it.

12. Try to avoid large transaction operations and improve system concurrency.

13. Try to avoid returning large amounts of data to the client. If the amount of data is too large, you should consider whether the corresponding requirements are reasonable.

MySQL Tutorial"

The above is the detailed content of How to optimize sql statement?. For more information, please follow other related articles on the PHP Chinese website!