Summary of common SQL statement optimization techniques [classic] and SQL statement techniques classic

Summary of common SQL statement optimization techniques [classic] and SQL statement techniques classic

This example summarizes common SQL statement optimization techniques. We will share this with you for your reference. The details are as follows:

In addition to creating indexes, maintaining good SQL statement writing habits will reduce SQL Performance problems.

① Set parameters through Variables

Good:

stringsql = "select * from people p where p.id = ? ";

Bad:

stringsql = "select * from people p where p.id = "+id;

The SQL parsing and execution plan of the database will be saved in the cache, but the SQL file must be parsed as long as it changes.

"... The where p. id = "+ id method needs to be re-parsed when the id value changes, which takes time.

② Do not use select *

Good:

stringsql = "select people_name,pepole_age from people ";

Bad:

stringsql = "select * from people ";

If select * is used, the resolution time is increased, and unnecessary data is also queried. data transmission is also time-consuming,

For example, a text field is usually used to store complicated content. If select * is used, this field is also queried.

③ Use fuzzy search with caution

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 index of this column is invalid. If it does not start with %, the index of this column is valid.

④ Do not use column numbers

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";

When Column numbers are used, unnecessary parsing time is added.

⑤ Use union all first to avoid 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 records of query subsets, the speed of UNION is usually much slower than union all. In general, if union all can meet the requirements, you must use union all. There is another case where duplicate records are not allowed in the business.

⑥ Avoid calculation of 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";

After the operation is performed on the index column, the index will become invalid. The correct method should be to calculate the value before passing in.

7. Use 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, all the internal and external tables are scanned and no indexes are used. However, the not extsts subquery can still use table indexes.

Difference between exist and in

In is a hash connection between the external table and the internal table, while exists is a loop on the External table. Each loop then queries the internal table. Therefore, in uses the external index, and exists uses the internal table index.

If the two tables to be queried are of the same size, there is little difference between in and exists.

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

Example: Table A (small table) and Table B (large table)

1:

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

Low Efficiency: the cc column index of Table A is used;

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

High Efficiency: the cc column index of Table B is used.

2:

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

High Efficiency: the cc column index of Table B is used;

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

Low Efficiency: the cc column index of Table A is used.

Avoid doing the following in the index column:

◆ Avoid using index fields <> ,! =
◆ Avoid using is null and is not null in the index Column
◆ Avoid data type conversion on the index column (for example, if a field is of the String type and the parameter is of the int type)

When the above operation is performed on the index column, the index will become invalid, resulting in a full table scan.

You can consider splitting the complex operations into several steps.

Sometimes there is an example of how to implement complex services using an SQL statement. To implement complex services, you can nest multi-level subqueries. This causes SQL Performance problems. In this case, you can consider splitting the SQL statement by using multiple SQL statements, or hand over the work that can be done by some programs to the program.

PS: here we recommend two SQL online tools for your reference:

SQL online compression/formatting tools:
Http://tools.jb51.net/code/ SQL _format_compress

SQL code online formatting and beautification tools:
Http://tools.jb51.net/code/sqlcodeformat

I hope this article will help you design your database program.