MySQL Index optimization 2

The ideal Index

• Frequent queries
• High degree of differentiation
• Small length
• Try to overwrite common query fields

Indexing and sorting

• When querying directly on an index for overwriting an index, there is a sequential, using index
  • In the InnoDB engine, sorting along indexed fields is naturally orderly
  • In the MyISAM engine, the index field is sorted, but the field that is taken out has non-indexed fields, not index-and-back rows, index-and-back rows, but once all data is fetched and sorted, and when explain such SQL statements, a using is found in extra. Filesort
  • Try to avoid filesort, and use the index as much as possible to sort
  • Where a > 3 order by B assumes that both A and B columns are stand-alone indexes, such that SQL explain also has a using filesort, because a A, B is an independent index, only a corresponding index is used in the query, and the index cannot be used when order by

Explain explanation

• Select Type field
  • Simple (does not include subqueries)
  • Primary (contains subqueries or derived queries)
    • Subquery (non-from subquery)
    • Derived (from sub-query)
    • Union
    • Union result
• The Type field parses the key fields of the query
  • All < index < range < ref < Eq_ref < (const, system, NULL)
  • All full table scan
  • Index scans the index tree for all indexes on the node
  • Range Scan by index
  • Ref refers directly to some rows of data through indexed columns
  • Eq_ref refers directly to a row of data through an indexed column, often used in a connection
  • Typically, when you query by primary key, the const appears, and the system appears null when the query does not pass through the table
• Table field
  • The real table name
  • Temporary tables such as TMP
  • Derived such as from type subquery
  • Null does not use table
• Ref field
  • When you connect a query, the reference relationship between the fields in the table
• Possible key field
  • Indexes that may be used
• Key field
  • End-Use index
• Key Len Field
  • Actual length of index used
• Rows field
  • Estimate how many rows to scan
• Extra field
  • The using index is used to index the overlay
  • The using where light Index cannot be positioned, and where to judge
  • Using temporary has been used to temporary tables, the effect is very bad
  • Using Filesort file Sorting, may be on disk, or may be in memory

MySQL Index optimization 2