The explain command 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 higher 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 join;
- 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: Scanning all table data;
- index: Traversing the index;
- range: Index range search;
- index_subquery: Using ref in subquery;
- unique_subquery: Using eq_ref in subquery;
- ref_or_null: Optimization of ref for null indexing;
- fulltext: Using full-text index;
- ref: Searching data using a non-unique index;
- eq_ref: Associating with primary key or unique index in join query;
- const: Placing a primary key after where as a condition query, the MySQL optimizer can optimize this query into a constant.
Summary#
Due to different versions of MySQL, index invalidation strategies also vary.
Most index invalidation situations are explicit, but a few may vary depending on the version of MySQL.
Therefore, we should conduct research in the practical process, analyze specific problems specifically, and if unable to accurately judge, we can use explain for verification.