MySQL EXPLAIN中的key_len：精准掌握索引使用情况
<blockquote><p>MySQL系列文章</p>
<p>深入解析MySQL执行计划中最关键的指标之一，助你快速定位索引优化点，提升查询性能！</p>
</blockquote>
<h3 id="一key_len索引使用的精准标尺">一、key_len：索引使用的精准标尺</h3>
<p>在MySQL执行计划中，<strong>key_len</strong>表示查询<strong>实际使用索引的字节长度</strong>。这个指标是索引优化的核心，它能揭示：</p>
<ul>
<li><strong>复合索引使用深度</strong>：显示使用了复合索引的前几列</li>
<li><strong>索引利用效率</strong>：值越大，索引利用率越高</li>
<li><strong>索引失效检测</strong>：NULL值表示索引未被使用</li>
<li><strong>数据类型成本</strong>：不同数据类型在索引中的开销</li>
</ul>
<h3 id="二key_len计算的核心规则重点掌握">二、key_len计算的核心规则（重点掌握！）</h3>
<h4 id="1-基础计算规则">1. 基础计算规则</h4>
<pre><code class="language-math">key_len = 数据类型基础长度 + NULL标记(1字节) + 变长类型额外开销（2字节）
</code></pre>
<h4 id="2-常用数据类型计算表utf8mb4环境">2. 常用数据类型计算表（utf8mb4环境）</h4>
<table>
<thead>
<tr>
<th>数据类型</th>
<th>基础长度</th>
<th>NULL开销</th>
<th>VARCHAR开销</th>
<th>NOT NULL示例</th>
<th>NULL示例</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong>INT</strong></td>
<td>4字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>4</code></td>
<td><code>5</code></td>
</tr>
<tr>
<td><strong>BIGINT</strong></td>
<td>8字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>8</code></td>
<td><code>9</code></td>
</tr>
<tr>
<td><strong>TINYINT</strong></td>
<td>1字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>1</code></td>
<td><code>2</code></td>
</tr>
<tr>
<td><strong>FLOAT</strong></td>
<td>4字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>4</code></td>
<td><code>5</code></td>
</tr>
<tr>
<td><strong>DOUBLE</strong></td>
<td>8字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>8</code></td>
<td><code>9</code></td>
</tr>
<tr>
<td><strong>DATE</strong></td>
<td>3字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>3</code></td>
<td><code>4</code></td>
</tr>
<tr>
<td><strong>DATETIME</strong></td>
<td>8字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>8</code></td>
<td><code>9</code></td>
</tr>
<tr>
<td><strong>TIMESTAMP</strong></td>
<td>4字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>4</code></td>
<td><code>5</code></td>
</tr>
<tr>
<td><strong>CHAR(10)</strong></td>
<td>10×字符集字节</td>
<td>+1字节</td>
<td>-</td>
<td><code>40</code> (utf8mb4)</td>
<td><code>41</code> (utf8mb4)</td>
</tr>
<tr>
<td><strong>VARCHAR(50)</strong></td>
<td>50×字符集字节</td>
<td>+1字节</td>
<td><strong>+2字节</strong></td>
<td><code>202</code> (utf8mb4)</td>
<td><code>203</code> (utf8mb4)</td>
</tr>
</tbody>
</table>
<blockquote>
<p><strong>核心要点</strong>：</p>
<ol>
<li>VARCHAR类型在索引中<strong>固定增加2字节长度前缀</strong><br>
（实际行存储时规则不一致：≤255字符+1字节，>255字符+2字节）</li>
<li>字符集直接影响长度：utf8mb4=4字节/字符，latin1=1字节/字符</li>
<li>NULL列增加1字节开销</li>
</ol>
</blockquote>
<h3 id="三key_len实战解析从案例学优化">三、key_len实战解析：从案例学优化</h3>
<h4 id="案例1复合索引使用深度判断">案例1：复合索引使用深度判断</h4>
<pre><code class="language-sql">-- 表结构
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,-- key_len:50×4+2=202
age TINYINT NOT NULL, -- key_len:1
email VARCHAR(100) NOT NULL, -- key_len:100×4+2=402
INDEX 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）
</code></pre>
<h4 id="案例2字符集对key_len的影响">案例2：字符集对key_len的影响</h4>
<pre><code class="language-sql">-- 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)
</code></pre>
<h4 id="案例3null值的隐藏成本">案例3：NULL值的隐藏成本</h4>
<pre><code class="language-sql">-- 允许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字节）
</code></pre>
<h3 id="四key_len揭示的三大优化机会">四、key_len揭示的三大优化机会</h3>
<h4 id="1-复合索引优化核心">1. 复合索引优化（核心！）</h4>
<p>当key_len < 索引总长度时：</p>
<ul>
<li><strong>问题</strong>：索引未充分利用</li>
<li><strong>解决方案</strong>：<pre><code class="language-sql">-- 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 ...
</code></pre>
</li>
</ul>
<h4 id="2-varchar列优化策略">2. VARCHAR列优化策略</h4>
<pre><code class="language-sql">-- 方案1：前缀索引（减少长度）
ALTER TABLE products ADD INDEX (description(20));
-- key_len从402降为82（VARCHAR(100)→20×4+2）
</code></pre>
<h4 id="3-消除null存储开销">3. 消除NULL存储开销</h4>
<pre><code class="language-sql">-- 优化前（允许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字节/行）
</code></pre>
<h3 id="五高级诊断技巧">五、高级诊断技巧</h3>
<h4 id="1-explain-formatjson推荐">1. EXPLAIN FORMAT=JSON（推荐）</h4>
<pre><code class="language-sql">EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE name='Lisa';
/* 输出片段 */
{
"query_block": {
"table": {
"key_length": 202,
"used_key_parts": ["name"],
// ...其他信息
}
}
}
</code></pre>
<h4 id="2-性能优化检查清单">2. 性能优化检查清单</h4>
<ol>
<li>检查key_len是否接近索引长度</li>
<li>确认复合索引是否满足最左前缀原则</li>
<li>分析VARCHAR列长度是否合理</li>
<li>检查是否有不必要的NULL列</li>
<li>对比不同字符集下的索引大小</li>
</ol>
<h3 id="六总结key_len优化四原则">六、总结：key_len优化四原则</h3>
<ol>
<li><strong>追求最大key_len</strong>：值越接近索引总长度，索引利用越充分</li>
<li><strong>警惕NULL开销</strong>：每允许一个NULL列，key_len增加1字节</li>
<li><strong>VARCHAR成本控制</strong>：长文本字段优先考虑前缀索引或哈希</li>
<li><strong>最左前缀原则</strong>：确保查询条件从复合索引最左侧开始</li>
</ol>
<blockquote>
<p><strong>终极技巧</strong>：当发现key_len显著小于索引长度时，立即检查：</p>
<ul>
<li>是否缺少必要查询条件？</li>
<li>索引列顺序是否合理？</li>
<li>是否存在数据类型转换？</li>
<li>字符集选择是否合适？</li>
</ul>
</blockquote>
<blockquote>
<p>文章的最后，想和你多聊两句。</p>
<p>技术之路，常常是热闹与孤独并存。那些深夜的调试、灵光一闪的方案、还有踩坑爬起后的顿悟，如果能有人一起聊聊，该多好。</p>
<p>为此，我建了一个小花园——我的微信公众号「<strong>[努力的小郑]</strong>」。</p>
<p>这里没有高深莫测的理论堆砌，只有我对后端开发、系统设计和工程实践的持续思考与沉淀。它更像我的<strong>数字笔记本</strong>，记录着那些值得被记住的解决方案和思维火花。</p>
<p>如果你觉得今天的文章还有一点启发，或者单纯想找一个同行者偶尔聊聊技术、谈谈思考，那么，欢迎你来坐坐。<br>
<img src="https://img2024.cnblogs.com/blog/3703499/202601/3703499-20260105210259813-964799315.jpg"></p>
<p>愿你前行路上，总有代码可写，有梦可追，也有灯火可亲。</p>
</blockquote><br><br>
来源：https://www.cnblogs.com/xzqcsj/p/19234645
頁:
[1]