20180625-mysql optimization

Mysql
Data optimization
Basic steps for slow query optimization
0. Run first to see if it is really slow, note the setting Sql_no_cache
1.where condition single Check, lock minimum return record table. This sentence means to apply the where of the query to the table the smallest number of records returned in the table began to look up, single table each field query, to see which field is the highest degree of distinction
2.explain View execution plan, consistent with 1 expected (start query from a table with fewer locked records)
3.order by limit SQL statement allows sorted tables to be prioritized
4. Understanding Business Party usage Scenarios
5. Index reference several principles of index construction
6. Observation results, non-conforming to the expected continuation from 0 analysis

1.sql_no_cache: Do not use the MySQL cache SELECT Sql_no_cache ...
(1) When the SELECT statement is:
Flushcache defaults to False, which means that any time the statement is invoked, the local cache and level two cache are not emptied.
UseCache By default is true, indicating that the result of this statement will be cached at level two.

(2) When an INSERT, UPDATE, DELETE statement:
Flushcache defaults to True, which means that any time the statement is invoked, the local cache and the level two cache are emptied.
The UseCache property is not in this case.

2.explain
ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra
Id:id if the same, can be considered a group, from the top down in order to execute; In all groups, the higher the ID value, the higher the priority, the more the first execution

Select_type: The type of each SELECT clause in the query (simple or complex)

Type: Indicates how MySQL finds the desired row in the table, also known as the "access type", the common types are as follows:
All, index, range, ref, EQ_REF, const, System, NULL
From left to right, performance from worst to best

Possible_keys: Indicates which index MySQL can use to find records in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query.

Key: Displays the index that MySQL is actually using in the query and displays NULL if no index is used

Key_len: Represents the number of bytes used in the index, which calculates the length of the index used in the query (Key_len displays the maximum possible length of the indexed field, not the actual length, that is, the Key_len is computed from the table definition, not retrieved from the table)

Ref: Represents the connection matching criteria for the above table, that is, which columns or constants are used to find the value on the index column

Rows: Indicates the number of rows required to be read by MySQL, based on table statistics and index selection

3. Two tables associated data volume big expense if can be divided into table query and then merged together will be raised high efficiency union all the former to the latter efficiency high not to heavy
Two tables that are associated with a large amount of data in the go sort operation cost is larger can be sorted first in the association is much better

Reference: https://tech.meituan.com/mysql-index.html

20180625-mysql optimization