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

This article mainly introduces "what are the SQL optimization methods". In the daily operation, I believe many people have doubts about what SQL optimization methods there are. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what SQL optimization methods are there?" Next, please follow the editor to study!

01

In order to optimize the query, we should avoid full table scanning as far as possible, and we should first consider establishing indexes on the columns involved in WHERE and ORDER BY.

02

Try to avoid judging the NULL value of a field in the WHERE clause. NULL is the default value when creating a table, but you should use NOT NULL most of the time, or use a special value, such as 0mai 1, as the default value.

03

Avoid using the! = or operator in the WHERE clause as much as possible. MySQL uses indexes only for the following operators: =, BETWEEN,IN, and sometimes LIKE.

04

Try to avoid using OR to join conditions in the WHERE clause, otherwise it will cause the engine to abandon the use of indexes and perform a full table scan, and you can use UNION to merge queries:

Select id from t where num=10 union all select id from t where num=20

05

IN and NOT IN should also be used with caution, otherwise it will lead to a full table scan. For consecutive values, do not use IN if you can use BETWEEN:

Select id from t where num between 1 and 3

06

The following query will also cause a full table scan:

Select id from t where name like'%abc%'

Or if select id from t where name like'%abc' wants to improve its efficiency, consider full-text search.

The index is used by select id from t where name like'abc%'.

07

Using parameters in the WHERE clause also results in a full table scan.

08

Expression operations on fields in the WHERE clause should be avoided, and functional operations on fields in the WHERE clause should be avoided.

09

In many cases, using EXISTS instead of IN is a good choice:

Select num from a where num in (select num from b)

Replace it with the following statement:

Select num from a where exists (select 1 from b where num=a.num)

ten

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, how to build the index needs to be carefully considered, depending on the circumstances.

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.

eleven

Updating clustered index data columns should be avoided as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the value of this column changes, it will lead to the adjustment of the order of the whole table records, which will consume a lot of resources.

If the application system needs to update the clustered index data column frequently, it needs to consider whether the index should be built as a clustered index.

twelve

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.

thirteen

Use varchar,nvarchar instead of char,nchar whenever possible. Because first of all, the storage space of the long field is small, which can save the storage space, and secondly, for the query, searching in a relatively small field is obviously more efficient.

fourteen

It's best not to use return all: select from t, replace "*" with a specific list of fields, and don't return any fields that you don't need.

fifteen

Try to avoid returning a large amount of data to the client. If the amount of data is too large, you should consider whether the corresponding requirements are reasonable.

sixteen

Use table aliases (Alias): when joining multiple tables in a SQL statement, use the table alias and prefix the alias on each Column.

In this way, you can reduce parsing time and reduce syntax errors caused by Column ambiguity.

seventeen

Use temporary tables to store intermediate results: an important way to simplify SQL statements is to use temporary tables to store intermediate results.

But the benefits of the temporary table are far more than these. The temporary results are temporarily stored in the temporary table, and the subsequent query is in tempdb, which can avoid scanning the main table many times in the program, and greatly reduce the "shared lock" blocking "update lock" in the program execution, reduce the blocking and improve the concurrency performance.

eighteen

Some SQL query statements should be added with nolock, read and write will block each other, in order to improve concurrency performance.

For some queries, you can add nolock so that you can write when reading, but the disadvantage is that you may read unsubmitted dirty data.

There are three principles for using nolock:

The result of the query is used for "insert, delete, change" can not add nolock.

The query table is a frequent page split, so use nolock with caution.

The use of temporary tables can also save "data foreground", play a similar function of Oracle undo table space, can use temporary tables to improve concurrent performance, do not use nolock.

nineteen

The common simplified rules are as follows: do not have more than 5 table joins (JOIN), consider using temporary tables or table variables to store intermediate results.

Use less subqueries, do not nest views too deeply, and generally do not nest more than 2 views.

twenty

The results of the query are pre-calculated and placed in the table, and then Select when querying. This was the most important method before SQL 7. 0, such as the calculation of hospital hospitalization fees.

twenty-one

OR words can be decomposed into multiple queries, and multiple queries can be joined through UNION.

Their speed is only related to whether or not to use indexes, and if the query needs to use federated indexes, it is more efficient to execute with UNION all.

Multiple OR words do not use the index, rewrite into the form of UNION and then try to match the index. A key question is whether indexes are used.

twenty-two

In the list of post-IN values, put the values that appear most frequently at the front and those that appear least at the end, reducing the number of judgments.

twenty-three

Try to put the data processing work on the server to reduce network overhead, such as the use of stored procedures.

Stored procedures are compiled, optimized, and organized into an execution plan and stored in the database SQL statements, is a collection of control flow language, of course, fast.

For dynamic SQL executed repeatedly, you can use temporary stored procedures, which (temporary tables) are placed in the Tempdb.

twenty-four

When the server has enough memory, the number of configuration threads = maximum number of connections + 5, which can achieve maximum efficiency; otherwise, use the number of configuration threads

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.