Home > Article > Backend Development > SQL statement optimization principles, sql statement optimization_PHP tutorial
SQL statement optimization principles, sql statement optimization_PHP tutorial
- WBOYOriginal
- 2016-07-13 09:46:22759browse
SQL statement optimization principles, sql statement optimization
Methods to improve query speed when processing data of more than one million levels:
1. You should try to avoid using != or <> operators in the where clause, otherwise the engine will give up using the index and perform a full table scan.
2. To optimize the query, you should try to avoid full table scans. You should first consider creating indexes on the columns involved in where and order by.
3. You should try to avoid judging the null value of fields in the where clause, otherwise the engine will give up using the index and perform a full table scan,
For example:
select id from t where num is null
You can set the default value 0 on num to ensure that there is no null in the num column in the table value, and then query like this:
select id from t where num=0
4. Should 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 .The following query will also result in a full table scan: (cannot precede the percent sign)
select id from t where name like '�c%'
If To improve efficiency, consider full-text search.
6.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, don’t use in Now:
select id from t where num between 1 and 3
select '13891030091' phone union select '13992085916' ………… UNION SELECT '13619100234' ) b
on a.Phone=b.phone
--Replace the following When a lot of data is separated
in('13891030091','13992085916','13619100234' …………)
7. If parameters are used in the where clause, it will also cause a full table scan. Because SQL resolves local variables only at runtime, the optimizer cannot defer selection of an access plan until runtime; it must make the selection at compile time. However, if the access plan is built at compile time, the value of the variable is still unknown and cannot be used as an input for index selection. For example, the following statement will perform a full table scan:
select id from t where num=@num You can change it to force the query to use an index:
select id from t with( index(index name)) where num=@num
8. You should try to avoid expression operations on fields in the where clause, which will cause the engine to Abandon using indexes 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
9. Should be avoided as much as possible Performing functional operations on fields in the where clause 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 id starting with abc
select id from t where datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ The generated id
should be changed to:
select id from t where name like 'abc%'
select id from t where createddate>='2005-11- 30′ and createdate<'2005-12-1′
10.Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the where clause, otherwise the system may not be able to use the index correctly.
11. 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. Only in this way can we ensure that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.
12. Do not write some 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 will consume system resources. It should be changed to this ：
create table #t(…)
13. 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)
14. Not all indexes are valid for queries, SQL is Query optimization is 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, male and female are almost equal, then even if an index is built on sex It also has no effect on query efficiency.
15. The more indexes, the better. Although indexes can improve the efficiency of the corresponding select, they also reduce the efficiency of insert and update, because The index may be rebuilt during insert or update, 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.
16. You should avoid updating clustered index data columns as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the order of the entire table records will be changed. Adjustment will consume considerable resources. If the application system needs to frequently update clustered index data columns, then you need to consider whether the index should be built as a clustered index.
17. Try to use numeric fields. If fields that only contain numerical information try not to design them as character fields. This will reduce the performance of queries and connections, and Will 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.
18. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, variable length fields have small storage space, which can save storage space, and secondly, for queries That said, searching within a relatively small field is obviously more efficient.
19. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any unused fields.
20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the indexes are very limited (only primary key indexes).
21. Avoid frequently creating and deleting temporary tables to reduce the consumption of system table resources.
22. Temporary tables are not unusable, and using them appropriately can make certain routines more efficient, for example, when large tables or frequently used tables need to be referenced repeatedly. a data set in the table. However, for one-time events, it is better to use export tables.
23. When creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid causing a large number of logs. Improve speed; if the amount of data is not large, in order to alleviate the resources of the system table, you should create table first and then insert.
24. 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, so Longer locking of system tables can be avoided.
25. 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.
26. 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. .
27. Like temporary tables, cursors are not unusable. Using FAST_FORWARD cursors for small data sets is often superior to other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. Routines that include "totals" in a 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.
28. Set SET NOCOUNT ON at the beginning of all stored procedures and triggers, and set SET NOCOUNT OFF at the end. There is no need to send a DONE_IN_PROC message to the client after each statement of stored procedures and triggers.
29. 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.
30. Try to avoid large transaction operations and improve system concurrency.
Article source: http://www.cnblogs.com/pepcod/archive/2013/01/01/2913496.html
Article reference for optimizing sql:
http://www.cnblogs.com/ATree/archive/2011/02/13/sql_optimize_1.html
http://blog.csdn.net/csh624366188/article/details/8457749
http://www.iteye.com/problems/100945
http://blog.itpub.net/28389881/viewspace-1301549/