Tags: database

SQL Optimization scheme

When we write SQL statements, the order in which we write them, and the strategy in which we write them, affect the performance of the SQL statements. Although the functions implemented are the same, the performance of the SQL statements is slightly different.

So, here’s how to do it when writing SQL.

Select the most efficient table name order

The database parser processes the table names in the FROM clause FROM right to left, and the table written last in the FROM clause is processed first

In the case of multiple tables in the FROM clause:

  • If the three tables are completely unrelated, write the table with the fewest records and column names last, and so on
  • In other words: select the table with the fewest entries and place it last

If there are more than 3 tables join query:

  • If three tables are related, place the table with the most references last, and so on.
  • That is: the table referenced by other tables comes last

For example: query employee number, name, salary, salary grade, department name

The EMP table is the most referenced and has the most records, and therefore is placed at the end of the form sentence


select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)  		


Copy the code

Join order in the WHERE clause

The database parses WHERE clauses from right to left. According to this principle, joins between tables must be written to the left of other WHERE conditions, and conditions that filter out the maximum number of records must be written to the right of WHERE clauses.

Emp. sal can filter multiple records, written to the far right of the WHERE sentence


      select emp.empno,emp.ename,emp.sal,dept.dname
      from dept,emp
      where (emp.deptno = dept.deptno) and (emp.sal > 1500) 
Copy the code

Avoid * in the SELECT clause

When we were studying, the * sign could fetch all the fields in the table.

  • But it does this by querying the data dictionary, which means it takes more time
  • SQL statements written with asterisks are also not intuitive.

Replace DELETE with TRUNCATE

Delete all records of the table except for the table structure.

DELETE deletes records one by one, while Truncate deletes the entire table and retains the table structure, which is faster than DELETE

Use internal functions to improve SQL efficiency

For example, using mysql concat () function is better than using | | for joining together quickly, because the concat () function has been mysql optimization.

Use aliases for tables or columns

If the table or column name is too long, using a few short aliases can also slightly improve SQL performance. After all, there are fewer characters to scan…

## Use commit##

Comiit will release rollback points…

## Use index ##

Indexes are used to improve our query data, and are used when the table volume is very large.

##SQL write capital ##

When writing SQL, it is officially recommended to write keywords in uppercase because Oracle always converts lowercase letters to uppercase before executing them

## avoid NOT## on indexed columns

When the Oracle server encounters NOT, it stops working and performs a full table scan instead

Avoid using computed ## on indexed columns

In the WHERE clause, if the index column is part of the function, the optimizer uses a full table scan instead of the index, which becomes slower

## replace ## with >=

Inefficient:SELECT * FROM EMP WHERE DEPTNO > 3Let's start with DEPTNO=3And scan for the first DEPT greater than3Record efficiently:SELECT * FROM EMP WHERE DEPTNO >= 4So let's jump to the first DEPT is equal to4The record ofCopy the code

## replace OR## with IN


      select * from emp where sal = 1500 or sal = 3000 or sal = 800;
      select * from emp where sal in (1500.3000.800);

Copy the code

Always use the first column ## of the index

If the index is built on multiple columns, the optimizer will choose to use the index only if its first column is referenced by the WHERE clause. When only the second column of the index is referenced, but not the first column of the index, the optimizer uses a full table scan and ignores the index


      create index emp_sal_job_idex
      on emp(sal,job);
      ----------------------------------
      select *
      from emp  
      wherejob ! ='SALES'; We don't use indexes up here.Copy the code

If you find this article helpful, give the author a little encouragement