MySQL performance optimization common tools

MySQL performance optimization common tools:

Common Performance testing tools

Show Engine InnoDB Status

Show Full Processlist

Information_schema.processlist

Explain

CREATE TABLE Innodb_table_monitor (a int) engine=innodb;

Innodb_locks

Innodb_lock_wait

Innodb_trx

Zabbix Monitoring

Pt-tools Tools

Two ways to optimize performance:

1. Interpretation of the implementation plan

Mysql> Explain select * from Mysql.user, +----+-------------+-------+------+---------------+------+---------+---- --+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | Simple | user | All | NULL | NULL | NULL |   NULL | 11 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+

Explain: Important column explanations:

Select_type: Query type

(

Simple: Easy query, no union, sub-query.

Primary: The outer Select, in the statement with the subquery, the outermost select query is primary

)

Type: Access types, how tables are accessed

(

Const/system: Unique key lookup or primary key lookup (=)

Ref: Non-unique index (=)

Range: Scope lookup, >,<,>=,<=,like ' dddd% ', in (', '), between and

Eq_ref: When two tables are connected, the connection field of the right table is the unique key or the primary key when this access occurs

Index: Full index Scan

All: Full table scan

)

Ref: Column or constant for index comparison

Rows: This number indicates how much data MySQL will traverse to find and is not accurate on InnoDB.

Extra:

(Using where,using temporary,using Filesort,

Using Index,

Using Join Buffer,

Impossible where)

Explain related explanations

Http://www.cnblogs.com/linjiqin/p/4125898.html

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1870212

MySQL performance optimization common tools