Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the ways to improve the performance of mysql", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the ways to improve the performance of mysql.

I. the raising of questions

In the initial stage of the development of the application system, due to the lack of development data, the performance of various writing methods of SQL statements can not be realized by querying SQL statements and writing complex views, but if the application system is submitted to the practical application, with the increase of data in the database, the response speed of the system has become one of the most important problems that the system needs to solve. A very important aspect of system optimization is the optimization of SQL statements. For large amounts of data, the speed difference between low-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 to achieve its function, but to write high-quality SQL statements to improve the availability of the system.

In most cases, indexes are used to traverse tables faster, and the optimizer improves performance mainly 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 speaking, this kind of SQL statement is the so-called inferior SQL statement. When writing SQL statements, we should know what principles the optimizer uses to delete indexes, which helps to write high-performance SQL statements.

II. Problems needing attention in the compilation of SQL sentences

The following is a detailed introduction to the problems that should be paid attention to in the writing of where clauses of some SQL statements. In these where clauses, even if some columns have an index, the system cannot use the index when running the SQL statement because of the poor SQL, and the full table scan is also used, which greatly reduces the response speed.

1. IS NULL and IS NOT NULL

You cannot use null as an index, and any column that contains 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 is excluded from the index. That is, if a column has a null value, even indexing that column will not improve performance.

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

two。 Join column

For columns with joins, the optimizer does not use indexes even if the final join value is a static value. Let's take a look at an example. Suppose there is a job list (employee). For an employee whose last name and first name are stored in two columns (FIRST_NAME and LAST_NAME), we now want to query a bill. An employee of Bill Cliton.

The following is a SQL statement that uses a join query

Select * from employss

Where

First_name | last_name = Beill Cliton

The above statement can well find out if there is an employee named Bill Cliton, but it is important to note that the system optimizer does not use indexes created based on last_name.

When writing the following SQL statement, the Oracle system can adopt an index created based on last_name.

Select * from employee where first_name = Beill and last_name = Cliton

How to deal with the following situations? If a variable (name) holds the name of the Bill Cliton employee, how can we avoid full traversal and use an index in this case? You can use a function to separate the first name from the first name in the variable name, but it is important to note that this function does not work on index columns. Here is the SQL query script:

Select * from employee where first_name = SUBSTR (& & name,1,INSTR (& & name)-1) and last_name = SUBSTR (& & name,INSTR (& & name',) + 1)

3. Like statement with wildcard (%)

Also use the above example to see this situation. The current requirement is to query the employee table for people whose names contain cliton. You can use the following query SQL statement:

Select * from employee where last_name like% cliton%

Here, because the wildcard (%) appears at the beginning of the search word, the Oracle system does not use last_name 's index. This may not be avoided in many cases, but be sure to keep in mind that using wildcards in this way can slow down queries. However, when wildcards appear elsewhere in the string, the optimizer can take advantage of the index. The index is used in the following query:

Select * from employee where last_name like c%

4. Order by statement

The ORDER BY statement determines how Oracle sorts the returned query results. The Order by statement does not have any special restrictions on the columns to be sorted, and you can add functions to the columns (such as joins or attachments, etc.). Any non-indexed items or expressions that are evaluated in the Order by statement will slow down the query.

Carefully examine the order by statement to find non-index items or expressions, which can degrade performance. The solution to this problem is to rewrite the order by statement to use the index, or to create another index on the column used, while absolutely avoiding the use of expressions in the order by clause.

5. NOT

When querying, we often use some logical expressions in the where clause, such as greater than, less than, equal to, not equal, and so on. We can also use and (and), or (or), and not (not). NOT can be used to reverse any logical operation symbol. Here is an example of a NOT clause:

... Where not (status = VALID)

If you are using NOT, you should precede the inverted phrase with parentheses and the NOT operator before the phrase. The NOT operator is contained in another logical operator, which is not equal to the () operator. In other words, even if the NOT word is not explicitly added to the query where clause, NOT is still in the operator, as shown in the following example:

... Where status INVALID

Take a look at the following example:

Select * from employee where salary3000

For this query, you can rewrite it to not use NOT:

Select * from employee where salary3000

Although the results of the two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes on salary columns, while the first query cannot.

6. IN and EXISTS

Sometimes a column is compared to a series of values. The easiest way is to use subqueries in the where clause. You can use subqueries in two formats in the where clause.

The first format is to use the IN operator:

... Where column in (select * from... Where.)

The second format is to use the EXIST operator:

... Where exists (select X from... where...)

I believe that most people will use the first format because it is easier to write, but in fact the second format is much more efficient than the first format. In Oracle, almost all IN operator subqueries can be rewritten into subqueries that use EXISTS.

In the second format, the subquery starts with 'select X. using the EXISTS clause, no matter what data the subquery extracts from the table, it only looks at the where clause. In this way, the optimizer does not have to traverse the entire table and can do its work based solely on the index (here, it is assumed that the columns used in the where statement have indexes). Compared to the IN clause, EXISTS uses contiguous subqueries, which are more difficult to construct than in subqueries.

By using the EXIST,Oracle system, the main query is checked first, and then the subquery is run until it finds the first match, which saves time. When Oracle system executes in subquery, it first executes the subquery and stores the list of results in an indexed temporary table. Before executing the subquery, the system suspends the main query, waits for the execution of the subquery, and then executes the main query after it is stored in the temporary table. This is why using EXISTS is usually faster than using IN.

At the same time, you should use NOT EXISTS instead of NOT IN whenever possible, although both use NOT (indexes cannot be used to slow down), NOT EXISTS is more efficient than NOT IN query.

At this point, I believe you have a deeper understanding of "what are the ways to improve the performance of mysql?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.