|NO.Z.00151|——————————|BigDataEnd|——|Java&MySQL.高级.V23|——|MySQL.v24|EXPLAIN_key_len&ref字段介绍|

一、EXPLAIN_key_len&ref字段介绍

### --- key_len介绍

——>        表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.
——>        key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长, 说明索引使用的越充分

### --- 创建表

CREATE TABLE T1(
    a INT PRIMARY KEY,
    b INT NOT NULL,
    c INT DEFAULT NULL,
    d CHAR(10) NOT NULL
);

### --- 使用explain 进行测试
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

mysql> EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | T1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

### --- 索引中只包含了1列,所以,key_len是4。
### --- 为b字段添加索引    

ALTER TABLE T1 ADD INDEX idx_b(b);

### --- 再次测试
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;

mysql> EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | T1    | NULL       | range | PRIMARY,idx_b | idx_b | 8       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+

### --- 两列都使用了索引,所以,这里ken_len是8。
### --- 为d字段添加索引

ALTER TABLE T1 ADD INDEX idx_d(d);

### --- 执行测试
~~~     字符集是utf8 一个字符3个字节，d字段是 char(10)代表的是10个字符相当30个字节

EXPLAIN SELECT * FROM T1 WHERE d = '';
mysql> EXPLAIN SELECT * FROM T1 WHERE d = '';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | T1    | NULL       | ref  | idx_d         | idx_d | 30      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

二、ref 介绍

### --- ref 介绍
~~~     显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值L1.id='1'; 1是常量 , ref = const

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

### --- L2表被关联查询的时候,使用了主键索引, 而值使用的是驱动表(执行计划中靠前的表是驱动表)L1表的ID, 所以 ref = test_explain.L1.id

EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON    L1.id = L2.id WHERE L1.title = 'yanqi01';
mysql> EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON    L1.id = L2.id WHERE L1.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 |
|  1 | SIMPLE      | L2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_explain.L1.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+

---

 

 

 

 

 

 

 

 

 

---

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

---