In order to improve query efficiency in SQL queries, we often take some measures to query statements for SQL optimization, some methods summarized below, there is a need for reference. 1. To optimize the query, avoid full table scan as far as possible, and first consider creating indexes on the columns involved in WHERE and ORDER by. 2. Avoid null values in the WHERE clause. Otherwise, the engine will abandon the use of index and perform full table scan. Select id from t where num is null select id from t where num=0 3 Use in where clauses should be avoided! = or <> otherwise the engine will abandon the index for a full table scan. Select id from t where num=10 or num=20 select id from t where num=10 or num=20 Select id from t where num=10 union all select id from t where num=20 5. Select id from t where num in(1,2,3); select id from t where num between 1 and 3. Select id from t where name like ‘% ABC %’ 7 Expression operations on fields in the WHERE clause should be avoided as much as possible, which can cause the engine to abandon indexes for a full table scan. Select id from t where num/2=100 select id from t where num/2=100 *2 8 Try to avoid functional manipulation of fields in the WHERE clause, which will cause the engine to abandon indexes for full table scans. Select id from t where substring(name,1,3)=’ ABC ‘–name select id from t where name like ‘ABC %’ 9 Do not perform functions, arithmetic operations, or other expression operations to the left of the “=” in the WHERE clause, or the system may not use the index properly. 10. When using an index field as a condition, if the index is a compound index, the first field in the index must be used as a condition to ensure that the system can use the index. Otherwise, the index will not be used, and the field order should be as consistent as possible with the index order. Select col1,col2 into #t from t where 1=0; select col1,col2 from t where 1=0; create table #t(…) Select num from a where num in(select num from b); select num from a where exists(select 1 from b where num=a.num) 13. Not all indexes are effective for queries. SQL queries are optimized according to the data in the table. When there is a large number of repeated data in the index column, SQL queries may not use indexes. 14. More indexes are not always better. While indexes can improve the efficiency of select operations, they can also reduce the efficiency of insert and update operations. The number of indexes in a table should not exceed 6. If there are too many, you should consider whether it is necessary to build indexes on infrequently used columns. 15. Use numeric fields as much as possible. If only numeric fields contain numeric information, do not use character fields, which reduces query and connection performance and increases storage overhead. This is because the engine compares each character in the string one by one while processing queries and joins, whereas for numeric types it only needs to compare once. 16. Use vARCHar instead of char whenever possible because it saves storage space and is obviously more efficient to search within a relatively small field for a query. 17. Do not use select * from t anywhere, replace “*” with a list of specific fields, and do not return any fields that are not needed. 18. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. 19. Temporary tables are not unusable, and their proper use can make some routines more efficient, for example, when a large table or a data set in a commonly used table needs to be referenced repeatedly. However, for one-off events, it is best to use exported tables. 20. When creating a temporary table, if a large amount of data is inserted at a time, you can use Select INTO instead of create table to avoid creating a large number of logs and improve the speed. If the amount of data is small, to reduce the resources of the system table, create table first, then insert. 21. If temporary tables are used, you must explicitly delete all temporary tables at the end of the stored procedure, truncate TABLE first, and then DROP TABLE. In this way, system tables cannot be locked for a long time. 22. Avoid using cursors as they are inefficient, and you should consider rewriting them if they operate on more than 10,000 rows. 23. Before using a cursor based approach or a temporary table approach, look for a set based solution to solve the problem, which is usually more efficient. 24. As with temporary tables, cursors are not unusable. Using the FAST_FORWARD cursor for small data sets is generally preferable to other line-by-line processing methods, especially if you have to reference several tables to get the data you want. Routines that include “totals” in the result set are generally faster to execute than those that use cursors. If development time allows, both the vernior-based approach and the set-based approach can be tried to see which works better. 25. Avoid large transaction operations and improve system concurrency. 26. Try to avoid returning a large amount of data to the client. If the amount of data is too large, consider whether the corresponding demand is reasonable. Quotations from blog: https://blog.csdn.net/tian31233/article/details/52052963
For more technical advice: Gzitcast