探索MySQL是否走索引(一)——范围查询一定走索引吗？

站长

2023年05月28日 01:25 · 阅读数 13

首先，准备4个版本的数据库，5.5/5.6/5.7/8.0

然后，每个库中有一模一样的表，数据量一样，建立的索引一样，InnoDB引擎。

先看一看，各个版本中，索引统计信息（Cardinality代表该列在总数据中有多少个不同的值，该值为估算值）

MySQL5.5
MySQL5.6
MySQL5.7
MySQL8.0

对比可知，统计的数据量方面，基本都差不多。

本文探索>,<,>=,<=走索引的情况，首先回答标题的问题，>,<,>=,<=不一定走索引，因为优化器会计算，符合条件的值的总和，与总数据量的比值，比值<=0.30，才会走索引。

官网原话如下： Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

查询每个表索引，并使用最佳索引，除非优化程序认为使用表扫描更有效。

一次使用扫描是基于**最佳索引**是否**跨越了表的30％以上**，但是固定百分比不再决定使用索引还是扫描。现在，优化器更加复杂，其估计基于**其他因素**，**例如表大小，行数和I / O块大小**。

注意：这段话有多个重点

假如where条件中，涉及到了多个索引，MySQL会选择一个最佳索引。最佳索引就是选择性最高的索引，因为它可以过滤掉很多的无用数据行。
0.30比例值。以前可能是>0.30就不走索引，但文档最低版本是5.6已无从考证。现在不仅取决于比例值，也取决于其他因素。所以，用 > 或 >= 或 < 或 <=其中测试以下即可。

接下来，准备开干。

MySQL5.5版本中，通过不断测试，找到了8这个关键数字。
```
explain select user_name,user_age from user1 where user_age <= 8;
explain select user_id,user_name,user_age from user1 where user_age < 8;
```
第二张图中Extra虽然没有明确说明Using index ,但是type显示为range范围查询，扫描行数rows为35042行，但第一张图中type:ALL，rows:210463与文章一开始的全表统计信息中，主键id数据量一致，主键id的数据量=全表数据多少行，可知这是全表扫描。差距只是在于是否包含8这个值，因此统计一下，<8 和 =8的数据量（特别说明，8这个值是试出来的，不可能一下找到该值）

计算一下比值：user_age < 8 比值为：35042/210463 = 0.1664（走索引） user_age <= 8比值为：(35042 + 2221)/210463 = 0.1770（不走索引）以上可知：比例值<=0.1664可以走索引，比例值>=0.1770优化器认为全表扫描更快，所以就不走索引。测试大于的情况（89也是不断测试出来的）
```
explain select user_name,user_age from user1 where user_age >= 89;
explain select user_id,user_name,user_age from user1 where user_age > 89;
```
一样，统计一下 >89 和 =89 的数据量

user_age > 89的比例值：35894 / 210463 = 0.1705（走索引） user_age >= 89的比例值：(35894 + 2088)/210463 = 0.1804（不走索引）结合user_age < 8的比例值，和user_age > 89的比例值，可知，对于5.5版本，user_age这个索引列，比例值在 0.1705~0.1770之间。

特殊情况1，可以使用FORCE INDEX强制走索引，不管比例值是多少
```
explain select user_name,user_age from user1 force index(idx_user_age) where user_age < 50;
explain select user_name,user_age from user1 force index(idx_user_age) where user_age > 20;
```
特殊情况2：只返回索引列时，不管比例值是多少，都会走索引
```
explain select user_age from user1 where user_age < 80;
```
MySQL5.6只测试一下<的情况（通过测试关键值也是8）
```
explain select user_name,user_age from user1 where user_age <= 8;
explain select user_id,user_name,user_age from user1 where user_age < 8;
```
同样计算一下比例值： user_age < 8 ：35042 / 208534 = 0.1680（走索引） user_age <= 8：(35042+2221)/208534 = 0.1786（不走索引）由此可大致推断：5.6版本下，该比例值在 0.1680 ~ 0.1786之间。 特别注意：第二张图片中，出现了using index condition，这是MySQL5.6做出的优化，ICP（index condition pushdown）可对比5.5版本，Extra中显示的是Using where，代表数据最终是在MySQL服务层过滤数据的，5.6版本显示的是Using index condition，代表数据在存储引擎层就过滤了无用的数据，ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。官网地址如下：dev.mysql.com/doc/refman/… 同样，如果使用FORCE INDEX,则肯定会走索引，只返回索引列，不管比例值，照样走索引，此处不再贴图
MySQL5.7只测试一下<的情况（测试到20是个关键值）
```
explain select user_name,user_age from user1 where user_age <= 20;
explain select user_id,user_name,user_age from user1 where user_age < 20;
```
计算比例值如下： user_age < 20：87966 / 208303 = 0.422 （走索引） user_age <= 20：(87966 + 2093)/208303 = 0.4323（不走索引）同样，如果使用FORCE INDEX,则肯定会走索引，只返回索引列，不管比例值，照样走索引，此处不再贴图 特别注意：此处出现MRR，本处不讨论，后边会有详细讨论。
MySQL8.0只测试一下<的情况(测试到关键值为12)
```
explain select user_name,user_age from user1 where user_age <= 12;
explain select user_id,user_name,user_age from user1 where user_age < 12;
```
计算一下比例值： user_age < 12：54798 / 208611 = 0.2626（走索引） user_age <= 12：(54798 + 2051)/208611 = 0.2725（不走索引）由此可推测，比例值在 0.2626 ~ 0.2725之间同样，如果使用FORCE INDEX,则肯定会走索引，只返回索引列，不管比例值，照样走索引，此处不再贴图

总结：

并不是给一个列建立了索引，对这个列进行范围查询的时候，就会走索引，他是有一个比例值的。比例值会随着版本、服务器、IO、数据量、数据重复情况而不同。也就是说，同一个版本，同一个库表，此时和下一时刻，比例值就可能不一样。测试中途遇到过该问题。
MySQL5.6版本的时候，进行了优化，ICP 和 MRR，极大提升性能。后边也会有例子。
FORCE INDEX 的作用，特殊情况下，可以只返回索引列。