MySQL 10 MySQL为什么有时候会选错索引？
<h3 id="场景引入">场景引入</h3><p>我们知道，MySQL中一张表可以支持多个索引。但是写SQL语句时，并没有主动指定使用哪个索引，而是由MySQL来确定。而有时候，MySQL会选错索引，导致执行速度变得很慢。</p>
<p>举个例子，假设一张表里有<code>(id,a,b)</code>三个字段，并分别建立索引。然后往表中插入10万行记录，取值依次递增，即数据从<code>(1,1,1)</code>一直到<code>(100000,100000,100000)</code>。</p>
<p>插入过程用了一个存储过程：</p>
<pre><code class="language-sql">delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
</code></pre>
<p>接下来分析一条SQL语句：</p>
<pre><code class="language-sql">select * from t where a between 10000 and 20000;
</code></pre>
<p>显然，该语句能用上索引a。对该语句进行EXPLAIN，查看执行情况：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125601683-432332014.png" width="90%"></div>
<p>接着，在表t上做如下操作：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125631788-258328130.png" width="50%"></div>
<p>如上，session A开启了一个事务，随后session B删除所有数据，又调用存储过程插入数据，并进行查询。</p>
<p>但是，这条查询语句没有选择索引a。使用如下三条语句进行实验：</p>
<pre><code class="language-sql">set long_query_time=0;
select * from t where a between 10000 and 20000;
select * from t force index(a) where a between 10000 and 20000;/*对照作用*/
</code></pre>
<p>慢查询日志如下：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125656023-1343353426.png" width="80%"></div>
<p>可以发现，session B的查询语句走的是全表扫描，即MySQL用错了索引。</p>
<p>这个场景其实很常见，对应平时不断删除数据和新增数据的场景。因此，本文要讲清为什么会选错索引。</p>
<h3 id="优化器的逻辑">优化器的逻辑</h3>
<p>在MySQL 01里，我们已经介绍过了，选择索引是由MySQL的<strong>优化器</strong>完成的。优化器选择索引的目的是找到一个最优的执行方案，并用最小代价执行语句。</p>
<p>在数据库里，判断执行代价的标准有很多。直观的就是扫描行数，扫描行数越少，意味着访问磁盘次数越少，消耗的CPU资源越少。除此之外，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。</p>
<p>在前面的例子中，没有涉及临时表和排序，那么就是在判断扫描行数是判断错误了。因此，我们需要知道，MySQL如何判断扫描行数。</p>
<p>MySQL在执行语句之前，并不能精确知道满足条件的记录有多少条，只能根据统计信息进行估算。这个统计信息指的是索引的区分度。一个索引上不同的值（我们称之为基数）越多，索引的区分度就越好。</p>
<p>可以使用<code>show index</code>方法来看索引的基数。我们查看例子中表t的基数，其结果如下：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125733635-165099955.png" width="90%"></div>
<p>可以发现，尽管三个字段插入的数据都是相同的，但MySQL统计的基数都不同，且都不准确。</p>
<p>由于取每行进行统计代价太高，MySQL在统计基数时使用的是<strong>采样统计</strong>的方法：采样统计时，InnoDB默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，计算出这个索引的基数。</p>
<p>数据表会持续更新，因此索引统计信息也会不断变化。当变更的数据行数超过总行数的1/M，会自动触发重新做一次索引统计。在MySQL中，索引统计有两种存储方式，可以通过设置参数<code>innodb_stats_persistent</code>的值进行选择：</p>
<ul>
<li>
<p>设为on，表示统计信息持久化存储，此时默认<code>N=20,M=10</code>；</p>
</li>
<li>
<p>设为off，表示统计信息只存在内存，此时默认<code>N=8,M=16</code>。</p>
</li>
</ul>
<p>尽管基数统计不是完全准确，但从<code>show index</code>的结果看，大体还是接近的，因此选错索引还有其他原因。</p>
<p>除了进行基数统计，优化器还会判断执行语句本身要扫描多少行。对于例子的语句，优化器预估的扫描行数为：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125811361-126569782.png" width="90%"></div>
<p>从<code>rows</code>字段可以看出，没用上a索引的预计扫描行数为104620，而强制使用a索引的预计扫描行数为37116。</p>
<p>此时，你或许有疑问，既然用上索引a的扫描行数少，且该语句可以用索引a，为什么优化器不使用呢？</p>
<p>这是因为，如果使用索引a，在<code>select *</code>时，需要先从索引a得到id，再回到主键索引找出整行数据，优化器会计算这个代价；如果不使用索引a，是直接在主键索引上扫描并获得数据。在这个例子中，优化器认为直接扫描主键索引更快，尽管该判断是错误的。</p>
<p>因此选错索引的本质原因还在于<strong>没有准确判断出扫描行数</strong>。</p>
<p>既然是统计信息有误，就需要进行修正。可以使用<code>analyze table t</code>来重新统计索引信息：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125836686-1595665607.png" width="90%"></div>
<p>可以发现这次判断正确。因此，当发现explain的结果和实际情况差距较大，可以先使用analyze进行重新统计。</p>
<br>
<p>基于相同的表t，来看另外一个语句：</p>
<pre><code class="language-sql">select * from t where (a between 1 and 1000)and (b between 50000 and 100000) order by b limit 1;
</code></pre>
<p>从条件来看，这个查询返回的是空。那么在索引的选择上，该语句会怎么选择呢？</p>
<p>按照我们自己的分析：</p>
<ul>
<li>
<p>若使用索引a，会先扫描索引a的前1000个值，取到对应的id后进行回表，然后根据字段b进行过滤，这样需要扫描1000行。</p>
</li>
<li>
<p>若使用索引b，会先扫描索引b的最后50001个值，取到对应的id后进行回表，然后根据字段b进行过滤，这样需要扫描50001行。</p>
</li>
</ul>
<p>对该语句explain的结果：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125912765-1613686025.png" width="90%"></div>
<p>可以看到，优化器选择的是索引b，预估扫描行数为50198，即MySQL又选错了索引。</p>
<h3 id="索引选择异常和处理">索引选择异常和处理</h3>
<p>遇到上面例子中选错索引的情况，处理办法主要有三种：</p>
<p>（1）采用<code>force index</code>强制选择索引</p>
<p>在不强制选择索引时，MySQL会根据词法解析结果分析出可能使用的索引，然后依次判断每个索引需要扫描多少行。而强制选择后，MySQL会直接选择这个索引。</p>
<p>比如对于例子2，假设使用<code>force index</code>强制选择索引a：</p>
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250710125939962-457103793.png" width="90%"></div>
<p>可以看到，使用合理的索引，速度快了很多。</p>
<p>不过强制选择也有缺点：</p>
<ul>
<li>
<p>如果索引改名，该语句也得修改；</p>
</li>
<li>
<p>如果以后迁移到其他数据库，该语法不一定兼容；</p>
</li>
<li>
<p>变更不及时，往往是等出现选错索引的问题时才会去强制选择。</p>
</li>
</ul>
<p>（2）修改语句</p>
<p>比如在例子2中，将<code>order by b limit 1</code>改为<code>order by b,a limit 1</code>，语义逻辑不变。但之前优化器使用索引b是因为认为使用索引b可以避免排序，而修改后使用两个索引都需要排序，扫描行数成了影响决策的主要条件，此时优化器选择扫描行数较少的a。</p>
<p>这种方法的缺点就是不通用，需要根据不同语句做不同修改。</p>
<p>（3）新建更合适的索引</p>
<p>在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。</p><br><br>
来源：https://www.cnblogs.com/san-mu/p/18976703
頁:
[1]