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

This article mainly explains "how to improve the performance of mysql database". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to improve the performance of mysql database".

What are the ways to improve the performance of mysql database

I. the raising of questions

In the initial stage of the development of the application system, due to the lack of database 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, 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 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.ISNULL and ISNOTNULL

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 isnull or isnotnull 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 BillCliton.

The following is a SQL statement that uses a join query

Select*fromemployss

Where

First_name | |''| | last_name='BeillCliton'

The above statement can well find out if there is an employee named BillCliton, 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*fromemployee

Where

First_name='Beill'andlast_name='Cliton'

How to deal with the following situations? If a variable (name) holds the name of the BillCliton 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*fromemployee

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*fromemployeewherelast_namelike'%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*fromemployeewherelast_namelike'c%'

4.Orderby statement

The ORDERBY statement determines how Oracle sorts the returned query results. The Orderby 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 Orderby statement will slow down the query.

Carefully examine the orderby statement to find non-index items or expressions, which can degrade performance. The solution to this problem is to rewrite the orderby statement to use the index, or to create another index on the column used, while absolutely avoiding the use of expressions in the orderby 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:

... wherenot (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:

... wherestatus'INVALID'

Take a look at the following example:

Select*fromemployeewheresalary3000

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

Select*fromemployeewheresalary3000

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:

... wherecolumnin (select*from...where...)

The second format is to use the EXIST operator:

... whereexists (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'. Use the EXISTS clause to view only the where clause regardless of what data is extracted from the table by the subquery. 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 column used in the where statement has an index). Compared to the IN clause, EXISTS uses associative 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 NOTEXISTS instead of NOTIN whenever possible, although both use NOT (indexes cannot be used to slow down), NOTEXISTS is more efficient than NOTIN query.

On improving the performance of mysql database methods for you to sum up so much, I believe you through the above study after the improvement of the performance of mysql database to understand, master the method can easily achieve mysql database performance improvement in the future work, I hope that the content involved in the above can be helpful to you.

Thank you for your reading, the above is the content of "how to improve the performance of mysql database". After the study of this article, I believe you have a deeper understanding of how to improve the performance of mysql database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.