Expert experience: Methods to improve MySQL performance (1)_PHP tutorial

1. Problem raised

In the early stage of application system development, due to the relatively small amount of data in the development database, it was difficult to understand the various aspects of SQL statements for querying SQL statements and writing complex views. The performance of each writing method is good and bad, but if the application system is submitted for actual application, with the increase of data in the database, the response speed of the system will become one of the most important problems that the current system needs to solve. A very important aspect in system optimization is the optimization of SQL statements. For massive amounts of data, the speed difference between poor-quality SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that for a system, it is not simply about being able to realize its functions, but about writing high-quality SQL statements to improve the usability 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 will cause the optimizer to delete the index and use a full table scan. Generally, this kind of SQL statement is the so-called inferior SQL statement. When writing SQL statements, we should know the principles according to which the optimizer deletes indexes, which helps to write high-performance SQL statements.

2. Issues to note when writing SQL statements

The following is a detailed introduction to the issues that need to be paid attention to when writing the where clause of some SQL statements. In these where clauses, even if there are indexes for some columns, due to poor quality SQL written, the system cannot use the index when running the SQL statement, and also uses a full table scan, which results in extremely slow response speed. Great reduction.

1. IS NULL and IS NOT NULL

You cannot use null as an index, and any column containing a null value will not be included in the index. Even if the index has multiple columns, as long as one of these columns contains null, the column will be excluded from the index. That is to say, if there is a null value in a column, even building an index on the column will not improve performance.

Any statement that uses is null or is not null in a where clause is not allowed to use an index by the statement optimizer.

2. Join columns

For columns with joins, even if the final join value is a static value, the optimizer will not use the index. Let's take a look at an example. Suppose there is an employee table (employee). An employee's first and last name are stored in two columns (FIRST_NAME and LAST_NAME). Now we want to query an employee named Bill Cliton.

The following is a SQL statement using a join query,

select * from employss

where

http://www.bkjia.com/PHPjc/631006.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631006.htmlTechArticle1. Problem raised In the early stage of application system development, due to the relatively small amount of data in the development database, it is complicated to query SQL statements. I can’t understand the performance of various writing methods of SQL statements when writing views...