<p>首先通过慢查询日志和expl<a >ai</a>n分析定位性能瓶颈，重点关注type、rows和extra字段，若出现type为all或extra含using filesort则需优化；2. 根据查询模式选择合适索引类型，优先使用b-tree索引，高选择性列前置，遵循最左前缀原则设计复合索引；3. 避免在索引列上使用函数或类型转换以防索引失效；4. 利用覆盖索引减少回表，提升查询效率；5. 定期使用analyze table更新统计信息，结合sys.schema_unused_indexes移除冗余索引，并通过optimize table或在线<a >工具</a>整理碎片；6. 持续监控慢查询日志、performance schema及系统状态变量，动态调整索引策略以适应数据增长和业务变化，确保索引长期高效有效。</p> <p><img src=”https://img.php.cn/upload/article/001/503/042/175462266363708.jpeg” alt=”mysql怎样进行索引优化 mysql索引优化的核心方法与案例”></p> <p>MySQL索引优化，说白了，就是为了让你的数据库查询跑得更快，少花冤枉钱在不必要的IO和CPU上。核心在于巧妙地引导MySQL，让它在茫茫数据中，能像导航一样精准定位到你需要的信息，而不是大海捞针。这通常涉及到选择合适的列来建立索引，理解不同索引类型的适用场景，以及更重要的，如何通过工具去分析和验证你的优化效果。</p> <img src=”https://img.php.cn/upload/article/001/503/042/175462266355377.jpeg” alt=”MySQL怎样进行索引优化 MySQL索引优化的核心方法与案例”><h2>解决方案</h2> <p>MySQL的索引优化，本质上是一门艺术，也是一门科学。它要求你对<a >数据访问</a>模式有深入的理解，并能熟练运用MySQL提供的各种工具。以下是我个人总结的一些核心方法和策略：</p> <ol> <li> <p><strong>理解查询模式：</strong> 在优化之前，你得知道哪些查询是瓶颈。慢查询日志（Slow Query Log）是你的第一手资料，它能告诉你哪些sql语句执行时间过长，是优化的重点对象。我通常会结合<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>pt-query-digest</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>这类工具来分析日志，找出那些“罪魁祸首”。</p> <img src=”https://img.php.cn/upload/article/001/503/042/175462266363708.jpeg” alt=”MySQL怎样进行索引优化 MySQL索引优化的核心方法与案例”> </li> <li> <p><strong>选择合适的索引类型：</strong></p> <ul> <li> <strong>B-Tree索引：</strong> 这是MySQL最常用的索引类型，适用于全值匹配、范围查询、最左前缀匹配等。几乎所有你能在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>JOIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、`<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ORDER BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句中看到的列，都可能用到B-Tree索引。</li> <li> <strong>Hash索引：</strong> 仅用于精确匹配，查询速度极快，但不支持范围查询和排序。InnoDB存储引擎只支持自适应哈希索引，不能手动创建。而Memory存储引擎支持手动创建哈希索引。如果你有大量等值查询，且数据更新不频繁，可以考虑Memory表上的Hash索引。但实际生产中，B-Tree索引覆盖了绝大多数场景。</li> </ul> </li> <li> <p><strong>索引列的选择：</strong></p> <img src=”https://img.php.cn/upload/article/001/503/042/175462266376670.jpeg” alt=”MySQL怎样进行索引优化 MySQL索引优化的核心方法与案例”><ul> <li> <strong>高选择性（Cardinality）的列：</strong> 索引的列值越不重复，选择性就越高，索引效果越好。比如，用户ID、订单号通常是高选择性，而性别、状态等低选择性列单独做索引效果不佳。</li> <li> <strong>WHERE、JOIN、ORDER BY、GROUP BY子句中频繁出现的列：</strong> 这些是查询优化的核心。如果一个列经常出现在这些子句中，那么它就是索引的候选者。</li> <li> <strong>避免在索引列上进行函数操作或类型转换：</strong> 这样做会导致索引失效，MySQL将不得不进行全表扫描。比如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE date(create_time) = ‘2023-01-01′</pre><div class=”contentsignin”></div></div>，这会让<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>create_time</pre><div class=”contentsignin”></div></div>上的索引失效。</li> </ul> </li> <li> <p><strong>复合索引（联合索引）的艺术：</strong></p> <ul> <li> <strong>最左前缀原则：</strong> 这是复合索引的灵魂。一个包含<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2, col3)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的复合索引，可以支持<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col1</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2, col3)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的查询，但不能直接支持<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col2</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col2, col3)</pre><div class=”contentsignin”></div></div>的查询。</li> <li> <strong>列的顺序：</strong> 通常，将选择性高的列放在前面，或者将等值查询的列放在前面，范围查询的列放在后面。这个顺序对查询性能影响很大。</li> <li> <strong>覆盖索引（Covering Index）：</strong> 如果一个查询所需的所有列都包含在索引中，那么MySQL可以直接从索引中获取数据，而无需回表查询，这能极大提升性能。<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>结果中<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列显示<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using index</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>就是覆盖索引的标志。</li> </ul> </li> <li> <p><strong>索引维护：</strong></p> <ul> <li> <strong>定期分析表（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ANALYZE TABLE</pre><div class=”contentsignin”></div></div>）：</strong> 更新索引统计信息，帮助优化器做出更准确的查询计划。</li> <li> <strong>移除冗余和未使用的索引：</strong> 过多的索引会增加写操作的开销，并占用存储空间。MySQL 5.7+可以通过<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>sys.schema_unused_indexes</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>视图来查找未使用的索引。</li> <li> <strong>碎片整理（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OPTIMIZE TABLE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>）：</strong> 对于某些存储引擎（如MyISAM），可以整理表和索引碎片。对于InnoDB，这通常意味着重建表和索引，会锁定表，需要谨慎操作。</li> </ul> </li> </ol> <h3>如何通过EXPLAIN语句分析查询性能并指导索引优化？</h3> <p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>语句是MySQL查询优化的“X光片”，它能清晰地展示MySQL如何执行你的SQL查询。我个人觉得，掌握<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的输出是索引优化路上最关键的一步。</p> <p>当你执行<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN select … FROM … WHERE …;</pre><div class=”contentsignin”></div></div>时，会得到一张表格，其中有几个关键列需要你重点关注：</p> <ul> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>id</pre><div class=”contentsignin”></div></div>：</strong> 查询的标识符，越大越先执行，相同id的从上到下执行。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>select_type</pre><div class=”contentsignin”></div></div>：</strong> 查询类型，如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SIMPLE</pre><div class=”contentsignin”></div></div>（简单查询）、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>PRIMARY</pre><div class=”contentsignin”></div></div>（主查询）、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SUBQUERY</pre><div class=”contentsignin”></div></div>（子查询）、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>union</pre><div class=”contentsignin”></div></div>（联合查询）等。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>table</pre><div class=”contentsignin”></div></div>：</strong> 正在访问的表名。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>type</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> 这是最重要的指标之一，表示MySQL如何找到所需行。<ul> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ALL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> 最差的情况，全表扫描。这意味着你的查询没有用到索引，或者索引失效了。看到这个，你得警惕了。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>index</pre><div class=”contentsignin”></div></div>：</strong> 全索引扫描。虽然比<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ALL</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>好，但仍然是扫描了整个索引。通常发生在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ORDER BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>GROUP BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>子句只使用索引列时。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>range</pre><div class=”contentsignin”></div></div>：</strong> 范围扫描。通常用于<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”><</pre><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>></pre><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>LIKE</pre><div class=”contentsignin”></div></div>（非前缀匹配）、<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>BETWEEN</pre><div class=”contentsignin”></div></div>等操作。这是一个不错的类型。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ref</pre><div class=”contentsignin”></div></div>：</strong> 非唯一索引扫描，或者唯一索引的非前缀扫描。例如，基于一个普通索引列的等值查询。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>eq_ref</pre><div class=”contentsignin”></div></div>：</strong> 唯一性索引扫描，通常发生在联接操作中，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>JOIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>字段是主键或唯一索引。这是非常高效的类型。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>const</pre><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>system</pre><div class=”contentsignin”></div></div>：</strong> 当查询优化器能将查询转换为一个常量时，这是最快的类型。</li> </ul> </li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>possible_keys</pre><div class=”contentsignin”></div></div>：</strong> MySQL认为可能用到的索引。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>key</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> MySQL实际选择使用的索引。如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>key</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>为NULL，说明没有使用索引。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>key_len</pre><div class=”contentsignin”></div></div>：</strong> 使用的索引的长度。越短越好，说明匹配的越精确。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>rows</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> MySQL估计为了找到所需行而扫描的行数。这个值越小越好，直接反映了查询效率。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> 额外信息，这里面藏着很多秘密。<ul> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using filesort</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> MySQL需要对结果进行外部排序，通常发生在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ORDER BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>GROUP BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的列没有索引覆盖时。这是个性能杀手。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using temporary</pre><div class=”contentsignin”></div></div>：</strong> MySQL需要创建临时表来处理查询，通常发生在复杂的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>GROUP BY</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DISTINCT</pre><div class=”contentsignin”></div></div>操作中。这也是个性能杀手。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using index</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>：</strong> 恭喜你，这是一个覆盖索引，查询所需的所有数据都可以在索引中找到，无需回表。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using where</pre><div class=”contentsignin”></div></div>：</strong> 表明MySQL将通过<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>条件来过滤结果。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using index condition</pre><div class=”contentsignin”></div></div>：</strong> 索引条件下推（Index Condition Pushdown, ICP），MySQL 5.6+的优化，它会在存储引擎层进行过滤，而不是将所有数据返回到服务器层再过滤。</li> </ul> </li> </ul> <p>举个例子，如果你看到<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>type: ALL</pre><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra: Using filesort</pre><div class=”contentsignin”></div></div>，那几乎可以肯定，你的查询需要索引优化。比如：</p><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=’brush:sql;toolbar:false;’>EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_time DESC;</pre><div class=”contentsignin”></div></div><p>如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>customer_id</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>没有索引，或者<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>order_time</pre><div class=”contentsignin”></div></div>没有与<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>customer_id</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>构成合适的复合索引，你很可能会看到糟糕的<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>type</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using filesort</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>。我的做法是，先看<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>type</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，再看<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>rows</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，最后看<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，这三者结合起来，基本就能定位问题了。</p> <h3>复合索引的最佳实践与常见误区有哪些？</h3> <p>复合索引，也就是联合索引，是MySQL索引优化中非常强大但也容易踩坑的一个点。我见过太多开发者，因为不理解其原理而白白浪费了索引的功效。</p> <p><strong>最佳实践：</strong></p> <ol> <li> <p><strong>遵循最左前缀原则：</strong> 这是复合索引的基石。一个复合索引<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2, col3)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，可以有效地支持以下查询：</p> <ul> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ?</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div></li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ? AND col2 = ?</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div></li> <li><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ? AND col2 = ? AND col3 = ?</pre><div class=”contentsignin”></div></div></li> <li>甚至<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ? AND col3 = ?</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>（<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col2</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>会被跳过，但<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>col1</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>依然会用到索引）</li> <li>但它不能直接支持<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col2 = ?</pre><div class=”contentsignin”></div></div>或<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col3 = ?</pre><div class=”contentsignin”></div></div>的查询，因为它们不从最左边的列开始。</li> <li> <strong>个人经验：</strong> 在设计复合索引时，我通常会把最常用于等值查询的列放在前面，然后是范围查询的列，最后是用于排序或分组的列。</li> </ul> </li> <li><p><strong>高选择性优先：</strong> 在复合索引中，将选择性（唯一性）最高的列放在最前面。这能让MySQL在索引扫描时，尽快地缩小查找范围。比如，如果你有一个用户表，经常根据<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>city</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>age</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>查询，但<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>city</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的选择性远高于<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>age</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>，那么<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(city, age)</pre><div class=”contentsignin”></div></div>会比<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(age, city)</pre><div class=”contentsignin”></div></div>更有效。</p></li> <li> <p><strong>考虑查询模式的组合：</strong> 如果你的应用有多种查询模式，例如：</p> <ul> <li>查询A：<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ? AND col2 = ?</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div></li> <li>查询B：<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ? AND col3 = ?</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div></li> <li>查询C：<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col1 = ?</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div> 那么一个<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2, col3)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的复合索引可能就能同时优化这三种查询。但如果还有查询D：<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE col2 = ? AND col3 = ?</pre><div class=”contentsignin”></div></div>，那这个复合索引就无能为力了，你可能需要考虑额外的索引。</li> </ul> </li> <li><p><strong>利用覆盖索引：</strong> 如果你的查询只需要索引中的列，那么这个查询就是“覆盖索引”查询。例如，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SELECT col1, col2 FROM table WHERE col1 = ?</pre><div class=”contentsignin”></div></div>，如果存在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>(col1, col2)</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>的复合索引，那么MySQL就无需回表查询，大大提升性能。在<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>结果中，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Extra</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>列显示<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Using index</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>就是这个意思。</p></li> </ol> <p><strong>常见误区：</strong></p> <ol> <li><p><strong>不理解最左前缀原则：</strong> 这是最常见的误区。很多人以为只要列在复合索引里，不管顺序如何，查询都能用到。结果就是创建了索引，但查询性能依然不佳。</p></li> <li> <p><strong>过度索引：</strong> 为每个可能的查询都创建一个独立的索引，或者在一个表上创建了过多的复合索引。</p> <ul> <li> <strong>后果：</strong> 增加磁盘空间占用；写操作（INSERT, UPDATE, delete）时，所有相关索引都需要更新，导致性能下降；优化器在选择索引时，决策成本增加，甚至可能选择错误的索引。</li> <li> <strong>我的建议：</strong> 保持索引数量的精简，一个复合索引能解决多个查询问题时，就尽量用复合索引。</li> </ul> </li> <li><p><strong>索引低选择性列：</strong> 比如，对一个只有“是/否”两个值的布尔列单独创建索引，效果通常很差，因为MySQL可能认为全表扫描更快。当然，如果这个低选择性列是复合索引的第一列，并且后续有高选择性列，那又是另一回事。</p></li> <li><p><strong>索引列上使用函数或表达式：</strong> 任何在索引列上进行的函数操作（如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>DATE()</pre><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SUBSTRING()</pre><div class=”contentsignin”></div></div>, <div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>UPPER()</pre><div class=”contentsignin”></div></div>等）或算术运算，都会导致索引失效。例如，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE YEAR(order_date) = 2023</pre><div class=”contentsignin”></div></div>会让<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>order_date</pre><div class=”contentsignin”></div></div>上的索引失效。正确的做法是，将函数应用于常量，例如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31′</pre><div class=”contentsignin”></div></div>。</p></li> <li><p><strong>不定期检查索引使用情况：</strong> 索引不是一劳永逸的。随着业务发展和数据量变化，一些索引可能变得不再适用，或者新的查询模式需要新的索引。</p></li> </ol> <h3>索引优化后，如何持续监控和维护以保持其高效性？</h3> <p>索引优化不是一次性的任务，它是一个持续的过程。就像汽车需要定期保养一样，数据库索引也需要监控和维护，才能保证其长期的高效性。我个人在实际工作中，会把这部分工作融入到日常的运维流程中。</p> <ol> <li> <p><strong>持续监控慢查询日志：</strong></p> <ul> <li>即使你优化了一批慢查询，新的业务逻辑或数据增长可能又会产生新的慢查询。</li> <li>我通常会设置自动化脚本，定时分析慢查询日志，并对新的慢查询进行告警。这能帮助我及时发现问题，而不是等到用户抱怨时才行动。</li> <li>工具如<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>pt-query-digest</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>、Percona Monitoring and Management (PMM) 都非常有用。</li> </ul> </li> <li> <p><strong>利用MySQL的性能监控工具：</strong></p> <ul> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>SHOW GLOBAL STATUS LIKE ‘Handler_read%’;</pre><div class=”contentsignin”></div></div>：</strong> 这组状态变量可以告诉你MySQL在处理请求时，从表中读取行的次数。如果<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>Handler_read_rnd_next</pre><div class=”contentsignin”></div></div>（随机读取下一行）的值很高，可能意味着存在大量的全表扫描。</li> <li> <strong>Performance Schema 和 sys schema：</strong> 这是MySQL 5.7+提供的强大工具，可以深入分析SQL语句、等待事件、I/O等性能指标。<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>sys.schema_table_io_waits</pre><div class=”contentsignin”></div></div>可以帮你找出哪些表是I/O瓶颈，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>sys.schema_index_statistics</pre><div class=”contentsignin”></div></div>能告诉你索引的使用情况。我发现<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>sys.schema_unused_indexes</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>视图特别有用，它能直接列出那些创建了却从未被使用的索引，这是清理冗余索引的好起点。</li> </ul> </li> <li> <p><strong>定期分析和优化表：</strong></p> <ul> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ANALYZE TABLE tbl_name;</pre><div class=”contentsignin”></div></div>：</strong> 这个命令会重新收集表的统计信息，包括索引的基数（cardinality）。Mysql优化器会根据这些统计信息来决定最佳的查询执行计划。如果数据发生了大量增删改，统计信息可能会过时，导致优化器做出错误的判断。我通常会安排在业务低峰期执行这个操作。</li> <li> <strong><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OPTIMIZE TABLE tbl_name;</pre><div class=”contentsignin”></div></div>：</strong> 对于InnoDB表，<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>OPTIMIZE TABLE</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>实际上等同于<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>ALTER TABLE tbl_name ENGINE=InnoDB;</pre><div class=”contentsignin”></div></div>，它会重建表和索引，消除碎片，并更新统计信息。这通常需要锁定表，所以操作前务必评估影响。对于MyISAM表，它能有效回收空间和整理碎片。我个人在InnoDB表上更倾向于使用<div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>pt-online-schema-change</pre><div class=”contentsignin”></div></div>这类工具进行在线DDL操作，以避免长时间的表锁定。</li> </ul> </li> <li> <p><strong>定期审查索引的有效性：</strong></p> <ul> <li>随着时间的推移，业务需求可能会变化，一些旧的索引可能变得不再重要，而新的查询模式可能需要新的索引。</li> <li>我建议每隔一段时间（比如几个月或半年），就重新审视一下核心表的索引设计，结合慢查询日志和<div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><div class=”code” style=”position:relative; padding:0px; margin:0px;”><pre class=”brush:php;toolbar:false”>EXPLAIN</pre><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div><div class=”contentsignin”></div></div>结果，看看是否有可以移除的冗余索引，或者需要新增的索引。</li> <li>不要害怕删除不必要的索引。虽然创建索引是为了提升查询，但过多的索引反而会拖累写入性能，并增加存储成本。</li> </ul> </li> <li> <p><strong>关注数据增长和分布：</strong></p> <ul> <li>数据量的持续增长，可能会让原本有效的索引变得效率低下。例如，一个在小数据量下表现良好的索引，在大数据量下可能因为选择性降低（比如某个字段的值分布变得不均匀）而失效。</li> <li>数据分布的变化也会影响索引效果。例如，某个时间段的数据特别多，导致范围查询效率下降。</li> <li>理解这些变化，能帮助你预判并调整索引策略。</li> </ul> </li> </ol> <p>总之，索引优化是一个迭代的过程。你需要不断地“观察、分析、优化、再观察”，才能让你的MySQL数据库保持最佳性能。</p>
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
