Mysql database sql statement commonly used optimization methods

Mysql database sql statement commonly used optimization methods

1. Query optimization, should try to avoid full table scan, should first consider where and order by the columns involved in the establishment of the index.

2. Should be avoided in the where clause on the field null value judgment, otherwise it will cause the engine to abandon the use of indexes and full table scan, such as:

select id from t where num is null

You can set the default value of num 0, num column to ensure that no null value in the table, and then query:

select id from t where num = 0

3. Should be avoided in the where clause! = Or <> operator, otherwise the engine to abandon the use of indexes and full table scan.

4. Should be avoided in the where clause or to connect conditions, otherwise it will cause the engine to abandon the use of indexes and full table scan, such as:

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

You can query this:

select id from t where num = 10

union all

select id from t where num = 20

5. In and not in should 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, you can not use in between:

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%'

To improve efficiency, you can consider full-text search.

7. If the parameters used in the where clause, will lead to a full table scan. Because SQL parses local variables only at runtime, the optimizer can not defer the choice of access plan to the runtime; it must be selected at compile time. However, if you build an access plan at compile time, the value of the variable is unknown and can not be used as an indexed input. As the following statement will be full table scan:

select id from t where num = @ num

Can be used to force the query index:

select id from t with (index (index)) where num = @ num

8. Should be avoided in the where clause on the field expression operation, which will cause the engine to abandon the use of indexes and full table scan. Such as:

select id from t where num / 2 = 100

Should be changed to:

select id from t where num = 100 * 2

9. Should be avoided in the where clause on the field function operation, which will cause the engine to abandon the use of indexes and full table scan. Such as:

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' generated id

Should be changed to:

select id from t where name like 'abc%'

select id from t where createdate> = '2005-11-30' and createdate <'2005-12-1'

10. Do not function, arithmetic or other expressions on the left of the "=" in the where clause, otherwise the system may not be able to properly use the index.

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

12. Do not write some meaningless query, such as the need to generate an empty table structure:

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

Such code will not return any result set, but will consume system resources, should be replaced by this:

create table #t (...)

13. Often used to replace 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 the query, SQL is based on the data in the table to query optimization, when the index column has a large number of data duplication, the SQL query may not be to use the index, such as a table field sex, male, Almost half of the female, then even if the index was built on sex also ineffective on the efficiency of the search.

15. The index is not the better, the index can certainly improve the efficiency of the corresponding select, but also reduces the efficiency of insert and update, because insert or update may rebuild the index, so how to build the index needs careful consideration, As the case may be. A table index is best not more than six, if too many should consider some of the infrequently used index built on the column is necessary.

16. Whenever possible, avoid updating clustered indexed data columns because the order of the clustered indexed data columns is the physical storage order of the table records, which can cost considerable resources once the column value changes will cause the order of the entire table to be adjusted. If the application system needs frequent updates clustered index data column, then need to consider whether the index should be clustered index.

17. Try to use the number of fields, if only the numerical information field try not to design as a character, which will reduce the performance of queries and connections, and will increase storage costs. This is because the engine compares each character in the string one by one as it processes queries and joins, but only needs to compare once for the numeric type.

18. As far as possible the use of varchar / nvarchar instead of char / nchar, because the first variable-length field storage space, you can save storage space, followed by the query, in a relatively small field search efficiency is obviously higher.

19. Do not use select * from t anywhere, with a specific list of fields instead of "*", do not return to any field can not 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, and the proper use of them can make some routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-off events, it is best to use export tables.

23. In the new temporary table, if the one-time insert a large amount of data, you can use select into instead of create table, to avoid causing a large number of log, in order to speed up; if the amount of data is not large, in order to ease the system table resources should be create table, then insert.

24. If you use a temporary table, the end of the stored procedure must be explicitly deleted all the temporary table, first truncate table, and then drop table, so you can avoid the system table for a long time to lock.

25. Try to avoid the use of cursors, cursors because the less efficient, if the cursor operation data more than 10,000 lines, then you should consider rewriting.

26. Before using a cursor-based or temporary table approach, you should first look for a solution based on a set solution to a problem, and a set-based approach is usually more efficient.

Like temporary tables, cursors are not unusable. Using FAST_FORWARD cursors for small data sets is usually better than other progressive methods, especially when you have to reference a few tables to get the data you need. Routines that include "totals" in the result set are usually faster than cursors. If development time permits, both cursor-based and collection-based methods can be tried to see which one works better.

Set SET NOCOUNT ON at the beginning of all stored procedures and triggers, and SET NOCOUNT OFF at the end. You do not need to send the DONE_IN_PROC message to the client after each statement that executes the stored procedure and the trigger.

29. Try to avoid big business operations and improve system concurrency.

30. try to avoid returning large amounts of data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.