[Database] query optimization summary and database query Summary

[Database] query optimization summary and database query Summary
I. Rational Use of Indexes

Usage principles:

1. columns that are frequently used as conditions in queries should be indexed.

2. columns that are frequently sorted, grouped by, or order by should be indexed.

3. An index should be created for a column with a large value range.

4. If there are multiple columns to be sorted, index the columns.

5. Attributes involved in the connection operation

6. attributes that are frequently searched in a certain range and attributes that are sorted in an ordinary order

7. Multiple Attributes of a link contained in the where clause can be considered to create a multi-attribute index on these attributes.

In addition, if the database file needs to frequently perform exact match queries (such as equivalent queries), you can create a hash index. Ordered indexes such as B + numbers are more suitable for range queries.

Ii. Avoid or simplify sorting

When running an Order by or Group by SQL statement, sorting operations are involved. You should simplify or avoid repeated sorting of large tables, because the overhead of sorting disks is large. Reduces database performance, and disk sorting consumes resources in temporary tablespace.

The optimizer can avoid unnecessary sorting operations when indexes are used to automatically generate output in the appropriate order. The following are some influencing factors:

1. Due to the deficiency of existing indexes, the index does not contain one or more columns to be sorted.

2. The order of the columns in the group by or order by clause is different from that of the index.

In order to avoid unnecessary sorting, We need to correctly add indexes and reasonably merge database tables (although sometimes it may affect table normalization, it is worthwhile to improve efficiency ). If sorting is inevitable, try to simplify it, such as narrowing the range of sorting columns.

3. Eliminate sequential access to data in large table rows

In nested queries, sequential access to a table may have a fatal impact on query efficiency. The primary way to avoid this is to index the connected columns. Union can also be used to avoid sequential access. Although there are indexes in all the exam cases, some forms of where clauses force the optimizer to use sequential indexes.

4. Avoid related subqueries

If a column attribute appears in both the primary query and subquery, it is very likely that when the column value in the query changes, the subquery must be re-queried. The more queries are nested, the lower the efficiency. Therefore, avoid subqueries as much as possible.

If the subquery is unavoidable, filter as many rows as possible in the subquery.

5. Avoid difficult addition expressions

Avoid the increment expression containing the MATCHES and LIKE keywords.

6. Use temporary tables to accelerate queries

Sort a table by yourself and create a temporary table, which sometimes accelerates query. It helps avoid multiple sorting operations and simplifies the optimizer's work in other aspects. The number of rows in a temporary table is smaller than that in the primary table, and the order is the required order, which reduces disk I/O operations. Therefore, the query workload can be greatly reduced.

VII. Replacing unordered disk access with sorting

Unsequential disk access is the slowest operation. However, this is often ignored when writing SQL statements. When writing an application, it is easy to write queries that require access to a large number of non-sequential pages, resulting in reduced efficiency. In some cases, you can use SQL statements based on the database sorting function to replace non-sequential access to improve query efficiency.

8. Inadequate connection operations

The left (right) connection consumes a lot of resources because it contains data that matches NULL (nonexistent) data, and the cost may be very high. The left (right) connection consumes more resources than the internal connection. Therefore, if you can rewrite the query so that the left (right) connection is not used for the query, a considerable return will be obtained.

9. Stored Procedure

Every time you send an SQL script to a database, you need to compile it before executing it. In this way, the efficiency of using the same statement will be much lower, and the stored procedure can be executed directly without compilation, so the speed may be faster. Therefore, we recommend that you use stored procedures for SQL statements that are commonly used. In addition, you should try to use the built-in return parameters of SQ in the stored procedure, instead of custom return parameters to reduce unnecessary parameters and avoid data redundancy.

10. Do not use cursors.

A cursor occupies a large amount of system resources, especially when the concurrency is large, it is easy to exhaust system resources and crash. Therefore, do not use the cursor at will, and close and destroy the cursor in time to release resources.

XI. Transaction Processing

Transactions are often used to ensure Database Consistency while operating multiple tables at the same time. However, once multiple processes are put into the transaction, the processing speed of the system will be reduced. Therefore, the consistency of the bookstore should be ensured, put multiple sharded processing processes with frequent operations into multiple stored procedures, which will greatly improve the system response speed.

SQL database query optimization

This problem is too big. If you don't understand it, add memory, CPU, and RAID storage.

What is the SQL query optimized by the Oracle database?

This big problem? It's too big. I have clearly explained a book. Simply put, the backend optimized processor processes the execution plan, analyzes the data structure in the current log, and estimates the appropriate execution plan. Disk storage and so on are not related to queries. They are their own structures. At most, they barely say that primary keys are automatically indexed. Let's see the indexing principle and use it as a table. But now oracle has begun to study cloud storage. After all, it is a trend. It is estimated that it will be partially implemented in two years. Cloud storage can look at hadoop first. The execution plan is related to the default configuration of your current database. See the hint for details.