1. Use explain statement to view analysis results
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 said that through the index once found;
Key=primary, the use of the primary key;
Type=all, expressed as full table scan;
Key=null indicates no use to index. Type=ref, because this is considered to be multiple matching rows, in a federated query, generally ref.
combined index in 2.MYSQL
Assuming that the table has Id,key1,key2,key3 and that the three are grouped into one index, the
Such as:
Copy Code code as follows:
Where key1= ....
where Key1=1 and key2=2
where Key1=3 and Key3=3 and key2=2
According to the leftmost principle, these can be indexed, such as the from Test where Key1=1 ordered by Key3, with explain parsing, only the Normal_key index is used, but only the WHERE clause works, and the following order by needs to be sorted.
3. Use 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 a slow query log
You can use Mysqlsla to analyze them. can also be in the mysqlreport, like
DMS analyzed the percentage of select, Update,insert,delete,replace, etc.
locking of 4.MYISAM and InnoDB
In MyISAM, note that the table lock comes in, for example, after multiple update operations, and then select, you will find that the select operation is locked, you must wait for all update operations complete, and then select
InnoDB words are different, with the line lock, there is no above problems.
transaction configuration items for 5.MYSQL
Innodb_flush_log_at_trx_commit=1
Indicates that transaction logs are written to disk immediately when a transaction is committed, and that data and indexes are updated as well.
Innodb_flush_log_at_trx_commit=0
Transaction log is not written to disk immediately when transaction commits, written every 1 seconds
innodb_flush_log_at_trx_commit=2
When a transaction commits, write to the disk file immediately (this is only written to the kernel buffer but not immediately to the disk, but is refreshed every 1 seconds to the disk, 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 to talk about the latter.
Example
Copy Code code as follows:
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 a select query is primarily a complex query that distinguishes between common and federated queries, subqueries, and so on.
Table
The table referenced by the row being exported.
Type
The type used by the union query.
Type shows access types and is a more important metric, and the resulting values are from good to bad in turn:
System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All
In general, you have to ensure that the query reaches at least range level, preferably to 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 check the WHERE clause to see if some of the fields are referenced, or if the field is not an appropriate index.
Key
Displays the key that MySQL has actually decided to use. If no index is selected, the key is null.
Key_len
Displays the length of the key that MySQL decided to use. If the key is null, the length is null. Document prompts pay special attention to this value to derive which part of MySQL is actually used in a multiple primary key.
Ref
Shows which field or constant is used with the key.
Rows
This number indicates how much data MySQL is going to traverse to find, and is inaccurate on the InnoDB.
Extra
If it is only index, this means that the information is retrieved using only the information in the index tree, which is faster than scanning the entire table.
If it is a where used, the where constraint is used.
If it's a impossible where it means no where, it's usually nothing.
If this information displays using Filesort or using temporary, it will be difficult to find the index of where and order by, and if the index is determined according to the where, then it is bound to cause a using Filesort, this depends on the first filter and then sort the cost, or the first sorting and filtering cost-effective.
About MySQL performance analysis and explain usage knowledge is introduced here, I hope this introduction can be harvested for you!
