SQL optimization solution

The following is my summary of SQL optimization ideas and a summary of system and database Optimization for myself over the past few years. It is not very complete. writing this document is for leaders. Now we share it with you, make corrections later!

 

Database-level optimization solution:

 

WhenSQLSlow Query

 

At the database level, the optimization method is generally used,Reduce access times,EfficientSQL, Creating indexes and table partitions, good database table Design.

 

 Scenario 1: The execution of a function is slow.SQL ProfileRetrieveSQLStatement. If you viewSQLIf the data volume of the queried table is5Less than 10 thousand,

 

Solution:

1To view the querySQLWhether it is called in a loop statement. If it is used to check whether it can be changed toSQLTo minimize Database Access

 

 Case 2The data volume of the queried table is large (exceeds50More than 10 million)

1,CheckSQLStatement writing:

This is a lookSQLStatement Syntax:

1,Avoid using:Like '%Keywords%'

2,Use pagination to reduce the amount of data queried.

3,Avoid using functions on fields:

 For exampleUpper("Field ")=Value Change Field= Upper ('Value').

4, Use less"Or"Keywords

2,Index the queried Fields

For example:Select * from Doc where objid =' 4028819e181e984c01181f5874f703f1'

If the query is slowDocTableObjidCreate an index

3,Create partitions for large tables

 Partitions are generally created for large tables. You can determine whether to end the partition (Isfinished),Whether to delete (Isdeleted),And module partition (Doc,CusrAnd so on.