MySQL Performance Analysis and usage of explain

MySQL Performance AnalysisAndExplainUsage knowledge is what we will introduce in this article. Next we will introduce this process through some practical examples, hoping to help you.

1. Use the explain statement to view the analysis results

For example, explain select * from test1 where id = 1; the columns of id selecttype table type possible_keys key key_len ref rows extra are displayed.

Where,

Type = const indicates that the index is located once;

If key = primary, the primary key is used;

Type = all, which indicates full table scan;

Key = null indicates no index is used. Type = ref, because it is considered to be multiple matching rows, in the joint query, it is generally REF.

2. combined index in MYSQL

Assume that the table has IDs, key1, key2, key3, and a composite index.

For example:

 
 

  
  
where key1=....  
  
  
   where key1=1 and key2=2  
  
  
   where key1=3 and key3=3 and key2=2 
 
 

According to the leftmost principle, these indexes can be used. For example, from test where key1 = 1 order by key3, if you use explain for analysis, only the normal_key index is used, but only the where clause works, the order by clause must be sorted.

3. Use slow query analysis

In my. ini:

Long_query_time = 1

Log-slow-queries = d: \ mysql5 \ logs \ mysqlslow. log

Records that exceed 1 second in the slow query log

You can use mysqlsla for analysis. In mysqlreport

DMS analyzes the percentage of select, update, insert, delete, replace, and so on.

4. MYISAM and INNODB locking

In myisam, note the table lock. For example, after multiple UPDATE operations and then SELECT, you will find that the SELECT Operation is locked. After all UPDATE operations are completed, SELECT again

If innodb is used, the row lock is used. The above problem does not exist.

5. MYSQL transaction configuration items

Innodb_flush_log_at_trx_commit = 1

It indicates that the transaction log is written to the disk immediately when the transaction is committed, and the data and index are also updated.

Innodb_flush_log_at_trx_commit = 0

When a transaction is committed, the transaction log is not immediately written to the disk and written every 1 second.

Innodb_flush_log_at_trx_commit = 2

When a transaction is committed, immediately write the disk file. Here, it is only written to the kernel buffer, but not immediately refreshed to the disk. Instead, it is refreshed to the disk every one second, and data and indexes are updated at the same time.

Explain usage

EXPLAIN tbl_name or: EXPLAIN [EXTENDED] SELECT select_options

The former can obtain the field structure of a table, and the latter mainly provides related index information. The latter focuses on the latter.

Example

 
 

  
  
mysql> explain select * from event;  
  
  
+—-+————-+——-+——+—————+——+———+——+——+——-+  
  
  
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  
  
  
+—-+————-+——-+——+—————+——+———+——+——+——-+  
  
  
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |  
  
  
+—-+————-+——-+——+—————+——+———+——+——+——-+  
  
  
1 row in set (0.00 sec) 
 
 

Meaning of each attribute

Id

Serial number of the select query

Select_type

Select queries are different from complex queries such as common queries and joint queries and subqueries.

Table

The table referenced by the output row.

Type

The type used by the Union query.

Type Displays the access type and is an important indicator. The result values are as follows:

System> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL

In general, make sure that the query reaches the range level at least, and it is best to reach the ref level.

Possible_keys

Specifies which index MySQL can use to find rows in the table. If it is null, there is no relevant index. To improve performance, you can check the WHERE clause to see if some fields are referenced or if the fields are not suitable for indexing.

Key

Displays the keys actually determined by MySQL. If no index is selected, the key is NULL.

Key_len

Displays the key length determined by MySQL. If the key is NULL, the length is NULL. Note that this value can be used to determine which part of mysql is actually used in multiple primary keys.

Ref

Shows the field or constant used with the key.

Rows

This number indicates how much data mysql needs to traverse before it can be found, which is inaccurate in innodb.

Extra

If it is Only index, this means that information is Only retrieved from the index tree, which is faster than scanning the entire table.

If it is where used, the where restriction is applied.

If it is impossible where, it indicates that the where is not needed. Generally, nothing is found.

If this information shows Using filesort or Using temporary, it will be very difficult, and the WHERE and order by indexes are often unable to take into account. If the index is determined by where, then in order, this will inevitably lead to Using filesort. It depends on whether filtering and sorting are cost-effective, or sorting and filtering are cost-effective.

This article introduces the MySQL Performance Analysis and explain usage!

Source: http://blog.sina.com.cn/s/blog_4586764e0100o9s1.html.