SQL Optimization Summary Refinement

Index:
　　consider establishing an index on the columns involved in where and order by

Multiple columns are frequently accessed at the same time, and each column contains duplicate values to consider creating a composite index, and the more frequently the query is placed in front of the field

use clustered and nonclustered indexes on demand, aggregation not suitable for frequent updates, scoping queries (>,<, > =,< =) and order by group by Note the order of the composite indexes, the recommended high selectivity is put in front
Do not index a field with low data selectivity
< Span style= "font-family: Arial" > index control in 6 good

remove redundant index

where clause operation:

try to avoid in where clause in the null != or operator, or to join conditions, in and not in like % in front, using parameters, such as where [email  Protected] ,

expression operations, such as where Num/2=100" function operation ("=" Left function substring (name,1,3) = ' abc '; #name

　　exists instead of In
　　Avoid multiple range queries in one query
　　where The data in the clause is scanned no more than 30% of the total amount of table data

Table structure:

　　You can use numbers and enum types without other types

use Varchar/nvarchar instead of char/nchar

field as far as possible use not NULL
ip address unsigned INT
fixed-length tables are faster
temp table:

　　can be do not use temporary tables with variables

　　Avoid frequent creation and deletion of temporary tables

you need to repeatedly reference large tables or common A temporary table is available when a dataset in a table

　　when creating a new temporary table, if you insert a large amount of data at once, replace the create table with SELECT INTO

　　Note Delete the temporary table , TRUNCATE TABLE First, and then drop table

Other:
　　do not use select *

　　A large amount of data is not suitable for cursor handling

set at the beginning of all stored procedures and triggers Set NOCOUNT on, sets NOCOUNT OFF at the end

Regular ANALYZE,CHECK ,OPTIMIZE table

EXPLAIN your SELECT Query

Use LIMIT avoids querying large amounts of data at once

in the The Join table uses the same type of column and indexes it

Please don't ORDER by RAND ()

in addition to association tables always set an ID for each table

Prepared statements be careful with " permanent link "
　　Try to avoid large transaction operations

split the Big DELETE or insert or insert: Into: Select . Statement reduce lock table time

using ORM
　　use caching, such as level cache, level two cache, Redis,memcace distributed

Improve data storage and extraction speed by using transport sub-Library, sub-table and partition table rationally

SQL Optimization Summary Refinement