Database Tuning Tutorial (iv) Explain performance analysis commands

In the previous chapter, we will find out how to discover slow queries and log statements to the logs, so how do you know what the problem of a statement is going to be when you find a slow query. This chapter describes how to use the database profiling commands provided by MySQL to analyze SQL statements.

Second, database performance analysis explain command

Explain's role is to generate a QEP (query execution plan) that helps us to see how MySQL executes when not actually executing an SQL statement, so that we can analyze the SQL instructions.

Execute the following statement

Explain SELECT * from emp where empno = 3333\g

For the information returned, we mainly care about a few

1) Type

All full table scan, usually bad, other such as index, range, const, ref, System is better

2) Possible_keys

Indexes that may be used

3) Key

Indexes that are actually used during a query, when NULL indicates that no indexes are used, are usually not good

4) Key_len

The maximum possible length of an indexed field, also called the index cardinality. The larger the index cardinality, the more rows that might be found, and the slower the query efficiency.

5) Rows

MySQL estimates the number of rows that need to be scanned. Just an estimate. The more you find the more rows you look for, the slower the nature is.

6) Extra

It is important to display information other than the above information. It mainly has the return result.

Usingindex

Indicates that the query uses an overwrite index (COVERINGINDEX), which means that the index returns the result without having to access the table. (Overlay index is a very good index, its use see http://blog.csdn.net/hzy38324/article/details/44857721)

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

Usingindex condition

The index may be used

Usingwhere

Indicates that the MySQL server reads the entire row of data first, and then checks whether the row meets the conditions of the WHERE clause, and the match is left, and the non-conformance is discarded. Efficiency is relatively slow.

Usingfilesort

Indicates that MySQL will sort the results in the order required by the query, and Usingfilesort will appear. Sorting naturally increases query time, resulting in slower efficiency. The workaround is to use the index for sorting. If the order required for the query is the same as the sort used by the index, because the indexes are sorted, and therefore the results are returned in sequential read by index, the using Filesort does not appear at this time.

As for the difference between using index and using index condition, I refer to an article on StackOverflow

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

That's how it's explained inside.

To put it simply

The using index is used to index, which becomes an overlay index, and using index condition is used when necessary.

How to make Usingindex condition a using index, the answer is naturally to create an overlay index, again, I will explain how the overlay index is created later in the chapter.

The end of this chapter, the next chapter, we will explain the performance optimization of the tool-index.

Database Tuning Tutorial (iv) Explain performance analysis commands