Master experience: Ways to improve MySQL performance (1) _php tutorial

first, the question of the proposed

In the early stage of application system development, due to less development database data, the query SQL statement, complex view of the writing of the performance of the SQL statement is not good or bad, but if the application system submitted to the actual application, as the data in the database increases, The response speed of the system is one of the most important problems that the system needs to solve at present. An important aspect of system optimization is the optimization of SQL statements. For the massive data, the speed difference between the inferior SQL statement and the high-quality SQL statement can reach hundreds of times, it can be seen that a system is not simply able to achieve its function, but to write high-quality SQL statements, improve the availability of the system.

In most cases, Oracle uses indexes to traverse tables more quickly, and the optimizer improves performance primarily based on defined indexes. However, if the SQL code written in the WHERE clause of the SQL statement is not reasonable, it will cause the optimizer to delete the index and use a full table scan, which is generally referred to as the poor SQL statement. When writing SQL statements, we should be aware of the principles by which the optimizer removes the index, which helps to write high-performance SQL statements.

second, the SQL statement writing attention issues

The following is a detailed description of the issues that need to be noted in writing the WHERE clause of some SQL statements. In these where clauses, even if there are indexes on some columns, because poor SQL is written, the system cannot use the index while running the SQL statement, and it also uses a full table scan, which results in a very slow response.

1. Is null and is not NULL

You cannot use NULL as an index, and any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance.

Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

2. Join columns

For a joined column, the optimizer does not use the index, even if the last join value is a static value. Let's take a look at an example, assuming that there is a staff table (employee), for a worker's surname and name in two columns (First_Name and last_name), now to query a Bill Clinton Cliton.

Here is an SQL statement that takes a join query.

SELECT * FROM Employss

where

http://www.bkjia.com/PHPjc/631006.html www.bkjia.com true http://www.bkjia.com/PHPjc/631006.html techarticle first, the problem is put forward in the early stage of application system development, because the development of database data is relatively small, for query SQL statements, complex view of the writing, etc. can not understand the performance of various written SQL statements ...

