MySQL Learning-explain

The table header contains:

ID---select_type---table---type---possible_keys---key---key_len---ref---rows---Extra

• The sequence number of the Id:select query, which contains a set of numbers that represents the order in which a SELECT clause or action table is executed in a query, in three cases:
  

  Three cases:1. Same ID, execution order from top to bottom 2. ID is different, if it is a subquery, ID number will be incremented, the higher the ID value priority, the first is executed 3. The ID is thesame, the ID is large, The sibling is  derived from top to bottom derived

• select_type
  

   simple: A simple select query that contains no subqueries or unionprimary: If any complex sub-parts are included in the query , the outermost query is marked as subquery: A subquery is included in the Select or where list derived: The subquery contained in the From list is marked as Derived,mysql recursively executes the subquery, placing the result in the temporary table. Union: If the second select appears after the union and is just marked as union, if the Union is contained in a subquery in the FROM clause, the outer select is marked as derivedunion result: The select that gets the result from the Union table  

   

• type
  

   all:full table scan, traversing the entire table to find a matching row index:full index Scan, 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, range: Retrieves only the row of the given range, uses an index to select the row, and the key column shows which index is used. This is generally the case in your where statement between\  <\>\in, and so on, this range Scan index scan is better than the full table scan, Since it only needs to start at some point of the index and end at another point without scanning all indexes, ref: a non-unique index scan that returns all rows that match a single value, essentially an indexed access that returns all rows matching a single value, however, it may find multiple rows that match the criteria. So he should belong to the search and Scan hybrid eq_ref: Unique index Scan, for each index key, only one record in the table matches it. Common in primary key or unique index Scan system: Table with only one row of records, this is a special case of the const type, usually does not appear, this ignores  const   indicates that it can be found by index one time, and const is used to compare primary key or unique index because it matches only one row of data, so fast, if the primary key is placed in the where list, MySQL can convert the query to a constant  null  :system  >const  >eq_ref>ref>range  >index>all 

   

• Possible_keys,key,key_len
  

  Pessible_keys: Displays the indexes that may be applied to this table, one or more, if there is an index on the field that the query involves, the index will be listed, but not necessarily the actual use of the key: The index is actually used, if it is null, the index is not used, if the overwrite index is used in the query. The index appears only in the key list
  Key_len: The value displayed is the maximum possible length of the indexed field, not the actual length, that is, the Key_len is calculated from the table definition and not retrieved from the table.

• Ref
  

  Shows which column of the index is used, if possible, is a constant, which column or constant is used to find the value on the index column

• Rows
  

  Approximate number of rows to be read to find the required records based on table statistics and index selection

MySQL Learning-explain