Summary of "database" query optimization

I. Rational use of the index

Principle of Use:

1. The column that is used frequently as a condition in the query should be indexed.

2. The columns that are frequently sorted or grouped by the group by or order by operations should be indexed.

3. When a column has a large domain value, it should be indexed.

4. If there are multiple columns to sort, you should establish a compliance index on those columns.

5, participate in the connection operation properties

6. properties that are searched frequently in a certain range and those that are retrieved in sorted order

7. Multiple properties of a relationship contained in the WHERE clause may be considered to establish a multi-attribute index on these properties.

Also, consider creating a hash index if the database file requires frequent exact matching queries, such as an equivalent query. Ordered indexes such as B + numbers are better suited for range queries.

Ii. avoidance or simplification of sorting

When you run an order by or Group by SQL statement, the sort operation is involved, and you should simplify or avoid repeating the large tables because the cost of sorting the disks is large. Reduces database performance, and disk ordering consumes resources in the staging table space.

The optimizer avoids unnecessary sorting operations when it can automatically generate output in the appropriate order using an index. Here are some of the factors that affect:

1. Because of insufficient existing indexes, the index does not contain one or several columns to be sorted when sorting.

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

In order to avoid unnecessary sorting, it is necessary to construct the index correctly and combine the database tables reasonably (although it can sometimes affect the normalization of the table, it is worthwhile to improve relative to the efficiency). If the ordering is unavoidable, then the band should try to simplify it, such as narrowing the range of the sorting sequence.

Iii. eliminating sequential access to large table row data

In nested queries, sequential access to a table can have a fatal effect on query efficiency. The primary way to avoid this situation is to index the concatenated columns. You can also use a set to avoid sequential access. Although there are indexes on all of the check lacerations, some form of where clause forces the optimizer to use sequential indexing.

Iv. Avoid related sub-queries

When a column property is simultaneously present in both the main query and the subquery, it is likely that the subquery must be queried again once the column values in the query are changed. The more nested the query, the lower the efficiency, so you should avoid subqueries as much as possible.

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

V. To avoid difficulties in increasing the expression

Avoid add-on expressions with matches and like keywords.

Vi. using temporary tables to speed up queries

Sort a table by itself and create temporary tables that can sometimes speed up queries. It helps to avoid multiple sorting operations, and in other ways simplifies the work of the optimizer. The rows in the staging table are less than the rows in the primary table, and the order is the required order, reducing the I/O operation of the disk, so the query effort can be significantly reduced.

Vii. sorting to replace non-sequential disk access

Non-sequential disk access is the slowest operation. However, this is often overlooked when writing SQL statements, and it is easy to write queries that require access to a large number of non-sequential pages, resulting in reduced efficiency. In some cases, SQL based on the database sorting function can be used instead of non-sequential access to improve query efficiency.

Viii. Insufficient Connection operation

Left (right) connections consume a lot of resources because they contain data that matches null (nonexistent) data, which can be expensive. The left (right) connection consumes more resources than the inner connection, so if you can rewrite the query so that the query does not use a left (right) connection, you get a very good return.

Ix. Stored Procedures

Each SQL script sent to the database usually needs to be compiled and executed first. This can be much less efficient when using the same statement, while stored procedures can be executed directly without compiling, so the speed may be faster. Therefore, it is recommended to use stored procedures for trivial SQL statements. In addition, it is important to note that the return parameters of sq are used as much as possible in the stored procedure, rather than the custom return parameters, reducing unnecessary parameters and avoiding data redundancy.

Ten, do not arbitrarily use cursors

Cursors can take up more system resources, especially in the case of large-scale concurrency, which could easily cause the system to run out of resources and crash. Therefore, do not arbitrarily use cursors, and the use of cursors should be closed and destroyed in time to release resources.

XI. Transaction Processing

In order to ensure the consistency of the database by operating multiple tables simultaneously, transactions are often used. However, once multiple processing is put into the transaction, the processing speed of the system will be reduced, so it should be in the premise of ensuring the consistency of the house, the frequent operation of multiple divisible processing process into a number of stored procedures, which will greatly improve the system's response speed.

Summary of "database" query optimization