SQL statement simple optimization

Summary notes

1、 Avoid using ‘*’, Use column names directly

During parsing , Will ‘*’ Convert to all column names at once , And complete by querying the dictionary , Consume more time .

2、 When querying multiple tables , Try to use table aliases

You can more accurately define which fields of which table , Also avoid the problem of the same column name .

3、Where clause , Put the statement that can quickly narrow the query scope on the far right

because where Statement in clause , It is executed from right to left .

The first “=30” Find , Quickly narrowed the scope , And then “=30” Find... In the scope of “>10” The scope of the .

4、 Use “≥” Instead of “>”

If you use “>3”, The first to find is 3, Then compare them in turn 3 Big data , But the first to find 3 It has no effect on the results .

When using “≥4” when , Will find 4, Then compare them in turn 4 Big data , here 4 Is already one of the right answers

5、 use TRUNCATE Instead of DELETE

DELETE Safe operation , Because it was deleted before , The deleted content will be put into the rollback segment first , If you delete, you can recover .

TRUNCATE Is to delete the data directly , So when it is certain that the data will be deleted , You can use it directly TRUNCATE, Faster execution .

6、 How practical COMMIT

When using COMMIT after , Will release some resources .

7、 Avoid using functions on index columns

When sal Used *2 after ,sal The index on will not take effect , It will still query the whole table .

Reference link

https://classroom.devcloud.huaweicloud.com/MOOCCourseChapters/learning/d9eab20e10e44c00bd01c904c876b78b/eb508df9d1f445c0af93f57ea4b5fe57