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

This article mainly explains "how to write a high-quality and high-performance SQL query statement", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to write a high-quality and high-performance SQL query statement"!

First, we must first understand what is meant by implementing the plan.

The execution plan is a query scheme made by the database based on SQL statements and statistics of related tables, which is automatically analyzed by the query optimizer. For example, if a SQL statement is used to look up a record from a table of 100000 records, the query optimizer will choose the "index lookup" mode. If the table is archived and there are only 5000 records left, the query optimizer will change the scheme. The "full table scan" mode is adopted.

It can be seen that the execution plan is not fixed, it is "personalized". It is important to produce a correct "execution plan":

(1) does the SQL statement clearly tell the query optimizer what it wants?

(2) is the database statistics obtained by the query optimizer up-to-date and correct?

II. The writing of unified SQL sentences

The programmer thinks it is the same for the following two SQL statements, and the database query optimizer thinks it is different.

Select*from dual

Select*From dual

In fact, because of the difference in case, the query analyzer thinks that they are two different SQL statements that must be parsed twice. Generate 2 execution plans. So as a programmer, you should make sure that the same query statements are consistent everywhere, not even one more space!

III. Problems needing attention in the compilation of SQL sentences

The following is a detailed introduction to some points for attention in writing SQL statements. In these where clauses, even if some columns have an index, the system cannot use the index when running the SQL statement because of the poor SQL, and the full table scan is also used, which greatly reduces the response speed.

1.IS NULL and IS NOT NULL

You cannot use null as an index, and any column that contains a null value will not be included in the index. Even if the index has multiple columns, as long as one of these columns contains null, the column is excluded from the index. That is, if a column has a null value, even indexing that column will not improve performance.

Any statement optimizer that uses is null or is not null in the where clause does not allow the use of indexes.

two。 Avoid using incompatible data types.

Incompatible data types represent the type conversion of full table retrieval data, and access will become a full table scan.

Select * from employee where last_name = 100; Note last_name is varchar type

3. Join column

For columns with joins, the optimizer does not use indexes even if the final join value is a static value. Let's take a look at an example. Suppose there is a job list (employee). For an employee whose last name and first name are stored in two columns (FIRST_NAME and LAST_NAME), we now want to query a bill. An employee of Bill Cliton.

The following is a SQL statement that uses a join query

Select * from employss where first_name | |''| | last_name = 'Beill Cliton'

The above statement can well find out if there is an employee named Bill Cliton, but it is important to note that the system optimizer does not use indexes created based on last_name.

When writing the following SQL statement, the Oracle system can adopt an index created based on last_name.

* where first_name = 'Beill' and last_name =' Cliton'

4. Like statement at the beginning of wildcard (%)

The current requirement is to query the employee table for people whose names contain cliton. You can use the following query SQL statement:

Select * from employee where last_name like'% cliton%' here the Oracle system does not use last_name 's index because the wildcard (%) appears at the beginning of the search word. However, when wildcards appear elsewhere in the string, the optimizer can take advantage of the index. The index is used in the following query:

Select * from employee where last_name like'c%'

5. An operation on an index field invalidates the index.

Try to avoid functional or expression operations on fields in the WHERE clause, which will cause the engine to abandon the use of indexes and perform full table scans.

Eg:SELECT * FROM T1 WHERE F1amp 2x100 should be changed to: SELECT * FROM T1 WHERE F1x100x2

6. Order by statement

The ORDER BY statement determines how Oracle sorts the returned query results. The Order by statement does not have any special restrictions on the columns to be sorted, and you can add functions to the columns (such as joins or attachments, etc.). Any non-indexed items or expressions that are evaluated in the Order by statement will slow down the query.

Carefully examine the order by statement to find non-index items or expressions, which can degrade performance. The solution to this problem is to rewrite the order by statement to use the index, or to create another index on the column used, while absolutely avoiding the use of expressions in the order by clause.

7. NOT

When querying, we often use some logical expressions in the where clause, such as greater than, less than, equal to, not equal, and so on. We can also use and (and), or (or), and not (not). NOT can be used to reverse any logical operation symbol. Here is an example of a NOT clause:

... Where not (status = 'VALID')

If you are using NOT, you should precede the inverted phrase with parentheses and the NOT operator before the phrase. The NOT operator is contained in another logical operator, which is not equal to the () operator. In other words, even if the NOT word is not explicitly added to the query where clause, NOT is still in the operator, as shown in the following example:

... Where status' INVALID'

For this query, you can rewrite it to not use NOT:

Select * from employee where salary3000

Although the results of the two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes on salary columns, while the first query cannot.

8. IN and EXISTS

Sometimes a column is compared to a series of values. The easiest way is to use subqueries in the where clause. You can use subqueries in two formats in the where clause.

The first format is to use the IN operator:

... Where column in (select * from... Where.)

The second format is to use the EXIST operator:

... Where exists (select'X' from... where...)

I believe that most people will use the first format because it is easier to write, but in fact the second format is much more efficient than the first format. In Oracle, almost all IN operator subqueries can be rewritten into subqueries that use EXISTS.

In the second format, the subquery starts with 'select' X'. Use the EXISTS clause to view only the where clause regardless of what data is extracted from the table by the subquery. In this way, the optimizer does not have to traverse the entire table and can do its work based solely on the index (here, it is assumed that the column used in the where statement has an index). Compared to the IN clause, EXISTS uses associative subqueries, which are more difficult to construct than in subqueries.

By using the EXIST,Oracle system, the main query is checked first, and then the subquery is run until it finds the first match, which saves time. When the in subquery is executed in the Oracle system, the subquery is executed first, and the list of results is stored in an indexed temporary table. Before executing the subquery, the system suspends the main query, waits for the execution of the subquery, and then executes the main query after it is stored in the temporary table. This is why using EXISTS is usually faster than using IN.

At the same time, you should use NOT EXISTS instead of NOT IN whenever possible, although both use NOT (indexes cannot be used to slow down), NOT EXISTS is more efficient than NOT IN query.

9. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan

Such as: select id from employee where num=10 or num=20

You can query it like this: select id from employeewhere num=10 union all select id from employeewhere num=20

10. Expression operations on fields in where clauses should be avoided as far as possible

This will cause the engine to abandon the use of indexes and perform a full table scan. For example, select id from t where num/2=100 should be changed to: select id from t where num=100*2

11. Functional manipulation of fields in where clauses should be avoided as far as possible

This will cause the engine to abandon the use of indexes and perform a full table scan. For example: select id from t where substring (name,1,3) = 'abc', the id that name begins with abc should be changed to:

Select id from t where name like 'abc%'

twelve。 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.

13. 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.

14. The more indexes, the better.

Although the index can improve the efficiency of the corresponding select, it also reduces the efficiency of insert and update, because the index may be rebuilt during insert or update, so how to build the index needs to be carefully considered, depending on the specific situation. It is best to have no more than 6 indexes in a table, and if there are too many, consider whether it is necessary to build indexes on some infrequently used columns.

15. 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.

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

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

Thank you for your reading, the above is the content of "how to write a high-quality and high-performance SQL query statement". After the study of this article, I believe you have a deeper understanding of how to write a high-quality and high-performance SQL query statement. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.