如何通过索引优化MySQL查询？创建高效索引的正确步骤
<blockquote>索引优化需先分析查询需求，使用EXPLAIN查看执行计划，优先为高选择性列及WHERE、JOIN、ORDER BY、GROUP BY子句创建复合索引，遵循最左前缀原则，避免过度索引影响写性能。</blockquote>
<p><img src="https://img.php.cn/upload/article/001/503/042/175669992142821.jpeg" alt="如何通过索引优化mysql查询？创建高效索引的正确步骤"></p>
<p>索引优化MySQL查询，说白了，就是给数据库提供一张“地图”，让它能更快找到数据，而不是盲目地翻遍所有记录。这能大幅度提升查询速度。创建高效索引的正确步骤，我认为，不只是技术活，更是一种洞察力，要理解你的数据和应用怎么“问”数据，然后才能对症下药，选择正确的索引类型，甚至调整表结构。</p>
<div class="aritcle_card">
<a class="aritcle_card_img" href="/ai/2373">
<img src="https://img.php.cn/upload/ai_manual/001/246/273/176239923777053.png" alt="论小文">
</a>
<div class="aritcle_card_info">
<a href="/ai/2373">论小文</a>
<p>可靠的论文写作助手，包含11种学术写作类型，万字论文一键生成，可降重降AIGC，参考文献真实可标注，图表代码均可自定义添加。</p>
<div class="">
<img src="/static/images/card_xiazai.png" alt="论小文">
<span>431</span>
</div>
</div>
<a href="/ai/2373" class="aritcle_card_btn">
<span>查看详情</span>
<img src="/static/images/cardxiayige-3.png" alt="论小文">
</a>
</div>
<p>要真正做到高效索引，我们得从几个核心点入手。
你得知道你的数据库“在做什么”。这不是一句空话，而是要深入分析你的应用中最慢、最频繁的查询。<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">EXPLAIN</pre></div>是你的眼睛，它能告诉你MySQL如何执行你的查询，是全表扫描，还是走了索引，走了哪个索引，效果如何。
我经常看到有人直接在所有<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;">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>子句中的列。
选择索引列时，要考虑列的“选择性”或“基数”。高选择性的列（比如用户ID、身份证号）更适合做索引，因为它们能快速缩小结果集。而像性别、状态这种只有几个固定值的列，单独做索引效果可能不佳，除非它们是复合索引的前缀。
复合索引是另一个关键。它的列顺序至关重要。MySQL只能使用索引的最左前缀。比如，<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">INDEX(col1, col2, col3)</pre></div>可以用于<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brus
h:php;toolbar:false;">col1</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col1, col2</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col1, col2, col3</pre></div>的查询，但不能直接用于<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col2</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col3</pre></div>的查询。所以，把最常用的、选择性最高的列放在复合索引的最前面，这是我的经验。
有时候，如果一个索引包含了查询所需的所有列（包括<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT</pre></div>列表中的），那么MySQL甚至不需要回表查询，这叫“覆盖索引”，性能提升非常显著。
但别忘了，索引不是越多越好。每个索引都会占用磁盘空间，并且在数据写入（INSERT, UPDATE, DELETE）时需要维护，这会增加写操作的开销。所以，找到一个平衡点很重要。</p>
<h3>MySQL索引的选择性与基数对性能有何影响？</h3>
<p>这个问题，其实是理解索引效能的核心。简单来说，“选择性”指的是索引列中不重复值的比例。如果一个列的所有值都是唯一的，比如主键，那么它的选择性就是100%。而“基数”则是指该列中不重复值的数量。
当一个列的选择性很高时，MySQL通过索引查找特定值时，能迅速定位到极少数甚至唯一的一行数据。想象一下，你有一本字典，如果每个词条都非常独特，你就能很快找到你要找的那个词。
反之，如果一个列的选择性很低，比如一个“性别”字段，只有“男”和“女”两个值，那么无论你查询“男”还是“女”，MySQL通过这个索引找到的结果集都会占据总数据量的一半左右。这时候，索引的优势就不明显了，甚至可能不如全表扫描来得快，因为数据库还需要额外维护索引的开销。
我个人在实践中，会尽量把高选择性的列放在复合索引的前面。这就像是你在一个大型图书馆里找一本书，如果你知道书名（高选择性），你就能直接去对应的书架。如果你只知道作者的姓氏（低选择性），你可能还得在那个姓氏的区域里找很久。所以，理解并利用好列的选择性，是创建真正高效索引的基石。你可以用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">COUNT(DISTINCT column_name) / COUNT(*)</pre></div>来粗略估算一个列的选择性。</p>
<h3>复合索引的列顺序应该如何设计才能最大化查询效率？</h3>
<p>这真是一个我经常和团队成员讨论的话题，因为这里面学问不小，搞错了代价也大。核心原则是“最左前缀匹配”。这意味着，如果你有一个复合索引 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(A, B, C)</pre></div>，MySQL可以使用 <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;">(B, C)</pre></div> 或 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">C</pre></div> 来开始查找。
那么，具体怎么设计呢？
我通常会建议：把最常用于<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;">=</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>）的列放在最前面。因为这些列是筛选数据的第一道关卡，它们能最快地缩小搜索范围。
如果你的查询经常有<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>操作，并且这些操作的列也在你的<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;">WHERE</pre></div>子句列的后面。这样，MySQL在找到数据后，可能直接从索引中获取排序好的结果，避免了额外的文件排序（filesort），这能带来巨大的性能提升。
举个例子，如果你有一个查询 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE city = 'Beijing' AND age > 25 ORDER BY registration_date DESC;</pre></div>
一个好的复合索引可能是 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(city, age, registration_date)</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;">age</pre></div> 是范围匹配，其次；<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">registration_date</pre></div> 用于排序，放在最后。这样，索引能服务于<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;">ORDER BY</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;">LIKE '%...'</pre></div>），其后续的列就可能无法继续利用索引来过滤了。所以，将等值查询的列放在范围查询的列之前，这是一个非常实用的经验法则。</p>
<h3>索引对数据库写入性能的影响有多大，我们应该如何权衡？</h3>
<p>这是一个老生常谈但又不得不面对的问题：索引是读性能的“加速器”，但也是写性能的“负担”。每次你向表中插入（INSERT）、更新（UPDATE）或删除（DELETE）数据时，数据库不仅仅要操作表中的数据，还需要同步更新所有相关的索引。
这个“负担”具体体现在：</p>
<ol>
<li>
<strong>磁盘I/O和存储空间：</strong> 每个索引都需要占用额外的磁盘空间。当数据写入时，不仅要写入数据文件，还要写入索引文件。</li>
<li>
<strong>CPU开销：</strong> 数据库需要计算新数据的索引位置，并维护索引树的平衡（尤其是B-Tree索引）。这会消耗CPU资源。</li>
<li>
<strong>锁竞争：</strong> 在高并发写入场景下，更新索引可能会导致锁竞争，进而降低写入吞吐量。
所以，一个表上的索引越多，写入操作的开销就越大，性能自然就越慢。
那么，我们该如何权衡呢？
我的经验是，首先要明确你的应用是“读多写少”还是“写多读少”。绝大多数Web应用都是读多写少，这种情况下，适当增加索引以优化查询是值得的。但如果你的应用是像日志系统、实时数据采集这种写入量巨大的场景，那么对索引的设计就必须非常谨慎，甚至可能需要牺牲一部分查询性能来保证写入吞吐量。
在实际操作中，我建议：</li>
</ol>
<ul>
<li>
<strong>只创建必要的索引：</strong> 避免为那些不常用于查询、或者选择性极低的列创建独立索引。</li>
<li>
<strong>利用复合索引：</strong> 尽量用一个复合索引来满足多个查询条件，而不是为每个条件都创建单独索引。</li>
<li>
<strong>延迟索引创建：</strong> 对于一些批处理导入的场景，可以考虑先禁用索引，导入完成后再创建索引，或者在业务低峰期进行。</li>
<li>
<strong>监控写入性能：</strong> 持续监控数据库的写入延迟和吞吐量，如果发现写入性能下降，要检查是否是新增索引导致的。
最终的权衡，没有一劳永逸的答案，它需要你对业务场景、<a style="color:#f60; text-decoration:underline;" title="数据访问" href="https://www.php.cn/zt/35234.html" target="_blank">数据访问</a>模式以及数据库自身的特性有深入的理解和持续的观察。这是一个动态调整的过程。</li>
</ul>
h:php;toolbar:false;">col1</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col1, col2</pre></div>、<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col1, col2, col3</pre></div>的查询，但不能直接用于<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col2</pre></div>或<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">col3</pre></div>的查询。所以，把最常用的、选择性最高的列放在复合索引的最前面，这是我的经验。
有时候，如果一个索引包含了查询所需的所有列（包括<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT</pre></div>列表中的），那么MySQL甚至不需要回表查询，这叫“覆盖索引”，性能提升非常显著。
但别忘了，索引不是越多越好。每个索引都会占用磁盘空间，并且在数据写入（INSERT, UPDATE, DELETE）时需要维护，这会增加写操作的开销。所以，找到一个平衡点很重要。</p>
<h3>MySQL索引的选择性与基数对性能有何影响？</h3>
<p>这个问题，其实是理解索引效能的核心。简单来说，“选择性”指的是索引列中不重复值的比例。如果一个列的所有值都是唯一的，比如主键，那么它的选择性就是100%。而“基数”则是指该列中不重复值的数量。
当一个列的选择性很高时，MySQL通过索引查找特定值时，能迅速定位到极少数甚至唯一的一行数据。想象一下，你有一本字典，如果每个词条都非常独特，你就能很快找到你要找的那个词。
反之，如果一个列的选择性很低，比如一个“性别”字段，只有“男”和“女”两个值，那么无论你查询“男”还是“女”，MySQL通过这个索引找到的结果集都会占据总数据量的一半左右。这时候，索引的优势就不明显了，甚至可能不如全表扫描来得快，因为数据库还需要额外维护索引的开销。
我个人在实践中，会尽量把高选择性的列放在复合索引的前面。这就像是你在一个大型图书馆里找一本书，如果你知道书名（高选择性），你就能直接去对应的书架。如果你只知道作者的姓氏（低选择性），你可能还得在那个姓氏的区域里找很久。所以，理解并利用好列的选择性，是创建真正高效索引的基石。你可以用<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">COUNT(DISTINCT column_name) / COUNT(*)</pre></div>来粗略估算一个列的选择性。</p>
<h3>复合索引的列顺序应该如何设计才能最大化查询效率？</h3>
<p>这真是一个我经常和团队成员讨论的话题，因为这里面学问不小，搞错了代价也大。核心原则是“最左前缀匹配”。这意味着，如果你有一个复合索引 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(A, B, C)</pre></div>，MySQL可以使用 <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;">(B, C)</pre></div> 或 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">C</pre></div> 来开始查找。
那么，具体怎么设计呢？
我通常会建议：把最常用于<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;">=</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>）的列放在最前面。因为这些列是筛选数据的第一道关卡，它们能最快地缩小搜索范围。
如果你的查询经常有<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>操作，并且这些操作的列也在你的<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;">WHERE</pre></div>子句列的后面。这样，MySQL在找到数据后，可能直接从索引中获取排序好的结果，避免了额外的文件排序（filesort），这能带来巨大的性能提升。
举个例子，如果你有一个查询 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">SELECT * FROM users WHERE city = 'Beijing' AND age > 25 ORDER BY registration_date DESC;</pre></div>
一个好的复合索引可能是 <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(city, age, registration_date)</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;">age</pre></div> 是范围匹配，其次；<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">registration_date</pre></div> 用于排序，放在最后。这样，索引能服务于<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;">ORDER BY</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;">LIKE '%...'</pre></div>），其后续的列就可能无法继续利用索引来过滤了。所以，将等值查询的列放在范围查询的列之前，这是一个非常实用的经验法则。</p>
<h3>索引对数据库写入性能的影响有多大，我们应该如何权衡？</h3>
<p>这是一个老生常谈但又不得不面对的问题：索引是读性能的“加速器”，但也是写性能的“负担”。每次你向表中插入（INSERT）、更新（UPDATE）或删除（DELETE）数据时，数据库不仅仅要操作表中的数据，还需要同步更新所有相关的索引。
这个“负担”具体体现在：</p>
<ol>
<li>
<strong>磁盘I/O和存储空间：</strong> 每个索引都需要占用额外的磁盘空间。当数据写入时，不仅要写入数据文件，还要写入索引文件。</li>
<li>
<strong>CPU开销：</strong> 数据库需要计算新数据的索引位置，并维护索引树的平衡（尤其是B-Tree索引）。这会消耗CPU资源。</li>
<li>
<strong>锁竞争：</strong> 在高并发写入场景下，更新索引可能会导致锁竞争，进而降低写入吞吐量。
所以，一个表上的索引越多，写入操作的开销就越大，性能自然就越慢。
那么，我们该如何权衡呢？
我的经验是，首先要明确你的应用是“读多写少”还是“写多读少”。绝大多数Web应用都是读多写少，这种情况下，适当增加索引以优化查询是值得的。但如果你的应用是像日志系统、实时数据采集这种写入量巨大的场景，那么对索引的设计就必须非常谨慎，甚至可能需要牺牲一部分查询性能来保证写入吞吐量。
在实际操作中，我建议：</li>
</ol>
<ul>
<li>
<strong>只创建必要的索引：</strong> 避免为那些不常用于查询、或者选择性极低的列创建独立索引。</li>
<li>
<strong>利用复合索引：</strong> 尽量用一个复合索引来满足多个查询条件，而不是为每个条件都创建单独索引。</li>
<li>
<strong>延迟索引创建：</strong> 对于一些批处理导入的场景，可以考虑先禁用索引，导入完成后再创建索引，或者在业务低峰期进行。</li>
<li>
<strong>监控写入性能：</strong> 持续监控数据库的写入延迟和吞吐量，如果发现写入性能下降，要检查是否是新增索引导致的。
最终的权衡，没有一劳永逸的答案，它需要你对业务场景、<a style="color:#f60; text-decoration:underline;" title="数据访问" href="https://www.php.cn/zt/35234.html" target="_blank">数据访问</a>模式以及数据库自身的特性有深入的理解和持续的观察。这是一个动态调整的过程。</li>
</ul>以上就是如何通过索引优化MySQL查询？创建高效索引的正确步骤的详细内容，更多请关注其它相关文章！
热门内容推荐
快捷栏目导航
