Improve 30 SQL query optimization methods for 10 million level MySQL data

Time:2021-1-26

See what you use:

1. To optimize the query, we should try to avoid full table scanning. First, we should consider building indexes on the columns involved in where and order by.

2. Try to avoid judging null value of field in where clause, otherwise it will cause the engine to give up using index and scan the whole table. For example: 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 in num column in the table, and then query like this: select id from t where num = 0

3. Avoid using the! = or < > operators in the where clause, otherwise the engine will give up the index and scan the whole table.

4. Try to avoid using or to join conditions in where clause, otherwise the engine will give up using index and scan the whole table, for example: 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

http://5.inFor example: select id from t where num in (1,2,3) for continuous values, use between instead of in: select id from t where num between 1 and 3

6. The following query will also lead to full table scan: select id from t where name like ‘Li%’ to improve efficiency, full text search can be considered.

7. If parameters are used in the where clause, it will also result in a full table scan. Because SQL only resolves local variables at run time, the optimizer cannot postpone the selection of access plan until run time; it must make the selection at compile time. However, if an access plan is created at compile time, the value of the variable is unknown and cannot be used as an input for index selection. For example, the following statement will scan the whole table: select id from t where [email protected] You can force the query to use an index instead: select id from t with (index name)) where [email protected]

8. We should try to avoid the expression operation on the field in the where clause, which will cause the engine to give up the index and scan the whole table. For example, select id from t where num / 2 = 100 should be changed to select id from t where num = 100 * 2

9. We should try to avoid the function operation on the field in the where clause, which will cause the engine to give up the index and scan the whole table. For example: select id from t where substring (name, 1,3) =’abc ‘, name is the ID that starts with ABC
It should be changed to:
select id from t where name like ‘abc%’

10. Do not perform function, arithmetic operation or other expression operation on the left side of “=” in where clause, otherwise the system may not be able to use index correctly.

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

12. Don’t write meaningless queries, such as generating an empty table structure: select col1, col2 into # t from t where 1 = 0
This kind of code will not return any result set, but will consume system resources
create table #t(…)

13. It is a good choice to use exists instead of in many times: 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 effective for queries. SQL optimizes queries based on the data in the table. When there are a large number of duplicate data in the index column, SQL queries may not use the index. For example, if there are almost half of the fields sex, male and female in a table, even if the index is built on sex, the query efficiency will not be affected.

15. The more indexes, the better. Indexes can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update. Because the index may be rebuilt during insert or update, how to build an index needs careful consideration, depending on the specific situation. The number of indexes in a table should not exceed 6. If it is too many, we should consider whether it is necessary to build indexes on some infrequently used columns.

16. We should avoid updating clustered index data column as far as possible, because the order of clustered index data column is the physical storage order of table records. Once the value of the column changes, the order of the whole table records will be adjusted, which will consume considerable resources. If the application system needs to update the clustered index data column frequently, it needs to consider whether the index should be built as a clustered index.

17. Try to use numeric field. If the field only contains numeric information, try not to design it as character type. This will reduce the performance of query and connection, and increase the storage overhead. This is because the engine will compare each character in the string one by one when processing queries and joins. For numeric type, only one comparison is enough.

18. Use varchar / nvarchar instead of char / nchar as much as possible, because firstly, the storage space of the variable length field is small, which can save the storage space. Secondly, for the query, the search efficiency in a relatively small field is obviously higher.

19. Do not use select * from t anywhere, replace “*” with a specific field list, and do not return any fields that cannot be used.

20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index).

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

22. Temporary tables are not unusable. Proper use of them can make some routines more effective, for example, when it is necessary to repeatedly refer to a large table or a dataset in a common table. However, for one-time events, it is best to use the export table.

23. When creating a new temporary table, if there is a large amount of data to be inserted at one time, you can use select into instead of create table to avoid a large number of logs and improve the speed; if the amount of data is small, in order to ease the resources of the system table, you should first create table 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, then drop table. This can avoid long-term locking of system tables.

25. Avoid using cursors as much as possible, because the efficiency of cursors is poor. If the data operated by cursors exceeds 10000 rows, then rewriting should be considered.

26. Before using cursor based method or temporary table method, we should find a set based solution to solve the problem, which is usually more effective.

27. Like temporary tables, cursors are not unusable. Using fast for small datasets_ Forward cursors are usually better than other row by row methods, especially when you have to refer to several tables to get the data you need. Routines that include “totals” in the result set are usually faster to execute than using cursors. If development time permits, cursor based method and set based method can be tried to see which method is better.

28. Set NOCOUNT on at the beginning of all stored procedures and triggers and set NOCOUNT off at the end. There is no need to send done to the client after executing each statement of stored procedures and triggers_ IN_ Proc message.

29. Try to avoid big transaction operation and improve system concurrency.

30. Try to avoid returning large amount of data to the client. If the amount of data is too large, we should consider whether the corresponding demand is reasonable.

 

For those interested in PHP back-end technology and PHP architecture technology, my official group is 1023755567Click here Learn together and discuss with each other.
There is already management in the group to sort out the knowledge system (source code, learning video and other materials). You are welcome to get it for free.

 

 

PHP advanced learning mind map, interview; free access to documents and video resources