Summary of SQL query optimization

2010-10-22  来源:本站原创  分类:Database  人气:104 

Recent projects require a higher data processing requirements, had to optimize the look, so do a consolidation, venture to throw a few bricks, hope to stay sort of people you pass by surprise. Expectations. .
SQL query optimization summary 20,101,022

Optimization requires a combination of the actual situation in their own database:
Sometimes because of the amount of data will affect the MSSQL on the same written statement query execution plan, which is in the non-clustered index is particularly evident, there is a single multi-CPU and CPU, in a multi-user concurrent cases, the same wording of the query execution plan will be different.
MSSQL to understand the implementation plan (graphics and text), by observing and analyzing the client software to MSSQL how to perform, read through the IO logic, by looking at the icon of the query plan, which through its implementation of the optimized SQL statement. This is the real way to optimize the SQL.
What is the situation from the clustered index? With the index and what not about? MSSQL subquery is how? IN with no index, LIKE not using index? Function with no index? OR, AND, UNION? Subqueries? These areas to try to figure out.

Here is part of the review: (to be improved)

A written specification on: sql need capital; in the java code to minimize the use-character "+" connection string

2 syntax specification at:

a
SELECT clause to avoid using '*':
ORACLE parsing process, will be '*' in order to convert all the column names, the work is done by querying the data dictionary, which means more time-consuming

b
Choose the most efficient order in the table name (only in the rule-based optimizer valid):
ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name, FROM clause written in the final table (base table driving table) will be the first treatment,
FROM clause in the case of multiple tables, you must select the least number of records as the basis of the table table. If there are more than 3 tables join query, it would need to select the cross-table (intersection table) as the underlying table, cross table is the table that is referenced by other tables.

c
WHERE clause in the order of the connections. :
ORACLE uses a bottom-up parsing WHERE clause in the order, according to this principle, the connection between the tables must be written before the other WHERE condition, that can filter out the maximum number of records written in the WHERE clause conditions must end. SQL is On the contrary, the use of bottom-up parsing WHERE clause of the order;

Two points in the b and c it should be noted, oracle and mssql is exactly the opposite. Table where the words in order and the smooth nature will be different.
d related to the Index:
Index is a concept part of the table, used to improve the efficiency of retrieving data, ORACLE uses a complex self-balancing B-tree structure. In general, query the data through the index faster than full table scan. When run the query and identify ORACLE Update The best path statement, ORACLE optimizer will use index. the same number of tables in the join index can also be used to improve efficiency. Another advantage of using the index is that it provides a primary key (primary key) the only validation. . Those LONG or LONG RAW data type, you can index almost all of the columns.
Typically, in a large table using the index are particularly effective. Of course, you will find a small table in the scan, use the index can also improve efficiency. While the use of the index can get the query efficiency, but we must also note that the cost of its . indexes need space to store, also need regular maintenance, when a record changes in the table or index column is modified, the index itself can be modified. This means that each record of the INSERT, DELETE, UPDATE will do to pay more than 4, 5 times the disk I / O. because the index requires additional storage space and processing
Index would bring unnecessary slow query response time.. Reconstruction of the index on a regular basis is necessary.
(1) Avoid the use of the indexed column NOT usually
We must avoid the use of the index column NOT, NOT in the index will be generated in and out of the use of the same function. When ORACLE "encounter" NOT, he will stop using the index instead perform a full table scan.
(2) Avoid the use of columns in the index calculation.
WHERE clause, if the index column is part of the function. Optimizer will not use the index and the use of full table scan.
(3) need to be careful of the WHERE clause:
Some of the SELECT statement WHERE clauses do not use the index. Here are some examples.
In the following example, (1 )'!=' will not use the index. Remember, the index can only tell you what exists in the table, but can not tell you what does not exist in the table. (2) '| |' is the character connection function. like any other function that, disable the index. (3) '+' is a mathematical function. just like that other mathematical functions, disable the index. (4) the same index columns can not be compared with each other, which will enable full table scan (such as the not so key).
(4)
Many are related to the operation of the following keywords.
e
Used> = alternative>

3 operators and keywords:

a
Use the DECODE function to reduce the processing time:
Scan using the DECODE function to avoid duplicate records or repeat the same connection the same table.

b
Replaced with TRUNCATE DELETE:
When you delete records in the table, under normal circumstances, rollback segments (rollback segments) can be used to store recovery information. If you do not COMMIT transaction, ORACLE will be deleted before the data back to the state (precisely return to the situation before the implementation of the Delete command) and when using TRUNCATE, the rollback will not store any information that can be restored. When the command runs, the data can not be restored. so few resources is called, the execution time will very short. (Translator: TRUNCATE to delete the whole table only applies, TRUNCATE is DDL not DML)

c
As much as possible to use COMMIT:
Whenever possible, use as much as possible in the program COMMIT, improve the performance of such procedures, the demand will be because the resources released by COMMIT reduced:
The resources released by COMMIT:
(1). Rollback on the information used to recover data.
(2.) Locks obtained by the program statement
(3). Redo log buffer space in the
(4). ORACLE 3 for the management of the internal resources to spend

d
HAVING clause is replaced with the Where clause:
Avoid using HAVING clause, HAVING retrieve all the records only after the fishes to filter the result set. The processing needs sorting, totaling and other operations. If through the WHERE clause limits the number of records that can reduce the cost of this . (non-oracle in the) on, where, having these three conditions can be added clause, on a first implementation, where second place, having finally, because on is to first filter the records do not meet the conditions only after statistics, it can reduce data center operations to deal with, arguably should be the speed is the fastest,
where should quickly than having, as it filters the data only after sum, when used in the two tables join on, so in a table, where the rest with having to compare. Single table query statistics in this case, if the conditions are not related to filter to calculate the field, and that their results are the same, but where Keyi use rushmore technology, and having not, in the slower speed of the latter If you are involved in the calculation of the field to that in the not calculated before, the value of this field is uncertain, according to articles written on the work process
where the role of time in the calculation done before, but after having that role in the calculation, so in this case, the two results will be different. In the multi-table join query, on earlier work than the where. System first join between tables based on various conditions, the synthesis of a temporary table more than one table, and then filtered by the where, and then calculate, calculate and then again from having to filter finished. Thus, in order to filter the right to play the role, we must first understand the conditions of work should be at what time, and then decided to put there

e
Replace with EXISTS IN, NOT EXISTS instead using NOT IN:
In many queries on the basis of the table, in order to satisfy a condition, often need to join to another table. In this case, use the EXISTS (or NOT EXISTS) will usually improve the query efficiency. In the sub-query, NOT IN clause will perform an internal sorting and merging. In either case, NOT IN is the least efficient (because of its sub-tables in the query performs a full table traverse). In order to avoid using NOT IN, we can rewrite it into outer join (Outer Joins) or NOT EXISTS.
Example:
(High) SELECT * FROM EMP (base table) WHERE EMPNO> 0 AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
(Inefficient) SELECT * FROM EMP (base table) WHERE EMPNO> 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')

f
Replaced with EXISTS DISTINCT:
When submitting a one to many table contains information (such as the department table and employee table) the query, to avoid the use clause in the SELECT DISTINCT. Generally consider EXIST replacement, EXISTS make the query more quickly, because the RDBMS kernel module will be Once the sub-query conditions are met, immediately returns the result. examples:
(Inefficient):
SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E
WHERE D. DEPT_NO = E. DEPT_NO
(High):
SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X'
FROM EMP E WHERE E. DEPT_NO = D. DEPT_NO);

g
Replace with UNION OR (for the index column)
Under normal circumstances, replace with a UNION OR WHERE clause will play a good effect. On the indexed column using OR will result in full table scan. Note that the above rule is only valid for multiple indexed columns. If there is not column index, query performance may be because you have no choice but reduce the OR. In the example below, LOC_ID and REGION are built on the index.
High:
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE REGION = "MELBOURNE"
Inefficient:
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = "MELBOURNE"
If you insist on using OR, then the index need to return to record at least written in the front row.

h
Used to replace OR IN
This is a simple and easy to remember rules, but the actual implementation of the results must also tested in ORACLE8i, the execution path between the two seems to be the same.
Inefficient:
SELECT .... FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
Efficient
SELECT ... FROM LOCATION WHERE LOC_IN IN (10,20,30);

i
Replaced with a UNION-ALL UNION (if possible):
When the UNION SQL statement requires two query result sets, the two result sets will be UNION-ALL ways are combined, and then sort out the final outcome. If you use UNION ALL instead UNION, so sorting is not necessary. efficiency will thus be enhanced. be noted that, UNION ALL will repeat the same output result set two records. so you, or from the business needs of the feasibility of using UNION ALL.
UNION will sort the result set, this operation will be used to SORT_AREA_SIZE this memory. For this piece of memory optimization is also very important. The following SQL can be used to check the consumption of inefficient sorting:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95 '
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95 '
High:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95 '
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95 '
j
Replaced by WHERE ORDER BY:
ORDER BY clause only under strict conditions in the use of two indexes.
ORDER BY column must contain all of the same index and maintain the order in the index.
ORDER BY column must be defined for all non-empty.
WHERE clause uses the index and ORDER BY clause can not be used for parallel index.
4 Other

a
ORACLE SGA from the principle of shared memory, can be drawn for each SQL ORACLE will analyze its time and uses shared memory, if the SQL string and format written in exactly the same, then ORACLE will analyze time, shared memory will only leave once the results, not only can reduce the time of SQL, and can reduce the duplication of the information shared memory, ORACLE SQL accurate statistics can also be the implementation of frequency.

b
Avoid the use of expensive operations:
With DISTINCT, UNION, MINUS, INTERSECT, ORDER BY in the SQL statement will start the SQL engine to perform resource-intensive sorting (SORT) function. DISTINCT need a sort operation, while others need to perform at least twice a sort. Usually with UNION , MINUS, INTERSECT in SQL statements can be rewritten in other ways. If you deploy the database SORT_AREA_SIZE well, use UNION, MINUS, INTERSECT can be considered, after all, they are very readable

c
Improve the efficiency of internal functions through SQL.:
Often at the expense of complex SQL execution efficiency. Able to master the use of the above functions to solve problems in practical work is very significant (14) using the table alias (Alias):
When the SQL statement, connecting more than one table, use the table alias and the alias prefix in each Column on. In this way, we can reduce the time resolution and reduce the ambiguity that caused by the Column syntax error.

d
Increased by in-house function SQL efficiency.:
Often at the expense of complex SQL execution efficiency. Able to master the use of the above functions to solve problems in practical work is very meaningful

e
Use the table alias (Alias):
When the SQL statement, connecting more than one table, use the table alias and the alias prefix in each Column on. In this way, we can reduce the time resolution and reduce the ambiguity that caused by the Column syntax error.

相关文章
  • Summary of SQL query optimization 2010-10-22

    Recent projects require a higher data processing requirements, had to optimize the look, so do a consolidation, venture to throw a few bricks, hope to stay sort of people you pass by surprise. Expectations. . SQL query optimization summary 20,101,022

  • SQL Query Optimization Summary 2010-10-22

    Recent projects require a higher data processing requirements, had to optimize the look and therefore make an order, venture to throw a few bricks, hope to stay sort of people you pass by surprise. Expectations. . SQL query optimization summary 20,10

  • SQL query optimization of index scan and full table scan Talk 2010-09-28

    SQL query optimization of index scan and full table scan Talk Read object: Novice Index scan --- Maybe you have had the experience, the first time, was invited to a friend's house, a friend told that you are happy home 132 1102, and then you went hom

  • SQL query optimization of index scan and full table scans Talk 2010-09-28

    SQL query optimization of index scan and full table scans Talk Read object: junior user Index scan --- maybe you have had the experience, was invited for the first time one day to a friend's house, a friend told you happy home 132 1102, and then you

  • 15 SQL query optimization approach (reproduced) 2011-07-28

    15 Ways to Optimize Your SQL Queries Posted on October 27 by Clay Previous article was on 10 Ways To Destroy A SQL Database that sort of teaches you what company might make many mistakes on their Database Eventually that will lead to a Database destr

  • sql query optimization 2010-04-01

    Address: http://blog.csdn.net/fertiland/archive/2007/07/12/1687008.aspx 1 There are two tables, A Table 800 million, B Table 900 million, the two tables related queries do not GROUP BY, etc, just a general inquiry, how to improve the speed? [1] the m

  • Orace sql query optimization 2010-11-11

    When people use tend to fall into a SQL error, that is too much focus on the results is correct, and ignore the different implementations may exist between the performance differences, this difference in performance in large or complex database envir

  • SQL query optimization superstition of the index articles 2010-12-07

    Before that a lot of articles on the Internet I have some misleading, for example, SQL execution order, for example, the query efficiency index higher than the full table scan. This article is to prove the use of the index is not necessarily efficien

  • SQL Query Optimization superstition of the articles index 2010-12-07

    Thank you for the concern of friends, but note that the purpose of this post I am not saying do not use the index, but not blindly use, where 10 000 data is specially created for all tables in consecutive t_noindex.x value not in the same block store

  • High-performance SQL query optimization statement 2011-02-09

    1 should be avoided in the where clause to determine the field for null value, otherwise it will cause the engine to abandon the use of indexes and full table scan, such as: select id from t where num is null You can set the default value of 0 on the

  • PL / SQL Query Optimization Experience 2010-02-13

    Reprinted reprinted reprinted are 1 to avoid the calculation of the index column Example: X WHERE sa * 1.1> 950 O WHERE sa> 950/1.1 X WHERE SUBSTR (name, 1,7) = 'CAP' O WHERE name LIKE 'CAP% 2, compare the value of data type and indexing the same co

  • SQL query optimization, pay attention to where the order of conditions 2011-07-14

    1 test sheet employee Id id payroll department employees emp_id dept_id salary 01,011,050 02,012,000 ok, we have to find the 01 departments under the salary is higher than 1000 employees (2) principles and comparison of two SQL Principle, the majorit

  • SQL Server and Oracle database on the differences in query optimization 2011-03-18

    [IT News Writer In general, Oracle database data in a large environment, its operating performance than the higher efficiency of the SQL Server database. From the query optimization in terms of a single, relatively large differences between the two t

  • Oracle SQL Performance Optimization Tips Great summary 2010-08-03

    Oracle SQL Performance Optimization Tips Great summary (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the tab

  • Massive SQL server database query optimization and paging algorithm (a) 2010-11-03

    Massive Database query optimization and paging algorithm program (1) With the "Golden Shield Project" gradual and rapid development of public security information technology, public security computer application systems are widely used in variou

  • MS SQL Server Query Optimization 2010-06-09

    MS SQL Server Query Optimization Study: xmllover 2007-11-29 Query slow for many reasons, as several common 1, no index or index is not used (this is the most common problems slow query is programming defects) 2, I / O throughput is small, the formati

  • sql performance optimization summary 2011-09-20

    Recently to help a student to do database optimization, looked at the statistics, I feel very good. One. Purpose Database parameters to optimize the performance improvement gained only a database application system all add up to about 40% performance

  • sql query execution order theory and Select (sql optimization) 2011-07-26

    This article is reproduced from csdn blog Principles and Select sql query execution order Keywords: database a sql statement execution steps 1) syntax analysis and syntax of the statement is compliant to measure the significance of the expression of

  • Database Query Optimization 2010-03-09

    Database system is the core of management information systems, database-based online transaction processing (OLTP) and online analytical processing (OLAP) is a bank, business, government and other departments of the most important computer applicatio

  • Massive data query optimization (fine) 2010-04-25

    Database query plan optimization method Database system is the core of management information systems, database-based online transaction processing (OLTP) and online analytical processing (OLAP) is the banks, enterprises, government departments one o