Introduction to SQL statement optimization

1, to optimize the query, should try to avoid full table scan, first of all should consider the where and order by the columns involved in the index

2, you should try to avoid null values for the field in the WHERE clause, NULL is the default when creating the table, but most of the time you should use NOT NULL, or use a special value such as 0,-1 as the default value.

3, try to avoid using the! = or <> operator in the WHERE clause, and MySQL uses the index only for the following operators:<,<=,=,>,>=,between,in, and sometimes like.

4, you should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon the use of indexes and full table scan, you can use the UNION Merge query: Select ID from t where num =10 UNION ALL select IDs from T where num=20

5,in and not in also need to use caution, otherwise it will result in a full table scan, for continuous values, you can use between do not use in: Select ID from t where num between 1 and 3

6, The following query will also cause a full table scan: select ID from t where name like '%abc% ' or select ID from t where nam E like '%abc ' to improve efficiency, full-text search can be considered. the Select ID from the t where name like ' abc% ' is used for the index

7, If you use a parameter in the WHERE clause, it also causes a full table scan.

8, you should try to avoid the field in the WHERE clause expression operation, should try to avoid the field in the WHERE clause function operation

9, many times replacing in with exists 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)

Index, Although it can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update, because insert or update may be rebuilt index, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

One, you should avoid updating the clustered index data columns as much as possible , because the order of the clustered index data columns is the physical storage order of the table records, and once the column values change, the order of the entire table records will be adjusted, which can consume considerable resources. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index.

Use a numeric field as much as possible , and if a field that contains only numeric information is not designed to be a character type, it can degrade query and connection performance and increase storage overhead.

As far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field in the search efficiency is obviously higher.

It is best not to use "*" to return all: SELECT * from T, Replace "*" with a specific field list, and do not return any fields that are not available.

To Avoid returning large amounts of data to the client, if the amount of data is too large, you should consider whether the corresponding demand is reasonable.

16,

Introduction to SQL statement optimization