High-quality and high-performance compiling of MySQL syntax

The following articles mainly introduce the practical operation solutions for compiling high-quality and high-performance MySQL syntaxes. We all know that the practical application of MySQL syntaxes has become more and more extensive, therefore, it is very important to write MySQL syntax with high quality and high performance.

At the early stage of application system development, due to the relatively small amount of data in the development database, the compilation of SQL statements and complex views won't be able to understand the performance of various SQL statements at the beginning, 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.

At the early stage of application system development, due to the relatively small amount of data in the development database, the compilation of SQL statements and complex views won't be able to understand the performance of various SQL statements at the beginning, 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. Generally, this SQL statement is used, it is called a poor SQL statement. When writing SQL statements, we should be clear about the optimizer's principles for deleting indexes, which helps to write high-performance 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.

Compiling high quality and high performance MySQL syntaxes 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.

Writing high-quality and high-performance MySQL syntax 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,

The preceding statement can be used to check whether the employee Bill Cliton exists. However, the system optimizer does not use the index created based on last_name.

When the following SQL statement is used, the Oracle system can use an index created based on last_name.

What should we do in the following situations? If a variable (name) stores the name of the employee Bill Cliton, how can we avoid full traversal and use indexes in this case? You can use a function to separate the surname and name in the variable name. However, note that this function cannot be used in the index column. The following is an SQL query script:

Write high-quality and high-performance MySQL syntax 3. like statements with wildcards (%)

The above example shows this situation. Currently, You need to query the persons whose names contain cliton in the employee table. The following SQL statement can be used:

Here, because the wildcard (%) appears at the beginning of the search term, the Oracle system does not use the last_name index. This situation may not be avoided in many cases, but it must be well understood. Using wildcard characters will reduce the query speed. However, when a wildcard appears at another position of a string, the optimizer can use the index.

Compiling high-quality and high-performance MySQL syntax 4. Order by statement

The order by statement determines how Oracle sorts the returned query results. The Order by statement has no special restrictions on the columns to be sorted. You can also add functions to the columns (such as joining or appending ). Any non-index item or computed expression in the Order by statement will reduce the query speed.

Check the order by statement carefully to find out non-index items or expressions, which will reduce performance. To solve this problem, rewrite the order by statement to use the index. You can also create another index for the column you are using. Avoid using an expression in the order by clause.