Database Tuning tutorial (4) Explain performance analysis command, tuning explain

Database Tuning tutorial (4) Explain performance analysis command, tuning explain

In the previous chapter, we learned how to find slow queries and record statements into logs. How can we know where statement problems occur after slow queries are found. This chapter describes how to use the database performance analysis commands provided by Mysql to analyze SQL statements.

Ii. Explain commands for database Performance Analysis

The role of Explain is to generate a QEP (query execution plan), which can help us to see how mysql runs when an SQL statement is not actually executed, this helps us analyze the quality of SQL commands.

Execute the following statement:

Explain select * from emp where empno = 3333\G

For the returned information, we mainly focus on a few

1) Type

Full table scan of ALL is usually not good. Other Such scans, such as index, range, const, ref, and system, are better.

2) Possible_keys

Indexes that may be used

3) Key

The index actually used in the query process. If it is null, it indicates no index is used, which is usually not good.

4) key_len

The maximum length that an index field may use, also known as the index base. The larger the Index base, the more rows that may be queried, the slower the query efficiency.

5) Rows

The number of rows to be scanned by MySQL. It's just an estimate. The larger the number of rows, the slower the query.

6) Extra

It is very important to display other information than the preceding information. It mainly includes the returned results.

Usingindex

This indicates that this query uses CoveringIndex, which means that the results can be returned through the index without accessing the table. (Covered index is a very good index, its use is see the http://blog.csdn.net/hzy38324/article/details/44857721)

If "Usingindex" is not displayed, the table data is read.

Usingindex condition

Indexes may be used.

Usingwhere

The MySQL Server reads the entire row of data first, and then checks whether the row meets the where clause conditions. Low efficiency.

Usingfilesort

Mysql sorts the results in the order required by the query, and Usingfilesort appears. Sorting naturally increases the query time, resulting in lower efficiency. The solution is to use indexes for sorting. If the sorting required by the query is the same as that of the index used, because the index is sorted, the results are read and returned in the order of the index. In this case, no Using filesort occurs.

The difference between "Using index" and "Using index condition" is described as follows:StackoverflowAn article on

Http://stackoverflow.com/questions/1687548/mysql-explain-using-index-vs-using-index-condition

That's why

Simply put

Using index must be used. This index is used to overwrite the index, and Using index condition is used when necessary.

The answer to how to change Usingindex condition to Using index is to create a overwriting index. Similarly, I will introduce how to create a overwriting index in the following sections.

This chapter ends. In the next chapter, we will explain the powerful tool of Performance Optimization-index.