Use explain to analyze and optimize the view performance composed of multiple tables (more than three tables ).

Use explain to analyze and optimize the view performance composed of multiple tables (more than three tables ).

View:

Create or replace view FLOW_SUBMITPROCESS_V

Select pi. START_USER_ID _, pir. STARTUSERID, pi. PROC_INST_ID _, pir. PROCESSINSTID, pir. CURRENTTASKINSTID as TASKID, pir. PROCESSSTARTER as SUBMITUSERJSON _, ta. NAME _

From ACT_HI_TASKINST ta, ACT_HI_PROCINST pi, FLOW_PROCESSINST_RELA pir

Where ta. ID _ = pir. CURRENTTASKINSTID and pi. PROC_INST_ID _ = pir. PROCESSINSTID;

1. Use the index field of the slave table as the query condition.

Use a field from the table as the query condition (This field in the table is already an index field). From the results, we can see that type has an ALL, full table retrieval, it proves that the index field from the table is not helpful for the query.

Replace the query field with the StartUserID of the primary table (This field is an index field)

2. query performance using fields associated with the primary table in the view
, The queried field is associated with the primary key PROCESSINSTID of the master table, and the efficiency is still a full table search

Performance is optimized when the primary key field of the primary table is used for query.

Summary:
1. When creating a multi-table (three or more tables) associated view, if the fields are in both the primary and secondary tables, try to use the fields in the primary table (especially the primary key of the primary table)
2. Fields in the sub-table (both common fields, primary keys, and index fields) as query conditions are not helpful for queries. Full table search is required.

Description of each explain 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

Which table does the output row come from.

Type

Important performance indicators: types used for joint queries.

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

System (the table has only one row, that is, the constant table ),

Const (a single table can have at most one matching row ),

Eq_ref (for each row above, only one record is queried in this table ),

Ref (using common indexes ),

Ref_or_null (similar to ref, but the condition contains NULL queries ),

Index_merge (index merge optimization ),

Unique_subquery (in is followed by a subquery that queries the primary key field ),

Index_subquery (similar to unique_subquery, which is followed by subqueries for non-unique index fields), range (range query in a single table ),

Index (for each current row, data is obtained by querying the index ),

All (for each current row, data is obtained through full table scan ))

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

Distinct once MYSQL finds the row that matches with the row, it no longer searches

Not exists MYSQL optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.

Range checked for eachRecord (index map: #) does not find the desired index. Therefore, for each row combination in the preceding table, MYSQL checks which index is used, use it to return rows from the table. This is one of the slowest connections using indexes.

When Using filesort sees this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.

The Using index column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are part of the same index.

When Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.

Using Where uses the WHERE clause to limit which rows match the next table or are returned to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this may occur or the query is faulty.

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.