SQL statement Query Performance optimization

1. Introduction

In the early stage of application system development, because the database data is relatively small, for querying SQL statements, the use of indexes and the writing of complex views can not understand the performance of the SQL statement of various ways, but the application system in practice, with the data in the database increases, The response speed of the system is one of the most important problems that the system needs to solve at present. An important aspect of system optimization is the optimization of SQL statements. For the massive data, the speed difference between the inferior SQL statement and the high-quality SQL statement can reach hundreds of times, it can be seen that a system is not simply able to achieve its function, but to write high-quality SQL statements, improve the availability of the system. In the face of the massive data query, delete, update and insert the large amount of data in time, seize the main aspects needing optimization, and how to use the efficient SQL to start with the different situations.

2. Correct use of the index in a large data table, the basic each table has one or more indexes to ensure efficient query, the use of indexes need to follow the following usage principles:

Function () {$ ('. Ad-hidden '). Show ();});

Low efficiency:Select*FromTablewhere salary* > 25000;   Efficient:Select*FromTablewhere salary>25000/12select * from table1 where name=  ' zhangsan "and tID > 10000 efficient: Span style= "color: #0000ff;" >select * from table1  where TID > 10000 and Name= zhangsan  < Span style= "color: #0000ff;" >< Span style= "color: #808080;" >< Span style= "color: #808080;" >                 

 Efficient: select * from Span style= "color: #0000ff;" >table where Deptno >=4 
   inefficient:  select * from table where Deptno >         < Span style= "color: #800000;" >3               

Create  index  emp_i    on EMP (/* Create a function-based index

/* will use  the index

3. SQL Statement Performance optimization

Oracle uses a bottom-up sequence to parse where clauses, and according to this principle, the connections between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

Low efficiency:Select*FromTablewhere Salary>50000and Job= ' MANAGER 'and25< (SelectCount*)from table where mgr< Span style= "color: #808080;" >=tableselect * from table where 25 < ( Span style= "color: #0000ff;" >select count (from Span style= "color: #0000ff;" >table where mgr=table . Empno) and salary> 50000 and job =  ' MANAGER ';       

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition, for example, when the ETL process writes data to the model, it often needs to correlate about 10 dimension tables. The steps taken from Oracle to parse SQL with in in are different from SQL without in: Oracle tries to convert it to multiple tables, and if the conversion is unsuccessful, it executes the subquery in the inside, then queries the outer table record, and if the conversion succeeds, it directly uses the connection method of multiple tables. This shows that using in SQL at least one more conversion process, in this case, using exists instead of in will improve the efficiency of the query.

In a subquery, the NOT IN clause performs an internal sort and merge, and in either case, not is the least effective because it performs a full table traversal of the table in the subquery. Replacing not with not exists will improve the efficiency of the query.

3.4! = or <> operator (not equal to)

The greater than or less than the operator generally does not need to adjust, because it has an index will be indexed to find, but in some cases it can be optimized, such as a table has 1 million records, a numeric field A, 300,000 records of a=0,30 Records of the A=1,39 million records of a=2,1 Records of the a=3. There is a big difference between performing a>2 and a>=3, because Oracle finds the index of records for 2 and then compares them, while A>=3 Oracle locates the records index of =3 directly.

The efficiency of the group BY statement can be improved by filtering out unwanted records before group by.

 inefficient: select Job, from table group by Job having Job = ' President ' or Job = MANAGER "Efficient: Span style= "color: #0000ff;" >select Job, avg (Salary) from table =" President ' or Job = ' MANAGER ' group by Job       

For multi-table join operations, there are a number of situations where conditional use of union-all to replace the union presupposes that no primary key is the same record in the connected tables, because union all repeats the same record in two result sets. The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output. If you use UNION ALL instead of union, the order is not necessary and the efficiency is increased by 3-5 times.

Low efficiency:Select NameFromTablewhereSUBSTRING (ID,1, 1) = 'C'     Inefficient :Select name from  table where salary *  >   40000  

4. Other optimization methods

The query optimization method of database is not only the optimization of indexes and SQL statements, but also the rational use of other methods can optimize the function of database query. Let's go through a few simple and practical methods.

You should simplify or avoid repeating the ordering of large tables. The optimizer avoids sequencing steps when it is possible to automatically generate output in the appropriate order using the index. Here are some of the factors that affect:

1. One or several columns to be sorted are not included in the index

2. The order of the columns in the group BY or ORDER BY clause is different from the order of the indexes

3. Sorted columns from different tables in order to avoid unnecessary sorting, it is necessary to construct the indexes correctly and to properly merge the database tables (although sometimes it may affect the normalization of the table, but it is worthwhile to improve the efficiency). If sorting is unavoidable, you should try to simplify it, such as narrowing the range of sorted columns.

For the query optimization of massive data, we should grasp the key problems, write high-quality SQL statements and improve the usability of the system. When calculating the actual query processing cost, you must consider the time and space overhead of optimizing itself. If the cost of choosing the best solution is too high, it may outweigh the costs. In fact, the query optimization in the reasonable optimization processing cost, looking for a better solution, not necessarily the best solution 

SQL statement Query Performance optimization