What are the commonly used methods for sql optimization?

Commonly used methods for sql optimization are: 1. Try to avoid full table scans, and consider establishing indexes on the columns involved in where and order by; 2. Try to avoid null values ​​for fields in the where clause. Judgment; 3. Use in and not in with caution; 4. Try to avoid large transaction operations and improve system concurrency.

1. Why should we optimize SQL

In the early days of our development project, due to the business data The amount of SQL is relatively small, and the impact of the execution efficiency of some SQL on the running efficiency of the program is not obvious, and development and operation and maintenance personnel cannot judge how efficient SQL is on the running efficiency of the program. Therefore, special optimization of SQL is rarely carried out, and as a result, SQL is rarely optimized. 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, it is necessary to optimize SQL.

2. Some common methods of SQL optimization

1. To optimize the query, you should try to avoid full table scans. First, you should consider where and order by. Create an index on the column.

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 set on num The default value is 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 the != or operator in the where clause, otherwise the engine will be abandoned. Index and perform 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

You can query like this:

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 a full table scan, such as:

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

For continuous values, if you can use between, do not use 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. You should try to avoid expression operations on fields in the where clause, which will Causes 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. 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:

select id from t where substring(name,1,3)='abc'--name以abc开头的id

should be changed to:

select id from t where name like 'abc%'

9. Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the where clause, otherwise the system will Indexes may not be used correctly.

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

11. Do not write meaningless queries. For example, if you need to generate an empty table structure:

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

This type of code will not return any result set, but it will consume system resources and should be changed. Like this:

create table #t(...)

12. Many times it is a good choice to use exists instead of in:

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 It is valid for all queries. SQL optimizes queries based on the data in the table. When there is a large amount of duplicate data in the index column, the SQL query may not use the index. For example, if there is a field sex in a table, almost half of it is male and half of female. Then Even if an index is built on sex, it will not affect query efficiency.

14. The more indexes, the better. Although the index can improve the efficiency of the corresponding select, it also reduces the efficiency of insert and update.

Because it is possible to insert or update The index will be rebuilt, so how to build the index needs to be carefully considered and depends on the specific situation.​

It is best not to have more than 6 indexes on a table. If there are too many, you should consider whether it is necessary to build indexes on some columns that are not commonly used.​

15. 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 for numeric types, only one comparison is enough.

16. 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.

17. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any unused fields.​

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

19. Temporary tables are not unusable, and using them appropriately can make certain routines more efficient, for example, when you need to repeatedly reference a certain data set in a large table or a commonly used table. However, for one-off events, it's better to use an export table.

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 improve the speed; if the amount of data Not big. In order to alleviate the resources of the system table, you should first create the table and then insert.

21. If temporary tables are used, all temporary tables must be explicitly deleted at the end of the stored procedure, first truncate table, and then drop table. This can avoid long-term locking of system tables.

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

23. Before using the cursor-based method or the temporary table method, you should first look for a set-based solution to solve the problem. The set-based method is usually more effective.

24. Like temporary tables, cursors are not unusable. Using FAST_FORWARD cursors with small data sets is often better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data.

Routines that include a "total" in the result set are usually faster than using a cursor. If development time permits, you can try both the cursor-based method and the set-based method to see which method 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, you should consider whether the corresponding requirements are reasonable.

Related recommendations: "PHP Tutorial", "mysql Tutorial"

The above is the detailed content of What are the commonly used methods for sql optimization?. For more information, please follow other related articles on the PHP Chinese website!