Shulou(Shulou.com)06/01 Report--

Main content: explain possible_key, key, key_len, ref, rows entry.

1、possible_key

Represents indexes that may be used during a query. If there is an index on the field involved in the query, the index will appear in the possible_key column to indicate that it may be used, but it may not be used in practice. Example:

mysql> explain select * from t_blog where id = 1;+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | t_blog | const | PRIMARY | PRIMARY | 4 | const | 1 | |+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+1 row in set

Because id is the primary key of t_blog, this field is involved in the query, and there is the word primary key in the possible_key column.

2、key

Indicates the index actually used in the re-query process. If null, it indicates that the index is not used.

In the above example, key is the primary key, which means that the primary key is used in the query process. Complete interpretation: In theory, the primary key is used, but in fact, the primary key is used.

There should be an index in theory, but it is not used in practice, that is, the index is invalid;

If an overlay index is used in the query (the select clause is identical to the index order and fields), possible_key is null and key is the overlay index.

3、key_len

The number of bytes used in the index, the shorter the better. This value indicates the maximum possible length rather than the actual length, for example:

mysql> explain select * from t_blog where title = 'C Language Intensive';+--------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+| 1 | SIMPLE | t_blog | ref | index_1 | index_1 | 153 | const | 1 | Using where; Using index |+----+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+1 row in set

At this time, there is one more query condition:

mysql> explain select * from t_blog where title = 'C LANGUAGE PRESS' and typeId = 2;+----+-------------+--------+------+---------------+---------+---------+-------------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+---------+---------+-------------+------+--------------------------+| 1 | SIMPLE | t_blog | ref | index_1 | index_1 | 158 | const,const | 1 | Using where; Using index |+----+-------------+--------+------+---------------+---------+---------+-------------+------+--------------------------+1 row in set

More query conditions, higher precision, and key_len is also increasing.

4、ref

Shows which column of the index is used, possibly a constant.

There are two formats:

1>const: constant, where ='constant'

2>.. A column in a table in a database is used.

5、rows

How many rows per table are queried by the optimizer

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.