Tips: How to improve MySQL Performance (1) _ PHP Tutorial-php Tutorial

Tips: How to improve MySQL Performance (1 ). I. question raised: at the early stage of application system development, due to the small amount of data in the development database, the performance of various SQL statement writing methods cannot be realized for Querying SQL statements and compiling complex views. I. question proposal

At the early stage of application system development, due to the relatively small amount of data in the development database, the performance of SQL statement writing is not good for Querying SQL statements and writing complex views, however, after the application system is submitted to the actual application, as the data in the database increases, the system response speed becomes one of the most important problems to be solved by the system. An important aspect of system optimization is the optimization of SQL statements. For massive data, the speed difference between inferior SQL statements and high-quality SQL statements can reach hundreds of times. it can be seen that a system can not simply implement its functions, instead, we need to write high-quality SQL statements to improve system availability.

In most cases, Oracle uses indexes to Traverse tables faster. The Optimizer improves performance based on the defined indexes. However, if the SQL code written in the where clause of the SQL statement is unreasonable, the optimizer will delete the index and use full table scan, in general, such SQL statements are so-called inferior SQL statements. When writing SQL statements, we should be clear about the optimizer's principles for deleting indexes, which helps to write high-performance SQL statements.

II. notes for writing SQL statements

The following describes the issues that need to be paid attention to when writing the where clause of some SQL statements. In these where clauses, even if some columns have indexes, the system cannot use these indexes when running the SQL statement because of poor SQL writing. The full table scan is also used, this greatly reduces the response speed.

1. is null and IS NOT NULL

Null cannot be used as an index. any column containing null values will not be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved.

Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.

2. join columns

For joined columns, the optimizer does not use indexes even if the last joined value is a static value. Let's take a look at an example. suppose there is a employee table (employee). For a employee's surname and name are divided into two columns for storage (FIRST_NAME and LAST_NAME), we want to query a table named Bill. bill Cliton employees.

The following is an SQL statement that uses join queries,

Select * from employss

Where

At the early stage of application system development, because the development of database data is relatively small, the query of SQL statements, complex view writing, and so on cannot understand the performance of various SQL statement writing...