Shulou(Shulou.com)06/01 Report--

Editor to share with you about mysql sql optimization methods, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

Mysql sql optimization methods: 1, to avoid full table scan, the columns involved in where and order by to build an index; 2, in the where clause to avoid the field null value judgment, avoid using the "! =" or "" operator, avoid using or to connect conditions; 3, be careful to use in and not in.

Common Optimization Strategies of SQL in MySQL

1 avoid full table scanning

In order to optimize the query, we should avoid full table scanning as far as possible, and we should first consider establishing indexes on the columns involved in where and order by.

2 avoid judging null values

Try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to abandon the use of the index and perform a full table scan, such as:

Select id from t where num is null

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

Select id from t where num=0

(3) avoid unequal judgment

Avoid using the! = or operator in the where clause as much as possible, otherwise the engine will give up using the index and do a full table scan.

4 avoid using or logic

Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans, such as:

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

You can query it like this:

Select id from t where num=10union allselect id from t where num=20

5 be careful with in and not in logic

In and not in should also be used with caution, otherwise it will lead to full table scanning, such as:

Select id from T1 where num in (select id from T2 where id > 10)

At this point, the outer query scans the whole table without using indexes. Can be modified to:

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 the query efficiency.

6 pay attention to fuzzy query

The following query will also cause a full table scan:

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

If a fuzzy query is a necessary condition, you can use select id from t where name like 'abc%' to implement the fuzzy query, and the index will be used. If header matching is necessary logic, it is recommended to use full-text search engines (Elastic search, Lucene, Solr, etc.).

7 avoid field calculation in query conditions

Expression manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to abandon the use of indexes and perform full table scans. Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

8 avoid functional operations on fields in query conditions

Functional manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to abandon the use of indexes and perform full table scans. Such as:

Select id from t where substring (name,1,3) = 'abc'--name id that begins with abc

It should be changed to:

Select id from t where name like 'abc%'

9 WHERE clause "=" pay attention to the left

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

10 combinational index usage

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 a condition to ensure that the system uses the index, otherwise the index will not be used. and the order of the fields should be consistent with the order of the index as far as possible.

11 do not define unobjectionable queries

Don't write meaningless queries, such as generating an empty table structure:

Select col1,col2 into # t from t where 1: 0

This type of code does not return any result sets, but consumes system resources, and should be changed to this:

Create table # t (...)

12 exists

In many cases, using exists instead of in is a good choice:

Select num from a where num in (select num from b)

Replace it with the following statement:

Select num from a where exists (select 1 from b where num=a.num)

13 the index may also fail

Not all indexes are valid for the query. SQL optimizes the query according to the data in the table. When there are a large number of duplicate data in the index column, the SQL query may not use the index. For example, if there are fields sex,male and female in a table, then even if the index is built on the sex, it will not play a role in the query efficiency.

14 form field type selection

Try to use numeric fields, and try not to design character fields that contain only numeric information, which will reduce the performance of queries and connections, and increase storage overhead. This is because the engine compares each character in the string one by one when processing queries and connections, while for numeric types, it only needs to be compared once.

Use varchar instead of char as much as possible, because first of all, the storage space of variable length fields is small, which can save storage space, and secondly, for queries, searching in a relatively small field is obviously more efficient.

15 Fields in query syntax

Don't use select * from t anywhere, replace "*" with a specific list of fields, and don't return any fields that you don't need.

16 Index independent optimization

Do not use *, try not to use keywords such as union,union all, try not to use or keywords, and try to use equivalent judgment.

No more than 5 table connections are recommended. If there are more than 5, consider the design of the form. (in Internet applications)

Table connection is better than inline in the use of outreach.

There is basic data in the external connection. Such as: A left join B, the basic data is A.

An inner join B, if there is no basic data, first use Cartesian product to complete the full join, and then get the inner join result set according to the join condition.

Big data order of magnitude of the table to do paging query, if the number of pages is too large, then use subqueries to complete the paging logic.

Select * from table limit 1000000, 10

Select * from table where id in (select competes for from table limit 100000, 10)

The above is all the contents of mysql's sql optimization method, thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.