teach you to write high quality and high performance MySQL syntax accurately

In the early development of the application system, because the development of database data is relatively few, for query SQL statements, complex view of the compilation, just start will not realize the performance of the various writing of SQL statements, but if the application system submitted to the actual application, as the data in the database increased, The response speed of the system becomes one of the most important problems that the system needs to solve at present. A very important aspect of system optimization is the optimization of SQL statements. For large amounts of data, poor SQL statements and high-quality SQL statements between the speed difference can be achieved hundreds of times, visible for a system is not simple to achieve its function can be, but to write high-quality SQL statements, improve the availability of the system.

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

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 some of the columns are indexed, the system cannot use the index when running the SQL statement because of poor SQL writing, and the same use of full table scans, which results in a dramatic decrease in response speed.

1 is null and is not NULL

cannot be indexed with NULL, and any column that contains null values will not be included in the index. Even if there are multiple columns in the index, the column is excluded from the index as long as one of the columns 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 column

For columns with joins, the optimizer will 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), which is divided into two columns for the surname and name of a worker (First_Name and Last_Name), and is now inquiring about a worker named Bill Cliton.

The following is an SQL statement that takes a join query.

The above statement can be used to find out if there is a bill Cliton this employee, but it should be noted that the system optimizer is not using the index created on last_name.

When written using the following SQL statement, the Oracle system can take an index created based on last_name.

How do you deal with the situation below? If a variable (name) holds the name of the employee of Bill Cliton, how do we avoid the whole traversal and use the index for this situation? You can use a function to separate the last name from the name in the variable name, but one thing to note is that this function does not work on an indexed column. Here is the SQL query script:

3. Like statement with wildcard character (%)

This is also seen in the example above. The current demand is such that the list of employees should be queried in the name of the person containing the Cliton. You can use the following query SQL statement:

Because the wildcard character (%) appears at the top of the search word, the Oracle system does not use the last_name index. This may not be avoided in many cases, but it must be in the bottom of the mind that using wildcards like this can slow down the query. However, when wildcards appear in other positions in the string, the optimizer can take advantage of the index.