How does mysql perform sql optimization?

Mysql SQL optimization method: 1. Avoid full table scans and create indexes on the columns involved in where and order by; 2. Avoid null value judgments on fields in the where clause and avoid using " !=" or "<>" operators, avoid using or to connect conditions; 3. Use in and not in with caution.

Common SQL optimization strategies in MySQL

1 Avoid full table scan

To optimize the query, you should try to avoid full table scans. You should first consider creating indexes on the columns involved in where and order by.

2 Avoid judging null values
You should try to avoid judging null values ​​for fields in the where clause, otherwise the engine will give up using the index and proceed. Full table scan, such as:

select id from t where num is null

You can set the default value 0 on num to ensure that there is no null value in the num column in the table, and then query like this:

select id from t where num=0

3 Avoid unequal value judgment

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

4 Avoid using or logic
You should try to avoid using or in the where clause to connect conditions, otherwise it will cause the engine to Abandon using the index and perform a full table scan, such as:

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

5 Use in and not with caution in logic
in and not in should also be used with caution, otherwise it will lead to a full table scan, such as:
select id from t1 where num in(select id from t2 where id > ; 10)
At this time, the outer query will scan the entire table without using indexes. It can be modified as:
select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
At this time, the index is used, which can significantly improve query efficiency.

6 Pay attention to fuzzy queries
The following query will also cause a full table scan:
select id from t where name like '�c%'
If fuzzy query is a necessary condition, you can use select id from t where name like 'abc%' to implement fuzzy query, and the index will be used. If header matching is necessary logic, it is recommended to use a full-text search engine (Elastic search, Lucene, Solr, etc.).

7 Avoid field calculations in query conditions
You should try to avoid performing expression operations on fields in the where clause. 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

## 8. Avoid performing functional operations on fields in query conditionsYou should 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'--id whose name starts with abc
should be changed to:
select id from t where name like 'abc% '

9 Pay attention to the left side of "=" in the WHERE clauseDo not do it on the left side of "=" in the where clause functions, arithmetic operations, or other expression operations, otherwise the system may not use the index correctly.

10 Use of composite indexWhen using the index field as a condition, if the index is a composite index, you must use Only when the first field in the index is used as a condition can the system use the index. Otherwise, the index will not be used, and the field order should be consistent with the index order as much as possible.

11 Do not define unobjectionable queriesDo not write meaningless queries. For example, you need to generate 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 it will consume system resources. It should be changed to this:
create table #t(. ..)

12 existsMany 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)

13 Indexes may also fail
Not all indexes are valid 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.

14 Table field type selection
Try to use numeric fields. If the fields contain only numerical information, try not to design them as characters. type, which reduces query and join performance and increases 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.
Use varchar instead of char as much as possible, because first of all, variable length fields have small storage space and can save storage space. Secondly, for queries, the search efficiency in a relatively small field is obviously higher.

15 Fields in query syntax
Do not use select * from t anywhere, use a specific field list instead of " *", do not return any fields that are not used.

16 Index-independent optimization
Do not use *, try not to use union, union all and other keywords, try not to use or Keywords, try to use equivalent judgment.

It is recommended that the number of table connections should not exceed 5. If there are more than 5, consider the design of the table. (In Internet applications)

For table connection methods, external links are better than inline ones.
Basic data exists in outer connections. For example: A left join B, the basic data is A.
A inner join B, if there is no basic data, first use Cartesian product to complete the full join, and then obtain the inner join result set according to the connection conditions.

When performing paging queries on large data-level tables, if the number of page numbers is too large, use subqueries to complete the paging logic.
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)

The above is the detailed content of How does mysql perform sql optimization?. For more information, please follow other related articles on the PHP Chinese website!