[MySQL database advanced] index performance analysis explain field explanation

Id:

  • The sequence number of the select query, including a set of numbers, indicating the order in which the select clause or operation table is executed in the query
  • The three cases
    a. The id is the same, and the execution order is from top to bottom
Insert picture description here


. The id is different. If it is a subquery, the id's serial number will increase. The larger the id value, the higher the priority, and the first it will be executed

Insert picture description here


c. The id is the same and different, and exist

Insert picture description here

select_type:

  • 有哪些?:
    Simple, primary, subquery, derived, union, union result
  • 查询的类型: Mainly used to distinguish complex queries such as ordinary queries, joint queries, sub-queries, etc.
  • simple: Simple select query, the query does not contain subqueries or unions
  • primary: If the query contains complex sub-parts, the outermost query is marked as primary
  • subquery: Include a subquery in the select or where list
  • derived: The sub-queries contained in the from list are marked as derived, and MySQL will execute these sub-queries recursively and place the results in a temporary table
  • union: If the second select appears after the union, it will be marked as union; if the union is included in the subquery of the from clause, the outer select will be marked as derived
    * union result: the select that gets the result from the union table

type:

  • ALL,index,range,ref,eq_ref,(const,system),null
  • Access type ranking
  • Show what type of query is used, from best to worst: system>const>eq_ref>ref>range>index>all
  • 一般来说,得保证查询至少在range级别,最好能达到ref
  • system: The table has only one row of records (equal to the system table), this is a special column of const type, usually does not appear, can be ignored
  • const: Indicates that it is found once by index, and const is used to compare primary key or unique index. Because it only matches one row of data, it is fast. If the primary key is placed in the where list, MySQL can convert the query into a constant
  • eq_ref: Unique index scan, for each index key, there is only one record in the table that matches it, which is often used for primary key or unique index scans
  • ref: A non-unique index scan returns all rows that match a single value. It is essentially an index access. It returns all rows that match a single value. However, multiple qualified rows will be found, so it should be a search And scanning
  • range: Only retrieve rows in a given range, and use an index to select rows. The key column shows which index is used. Generally, queries such as between, <,>, in, etc. appear in your where. This range scan index is better than a full table scan, because it only needs to start at one point of the index and end at another point, instead of scanning all indexes
  • index:Full Index Scan, the difference between index and all is that the index type only traverses the index tree, which is usually faster than all because the index file is usually smaller than the data file. (Although index and all read the entire table, index is read from the index and all is read from the hard disk)
  • all:Full Table Scan, traverse the entire table to find matching rows

possible_keys,keys:

  • pssible_keys: Display one or more indexes that may be applied to this table. If there is an index on the field involved in the query, the index will be listed, but it may not be actually used
  • key: The index actually used. If it is null, the index is not used. If a covering index is used in the query, the index only appears in the key list

key_len:

  • Indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query. Without loss of accuracy, the shorter the length, the better.
  • The value displayed by Key_len is the maximum possible length of the index field 并非实际使用长度, that is, key_len is calculated according to the table definition, not retrieved from the table

ref:

  • The column showing the index is used, if possible, a constant. Those columns or constants are used to find the value on the indexed column

rows:

  • According to table statistics and index selection, roughly estimate the number of rows needed to read the required records

Rxtra:

Contains additional information that is not suitable for display in other columns, but is very important

Using filesort:It means that MySQL will use an external index to sort the data, instead of reading it according to the index order in the table. The sorting operation that cannot be completed by the index in MySQL is called file sorting.

Using temporay: A temporary table is used to save the intermediate results, and MySQL uses a temporary table when sorting the query results. Commonly used in order by and group by query group by

Using indexThe :select operation uses a covering index to avoid accessing the data rows of the table, which is very efficient. If the Using where appears at the same time, it means that the index is used to search for the index key value. If there is no Using where, it means that the index is used to read the data instead of performing the search action

覆盖索引:

Insert picture description here

using where: Use where filtering

using join buffer: Connection cache is used

impossible whereThe value of the :where clause is always false and cannot be used to get any tuples

select table optimized away: Without the gruop by clause, optimize the MIN/MAX operation based on the index or optimize the count(*) operation for the MyISAM storage engine. You don’t have to wait until the execution phase is performing calculations, and the query execution plan generation phase is optimized

distinct: Optimize the distinct operation, stop the action of finding the same value after finding the first matching tuple