explain can be used to analyze SQL statements, the specific usage is as follows:
Just add explain in front of the SQL statement to be executed.
The meanings of each field are as follows:
- id: Selection identifier, the larger the id, the higher the priority, and the earlier it is executed;
- select_type: Indicates the type of query;
- table: The table that outputs the result set;
- partitions: Matching partitions;
- type: Indicates the type of table connection;
- possible_keys: Indicates the indexes that may be used during the query;
- key: Indicates the actual index used;
- key_len: The length of the index field;
- ref: Comparison between columns and indexes;
- rows: Rough estimate of the number of rows;
- filtered: Percentage of rows filtered by table conditions;
- Extra: Description and explanation of the execution situation.
The most important field is the type field, with the following value types:
- all: Scan the entire table data;
- index: Traverse the index;
- range: Index range lookup;
- index_subquery: Use ref in subqueries;
- unique_subquery: Use eq_ref in subqueries;
- ref_or_null: Optimization of ref for null indexing;
- fulltext: Use full-text indexing;
- ref: Use non-unique index to find data;
- eq_ref: Use primary key or unique index association in join queries;
- const: Place a primary key after where as a condition query, and the MySQL optimizer can optimize this query into a constant.
Summary#
Due to different versions of MySQL, the index invalidation strategy also varies.
Most index invalidation situations are explicit, but a few index invalidations may vary depending on the version of MySQL.
Therefore, we should conduct research in the practical process, analyze specific problems specifically, and if there is no accurate judgment, we can use explain for verification.