Shulou(Shulou.com)06/01 Report--

Editor to share with you what are the optimization methods of sql sentences, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

There are several ways to optimize sql statements: 1, unify the format of SQL statements; 2, optimize the query and avoid full table scanning as far as possible; 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.

At the initial stage of the launch of our development project, due to the relatively small amount of business data, the impact of the execution efficiency of some SQL on the running efficiency of the program is not obvious, and the developers and operators are unable to judge the running efficiency of the program by SQL, so there is little special optimization for SQL, but with the accumulation of time and the increase of the amount of business data, the influence of the execution efficiency of SQL on the running efficiency of the program increases gradually. At this point, it is necessary to optimize SQL.

Several methods for optimizing sql statements:

1. Unify the format of SQL statements

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

● select * from dual

● select * From dual

Although there is only a difference in case, the query analyzer thinks that they are two different SQL statements that must be parsed twice. Generate 2 execution plans. So as a programmer, you should make sure that the same query statements are consistent everywhere, not even one more space!

2. Use less *, replace "*" with a specific list of fields, and do not return any fields that are not needed.

3. to optimize the query, full table scanning should be avoided as far as possible.

1). You should consider indexing the columns involved in where and order by.

2). Try to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and perform a full table scan, such as:

Select id from t where num is null

You can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num=0

3) try to avoid using the! = or operator in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan

4) try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans, such as:

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

You can query it like this:

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

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

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

For consecutive values, use between instead of in:

Select id from t where num between 1 and 3

6) rational use of like fuzzy query

Sometimes you need to make some fuzzy queries, such as:

Select * from contact where username like'% yue%'

Keyword% yue%, due to the use of "%" in front of yue, the query must take a full table scan, do not add% in front of keywords unless necessary

7) try to avoid expression operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

8) functional manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to give up using indexes and perform full table scans. Such as:

Query the id of name that begins with abc

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

It should be changed to:

Select id from t where name like 'abc%'

4. Replace in with exists

In many cases, using exists instead of in is a good choice. Exists only checks for existence, and its performance is much better than in. Example:

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

Replace it with the following statement:

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

5. Don't write SQL sentences too long, too redundant and concise. Don't use two sentences if you can.

In general, it is common to take the results of an Select statement as a subset and then query from that subset, but according to experience, more than three layers of nesting, the query optimizer is easy to give the wrong execution plan. Because it was fainted. Things like artificial intelligence, after all, are worse than people's resolution. If people are dizzy, I can guarantee that the database will also be dizzy.

In addition, execution plans can be reused, and the simpler the SQL statement, the more likely it is to be reused. Complex SQL statements have to be reparsed as long as one character changes, and then this pile of garbage is stuffed into memory. You can imagine how inefficient the database will be.

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

An important way to simplify SQL statements is to use temporary tables to temporarily store intermediate results, but the benefits of temporary tables are far more than these. Temporary results are temporarily stored in temporary tables, and the subsequent query is in tempdb, which can avoid scanning the main table many times in the program, and greatly reduce the "shared lock" blocking "update lock" in program execution, reducing blocking and improving concurrent performance.

7. When using an index field as a condition, if the index is a composite index, you must use the first field in the index as a condition to ensure that the system uses the index, otherwise the index will not be used. and the order of the fields should be consistent with the order of the index as far as possible.

8. Try to use numeric fields, and try not to design character fields that contain only numeric information, which will reduce the performance of queries and connections, and increase storage overhead.

This is because the engine compares each character in the string one by one when processing queries and connections, while for numeric types, it only needs to be compared once.

9. Use varchar instead of char as much as possible, because first of all, the storage space of long fields is small, which can save storage space, and secondly, for queries, searching in a relatively small field is obviously more efficient.

Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

11. Avoid using cursors as much as possible, because cursors are inefficient, and if you operate on more than 10,000 rows of data, you should consider rewriting them.

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

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

The above are all the contents of the optimization methods of sql statements. Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.