Shulou(Shulou.com)05/31 Report--

In this article, the editor introduces in detail "how to use MySQL's explain". The content is detailed, the steps are clear, and the details are handled properly. I hope this article "how to use MySQL's explain" can help you solve your doubts.

What can explain do?

   through the explain statement, we can analyze the following results

Table read order data read operation type of operation reference between tables which indexes can be used how many rows in each table are queried by the optimizer which indexes are actually used 2, how to use explain

   usage: explain + SQL statement

MariaDB [class_info] > explain select * from student +-id | select_type | table | type | key | key_len | ref | rows | Extra | +- -+-+ | 1 | SIMPLE | student | ALL | NULL | 1 | | +-+ -+-+ 1 row in set (0.00 sec)

   expain shows 10 fields, which are id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

   summary description:

Field description id selection identifier select_type query type table output result set table type table join type possible_keys query may use index key actual use index key_len index field length ref column compared with index number of rows scanned by rows (estimated number of rows) description and description of Extra implementation 3. Meaning of explain fields 3.1 id

The    id is the sequence number of the select query that contains a set of numbers indicating the order in which the select clause or action table is executed in the query. The results of id have the following three situations:

  ● id is the same, and the execution order is from top to bottom, regardless of the order in sql.

If   ● is a subquery, the sequence number of id will be incremented. The larger the id, the higher the priority, and the more it will be executed first.

If the   ● id is the same, it can be thought of as a group, executed sequentially from top to bottom; in all groups, the higher the id value, the higher the priority and the first execution.

3.2 select_type

   select_type displays the type of each select clause in the query. The commonly used select_type types are simple, primary, subquery, derived, union, union result

   (1) simple (simple select, no complex queries such as union or subqueries)

   (2) primary (outermost query in a subquery. If the query contains any complex subsections, the outermost select is marked primary)

   (3) subquery (including subqueries in the select or where list)

   (4) derived (subqueries included in the from list are marked as derived (derived), and MySQL recursively executes these subqueries to put the results in the temporary table)

   (5) union (second or subsequent select statement in union)

   (6) union result (result of union, all subsequent select starting with the second select in the union statement)

3.3 table

   table displays the name of the table in the database accessed in this step (showing which table the data in this row is about).

3.4 type

What    type shows is which type is used for the query. Type contains types such as all, index, range, ref, eq_ref, const, system, and NULL, and its performance increases in turn.

  ● all: Full Table Scan, MySQL will traverse the entire table to find matching rows

  ● index: Full Index Scan,index differs from ALL in that index only traverses the index tree

  ● range: retrieves only a given range of rows, using an index to select rows

  ● ref: indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

  ● eq_ref: similar to ref, the difference is that the index used is a unique index. For each index key value, only one record in the table matches. To put it simply, primary key or unique key is used as the association condition in multi-table joins.

  ● const, system: use these types of access when MySQL optimizes some part of the query and converts it to a constant. If you put the primary key in the where list, MySQL can convert the query to a constant, and system is a special case of type const, using system when the query table has only one row.

  ● NULL: MySQL breaks up statements during optimization and executes without even accessing tables or indexes. For example, selecting a minimum value from an index column can be done through a separate index lookup.

3.5 possible_keys

   possible_keys shows one or more indexes that may be applied to this table. If an index exists on the fields involved in the query, the index is listed, but not necessarily used by the query. (the index that can be utilized by this query, if no index shows null)

3.6 key

   key shows that the key (index) that MySQL actually decides to use must be included in the possible_keys. If no index is selected, it is NULL. To force MySQL to use or ignore indexes in possible_keys columns, use force index, use index, or ignore index in the query.

3.7 key_len

   key_len represents the number of bytes used in the index, and can be used to calculate the length of the index used in the query (the value displayed by key_len is the maximum possible length of the index field, not the actual used length, that is, the key_len is calculated based on the table definition, not retrieved within the table). The shorter the length, the better without losing accuracy.

3.8 ref

   ref shows which column of the index is used, indicating the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

3.9 rows

   rows estimates the number of rows in the result set, indicating that MySQL estimates the number of rows that need to be read to find the required records based on table statistics and index selection.

3.10 Extra

   Extra this column contains the details of the query resolved by MySQL, in the following cases:

When the request columns of    are all parts of the same index, it means that the mysql server will filter the rows after the storage engine has retrieved them.

  ● Using temporary indicates that MySQL needs to use temporary tables to store result sets, which is common in sorting and grouping queries, such as group by; order by

  ● Using filesort when the Query contains order by operations, and the sort operation that cannot be done with the index is called "file sorting"

The   ● Using join buffer change emphasizes that indexes are not used when getting connection conditions and that connection buffers are required to store intermediate results. If this value occurs, it should be noted that indexes may need to be added to improve energy depending on the specific circumstances of the query.

The value of   ● Impossible where emphasizes that the where statement results in no qualified rows (no results are possible by collecting statistics).

The value of   ● Select tables optimized away means that by using the index alone, the optimizer may return only one row from the aggregate function result

Use from dual or do not contain any from clauses in   ● No tables used Query statements

After reading this, the article "how to use the explain of MySQL" has been introduced. If you want to master the knowledge of this article, you still need to practice and use it yourself. If you want to know more about the article, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.