|NO.Z.00150|——————————|BigDataEnd|——|Java&MySQL.高级.V22|——|MySQL.v23|EXPLAN_possible_keys&key字段介绍|

一、possible_keys 与 key介绍 

### --- possible_keys

——>        显示可能应用到这张表上的索引, 一个或者多个. 
——>        查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用.

### --- key

——>        实际使用的索引，若为null，则没有使用到索引。
——>        （两种可能，1.没建立索引, 2.建立索引，但索引失效）。
——>        查询中若使用了覆盖索引，则该索引仅出现在key列表中。
——>        覆盖索引：一个索引包含(或覆盖)所有需要查询的字段的值,
——>        通过查询索引就可以获取到字段值 理论上没有使用索引,但实际上使用了

EXPLAIN SELECT L1.id FROM L1;

mysql> EXPLAIN SELECT L1.id FROM L1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

### --- 理论和实际上都没有使用索引

EXPLAIN SELECT * FROM L1 WHERE title = 'yanqi01';

mysql> EXPLAIN SELECT * FROM L1 WHERE title = 'yanqi01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

### --- 理论和实际上都使用了索引

EXPLAIN SELECT * FROM L2 WHERE title = 'yanqi02';

mysql> EXPLAIN SELECT * FROM L2 WHERE title = 'yanqi02';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | L2    | NULL       | ref  | idx_title     | idx_title | 303     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---