Optimization Strategy Explain

  • explain +sql
  • Related fieldid.select_type.type.prossible_keys.key.key_len.ref.rows.extra

id: Query sequence

  • Execute from top to bottom with the same ID (table execution order, normal join query changes by number of quantities: Cartesian product order)
  • If the ids are different, subqueries are included and the IDS increase. The greater the ID value, the higher the priority and the earlier the execution. The essence is to check the inner layer, then check the outer layer.

select_type: Query type

  • SIMPLE: simple query, no UNION or subquery.
  • PRIMARYIf the query contains subqueries or other parts, the outer SELECT will be marked as PRIMARY.
  • SUBQUERY: All subqueries that contain subqueries.
  • DERIVEDDERIVED query (temporary table), subqueries that appear in FROM will be marked as DERIVED.
    • A: There is only one table in the FROM subquery.
    • B: In the from subquery, if you have table1 union table2 then table1 is derived and table2 is derivedunion.
  • UNION: In a UNION statement, the SELECT that follows the UNION.
  • UNION RESULT: UNION Result of the query.

type: Index type

  • Const >eq_ref>ref>range>index>all
  • Where system,const is just the ideal case; It actually reaches ref>range
  • system(ignored) : Primary query that has only one data in a system table or derived table.
  • constSQL where only one piece of data can be found, primary key query or unique index single-value query.
  • eq_ref: When a concatenated table is queried, the row from the previous table has only one corresponding row in the current table. Is the best way to join a table other than system and const, and is often used as a join condition with all fields that use a primary key or unique index.
  • ref: Queries using normal indexes (not unique indexes). Returns all matching rows (0 or more)
  • range: Retrieves rows of a specified range, followed by a range query (between,>,<,>=,<=,in: sometimes fails and is downgraded to all)
  • index: Queries all index data
  • all: Queries all data in the table

possible_type: the index that is likely to be used is predicted to be used.

  • If possible_key and key are NULL, there is no index

key: Indicates the actual index

  • If possible_key and key are NULL, there is no index

key_len: The length of the index

  • Used to determine whether a composite index is fully used.
  • If the index field can be NULL, +1 byte (fixed length field), +2 (variable length field)

ref: Specifies the field to which the current table is referenced

  • Constants are const. Other fields are for reference

rows: Indicates the number of data to be indexed

  • The actual number queried using the index

extra:

  • using filesort: A file index consumes a lot of performance and requires an extra sort or query. This is common in order by statements
    • Single-column index: Does not appear if sort and find are one fieldusing filesort, if sort and find are not the same field. Select * from ‘where’ where ‘order by’ where ‘order by
    • Composite indexes: Cross-column queries (where and Order by combined) appearusing filesortWhere and order by in order of index
  • using temporary: High performance loss, using temporary tables, common with group by statements. General query column and group column inconsistent, optimization: query what column to use what column group
  • using where: You need to use the back table to query information. Fields other than the index that you need to query.
  • using index: Performance improvement, index coverage. This query does not read the original file, but only fetkes data from the index, and does not query other column data back to the table. As long as the columns used are all in the index, it is index coverage.
  • Impossiable where: The query condition is always false.
  • using join bufferMysql uses connection caching, indicating poor SQL writing.