Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the common method of sql optimization. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

SQL optimization commonly used methods are: 1, should try to avoid full table scanning, should consider where and order by involved in the establishment of the index on the column;2, try to avoid in the where clause for the null value judgment of the field;3, careful use of in and not in;4, try to avoid large transaction operations, improve system concurrency.

Why should SQL be optimized?

At the initial stage of our development project, due to the relatively small amount of business data, the execution efficiency of some SQL has little impact on the operation efficiency of the program, and the development and operation personnel cannot judge how much SQL affects the operation efficiency of the program, so they rarely optimize SQL. However, as time accumulates and the amount of business data increases, the impact of SQL execution efficiency on the operation efficiency of the program gradually increases. At this time, it is necessary to optimize SQL.

Some common methods of SQL optimization

1. To optimize queries, avoid full table scans as much as possible, and first consider indexing where and order by columns.

2. Try to avoid making null value judgments on fields in where clauses, otherwise the engine will abandon using indexes and perform full table scans, such as:

select id from t where num is null

You can set the default value 0 on num, make sure there are no null values in num column in the table, and then query like this:

select id from t where num=0

3. Avoid using!= in where clauses Or operator, otherwise the engine forgoes index use and performs a full table scan.

4. Avoid using or in the where clause to join conditions, otherwise it will cause the engine to abandon the use of indexes and perform a full table scan, such as:

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

You can query:

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

5.in and not in should also be used with caution, otherwise it will lead to full table scanning, such as:

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

For continuous values, you can use between instead of in:

select id from t where num between 1 and 3

6. The following query will also result in a full table scan:

select id from t where name like '%abc%'

7. Expression operations on fields in where clauses should be avoided as much as possible, as this will cause the engine to forgo indexes in favor of a full table scan. For example:

select id from t where num/2=100

should read

select id from t where num=100*2

8. Function operations on fields in where clauses should be avoided as much as possible, which will cause the engine to forgo indexes in favor of a full table scan. For example:

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

should read

select id from t where name like 'abc%'

9. Do not perform functions, arithmetic operations, or other expression operations to the left of "=" in where clauses, or the system may not use indexes correctly.

10. When index fields are used as a condition, if the index is a composite index, the index must be used until the first field in the index is used as a condition, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.

11. Don't write meaningless queries, such as generating an empty table structure:

select col1,col2 into #t from t where 1=0

This code does not return any result set, but consumes system resources, so it should be changed to:

create table #t(...)

12. Many times using exists instead of in is a good choice:

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)

13. Not all indexes are valid for queries. SQL queries are optimized according to the data in the table. When there is a large amount of duplicate data in the index column, SQL queries may not use the index. For example, if there is a field sex in a table, male and female are almost half, then even if the index is built on sex, it will not play a role in query efficiency.

14. Index is not the more the better, index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update,

Because it is possible to rebuild the index when inserting or updating, how to build the index needs to be carefully considered, depending on the specific situation.

A table index number is best not to exceed 6, if too many should consider whether some infrequently used columns built index is necessary.

15. Use numeric fields as much as possible. If fields only contain numeric information, try not to design them as character fields, which will reduce query and join performance and increase storage overhead.

This is because the engine compares each character in the string individually when processing queries and joins, whereas only one comparison is required for numeric types.

16. Use varchar instead of char as much as possible, because the storage space of variable length field is small first, which can save storage space.

Second, for queries, it is obviously more efficient to search within a relatively small field.

17. Never use select * from t anywhere, replace the "*" with a specific list of fields, and don't return any fields that you don't need.

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

19. Temporary tables are not unusable, and appropriate use of them can make certain routines more efficient, for example, when you need to reference a dataset repeatedly from a large or frequently used table. However, for one-time events, it is best to use export tables.

20. When creating a temporary table, if the amount of data inserted at one time is large, you can use select into instead of create table to avoid causing a large number of logs.

To speed up; if the data volume is not large, in order to ease the resources of the system table, you should first create the table, and then insert.

21. If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the storage process, truncate the table first, and then drop the table, so as to avoid long-term locking of the system table.

22. Cursors should be avoided because they are inefficient, and if they operate on more than 10,000 rows, they should be overwritten.

23. Before using a cursor-based or temporary table approach, you should look for a set-based solution to the problem, which is usually more efficient.

24. Cursors, like temporary tables, are not unusable. Using FAST_FORWARD cursors on small datasets is generally preferable to other row-by-row processing methods, especially when several tables must be referenced to obtain the desired data.

A routine that includes Total in the result set is usually faster than one that executes with cursors. If development time permits, both cursor-based and set-based approaches can be tried to see which works better.

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

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

About "sql optimization common method is what" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.