SQL statement Optimization principles, SQL statement Optimization _php Tutorial

SQL statement Optimization principles, SQL statement optimization

Ways to improve query speed with data above millions:

1. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

2. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by.

3. You should try to avoid null values in the WHERE clause, otherwise it will cause the engine to abandon using the index for a full table scan.

such as:
Select ID from t where num is null
you can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:
Select ID from t where num=0

4. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as:
Select ID from t where num= or num=
You can query this:
Select ID from t where num=
Union All
Select ID from t where num=20

5. The following query will also result in a full table scan: (no preceding percent sign)
Select ID from t where name like '%abc% '
to be more efficient, consider full-text indexing.

6. In and not are also used with caution, otherwise it will cause a full table scan, such as:
Select ID from t where num in (1,2,3)
for consecutive values, you can use between instead of in:
Select ID from t where num between 1 and 3

Select Xx,phone from Send a JOIN (
Select ' 13891030091 ' phone Union select ' 13992085916 ' ..... UNION SELECT ' 13619100234 ') b
On A.phone=b.phone
--instead of a lot of data separating
In (' 13891030091 ', ' 13992085916 ', ' 13619100234 ' ...) .

7. If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan:
Select ID from t where num= @num can be forced to query using the index instead:
Select ID from T with (index name) where num= @num

8. You should try to avoid expression operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. such as:
Select ID from t where num/2=
should read:
Select ID from t where num=*2

9. You should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. such as:
Select ID from t where substring (name,1,3) = ' abc ' –name ID starting with ABC
Select ID from t where DATEDIFF (Day,createdate, '2005-One-all) =0-' 2005-one-by-one ID generated
should read:
Select ID from t where name like ' abc% '
Select ID from t where createdate>= '2005-one-′and createdate< '2005 -1-

do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.

one. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.

do not write some meaningless queries, such as the need to generate an empty table structure:
Select Col1,col2 into #t from T where 1=0
This type of code does not return any result sets, but consumes system resources and should be changed to this:
CREATE TABLE #t (...)

many times, replacing in with exists is a good choice:
Select num from a where num in (select num from b)
Replace with the following statement:
Select num from a where exists (select 1 from B where Num=a.num)

Not all indexes are valid for queries, SQL is query-optimized based on the data in the table, and when there is a large amount of data duplication in the index column, the SQL query may not take advantage of the index, as there are fields in the table Sex,male, female almost half, So even if you build an index on sex, it doesn't work for query efficiency.

The index is not the more the better , although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build indexes need careful consideration, Depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

as much as possible, avoid updating the clustered index data columns, because the order of the clustered index data columns is the physical storage order of the table records, and once the column values change, the order of the entire table records will be adjusted, which can consume considerable resources. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index.

To use numeric fields as much as possible, if a field with numeric information is not designed as a character type, this can degrade query and connection performance and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.

As far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, can save storage space, second for the query, in a relatively small field search efficiency is obviously higher.

do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.

try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).

Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

Temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.

When you create a new temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid creating a large number of logs to improve speed, and if the amount of data is small, in order to mitigate the resources of the system tables, You should create table first, and then insert.

If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.

. Before you can use a cursor-based method or a temporary table method, you should look for a set-based solution to solve the problem, and the set-based approach is usually more efficient.

As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data. Routines that include "totals" in the result set are typically faster than using cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better.

Set NOCOUNT on at the beginning of all stored procedures and triggers, set NOCOUNT OFF at the end. You do not need to send a DONE_IN_PROC message to the client after each statement that executes the stored procedure and trigger.

try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the corresponding requirements are reasonable.

Try to avoid large transaction operations and improve system concurrency.

Article Source: http://www.cnblogs.com/pepcod/archive/2013/01/01/2913496.html

SQL-Optimized article reference:

Http://www.cnblogs.com/ATree/archive/2011/02/13/sql_optimize_1.html

http://blog.csdn.net/csh624366188/article/details/8457749

http://www.iteye.com/problems/100945

http://blog.itpub.net/28389881/viewspace-1301549/An INSERT INSERT statement is slow to optimize

http://www.bkjia.com/PHPjc/1033985.html www.bkjia.com true http://www.bkjia.com/PHPjc/1033985.html techarticle SQL statement Optimization principle, SQL statements optimize the processing of data above millions to improve query speed: 1. You should try to avoid using the! = or operator in the WHERE clause, or discard the engine to make ...

