Shulou(Shulou.com)05/31 Report--

This article will explain in detail about the commonly used SQL sentence optimization skills, the editor thinks it is very practical, so share it for you to do a reference, I hope you can get something after reading this article.

The details are as follows:

In addition to indexing, maintaining good SQL statement writing habits will reduce SQL performance problems.

① sets parameters by means of variables

Good:

Stringsql = "select * from people p where p.id =?"

Bad:

Stringsql = "select * from people p where p.id =" + id

The SQL text parsing and execution plan of the database are saved in the cache, but the SQL text has to be reparsed whenever there is a change.

The "... where p.id =" + id mode requires reparsing when the id value changes, which can be time-consuming.

② do not use select *

Good:

Stringsql = "select people_name,pepole_age from people"

Bad:

Stringsql = "select * from people"

Using select * will not only increase the parsing time, but also query the unneeded data, and data transmission is also time-consuming.

For example, a field of type text is usually used to save some complicated things. If you use select *, it will also be queried.

③ is cautious in using fuzzy queries

Good:

Stringsql = "select * from people p where p.id like 'parm1%'"

Bad:

Stringsql = "select * from people p where p.id like'% parm1%'"

When a fuzzy match starts with%, the column index is invalid, and if it does not start with%, the column index is valid.

Do not use column numbers for ④

Good:

Stringsql = "select people_name,pepole_age from people order by name,age"

Bad:

Stringsql = "select people_name,pepole_age from people order by 6, 8"

Using column numbers will increase unnecessary parsing time.

⑤ gives priority to UNION ALL and avoids the use of UNION

Good:

Stringsql = "select name from student union all select name from teacher"

Bad:

Stringsql = "select name from student union select name from teacher"

Because UNION compares the records of each query subset, it is usually much slower than UNION ALL. In general, be sure to use UNION ALL if you can meet the requirements with UNION ALL. There is also a situation where business can ensure that there are no duplicate records.

⑥ avoids calculating index fields in where or order by statements

Good:

Stringsql = "select people_name,pepole_age from people where create_date=date1"

Bad:

Stringsql = "select people_name,pepole_age from people where trunc (create_date) = date1"

When an operation is performed on an index column, the index is invalidated. The right thing to do is to calculate the value and then pass it in.

⑦ uses not exist instead of not in

Good:

Stringsql = "select * from orders where customer_name not exist (select customer_name from customer)"

Bad:

Stringsql = "select * from orders where customer_name not in (select customer_name from customer)"

If the query statement uses not in, then both the inside and outside of the table are scanned without using the index, while the subquery of not extsts can still use the index on the table.

The difference between ⑧ exist and in

In connects the outer table with the inner table as hash, while exists uses the external table as a loop loop, and each loop loop queries the inner table. Therefore, in uses the outer index, and exists uses the index of the inner table.

If the two tables of the query are of the same size, there is little difference between using in and exists.

If one of the two tables is small and the other is large, the large subquery table uses exists, and the small subquery table uses in:

For example: table A (small table), table B (large table)

1:

Select * from A where cc in (select cc from B)

Inefficient, using the index of the cc column on table A

Select * from A where exists (select cc from B where cc=A.cc)

Efficient, using the index of the cc column on table B.

2:

Select * from B where cc in (select cc from A)

Efficient, using the index of the cc column on table B.

Select * from B where exists (select cc from A where cc=B.cc)

Inefficient, using the index of the cc column on table A.

⑨ avoids doing the following on index columns:

◆ avoids using on index fields,! =

◆ avoids using IS NULL and IS NOT NULL on index columns

◆ avoids data type conversions on index columns (for example, a field is of String type and a parameter is of int type when passed in)

When you use the above operation on an index column, the index will fail, resulting in a full table scan.

⑩ complex operation can be properly broken down into several steps.

Sometimes there is an example of implementing a complex business through a SQL statement, nesting multi-level subqueries in order to implement a complex business. Causing SQL performance problems. In this case, you can consider splitting the SQL, through multiple SQL statements, or leaving some of the work that the program can do to the program.

About "commonly used SQL sentence optimization skills" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.