Why optimize:
With the start of the actual project, after a period of operation, the initial database settings will have some differences with the actual database performance, so we need to make an optimization adjustment.
Database optimization is a big topic, which can be divided into four categories:
Host performance
Memory usage performance
Network transmission performance
"SQL statement execution performance software engineer
The following are some database SQL optimization schemes:
(0 1) Choose the most effective table name order (written test)
The parser of the database processes the table names in the FROM clause from right to left,
Tables written at the end of the FROM clause will be processed first.
When the FROM clause contains multiple tables, the table with the least number of records must be selected at the end.
If more than three tables are connected to the query, you need to select the table referenced by other tables and put it last.
For example, query the employee's number, name, salary, salary grade and department name.
Select employee number, employee name, employee sales, sales grade and department name.
From sales levels, departments, employees
Among them, (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal).
1) If the three tables are completely irrelevant, write the table with the least records and column names at the end, and so on.
2) If three tables are related, put the most cited table last, and so on.
(02) Connection order in where clause (common written test)
The database parses the WHERE clause from right to left. According to this principle, the connection between tables must be written to the left of other WHERE conditions.
The condition that the maximum number of records can be filtered out must be written on the right side of the WHERE clause.
For example, query the employee's number, name, salary and department name.
Select employee number, employee name, employee sales and department name.
From employees, departments
Among them, (emp.deptno = dept.deptno) and (emp.sal & gt 1500).
(03) Avoid using the * symbol in the 03)SELECT clause.
During database parsing, * will be converted into all column names in turn. This work is done by consulting the data dictionary, which means more time is needed.
select empno，ename from emp
(04) DELETE all records in the table and replace delete with TRUNCATE.
(05) Use COMMIT whenever possible.
Because the commit released the rollback point
(06) Replace the HAVING clause with the WHERE clause.
Execute WHERE first, then executing HAVING.
(07) Use more internal functions to improve the efficiency of SQL.
(08) Use the alias of the table
salgrade s
(09) Use aliases for columns
Enamel e