optimization of MySQL SQL statements

Learn for yourself only

The conclusion is written in front:

1, try to avoid the full table scan, you can set the where and order by the columns involved in the index

2, try to use in the WHERE clause! = or <> operator because it causes the engine to discard the index and perform a full table scan

3. Try to avoid null judgments in the WHERE clause (e.g., select ID from t where-is null)

4, try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to discard the index and perform a full table scan (for example: SELECT ID from t where num=10 or NUM=20, you can union all:select ID fro M t where num=10 Union All select IDs from T where num=20)

5, try to avoid the use of 2 in the like query to query, otherwise full table scan (such as: Select ID from the t where name like '%abc% ')

6, try to avoid using in and not in the WHERE clause to query, otherwise full table scan (such as: Select ID from t where num in (), you can use the Between:select ID from t where num BETW Een 1 and 3)

7, try to avoid the field in the WHERE clause expression operation, otherwise full table scan (such as:select ID from t where num/2=100, can: Select ID from t where num=100*2 /c8>)

8, try to avoid the field in the WHERE clause function operation, otherwise full table scan

9. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee 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.

10, many times with exists instead of in is a good choice (such as: Select num from a where num in (select num from B), can be replaced by: Select Num from a where exists (SE Lect 1 from B where num=a.num))

11. Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.

optimization of MySQL SQL statements