Database optimization and database Optimization Solutions

Database optimization and database Optimization Solutions
I am looking for a job recently. During the interview, many companies will ask questions about database optimization. I will summarize the database optimization questions here today. I will take the MySQL database as an example to explain.

 

Why optimization:
As the actual project starts and the database runs for a period of time, the initial database settings will have some differences with the actual database running performance. At this time, we need to make an optimization adjustment.

 

Database optimization is a big topic and can be divided into four categories:
Host Performance
Memory Usage Performance
Network Transmission Performance
SQL statement execution performance [software engineer]

 

Some Database SQL optimization solutions are listed below:

(01) select the most efficient table name sequence (general test)

The database parser processes the table names in the FROM clause in the order FROM right to left,
The table written in the FROM clause will be processed first,
When the FROM clause contains multiple tables, you must put the table with the least number of records at the end,
If there are more than three tables for join query, You need to select the table referenced by other tables and put it at the end.
For example, you can query the employee ID, name, salary, salary grade, and department name.
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)
1) if the three tables are completely unrelated, write the tables with the least number of records and column names at the end, and so on.
2) If the three tables have a relationship, put the most referenced tables at the end, and so on.

(02) join sequence in the WHERE clause (general test)

The database parses the WHERE clause in order from right to left. According to this principle, the connections between tables must be written on the left of other WHERE conditions,
The conditions that can filter out the maximum number of records must be written at the right of the WHERE clause.
For example, you can query the employee ID, name, salary, and department name.
Select emp. empno, emp. ename, emp. sal, dept. dname
From emp, dept
Where (emp. deptno = dept. deptno) and (emp. sal> 1500)

(03) avoid using the * sign in the SELECT clause

During the parsing process, the database converts * to all column names in sequence. This task is completed by querying the data dictionary, which means it takes more time.
Select empno, ename from emp;

(04) DELETE all records in the table and replace DELETE with TRUNCATE.

(05) Try to use COMMIT as much as possible

Because COMMIT releases the rollback point

(06) replace HAVING clause with WHERE clause

WHERE is executed first, HAVING is executed later

(07) use more internal functions to improve SQL Efficiency

(08) use the table alias

Salgrade s

(09) use the column alias

Ename e