How to write high-quality and high-performance SQL query statements

1. First, we must understand what an execution plan is?

The execution plan is a query plan made by the database based on the statistical information of the SQL statement and related tables. This plan is automatically analyzed by the query optimizer. For example, if a SQL statement is used to extract 100,000 records To search for 1 record in the table, the query optimizer will choose the "index search" method. If the table is archived and there are only 5,000 records left, the query optimizer will change the plan and use "full table scan" "Way.

It can be seen that the execution plan is not fixed, it is "personalized". There are two important points in generating a correct "execution plan":

(1) Does the SQL statement clearly tell the query optimizer what it wants to do?

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

2. Unified way of writing SQL statements

For the following two SQL statements, programmers think they are the same, but the database query optimizer thinks they are different.

select*from dual

select*From dual

In fact, the case is different. The query analyzer considers it to be two different SQL statements and must be parsed twice. . Generate 2 execution plans. Therefore, as a programmer, you should ensure that the same query statement is consistent everywhere, even one more space will not work!

3. Issues to note when writing SQL statements

The following is a detailed introduction to some issues to note when writing SQL statements. In these where clauses, even if there are indexes for some columns, due to poor quality SQL written, the system cannot use the index when running the SQL statement, and also uses a full table scan, which results in extremely slow response speed. Great reduction.

1.IS NULL and IS NOT NULL

You cannot use null as an index, and any column containing 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 will be excluded from the index. That is to say, if there is a null value in a column, even building an index on the column will not improve performance.

Any statement optimizer that uses is null or is not null in a where clause is not allowed to use an index.

2. Avoid using incompatible data types.

The incompatible data type represents the type conversion of the full table retrieval data, and the access will become a full table scan

select * from employee where last_name = 100; note last_name is a varchar type

3. Join columns

For columns with joins, even if the final join value is a static value, the optimizer will not use the index. Let's look at an example together. Suppose there is an employee table (employee). An employee's first and last name are stored in two columns (FIRST_NAME and LAST_NAME). Now we want to query an employee named Bill Cliton.

The following is a SQL statement using a join query,

select * from employss where first_name||''||last_name ='Beill Cliton';

The above statement The statement can completely query whether there is an employee named Bill Cliton, but it should be noted here that the system optimizer does not use the index created based on last_name.

When the following SQL statement is written, the Oracle system can use the index created based on last_name.

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

4. Like statement starting with wildcard character (%)

The current demand is like this , requesting 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%'. Since the wildcard character (%) appears at the beginning of the search word, the Oracle system does not use the index of last_name. 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. Performing operations on the index field will invalidate the index.

Try to avoid performing function or expression operations on fields in the WHERE clause, which will cause the engine to give up using the index and perform a full table scan.

eg: SELECT * FROM T1 WHERE F1/2=100 should be changed to: SELECT * FROM T1 WHERE F1=100*2

6. Order by statement

ORDER The BY statement determines how Oracle sorts the returned query results. The Order by statement has no special restrictions on the columns to be sorted, and functions can also be added to the columns (such as joins or appends, etc.). Any non-index items or calculated expressions in the Order by statement will slow down the query.

Check the order by statement carefully to find non-indexed items or expressions, which will reduce performance. The solution to this problem is to rewrite the order by statement to use an index. You can also create another index for the column used. At the same time, you should absolutely avoid using expressions in the order by clause.

7. NOT

We often use some logical expressions in the where clause when querying, such as greater than, less than, equal to, not equal to, etc. You can also use and (and), or (or) and not (not). NOT can be used to negate any logical operation sign. The following is an example of a NOT clause:

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

If you want to use NOT, you should put parentheses before the negated phrase and the NOT operator before the phrase. The NOT operator is contained within another logical operator, which is the not equal to (<>) operator. In other words, even if the NOT word is not explicitly added to the where clause of the query, NOT is still in the operator, see the following example:

... where status <>'INVALID';

This query can be rewritten without using NOT:

select * from employee where salary<3000 or salary>3000;

Although the results of these two queries are the same, The second query plan will be faster than the first query plan. The second query allows Oracle to use indexes on the salary column, while the first query cannot use indexes.

8. IN and EXISTS

Sometimes a column is compared to a series of values. The simplest way is to use a subquery in the where clause. Two formats of subqueries can be used in the where clause.

The first format is to use the IN operator:

... where column in(select * from ... where ...);

The second format The format is to use the EXIST operator:

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

I believe that most people will use the first one format, because it is easier to write, and the second format is actually far more efficient than the first format. In Oracle, almost all IN operator subqueries can be rewritten as subqueries using EXISTS.

In the second format, the subquery starts with 'select 'X'. Using the EXISTS clause no matter what data the subquery extracts from the table, it only looks at the where clause. In this way, the optimizer does not have to traverse the entire table and can complete the work based on the index only (this assumes that the column used in the where statement has an index). Compared with the IN clause, EXISTS uses connected subqueries, which are more difficult to construct than IN subqueries.

By using EXIST, the Oracle system checks the main query first and then runs the subquery until it finds the first match, which saves time. When the Oracle system executes the IN subquery, it first executes the subquery and stores the obtained result list in an indexed temporary table. Before executing the subquery, the system first suspends the main query. After the subquery is completed, it is stored in the temporary table and then the main query is executed. This is why using EXISTS is faster than using IN for general queries.

At the same time, NOT EXISTS should be used instead of NOT IN as much as possible. Although both use NOT (the index cannot be used to reduce the speed), NOT EXISTS is more efficient than NOT IN query.

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

For example: select id from employee where num= 10 or num=20

can be queried like this: select id from employee where num=10 union all select id from employee where num=20

10. Try to avoid pairing fields in the where clause Performing expression operations

This will cause the engine to give up using the index 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. Try to avoid performing functional operations on fields in the where clause

This will cause the engine to give up using the index and perform a full table scan. For example: select id from t where substring(name,1,3)='abc', the id whose name starts with abc should be changed to:

select id from t where name like 'abc%'

12. Do not perform functions, arithmetic operations or other expression operations on the left side of "=" in the where clause, otherwise 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 the condition to ensure that the system uses the index, otherwise the index will not will be used, and the field order should be consistent with the index order as much as possible.

14. The more indexes, the better.

Although indexes can improve the efficiency of the corresponding select, they also reduce the efficiency of insert and update, because there may be errors when inserting or updating. Rebuild the index, so how to build the index needs to be carefully considered and depends on the specific situation. It is best not to have more than 6 indexes on a table. If there are too many, you should consider whether it is necessary to build indexes on some columns that are not commonly used.

15. Try to use numeric fields. If the fields contain only numerical information, try not to design them as character fields. This will reduce the performance of queries and connections, and increase storage overhead. This is because the engine will compare each character in the string one by one when processing queries and connections, and only one comparison is enough for numeric types.

16. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, variable length fields have small storage space and can save storage space. Secondly, for queries, search efficiency in a relatively small field is high. Obviously higher.

17. Do not use select * fromt anywhere, replace "*" with a specific field list, and do not return any unused fields.

The above is the detailed content of How to write high-quality and high-performance SQL query statements. For more information, please follow other related articles on the PHP Chinese website!