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