MySQL Statement Analysis

The database used by the company is the MySQL database, and my knowledge of MySQL is just a little bit of learning about PHP at school.

I think that as a back-end programmer, in addition to the ability to be able to realize the function,

After the implementation of the function, but also to think there is no better way, is not a more performance-saving.

Because just entered the job, has not touched the company's official environmental database. But it is a project that has been running for almost two years and the amount of data should be low.

So you should be concerned about the performance of SQL statements. So he did a little homework. Get a little bit of a look.

Performance issues must be an essential experience for every backend programmer to upgrade.

The sooner you get in touch with these issues, the more you care about these issues and the benefits to personal and corporate code quality are self-evident.

So in MySQL, there is a Explain command.

The Explain command is the first recommended command to resolve database performance, and most of the performance issues can be solved simply by this command.

Explain can be used to look at the performance of SQL statements, to help select better indexes and refine query statements, and to write better optimization statements.

In Navicat for MySQL, the query interface is directly with the [explain] button.

After writing down the simplest query statement, let's look at the results of the analysis.

Here's a look at the individual properties:

1.ID: This is the query sequence number for select

2.Select_type: Select_type is the type of select, which can be in the following ways:

Simple: Easy Select (Do not use union or subquery, etc.)

PRIMARY: The outermost Select

The second or subsequent SELECT statement in a union:union

DEPENDENT the second or subsequent SELECT statement in the Union:union, depending on the outside query

The result of the UNION result:union.

Subquery: The first select in a subquery

DEPENDENT subquery: The first select in a subquery, depending on the outside query

DERIVED: Export table's SELECT (subquery FROM clause)

3.Table: Shows which table the data for this row is about

4.type: This column is the most important, showing which category the connection uses, whether or not the index is used, and is one of the keys to analyzing the performance bottleneck with the explain command.

The result values from good to bad are:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

In general, you must ensure that the query reaches at least the range level, preferably ref, or performance issues may occur.

5.Possible_keys: column indicates which index MySQL can use to find rows in the table

6.Key: Displays the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null

7.Key_len: Shows the key lengths that MySQL decides to use. If the key is null, the length is null. The length of the index to use. The shorter the length the better, without loss of accuracy

8.ref: Shows which column or constant is used together with key to select rows from the table.

9.rows: Shows the number of rows that MySQL must check when it executes a query.

10.Extra: Contains the details of the MySQL solution query and is also one of the key references.

Distinct
Once MySQL finds a row that matches the row, it no longer searches for

NOT EXISTS
MYSQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches

Range checked for each

Record (Index map:#)
The ideal index was not found, so for each combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

Using Filesort
When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

Using Index
Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of the table are part of the same index

Using Temporary
When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by

Using where
A WHERE clause is used to restrict which rows will match the next table or are returned to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem

Some other tip:

When the type is displayed as "index" and extra is displayed as "using index", the overwrite index is used.

Reference: http://www.cnblogs.com/hailexuexi/archive/2011/11/20/2256020.html

MySQL Statement Analysis