MySQL performance analysis and explain usage knowledge is the main content of this article, and then let us through some practical examples to introduce the process, I hope to be able to help you.
1. Use the Explain statement to view the results of the analysis
such as explain select * from Test1 where id=1; appears: ID selecttype table type possible_keys key Key_len ref rows extra columns.
which
Type=const means that it was found by index one time;
Key=primary words, the use of the primary key;
Type=all, expressed as 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.
Combined index in 2.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 indexed, such as the from test where key1=1 the order by Key3, with explain parsing, 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=1
Log-slow-queries=d:\mysql5\logs\mysqlslow.log
Record more than 1 seconds in the slow query log
You can use Mysqlsla to analyze it. can also be in the mysqlreport, like
DMS separately analyzes the percentage of select, Update,insert,delete,replace, etc.
Locking of 4.MYISAM and InnoDB
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.
Transaction configuration entries for 5.MYSQL
Innodb_flush_log_at_trx_commit=1
Indicates that the transaction log is written to disk as soon as the transaction commits, and the data and indexes 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 immediately written to the disk file (this 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
Explain usage
EXPLAIN tbl_name or: EXPLAIN [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
- 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)
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.
The knowledge about MySQL performance analysis and explain usage is introduced here, I hope this introduction can have a harvest for you!
Source: http://blog.sina.com.cn/s/blog_4586764e0100o9s1.html.
MySQL performance analysis and the use of explain