如何利用MySQL索引提升查询性能技巧 MySQL索引优化详细教程助你快速上手

<p><a style="color:#f60; text-decoration:underline;" title="mysql" href="https://www.php.cn/zt/15713.html" target="_blank">mysql</a>索引通过b-tree结构加速数据检索，减少全表扫描和io开销；2. 索引类型包括主键、唯一、普通、复合、全文和空间索引，应根据数据特性和查询需求选择；3. 使用expl<a style="color:#f60; text-decoration:underline;" title="ai" href="https://www.php.cn/zt/17539.html" target="_blank">ai</a>n分析执行计划，关注type、key、rows和extra等字段判断索引使用情况；4. 复合索引遵循最左前缀原则，查询条件需从索引最左列开始连续匹配；5. 避免对索引列使用函数、类型转换、前导通配符like或负向查询以防止索引失效；6. 优先为高选择性列和常用查询条件创建复合索引，并考虑覆盖索引提升性能；7. 定期监控索引使用情况，删除冗余索引，避免过度索引影响写性能和存储效率；8. 在低峰期使用alter table force或optimize table重建索引以减少碎片，保持查询效率。</p> <p><img src="https://img.php.cn/upload/article/001/503/042/175634046655844.jpg" alt="如何利用MySQL索引提升查询性能技巧 MySQL索引优化详细教程助你快速上手"></p> <p>MySQL索引是提升查询性能的关键，说白了，它就像一本书的目录，能让你快速定位到需要的信息，而不用一页一页地翻。通过合理创建和使用索引，能够显著减少数据库的IO操作，加速数据检索，让你的系统跑得更快，用户体验也更好。</p> <h3>解决方案</h3> <p>要真正利用MySQL索引提升查询性能，我们得从它的本质和使用策略入手。在我看来，这不仅仅是“建个索引”那么简单，它更像是一门艺术，需要你对数据、查询模式以及MySQL内部机制有深入的理解。</p> <p>首先，核心在于理解索引的工作原理。大多数MySQL索引（比如InnoDB的B-Tree索引）都是以B-Tree（或B+Tree）结构存储的。这种树形结构能让数据库系统在查找数据时，通过几次树的遍历就能找到目标数据行，而不是扫描整个表。这大大减少了磁盘I/O，因为磁盘I/O是数据库性能瓶颈的常见元凶。</p> <p>创建索引时，你需要考虑几个关键点：</p> <ol> <li> <strong>选择性（Cardinality）</strong>：索引列的唯一值越多，选择性越高，索引效果越好。比如，性别字段（男/女）选择性就很低，不适合单独建索引。</li> <li> <strong>查询模式</strong>：你的SQL语句中，哪些列经常出现在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">JOIN</pre></div>条件、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ORDER BY</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>中？这些是建立索引的重点。</li> <li> <strong>复合索引（联合索引）</strong>：当你的查询条件涉及多个列时，考虑创建复合索引。但要记住“最左前缀原则”，这个我们后面会详细聊。</li> <li> <strong>索引维护成本</strong>：索引虽然能加速读操作，但会增加写操作（INSERT/UPDATE/DELETE）的开销，因为每次数据变动，索引也需要更新。所以，不要盲目地给所有列都建索引。</li> <li> <strong>覆盖索引（Covering Index）</strong>：如果一个索引包含了查询所需的所有列，那么MySQL可以直接从索引中获取数据，而无需回表查询，效率极高。这是优化查询的一个高级技巧。</li> </ol> <p>分析<a style="color:#f60; text-decoration:underline;" title="工具" href="https://www.php.cn/zt/16887.html" target="_blank">工具</a>方面，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>命令是你的得力助手。它能展示SQL语句的执行计划，告诉你查询是如何使用索引的，或者<a style="color:#f60; text-decoration:underline;" title="为什么" href="https://www.php.cn/zt/92702.html" target="_blank">为什么</a>没有使用索引。通过分析<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>的输出，你可以清晰地看到查询的瓶颈在哪里，从而有针对性地进行优化。</p> <h3>MySQL索引的类型有哪些，各自适用于什么场景？</h3> <p>说起MySQL的索引类型，其实远不止一种，每种都有它存在的价值和最佳应用场景。搞清楚这些，是咱们能灵活运用索引的前提。</p> <ul> <li><p><strong>主键索引（Primary Key Index）</strong>：这个是最常见的了，每个表通常都有一个主键，它自带唯一性和非空属性。在InnoDB存储引擎中，主键索引是<strong>聚簇索引</strong>。这意味着数据行是按照主键的顺序物理存储的。所以，通过主键查询效率极高，因为它直接定位到数据行本身。适用于所有需要唯一标识和高频查询的表。</p></li> <li><p><strong>唯一索引（Unique Index）</strong>：顾名思义，它保证了索引列中的所有值都是唯一的，但允许有NULL值（可以有多个NULL）。它和主键索引的<a style="color:#f60; text-decoration:underline;" title="区别" href="https://www.php.cn/zt/27988.html" target="_blank">区别</a>在于，唯一索引不一定是聚簇索引，而且一个表可以有多个唯一索引。当你需要确保某一列（或多列组合）的值不重复，但又不想将其设为主键时，唯一索引就派上用场了。比如，用户表的email字段，通常会设为唯一索引。</p></li> <li><p><strong>普通索引（Normal/Non-Unique Index）</strong>：这是最基本的索引类型，没有唯一性的限制。它就是为了加速查询而生，可以创建在任何需要快速检索的列上。绝大多数的查询优化，都是围绕普通索引展开的。</p></li> <li><p><strong>复合索引（Composite Index/联合索引）</strong>：这个就有点意思了，它是指在一个索引上包含多个列。比如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INDEX(last_name, first_name)</pre></div>。它的优势在于，当你的查询条件涉及到多个列时，一个复合索引可能比多个单列索引更有效。但它有一个非常重要的特性，就是“最左前缀原则”，这个我们后面会单独拎出来说。适用于多条件联合查询。</p></li> <li><p><strong>全文索引（Full-text Index）</strong>：这个比较特殊，它主要用于对文本内容进行关键词搜索，比如文章标题、博客内容等。它不是基于B-Tree结构，而是使用倒排索引。当你需要实现类似搜索引擎的文本检索功能时，全文索引是首选。但要注意，它对中文支持可能需要额外配置（比如使用ngram解析器）。</p></li> <li><p><strong>空间索引（Spatial Index）</strong>：主要用于存储和查询地理空间数据，比如经纬度信息。如果你在做地图应用或者需要处理<a style="color:#f60; text-decoration:underline;" title="地理位置" href="https://www.php.cn/zt/36976.html" target="_blank">地理位置</a>相关的查询，这个就用得上了。</p></li> </ul> <p>选择哪种索引，核心思路就是：看你的数据特点和查询需求。主键和唯一索引保障数据完整性并加速特定查询；普通索引是通用加速器；复合索引是多条件查询的利器；而全文和空间索引则是针对特定数据类型的专业工具。</p> <h3>如何判断哪些SQL查询语句可以利用到索引，并进行优化？</h3> <p>判断SQL查询是否利用了索引，以及如何优化，这事儿的核心工具就是MySQL的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>命令。它能给你一份详细的“执行计划”，告诉你数据库在执行你的SQL时，到底做了些什么。</p> <p>你只需要在你的<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT</pre></div>语句前面加上<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>，比如： <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'New York';</pre></div></p> <p>然后，你就会看到一个表格输出，里面有很多列，其中有几个是咱们重点关注的：</p> <ul> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">type</pre></div> 列</strong>：这是最重要的一个指标，它表示MySQL是如何查找表的。</p> <ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</pre></div>：全表扫描，性能最差。意味着你的查询没有用到索引，或者索引不合适。这是我们最想避免的。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">index</pre></div>：索引全扫描。虽然比<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</pre></div>好，因为它避免了回表操作（如果索引是覆盖索引的话），但仍然是扫描了整个索引。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">range</pre></div>：范围扫描。通常是好的，表示索引用于范围查询（如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">></pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"><</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">BETWEEN</pre></div>）。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ref</pre></div> / <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">eq_ref</pre></div>：等值查找。非常高效，表示通过索引进行精确查找。<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">eq_ref</pre></div>用于连接操作，通常是最佳的。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">const</pre></div> / <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">system</pre></div>：最佳类型，表示查询优化器直接将查询转换为一个常量，通常是主键或唯一索引的等值查询。</li> </ul> </li> <li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">possible_keys</pre></div> 列</strong>：MySQL认为可能用于这个查询的索引列表。</p></li> <li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key</pre></div> 列</strong>：MySQL实际决定使用的索引。如果这里是NULL，那说明没用上索引。</p> <div class="aritcle_card"> <a class="aritcle_card_img" href="/ai/2333"> <img src="https://img.php.cn/upload/ai_manual/001/246/273/68d8dabc0512c449.png" alt="灵思AI"> </a> <div class="aritcle_card_info"> <a href="/ai/2333">灵思AI</a> <p>专业的智能写作辅助平台</p> <div class=""> <img src="/static/images/card_xiazai.png" alt="灵思AI"> <span>202</span> </div> </div> <a href="/ai/2333" class="aritcle_card_btn"> <span>查看详情</span> <img src="/static/images/cardxiayige-3.png" alt="灵思AI"> </a> </div> </li> <li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">key_len</pre></div> 列</strong>：MySQL使用的索引的长度。越短越好，说明匹配的越精确。</p></li> <li><p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">rows</pre></div> 列</strong>：MySQL预估需要扫描的行数。这个数字越小越好。</p></li> <li> <p><strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Extra</pre></div> 列</strong>：额外信息，这里面有很多宝贝，能告诉你很多优化方向。</p> <ul> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using filesort</pre></div>：表示MySQL需要对结果进行外部排序，这通常意味着<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ORDER BY</pre></div>子句没有用到索引，性能会比较差。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using temporary</pre></div>：表示MySQL使用了临时表来处理查询，比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">GROUP BY</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DISTINCT</pre></div>操作，这也会影响性能。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using index</pre></div>：<strong>覆盖索引！</strong> 这是非常好的情况，表示查询所需的所有数据都可以从索引中获取，不需要回表查询数据行。</li> <li><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using where</pre></div>：表示使用了<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句进行过滤。</li> </ul> </li> </ul> <p><strong>常见的无法利用索引的情况：</strong></p> <ol> <li> <strong>对索引列进行函数操作</strong>：比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE DATE(create_time) = CURDATE()</pre></div>，即使<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">create_time</pre></div>有索引，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DATE()</pre></div>函数也会导致索引失效。应该改为<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE create_time >= CURDATE() AND create_time < CURDATE() + INTERVAL 1 DAY</pre></div>。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE '%keyword%'</pre></div> 模糊查询</strong>：如果通配符<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">%</pre></div>在开头，索引会失效。<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">LIKE 'keyword%'</pre></div>则可以利用索引。</li> <li> <strong>数据类型不匹配</strong>：比如索引列是<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">VARCHAR</pre></div>，但你用数字去查询，MySQL可能会进行<a style="color:#f60; text-decoration:underline;" title="隐式转换" href="https://www.php.cn/zt/77300.html" target="_blank">隐式转换</a>，导致索引失效。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div> 连接条件</strong>：除非<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OR</pre></div>连接的所有条件列都有索引，并且优化器能有效地使用它们，否则通常会导致索引失效。</li> <li> <strong>负向查询</strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">!=</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;"><></pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOT IN</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">NOT EXISTS</pre></div>等，有时会导致索引失效。</li> <li> <strong>索引列参与计算</strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE column + 1 = 10</pre></div> 这种也会导致索引失效。</li> </ol> <p>优化思路嘛，就是根据<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>的输出，调整你的SQL语句，或者创建/调整索引。看到<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALL</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using filesort</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Using temporary</pre></div>，那肯定是要重点关注的。</p> <h3>复合索引（联合索引）的最佳实践与“最左前缀原则”解析</h3> <p>复合索引，或者叫联合索引，它允许你在一个索引上包含多个列。这在多条件查询中非常有用，能避免创建多个单列索引带来的开销和潜在的查询效率问题。但它有一个核心的概念，那就是“最左前缀原则”。</p> <p><strong>什么是“最左前缀原则”？</strong> 说白了，就是MySQL在使用复合索引时，会从索引的最左边的列开始匹配。如果你的查询条件没有包含复合索引的第一个列，或者跳过了中间的列，那么这个复合索引就可能无法被完全利用，甚至完全失效。</p> <p>举个例子，假设我们有一个用户表<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">users</pre></div>，并且创建了一个复合索引 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">idx_name_age_city</pre></div>，包含 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(last_name, first_name, city)</pre></div> 三个列。</p> <ul> <li> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE last_name = 'Zhang';</pre></div></p> <ul><li>这个查询可以完全利用到 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">idx_name_age_city</pre></div> 索引的 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">last_name</pre></div> 部分。</li></ul> </li> <li> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San';</pre></div></p> <ul><li>这个查询可以利用到 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">last_name</pre></div> 和 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">first_name</pre></div> 两部分。</li></ul> </li> <li> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE last_name = 'Zhang' AND first_name = 'San' AND city = 'Beijing';</pre></div></p> <ul><li>这个查询可以完全利用到 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">last_name</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">first_name</pre></div> 和 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">city</pre></div> 三个部分。</li></ul> </li> <li> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE first_name = 'San';</pre></div></p> <ul><li> <strong>注意！</strong> 这个查询就无法利用到 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">idx_name_age_city</pre></div> 索引，因为它没有包含最左边的 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">last_name</pre></div>。MySQL会进行全表扫描。</li></ul> </li> <li> <p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE last_name = 'Zhang' AND city = 'Beijing';</pre></div></p> <ul><li>这个查询只能利用到 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">last_name</pre></div> 部分，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">city</pre></div> 部分就用不上了，因为中间的 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">first_name</pre></div> 被跳过了。</li></ul> </li> </ul> <p><strong>复合索引的设计策略：</strong></p> <ol> <li> <strong>将最常用的查询条件放在前面</strong>：根据你的业务查询模式，把那些最常出现在<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">WHERE</pre></div>子句中的列放在复合索引的最左边。</li> <li> <strong>选择性高的列放在前面</strong>：如果多个列都被频繁查询，那么将选择性（唯一值数量）最高的列放在前面，这样可以更快地缩小搜索范围。</li> <li> <strong>避免冗余索引</strong>：如果你已经有 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(A, B, C)</pre></div> 的复合索引，那么就没有必要再单独创建 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(A)</pre></div> 或 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(A, B)</pre></div> 的索引了，因为 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(A, B, C)</pre></div> 已经包含了它们。但如果你经常只查询 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(B)</pre></div> 或 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(C)</pre></div>，那可能需要单独的索引。</li> <li> <strong>考虑覆盖索引</strong>：如果你的查询只需要索引中的列，而不需要回表查询数据行，那么这个索引就是覆盖索引。设计复合索引时，可以考虑把查询结果中需要的列也包含进去，以达到覆盖索引的效果，进一步提升性能。</li> </ol> <p>总的来说，复合索引的设计是一个权衡的过程，既要考虑查询效率，又要考虑索引维护的成本和存储空间。多用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>去验证你的索引设计是否有效，这比任何理论知识都来得实在。</p> <h3>索引维护与管理策略</h3> <p>索引不是建好就一劳永逸了，它也需要适当的维护和管理，才能持续发挥最佳效果。这就像我们日常生活中，光买了好工具还不够，还得定期保养不是？</p> <p><strong>索引的创建与删除</strong> 这是最基础的操作，咱们用SQL命令就能搞定：</p> <ul> <li> <strong>创建索引</strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>CREATE INDEX idx_name ON table_name (column1, column2); -- 或者在创建表时指定 CREATE TABLE my_table ( id INT PRIMARY KEY, name VARCHAR(100), age INT, INDEX idx_name_age (name, age) );</pre></div></li> <li> <strong>删除索引</strong>：<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>DROP INDEX idx_name ON table_name;</pre></div><p>删除索引通常发生在索引不再需要、或者需要重建以优化结构时。</p> </li> </ul> <p><strong>索引的重建或优化</strong> 随着数据的不断插入、更新和删除，索引可能会出现碎片化。这会导致索引的物理存储不再连续，从而降低查询效率。虽然InnoDB引擎在处理碎片方面做得比较好，但有时我们仍然需要考虑优化。</p> <ul> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">ALTER TABLE ... FORCE</pre></div></strong>：这个命令可以强制重建表和索引，相当于把表的数据和索引都重新整理一遍。<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>ALTER TABLE my_table FORCE;</pre></div></li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OPTIMIZE TABLE</pre></div></strong>：对于InnoDB表，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">OPTIMIZE TABLE</pre></div>实际上会复制表，然后删除旧表，这也能达到整理碎片、回收空间的效果。<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class='brush:sql;toolbar:false;'>OPTIMIZE TABLE my_table;</pre></div><p>这些操作通常在业务低峰期进行，因为它们可能会锁定表，影响正常服务。</p> </li> </ul> <p><strong>索引的监控</strong> 了解你的索引使用情况，是优化决策的重要依据。</p> <ul> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SHOW INDEX FROM table_name;</pre></div></strong>：这个命令能列出表的所有索引信息，包括索引名、列名、是否唯一等。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">information_schema.STATISTICS</pre></div></strong>：这是一个系统视图，包含了更详细的索引统计信息，比如基数（Cardinality）。</li> <li> <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SHOW STATUS LIKE 'Handler%';</pre></div></strong>：可以查看各种<a style="color:#f60; text-decoration:underline;" title="处理器" href="https://www.php.cn/zt/16030.html" target="_blank">处理器</a>操作的次数，比如<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Handler_read_key</pre></div>（通过索引读取的行数），<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">Handler_read_rnd_next</pre></div>（全表扫描的行数），这些数据能间接反映索引的使用效率。</li> </ul> <p><strong>避免过度索引</strong> 这是一个很常见的误区。很多人觉得索引越多越好，但事实并非如此。</p> <ul> <li> <strong>增加写操作开销</strong>：每次<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INSERT</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">UPDATE</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">DELETE</pre></div>操作，数据库都需要同时更新所有相关的索引。索引越多，写操作的性能就越差。</li> <li> <strong>占用存储空间</strong>：每个索引都需要占用磁盘空间，索引多了，数据库文件也会变得更大。</li> <li> <strong>查询优化器选择困难</strong>：索引过多，查询优化器在选择最佳执行计划时，需要考虑的路径就越多，反而可能导致优化器做出次优选择。</li> </ul> <p>所以，在创建索引时，务必做到“少而精”。只创建那些真正能带来性能提升、且高频使用的索引。定期审查你的索引，删除那些不常用或冗余的索引，保持数据库的“轻量化”和高效运行。</p>

以上就是如何利用MySQL索引提升查询性能技巧 MySQL索引优化详细教程助你快速上手的详细内容，更多请关注php中文网其它相关文章！