CC00151.bigdatajava——|Java&MySQL.高级.V23|——|MySQL.v24|EXPLAIN_key_len&ref字段介绍|

yanqi_vip

已于 2022-04-09 22:25:54 修改

阅读量85

点赞数

分类专栏： bigdatav005——mysql 文章标签： mysql 数据库 java sql 索引

于 2022-04-06 16:03:00 首次发布

不予转载

本文链接：https://blog.csdn.net/yanqi_vip/article/details/124031591

版权

bigdatav005——mysql 专栏收录该内容

163 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中`key_len`字段的含义及其在查询优化中的作用。`key_len`表示索引中使用的字节数，它帮助判断是否充分利用了索引。通过示例展示了当为不同字段添加索引后，`key_len`的变化，揭示了索引使用情况。同时，还解释了`ref`字段，它显示了查询中哪些列或常量被用于查找索引列上的值。通过对查询计划的分析，可以更好地理解和优化数据库查询性能。

摘要由CSDN通过智能技术生成

一、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        |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+