Summary of commonly used mysql optimization sql statement query methods

1. To optimize the query, try to avoid full table scans. First, consider creating indexes on the columns involved in where and order by.

2. Try to avoid using != or <> operators in the where clause, otherwise the engine will give up using the index and perform a full table scan.

3. Try to avoid making null value judgments on fields in the where clause, otherwise the engine will give up using the index and perform a full table scan, such as:

select id from t where num is null

Can be used in Set the default value 0 on num, make sure there is no null value in the num column in the table, and then query like this:

select id from t where num=0

4. Try to avoid using or in the where clause to connect conditions, otherwise This will cause the engine to give up using the index and perform a full table scan, such as:

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

can be queried like this:

select id from t where num=10 
union all 
select id from t where num=20

5. The following query will also cause a full table scan:

select id from t where name like '%abc%'

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

6.in and not in should also 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, if you can use between, do not use in:

select id from t where num between 1 and 3

7. If parameters are used in the where clause, it will also cause a full table scan. Because SQL resolves local variables only at runtime, the optimizer cannot defer selection of an access plan until runtime; it must make the selection at compile time. However, if the access plan is built at compile time, the value of the variable is still unknown and cannot be used as input for index selection. For example, the following statement will perform a full table scan:
select id from t where num=@num
You can change it to force the query to use an index:

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

8. Try to avoid using the where subtitle Perform expression operations on fields in the sentence, which will cause the engine to give up using the index and perform a full table scan. For example:

select id from t where num/2=100

should be changed to:

select id from t where num=100*2

9. Try to avoid performing functional operations on fields in the where clause, which will cause the engine to give up using the index and Perform a full table scan. For example:

select id from t where substring(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

should be changed to:

select id from t where name like 'abc%' 
select id from t where createdate>=&#39;2005-11-30&#39; and createdate<&#39;2005-12-1&#39;

10. Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the where clause. Otherwise the system may not be able to use the index correctly.

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

12. Do not write meaningless queries, such as generating an empty table structure:

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

This type of code will not return any result set, but will consume system resources. , should be changed to this:

create table #t(...)

13. Many times it is a good choice to use exists instead of in:

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 is a large amount of duplicate data in the index column, the SQL query may not use the index. For example, if there is a field sex in a table, and almost half are male and half female, then even if an index is built on sex, it will not have any effect on query efficiency.

15. The more indexes, the better. Although the index can improve the efficiency of the corresponding select, it also reduces the efficiency of insert and update, because the index may be rebuilt during insert or update, so what? Indexing requires careful consideration and will depend on the circumstances. It is best not to have more than 6 indexes on a table. If there are too many, you should consider whether it is necessary to build indexes on some columns that are not commonly used.

16. Avoid updating clustered index data columns as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the order of the entire table records will be adjusted. It consumes considerable resources. If the application system needs to frequently update clustered index data columns, then you need to consider whether the index should be built as a clustered index.

17. Try to use numeric fields. If fields that only contain numerical information try not to design them as character fields. This will reduce the performance of queries and connections, and increase storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections, and only one comparison is enough for numeric types.

18. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, variable length fields have small storage space and can save storage space. Secondly, for queries, search efficiency in a relatively small field is high. Obviously higher.

19. Do not use select * from t anywhere, replace "*" with a specific field list, and do not return any unused fields.

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

21. Avoid frequently creating and deleting temporary tables to reduce the consumption of system table resources.

The above is the detailed content of Summary of commonly used mysql optimization sql statement query methods. For more information, please follow other related articles on the PHP Chinese website!