Small ant learns MySQL performance optimization (3)--sql and index optimization--Slow log analysis tool and explain instructions

Yesterday in the test operation of the database encountered two problems forgot to record, today add up, connect to the article

1. Installing the test database Sakila times wrong. Mysql server has gone away problem. Workaround:

View show global variables like ' max_allowed_packet ';

Generally, Max_allowed_packet 1048576 is displayed.

Modified to set global Max_allowed_packet = 1024*1024*16; Problem solving. Don't ask me why, I don't know t_t

2. I use the MySQL version 5.6.* in the setting long_query_time time, has not changed, does not take effect. On the Internet to check the information, after their own testing, this estimate is a bug it, in fact, has been modified successfully. You can also open a MySQL console and view it again, and it will be displayed correctly.

This is the two problems we met yesterday, here to add.

Analysis tools for slow-scan logs

The system comes with Mysqldumpslow when installing MySQL. Most commonly used, but the function is not very perfect.

Example: Mysqldumpslow-t 3 slow-Scan log path | More

Analyze the top three time-consuming logs for slow-scan logging.

Pt_query_digest recommended this more sophisticated tool

Examine word check rows examine scan number of lines

How do I find problematic SQL by slow-scan logs?

1. SQL, which is more frequently queried and takes a long time to query, is typically the first few queries for pt_query_digest analysis.

2. IO large SQL. Note the rows examine item in the Pt_query_digest analysis.

The SQL that was not hit by the index.

Note the comparison between the two items in the Pt_query_digest analysis rows examine and rows send scan lines and the number of rows sent

If the rows examine is much larger than the rows send, the description is missing.

After finding the very time-consuming SQL, how to parse and return the meaning of each column using Explain,explain

Table: When the data for this row is displayed

Type: Important column!! Shows what type of connection was used. The best to worst connection types are const, EQ_REG, Reg, range, index, and all.

Possible_keys: Displays the indexes that may be applied to this table. If it is empty, there is no possible index.

Key: The actual index used. If NULL, the index is not used.

Key_len: The length of the index used. The shorter the length the better, without loss of accuracy

Ref: Shows which column of the index is being used and, if possible, a constant

Rows:mysql the number of rows that must be checked to return the requested data

Extra: The return value to note (extended column)

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 link type and the row pointers for all rows that store the sort key values and matching criteria.

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

Small ant learns MySQL performance optimization (3)--sql and index optimization--Slow log analysis tool and explain instructions