MySQL performance analysis and the use of explain

1. Use the explain statement to view the results of the analysis, such as

Select * from Test1 where id=1;

will appear:

ID  selecttype  table  type possible_keys  key Key_len  ref rows  Extra

which

• Type=const indicates that it was found once through the index.
• Key=primary, indicates that a primary key is used
• Type=all indicates full table scan
• Key=null indicates that the index is useless
• Type=ref because this is considered to be more than one matching row, in a federated query, it is generally ref

2. Combined index in MySQL

Assuming that the table has Id,key1,key2,key3, the three are formed into a composite index, then

Such as:

where key1=.... where key1=1 and Key2=2 where key1=3 and Key3=3 and Key2=2

According to the leftmost principle, these are the indexes that can be used

Such as

 from where key1=1order by Key3

With explain analysis, only the Normal_key index is used, but only the WHERE clause works, and the subsequent order by needs to be sorted

3, using slow query analysis:

In the My.ini:

Long_query_time=1log-Slow-queries=D:\mysql5\logs \mysqlslow. Log

Record more than 1 seconds in the slow query log

You can use Mysqlsla to analyze it. It is also possible to analyze the percentage of select,update,insert,delete,replace, such as the DMS, in Mysqlreport, respectively.

4, MyISAM and InnoDB lock

MyISAM, note is a table lock, such as after more than one update operation, then select, you will find that the select operation is locked, must wait until all the update operation is complete, and then can select

InnoDB words are different, with a row of locks, there is no problem above.

5. mysql Transaction configuration items

Innodb_flush_log_at_trx_commit=1

Indicates that the transaction log is immediately written to disk when the transaction commits, and the data and index are also updated

Innodb_flush_log_at_trx_commit=0

The transaction log is not written to disk immediately when the transaction is committed, written every 1 seconds

Innodb_flush_log_at_trx_commit=2

When a transaction commits, it is written to the disk file immediately (it is written to the kernel buffer, but not immediately to the disk, but is refreshed every 1 seconds to the drive, updating the data and index)

6. Explain usage

[extended] Select select_options

The former can draw a table of the field structure and so on, the latter is mainly to give some relevant index information, and today the focus is the latter.
Example:

Select *  from event;

+—-+————-+——-+——+—————+——+———+——+——+——-+|Id|Select_type| Table |Type|Possible_keys| Key |Key_len|Ref|Rows|Extra|+—-+————-+——-+——+—————+——+———+——+——+——-+| 1 |Simple|Event|  All | NULL | NULL | NULL | NULL |  - | |+—-+————-+——-+——+—————+——+———+——+——+——-+1Rowinch Set(0.00Sec

The meaning of each property:

• Id
  • Serial number of the select query
  • Select_type
  • The type of select query is mainly the difference between common queries and complex queries such as federated queries and subqueries.
• Table
  • The table that is referenced by the output row.
• Type
  • The type used by the union query.
  • Type shows the type of access, which is an important indicator, and the resulting 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, it is best to ensure that the query reaches at least the range level, preferably ref.
• Possible_keys
  • Indicates which index MySQL can use to find rows in the table. If it is empty, there is no index associated with it. To improve performance, you can examine where clauses to see if some fields are referenced, or check that the fields are not appropriate for the index.
• Key
  • Displays the keys that MySQL actually decides to use. If no index is selected, the key is null.
• Key_len
  • Displays the key lengths that MySQL decides to use. If the key is null, the length is null. Documentation tips pay particular attention to this value to derive a multi-primary key in what part of MySQL is actually used.
• Ref
  • Shows which field or constant is used together with the key.
• Rows
  • This number indicates how much data MySQL will traverse to find and is inaccurate on InnoDB.
• Extra
  • If it is only index, this means that information is retrieved only from the information in the index tree, which is faster than scanning the entire table.
  • If it is a where used, the where limit is used.
  • If it is impossible where means no where, it is generally not found out what.
  • If this information shows the using Filesort or using temporary, then the where and order by indexes are often out of balance, and if the index is determined by where, then the order by will inevitably cause the using Filesort, it depends on whether to filter and reorder the cost, or first sort and then filter the cost.
• Some common noun explanations
  • Using Filesort
    • MySQL requires an extra pass to find out how rows are retrieved in sorted order.
  • Using index
    • Retrieves column information from a table by using only the information in the index tree without requiring a further search to read the actual rows.
  • Using temporary
    • To resolve the query, MySQL needs to create a temporary table to accommodate the results.
  • Ref
    • For each row combination from the preceding table, all rows with matching index values are read from this table
  • All
    • With no index at all, the performance is very poor.
  • Index
    • Same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.
  • Simple
    • Simple select (Do not use union or subquery)

Reference:

Http://blog.sina.com.cn/s/blog_4586764e0100o9s1.html (the above content is transferred from this article)

MySQL performance analysis and use of explain (RPM)