Common SQL optimization

Summarize the common sql Optimize , Avoid invalid queries from 、 Use index correctly 、 Avoid index invalidation 、 The other four aspects of optimization are discussed

Rule one ： Avoid querying invalid data

Avoid using select * operation , We don't use all the time fields , Reading all fields and transmitting more data over the network will lead to performance degradation .

Rule two ： Add the correct index

The most effective tuning is to add indexes , Adding indexes can avoid full table scanning ; And we should avoid the operation of index invalidation after adding an index ;

Should be indexed 4 place ：

• Where The fields that often follow
• Order by Keywords after sorting
• Group by Keywords after grouping
• Join on Keywords after

Rule 3 ： Try to avoid index invalidation

Actions to avoid , These operations will result in invalidation even if the index is added ：

• Like When the operation wildcard appears on the left , Such as like % Wenlong , Because the index sorts the data , Wildcard characters appear on the left, which invalidates the sorting , This causes the index to fail .

How to optimize ： Use should be avoided , If there is such a need , have access to INSTR(str, substr) replace , Full text search engines can also be considered when the data is large , Such as ES(Elasticsearch)

• Avoid using in And not in, Can cause indexes to fail ;

How to optimize ： If values are continuously available between and Instead of , If not continuous, you can use EXISTS And NOT EXISTS Instead of ; because EXISTS Indexed

• Where The following index fields have expression operations or use functions, which will lead to index invalidation

How to optimize ： Expression 、 The function operation moves to the right of the equal sign

• Where In the sentence , If you use is null or is not null To determine the index key , Index failure

How to optimize ： Give default

• Or use union Instead of ,or The database engine will abandon the index for full table scanning
• Avoid using where 1=1 , Tagging ,where 1=1 The database engine will abandon the index for full table scanning

Rule four ： Other optimization

Can page , Can batch