Mysql Performance Analysis and explain

The explain syntax is as follows:

Explain [extended] select... From... Where...

If extended is used, you can use the show warnings statement to query the optimization information after the explain statement is executed.

For example, if we execute select uid from user where uname = 'scofield' order by uid, the execution result will be

+ -- + ----- + --- + ------- + --- + -- + --- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ -- + ----- + --- + ------- + --- + -- + --- +

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

Generally, at least the range level should be reached, preferably the ref level. Otherwise, there may be performance problems.

Possible_keys indicates the index that can be applied to the table. If it is NULL, no.

Key refers to the index used.

Key_len is the index length. The smaller the value, the better, without affecting the query accuracy.

Ref indicates that the column of the index is used. It is usually a constant.

Rows indicates the number of rows.

Extra refers to additional information. It is also important. If the value is distinct, it means that the mysql tutorial finds the row that matches the domain row union and no longer searches.

If the value is not exits: mysql optimizes left join. Once the matching row of left join is found, no search is performed.

If the value is rang checked for each: the ideal index is not found.

If it is using filesort, you need to improve the SQL. This indicates that File Sorting is required for mysql execution. This affects efficiency.

If it is using temporary, a temporary table is used. This situation also affects efficiency, and SQL needs to be improved. You can also make improvements at the application layer.

If where used is used, the where statement is used. If the type is all or index, this result is usually displayed. This is generally because the query needs to be improved.

In a system that is generally slightly larger, the join and subquery operations are minimized. Mysql uses the simplest query, which is the most efficient. Join and so on can be solved at the application layer.

 

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 that the index is not 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, 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: mysql5logsmysqlslow. 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
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 the transaction is committed, immediately write the disk file (here only write to the kernel buffer, but not immediately refresh to the disk, but refresh to the disk every one second, and update the data and index 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 | 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.

COMMON GLOSSARY
Using filesort
MySQL requires an additional pass to find out how to retrieve rows in order.

Using index
You can use only the information in the index tree without further searching and reading the actual row to retrieve the column information in the table.

Using temporary
To solve the query, MySQL needs to create a temporary table to accommodate the results.

Ref
For each row combination from the preceding table, all rows that match the index value are read from this table.

ALL
If there is 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 index files are usually smaller than data files.

SIMPLE
Simple SELECT (do not use UNION or subquery)