MYSQL SQL Optimization

1. Learn the frequency of various SQL executions with the show status command

The show [session|global]status command can provide server state information, com_xxx indicates the number of times each statement was executed. Innodb_xxx at least for INNODB engines

These parameters make it easy to understand whether the current database is inserted as primary or query-focused, and how many types of SQL execution scale.

Connections: Number of attempts to connect to MySQL server

Uptime: Server Working time

Slow_querier: Slow Query count

2. Locating SQL statements that perform less efficiently

Location by slow query log, not very accurate

You can use show processlist to view the current MySQL threads in progress, including the state of the thread, whether the lock table, and so on, can see the execution of SQL in real time. Some locking table operations are also optimized.

3. Analyze the execution plan for inefficient SQL through explain

Select_type: Represents the type of select, a common value simple (i.e. not using table joins or subqueries). PRIMARY (main query, or outer query). Union (second or subsequent query statement in Union), subquery (the first select in a subquery, etc.)

Table: output result set tables

Type: Indicates how MySQL finds the desired row in the table, or is called a service type.

Common, all INDEX RANGE REF eq_ref const,system null from left to right, performance from worst to best

All: Full table scan, traverse full table

Index: Indexed full scan, MySQL traverses entire index query matching rows

Range:, index range Scan, common to <,<=,>,>=,between and other operators

Ref, using a non-unique index scan or prefix scan of a unique index, returns a row of records that match a single value

Eq_ref: Similar to ref, the difference is that the index used is a unique index, for one day the record match in the key table of each index, simply, is the use of primary key or unique index as the association condition in a multi-table connection

Const, Systom: There is a maximum of one matching row in a single table, and the query is very fast, so the other columns in the matching row can be handled by the optimizer as constants in the current query. Lee