当前位置：首页 > news >正文

MySQL EXPLAIN中的key_len终极指南：精准掌握索引使用情况

news 2025/7/18 11:09:09

深入解析MySQL执行计划中最关键的指标之一，助你快速定位索引优化点，提升查询性能！

一、key_len：索引使用的精准标尺

在MySQL执行计划中，key_len表示查询实际使用索引的字节长度。这个指标是索引优化的核心，它能揭示：

复合索引使用深度：显示使用了复合索引的前几列
索引利用效率：值越大，索引利用率越高
索引失效检测：NULL值表示索引未被使用
数据类型成本：不同数据类型在索引中的开销

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'shipped';-- 输出示例（修正对齐问题）：
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_composite | idx_composite | 86      | const,const |    1 | NULL  |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-------+

二、key_len计算的核心规则（重点掌握！）

1. 基础计算规则

key_len = 数据类型基础长度 + NULL标记(1字节) + 变长类型额外开销

2. 常用数据类型计算表（utf8mb4环境）

数据类型	基础长度	NULL开销	VARCHAR开销	NOT NULL示例	NULL示例
INT	4字节	+1字节	-	`4`	`5`
BIGINT	8字节	+1字节	-	`8`	`9`
TINYINT	1字节	+1字节	-	`1`	`2`
FLOAT	4字节	+1字节	-	`4`	`5`
DOUBLE	8字节	+1字节	-	`8`	`9`
DATE	3字节	+1字节	-	`3`	`4`
DATETIME	8字节	+1字节	-	`8`	`9`
TIMESTAMP	4字节	+1字节	-	`4`	`5`
CHAR(10)	10×字符集字节	+1字节	-	`40` (utf8mb4)	`41` (utf8mb4)
VARCHAR(50)	50×字符集字节	+1字节	+2字节	`202` (utf8mb4)	`203` (utf8mb4)
DECIMAL(10,2)	整数4字节+小数2字节	+1字节	-	`6`	`7`

核心要点：

VARCHAR类型在索引中固定增加2字节长度前缀（实际行存储：≤255字符+1字节，>255字符+2字节）
字符集直接影响长度：utf8mb4=4字节/字符，latin1=1字节/字符
NULL列增加1字节开销

三、key_len实战解析：从案例学优化

案例1：复合索引使用深度判断

-- 表结构
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,  -- key_len:50×4+2=202age TINYINT NOT NULL,        -- key_len:1email VARCHAR(100) NOT NULL, -- key_len:100×4+2=402INDEX idx_profile (name, age, email)
) CHARSET=utf8mb4;-- 场景1：仅使用name列
EXPLAIN SELECT * FROM users WHERE name = 'John';
-- key_len = 202（复合索引第一列）-- 场景2：使用前两列
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 30;
-- key_len = 203（202+1）-- 场景3：使用所有列
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 30 AND email = 'john@example.com';
-- key_len = 605（202+1+402）

案例2：字符集对key_len的影响

-- latin1字符集对比
CREATE TABLE logs_latin1 (message VARCHAR(100) NOT NULL
) CHARSET=latin1;CREATE TABLE logs_utf8mb4 (message VARCHAR(100) NOT NULL
) CHARSET=utf8mb4;EXPLAIN SELECT * FROM logs_latin1 WHERE message = 'error';
-- key_len = 102 (100×1 + 2)EXPLAIN SELECT * FROM logs_utf8mb4 WHERE message = 'error';
-- key_len = 402 (100×4 + 2)

案例3：NULL值的隐藏成本

-- 允许NULL的列
ALTER TABLE users MODIFY age TINYINT NULL;-- 相同查询条件
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age = 30;
-- key_len = 204（202+1+1，比非NULL多1字节）

四、key_len揭示的三大优化机会

1. 复合索引优化（核心！）

当key_len < 索引总长度时：

问题：索引未充分利用

解决方案：

-- 1. 补充缺失查询条件
SELECT ... WHERE col1=1 AND col2=2 AND col3=3-- 2. 重建索引（高频查询列前置）
ALTER TABLE orders DROP INDEX idx_old;
ALTER TABLE orders ADD INDEX idx_new (status, user_id, created_at);-- 3. 使用覆盖索引
SELECT indexed_columns FROM table WHERE ...

2. VARCHAR列优化策略

-- 方案1：前缀索引（减少长度）
ALTER TABLE products ADD INDEX (description(20)); 
-- key_len从402降为82（VARCHAR(100)→20×4+2）-- 方案2：ENUM替代（有限值场景）
ALTER TABLE orders MODIFY status ENUM('pending','shipped','completed') NOT NULL;
-- key_len≈1-2字节-- 方案3：压缩长文本+哈希索引
ALTER TABLE articles ADD COLUMN content_hash BINARY(16) NOT NULL,ADD INDEX (content_hash);

3. 消除NULL存储开销

-- 优化前（允许NULL）
ALTER TABLE users MODIFY phone VARCHAR(20) NULL;
-- key_len=20×4+2+1=83-- 优化后（禁止NULL）
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL DEFAULT '';
-- key_len=82（节省1字节/行）

五、高级诊断技巧

1. EXPLAIN FORMAT=JSON（推荐）

EXPLAIN FORMAT=JSON 
SELECT * FROM users WHERE name='Lisa';/* 输出片段 */
{"query_block": {"table": {"key_length": 202,"used_key_parts": ["name"],// ...其他信息}}
}